SQL AND SCALA

FOR

BEGINNERS

LEARN TO CODE FAST

BY

TAM SEL

SQL

What is Data?

RDBMS

Difference between DBMS and RDBMS

SQL Syntax

SQL Data Types

SQL Operators

SQL CREATE Database

SQL DROP Database

SQL RENAME Database

SQL Table

SQL TABLE Variable

SQL CREATE TABLE

SQL DROP TABLE

SQL DELETE TABLE

SQL RENAME TABLE

SQL TRUNCATE TABLE

SQL COPY TABLE

SQL TEMP TABLE

SQL ALTER TABLE

SQL SELECT

SQL SELECT UNIQUE

SQL SELECT DISTINCT

SQL SELECT COUNT

SQL SELECT TOP

SQL SELECT FIRST

SQL SELECT LAST

SQL SELECT RANDOM

SQL SELECT AS

SQL SELECT IN

SQL SELECT from Multiple Tables

SQL SELECT DATE

SQL SELECT SUM

SQL SELECT NULL

SQL Clause

SQL WHERE

SQL AND

SQL OR

SQL WITH CLAUSE

SQL ORDER BY Clause

SQL ORDER BY CLAUSE WITH ASCENDING ORDER

SQL Insert

SQL INSERT STATEMENT

SQL Update

SQL UPDATE DATE

SQL Delete

SQL JOIN

SQL Keys

SQL PRIMARY KEY

Unique Key in SQL

SQL vs NoSQL

SQL Interview Questions

SCALA

History of Scala

Features of Scala

Simple Program of Scala

Scala Variables and Data Types

Scala Conditional Expressions

Scala Pattern Matching

Scala while loop

Scala for loop

Scala Break

Scala Comments

Scala Functions

Scala Higher Order Functions

Scala Object and Class

Scala Singleton and Companion Object

Scala Case Classes and Case Object

Scala Constructor

Scala Method Overloading

Scala this

Scala Inheritance

Scala Method Overriding

Scala Field Overriding

Scala Final

Scala Abstract Class

Scala Trait

Scala Trait Mixins

Scala Access Modifier

Scala Array

Scala Multidimensional Array

Scala String

Scala String Methods

Scala String Interpolation

Scala Exception Handling

Scala Try Catch

Scala Finally

Scala Throw keyword

Scala Throws Keyword

Scala Custom Exception

Scala Collection

Scala Set

Scala HashSet

Scala BitSet

Scala ListSet

Scala Seq

Scala Vector

Scala List

Scala Queue

Scala Stream

Scala Maps

Scala HashMap

Scala ListMap

Scala Tuples

Scala Tuple Example: Function Return Multiple Values

Scala File handling

Scala Multithreading

Scala Thread

Scala Interview Questions

SQL

FOR

BEGINNERS

LEARN TO CODE FAST

BY

TAM SEL

SQL

SQL tutorial provides basic and advanced concepts of SQL. Our SQL tutorial is designed for beginners and professionals.

SQL (Structured Query Language) is used to perform operations on the records stored in the database such as updating records, deleting records, creating and modifying tables, views, etc.

SQL is just a query language; it is not a database. To perform SQL queries, you need to install any database, for example, Oracle, MySQL, MongoDB, PostGre SQL, SQL Server, DB2, etc.

What is SQL

- SQL stands for Structured Query Language.

- It is designed for managing data in a relational database management system (RDBMS).

- It is pronounced as S-Q-L or sometime See-Qwell.

- SQL is a database language, it is used for database creation, deletion, fetching rows, and modifying rows, etc.

- SQL is based on relational algebra and tuple relational calculus.

All DBMS like MySQL, Oracle, MS Access, Sybase, Informix, Postgres, and SQL Server use SQL as standard database language.

Why SQL is required

SQL is required:

- To create new databases, tables and views

- To insert records in a database

- To update records in a database

- To delete records from a database

- To retrieve data from a database

What SQL does

- With SQL, we can query our database in several ways, using English-like statements.

- With SQL, a user can access data from a relational database management system.

- It allows the user to describe the data.

- It allows the user to define the data in the database and manipulate it when needed.

- It allows the user to create and drop database and table.

- It allows the user to create a view, stored procedure, function in a database.

- It allows the user to set permission on tables, procedures, and views.

What is Data?

Data is a collection of a distinct small unit of information. It can be used in a variety of forms like text, numbers, media, bytes, etc. it can be stored in pieces of paper or electronic memory, etc.

Word 'Data' is originated from the word 'datum' that means 'single piece of information.' It is plural of the word datum.

In computing, Data is information that can be translated into a form for efficient movement and processing. Data is interchangeable.

What is Database?

A database is an organized collection of data, so that it can be easily accessed and managed.

You can organize data into tables, rows, columns, and index it to make it easier to find relevant information.

Database handlers create a database in such a way that only one set of software program provides access of data to all the users.

The main purpose of the database is to operate a large amount of information by storing, retrieving, and managing data.

There are many dynamic websites on the World Wide Web nowadays which are handled through databases. For example, a model that checks the availability of rooms in a hotel. It is an example of a dynamic website that uses a database.

There are many databases available like MySQL, Sybase, Oracle, MongoDB, Informix, PostgreSQL, SQL Server, etc.

Modern databases are managed by the database management system (DBMS).

SQL or Structured Query Language is used to operate on the data stored in a database. SQL depends on relational algebra and tuple relational calculus.

A cylindrical structure is used to display the image of a database.

Evolution of Databases

The database has completed more than 50 years of journey of its evolution from flat-file system to relational and objects relational systems. It has gone through several generations.

The Evolution

File-Based

1968 was the year when File-Based database were introduced. In file-based databases, data was maintained in a flat file. Though files have many advantages, there are several limitations.

One of the major advantages is that the file system has various access methods, e.g., sequential, indexed, and random.

It requires extensive programming in a third-generation language such as COBOL, BASIC.

Hierarchical Data Model

1968-1980 was the era of the Hierarchical Database. Prominent hierarchical database model was IBM's first DBMS. It was called IMS (Information Management System).

In this model, files are related in a parent/child manner.

ike file system, this model also had some limitations like complex implementation, lack structural independence, can't easily handle a many-many relationship, etc.

Network data model

Charles Bachman developed the first DBMS at Honeywell called Integrated Data Store (IDS). It was developed in the early 1960s, but it was standardized in 1971 by the CODASYL group (Conference on Data Systems Languages).

In this model, files are related as owners and members, like to the common network model.

Network data model identified the following components:

- Network schema (Database organization)

- Sub-schema (views of database per user)

- Data management language (procedural)

This model also had some limitations like system complexity and difficult to design and maintain.

Relational Database

1970 - Present: It is the era of Relational Database and Database Management. In 1970, the relational model was proposed by E.F. Codd.

Relational database model has two main terminologies called instance and schema.

The instance is a table with rows or columns

Schema specifies the structure like name of the relation, type of each column and name.

This model uses some mathematical concept like set theory and predicate logic.

The first internet database application had been created in 1995.

During the era of the relational database, many more models had introduced like object-oriented model, object-relational model, etc.

Cloud database

Cloud database facilitates you to store, manage, and retrieve their structured, unstructured data via a cloud platform. This data is accessible over the Internet. Cloud databases are also called a database as service (DBaaS) because they are offered as a managed service.

Some best cloud options are:

- AWS (Amazon Web Services)

- Snowflake Computing

- Oracle Database Cloud Services

- Microsoft SQL server

- Google cloud spanner

Advantages of cloud database

Lower costs

Generally, company provider does not have to invest in databases. It can maintain and support one or more data centers.

Automated

Cloud databases are enriched with a variety of automated processes such as recovery, failover, and auto-scaling.

Increased accessibility

You can access your cloud-based database from any location, anytime. All you need is just an internet connection.

NoSQL Database

A NoSQL database is an approach to design such databases that can accommodate a wide variety of data models. NoSQL stands for "not only SQL." It is an alternative to traditional relational databases in which data is placed in tables, and data schema is perfectly designed before the database is built.

NoSQL databases are useful for a large set of distributed data.

Some examples of NoSQL database system with their category are:

- MongoDB, CouchDB, Cloudant (Document-based)

- Memcached, Redis, Coherence (key-value store)

- HBase, Big Table, Accumulo (Tabular)

Advantage of NoSQL

High Scalability

NoSQL can handle an extensive amount of data because of scalability. If the data grows, NoSQL database scale it to handle that data in an efficient manner.

High Availability

NoSQL supports auto replication. Auto replication makes it highly available because, in case of any failure, data replicates itself to the previous consistent state.

Disadvantage of NoSQL

Open source

NoSQL is an open-source database, so there is no reliable standard for NoSQL yet.

Management challenge

Data management in NoSQL is much more complicated than relational databases. It is very challenging to install and even more hectic to manage daily.

GUI is not available

GUI tools for NoSQL database are not easily available in the market.

Backup

Backup is a great weak point for NoSQL databases. Some databases, like MongoDB, have no powerful approaches for data backup.

The Object-Oriented Databases

The object-oriented databases contain data in the form of object and classes. Objects are the real-world entity, and types are the collection of objects. An object-oriented database is a combination of relational model features with objects oriented principles. It is an alternative implementation to that of the relational model.

Object-oriented databases hold the rules of object-oriented programming. An object-oriented database management system is a hybrid application.

The object-oriented database model contains the following properties.

Object-oriented programming properties

- Objects

- Classes

- Inheritance

- Polymorphism

- Encapsulation

Relational database properties

- Atomicity

- Consistency

- Integrity

- Durability

- Concurrency

- Query processing

Graph Databases

A graph database is a NoSQL database. It is a graphical representation of data. It contains nodes and edges. A node represents an entity, and each edge represents a relationship between two edges. Every node in a graph database represents a unique identifier.

Graph databases are beneficial for searching the relationship between data because they highlight the relationship between relevant data.

Graph databases are very useful when the database contains a complex relationship and dynamic schema.

It is mostly used in supply chain management, identifying the source of IP telephony.

DBMS (Data Base Management System)

Database management System is software which is used to store and retrieve the database. For example, Oracle, MySQL, etc.; these are some popular DBMS tools.

- DBMS provides the interface to perform the various operations like creation, deletion, modification, etc.

- DBMS allows the user to create their databases as per their requirement.

- DBMS accepts the request from the application and provides specific data through the operating system.

- DBMS contains the group of programs which acts according to the user instruction.

- It provides security to the database.

Advantage of DBMS

Controls redundancy

It stores all the data in a single database file, so it can control data redundancy.

Data sharing

An authorized user can share the data among multiple users.

Backup

It providesBackup and recovery subsystem. This recovery system creates automatic data from system failure and restores data if required.

Multiple user interfaces

It provides a different type of user interfaces like GUI, application interfaces.

Disadvantage of DBMS

Size

It occupies large disk space and large memory to run efficiently.

Cost

DBMS requires a high-speed data processor and larger memory to run DBMS software, so it is costly.

Complexity

DBMS creates additional complexity and requirements.

RDBMS (Relational Database Management System)

The word RDBMS is termed as 'Relational Database Management System.' It is represented as a table that contains rows and column.

RDBMS is based on the Relational model; it was introduced by E. F. Codd.

A relational database contains the following components:

- Table

- Record/ Tuple

- Field/Column name /Attribute

- Instance

- Schema

- Keys

An RDBMS is a tabular DBMS that maintains the security, integrity, accuracy, and consistency of the data.

RDBMS

RDBMS stands for Relational Database Management Systems..

All modern database management systems like SQL, MS SQL Server, IBM DB2, ORACLE, My-SQL and Microsoft Access are based on RDBMS.

It is called Relational Data Base Management System (RDBMS) because it is based on relational model introduced by E.F. Codd.

How it works

Data is represented in terms of tuples (rows) in RDBMS.

Relational database is most commonly used database. It contains number of tables and each table has its own primary key.

Due to a collection of organized set of tables, data can be accessed easily in RDBMS.

Brief History of RDBMS

During 1970 to 1972, E.F. Codd published a paper to propose the use of relational database model.

RDBMS is originally based on that E.F. Codd's relational model invention.

What is table

The RDBMS database uses tables to store data. A table is a collection of related data entries and contains rows and columns to store data.

A table is the simplest example of data storage in RDBMS.

Let's see the example of student table.

ID-Name-AGE-COURSE

1-James-24-B.Tech

2-Michael-20-C.A

3-Robert-21-BCA

4-David-22-MCA

5-George-26-BSC

What is field

Field is a smaller entity of the table which contains specific information about every record in the table. In the above example, the field in the student table consist of id, name, age, course.

What is row or record

A row of a table is also called record. It contains the specific information of each individual entry in the table. It is a horizontal entity in the table. For example: The above table contains 5 records.

Let's see one record/row in the table.

1-James-24-B.Tech

What is column

A column is a vertical entity in the table which contains all information associated with a specific field in a table. For example: "name" is a column in the above table which contains all information about student's name.

James

Michael

Robert

David

George

NULL Values

The NULL value of the table specifies that the field has been left blank during record creation. It is totally different from the value filled with zero or a field that contains space.

Data Integrity

There are the following categories of data integrity exist with each RDBMS:

Entity integrity: It specifies that there should be no duplicate rows in a table.

Domain integrity: It enforces valid entries for a given column by restricting the type, the format, or the range of values.

Referential integrity: It specifies that rows cannot be deleted, which are used by other records.

User-defined integrity: It enforces some specific business rules that are defined by users. These rules are different from entity, domain or referential integrity.

Difference between DBMS and RDBMS

Although DBMS and RDBMS both are used to store information in physical database but there are some remarkable differences between them.

The main differences between DBMS and RDBMS are given below:

DBMS Vs RDBMS

1)

DBMS applications store data as file.

RDBMS applications store data in a tabular form.

2)

In DBMS, data is generally stored in either a hierarchical form or a navigational form.

In RDBMS, the tables have an identifier called primary key and the data values are stored in the form of tables.

3)

Normalization is not present in DBMS.

Normalization is present in RDBMS.

4)

DBMS does not apply any security with regards to data manipulation.

RDBMS defines the integrity constraint for the purpose of ACID (Atomocity, Consistency, Isolation and Durability) property.

5)

DBMS uses file system to store data, so there will be no relation between the tables.

in RDBMS, data values are stored in the form of tables, so a relationship between these data values will be stored in the form of a table as well.

6)

DBMS has to provide some uniform methods to access the stored information.

RDBMS system supports a tabular structure of the data and a relationship between them to access the stored information.

7)

DBMS does not support distributed database.

RDBMS supports distributed database.

8)

DBMS is meant to be for small organization and deal with small data. it supports single user.

RDBMS is designed to handle large amount of data. it supports multiple users.

9)

Examples of DBMS are file systems, xml etc.

Example of RDBMS are mysql, postgre, sql server, oracle etc.

After observing the differences between DBMS and RDBMS, you can say that RDBMS is an extension of DBMS. There are many software products in the market today who are compatible for both DBMS and RDBMS. Means today a RDBMS application is DBMS application and vice-versa.

SQL Syntax

SQL follows some unique set of rules and guidelines called syntax. Here, we are providing all the basic SQL syntax.

- SQL is not case sensitive. Generally SQL keywords are written in uppercase.

- SQL statements are dependent on text lines. We can place a single SQL statement on one or multiple text lines.

- You can perform most of the action in a database with SQL statements.

- SQL depends on relational algebra and tuple relational calculus.

SQL statement

SQL statements are started with any of the SQL commands/keywords like SELECT, INSERT, UPDATE, DELETE, ALTER, DROP etc. and the statement ends with a semicolon (;).

Example of SQL statement:

1. SELECT "column_name" FROM "table_name";

Why semicolon is used after SQL statements:

Semicolon is used to separate SQL statements. It is a standard way to separate SQL statements in a database system in which more than one SQL statements are used in the same call.

In this tutorial, we will use semicolon at the end of each SQL statement.

SQL Commands

These are the some important SQL command:

SELECT: it extracts data from a database.

UPDATE: it updates data in database.

DELETE: it deletes data from database.

CREATE TABLE: it creates a new table.

ALTER TABLE: it is used to modify the table.

DROP TABLE: it deletes a table.

CREATE DATABASE: it creates a new database.

ALTER DATABASE: It is used to modify a database.

INSERT INTO: it inserts new data into a database.

CREATE INDEX: it is used to create an index (search key).

DROP INDEX: it deletes an index.

SQL Data Types

Data types are used to represent the nature of the data that can be stored in the database table. For example, in a particular column of a table, if we want to store a string type of data then we will have to declare a string data type of this column.

Data types mainly classified into three categories for every database.

- String Data types

- Numeric Data types

- Date and time Data types

Data Types in MySQL, SQL Server and Oracle Databases

MySQL Data Types

A list of data types used in MySQL database. This is based on MySQL 8.0.

MySQL String Data Types

CHAR(Size)

It is used to specify a fixed length string that can contain numbers, letters, and special characters. Its size can be 0 to 255 characters. Default is 1.

VARCHAR(Size)

It is used to specify a variable length string that can contain numbers, letters, and special characters. Its size can be from 0 to 65535 characters.

BINARY(Size)

It is equal to CHAR() but stores binary byte strings. Its size parameter specifies the column length in the bytes. Default is 1.

VARBINARY(Size)

It is equal to VARCHAR() but stores binary byte strings. Its size parameter specifies the maximum column length in bytes.

TEXT(Size)

It holds a string that can contain a maximum length of 255 characters.

TINYTEXT

It holds a string with a maximum length of 255 characters.

MEDIUMTEXT

It holds a string with a maximum length of 16,777,215.

LONGTEXT

It holds a string with a maximum length of 4,294,967,295 characters.

ENUM(val1, val2, val3,...)

It is used when a string object having only one value, chosen from a list of possible values. It contains 65535 values in an ENUM list. If you insert a value that is not in the list, a blank value will be inserted.

SET( val1,val2,val3,....)

It is used to specify a string that can have 0 or more values, chosen from a list of possible values. You can list up to 64 values at one time in a SET list.

BLOB(size)

It is used for BLOBs (Binary Large Objects). It can hold up to 65,535 bytes.

MySQL Numeric Data Types

BIT(Size)

It is used for a bit-value type. The number of bits per value is specified in size. Its size can be 1 to 64. The default value is 1.

INT(size)

It is used for the integer value. Its signed range varies from -2147483648 to 2147483647 and unsigned range varies from 0 to 4294967295. The size parameter specifies the max display width that is 255.

INTEGER(size)

It is equal to INT(size).

FLOAT(size, d)

It is used to specify a floating point number. Its size parameter specifies the total number of digits. The number of digits after the decimal point is specified by d parameter.

FLOAT(p)

It is used to specify a floating point number. MySQL used p parameter to determine whether to use FLOAT or DOUBLE. If p is between 0 to24, the data type becomes FLOAT (). If p is from 25 to 53, the data type becomes DOUBLE().

DOUBLE(size, d)

It is a normal size floating point number. Its size parameter specifies the total number of digits. The number of digits after the decimal is specified by d parameter.

DECIMAL(size, d)

It is used to specify a fixed point number. Its size parameter specifies the total number of digits. The number of digits after the decimal parameter is specified by d parameter. The maximum value for the size is 65, and the default value is 10. The maximum value for d is 30, and the default value is 0.

DEC(size, d)

It is equal to DECIMAL(size, d).

BOOL

It is used to specify Boolean values true and false. Zero is considered as false, and nonzero values are considered as true.

MySQL Date and Time Data Types

DATE

It is used to specify date format YYYY-MM-DD. Its supported range is from '1000-01-01' to '9999-12-31'.

DATETIME(fsp)

It is used to specify date and time combination. Its format is YYYY-MM-DD hh:mm:ss. Its supported range is from '1000-01-01 00:00:00' to 9999-12-31 23:59:59'.

TIMESTAMP(fsp)

It is used to specify the timestamp. Its value is stored as the number of seconds since the Unix epoch('1970-01-01 00:00:00' UTC). Its format is YYYY-MM-DD hh:mm:ss. Its supported range is from '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC.

TIME(fsp)

It is used to specify the time format. Its format is hh:mm:ss. Its supported range is from '-838:59:59' to '838:59:59'

YEAR

It is used to specify a year in four-digit format. Values allowed in four digit format from 1901 to 2155, and 0000.

SQL Server Data Types

SQL Server String Data Type

char(n)

It is a fixed width character string data type. Its size can be up to 8000 characters.

varchar(n)

It is a variable width character string data type. Its size can be up to 8000 characters.

varchar(max)

It is a variable width character string data types. Its size can be up to 1,073,741,824 characters.

text

It is a variable width character string data type. Its size can be up to 2GB of text data.

nchar

It is a fixed width Unicode string data type. Its size can be up to 4000 characters.

nvarchar

It is a variable width Unicode string data type. Its size can be up to 4000 characters.

ntext

It is a variable width Unicode string data type. Its size can be up to 2GB of text data.

binary(n)

It is a fixed width Binary string data type. Its size can be up to 8000 bytes.

varbinary

It is a variable width Binary string data type. Its size can be up to 8000 bytes.

image

It is also a variable width Binary string data type. Its size can be up to 2GB.

SQL Server Numeric Data Types

bit

It is an integer that can be 0, 1 or null.

tinyint

It allows whole numbers from 0 to 255.

Smallint

It allows whole numbers between -32,768 and 32,767.

Int

It allows whole numbers between -2,147,483,648 and 2,147,483,647.

bigint

It allows whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807.

float(n)

It is used to specify floating precision number data from -1.79E+308 to 1.79E+308. The n parameter indicates whether the field should hold the 4 or 8 bytes. Default value of n is 53.

real

It is a floating precision number data from -3.40E+38 to 3.40E+38.

money

It is used to specify monetary data from -922,337,233,685,477.5808 to 922,337,203,685,477.5807.

SQL Server Date and Time Data Type

datetime

It is used to specify date and time combination. It supports range from January 1, 1753, to December 31, 9999 with an accuracy of 3.33 milliseconds.

datetime2

It is used to specify date and time combination. It supports range from January 1, 0001 to December 31, 9999 with an accuracy of 100 nanoseconds

date

It is used to store date only. It supports range from January 1, 0001 to December 31, 9999

time

It stores time only to an accuracy of 100 nanoseconds

timestamp

It stores a unique number when a new row gets created or modified. The time stamp value is based upon an internal clock and does not correspond to real time. Each table may contain only one-time stamp variable.

SQL Server Other Data Types

Sql_variant

It is used for various data types except for text, timestamp, and ntext. It stores up to 8000 bytes of data.

XML

It stores XML formatted data. Maximum 2GB.

cursor

It stores a reference to a cursor used for database operations.

table

It stores result set for later processing.

uniqueidentifier

It stores GUID (Globally unique identifier).

Oracle Data Types

Oracle String data types

CHAR(size)

It is used to store character data within the predefined length. It can be stored up to 2000 bytes.

NCHAR(size)

It is used to store national character data within the predefined length. It can be stored up to 2000 bytes.

VARCHAR2(size)

It is used to store variable string data within the predefined length. It can be stored up to 4000 byte.

VARCHAR(SIZE)

It is the same as VARCHAR2(size). You can also use VARCHAR(size), but it is suggested to use VARCHAR2(size)

NVARCHAR2(size)

It is used to store Unicode string data within the predefined length. We have to must specify the size of NVARCHAR2 data type. It can be stored up to 4000 bytes.

Oracle Numeric Data Types

NUMBER(p, s)

It contains precision p and scale s. The precision p can range from 1 to 38, and the scale s can range from -84 to 127.

FLOAT(p)

It is a subtype of the NUMBER data type. The precision p can range from 1 to 126.

BINARY_FLOAT

It is used for binary precision( 32-bit). It requires 5 bytes, including length byte.

BINARY_DOUBLE

It is used for double binary precision (64-bit). It requires 9 bytes, including length byte.

Oracle Date and Time Data Types

DATE

It is used to store a valid date-time format with a fixed length. Its range varies from January 1, 4712 BC to December 31, 9999 AD.

TIMESTAMP

It is used to store the valid date in YYYY-MM-DD with time hh:mm:ss format.

Oracle Large Object Data Types (LOB Types)

BLOB

It is used to specify unstructured binary data. Its range goes up to 2^32-1 bytes or 4 GB.

BFILE

It is used to store binary data in an external file. Its range goes up to 2^32-1 bytes or 4 GB.

CLOB

It is used for single-byte character data. Its range goes up to 2^32-1 bytes or 4 GB.

NCLOB

It is used to specify single byte or fixed length multibyte national character set (NCHAR) data. Its range is up to 2^32-1 bytes or 4 GB.

RAW(size)

It is used to specify variable length raw binary data. Its range is up to 2000 bytes per row. Its maximum size must be specified.

LONG RAW

It is used to specify variable length raw binary data. Its range up to 2^31-1 bytes or 2 GB, per row.

SQL Operators

SQL statements generally contain some reserved words or characters that are used to perform operations such as comparison and arithmetical operations etc. These reserved words or characters are known as operators.

Generally there are three types of operators in SQL:

1. SQL Arithmetic Operators

2. SQL Comparison Operators

3. SQL Logical Operators

SQL Arithmetic Operators:

Let's assume two variables "a" and "b". Here "a" is valued 50 and "b" valued 100.

Example:

Operators and Descriptions and Examples

+

It is used to add containing values of both operands

a+b will give 150

-

It subtracts right hand operand from left hand operand

a-b will give -50

*

It multiply both operand's values

a*b will give 5000

/

It divides left hand operand by right hand operand

b/a will give 2

%

It divides left hand operand by right hand operand and returns reminder

b%a will give 0

SQL Comparison Operators:

Let's take two variables "a" and "b" that are valued 50 and 100.

Operator and Description and Example

=

Examine both operands value that are equal or not,if yes condition become true.

(a=b) is not true

!=

This is used to check the value of both operands equal or not,if not condition become true.

(a!=b) is true

< >

Examines the operand's value equal or not, if values are not equal condition is true

(a<>b) is true

>

Examine the left operand value is greater than right Operand, if yes condition becomes true

(a>b) is not true

<

Examines the left operand value is less than right Operand, if yes condition becomes true

(a<="" td="">

>=

Examines that the value of left operand is greater than or equal to the value of right operand or not,if yes condition become true

(a>=b) is not true

<=

Examines that the value of left operand is less than or equal to the value of right operand or not, if yes condition becomes true

(a<=b) is true

!<

Examines that the left operand value is not less than the right operand value

(a!<="" td="">

!>

Examines that the value of left operand is not greater than the value of right operand

(a!>b) is true

SQL Logical Operators:

This is the list of logical operators used in SQL.

Operator

Description

ALL

this is used to compare a value to all values in another value set.

AND

this operator allows the existence of multiple conditions in an SQL statement.

ANY

this operator is used to compare the value in list according to the condition.

BETWEEN

this operator is used to search for values, that are within a set of values

IN

this operator is used to compare a value to that specified list value

NOT

the NOT operator reverse the meaning of any logical operator

OR

this operator is used to combine multiple conditions in SQL statements

EXISTS

the EXISTS operator is used to search for the presence of a row in a specified table

LIKE

this operator is used to compare a value to similar values using wildcard operator

SQL CREATE Database

The SQL CREATE DATABASE statement is used by a developer to create a database.

Let's see the syntax of SQL CREATE DATABASE:

1. CREATE DATABASE database_name;

If you want to add tables in that database, you can use CREATE TABLE statement.

Create Database in MySQL

In MySQL, same command is used to create a database.

1. CREATE DATABASE database_name;

Create Database in Oracle

You don't need to create database in Oracle. In Oracle database, you can create tables directly.

You can also rename, drop and select database that is covered in next pages.

We should always remember that database name should be unique in the RDBMS.

SQL DROP Database

SQL DROP statement is used to delete or remove indexes from a table in the database.

If you want to delete or drop an existing database in a SQL schema, you can use SQL DROP DATABASE

Let's see the syntax of SQL DROP DATABASE:

1. DROP DATABASE database_name;

If you delete or drop the database, all the tables and views will also be deleted. So be careful while using this command.

SQL RENAME Database

SQL RENAME DATABASE is used when you need to change the name of your database. Sometimes it is used because you think that the original name is not more relevant to the database or you want to give a temporary name to that database.

Let's see how to rename MySql and SQL Server databases.

Rename MySQL database

To rename the mysql database, you need to follow the following syntax:

1. RENAME DATABASE old_db_name TO new_db_name;

Rename SQL server database using T-SQL

This command is useful for SQL server 2005, 2008, 2008R2 and 2012.

1. ALTER DATABASE old_name MODIFY NAME = new_name

If you are using SQL server 2000, you can also use this command to rename the database. But, Microsoft phased out it.

1. EXEC sp_renamedb 'old_name' , 'new_name'

SQL SELECT Database

In MySQL database, you need to select a database first before executing any query on table, view etc. To do so, we use following query:

1. USE DATABASE database_name;

In oracle, you don't need to select database.

SQL Table

Table is a collection of data, organized in terms of rows and columns. In DBMS term, table is known as relation and row as tuple.

Note: A table has a specified number of columns, but can have any number of rows.

Table is the simple form of data storage. A table is also considered as a convenient representation of relations.

Let's see an example of an employee table:

Employee

EMP_NAME-ADDRESS-SALARY

Ankit-Lucknow-15000

Raman-Allahabad-18000

Mike-New York-20000

In the above table, "Employee" is the table name, "EMP_NAME", "ADDRESS" and "SALARY" are the column names. The combination of data of multiple columns forms a row e.g. "Ankit", "Lucknow" and 15000 are the data of one row.

SQL TABLE Variable

The SQL Table variable is used to create, modify, rename, copy and delete tables. Table variable was introduced by Microsoft.

It was introduced with SQL server 2000 to be an alternative of temporary tables.

It is a variable where we temporary store records and results. This is same like temp table but in the case of temp table we need to explicitly drop it.

Table variables are used to store a set of records. So declaration syntax generally looks like CREATE TABLE syntax.

1. create table "tablename"

2. ("column1" "data type",

3. "column2" "data type",

4. ...

5. "columnN" "data type");

When a transaction rolled back the data associated with table variable is not rolled back.

A table variable generally uses lesser resources than a temporary variable.

Table variable cannot be used as an input or an output parameter.

SQL CREATE TABLE

SQL CREATE TABLE statement is used to create table in a database.

If you want to create a table, you should name the table and define its column and each column's data type.

Let's see the simple syntax to create the table.

1. create table "tablename"

2. ("column1" "data type",

3. "column2" "data type",

4. "column3" "data type",

5. ...

6. "columnN" "data type");

The data type of the columns may vary from one database to another. For example, NUMBER is supported in Oracle database for integer value whereas INT is supported in MySQL.

Let us take an example to create a STUDENTS table with ID as primary key and NOT NULL are the constraint showing that these fields cannot be NULL while creating records in the table.

1. SQL> CREATE TABLE STUDENTS (

2. ID INT                           NOT NULL,

3. NAME VARCHAR (20) NOT NULL,

4. AGE INT                         NOT NULL,

5. ADDRESS CHAR (25),

6. PRIMARY KEY (ID)

7. );

You can verify it, if you have created the table successfully by looking at the message displayed by the SQL Server, else you can use DESC command as follows:

SQL> DESC STUDENTS;

FIELD-TYPE-NULL-KEY-DEFAULT-EXTRA

ID-Int(11)-NO-PRI--

NAME-Varchar(20)-NO---

AGE-Int(11)-NO---

ADDRESS-Varchar(25)-YES--NULL-

4 rows in set (0.00 sec)

Now you have the STUDENTS table available in your database and you can use to store required information related to students.

SQL CREATE TABLE Example in MySQL

Let's see the command to create a table in MySQL database.

1. CREATE TABLE Employee

2. (

3. EmployeeID int,

4. FirstName varchar(255),

5. LastName varchar(255),

6. Email varchar(255),

7. AddressLine varchar(255),

8. City varchar(255)

9. );

SQL CREATE TABLE Example in Oracle

Let's see the command to create a table in Oracle database.

1. CREATE TABLE Employee

2. (

3. EmployeeID number(10),

4. FirstName varchar2(255),

5. LastName varchar2(255),

6. Email varchar2(255),

7. AddressLine varchar2(255),

8. City varchar2(255)

9. );

SQL CREATE TABLE Example in Microsoft SQLServer

Let's see the command to create a table in SQLServer database. It is same as MySQL and Oracle.

1. CREATE TABLE Employee

2. (

3. EmployeeID int,

4. FirstName varchar(255),

5. LastName varchar(255),

6. Email varchar(255),

7. AddressLine varchar(255),

8. City varchar(255)

9. );

Create a Table using another table

We can create a copy of an existing table using the create table command. The new table gets the same column signature as the old table. We can select all columns or some specific columns.

If we create a new table using an old table, the new table will be filled with the existing value from the old table.

The basic syntax for creating a table with the other table is:

1. CREATE TABLE table_name  AS

2. SELECT column1, column2,...

3. FROM old_table_name WHERE ..... ;

4. The following SQL creates a copy of the employee table.

5. CREATE TABLE EmployeeCopy AS

6. SELECT EmployeeID, FirstName, Email

7. FROM Employee;

SQL Primary Key with CREATE TABLE Statement

The following query creates a PRIMARY KEY on the "D" column when the "Employee" table is created.

MySQL

1. CREATE TABLE Employee(

2. EmployeeID NOT NULL,

3. FirstName varchar(255) NOT NULL,

4. LastName varchar(255),

5. City varchar(255),

6. PRIMARY KEY (EmployeeID)

7. );

SQL Server / Oracle / MS Access

1. CREATE TABLE Employee(

2. EmployeeID NOT NULL PRIMARY KEY,

3. FirstName varchar(255) NOT NULL,

4. LastName varchar(255),

5. City varchar(255)

6. );

Use the following query to define a PRIMARY KEY constraints on multiple columns, and to allow naming of a PRIMARY KEY constraints.

For MySQL / SQL Server /Oracle / MS Access

1. CREATE TABLE Employee(

2. EmployeeID NOT NULL,

3. FirstName varchar(255) NOT NULL,

4. LastName varchar(255),

5. City varchar(255),

6. CONSTRAINT     PK_Employee PRIMARY KEY (EmployeeID, FirstName)

7. );

SQL DROP TABLE

A SQL DROP TABLE statement is used to delete a table definition and all data from a table.

This is very important to know that once a table is deleted all the information available in the table is lost forever, so we have to be very careful when using this command.

Let's see the syntax to drop the table from the database.

1. DROP TABLE "table_name";

Let us take an example:

First we verify STUDENTS table and then we would delete it from the database.

1. SQL> DESC STUDENTS;

FIELD-TYPE-NULL-KEY-DEFAULT-EXTRA

ID-Int(11)-NO-PRI--

NAME-Varchar(20)-NO---

AGE-Int(11)-NO---

ADDRESS-Varchar(25)-YES--NULL-

1. 4 rows in set (0.00 sec)

This shows that STUDENTS table is available in the database, so we can drop it as follows:

1. SQL>DROP TABLE STUDENTS;

Now, use the following command to check whether table exists or not.

1. SQL> DESC STUDENTS;

1. Query OK, 0 rows affected (0.01 sec)

As you can see, table is dropped so it doesn't display it.

SQL DROP TABLE Example in MySQL

Let's see the command to drop a table from the MySQL database.

1. DROP TABLE table_name;

SQL DROP TABLE Example in Oracle

Let's see the command to drop a table from Oracle database. It is same as MySQL.

1. DROP TABLE table_name;

SQL DROP TABLE Example in Microsoft SQLServer

Let's see the command to drop a table from SQLServer database. It is same as MySQL.

1. DROP TABLE table_name;

SQL DELETE TABLE

The DELETE statement is used to delete rows from a table. If you want to remove a specific row from a table you should use WHERE condition.

1. DELETE FROM table_name [WHERE condition];

But if you do not specify the WHERE condition it will remove all the rows from the table.

1. DELETE FROM table_name;

There are some more terms similar to DELETE statement like as DROP statement and TRUNCATE statement but they are not exactly same there are some differences between them.

Difference between DELETE and TRUNCATE statements

There is a slight difference b/w delete and truncate statement. The DELETE statement only deletes the rows from the table based on the condition defined by WHERE clause or delete all the rows from the table when condition is not specified.

But it does not free the space containing by the table.

The TRUNCATE statement: it is used to delete all the rows from the table and free the containing space.

Let's see an "employee" table.

Emp_id-Name-Address-Salary

1-Michael-Allahabad-22000

2-Shurabhi-Varanasi-13000

3-Pappu-Delhi-24000

Execute the following query to truncate the table:

1. TRUNCATE TABLE employee;

Difference b/w DROP and TRUNCATE statements

When you use the drop statement it deletes the table's row together with the table's definition so all the relationships of that table with other tables will no longer be valid.

When you drop a table:

- Table structure will be dropped

- Relationship will be dropped

- Integrity constraints will be dropped

- Access privileges will also be dropped

On the other hand when we TRUNCATE a table, the table structure remains the same, so you will not face any of the above problems.

SQL RENAME TABLE

SQL RENAME TABLE syntax is used to change the name of a table. Sometimes, we choose non-meaningful name for the table. So it is required to be changed.

Let's see the syntax to rename a table from the database.

1. ALTER TABLE table_name

2. RENAME TO new_table_name;

Optionally, you can write following command to rename the table.

1. RENAME old_table _name To new_table_name;

Let us take an example of a table named "STUDENTS", now due to some reason we want to change it into table name "ARTISTS".

Table1: students

Name-Age-City

Amrita gill-25-Amritsar

Amrender sirohi-22-Ghaziabad

Divya khosla-20-Delhi

You should use any one of the following syntax to RENAME the table name:

1. ALTER TABLE STUDENTS

2. RENAME TO ARTISTS;

Or

1. RENAME STUDENTS TO ARTISTS;

2. After that the table "students" will be changed into table name "artists"

SQL TRUNCATE TABLE

A truncate SQL statement is used to remove all rows (complete data) from a table. It is similar to the DELETE statement with no WHERE clause.

TRUNCATE TABLE Vs DELETE TABLE

Truncate table is faster and uses lesser resources than DELETE TABLE command.

TRUNCATE TABLE Vs DROP TABLE

Drop table command can also be used to delete complete table but it deletes table structure too. TRUNCATE TABLE doesn't delete the structure of the table.

Let's see the syntax to truncate the table from the database.

1. TRUNCATE TABLE table_name;

For example, you can write following command to truncate the data of employee table

1. TRUNCATE TABLE Employee;

Note: The rollback process is not possible after truncate table statement. Once you truncate a table you cannot use a flashback table statement to retrieve the content of the table.

SQL COPY TABLE

If you want to copy a SQL table into another table in the same SQL server database, it is possible by using the select statement.

The syntax of copying table from one to another is given below:

1. SELECT * INTO <destination_table> FROM <source_table>

For example, you can write following command to copy the records of hr_employee table into employee table.

1. SELECT * INTO admin_employee FROM hr_employee;

Note: SELECT INTO is totally different from INSERT INTO statement

SQL TEMP TABLE

The concept of temporary table is introduced by SQL server. It helps developers in many ways:

Temporary tables can be created at run-time and can do all kinds of operations that a normal table can do. These temporary tables are created inside tempdb database.

There are two types of temp tables based on the behavior and scope.

1. Local Temp Variable

2. Global Temp Variable

Local Temp Variable

Local temp tables are only available at current connection time. It is automatically deleted when user disconnects from instances. It is started with hash (#) sign.

1. CREATE TABLE #local temp table (

2. User id int,

3. Username varchar (50),

4. User address varchar (150)

5. )

Global Temp Variable

Global temp tables name starts with double hash (##). Once this table is created, it is like a permanent table. It is always ready for all users and not deleted until the total connection is withdrawn.

1. CREATE TABLE ##new global temp table (

2. User id int,

3. User name varchar (50),

4. User address varchar (150)

5. )

SQL ALTER TABLE

The ALTER TABLE statement is used to add, modify or delete columns in an existing table. It is also used to rename a table.

You can also use SQL ALTER TABLE command to add and drop various constraints on an existing table.

SQL ALTER TABLE Add Column

If you want to add columns in SQL table, the SQL alter table syntax is given below:

1. ALTER TABLE table_name ADD column_name column-definition;

If you want to add multiple columns in table, the SQL table will be

1. ALTER TABLE table_name

2. ADD (column_1 column-definition,

3. column_2 column-definition,

4. .....

5. column_n column-definition);

SQL ALTER TABLE Modify Column

If you want to modify an existing column in SQL table, syntax is given below:

1. ALTER TABLE table_name MODIFY column_name column_type;

If you want to modify multiple columns in table, the SQL table will be

1. ALTER TABLE table_name

2. MODIFY (column_1 column_type,

3. column_2 column_type,

4. .....

5. column_n column_type);

SQL ALTER TABLE DROP Column

The syntax of alter table drop column is given below:

1. ALTER TABLE table_name DROP COLUMN column_name;

SQL ALTER TABLE RENAME Column

The syntax of alter table rename column is given below:

1. ALTER TABLE table_name

2. RENAME COLUMN old_name to new_name;

SQL SELECT

The most commonly used SQL command is SELECT statement. It is used to query the database and retrieve selected data that follow the conditions we want.

In simple words, we can say that the select statement used to query or retrieve data from a table in the database.

Let's see the syntax of select statement.

1. SELECT expressions

2. FROM tables

3. WHERE conditions;

Here expression is the column that we want to retrieve.

Tables indicate the tables, we want to retrieve records from.

Optional clauses in SELECT statement

There are some optional clauses in SELECT statement:

[WHERE Clause] : It specifies which rows to retrieve.

[GROUP BY Clause] : Groups rows that share a property so that the aggregate function can be applied to each group.

[HAVING Clause] : It selects among the groups defined by the GROUP BY clause.

[ORDER BY Clause] : It specifies an order in which to return the rows.

For example, let a database table: student_details;

ID-First_name-Last_name-Age-Subject-Hobby

1-Amar-Sharma-20-Maths-Cricket

2-Akbar-Khan-22-Biology-Football

3-Anthony-Milton-25-Commerce-Gambling

From the above example, select the first name of all the students. To do so, query should be like this:

1. SELECT first_name FROM student_details;

Note: the SQL commands are not case sensitive. We can also write the above SELECT statement as:

1. select first_name from student_details;

Now, you will get following data:

Amar

Akbar

Anthony

We can also retrieve data from more than one column. For example, to select first name and last name of all the students, you need to write

1. SELECT first_name, last_name FROM student_details;

Now, you will get following data:

Amar-Sharma

Akbar-Khan

Anthony-Milton

We can also use clauses like WHERE, GROUP BY, HAVING, ORDER BY with SELECT statement.

Here a point is notable that only SELECT and FROM statements are necessary in SQL SELECT statements. Other clauses like WHERE, GROUP BY, ORDER BY, HAVING may be optional.

SQL SELECT UNIQUE

Actually, there is no difference between DISTINCT and UNIQUE.

SELECT UNIQUE is an old syntax which was used in oracle description but later ANSI standard defines DISTINCT as the official keyword.

After that oracle also added DISTINCT but did not withdraw the service of UNIQUE keyword for the sake of backward compatibility.

In simple words, we can say that SELECT UNIQUE statement is used to retrieve a unique or distinct element from the table.

Let's see the syntax of select unique statement.

1. SELECT UNIQUE column_name

2. FROM table_name;

SQL SELECT DISTINCT statement can also be used for the same cause.

SQL SELECT DISTINCT

The SQL DISTINCT command is used with SELECT key word to retrieve only distinct or unique data.

In a table, there may be a chance to exist a duplicate value and sometimes we want to retrieve only unique values. In such scenarios, SQL SELECT DISTINCT statement is used.

Note: SQL SELECT UNIQUE and SQL SELECT DISTINCT statements are same.

Let's see the syntax of select distinct statement.

1. SELECT DISTINCT column_name ,column_name

2. FROM  table_name;

Let's try to understand it by the table given below:

Student_Name-Gender-Mobile_Number-HOME_TOWN

Rahul Ojha-Male-7503896532-Lucknow

Disha Rai-Female-9270568893-Varanasi

Sonoo Jaiswal-Male-9990449935-Lucknow

Here is a table of students from where we want to retrieve distinct information For example: distinct home-town.

1. SELECT DISTINCT home_town

2. FROM students

Now, it will return two rows.

HOME_TOWN

Lucknow

Varanasi

SQL SELECT COUNT

The SQL COUNT() function is used to return the number of rows in a query.

The COUNT() function is used with SQL SELECT statement and it is very useful to count the number of rows in a table having enormous data.

For example: If you have a record of the voters in selected area and want to count the number of voters then it is very difficult to do it manually but you can do it easily by using the SQL SELECT COUNT query.

Let's see the syntax of SQL COUNT statement.

1. SELECT COUNT (expression)

2. FROM tables

3. WHERE conditions;

Let's see the examples of sql select count function.

SQL SELECT COUNT(column_name)

1. SELECT COUNT(name) FROM employee_table;

It will return the total number of names of employee_table. But null fields will not be counted.

SQL SELECT COUNT(*)

1. SELECT COUNT(*) FROM employee_table;

The "select count(*) from table" is used to return the number of records in table.

SQL SELECT COUNT(DISTINCT column_name)

1. SELECT COUNT(DISTINCT name) FROM employee_table;

It will return the total distinct names of employee_table.

SQL SELECT TOP

The SQL SELECT TOP Statement is used to select top data from a table. The top clause specifies that how many rows are returned.

Let's see an example. If a table has a large number of data, select top statement determines that how many rows will be retrieved from the given table.

There is an example of employee table:

EMP_ID-NAME-SIR_NAME-USER_NAME

1-RAHUL-OJHA-ra@jha

2-ANU-SHARMA-anusha1

3-RAVI-SINGHAL-ravin

Let's see the syntax for the select top statement.

1. SELECT COUNT (expression)

Let's see the example of sql select top statement.

1. SELECT TOP 2 * FROM employee

It will return the following table:

EMP_ID-NAME-SIR_NAME-USER_NAME

1-RAHUL-OJHA-ra@jha

2-ANU-SHARMA-anusha1

SQL SELECT FIRST

The SQL first() function is used to return the first value of the selected column.

Let's see the syntax of sql select first() function:

1. SELECT FIRST(column_name) FROM table_name;

Here a point is notable that first function is only supported by MS Access.

If you want to retrieve the first value of the "customer_name" column from the "customers" table, you need to write following query:

1. SELECT FIRST(customer_name) AS first_customer FROM customers;

Let us take the example of CUSTOMERS to examine SQL SELECT FIRST command:

Table CUSTOMERS

CUSTOMER_NAME-AGE-ADDRESS-EXPENDITURE

KAMAL SHARMA-26-GHAZIABAD-6000

ROBERT PETT-23-NEWYORK-26000

SHIKHA SRIVASTAV-22-DELHI-9000

If you want to retrieve the first value of the "customer_name" column from the "customers" table, you need to write following query:

Let's see the syntax of sql select first() function:

1. SELECT FIRST (CUSTOMER_NAME) AS first_customer FROM CUSTOMERS;

2. After that query, you will find the result:

3. KAMAL SHARMA

Note: The SELECT FIRST statement is only supported by MS Access. This statement doesn't work with other databases like Oracle, MySQL etc.

SQL SELECT LAST

The last() function is used to return the last value of the specified column.

Syntax for SQL SELECT LAST() FUNCTION:

1. SELECT LAST (column_name) FROM table_name;

You should note that the last() function is only supported in MS Access. But there are ways to get the last record in MySql, SQL Server, Oracle etc. databases.

My SQL syntax:

1. SELECT column_name FROM table_name

2. ORDER BY column_name DESC

3. LIMIT 1;

SQL Server syntax:

1. SELECT TOP 1 column_name FROM table_name

2. ORDER BY column_name DESC;

Oracle syntax:

1. SELECT column_name FROM table_name

2. ORDER BY column_name DESC

3. WHERE ROWNUM <=1;

Let us take the example of CUSTOMERS to examine SQL SELECT LAST command:

Table CUSTOMERS

CUSTOMER_NAME-AGE-ADDRESS-EXPENDITURE

KAMAL SHARMA-26-GHAZIABAD-6000

ROBERT PETT-23-NEWYORK-26000

SHIKHA SRIVASTAV-22-DELHI-9000

If you want to retrieve the last value of the "customer_name" column from the "customers" table, you need to write following query:

1. SELECT LAST (CUSTOMER_NAME) AS LAST_CUSTOMER FROM CUSTOMERS;

2. After that query, you will find the result:

3. SHIKHA SRIVASTAV

SQL SELECT RANDOM

The SQL SELECT RANDOM() function returns the random row. It can be used in online exam to display the random questions.

There are a lot of ways to select a random record or row from a database table. Each database server needs different SQL syntax.

If you want to select a random row with MY SQL:

1. SELECT column FROM table

2. ORDER BY RAND ( )

3. LIMIT 1

If you want to select a random row with Microsoft SQL server:

1. SELECT TOP 1 column FROM table

2. ORDER BY NEW ID()

If you want to select a random record with ORACLE:

1. SELECT column FROM

2. (SELECT column FROM table

3. ORDER BY dbms_random.value)

4. WHERE rownum =1

If you want to select a random row with PostgreSQL:

1. SELECT column FROM table

2. ORDER BY RAND()

3. LIMIT 1

SQL SELECT AS

SQL AS is used to assign temporarily a new name to a table column.

It makes easy presentation of query results and allows the developer to label results more accurately without permanently renaming table columns.

Let's see the example of select as:

1. SELECT day_of_order AS "Date"

2. Customer As "Client",

3. Product,

4. Quantity,

5. FROM orders;

Let us take a table named orders, it contains:

Day_of_order-Customer-Product-Quantity

11-09-2001-James-Mobile-2

13-12-2001-Mayank-Laptop-20

26-12-2004-Balaswamy-Water cannon-35

After applying this SQL AS example syntax

1. SELECT day_of_order AS "Date"

2. Customer As "Client",

3. Product,

4. Quantity,

5. FROM orders;

Result will be shown as this table:

Date-Client-Product-Quantity

11-09-2001-James-Mobile-2

13-12-2001-Mayank-Laptop-20

26-12-2004-Balaswamy-Water cannon-35

Note: SQL AS is same as SQL ALIAS

SQL SELECT IN

SQL IN is an operator used in a SQL query to help reduce the need to use multiple SQL "OR" conditions.

It is used in SELECT, INSERT, UPDATE or DELETE statement.

Advantage of SQL SELECT IN

It minimizes the use of SQL OR operator.

Let's see the syntax for SQL IN:

1. Expression IN (value 1, value 2 ... value n);

Take an example with character values.

1. SELECT *

2. FROM students

3. WHERE students_name IN ( Amit , Raghav, Rajeev)

Let's take another example with numeric values.

1. SELECT *

2. FROM marks

3. WHERE roll_no IN (001, 023, 024);

SQL SELECT from Multiple Tables

This statement is used to retrieve fields from multiple tables. To do so, we need to use join query to get data from multiple tables.

Let's see the example for the select from multiple tables:

1. SELECT orders.order_id, suppliers.name

2. FROM suppliers

3. INNER JOIN orders

4. ON suppliers.supplier_id = orders.supplier_id

5. ORDER BY order_id;

Let us take three tables, two tables of customers named customer1 and customer2 and the third table is product table.

Customer1 table

Cus_id-Name1

1-Jack

2-Jill

Customer2 table

Cus_id-Name2

1-Sandy

2-Venus

Product table

P_id-Cus_id-P_name

1-1-Laptop

2-2-Phone

3-P1-Pen

4-P2-Notebook

Example syntax to select from multiple tables:

1. SELECT p. p_id, p.cus_id, p.p_name, c1.name1, c2.name2

2. FROM product AS p

3. LEFT JOIN customer1 AS c1

4. ON p.cus_id=c1.cus_id

5. LEFT JOIN customer2 AS c2

6. ON p.cus_id = c2.cus_id

P_id-Cus_id-P_name-P_name-P_name

1-1-Laptop-Jack-NULL

2-2-Phone-Jill-NULL

3-P1-Pen-NULL-Sandy

4-P2-Notebook-NULL-Venus

SQL SELECT DATE

SQL SELECT DATE is used to retrieve a date from a database. If you want to find a particular date from a database, you can use this statement.

For example: let's see the query to get all the records after '2013-12-12'.

1. SELECT * FROM

2. table-name WHERE your date-column >= '2013-12-12'

Let's see the another query to get all the records after '2013-12-12' and before '2013-12-13' date.

1. SELECT* FROM

2. table-name where your date-column < '2013-12-13' and your date-column >= '2013-12-12'

If you want to compare the dates within the query, you should use BETWEEN operator to compare the dates.

1. SELECT * FROM

2. table_name WHERE yourdate BETWEEN '2012-12-12' and '2013-12-12'

Or if you are looking for one date in particular you can use. You should change the date parameter into the acceptable form.

1. SELECT* FROM

2. table_name WHERE cast (datediff (day, 0, yourdate) as datetime) = '2012-12-12'

SQL SELECT SUM

It is also known as SQL SUM() function. It is used in a SQL query to return summed value of an expression.

Let's see the Syntax for the select sum function:

1. SELECT SUM (expression)

2. FROM tables

3. WHERE conditions;

expression may be numeric field or formula.

This would produce the following result.

ID-EMPLOYEE_NAME-SALARY

1-JACK REACHER-32000

2-PADMA ROBERTWARI-22000

3-JOE PETRA-41000

4-AMBUJ AGRAWAL-21000

After using this SQL SELECT SUM example, it will produce the result containing the sum of the salary greater than 20000.

Total salary: 116,000

SQL SUM EXAMPLE with single field:

If you want to know how the combined total salary of all employee whose salary is above 20000 per month.

1. SELECT SUM (salary) AS "Total Salary"

2. FROM employees

3. WHERE salary > 20000;

In this example, you will find the expression as "Total Salary" when the result set is returned.

SQL SUM EXAMPLE with SQL DISTINCT:

You can also use SQL DISTINCT clause with SQL SUM function.

1. SELECT SUM (DISTINCT salary) AS "Total Salary"

2. FROM employees

3. WHERE salary > 20000;

SQL SUM EXAMPLE with SQL GROUP BY:

Sometimes there is a need to use the SQL GROUP BY statement with the SQL SUM function.

For example, we could also use the SQL SUM function to return the name of department and the total sales related to department.

1. SELECT department, SUM (sales) AS "Total Sales"

2. FROM order_details

3. GROUP BY department;

Let us take a table named order_details

ID-DEPARTMENT-DATE-DAILY SALES

1-Mechanical-2012-08-13-360

2-Electrical-2012-08-13-100

2-Electrical-2012-08-14-110

3-Electronics-2012-08-13-150

3-Electronics-2012-08-14-170

After using the SQL GROUP BY statement with SUM, you will find the following result.

DEPARTMENT-SUM(DAILY SALES)

Mechanical-360

Electrical-210

electronics-320

SQL SELECT NULL

First of all we should know that what null value is? Null values are used to represent missing unknown data.

There can be two conditions:

1. Where SQL is NULL

2. Where SQL is NOT NULL

If in a table, a column is optional, it is very easy to insert data in column or update an existing record without adding a value in this column. This means that field has null value.

Note: we should not compare null value with 0. They are not equivalent.

Where SQL is NULL:

How to select records with null values only? (in the marks column)

There is an example of student table:

SIR_NAME-NAME-MARKS

TYAGI-SEEMA-

SINGH-RAMAN-5.5

SHARMA-AMAR-

JAISWAL-VICKY-6.2

Let's see the query to get all the records where marks is NULL:

1. SELECT SIR_NAME, NAME, MARKS FROM STUDENTS

2. WHERE MARKS IS NULL

It will return the following records:

SIR_NAME-NAME-MARKS

SHARMA-AMAR-

TYAGI-SEEMA-

Where SQL is NOT NULL:

How to select records with no null values(in marks column)? Let's see the query to get all the records where marks is NOT NULL

1. SELECT SIR_NAME, FIRSTNAME, MARKS FROM STUDENTS

2. WHERE MARKS IS NOT NULL

SIR_NAME-NAME-MARKS

SINGH-RAMAN-5.5

JAISWAL-VICKY-6.2

SQL Clause

SQL WHERE

A WHERE clause in SQL is a data manipulation language statement.

WHERE clauses are not mandatory clauses of SQL DML statements. But it can be used to limit the number of rows affected by a SQL DML statement or returned by a query.

Actually. it filters the records. It returns only those queries which fulfill the specific conditions.

WHERE clause is used in SELECT, UPDATE, DELETE statement etc.

Let's see the syntax for sql where:

1. SELECT column1, column 2, ... column n

2. FROM    table_name

3. WHERE [conditions]

WHERE clause uses some conditional selection

=-equal

>-greater than

<-less than

>=-greater than or equal

<=-less than or equal

< >-not equal to

SQL AND

The SQL AND condition is used in SQL query to create two or more conditions to be met.

It is used in SQL SELECT, INSERT, UPDATE and DELETE statements.

Let's see the syntax for SQL AND:

1. SELECT columns

2. FROM tables

3. WHERE condition 1

4. AND condition 2;

The SQL AND condition requires that both conditions should be met.

The SQL AND condition also can be used to join multiple tables in a SQL statement.

SQL "AND" example with "INSERT" statement

This is how an SQL "AND" condition can be used in the SQL INSERT statement.

For example:

1. INSERT INTO suppliers

2. (supplier_id, supplier_name)

3. SELECT account_no, name

4. FROM customers

5. WHERE customer_name ='IBM'

6. AND employees =1000;

SQL "AND" example with "UPDATE" statement

This is how the "AND" condition can be used in the SQL UPDATE statement.

For example:

1. UPDATE suppliers

2. SET supplier_name = 'HP'

3. WHERE supplier_name = 'IBM'

4. AND offices = 8;

SQL "AND" example with "DELETE" statement

This is how an SQL "AND" condition can be used in the SQL DELETE statement.

For example:

1. DELETE FROM suppliers

2. WHERE supplier_name = 'IBM'

3. AND product = 'PC computers';

SQL OR

The SQL OR condition is used in a SQL query to create a SQL statement where records are returned when any one of the condition met. It can be used in a SELECT statement, INSERT statement, UPDATE statement or DELETE statement.

Let's see the syntax for OR condition:

1. SELECT columns

2. FROM tables

3. WHERE condition 1

4. OR condition 2;

SQL "OR" example with SQL SELECT

1. SELECT *

2. FROM suppliers

3. WHERE city = 'New York'

4. OR available_products >= 250;

SQL "OR" example with SQL INSERT

You can see in below example that how an SQL "OR" condition is used with SQL insert statement.

For example:

1. INSERT INTO suppliers(supplier_id, supplier_name)

2. SELECT account_no, name

3. FROM customers

4. WHERE city = 'New Delhi'

5. OR city = 'Ghaziabad';

SQL "OR" example with SQL UPDATE

For example:

1. UPDATE suppliers

2. SET supplier_name = 'HP'

3. WHERE supplier_name = 'IBM'

4. OR available_product >36;

SQL "OR" example with SQL DELETE

For example:

1. DELETE FROM suppliers

2. WHERE supplier_name = 'IBM'

3. OR employee <=100;

SQL WITH CLAUSE

The SQL WITH clause is used to provide a sub-query block which can be referenced in several places within the main SQL query. It was introduced by oracle in oracle 9i release2 database.

There is an example of employee table:

Syntax for the SQL WITH clause -

This syntax is for SQL WITH clause using a single sub-query alias.

1. WITH <alias_name> AS (sql_sub-query_statement)

2. SELECT column_list FROM <alias_name> [table name]

3. [WHERE <join_condition>]

When you use multiple sub-query aliases, the syntax will be as follows.

1. WITH <alias_name_A>  AS (sql_sub-query_statement)

2. <alias_name_B> AS (sql_sub-query_statement_from_alias_name_A

3. Or sql_sub-query_statement)

4. SELECT <column_list>

5. FROM <alias_name_A >,< alias_name_B >, [tablenames]

6. [WHERE < join_condition>]

SQL ORDER BY Clause

The SQL ORDER BY clause is used for sorting data in ascending and descending order based on one or more columns.

Some databases sort query results in ascending order by default.

SQL ORDER BY syntax:

1. SELECT expressions

2. FROM tables

3. WHERE conditions

4. ORDER BY expression [ASC | DESC];

Let us take a CUSTOMERS table having the following records:

ID-NAME-AGE-ADDRESS-SALARY

1-Himani gupta-21-Modinagar-22000

2-Shiva tiwari-22-Bhopal-21000

3-James bhargav-45-Meerut-65000

4-Ritesh yadav-36-Azamgarh-26000

5-Balwant singh-45-Varanasi-36000

6-Robert -26-Mathura-22000

This is an example that would sort the result in ascending order by NAME and SALARY.

1. SELECT * FROM CUSTOMERS

2. ORDER BY NAME, SALARY;

This would produce the following result.

ID-NAME-AGE-ADDRESS-SALARY

3-James bhargav-45-Meerut-65000

5-Balwant singh-45-Varanasi-36000

1-Himani gupta-21-Modinagar-22000

6-Robert -26-Mathura-22000

4-Ritesh yadav-36-Azamgarh-26000

2-Shiva tiwari-22-Bhopal-21000

This is an example to sort the result in descending order by NAME.

1. SELECT * FROM CUSTOMERS

2. ORDER BY NAME DESC;

This would produce the following result.

ID-NAME-AGE-ADDRESS-SALARY

2-Shiva tiwari-22-Bhopal-21000

4-Ritesh yadav-36-Azamgarh-26000

6-Robert -26-Mathura-22000

1-Himani gupta-21-Modinagar-22000

5-Balwant singh-45-Varanasi-36000

3-James bhargav-45-Meerut-65000

SQL ORDER BY CLAUSE WITH ASCENDING ORDER

This statement is used to sort data in ascending order. If you miss the ASC attribute, SQL ORDER BY query takes ascending order by default.

Let's take an example of supplier

1. SELECT supplier_city

2. FROM suppliers

3. WHERE supplier_name = 'IBM'

4. ORDER BY supplier_city;

Let us take a CUSTOMERS table having the following records:

ID-NAME-AGE-ADDRESS-SALARY

1-Himani gupta-21-Modinagar-22000

2-Shiva tiwari-22-Bhopal-21000

3-James bhargav-45-Meerut-65000

4-Ritesh yadav-36-Azamgarh-26000

5-Balwant singh-45-Varanasi-36000

6-Robert -26-Mathura-22000

This is an example to sort the result in ascending order by NAME and SALARY.

1. SELECT * FROM CUSTOMERS

2. ORDER BY NAME, SALARY;

This would produce the following result.

ID-NAME-AGE-ADDRESS-SALARY

3-James bhargav-45-Meerut-65000

5-Balwant singh-45-Varanasi-36000

1-Himani gupta-21-Modinagar-22000

6-Robert -26-Mathura-22000

4-Ritesh yadav-36-Azamgarh-26000

2-Shiva tiwari-22-Bhopal-21000

SQL ORDER BY CLAUSE WITH DESCENDING ORDER:

This statement is used to sort data in descending order. You should use the DESC attribute in your ORDER BY clause as follows.

1. SELECT supplier_city

2. FROM suppliers

3. WHERE supplier_name = 'IBM'

4. ORDER BY supplier_city DESC;

Let's see an example of an employee table:

ID-NAME-AGE-ADDRESS-SALARY

1-Himani gupta-21-Modinagar-22000

2-Shiva tiwari-22-Bhopal-21000

3-James bhargav-45-Meerut-65000

4-Ritesh yadav-36-Azamgarh-26000

5-Balwant singh-45-Varanasi-36000

6-Robert -26-Mathura-22000

This is an example to sort the result in descending order by NAME.

1. SELECT * FROM CUSTOMERS

2. ORDER BY NAME DESC;

This would produce the following result.

ID-NAME-AGE-ADDRESS-SALARY

2-Shiva tiwari-22-Bhopal-21000

4-Ritesh yadav-36-Azamgarh-26000

6-Robert -26-Mathura-22000

1-Himani gupta-21-Modinagar-22000

5-Balwant singh-45-Varanasi-36000

3-James bhargav-45-Meerut-65000

SQL ORDER BY RANDOM

If you want the resulting record to be ordered randomly, you should use the following codes according to several databases.

Here a question occurs that what is the need of fetching a random record or a row from a database?

Sometimes you may want to display random information like articles, links, pages etc. to your user.

If you want to fetch random rows from any of the databases you have to use some queries which are altered according to the databases.

Select a random row with MySQL:

If you want to return a random row with MY SQL, Use the following code:

1. SELECT column FROM table

2. ORDER BY RAND ()

3. LIMIT 1

Select a random row with Postgre SQL:

1. SELECT column FROM table

2. ORDER BY RANDOM ()

3. LIMIT 1

Select a random row with SQL Server:

1. SELECT TOP 1 column FROM table

2. ORDER BY NEWID ()

Select a random row with oracle:

1. SELECT column FROM

2. (SELECT column FROM table

3. ORDER BY dbms_random.value)

4. WHERE rownum = 1

Select a random row with IBM DB2:

1. SELECT column RAND () as IDX

2. FROM table

3. ORDER BY  IDX FETCH FIRST 1 ROWS ONLY

SQL ORDER BY LIMIT

We can retrieve limited rows from the database. I can be used in pagination where are forced to show only limited records like 10, 50, 100 etc.

LIMIT CLAUSE FOR ORACLE SQL:

If you want to use LIMIT clause with SQL, you have to use ROWNUM queries because it is used after result are selected.

You should use the following code:

1. SELECT name, age

2. FROM

3. (SELECT name, age, ROWNUM r

4. FROM

5. (SELECT name, age, FROM employee_data

6. ORDER BY age DESC

7. )

8. WHERE ROWNUM <=40

9. )

10. WHERE r >= 21;

This query will give you 21th to 40th rows.

SQL SORTING ON MULTIPLE COLUMNS

Let's take an example of customer table which has many columns, the following SQL statement selects all customers from the table named "customer", stored by the "country" and "Customer-Name" columns:

1. SELECT * FROM customers

2. ORDER BY country, Customer-Name;

SQL Insert

SQL INSERT STATEMENT

SQL INSERT statement is a SQL query. It is used to insert a single or a multiple records in a table.

There are two ways to insert data in a table:

1. By SQL insert into statement

1. By specifying column names

2. Without specifying column names

2. By SQL insert into select statement

1) Inserting data directly into a table

You can insert a row in the table by using SQL INSERT INTO command. But there are 2 ways to do this.

You can specify or ignore the column names while using INSERT INTO statement.

To insert partial column values, you must have to specify the column names. But if you want to insert all the column values, you can specify or ignore the column names.

If you specify the column names, syntax of the insert into statement will be as follows:

1. INSERT INTO TABLE_NAME

2. [(col1, col2, col3,.... col N)]

3. VALUES (value1, value2, value 3, .... Value N);

Here col1, col2, col3, .... colN are the columns of the table in which you want to insert data.

Note: At the time of inserting a row into table, if you add values for all columns then there is no need to specify the column name in SQL INSERT query. Moreover, you must be sure that you are entering the values in the same order as the columns exist.

But, If you ignore the column names, syntax of the insert into statement will be as follows:

1. INSERT INTO TABLE_NAME

2. VALUES (value1, value2, value 3, .... Value N);

2) Inserting data through SELECT Statement

SQL INSERT INTO SELECT Syntax

1. INSERT INTO table_name

2. [(column1, column2, .... column)]

3. SELECT column1, column2, .... Column N

4. FROM table_name [WHERE condition];

Note: when you add a new row, you should make sure that data type of the value and the column should be matched.

If any integrity constraints are defined for the table, you must follow them.

SQL INSERT INTO VALUE

There are two ways to insert values in a table.

In the first method there is no need to specify the column name where the data will be inserted, you need only their values.

1. INSERT INTO table_name

2. VALUES (value1, value2, value3....);

The second method specifies both the column name and values which you want to insert.

1. INSERT INTO table_name (column1, column2, column3....)

2. VALUES (value1, value2, value3.....);

Let's take an example of table which has five records within it.

1. INSERT INTO STUDENTS (ROLL_NO, NAME, AGE, CITY)

2. VALUES (1, ABHIRAM, 22, ALLAHABAD);

3. INSERT INTO STUDENTS (ROLL_NO, NAME, AGE, CITY)

4. VALUES (2, ALKA, 20, GHAZIABAD);

5. INSERT INTO STUDENTS (ROLL_NO, NAME, AGE, CITY)

6. VALUES (3, DISHA, 21, VARANASI);

7. INSERT INTO STUDENTS (ROLL_NO, NAME, AGE, CITY)

8. VALUES (4, ESHA, 21, DELHI);

9. INSERT INTO STUDENTS (ROLL_NO, NAME, AGE, CITY)

10. VALUES (5, MANMEET, 23, JALANDHAR);

It will show the following table as the final result.

ROLL_NO-NAME-AGE-CITY

1-ABHIRAM-22-ALLAHABAD

2-ALKA-20-GHAZIABAD

3-DISHA-21-VARANASI

4-ESHA-21-DELHI

5-MANMEET-23-JALANDHAR

You can create a record in CUSTOMERS table by using this syntax also.

1. INSERT INTO CUSTOMERS

2. VALUES (6, PRATIK, 24, KANPUR);

The following table will be as follow:

ROLL_NO-NAME-AGE-CITY

1-ABHIRAM-22-ALLAHABAD

2-ALKA-20-GHAZIABAD

3-DISHA-21-VARANASI

4-ESHA-21-DELHI

5-MANMEET-23-JALANDHAR

6-PRATIK-24-KANPUR

SQL INSERT INTO SELECT

We know how to insert a single row or an individual data in a table at a time, but if you want to insert multiple rows in a table. In addition to INSERT INTO you will combine it with the select statement.

In this command, you are using information from another table.

Let's see the Syntax for sql insert into select:

1. INSERT INTO "table 1" ("column1", "column2",....)

2. SELECT "column3", "column4",....

3. FROM "table2";

The INSERT INTO statement can also contain many clauses like SELECT, GROUP BY, HAVING as well as JOIN and ALIAS. So the insert into select statement may be complicated some times.

Let's take an example:

There is a table, named sales_data in a database name departmental store while table store_information contains the record of those data which are sold from the store per day. If you want to move data from sales_data to store_information

You should use the following syntax:

1. INSERT INTO store (store_name, sales, transaction_date)

2. SELECT store_name, sum (sales), transaction_date

3. FROM sales_information

4. GROUP BY store_name, transaction_date;

SQL INSERT MULTIPLE ROWS

Many times developers ask that is it possible to insert multiple rows into a single table in a single statement. Currently developers have to write multiple insert statement when they insert values in a table. It is not only boring, also time consuming. To get rid from this you should try this syntax. Actually there are three different methods to insert multiple values into a single table.

1. Traditional method (simple insert)

2. SQL insert select

3. SQL server 2008+ Row Construction

Insert multiple values in SQL server

1. CREATE TABLE student (ID INT VALUE VARCHAR (100));

1. SQL INSERT: (TRADITIONAL INSERT)

1. INSERT INTO student (ID, NAME)

2. VALUES (1, 'ARMAAN');

3. INSERT INTO student (ID, NAME)

4. VALUES (2, 'BILLY');

5. INSERT INTO student (ID, NAME)

6. VALUES (3, 'CHARLIE');

TO CLEAN-UP:

1. TRUNCATE TABLE student;

2. INSERT SELECT: (SELECT UNION INSERT)

1. INSERT INTO student (ID, NAME)

2. SELECT 1, 'ARMAAN'

3. UNION ALL

4. SELECT 2, 'BILLY'

5. UNION ALL

6. SELECT 3, 'CHARLIE';

7. TO CLEAN-UP:

8. TRUNCATE TABLE student;

3.SQL Server 2008+ Row Construction

1. INSERT INTO student (ID, NAME)

2. VALUES (1, 'ARMAAN'), (2, 'BILLY'), (3, 'CHARLIE');

3. TO CLEAN-UP:

4. DROP TABLE student;

SQL Update

The SQL commands (UPDATE and DELETE) are used to modify the data that is already in the database. The SQL DELETE command uses a WHERE clause.

SQL UPDATE statement is used to change the data of the records held by tables. Which rows is to be update, it is decided by a condition. To specify condition, we use WHERE clause.

The UPDATE statement can be written in following form:

1. UPDATE table_name SET [column_name1= value1,... column_nameN = valueN] [WHERE condition]

Let's see the Syntax:

1. UPDATE table_name

2. SET column_name = expression

3. WHERE conditions

Let's take an example: here we are going to update an entry in the source table.

SQL statement:

1. UPDATE students

2. SET User_Name = 'beinghuman'

3. WHERE Student_Id = '3'

Source Table:

Student_Id-FirstName-LastName-User_Name

1-Ada-Sharma-sharmili

2-Rahul-Maurya-sofamous

3-James-Walker-jonny

See the result after updating value:

Student_Id-FirstName-LastName-User_Name

1-Ada-Sharma-sharmili

2-Rahul-Maurya-sofamous

3-James-Walker-beinghuman

Updating Multiple Fields:

If you are going to update multiple fields, you should separate each field assignment with a comma.

SQL UPDATE statement for multiple fields:

1. UPDATE students

2. SET User_Name = 'beserious', First_Name = 'Johnny'

3. WHERE Student_Id = '3'

Result of the table is given below:

Student_Id-FirstName-LastName-User_Name

1-Ada-Sharma-sharmili

2-Rahul-Maurya-sofamous

3-Johnny-Walker-beserious

MYSQL SYNTAX FOR UPDATING TABLE:

1. UPDATE table_name

2. SET field1 = new-value1, field2 = new-value2,

3. [WHERE CLAUSE]

SQL UPDATE SELECT:

SQL UPDATE WITH SELECT QUERY:

We can use SELECT statement to update records through UPDATE statement.

SYNTAX:

1. UPDATE tableDestination

2. SET tableDestination.col = value

3. WHERE EXISTS (

4. SELECT col2.value

5. FROM  tblSource

6. WHERE tblSource.join_col = tblDestination. Join_col

7. AND  tblSource.Constraint = value)

You can also try this one -

1. UPDATE

2. Table

3. SET

4. Table.column1 = othertable.column 1,

5. Table.column2 = othertable.column 2

6. FROM

7. Table

8. INNER JOIN

9. Other_table

10. ON

11. Table.id = other_table.id

My SQL SYNTAX:

If you want to UPDATE with SELECT in My SQL, you can use this syntax:

Let's take an example having two tables. Here,

First table contains -

Cat_id, cat_name,

And the second table contains -

Rel_cat_id, rel_cat_name

SQL UPDATE COLUMN:

We can update a single or multiple columns in SQL with SQL UPDATE query.

SQL UPDATE EXAMPLE WITH UPDATING SINGLE COLUMN:

1. UPDATE students

2. SET student_id = 001

3. WHERE student_name = 'JAMES';

This SQL UPDATE example would update the student_id to '001' in the student table where student_name is 'JAMES'.

SQL UPDATE EXAMPLE WITH UPDATING MULTIPLE COLUMNS:

To update more than one column with a single update statement:

1. UPDATE students

2. SET student_name = 'JAMES',

3. Religion = 'HINDU'

4. WHERE student_name = 'RAJU';

This SQL UPDATE statement will change the student name to 'JAMES' and religion to 'HINDU' where the student name is 'RAJU'.

SQL UPDATE with JOIN

SQL UPDATE JOIN means we will update one table using another table and join condition.

Let us take an example of a customer table. I have updated customer table that contains latest customer details from another source system. I want to update the customer table with latest data. In such case, I will perform join between target table and source table using join on customer ID.

Let's see the syntax of SQL UPDATE query with JOIN statement.

1. UPDATE customer_table

2. INNER JOIN

3. Customer_table

4. ON customer_table.rel_cust_name = customer_table.cust_id

5. SET customer_table.rel_cust_name = customer_table.cust_name

How to use multiple tables in SQL UPDATE statement with JOIN

Let's take two tables, table 1 and table 2.

Create table1

1. CREATE TABLE table1 (column1 INT, column2 INT, column3 VARCHAR (100))

2. INSERT INTO table1 (col1, col2, col3)

3. SELECT 1, 11, 'FIRST'

4. UNION ALL

5. SELECT 11,12, 'SECOND'

6. UNION ALL

7. SELECT 21, 13, 'THIRD'

8. UNION ALL

9. SELECT 31, 14, 'FOURTH'

Create table2

1. CREATE TABLE table2 (column1 INT, column2 INT, column3 VARCHAR (100))

2. INSERT INTO table2 (col1, col2, col3)

3. SELECT 1, 21, 'TWO-ONE'

4. UNION ALL

5. SELECT 11, 22, 'TWO-TWO'

6. UNION ALL

7. SELECT 21, 23, 'TWO-THREE'

8. UNION ALL

9. SELECT 31, 24, 'TWO-FOUR'

Now check the content in the table.

1. SELECT * FROM table_1

1. SELECT * FROM table_2

-Col 1-Col 2-Col 3

1-1-11-First

2-11-12-Second

3-21-13-Third

4-31-14-Fourth

-Col 1-Col 2-Col 3

1-1-21-Two-One

2-11-22-Two-Two

3-21-23-Two-Three

4-31-24-Two-Four

Our requirement is that we have table 2 which has two rows where Col 1 is 21 and 31. We want to update the value from table 2 to table 1 for the rows where Col 1 is 21 and 31.

We want to also update the values of Col 2 and Col 3 only.

The most easiest and common way is to use join clause in the update statement and use multiple tables in the update statement.

1. UPDATE table 1

2. SET Col 2 = t2.Col2,

3. Col 3 = t2.Col3

4. FROM table1 t1

5. INNER JOIN table 2 t2 ON t1.Col1 = t2.col1

6. WHERE t1.Col1 IN (21,31)

Check the content of the table

SELECT FROM table 1

SELECT FROM table 2

-Col 1-Col 2-Col 3

1-1-11-First

2-11-12-Second

3-21-23-Two-Three

4-31-24-Two-Four

-Col 1-Col 2-Col 3

1-1-21-First

2-11-22-Second

3-21-23-Two-Three

4-31-24-Two-Four

Here we can see that using join clause in update statement. We have merged two tables by the use of join clause.

SQL UPDATE DATE

How to update a date and time field in SQL?

If you want to update a date & time field in SQL, you should use the following query.

let's see the syntax of sql update date.

1. UPDATE table

2. SET Column_Name = 'YYYY-MM-DD HH:MM:SS'

3. WHERE Id = value

Let us check this by an example:

Firstly we take a table in which we want to update date and time fields.

If you want to change the first row which id is 1 then you should write the following syntax:

1. UPDATE table

2. SET EndDate = '2014-03-16 00:00:00.000'

3. WHERE Id = 1

Note: you should always remember that SQL must attach default 00:00:00.000 automatically.

This query will change the date and time field of the first row in that above assumed table.

SQL Delete

The SQL DELETE statement is used to delete rows from a table. Generally DELETE statement removes one or more records from a table.

SQL DELETE Syntax

Let's see the Syntax for the SQL DELETE statement:

1. DELETE FROM table_name [WHERE condition];

Here table_name is the table which has to be deleted. The WHERE clause in SQL DELETE statement is optional here.

SQL DELETE Example

Let us take a table, named "EMPLOYEE" table.

ID-EMP_NAME-CITY-SALARY

101-Adarsh Singh-Obra-20000

102-Sanjay Singh-Meerut-21000

103-Priyanka Sharma-Raipur-25000

104-Esha Singhal-Delhi-26000

Example of delete with WHERE clause is given below:

1. DELETE FROM EMPLOYEE WHERE ID=101;

Resulting table after the query:

ID-EMP_NAME-CITY-SALARY

102-Sanjay Singh-Meerut-21000

103-Priyanka Sharma-Raipur-25000

104-Esha Singhal-Delhi-26000

Another example of delete statement is given below

1. DELETE FROM EMPLOYEE;

Resulting table after the query:

ID-EMP_NAME-CITY-SALARY

It will delete all the records of EMPLOYEE table.

It will delete the all the records of EMPLOYEE table where ID is 101.

The WHERE clause in the SQL DELETE statement is optional and it identifies the rows in the column that gets deleted.

WHERE clause is used to prevent the deletion of all the rows in the table, If you don't use the WHERE clause you might loss all the rows.

Invalid DELETE Statement for ORACLE database

You cannot use * (asterisk) symbol to delete all the records.

1. DELETE * FROM EMPLOYEE;

SQL DELETE TABLE

The DELETE statement is used to delete rows from a table. If you want to remove a specific row from a table you should use WHERE condition.

1. DELETE FROM table_name [WHERE condition];

But if you do not specify the WHERE condition it will remove all the rows from the table.

1. DELETE FROM table_name;

There are some more terms similar to DELETE statement like as DROP statement and TRUNCATE statement but they are not exactly same there are some differences between them.

Difference between DELETE and TRUNCATE statements

There is a slight difference b/w delete and truncate statement. The DELETE statement only deletes the rows from the table based on the condition defined by WHERE clause or delete all the rows from the table when condition is not specified.

But it does not free the space containing by the table.

The TRUNCATE statement: it is used to delete all the rows from the table and free the containing space.

Let's see an "employee" table.

Emp_id-Name-Address-Salary

1-Michael-Allahabad-22000

2-Shurabhi-Varanasi-13000

3-Pappu-Delhi-24000

Execute the following query to truncate the table:

1. TRUNCATE TABLE employee;

Difference b/w DROP and TRUNCATE statements

When you use the drop statement it deletes the table's row together with the table's definition so all the relationships of that table with other tables will no longer be valid.

When you drop a table:

- Table structure will be dropped

- Relationship will be dropped

- Integrity constraints will be dropped

- Access privileges will also be dropped

On the other hand when we TRUNCATE a table, the table structure remains the same, so you will not face any of the above problems.

SQL DELETE ROW

Let us take an example of student.

Original table:

ID-STUDENT _NAME-ADDRESS

001-JAMES MAURYA-GHAZIABAD

002-RAJA KHAN-LUCKNOW

003-RAVI MALIK-DELHI

If you want to delete a student with id 003 from the student_name table, then the SQL DELETE query should be like this:

1. DELETE FROM student_name

2. WHERE id = 003;

Resulting table after SQL DELETE query:

ID-STUDENT_NAME-ADDRESS

001-JAMES MAURYA-GHAZIABAD

002-RAJA KHAN-LUCKNOW

SQL DELETE ALL ROWS

The statement SQL DELETE ALL ROWS is used to delete all rows from the table. If you want to delete all the rows from student table the query would be like,

1. DELETE FROM STUDENT_NAME;

Resulting table after using this query:

ID-STUDENT_NAME-ADDRESS

SQL DELETE DUPLICATE ROWS

If you have got a situation that you have multiple duplicate records in a table, so at the time of fetching records from the table you should be more careful. You make sure that you are fetching unique records instead of fetching duplicate records.

To overcome with this problem we use DISTINCT keyword.

It is used along with SELECT statement to eliminate all duplicate records and fetching only unique records.

SYNTAX:

The basic syntax to eliminate duplicate records from a table is:

1. SELECT DISTINCT column1, column2,....columnN

2. FROM table _name

3. WHERE [conditions]

EXAMPLE:

Let us take an example of STUDENT table.

ROLL_NO-NAME-PERCENTAGE-ADDRESS

1-JAMES MAURYA-72.8-ALLAHABAD

2-CHANDAN SHARMA-63.5-MATHURA

3-DIVYA AGRAWAL-72.3-VARANASI

4-RAJAT KUMAR-72.3-DELHI

5-RAVI TYAGI-75.5-HAPUR

6-SONU JAISWAL-71.2-GHAZIABAD

Firstly we should check the SELECT query and see how it returns the duplicate percentage records.

1. SQL > SELECT PERCENTAGE FROM STUDENTS

2. ORDER BY PERCENTAGE;

PERCENTAGE

63.5

71.2

72.3

72.3

72.8

75.5

Now let us use SELECT query with DISTINCT keyword and see the result. This will eliminate the duplicate entry.

1. SQL > SELECT DISTINCT PERCENTAGE FROM STUDENTS

2. ORDER BY PERCENTAGE;

PERCENTAGE

63.5

71.2

72.3

72.8

75.5

SQL DELETE DATABASE

You can easily remove or delete indexes, tables and databases with the DROP statement.

The DROP index statement is:

Used to delete index in the table

DROP INDEX SYNTAX for MS Access:

1. DROP INDEX index_name ON table_name

DROP INDEX SYNTAX for MS SQL Server:

1. DROP INDEX table_name.index_name

DROP INDEX syntax for DB2/Oracle:

1. DROP INDEX index_name

DROP INDEX syntax for MySQL:

1. ALTER TABLE table_name DROP INDEX index_name

DROP DATABASE Statement:

The drop database statement is used to delete a database.

1. DROP DATABASE database_name

Note:

We should always note that in RDBMS, database name should be unique.

We should always remember that DROP database command may be the cause of loss of all information so we should always be careful while doing this operation.

SQL DELETE VIEW

Before knowing about what is SQL delete view, it is important to know -

What is SQL view?

A view is a result set of a stored query on the data.

The SQL view is a table which does not physically exist. It is only a virtual table.

SQL VIEW can be created by a SQL query by joining one or more table.

Syntax for SQL create view -

1. CREATE VIEW view_name AS

2. SELECT columns

3. FROM tables

4. WHERE conditions;

If you want to delete a SQL view, It is done by SQL DROP command you should use the following syntax:

SQL DROP VIEW syntax:

1. DROP VIEW view_name

Why use the SQL DROP VIEW statement?

When a view no longer useful you may drop the view permanently. Also if a view needs change within it, it would be dropped and then created again with changes in appropriate places.

SQL DELETE JOIN

This is very commonly asked question that how to delete or update rows using join clause

It is not a very easy process, sometimes, we need to update or delete records on the basis of complex WHERE clauses.

There are three tables which we use to operate on SQL syntax for DELETE JOIN.

These tables are table1, table2 and target table.

SQL Syntax for delete JOIN

1. DELETE [target table]

2. FROM    [table1]

3. INNER JOIN [table2]

4. ON [table1.[joining column] = [table2].[joining column]

5. WHERE   [condition]

Syntax for update

1. UPDATE [target table]

2. SET [target column] = [new value]

3. FROM    [table1]

4. INNER JOIN [table2]

5. ON [table1.[joining column] = [table2].[joining column]

6. WHERE   [condition]

SQL JOIN

As the name shows, JOIN means to combine something. In case of SQL, JOIN means "to combine two or more tables".

The SQL JOIN clause takes records from two or more tables in a database and combines it together.

ANSI standard SQL defines five types of JOIN :

1. inner join,

2. left outer join,

3. right outer join,

4. full outer join, and

5. cross join.

In the process of joining, rows of both tables are combined in a single table.

Why SQL JOIN is used?

If you want to access more than one table through a select statement.

If you want to combine two or more table then SQL JOIN statement is used .it combines rows of that tables in one table and one can retrieve the information by a SELECT statement.

The joining of two or more tables is based on common field between them.

SQL INNER JOIN also known as simple join is the most common type of join.

How to use SQL join or SQL Inner Join?

Let an example to deploy SQL JOIN process:

1.Staff table

ID-Staff_NAME-Staff_AGE-STAFF_ADDRESS-Monthley_Package

1-MICHAEL-22-MUMBAI-18000

2-SUSHIL-32-DELHI-20000

3-MONTY-25-MOHALI-22000

4-AMIT-20-ALLAHABAD-12000

2.Payment table

Payment_ID-DATE-Staff_ID-AMOUNT

101-30/12/2009-1-3000.00

102-22/02/2010-3-2500.00

103-23/02/2010-4-3500.00

So if you follow this JOIN statement to join these two tables ?

1. SELECT Staff_ID, Staff_NAME, Staff_AGE, AMOUNT

2. FROM STAFF s, PAYMENT p

3. WHERE s.ID =p.STAFF_ID;

This will produce the result like this:

STAFF_ID-NAME-Staff_AGE-AMOUNT

3-MONTY-25-2500

1-MICHAEL-22-3000

4-AMIT-25-3500

1-MICHAEL-22-3000

SQL OUTER JOIN

In the SQL outer JOIN all the content of the both tables are integrated together either they are matched or not.

If you take an example of employee table

Outer join of two types:

1.Left outer join (also known as left join): this join returns all the rows from left table combine with the matching rows of the right table. If you get no matching in the right table it returns NULL values.

2.Right outer join (also known as right join): this join returns all the rows from right table are combined with the matching rows of left table .If you get no column matching in the left table .it returns null value.

This diagram shows the different type of joins:

SQL LEFT JOIN

The SQL left join returns all the values from the left table and it also includes matching values from right table, if there are no matching join value it returns NULL.

BASIC SYNTAX FOR LEFT JOIN:

1. SELECT table1.column1, table2.column2....

2. FROM table1

3. LEFTJOIN table2

4. ON table1.column_field = table2.column_field;

let us take two tables in this example to elaborate all the things:

CUSTOMER TABLE:

ID-NAME-AGE-SALARY

1-MICHAEL-51-56000

2-AROHI-21-25000

3-VINEET-24-31000

4-JAMES-23-32000

5-RAVI-23-42000

This is second table

ORDER TABLE:

O_ID-DATE-CUSTOMER_ID-AMOUNT

001-20-01-2012-2-3000

002-12-02-2012-2-2000

003-22-03-2012-3-4000

004-11-04-2012-4-5000

join these two tables with LEFT JOIN:

1. SQL SELECT ID, NAME, AMOUNT,DATE

2. FROM CUSTOMER

3. LEFT JOIN ORDER

4. ON CUSTOMER.ID = ORDER.CUSTOMER_ID;

This will produce the following result:

ID-NAME-AMOUNT-DATE

1-MICHAEL-NULL-NULL

2-AROHI-3000-20-01-2012

2-AROHI-2000-12-02-2012

3-VINEET-4000-22-03-2012

4-JAMES-5000-11-04-2012

5-RAVI-NULL-NULL

SQL RIGHT JOIN

The SQL right join returns all the values from the rows of right table. It also includes the matched values from left table but if there is no matching in both tables, it returns NULL.

Basic syntax for right join:

1. SELECT table1.column1, table2.column2.....

2. FROM table1

3. RIGHT JOIN table2

4. ON table1.column_field = table2.column_field;

let us take an example with 2 tables table1 is CUSTOMERS table and table2 is ORDERS table.

CUSTOMER TABLE:

ID-NAME-AGE-SALARY

1-MICHAEL-51-56000

2-AROHI-21-25000

3-VINEET-24-31000

4-JAMES-23-32000

5-RAVI-23-42000

and this is the second table:

ORDER TABLE:

DATE-O_ID-CUSTOMER_ID-AMOUNT

20-01-2012-001-2-3000

12-02-2012-002-2-2000

22-03-2012-003-3-4000

11-04-2012-004-4-5000

Here we will join these two tables with SQL RIGHT JOIN:

1. SQL> SELECT ID,NAME,AMOUNT,DATE

2. FROM CUSTOMER

3. RIGHT JOIN ORDER

4. ON CUSTOMER.ID = ORDER.CUSTOMER_ID;

ID-NAME-AMOUNT-DATE

2-AROHI-3000-20-01-2012

2-AROHI-2000-12-02-2012

3-VINEET-4000-22-03-2012

4-JAMES-5000-11-04-2012

SQL FULL JOIN

The SQL full join is the result of combination of both left and right outer join and the join tables have all the records from both tables. It puts NULL on the place of matches not found.

SQL full outer join and SQL join are same. generally it is known as SQL FULL JOIN.

SQL full outer join:

What is SQL full outer join?

SQL full outer join is used to combine the result of both left and right outer join and returns all rows (don't care its matched or unmatched) from the both participating tables.

Syntax for full outer join:

1. SELECT *

2. FROM table1

3. FULL OUTER JOIN table2

4. ON table1.column_name = table2.column_name;

Note:here table1 and table2 are the name of the tables participating in joining and column_name is the column of the participating tables.

Let us take two tables to demonstrate full outer join:

table_A

A-M

1-m

2-n

4-o

table_B

A-N

2-p

3-q

5-r

Resulting table

A-M-A-N

2-n-2-p

1-m----

4-o----

----3-q

----5-r

Because this is a full outer join so all rows (both matching and non-matching) from both tables are included in the output. Here only one row of output displays values in all columns because there is only one match between table_A and table_B.

SQL Cross Join

When each row of first table is combined with each row from the second table, known as Cartesian join or cross join. In general words we can say that SQL CROSS JOIN returns the Cartesian product of the sets of rows from the joined table.

We can specify a CROSS JOIN in two ways:

1. Using the JOIN syntax.

2. the table in the FROM clause without using a WHERE clause.

SYNTAX of SQL Cross Join

1. SELECT * FROM [TABLE1] CROSS JOIN [TABLE2]

2. OR

3. SELECT * FROM [ TABLE1] , [TABLE2]

Let us take an example of two tables,

Table1 - MatchScore

Player-Department_id-Goals

Franklin-1-2

Alan-1-3

Priyanka-2-2

Rajesh-3-5

Table2 - Departments

Department_id-Department_name

1-IT

2-HR

3-Marketing

SQL Statement:

1. SELECT * FROM MatchScore CROSS JOIN Departments

After executing this query , you will find the following result:

Player-Department_id-Goals-Depatment_id-Department_name

Franklin-1-2-1-IT

Alan-1-3-1-IT

Priyanka-2-2-1-IT

Rajesh-3-5-1-IT

Franklin-1-2-2-HR

Alan-1-3-2-HR

Priyanka-2-2-2-HR

Rajesh-3-5-2-HR

Franklin-1-2-3-Marketing

Alan-1-3-3-Marketing

Priyanka-2-2-3-Marketing

Rajesh-3-5-3-Marketing

SQL Keys

SQL PRIMARY KEY

A column or columns is called primary key (PK) that uniquely identifies each row in the table.

If you want to create a primary key, you should define a PRIMARY KEY constraint when you create or modify a table.

When multiple columns are used as a primary key, it is known as composite primary key.

In designing the composite primary key, you should use as few columns as possible. It is good for storage and performance both, the more columns you use for primary key the more storage space you require.

Inn terms of performance, less data means the database can process faster.

Points to remember for primary key:

- Primary key enforces the entity integrity of the table.

- Primary key always has unique data.

- A primary key length cannot be exceeded than 900 bytes.

- A primary key cannot have null value.

- There can be no duplicate value for a primary key.

- A table can contain only one primary key constraint.

When we specify a primary key constraint for a table, database engine automatically creates a unique index for the primary key column.

Main advantage of primary key:

The main advantage of this uniqueness is that we get fast access.

In oracle, it is not allowed for a primary key to contain more than 32 columns.

SQL primary key for one column:

The following SQL command creates a PRIMARY KEY on the "S_Id" column when the "students" table is created.

MySQL:

1. CREATE TABLE students

2. (

3. S_Id int NOT NULL,

4. LastName varchar (255) NOT NULL,

5. FirstName varchar (255),

6. Address varchar (255),

7. City varchar (255),

8. PRIMARY KEY (S_Id)

9. )

SQL Server, Oracle, MS Access:

1. CREATE TABLE students

2. (

3. S_Id int NOT NULL PRIMARY KEY,

4. LastName varchar (255) NOT NULL,

5. FirstName varchar (255),

6. Address varchar (255),

7. City varchar (255),

8. )

SQL primary key for multiple columns:

MySQL, SQL Server, Oracle, MS Access:

1. CREATE TABLE students

2. (

3. S_Id int NOT NULL,

4. LastName varchar (255) NOT NULL,

5. FirstName varchar (255),

6. Address varchar (255),

7. City varchar (255),

8. CONSTRAINT pk_StudentID PRIMARY KEY (S_Id, LastName)

9. )

Note:you should note that in the above example there is only one PRIMARY KEY (pk_StudentID). However it is made up of two columns (S_Id and LastName).

SQL primary key on ALTER TABLE

When table is already created and you want to create a PRIMARY KEY constraint on the "S_Id" column you should use the following SQL:

Primary key on one column:

1. ALTER TABLE students

2. ADD PRIMARY KEY (S_Id)

Primary key on multiple column:

1. ALTER TABLE students

2. ADD CONSTRAINT pk_StudentID PRIMARY KEY (S_Id,LastName)

When you use ALTER TABLE statement to add a primary key, the primary key columns must not contain NULL values (when the table was first created).

How to DROP a PRIMARY KEY constraint?

If you want to DROP (remove) a primary key constraint, you should use following syntax:

MySQL:

1. ALTER TABLE students

2. DROP PRIMARY KEY

SQL Server / Oracle / MS Access:

1. ALTER TABLE students

2. DROP CONSTRAINT pk_StudentID

SQL FOREIGN KEY

In the relational databases, a foreign key is a field or a column that is used to establish a link between two tables.

In simple words you can say that, a foreign key in one table used to point primary key in another table.

Let us take an example to explain it:

Here are two tables first one is students table and second is orders table.

Here orders are given by students.

First table:

S_Id-LastName-FirstName-CITY

1-MAURYA-JAMES-ALLAHABAD

2-JAISWAL-DAVID-GHAZIABAD

3-ARORA-SAUMYA-MODINAGAR

Second table:

O_Id-OrderNo-S_Id

1-99586465-2

2-78466588-2

3-22354846-3

4-57698656-1

Here you see that "S_Id" column in the "Orders" table points to the "S_Id" column in "Students" table.

- The "S_Id" column in the "Students" table is the PRIMARY KEY in the "Students" table.

- The "S_Id" column in the "Orders" table is a FOREIGN KEY in the "Orders" table.

The foreign key constraint is generally prevents action that destroy links between tables.

It also prevents invalid data to enter in foreign key column.

SQL FOREIGN KEY constraint ON CREATE TABLE:

(Defining a foreign key constraint on single column)

To create a foreign key on the "S_Id" column when the "Orders" table is created:

MySQL:

1. CREATE TABLE orders

2. (

3. O_Id int NOT NULL,

4. Order_No  int NOT NULL,

5. S_Id int,

6. PRIMAY KEY (O_Id),

7. FOREIGN KEY (S_Id) REFERENCES Persons (S_Id)

8. )

SQL Server /Oracle / MS Access:

1. CREATE TABLE Orders

2. (

3. O_Id int NOT NULL PRIMAY KEY,

4. Order_No int NOT NULL,

5. S_Id int FOREIGN KEY REFERENCES persons (S_Id)

6. )

SQL FOREIGN KEY constraint for ALTER TABLE:

If the Order table is already created and you want to create a FOREIGN KEY constraint on the "S_Id" column, you should write the following syntax:

Defining a foreign key constraint on single column:

MySQL / SQL Server / Oracle / MS Access:

1. ALTER TABLE Orders

2. ADD CONSTRAINT fk_PerOrders

3. FOREIGN KEY(S_Id)

4. REFERENCES Students (S_Id)

DROP SYNTAX for FOREIGN KEY COSTRAINT:

If you want to drop a FOREIGN KEY constraint, use the following syntax:

MySQL:

1. ALTER TABLE Orders

2. ROP FOREIGN KEY fk_PerOrders

SQL Server / Oracle / MS Access:

1. ALTER TABLE Orders

2. DROP CONSTRAINT fk_PerOrders

Difference between primary key and foreign key in SQL:

These are some important difference between primary key and foreign key in SQL-

Primary key cannot be null on the other hand foreign key can be null.

Primary key is always unique while foreign key can be duplicated.

Primary key uniquely identify a record in a table while foreign key is a field in a table that is primary key in another table.

There is only one primary key in the table on the other hand we can have more than one foreign key in the table.

By default primary key adds a clustered index on the other hand foreign key does not automatically create an index, clustered or non-clustered. You must manually create an index for foreign key.

SQL Composite Key

A composite key is a combination of two or more columns in a table that can be used to uniquely identify each row in the table when the columns are combined uniqueness is guaranteed, but when it taken individually it does not guarantee uniqueness.

Sometimes more than one attributes are needed to uniquely identify an entity. A primary key that is made by the combination of more than one attribute is known as a composite key.

In other words we can say that:

Composite key is a key which is the combination of more than one field or column of a given table. It may be a candidate key or primary key.

Columns that make up the composite key can be of different data types.

SQL Syntax to specify composite key:

1. CREATE TABLE TABLE_NAME

2. (COLUMN_1, DATA_TYPE_1,

3. COLUMN_2, DATA_TYPE_2,

4. ???

5. PRIMARY KEY (COLUMN_1, COLUMN_2, ...));

In all cases composite key created consist of COLUMN1 and COLUMN2.

MySQL:

1. CREATE TABLE SAMPLE_TABLE

2. (COL1 integer,

3. COL2 varchar(30),

4. COL3 varchar(50),

5. PRIMARY KEY (COL1, COL2));

MySQL:

1. CREATE TABLE SAMPLE_TABLE

2. (COL1 integer,

3. COL2 varchar(30),

4. COL3 varchar(50),

5. PRIMARY KEY (COL1, COL2));

Oracle:

1. CREATE TABLE SAMPLE_TABLE

2. CREATE TABLE SAMPLE_TABLE

3. (COL1 integer,

4. COL2 varchar(30),

5. COL3 varchar(50),

6. PRIMARY KEY (COL1, COL2));

SQL Server:

Let's see the Syntax for the select top statement:

1. CREATE TABLE SAMPLE_TABLE

2. (COL1 integer,

3. COL2 nvarchar(30),

4. COL3 nvarchar(50),

5. PRIMARY KEY (COL1, COL2));

Unique Key in SQL

A unique key is a set of one or more than one fields/columns of a table that uniquely identify a record in a database table.

You can say that it is little like primary key but it can accept only one null value and it cannot have duplicate values.

The unique key and primary key both provide a guarantee for uniqueness for a column or a set of columns.

There is an automatically defined unique key constraint within a primary key constraint.

There may be many unique key constraints for one table, but only one PRIMARY KEY constraint for one table.

SQL UNIQUE KEY constraint on CREATE TABLE:

If you want to create a UNIQUE constraint on the "S_Id" column when the "students" table is created, use the following SQL syntax:

SQL Server / Oracle / MS Access:

(Defining a unique key constraint on single column):

1. CREATE TABLE students

2. (

3. S_Id int NOT NULL UNIQUE,

4. LastName varchar (255) NOT NULL,

5. FirstName varchar (255),

6. City varchar (255)

7. )

MySQL:

1. CREATE TABLE students

2. CREATE TABLE students

3. (

4. S_Id int NOT NULL,

5. LastName varchar (255) NOT NULL,

6. FirstName varchar (255),

7. City varchar (255),

8. UNIQUE (S_Id)

9. )

(Defining a unique key constraint on multiple columns):

MySQL / SQL Server / Oracle / MS Access:

1. CREATE TABLE students

2. (

3. S_Id int NOT NULL,

4. LastName varchar (255) NOT NULL,

5. FirstName varchar (255),

6. City varchar (255),

7. CONSTRAINT uc_studentId UNIQUE (S_Id, LastName)

8. )

SQL UNIQUE KEY constraint on ALTER TABLE:

If you want to create a unique constraint on "S_Id" column when the table is already created, you should use the following SQL syntax:

(Defining a unique key constraint on single column):

MySQL / SQL Server / Oracle / MS Access:

1. ALTER TABLE students

2. ADD UNIQUE (S_Id)

(Defining a unique key constraint on multiple columns):

MySQL / SQL Server / Oracle / MS Access:

1. ALTER TABLE students

2. ADD CONSTRAINT uc_StudentId UNIQUE  (S_Id, LastName)

DROP SYNTAX FOR A FOREIGN KEY constraint:

If you want to drop a UNIQUE constraint, use the following SQL syntax:

MySQL:

1. ALTER TABLE students

2. DROP INDEX uc_studentID

SQL Server / Oracle / MS Access:

1. ALTER TABLE students

2. DROP CONSTRAINT uc_studentID

Alternate Key in SQL

Alternate key is a secondary key it can be simple to understand by an example:

Let's take an example of student it can contain NAME, ROLL NO., ID and CLASS.

Here ROLL NO. is primary key and rest of all columns like NAME, ID and CLASS are alternate keys.

If a table has more than one candidate key, one of them will become the primary key and rest of all are called alternate keys.

In simple words, you can say that any of the candidate key which is not part of primary key is called an alternate key. So when we talk about alternate key, the column may not be primary key but still it is a unique key in the column.

An alternate key is just a candidate key that has not been selected as the primary key.

SQL vs NoSQL

There are a lot of databases used today in the industry. Some are SQL databases, some are NoSQL databases. The conventional database is SQL database system that uses tabular relational model to represent data and their relationship. The NoSQL database is the newer one database that provides a mechanism for storage and retrieval of data other than tabular relations model used in relational databases.

Following is a list of differences between SQL and NoSQL database:

SQL Vs NoSQL

1)

Databases are categorized as Relational Database Management System (RDBMS).

NoSQL databases are categorized as Non-relational or distributed database system.

2)

SQL databases have fixed or static or predefined schema.

NoSQL databases have dynamic schema.

3)

SQL databases display data in form of tables so it is known as table-based database.

NoSQL databases display data as collection of key-value pair, documents, graph databases or wide-column stores.

4)

SQL databases are vertically scalable.

NoSQL databases are horizontally scalable.

5)

SQL databases use a powerful language "Structured Query Language" to define and manipulate the data.

In NoSQL databases, collection of documents are used to query the data. It is also called unstructured query language. It varies from database to database.

6)

SQL databases are best suited for complex queries.

NoSQL databases are not so good for complex queries because these are not as powerful as SQL queries.

7)

SQL databases are not best suited for hierarchical data storage.

NoSQL databases are best suited for hierarchical data storage.

8)

MySQL, Oracle, Sqlite, PostgreSQL and MS-SQL etc. are the example of SQL database.

MongoDB, BigTable, Redis, RavenDB, Cassandra, Hbase, Neo4j, CouchDB etc. are the example of nosql database

SQL Interview Questions

There is given sql interview questions and answers that has been asked in many companies. For PL/SQL interview questions, visit our next page.

1) What is SQL?

SQL stands for the Structured Query Language. SQL is a standard query language used for maintaining the relational database and perform many different operations of data manipulation on the data. SQL initially was invented in 1970. It is a database language used for database creation, deletion, fetching rows and modifying rows, etc. sometimes it is pronounced as 'sequel.'

2) When SQL appeared?

It appeared in 1974. SQL is one of the often used languages for maintaining the relational database. SQL. In 1986 SQL become the standard of American National Standards Institute (ANSI) and ISO(International Organization for Standardization) in 1987.

3) What are the usages of SQL?

- SQL is responsible for maintaining the relational data and the data structures present in the database.

- To execute queries against a database

- To retrieve data from a database

- To inserts records in a database

- To updates records in a database

- To delete records from a database

- To create new databases

- To create new tables in a database

- To create views in a database

- To perform complex operations on the database.

4) Does SQL support programming?

SQL refers to the Standard Query Language, which is not actually the programming language. SQL doesn't have a loop, Conditional statement, logical operations, it can not be used for anything other than data manipulation. It is used like commanding (Query) language to access databases. The primary purpose of SQL is to retrieve, manipulate, update and perform complex operations like joins on the data present in the database.

5) What are the subsets of SQL?

There is three significant subset of the SQL:

1. Data definition language (DDL):DDL is used to define the data structure it consists of the commands like CREATE, ALTER, DROP, etc.

2. Data manipulation language (DML):DML is used to manipulate already existing data in the database. The commands in this category are SELECT, UPDATE, INSERT, etc.

3. Data control language (DCL):DCL is used to control access to data in the database and includes commands such as GRANT, REVOKE.

6) What is a Data Definition Language?

Data definition language (DDL) is the subset of the database which defines the data structure of the database in the initial stage when the database is about to be created. It consists of the following commands: CREATE, ALTER and DELETE database objects such as schema, tables, view, sequence, etc.

7) What is a Data Manipulation Language?

Data manipulation language makes the user able to retrieve and manipulate data. It is used to perform the following operations.

- Insert data into database through INSERT command.

- Retrieve data from the database through SELECT command.

- Update data in the database through UPDATE command.

- Delete data from the database through DELETE command.

8) What is Data Control Language?

Data control language allows you to control access to the database. DCL is the only subset of the database which decides that what part of the database should be accessed by which user at what point of time. It includes two commands GRANT and REVOKE.

GRANT: to grant the specific user to perform a particular task

REVOKE: to cancel previously denied or granted permissions.

9) What are tables and fields in the database?

A table is a set of organized data. It has rows and columns. Rows here refers to the tuples which represent the simple data item and columns are the attribute of the data items present in particular row. Columns can categorize as vertical, and Rows are horizontal.

A table contains a specified number of the column called fields but can have any number of rows which is known as the record. So, the columns in the table of the database are known as the fields and they represent the attribute or characteristics of the entity in the record.

10) What is a primary key?

A primary key is a field or the combination of fields which uniquely specify a row. The Primary key is a special kind of unique key. Primary key values cannot be NULL. For example, the Social Security Number can be treated as the primary key for any individual.

11) What is a foreign key?

A foreign key is specified as a key which is related to the primary key of another table. A relationship needs to be created between two tables by referencing foreign key with the primary key of another table. Foreign key acts like a cross-reference between tables as it refers to the primary key of other table and the primary key-foreign key relationship is a very crucial relationship as it maintains the ACID properties of database sometimes.

12) What is a unique key?

Unique key constraint uniquely identifies each record in the database. This key provides uniqueness for the column or set of columns.

The Unique key cannot accept a duplicate value.

The unique key can accept only on Null value.

13) What is the difference between primary key and unique key?

Primary key and unique key both are the essential constraints of the SQL, but there is a small difference between them

Primary key carries unique value but the field of the primary key cannot be Null on the other hand unique key also carry unique value but it can have a single Null value field.

14) What is a Database?

A Database is an organized form of data. The database is the electronic system which makes data access, data manipulation, data retrieval, data storing and data management very easy and structured. Almost every organization uses the database for storing the data due to its easily accessible and high operational ease. The database provides perfect access to data and lets us perform required tasks.

The Database is also called a structured form of data. Due to this structured format, you can access data very easily.

15) What is DBMS?

DBMS stands for Database Management System. This is a program which is used to control them. It is like a File Manager that manages data in a database rather than saving it in file systems.

Database management system is an interface between the database and the user. It makes the data retrieval, data access easier.

Database management system is a software which provides us the power to perform operations such as creation, maintenance and use of a data of the database using a simple query in almost no time.

Without the database management system, it would be far more difficult for the user to access the data of the database.

16) What are the different types of database management systems?

There are four types of database:

- Hierarchical databases (DBMS)

- Relational databases (RDBMS)

- Network databases (IDMS)

- Object-oriented databases

RDBMS is one of the most often used databases due to its easy accessibility and supports regarding complex queries.

17) What is RDBMS?

RDBMS stands for Relational Database Management System. It is a database management system based on a relational model. RDBMS stores the data into the collection of tables and links those table using the relational operators easily whenever required. It facilitates you to manipulate the data stored in the tables by using relational operators. Examples of the relational database management system are Microsoft Access, MySQL, SQLServer, Oracle database, etc.

18) What is Normalization in a Database?

Normalization is used to minimize redundancy and dependency by organizing fields and table of a database.

There are some rules of database normalization which commonly known as Normal From and they are:

- First normal form(1NF)

- Second normal form(2NF)

- Third normal form(3NF)

- Boyce-Codd normal form(BCNF)

Using these steps, the redundancy, anomalies, inconsistency of the data in the database can be removed.

19) What is the primary use of Normalization?

Normalization is mainly used to add, delete or modify a field that can be made in a single table. The primary use of Normalization is to remove redundancy and to remove the insert, delete and update distractions. Normalization breaks the table into small partitions and then link them using different relationships so that it will avoid the chances of redundancy.

20) What are the disadvantages of not performing Database Normalization?

The major disadvantages are:

- The occurrence of redundant terms in the database which causes the waste of the space in the disk.

- Due to redundant terms inconsistency may also occur id any change will be made in the data of one table but not made in the same data of another table then inconsistency will take place, which will lead to the maintenance problem and effects the ACID properties as well.

21) What is an inconsistent dependency?

Inconsistent dependency refers to the difficulty of accessing particular data as the path to reach the data may be missing or broken. Inconsistent dependency will leads users to search the data in the wrong table which will afterward give the error as an output.

22) What is Denormalization in a Database?

Denormalization is used to access the data from higher or lower normal form of database. It also processes redundancy into a table by incorporating data from the related tables. Denormalization adds required redundant term into the tables so that we can avoid using complex joins and many other complex operations. Denormalization doesn?t mean that normalization will not be done, but the denormalization process takes place after the normalization process.

23) What are the types of operators available in SQL?

Operators are the special keywords or special characters reserved for performing particular operations and are used in the SQL queries. There is three type of operators used in SQL:

1. Arithmetic operators: addition (+), subtraction (-), multiplication (*), division (/), etc.

2. Logical operators: ALL, AND, ANY, ISNULL, EXISTS, BETWEEN, IN, LIKE, NOT, OR, UNIQUE.

3. Comparison operator: =, !=, <>, <, >, <=, >=, !<, !>

24) What is view in SQL?

A view is a virtual table which contains a subset of data within a table. Views are not originally present, and it takes less space to store. A view can have data from one or more tables combined, and it depends on the relationship. Views are used to apply security mechanism in the SQL Server. The view of the database is the searchable object we can use a query to search the view as we use for the table.

25) What is an Index in SQL?

SQL indexes are the medium of reducing the cost of the query as the high cost of the query will lead to the fall in the performance of the query. An index is used to increase the performance and allow faster retrieval of records from the table. Indexing reduces the number of data pages we need to visit to find a particular data page. Indexing also has a unique value that means that the index cannot be duplicated. An index creates an entry for each value, and it will be faster to retrieve data. For example, suppose you have a book which carries the details of the countries, and you want to find out the information about India than why you will go through every page of that book you could directly go to the index, and then from index you can go to that particular page where all the information about India is given.

26) Which are the different types of indexes in SQL?

There are three types of Indexes in SQL:

- Unique Index

- Clustered Index

- NonClustered Index

- Bit-Map index

- Normal index

- Composite index

- B-tree index

- function based index

27) What is the unique Index?

Unique Index:

For creating a unique index, the user has to check the data in the column because the unique indexes are used when any column of the table has unique values. This indexing does not allow the field to have duplicate values if the column is unique indexed. A unique index can be applied automatically when a primary key is defined.

28) What is Clustered Index in SQL?

Clustered Index:

The clustered index is used to reorder the physical order of the table and search based on the key values. Each table can have only one clustered index. The Clustered index is the only index which has been automatically created when the primary key is generated. If moderate data modification needed to be done in the table then clustered indexes are preferred.

29) What is the Non-Clustered Index in SQL?

Non-Clustered Index:

The reason to create non-clustered index is searching the data. We well know that clustered indexes are created automatically primary keys are generated, but non-clustered indexes are created when multiple joins conditions and various filters are used in the query. Non-Clustered Index does not alter the physical order of the table and maintains logical order of data. Each table can have 999 non-clustered indexes.

30) What is the difference between SQL, MySQL and SQL Server?

SQL or Structured Query Language is a language which is used to communicate with a relational database. It provides a way to manipulate and create databases. On the other hand, MySQL and Microsoft's SQL Server both are relational database management systems that use SQL as their standard relational database language.

MySQL is available for free as it is open source whereas SQL server is not an open source software.

31) What is the difference between SQL and PL/SQL?

SQL or Structured Query Language is a language which is used to communicate with a relational database. It provides a way to manipulate and create databases. On the other hand, PL/SQL is a dialect of SQL which is used to enhance the capabilities of SQL. It was developed by Oracle Corporation in the early 90's. It adds procedural features of programming languages in SQL.

In SQL single query is being executed at once whereas in PL/SQL a whole block of code is executed at once.

SQL is like the source of data that we need to display on the other hand PL/SQL provides a platform where the SQL the SQL data will be shown.

SQL statement can be embedded in PL/SQL, but PL/SQL statement cannot be embedded in SQL as SQL do not support any programming language and keywords.

32) Is it possible to sort a column using a column alias?

Yes. You can use the column alias in the ORDER BY instead of WHERE clause for sorting.

33) What is the difference between clustered and non-clustered index in SQL?

There are mainly two type of indexes in SQL, Clustered index and non clustered index. The differences between these two indexes is very important from SQL performance perspective.

1. One table can have only one clustered index, but it can have many non-clustered index. (Approximately 250).

2. A clustered index determines how data is stored physically in the table. Clustered index stores data in the cluster, related data is stored together, so that retrieval of data becomes simple.

3. Clustered indexes store the data information and the data itself whereas non-clustered index stores only the information, and then it will refer you to the data stored in clustered data.

4. Reading from a clustered index is much faster than reading from non-clustered index from the same table.

5. Clustered index sort and store data row in the table or view based on their key value, while non-cluster has a structure separate from the data row.

34) What is the SQL query to display the current date?

There is a built-in function in SQL called GetDate() which is used to return the current timestamp.

35) Which are the most commonly used SQL joins?

Most commonly used SQL joins are INNER JOIN and LEFT OUTER JOIN and RIGHT OUTER JOIN.

36) What are the different types of joins in SQL?

Joins are used to merge two tables or retrieve data from tables. It depends on the relationship between tables.

Following are the most commonly used joins in SQL:

Inner Join: inner joins are of three type:

1. Theta join

2. Natural join

3. Equijoin

Outer Join: outer joins are of three type:

1. right outer join

2. Left outer join

3. Full outer join

37) What is Inner Join in SQL?

Inner join:

Inner join returns rows when there is at least one match of rows between the tables. INNER JOIN keyword joins the matching records from two tables.

38) What is Right Join in SQL?

Right Join:

Right Join is used to retrieve rows which are common between the tables and all rows of a Right-hand side table. It returns all the rows from the right-hand side table even though there are no matches in the left-hand side table.

39) What is Left Join in SQL?

Left Join:

The left join is used to retrieve rows which are common between the tables and all rows of the Left-hand side table. It returns all the rows from the Left-hand side table even though there are no matches on the Right-hand side table.

40) What is Full Join in SQL?

Full Join:

Full join return rows when there are matching rows in any one of the tables. This means it returns all the rows from the left-hand side table and all the rows from the right-hand side table.

41) What is a "TRIGGER" in SQL?

- A trigger allows you to execute a batch of SQL code when an insert, update or delete command is run against a specific table as TRIGGER is said to be the set of actions that are performed whenever commands like insert, update or delete are given through queries.

- The trigger is said to be activated when these commands are given to the system.

- Triggers are the particular type of stored procedures that are defined to execute automatically in place or after data modifications.

- Triggers are generated using CREATE TRIGGER statement.

42) What is self-join and what is the requirement of self-join?

A self-join is often very useful to convert a hierarchical structure to a flat structure. It is used to join a table to itself as like if that is the second table.

43) What are the set operators in SQL?

SQL queries which contain set operations are called compound queries.

Union, Union All, Intersect or Minus operators are the set operators used in the SQL.

44) What is the difference between BETWEEN and IN condition operators?

The BETWEEN operator is used to display rows based on a range of values. The values can be numbers, text, and dates as well. BETWEEN operator gives us the count of all the values occurs between a particular range.

The IN condition operator is used to check for values contained in a specific set of values. IN operator is used when we have more than one value to choose.

45) What is a constraint? Tell me about its various levels.

Constraints are the rules and regulations which are applied to the table column which enforces yours to store valid data and prevents users to store irrelevant data. There are two levels :

1. column level constraint

2. table level constraint

46) Write an SQL query to find names of employee start with 'A'?

1. SELECT * FROM Employees WHERE EmpName like 'A%'

47) Write an SQL query to get the third maximum salary of an employee from a table named employee_table.

1. SELECT TOP 1 salary

2. FROM (

3. SELECT TOP 3 salary

4. FROM employee_table

5. ORDER BY salary DESC ) AS emp

6. ORDER BY salary ASC;

48) What is ACID property in a database?

ACID property is used to ensure that the data transactions are processed reliably in a database system.

A single logical operation of a data is called transaction.

ACID is an acronym for Atomicity, Consistency, Isolation, Durability.

Atomicity: it requires that each transaction is all or nothing. It means if one part of the transaction fails, the entire transaction fails and the database state is left unchanged.

Consistency: the consistency property ensure that the data must meet all validation rules. In simple words you can say that your transaction never leaves your database without completing its state.

Isolation: this property ensure that the concurrent property of execution should not be met. The main goal of providing isolation is concurrency control.

Durability: durability simply means that once a transaction has been committed, it will remain so, come what may even power loss, crashes or errors.

49) What is the difference between NULL value, zero and blank space?

Ans: A NULL value is not the same as zero or a blank space. A NULL value is a value which is 'unavailable, unassigned, unknown or not applicable.' On the other hand, zero is a number, and a blank space is treated as a character.

The NULL value can be treated as unknown and missing value as well, but zero and blank spaces are different from the NULL value.

50) What is the usage of SQL functions?

Functions are the measured values and cannot create permanent environment changes to SQL server. SQL functions are used for the following purpose:

- To perform calculations on data

- To modify individual data items

- To manipulate the output

- To format dates and numbers

- To convert data types

SCALA

FOR

BEGINNERS

LEARN TO CODE FAST

BY

TAM SEL

SCALA

Scala tutorial provides basic and advanced concepts of Scala. Our Scala tutorial is designed for beginners and professionals.

Scala is an object-oriented and functional programming language.

Our Scala tutorial includes all topics of Scala language such as datatype, conditional expressions, comments, functions, examples on oops concepts, constructors, method overloading, this keyword, inheritance, final, exception handling, file handling, tuples, string, string interpolation, case classes, singleton objects, collection etc.

What is Scala

Scala is a general-purpose programming language. It supports object oriented, functional and imperative programming approaches. It is a strong static type language. In scala, everything is an object whether it is a function or a number. It does not have concept of primitive data.

It was designed by Martin Odersky. It was officially released for java platform in early 2004 and for .Net framework in June 2004. Later on, Scala dropped .Net support in 2012.

Scala is influenced by Java, Haskell, Lisp, Pizza etc. and influenced to F#, Fantom, Red etc.

File extension of scala source file may be either .scala or .sc.

You can create any kind of application like web application, enterprise application, mobile application, desktop based application etc.

Scala Program Example

Let's see the simple program of scala. A detailed description of this program is given in next chapters.

1. object MainObject{

2. def main(args:Array[String]){

3. print("Hello Scala")

4. }

5. }

Where to use Scala

- Web applications

- Utilities and libraries

- Data streaming with Akka

- Parallel batch processing

- Concurrency and distributed application

- Data analysis with Spark

- AWS lambda expression

- Ad hoc scripting in REPL etc.

In Scala, you can create any type of application in less time and coding whether it is web based, mobile based or desktop based application. Scala provides you powerful tools and API by using which you can create applications. Here, You can use play framework which provides a platform to build web application rapidly.

History of Scala

Scala is a general purpose programming language. It was created and developed by Martin Odersky. Martin started working on Scala in 2001 at the Ecole Polytechnique Federale de Lausanne (EPFL). It was officially released on January 20, 2004.

Scala is not an extension of Java, but it is completely interoperable with it. While compilation, Scala file translates to Java bytecode and runs on JVM (Java Virtual machine).

Scala was designed to be both object-oriented and functional. It is a pure object-oriented language in the sense that every value is an object and functional language in the sense that every function is a value. The name of scala is derived from word scalable which means it can grow with the demand of users.

Popularity of Scala

- Twitter have announced that it had switched large portions of its backend from Ruby to Scala and intended to convert the rest.

- Apple Inc. uses Scala in certain teams, along with Java and the Play framework.

- The New York Times revealed in 2014 that its internal content management system Blackbeard is built using Scala, Akka and Play Framework.

- There are teams within Google that use Scala, mostly due to acquisitions such as Firebase and Nest.

- The Walmart Canada Uses Scala for their back end platform.

Features of Scala

There are following features of scala:

- Type inference

- Singleton object

- Immutability

- Lazy computation

- Case classes and Pattern matching

- Concurrency control

- String interpolation

- Higher order function

- Traits

- Rich collection set

Type Inference

In Scala, you don't require to mention data type and function return type explicitly. Scala is enough smart to deduce the type of data. The return type of function is determined by the type of last expression present in the function.

Singleton object

In Scala, there are no static variables or methods. Scala uses singleton object, which is essentially class with only one object in the source file. Singleton object is declared by using object instead of class keyword.

Immutability

Scala uses immutability concept. Each declared variable is immutable by default. Immutable means you can't modify its value. You can also create mutable variables which can be changed.

Immutable data helps to manage concurrency control which requires managing data.

Lazy Computation

In Scala, computation is lazy by default. Scala evaluates expressions only when they are required. You can declare a lazy variable by using lazy keyword. It is used to increase performance.

Case classes and Pattern matching

Scala case classes are just regular classes which are immutable by default and decomposable through pattern matching.

All the parameters listed in the case class are public and immutable by default.

Case classes support pattern matching. So, you can write more logical code.

Concurrency control

Scala provides standard library which includes the actor model. You can write concurrency code by using actor. Scala provides one more platform and tool to deal with concurrency known as Akka. Akka is a separate open source framework that provides actor-based concurrency. Akka actors may be distributed or combined with software transactional memory.

String Interpolation

Since Scala 2.10.0, Scala offers a new mechanism to create strings from your data. It is called string interpolation. String interpolation allows users to embed variable references directly in processed string literals. Scala provides three string interpolation methods: s, f and raw.

Higher Order Functions

Higher order function is a function that either takes a function as argument or returns a function. In other words, we can say a function which works with another function is called higher order function.

Higher order function allows you to create function composition, lambda function or anonymous function etc.

Traits

A trait is like an interface with a partial implementation. In Scala, trait is a collection of abstract and non-abstract methods. You can create trait that can have all abstract methods or some abstract and some non-abstract methods.

Traits are compiled into Java interfaces with corresponding implementation classes that hold any methods implemented in the traits.

Rich Set of Collection

Scala provides rich set of collection library. It contains classes and traits to collect data. These collections can be mutable or immutable. You can use it according to your requirement. Scala.collection.mutable package contains all the mutable collections. You can add, remove and update data while using this package.

Scala.collection.immutable package contains all the immutable collections. It does not allow you to modify data.

Simple Program of Scala

In this tutorial, you will learn how to write scala programs. To write scala program you need to install scala on your machine. You must have latest jdk installed on your machine because scala compiler creates .class file which is a byte code. Scala interpreter executes this byte code by using jvm (Java Virtual Machine).

Scala Example: Hello Scala

The following code example is a simple scala program.

1. object ScalaExample{

2. def main(args:Array[String]){

3. println "Hello Scala"

4. }

5. }

In the above code, we have created an object ScalaExample. It contains a main method and display message using println method.

This file is saved with the name ScalaExample.scala.

Command to compile this code is: scalac ScalaExample.scala

Command to execute the compiled code is: scala ScalaExample

After executing code it yields the following output.

Output:

Hello Scala

You can also use IDE (Integrated Development Environment) for executing scala code.

The above example is written using object oriented approach. You can also use functional approach to write code in scala.

Scala Example: Hello Scala

Below is the example by using functional approach.

1. def scalaExample{

2. println("Hello Scala")

3. }

4. scalaExample            // Calling of function

Output:

Hello Scala

Scala Variables and Data Types

Variable is a name which is used to refer memory location. You can create mutable and immutable variable in scala. Let's see how to declare variable.

Mutable Variable

You can create mutable variable using var keyword. It allows you to change value after declaration of variable.

1. var data = 100

2. data = 101  // It works, No error.

In the above code, var is a keyword and data is a variable name. It contains an integer value 100. Scala is a type infers language so you don?t need to specify data type explicitly. You can also mention data type of variable explicitly as we have used in below.

Another example of variable

1. var data:Int = 100  // Here, we have mentioned Int followed by : (colon)

Immutable Variable

1. var data = 100

2. data = 101  // Error: reassignment to val

The above code throws an error because we have changed content of immutable variable, which is not allowed. So if you want to change content then it is advisable to use var instead of val.

Data Types in Scala

Data types in scala are much similar to java in terms of their storage, length, except that in scala there is no concept of primitive data types every type is an object and starts with capital letter. A table of data types is given below. You will see their uses further.

Data Type-Default Value-Size

Boolean-False-True or false

Byte-0-8 bit signed value (-2^7 to 2^7-1)

Short-0-16 bit signed value(-2^15 to 2^15-1)

Char-'\u0000'-16 bit unsigned Unicode character(0 to 2^16-1)

Int-0-32 bit signed value(-2^31 to 2^31-1)

Long-0L-64 bit signed value(-2^63 to 2^63-1)

Float-0.0F-32 bit IEEE 754 single-precision float

Double-0.0D-64 bit IEEE 754 double-precision float

String-Null-A sequence of characters

Scala Conditional Expressions

Scala provides if statement to test the conditional expressions. It tests boolean conditional expression which can be either true or false. Scala use various types of if else statements.

- If statement

- If-else statement

- Nested if-else statement

- If-else-if ladder statement

Scala if statement

The scala if statement is used to test condition in scala. If block executes only when condition is true otherwise execution of if block is skipped.

Syntax

1. if(condition){

2. // Statements to be executed

3. }

Scala Example: If Statement

1. var age:Int = 20;

2. if(age > 18){

3. println ("Age is greate than 18")

4. }

Output:

Age is greate than 18

Scala If-Else Statement

The scala if-else statement tests the condition. If the condition is true, if block executes otherwise else block executes.

Syntax

1. if(condition){

2. // If block statements to be executed

3. } else {

4. // Else bock statements to be executed

5. }

Scala if-else example

1. var number:Int = 21

2. if(number%2==0){

3. println("Even number")

4. }else{

5. println("Odd number")

6. }

Output:

Odd number

Scala If-Else-If Ladder Statement

The scala if-else-if ladder executes one condition among the multiple conditional statements.

Syntax

1. if (condition1){

2. //Code to be executed if condition1 is true

3. } else if (condition2){

4. //Code to be executed if condition2 is true

5. } else if (condition3){

6. //Code to be executed if condition3 is true

7. }

8. ...

9. else {

10. //Code to be executed if all the conditions are false

11. }

Scala If-Else-If Ladder Example

1. var number:Int = 85

2. if(number>=0 && number<50){

3. println ("fail")

4. }

5. else if(number>=50 && number<60){

6. println("D Grade")

7. }

8. else if(number>=60 && number<70){

9. println("C Grade")

10. }

11. else if(number>=70 && number<80){

12. println("B Grade")

13. }

14. else if(number>=80 && number<90){

15. println("A Grade")

16. }

17. else if(number>=90 && number<=100){

18. println("A+ Grade")

19. }

20. else println ("Invalid")

Output:

A Grade

Scala If Statement as better alternative of Ternary Operators

In scala, you can assign if statement result to a function. Scala does not have ternary operator concept like C/C++ but provides more powerful if which can return value. Let's see an example

Example

1. object MainObject {

2. def main(args: Array[String]) {

3. val result = checkIt(-10)

4. println (result)

5. }

6. def checkIt (a:Int)  =  if (a >= 0) 1 else -1    // Passing a if expression value to function

7. }

Output:

-1

Scala Pattern Matching

Pattern matching is a feature of scala. It works same as switch case in other programming languages. It matches best case available in the pattern.

Let's see an example.

Scala Pattern Matching Example

1. object MainObject {

2. def main(args: Array[String]) {

3. var a = 1

4. a match{

5. case 1 => println("One")

6. case 2 => println("Two")

7. case _ => println("No")

8. }

9. }

10. }

In the above example, we have implemented a pattern matching.

Here, match using a variable named a. This variable matches with best available case and prints output. Underscore (_) is used in the last case for making it default case.

Output:

One

Match expression can return case value also. In next example, we are defining method having a match with cases for any type of data. Any is a class in scala which is a super class of all data types and deals with all type of data. Let's see an example.

Scala Pattern Matching Example2

1. object MainObject {

2. def main(args: Array[String]) {

3. var result = search ("Hello")

4. print(result)

5. }

6. def search (a:Any):Any = a match{

7. case 1  => println("One")

8. case "Two" => println("Two")

9. case "Hello" => println("Hello")

10. case _ => println("No")

12. }

13. }

Output:

Hello

Scala while loop

In Scala, while loop is used to iterate code till the specified condition. It tests boolean expression and iterates again and again. You are recommended to use while loop if you don't know number of iterations prior.

Syntax

1. while(boolean expression){

2. // Statements to be executed

3. }

Scala while loop Example

1. object MainObject {

2. def main(args: Array[String]) {

3. var a = 10;                       // Initialization

4. while( a<=20 ){                // Condition

5. println(a);

6. a = a+2                        // Incrementation

7. }

8. }

9. }

Output:

10

12

14

16

18

20

Scala Infinite While Loop Example

You can also create an infinite while loop. In the below program, we just passed true in while loop. Be careful, while using infinite loop.

1. object MainObject {

2. def main(args: Array[String]) {

3. var a = 10;           // Initialization

4. while( true ){        // Condition

5. println(a);

6. a = a+2            // Incrementation

7. }

8. }

9. }

Output:

10

12

14

16

...

Ctr+Z    // To stop execution

Scala do-while loop example

1. object MainObject {

2. def main(args: Array[String]) {

3. var a = 10;         // Initialization

4. do {

5. println( a );

6. a = a + 2;      // Increment

7. }

8. while( a <= 20 )     // Condition

9. }

10. }

Output:

10

12

14

16

18

20

Scala Infinite do-while loop

In scala, you can create infinite do-while loop. To create infinite loop just pass true literal in loop condition.

Let's see an example.

1. object MainObject {

2. def main(args: Array[String]) {

3. var a = 10;                         // Initialization

4. do {

5. println( a );

6. a = a + 2;                      // Increment

7. }

8. while( true)                         // Condition

9. }

10. }

Output:

10

12

14

16

...

Ctrl+Z   // To stop execution of program

Scala for loop

In scala, for loop is known as for-comprehensions. It can be used to iterate, filter and return an iterated collection. The for-comprehension looks a bit like a for-loop in imperative languages, except that it constructs a list of the results of all iterations.

Syntax

1. for( i <- range){

2. // statements to be executed

3. }

In the above syntax, range is a value which has start and end point. You can pass range by using to or until keyword.

Scala for-loop example by using to keyword

1. object MainObject {

2. def main(args: Array[String]) {

3. for( a <- 1 to 10 ){

4. println(a);

5. }

6. }

7. }

Output:

1

2

3

4

5

6

7

8

9

10

In the below example, until is used instead of to. The major difference between until and to is, to includes start and end value given in the range, while until excludes last value of the range. So, the below example will print only 1 to 9.

Scala for-loop Example by using until keyword

1. object MainObject {

2. def main(args: Array[String]) {

3. for( a <- 1 until 10 ){

4. println(a);

5. }

6. }

7. }

Output:

1

2

3

4

5

6

7

8

9

It is helpful to apply until keyword when you are iterating string or array, because array range is 0 to n-1. until does not exceed to n-1. So, your code will not complain of upper range.

Scala for-loop filtering Example

You can use for to filter your data. In the below example, we are filtering our data by passing a conditional expression. This program prints only even values in the given range.

1. object MainObject {

2. def main(args: Array[String]) {

3. for( a <- 1 to 10 if a%2==0 ){

4. println(a);

5. }

6. }

7. }

Output:

2

4

6

8

10

Scala for-loop Example by using yield keyword

In the above example, we have used yield keyword which returns a result after completing of loop iterations. The for use buffer internally to store iterated result and after finishing all iterations it yields the final result from that buffer. It does not work like imperative loop.

1. object MainObject {

2. def main(args: Array[String]) {

3. var result = for( a <- 1 to 10) yield a

4. for(i<-result){

5. println(i)

6. }

7. }

8. }

Output:

1

2

3

4

5

6

7

8

9

10

Scala for-loop in Collection

In scala, you can iterate collections like list, sequence etc, either by using for each loop or for-comprehensions.

Let's see an example.

Scala for- loop Example for Iterating Collection

1. object MainObject {

2. def main(args: Array[String]) {

3. var list = List(1,2,3,4,5,6,7,8,9)          // Creating a list

4. for( i <- list){                         // Iterating the list

5. println(i)

6. }

8. }

9. }

Output:

1

2

3

4

5

6

7

8

9

10

Scala for-each loop Example for Iterating Collection

In the below code we have use three approaches of for-each loop. You can implement any of them according to your need.

1. object MainObject {

2. def main(args: Array[String]) {

3. var list = List(1,2,3,4,5,6,7,8,9)  // Creating a list

4. list.foreach{

5. println     // Print each element

6. }

7. list.foreach(print)

8. println

9. list.foreach((element:Int)=>print(element+" "))      // Explicitly mentioning type of elements

10. }

11. }

Output:

1

2

3

4

5

6

7

8

9

123456789

1 2 3 4 5 6 7 8 9

Scala for-loop Example using by keyword

In the above example, we have used by keyword. The by keyword is used to skip the iteration. When you code like: by 2 it means, this loop will skip all even iterations of loop.

1. object MainObject {

2. def main(args: Array[String]) {

3. for(i<-1 to 10 by 2){

4. println(i)

5. }

6. }

7. }

Output:

1

3

5

7

9

Scala Break

Break is used to break a loop or program execution. It skips the current execution. Inside inner loop it breaks the execution of inner loop.

In scala, there is no break statement but you can do it by using break method and by importing scala.util.control.Breaks._ package. Let's see an example.

Scala Break Example

1. import scala.util.control.Breaks._                  // Importing  package

2. object MainObject {

3. def main(args: Array[String]) {

4. breakable {                                 // Breakable method to avoid exception

5. for(i<-1 to 10 by 2){

6. if(i==7)

7. break                           // Break used here

8. else

9. println(i)

10. }

11. }

12. }

13. }

Output:

1

3

5

Scala Break Example: Break inner Loop

You can use break statement to terminate execution of inner loop in nested loop.

Let's see an example.

1. import scala.util.control.Breaks._

2. object MainObject {

3. def main(args: Array[String]) {

4. for(i <- 1 to 3){

5. breakable {

6. for(j <- 1 to 3){

7. if(i == 2 & j == 2 )

8. break

9. println(i+" "+j)

10. }

11. }

12. }

13. }

14. }

Output:

1 1

1 2

1 3

2 1

3 1

3 2

3 3

Scala Comments

The scala comments are statements which are not executed by the compiler or interpreter. The comments can be used to provide information or explanation about the variable, method, class or any statement. It can also be used to hide program code details.

In scala, there are three types of comments

1. Single line comment

2. Multiline comment

3. Documentation comment

Scala Single Line Comment Example

Single line comment is used to comment single line of code.

1. // Example of single line comment.

2. object MainObject {

3. def main(args: Array[String]) {

4. var a = 1           // Here, a is a variable

5. println(a)

6. }

7. }

Output:

1

Scala Multiline Comment

Multiline comment is used to comment multiple lines of code in the program.

1. // Example of multi line comment.

2. object MainObject {

3. def main(args: Array[String]) {

4. var a = 1

5. println(a)

6. }

7. /*

8. In the main method, we have created a variable named a

9. and printed it

10. */

11. }

Output:

1

Scala Documentation Comment Example

1. // Example of documentation comment.

2. object MainObject {

3. def main(args: Array[String]) {

4. var a = 1

5. println(a)

6. }

7. /**

8. *  In the main method, we have created a variable named a

9. *  and printed it

10. *

11. */

12. }

Output:

1

Scala Functions

Scala supports functional programming approach. It provides rich set of built-in functions and allows you to create user defined functions also.

In scala, functions are first class values. You can store function value, pass function as an argument and return function as a value from other function. You can create function by using def keyword. You must mention return type of parameters while defining function and return type of a function is optional. If you don't specify return type of a function, default return type is Unit.

Scala Function Declaration Syntax

1. def functionName(parameters : typeofparameters) : returntypeoffunction = {

2. // statements to be executed

3. }

In the above syntax, = (equal) operator is looking strange but don't worry scala has defined it as:

You can create function with or without = (equal) operator. If you use it, function will return value. If you don't use it, your function will not return anything and will work like subroutine.

Scala functions don?t use return statement. Return type infers by compiler from the last expression or statement present in the function.

Scala Function Example without using = Operator

The function defined below is also known as non parameterized function.

1. object MainObject {

2. def main(args: Array[String]) {

3. functionExample()           // Calling function

4. }

5. def functionExample()  {        // Defining a function

6. println("This is a simple function")

7. }

8. }

Output:

This is a simple function

Scala Function Example with = Operator

1. object MainObject {

2. def main(args: Array[String]) {

3. var result = functionExample()          // Calling function

4. println(result)

5. }

6. def functionExample() = {       // Defining a function

7. var a = 10

8. a

9. }

10. }

Output:

10

Scala Parameterized Function Example

when using parameterized function you must mention type of parameters explicitly otherwise compiler throws an error and your code fails to compile.

1. object MainObject {

2. def main(args: Array[String]) = {

3. functionExample(10,20)

4. }

5. def functionExample(a:Int, b:Int) = {

6. var c = a+b

7. println(c)

8. }

9. }

Output:

30

Scala Recursion Function

In the program given below, we are multiplying two numbers by using recursive function.

In scala, you can create recursive functions also. Be careful while using recursive function. There must be a base condition to terminate program safely.

1. object MainObject {

2. def main(args: Array[String]) = {

3. var result = functionExample(15,2)

4. println(result)

5. }

6. def functionExample(a:Int, b:Int):Int = {

7. if(b == 0)          // Base condition

8. 0

9. else

10. a+functionExample(a,b-1)

11. }

12. }

Output:

30

Function Parameter with Default Value

Scala provides a feature to assign default values to function parameters. It helps in the scenario when you don't pass value during function calling. It uses default values of parameters.

Let's see an example.

Scala Function Parameter example with default value

1. object MainObject {

2. def main(args: Array[String]) = {

3. var result1 = functionExample(15,2)     // Calling with two values

4. var result2 = functionExample(15)   // Calling with one value

5. var result3 = functionExample()     // Calling without any value

6. println(result1+"\n"+result2+"\n"+result3)

7. }

8. def functionExample(a:Int = 0, b:Int = 0):Int = {   // Parameters with default values as 0

9. a+b

10. }

11. }

Output:

17

15

0

Scala Function Named Parameter Example

In scala function, you can specify the names of parameters during calling the function. In the given example, you can notice that parameter names are passing during calling. You can pass named parameters in any order and can also pass values only.

Let's see an example.

1. object MainObject {

2. def main(args: Array[String]) = {

3. var result1 = functionExample(a = 15, b = 2)    // Parameters names are passed during call

4. var result2 = functionExample(b = 15, a = 2)    // Parameters order have changed during call

5. var result3 = functionExample(15,2)             // Only values are passed during call

6. println(result1+"\n"+result2+"\n"+result3)

7. }

8. def functionExample(a:Int, b:Int):Int = {

9. a+b

10. }

11. }

Output:

17

17

17

Scala Higher Order Functions

Higher order function is a function that either takes a function as argument or returns a function. In other words we can say a function which works with function is called higher order function.

Higher order function allows you to create function composition, lambda function or anonymous function etc.

Let's see an example.

Scala Example: Passing a Function as Parameter in a Function

1. object MainObject {

2. def main(args: Array[String]) = {

3. functionExample(25, multiplyBy2)                   // Passing a function as parameter

4. }

5. def functionExample(a:Int, f:Int=>AnyVal):Unit = {

6. println(f(a))                                   // Calling that function

7. }

8. def multiplyBy2(a:Int):Int = {

9. a*2

10. }

11. }

Output:

60

Scala Example: Function Composition

In scala, functions can be composed from other functions. It is a process of composing in which a function represents the application of two composed functions.

Let's see an example.

1. object MainObject {

2. def main(args: Array[String]) = {

3. var result = multiplyBy2(add2(10))      // Function composition

4. println(result)

5. }

6. def add2(a:Int):Int = {

7. a+2

8. }

10. def multiplyBy2(a:Int):Int = {

11. a*2

12. }

13. }

Output:

24

Scala Anonymous (lambda) Function

Anonymous function is a function that has no name but works as a function. It is good to create an anonymous function when you don't want to reuse it latter.

You can create anonymous function either by using => (rocket) or _ (underscore) wild card in scala.

Let's see an example.

Scala Anonymous function Example

1. object MainObject {

2. def main(args: Array[String]) = {

3. var result1 = (a:Int, b:Int) => a+b        // Anonymous function by using => (rocket)

4. var result2 = (_:Int)+(_:Int)              // Anonymous function by using _ (underscore) wild card

5. println(result1(10,10))

6. println(result2(10,10))

7. }

8. }

Output:

20

20

Scala Multiline Expression

Expressions those are written in multiple lines are called multiline expression. In scala, be carefull while using multiline expressions.

The following program explains about if we break an expression into multiline, the scala compiler throw a warning message.

Scala Multiline Expression Example

1. def add1(a:Int, b:Int) = {

2. a

3. +b

4. }

The above program does not evaluate complete expression and just return b here. So, be careful while using multiline expressions.

Output:

MainObject.scala:3: warning: a pure expression does nothing in statement

position; you may be omitting necessary parentheses

a

^

one warning found

10

You can apply following ways to avoid above problem.

Scala Example Multiline Expression

1. object MainObject {

2. def add2(a:Int, b:Int) = {

3. a+

4. b

5. }

6. def add3(a:Int, b:Int) = {

7. (a

8. +b)

9. }

10. def main(args: Array[String]) = {

11. var result2 = add2(10,10)

12. var result3 = add3(10,10)

13. println(result2+"\n"+result3)

14. }

15. }

Output:

20

20

Scala Function Currying

In scala, method may have multiple parameter lists. When a method is called with a fewer number of parameter lists, then this will yield a function taking the missing parameter lists as its arguments.

In other words it is a technique of transforming a function that takes multiple arguments into a function that takes a single argument.

Scala Function Currying Example

1. object MainObject {

2. def add(a:Int)(b:Int) = {

3. a+b

4. }

5. def main(args: Array[String]) = {

6. var result = add(10)(10)

7. println("10 + 10 = "+result)

8. var addIt = add(10)_

9. var result2 = addIt(3)

10. println("10 + 3 = "+result2)

11. }

12. }

Output:

20

Scala Nested Functions

Scala is a first class function language which means it allows you to passing function, returning function, composing function, nested function etc. An example below explain about how to define and call nested functions.

Scala Nested Functions Example

1. object MainObject {

2. def add(a:Int, b:Int, c:Int) = {

3. def add2(x:Int,y:Int) = {

4. x+y

5. }

6. add2(a,add2(b,c))

7. }

8. def main(args: Array[String]) = {

9. var result = add(10,10,10)

10. println(result)

11. }

12. }

Output:

30

Scala Function with Variable Length Parameters

In scala, you can define function of variable length parameters. It allows you to pass any number of arguments at the time of calling the function.

Let's see an example.

Scala Example: Function with Variable Length Parameters

1. def add(args: Int*) = {

2. var sum = 0;

3. for(a <- args) sum+=a

4. sum

5. }

6. var sum = add(1,2,3,4,5,6,7,8,9);

7. println(sum);

Output:

45

Scala Object and Class

Unlike java, scala is a pure object oriented programming language. It allows us to create object and class so that you can develop object oriented applications.

Object

Object is a real world entity. It contains state and behavior. Laptop, car, cell phone are the real world objects. Object typically has two characteristics:

1) State: data values of an object are known as its state.

2) Behavior: functionality that an object performs is known as its behavior.

Object in scala is an instance of class. It is also known as runtime entity.

Class

Class is a template or a blueprint. It is also known as collection of objects of similar type.

In scala, a class can contain:

1. Data member

2. Member method

3. Constructor

4. Block

5. Nested class

6. Super class information etc.

You must initialize all instance variables in the class. There is no default scope. If you don't specify access scope, it is public. There must be an object in which main method is defined. It provides starting point for your program. Here, we have created an example of class.

Scala Sample Example of Class

1. class Student{

2. var id:Int = 0;                         // All fields must be initialized

3. var name:String = null;

4. }

5. object MainObject{

6. def main(args:Array[String]){

7. var s = new Student()               // Creating an object

8. println(s.id+" "+s.name);

9. }

10. }

Output:

0 null

Scala Sample Example2 of Class

In scala, you can create class like this also. Here, constructor is created in class definition. This is called primary constructor.

1. class Student(id:Int, name:String){     // Primary constructor

2. def show(){

3. println(id+" "+name)

4. }

5. }

6. object MainObject{

7. def main(args:Array[String]){

8. var s = new Student(100,"Martin")   // Passing values to constructor

9. s.show()                // Calling a function by using an object

10. }

11. }

Output:

100 Martin

Scala Example of class that maintains the records of students

1. class Student(id:Int, name:String){

2. def getRecord(){

3. println(id+" "+name);

4. }

5. }

7. object MainObject{

8. def main(args: Array[String]){

9. var student1 = new Student(101,"Raju");

10. var student2 = new Student(102,"Martin");

11. student1.getRecord();

12. student2.getRecord();

13. }

14. }

Output:

101 Raju

102 Martin

Scala Anonymous object

In scala, you can create anonymous object. An object which has no reference name is called anonymous object. It is good to create anonymous object when you don't want to reuse it further.

Scala Anonymous object Example

1. class Arithmetic{

2. def add(a:Int, b:Int){

3. var add = a+b;

4. println("sum = "+add);

5. }

6. }

8. object MainObject{

9. def main(args:Array[String]){

10. new Arithmetic().add(10,10);

12. }

13. }

Output:

Sum = 20

Scala Singleton and Companion Object

Scala Singleton Object

Singleton object is an object which is declared by using object keyword instead by class. No object is required to call methods declared inside singleton object.

In scala, there is no static concept. So scala creates a singleton object to provide entry point for your program execution.

If you don't create singleton object, your code will compile successfully but will not produce any output. Methods declared inside Singleton Object are accessible globally. A singleton object can extend classes and traits.

Scala Singleton Object Example

1. object Singleton{

2. def main(args:Array[String]){

3. SingletonObject.hello()         // No need to create object.

4. }

5. }

8. object SingletonObject{

9. def hello(){

10. println("Hello, This is Singleton Object")

11. }

12. }

Output:

Hello, This is Singleton Object

Scala Companion Object

In scala, when you have a class with same name as singleton object, it is called companion class and the singleton object is called companion object.

The companion class and its companion object both must be defined in the same source file.

Scala Companion Object Example

1. class ComapanionClass{

2. def hello(){

3. println("Hello, this is Companion Class.")

4. }

5. }

6. object CompanoinObject{

7. def main(args:Array[String]){

8. new ComapanionClass().hello()

9. println("And this is Companion Object.")

10. }

11. }

Output:

Hello, this is Companion Class.

And this is Companion Object.

Scala Case Classes and Case Object

Scala case classes are just regular classes which are immutable by default and decomposable through pattern matching.

It uses equal method to compare instance structurally. It does not use new keyword to instantiate object.

All the parameters listed in the case class are public and immutable by default.

Syntax

1. case class className(parameters)

Scala Case Class Example

1. case class CaseClass(a:Int, b:Int)

3. object MainObject{

4. def main(args:Array[String]){

5. var c =  CaseClass(10,10)       // Creating object of case class

6. println("a = "+c.a)               // Accessing elements of case class

7. println("b = "+c.b)

8. }

9. }

Output:

a = 10

b = 10

Case classes support pattern matching. So, you can use that in patterns. Following is the example of case classes and pattern.

Scala Case Class and Pattern Matching Example

A case class which has no arguments is declared as case object instead of case class. case object is serializeable by default.

1. trait SuperTrait

2. case class CaseClass1(a:Int,b:Int) extends SuperTrait

3. case class CaseClass2(a:Int) extends SuperTrait         // Case class

4. case object CaseObject extends SuperTrait               // Case object

5. object MainObject{

6. def main(args:Array[String]){

7. callCase(CaseClass1(10,10))

8. callCase(CaseClass2(10))

9. callCase(CaseObject)

10. }

11. def callCase(f:SuperTrait) = f match{

12. case CaseClass1(f,g)=>println("a = "+f+" b ="+g)

13. case CaseClass2(f)=>println("a = "+f)

14. case CaseObject=>println("No Argument")

15. }

16. }

Output:

a = 10 b =10

a = 10

No Argument

Scala Constructor

In scala, constructor is not special method. Scala provides primary and any number of auxiliary constructors. We have explained each in details in the following example.

Scala Default Primary Constructor

In scala, if you don't specify primary constructor, compiler creates a constructor which is known as primary constructor. All the statements of class body treated as part of constructor. It is also known as default constructor.

Scala Default Primary Constructor Example

1. class Student{

2. println("Hello from default constructor");

3. }

Output:

Hello from default constructor

Scala Primary Constructor

Scala provides a concept of primary constructor with the definition of class. You don't need to define explicitly constructor if your code has only one constructor. It helps to optimize code. You can create primary constructor with zero or more parameters.

Scala Primary Constructor Example

1. class Student(id:Int, name:String){

2. def showDetails(){

3. println(id+" "+name);

4. }

5. }

7. object MainObject{

8. def main(args:Array[String]){

9. var s = new Student(101,"Rama");

10. s.showDetails()

11. }

12. }

Output:

101 Rama

Scala Secondary (auxiliary) Constructor

You can create any number of auxiliary constructors in a class. You must call primary constructor from inside the auxiliary constructor. this keyword is used to call constructor from other constructor. When calling other constructor make it first line in your constructor.

Scala Secondary Constructor Example

1. class Student(id:Int, name:String){

2. var age:Int = 0

3. def showDetails(){

4. println(id+" "+name+" "+age)

5. }

6. def this(id:Int, name:String,age:Int){

7. this(id,name)       // Calling primary constructor, and it is first line

8. this.age = age

9. }

10. }

12. object MainObject{

13. def main(args:Array[String]){

14. var s = new Student(101,"Rama",20);

15. s.showDetails()

16. }

17. }

Output:

101 Rama 20

Scala Example: Constructor Overloading

In scala, you can overload constructor. Let's see an example.

1. class Student(id:Int){

2. def this(id:Int, name:String)={

3. this(id)

4. println(id+" "+name)

5. }

6. println(id)

7. }

9. object MainObject{

10. def main(args:Array[String]){

11. new Student(101)

12. new Student(100,"India")

13. }

14. }

Output:

101

100

100 India

Scala Method Overloading

Scala provides method overloading feature which allows us to define methods of same name but having different parameters or data types. It helps to optimize code.

Scala Method Overloading Example by using Different Parameters

In the following example, we have define two add methods with different number of parameters but having same data type.

1. class Arithmetic{

2. def add(a:Int, b:Int){

3. var sum = a+b

4. println(sum)

5. }

6. def add(a:Int, b:Int, c:Int){

7. var sum = a+b+c

8. println(sum)

9. }

10. }

12. object MainObject{

13. def main(args:Array[String]){

14. var a  = new Arithmetic();

15. a.add(10,10);

16. a.add(10,10,10);

17. }

18. }

Output:

20

30

Scala Method Overloading Example by using Different Data Type

In the following example, we have created two add method having same number of parameters but different data types.

1. class Arithmetic{

2. def add(a:Int, b:Int){

3. var sum = a+b

4. println(sum)

5. }

6. def add(a:Double, b:Double){

7. var sum = a+b

8. println(sum)

9. }

10. }

11. object MainObject{

12. def main(args:Array[String]){

13. var b = new Arithmetic()

14. b.add(10,10)

15. b.add(10.0,20.0)

17. }

18. }

Output:

20

30.0

Scala this

In scala, this is a keyword and used to refer current object. You can call instance variables, methods, constructors by using this keyword.

Scala this Example

In the following example, this is used to call instance variables and primary constructotr.

1. class ThisExample{

2. var id:Int = 0

3. var name: String = ""

4. def this(id:Int, name:String){

5. this()

6. this.id = id

7. this.name = name

8. }

9. def show(){

10. println(id+" "+name)

11. }

12. }

14. object MainObject{

15. def main(args:Array[String]){

16. var t = new ThisExample(101,"Martin")

17. t.show()

18. }

19. }

Output:

101 Martin

Scala Constructor Calling by using this keyword

In the following example this is used to call constructor. It illustrates how we can call constructor from other constructor. You must make sure that this must be first statement in the constructor while calling to other constructor otherwise compiler throws an error.

1. class Student(name:String){

2. def this(name:String, age:Int){

3. this(name)

4. println(name+" "+age)

5. }

6. }

8. object MainObject{

9. def main(args:Array[String]){

10. var s = new Student("Rama",100)

11. }

12. }

Output:

Rama 100

Scala Inheritance

Inheritance is an object oriented concept which is used to reusability of code. You can achieve inheritance by using extends keyword. To achieve inheritance a class must extend to other class. A class which is extended called super or parent class. a class which extends class is called derived or base class.

Syntax

1. class SubClassName extends SuperClassName(){

2. /* Write your code

3. *  methods and fields etc.

4. */

5. }

Understand the Simple Example of Inheritance

Scala Single Inheritance Example

1. class Employee{

2. var salary:Float = 10000

3. }

5. class Programmer extends Employee{

6. var bonus:Int = 5000

7. println("Salary = "+salary)

8. println("Bonus = "+bonus)

9. }

11. object MainObject{

12. def main(args:Array[String]){

13. new Programmer()

14. }

15. }

Output:

Salary = 10000.0

Bonus = 5000

Types of Inheritance in Scala

Scala supports various types of inheritance including single, multilevel, multiple, and hybrid. You can use single, multilevel and hierarchal in your class. Multiple and hybrid can only be achieved by using traits. Here, we are representing all types of inheritance by using pictorial form.

Scala Multilevel Inheritance Example

1. class A{

2. var salary1 = 10000

3. }

5. class B extends A{

6. var salary2 = 20000

7. }

9. class C extends B{

10. def show(){

11. println("salary1 = "+salary1)

12. println("salary2 = "+salary2)

13. }

14. }

16. object MainObject{

17. def main(args:Array[String]){{

18. var c = new C()

19. c.show()

21. }

22. }

Output:

salary1 = 10000

salary2 = 20000

Scala Method Overriding

When a subclass has the same name method as defined in the parent class, it is known as method overriding. When subclass wants to provide a specific implementation for the method defined in the parent class, it overrides method from parent class.

In scala, you must use either override keyword or override annotation to override methods from parent class.

Scala Method Overriding Example 1

1. class Vehicle{

2. def run(){

3. println("vehicle is running")

4. }

5. }

7. class Bike extends Vehicle{

8. override def run(){

9. println("Bike is running")

10. }

11. }

13. object MainObject{

14. def main(args:Array[String]){

15. var b = new Bike()

16. b.run()

17. }

18. }

Output:

Bike is running

Real example of method overriding

Flowchart

Scala Method Overriding Example 2

This example shows how subclasses override the method of parent class.

1. class Bank{

2. def getRateOfInterest()={

3. 0

4. }

5. }

7. class SBI extends Bank{

8. override def getRateOfInterest()={

9. 8

10. }

11. }

13. class ICICI extends Bank{

14. override def getRateOfInterest()={

15. 7

16. }

17. }

19. class AXIS extends Bank{

20. override def getRateOfInterest()={

21. 9

22. }

23. }

25. object MainObject{

26. def main(args:Array[String]){

27. var s=new SBI();

28. var i=new ICICI();

29. var a=new AXIS();

30. println("SBI Rate of Interest: "+s.getRateOfInterest());

31. println("ICICI Rate of Interest: "+i.getRateOfInterest());

32. println("AXIS Rate of Interest: "+a.getRateOfInterest());

33. }

34. }

Output:

SBI Rate of Interest: 8

ICICI Rate of Interest: 7

AXIS Rate of Interest: 9

Scala Field Overriding

In scala, you can override fields also but it has some rules that need to be followed. Below are some examples that illustrate how to override fields.

Scala Field Overriding Example1

1. class Vehicle{

2. var speed:Int = 60

4. }

5. class Bike extends Vehicle{

6. var speed:Int = 100

7. def show(){

8. println(speed)

9. }

10. }

11. object MainObject{

12. def main(args:Array[String]){

13. var b = new Bike()

14. b.show()

15. }

16. }

Output:

Error - variable speed needs 'override' modifier

In scala, you must use either override keyword or override annotation when you are overriding methods or fields of super class. If you don't do this, compiler reports an error and stops execution of program.

Scala Field Overriding Example2

1. class Vehicle{

2. val speed:Int = 60

4. }

5. class Bike extends Vehicle{

6. override val speed:Int = 100     // Override keyword

7. def show(){

8. println(speed)

9. }

10. }

11. object MainObject{

12. def main(args:Array[String]){

13. var b = new Bike()

14. b.show()

15. }

16. }

Output:

100

In scala, you can override only those variables which are declared by using val keyword in both classes. Below are some interesting examples which demonstrate the whole process.

Scala Field Overriding Example3

1. class Vehicle{

2. var speed:Int = 60

3. }

4. class Bike extends Vehicle{

5. override var speed:Int = 100

6. def show(){

7. println(speed)

8. }

9. }

10. object MainObject{

11. def main(args:Array[String]){

12. var b = new Bike()

13. b.show()

14. }

15. }

Output:

variable speed cannot override a mutable variable

Scala Field Overriding Example4

1. class Vehicle{

2. val speed:Int = 60

4. }

6. class Bike extends Vehicle{

7. override var speed:Int = 100

8. def show(){

9. println(speed)

10. }

11. }

13. object MainObject{

14. def main(args:Array[String]){

15. var b = new Bike()

16. b.show()

17. }

18. }

Output:

Error - variable speed needs to be a stable, immutable value

Scala Final

Final is a keyword, which is used to prevent inheritance of super class members into derived class. You can declare final variables, methods and classes also.

Scala Final Variable Example

You can't override final variables in subclass. Let's see an example.

1. class Vehicle{

2. final val speed:Int = 60

3. }

4. class Bike extends Vehicle{

5. override val speed:Int = 100

6. def show(){

7. println(speed)

8. }

9. }

11. object MainObject{

12. def main(args:Array[String]){

13. var b = new Bike()

14. b.show()

15. }

16. }

Output:

Error - value speed cannot override final member

Scala Final Method

Final method declare in the parent class can't be override. You can make any method to final if you don't want to get it overridden. Attempt to override final method will cause to a compile time error.

Scala Final Method Example

1. class Vehicle{

2. final def show(){

3. println("vehicle is running")

4. }

5. }

6. class Bike extends Vehicle{

7. //override val speed:Int = 100

8. override def show(){

9. println("bike is running")

10. }

11. }

12. object MainObject{

13. def main(args:Array[String]){

14. var b = new Bike()

15. b.show()

16. }

17. }

Output:

method show cannot override final member

override def show(){

^

one error found

Scala Final Class Example

You can also make final class. Final class can't be inherited. If you make a class final, it can't be extended further.

1. final class Vehicle{

2. def show(){

3. println("vehicle is running")

4. }

6. }

8. class Bike extends Vehicle{

9. override def show(){

10. println("bike is running")

11. }

12. }

14. object MainObject{

15. def main(args:Array[String]){

16. var b = new Bike()

17. b.show()

18. }

19. }

Output:

error: illegal inheritance from final class Vehicle

class Bike extends Vehicle{

^

one error found

Scala Abstract Class

A class which is declared with abstract keyword is known as abstract class. An abstract class can have abstract methods and non-abstract methods as well. Abstract class is used to achieve abstraction. Abstraction is a process in which we hide complex implementation details and show only functionality to the user.

In scala, we can achieve abstraction by using abstract class and trait. We have discussed about these in detail here.

Scala Abstract Class Example

In this example, we have created a Bike abstract class. It contains an abstract method. A class Hero extends it and provides implementation of its run method.

A class that extends an abstract class must provide implementation of its all abstract methods. You can't create object of an abstract class.

1. abstract class Bike{

2. def run()

3. }

5. class Hero extends Bike{

6. def run(){

7. println("running fine...")

8. }

9. }

11. object MainObject{

12. def main(args: Array[String]){

13. var h = new Hero()

14. h.run()

15. }

16. }

Output:

running fine...

Scala Abstract Class Example: Having Constructor, Variables and Abstract Methods

1. abstract class Bike(a:Int){             // Creating constructor

2. var b:Int = 20                      // Creating variables

3. var c:Int = 25

4. def run()                           // Abstract method

5. def performance(){                  // Non-abstract method

6. println("Performance awesome")

7. }

8. }

10. class Hero(a:Int) extends Bike(a){

11. c = 30

12. def run(){

13. println("Running fine...")

14. println("a = "+a)

15. println("b = "+b)

16. println("c = "+c)

17. }

18. }

20. object MainObject{

21. def main(args: Array[String]){

22. var h = new Hero(10)

23. h.run()

24. h.performance()

25. }

26. }

Output:

Running fine...

a = 10

b = 20

c = 30

Performance awesome

Scala Abstract Class Example: Abstract Method is not implemented

In this example, we didn't implement abstract method run(). Compiler reports an error during compilation of this program. Error message is given below in output section.

1. abstract class Bike{

2. def run()               // Abstract method

3. }

5. class Hero extends Bike{        // Not implemented in this class

6. def runHero(){

7. println("Running fine...")

8. }

9. }

11. object MainObject{

12. def main(args: Array[String]){

13. var h = new Hero()

14. h.runHero()

15. }

16. }

Output:

error: class Hero needs to be abstract, since method run in class Bike of type ()Unit is not defined

class Hero extends Bike{

^

one error found

To avoid this problem either you must implement all abstract members of abstract class or make your class abstract too.

Scala Trait

A trait is like an interface with a partial implementation. In scala, trait is a collection of abstract and non-abstract methods. You can create trait that can have all abstract methods or some abstract and some non-abstract methods.

A variable that is declared either by using val or var keyword in a trait get internally implemented in the class that implements the trait. Any variable which is declared by using val or var but not initialized is considered abstract.

Traits are compiled into Java interfaces with corresponding implementation classes that hold any methods implemented in the traits.

Scala Trait Example

1. trait Printable{

2. def print()

3. }

5. class A4 extends Printable{

6. def print(){

7. println("Hello")

8. }

9. }

11. object MainObject{

12. def main(args:Array[String]){

13. var a = new A4()

14. a.print()

15. }

16. }

Output:

Hello

If a class extends a trait but does not implement the members declared in that trait, it must be declared abstract. Let's see an example.

Scala Trait Example

1. trait Printable{

2. def print()

3. }

5. abstract class A4 extends Printable{            // Must declared as abstract class

6. def printA4(){

7. println("Hello, this is A4 Sheet")

8. }

9. }

Scala Trait Example: Implementing Multiple Traits in a Class

If a class implements multiple traits, it will extend the first trait, class, abstract class. with keyword is used to extend rest of the traits.

You can achieve multiple inheritances by using trait.

1. trait Printable{

2. def print()

3. }

5. trait Showable{

6. def show()

7. }

9. class A6 extends Printable with Showable{

10. def print(){

11. println("This is printable")

12. }

13. def show(){

14. println("This is showable");

15. }

16. }

18. object MainObject{

19. def main(args:Array[String]){

20. var a = new A6()

21. a.print()

22. a.show()

23. }

24. }

Output:

This is printable

This is showable

Scala Trait having abstract and non-abstract methods

You can also define method in trait as like in abstract class. I.e. you can treat trait as abstract class also. In scala, trait is almost same as abstract class except that it can't have constructor. You can't extend multiple abstract classes but can extend multiple traits.

Scala Trait Example

1. trait Printable{

2. def print()         // Abstract method

3. def show(){         // Non-abstract method

4. println("This is show method")

5. }

6. }

8. class A6 extends Printable{

9. def print(){

10. println("This is print method")

11. }

12. }

14. object MainObject{

15. def main(args:Array[String]){

16. var a = new A6()

17. a.print()

18. a.show()

19. }

20. }

Output:

This is print method

This is show method

Scala Trait Mixins

In scala, trait mixins means you can extend any number of traits with a class or abstract class. You can extend only traits or combination of traits and class or traits and abstract class.

It is necessary to maintain order of mixins otherwise compiler throws an error.

You can use mixins in scala like this:

Scala Trait Example: Mixins Order Not Maintained

In this example, we have extended a trait and an abstract class. Let's see what happen.

1. trait Print{

2. def print()

3. }

5. abstract class PrintA4{

6. def printA4()

7. }

9. class A6 extends Print with PrintA4{

10. def print(){                // Trait print

11. println("print sheet")

12. }

13. def printA4(){              // Abstract class printA4

14. println("Print A4 Sheet")

15. }

16. }

18. object MainObject{

19. def main(args:Array[String]){

20. var a = new A6()

21. a.print()

22. a.printA4()

23. }

24. }

Output:

error: class PrintA4 needs to be a trait to be mixed in

class A6 extends Print with PrintA4{

^

one error found

The above program throws a compile time error, because we didn't maintain mixins order.

Scala Mixins Order

The right mixins order of trait is that any class or abstract class which you want to extend, first extend this. All the traits will be extended after this class or abstract class.

Scala Trait Example: Mixins Order Maintained

1. trait Print{

2. def print()

3. }

5. abstract class PrintA4{

6. def printA4()

7. }

9. class A6 extends PrintA4 with Print{            // First one is abstract class second one is trait

10. def print(){                                        // Trait print

11. println("print sheet")

12. }

13. def printA4(){                                      // Abstract class printA4

14. println("Print A4 Sheet")

15. }

16. }

18. object MainObject{

19. def main(args:Array[String]){

20. var a = new A6()

21. a.print()

22. a.printA4()

23. }

24. }

Output:

print sheet

Print A4 Sheet

Another Example of Scala Trait

Here, we have used one more approach to extend trait in our program. In this approach, we extend trait during object creation. Let's see an example.

1. trait Print{

2. def print()

3. }

5. abstract class PrintA4{

6. def printA4()

7. }

9. class A6 extends PrintA4 {

10. def print(){                             // Trait print

11. println("print sheet")

12. }

13. def printA4(){                              // Abstract class printA4

14. println("Print A4 Sheet")

15. }

16. }

18. object MainObject{

19. def main(args:Array[String]){

20. var a = new A6() with Print             // You can also extend trait during object creation

21. a.print()

22. a.printA4()

23. }

24. }

Output:

print sheet

Print A4 Sheet

Scala Access Modifier

Access modifier is used to define accessibility of data and our code to the outside world. You can apply accessibly to classes, traits, data members, member methods and constructors etc. Scala provides least accessibility to access to all. You can apply any access modifier to your code according to your application requirement.

Scala provides only three types of access modifiers, which are given below:

1. No modifier

2. Protected

3. Private

In scala, if you don't mention any access modifier, it is treated as no modifier.

Scala Example: Private Access Modifier

In scala, private access modifier is used to make data accessible only within class in which it is declared. It is most restricted and keeps your data in limited scope. Private data members does not inherit into subclasses.

1. class AccessExample{

2. private var a:Int = 10

3. def show(){

4. println(a)

5. }

6. }

7. object MainObject{

8. def main(args:Array[String]){

9. var p = new AccessExample()

10. p.a = 12

11. p.show()

12. }

13. }

Output:

error: variable a in class AccessExample cannot be accessed in AccessExample

p.a = 12

^

one error found

Scala Example: Protected Access Modifier

Protected access modifier is accessible only within class, sub class and companion object. Data members declared as protected are inherited in subclass. Let's see an example.

1. class AccessExample{

2. protected var a:Int = 10

3. }

4. class SubClass extends AccessExample{

5. def display(){

6. println("a = "+a)

7. }

8. }

9. object MainObject{

10. def main(args:Array[String]){

11. var s = new SubClass()

12. s.display()

13. }

14. }

Output:

a = 10

Scala Example: No-Access-Modifier

In scala, when you don't mention any access modifier, it is treated as no-access-modifier. It is same as public in java. It is least restricted and can easily accessible from anywhere inside or outside the package.

1. class AccessExample{

2. var a:Int = 10

3. def show(){

4. println(" a = "+a)

5. }

6. }

8. object MainObject{

9. def main(args:Array[String]){

10. var a = new AccessExample()

11. a.show()

12. }

13. }

Output:

a = 10

Scala Array

Array is a collection of mutable values. It is an index based data structure which starts from 0 index to n-1 where n is length of array.

Scala arrays can be generic. It means, you can have an Array[T], where T is a type parameter or abstract type. Scala arrays are compatible with Scala sequences - you can pass an Array[T] where a Seq[T] is required. It also supports all the sequence operations.

Following image represents the structure of array where first index is 0, last index is 9 and array length is 10.

Scala Types of array

1. Single dimensional array

2. Multidimensional array

Scala Single Dimensional Array

Single dimensional array is used to store elements in linear order. Array elements are stored in contiguous memory space. So, if you have any index of an array, you can easily traverse all the elements of the array.

Syntax for Single Dimensional Array

1. var arrayName : Array[arrayType] = new Array[arrayType](arraySize);   or

2. var arrayName = new Array[arrayType](arraySize)  or

3. var arrayName : Array[arrayType] = new Array(arraySize);   or

4. var arrayName = Array(element1, element2 ... elementN)

Scala Array Example: Single Dimensional

1. class ArrayExample{

2. var arr = Array(1,2,3,4,5)      // Creating single dimensional array

3. def show(){

4. for(a<-arr)                       // Traversing array elements

5. println(a)

6. println("Third Element  = "+ arr(2))        // Accessing elements by using index

7. }

8. }

10. object MainObject{

11. def main(args:Array[String]){

12. var a = new ArrayExample()

13. a.show()

14. }

15. }

Output:

1

2

3

4

5

Third Element  = 3

Scala Example 2: Single Dimensional

In this example, we have created an array by using new keyword which is used to initialize memory for array. The entire array elements are set to default value, you can assign that later in your code.

1. class ArrayExample{

2. var arr = new Array[Int](5)         // Creating single dimensional array

3. def show(){

4. for(a<-arr){                      // Traversing array elements

5. println(a)

6. }

7. println("Third Element before assignment = "+ arr(2))        // Accessing elements by using index

8. arr(2) = 10                                                          // Assigning new element at 2 index

9. println("Third Element after assignment = "+ arr(2))

10. }

11. }

13. object MainObject{

14. def main(args:Array[String]){

15. var a = new ArrayExample()

16. a.show()

17. }

18. }

Output:

0

0

0

0

0

Third Element before assignment = 0

Third Element after assignment = 10

Scala Passing Array into Function

You can pass array as an argument to function during function call. Following example illustrate the process how we can pass an array to the function.

1. class ArrayExample{

2. def show(arr:Array[Int]){

3. for(a<-arr)                // Traversing array elements

4. println(a)

5. println("Third Element = "+ arr(2))        // Accessing elements by using index

6. }

7. }

9. object MainObject{

10. def main(args:Array[String]){

11. var arr = Array(1,2,3,4,5,6)    // creating single dimensional array

12. var a = new ArrayExample()

13. a.show(arr)                     // passing array as an argument in the function

14. }

15. }

Output:

1

2

3

4

5

6

Third Element = 3

Scala Array Example: Iterating By using Foreach Loop

You can also iterate array elements by using foreach loop. Let's see an example.

1. class ArrayExample{

2. var arr = Array(1,2,3,4,5)      // Creating single dimensional array

3. arr.foreach((element:Int)=>println(element))       // Iterating by using foreach loop

4. }

6. object MainObject{

7. def main(args:Array[String]){

8. new ArrayExample()

9. }

10. }

Output:

1

2

3

4

5

Scala Multidimensional Array

Multidimensional array is an array which store data in matrix form. You can create from two dimensional to three, four and many more dimensional array according to your need. Below we have mentioned array syntax. Scala provides an ofDim method to create multidimensional array.

Multidimensional Array Syntax

1. var arrayName = Array.ofDim[ArrayType](NoOfRows,NoOfColumns) or

2. var arrayName = Array(Array(element...), Array(element...), ...)

Scala Multidimensional Array Example by using ofDim

In This example, we have created array by using ofDim method.

1. class ArrayExample{

2. var arr = Array.ofDim[Int](2,2)          // Creating multidimensional array

3. arr(1)(0) = 15                          // Assigning value

4. def show(){

5. for(i<- 0 to 1){                       // Traversing elements by using loop

6. for(j<- 0 to 1){

7. print(" "+arr(i)(j))

8. }

9. println()

10. }

11. println("Third Element = "+ arr(1)(1))        // Accessing elements by using index

12. }

13. }

15. object MainObject{

16. def main(args:Array[String]){

17. var a = new ArrayExample()

18. a.show()

19. }

20. }

Output:

0 0

15 0

Third Element = 0

Scala Multidimensional Array by using Array of Array

Apart from ofDim you can also create multidimensional array by using array of array. In this example, we have created multidimensional array by using array of array.

1. class ArrayExample{

2. var arr = Array(Array(1,2,3,4,5), Array(6,7,8,9,10))   // Creating multidimensional array

3. def show(){

4. for(i<- 0 to 1){               // Traversing elements using loop

5. for(j<- 0 to 4){

6. print(" "+arr(i)(j))

7. }

8. println()

9. }

10. }

11. }

13. object MainObject{

14. def main(args:Array[String]){

15. var a = new ArrayExample()

16. a.show()

17. }

18. }

Output:

1 2 3 4 5

6 7 8 9 10

Scala Addition of Two Matrix Example

You can manipulate array elements in scala. Here, we are adding two array elements and storing result into third array.

1. class ArrayExample{

2. var arr1 = Array(Array(1,2,3,4,5), Array(6,7,8,9,10))   // Creating multidimensional array

3. var arr2 = Array(Array(1,2,3,4,5), Array(6,7,8,9,10))

4. var arr3 = Array.ofDim[Int](2,5)

5. def show(){

6. for(i<- 0 to 1){               // Traversing elements using loop

7. for(j<- 0 to 4){

8. arr3(i)(j) = arr1(i)(j)+arr2(i)(j)

9. print(" "+arr3(i)(j))

10. }

11. println()

12. }

13. }

14. }

16. object MainObject{

17. def main(args:Array[String]){

18. var a = new ArrayExample()

19. a.show()

20. }

21. }

Output:

2 4 6 8 10

12 14 16 18 20

Scala String

In scala, string is a combination of characters or we can say it is a sequence of characters. It is index based data structure and use linear approach to store data into memory. String is immutable in scala like java. You can manipulate string and can apply method to get desire result but you can't change original string object.

Scala String Example

This is a simple string example in which we have created a string variable.

1. class StringExample{

2. var s1 = "Scala string example"

3. def show(){

4. println(s1)

5. }

6. }

8. object MainObject{

9. def main(args:Array[String]){

10. var s = new StringExample()

11. s.show()

12. }

13. }

Output:

Scala string example

Scala Immutable String Example 1

In this example, we are creating a string variable and trying to modify string object. Let's see what happen.

1. class StringExample{

2. var s1 = "Scala string example"

3. "This is "+s1           // Modifying string object

4. def show(){

5. println(s1)

6. }

7. }

9. object MainObject{

10. def main(args:Array[String]){

11. var s = new StringExample()

12. s.show()

13. }

14. }

Output:

Scala string example

Scala Immutable String Example 2

In this example, we are modifying the string and assigning it to a variable so that we can keep reference of modified string. Here, a new string object is created after modification. The s1 variable now refers to new string object. The old string object is still available in the memory but does not have any reference.

1. class StringExample{

2. var s1 = "Scala string example"

3. s1 = "This is "+s1

4. def show(){

5. println(s1)

6. }

7. }

9. object MainObject{

10. def main(args:Array[String]){

11. var s = new StringExample()

12. s.show()

13. }

14. }

Output:

This is Scala string example

Scala String Comparison Example

In scala, you can compare two string objects by using == (equal) method. The following program describes how to use equal operator. It returns boolean value either true or false.

1. class StringExample{

2. var s1 = "Scala string example"

3. var s2 = "Hello Scala"

4. var s3 = "Hello Scala"

5. def show(){

6. println(s1 == s2)

7. println(s2 == s3)

8. }

9. }

11. object MainObject{

12. def main(args:Array[String]){

13. var s = new StringExample()

14. s.show()

15. }

16. }

Output:

false

true

Scala String Methods

Scala String equals() Method Example

You can also use equal() method to compare two string objects. It returns true if both string object are equal otherwise returns false.

1. class StringExample{

2. var s1 = "Scala string example"

3. var s2 = "Hello Scala"

4. var s3 = "Hello Scala"

5. def show(){

6. println(s1.equals(s2))

7. println(s2.equals(s3))

8. }

9. }

11. object MainObject{

12. def main(args:Array[String]){

13. var s = new StringExample()

14. s.show()

15. }

16. }

Output:

false

true

Scala compareTo() Method Example

The compareTo() method compares the given string with current string lexicographically. It returns positive number, negative number or 0.

If first string is greater than second string, it returns positive number (difference of character value). If first string is less than second string, it returns negative number and if first string is equal to second string, it returns 0.

1. s1 > s2 => positive number

2. s1 < s2s2 => negative number

3. s1 == s2 => 0

1. class StringExample{

2. var s1 = "Scala string example"

3. var s2 = "Hello Scala"

4. var s3 = "Hello Scala"

5. def show(){

6. println(s1.compareTo(s2))

7. println(s2.compareTo(s3))

8. }

9. }

11. object MainObject{

12. def main(args:Array[String]){

13. var s = new StringExample()

14. s.show()

15. }

16. }

Output:

11

0

Scala Concatenation Example by using + (Plus) Operator

In scala, you can concatenate string by using + operator. An example is given below in which two strings are concatenated by using + operator.

1. class StringExample{

2. var s1 = "This is "+"Scala string example"

3. def show(){

4. println(s1)

5. }

6. }

8. object MainObject{

9. def main(args:Array[String]){

10. var s = new StringExample()

11. s.show()

12. }

13. }

Output:

This is Scala string example

Scala String concat() Method Example

Apart from + operator, you can also use concat() method to concatenate two strings. It is a predefined method of string class.

1. class StringExample{

2. var s1 = "This is "

3. var s2 = "Scala string example"

4. def show(){

5. println(s1.concat(s2))

6. }

7. }

9. object MainObject{

10. def main(args:Array[String]){

11. var s = new StringExample()

12. s.show()

13. }

14. }

Output:

This is Scala string example

Scala substring() Method Example

The substring() method is used to get substring from a string. By specifying start and end index as argument you can get substring according to your requirement. It is a predefined method of string class.

1. class StringExample3{

2. var s1 = "Scala string example"

3. def show(){

4. println(s1.substring(0,5))

5. }

6. }

8. object MainObject{

9. def main(args:Array[String]){

10. var s = new StringExample3()

11. s.show()

12. }

13. }

Output:

Scala

Scala String Interpolation

Starting in Scala 2.10.0, Scala offers a new mechanism to create strings from your data. It is called string interpolation. String interpolation allows users to embed variable references directly in processed string literals. Scala provides three string interpolation methods: s, f and raw.

Scala Program Example: Without using s Method

This is simple example which does not use s method in string.

1. class StringExample{

2. var pi = 3.14

3. def show(){

4. println("value of pi = "+pi)

5. }

6. }

7. object MainObject{

8. def main(args:Array[String]){

9. var s = new StringExample()

10. s.show()

11. }

12. }

Output:

value of pi = 3.14

Scala String Interpolation Example

This program use string interpolation in print function. You can see the advantage of interpolation. Here, we did not use + operator to concatenate string objects.

1. class StringExample{

2. var pi = 3.14

3. def show(){

4. println(s"value of pi = $pi")

5. }

6. }

8. object MainObject{

9. def main(args:Array[String]){

10. var s = new StringExample()

11. s.show()

12. }

13. }

Output:

value of pi = 3.14

Scala String Interpolation Example By using s Method

The s method of string interpolation allows us to pass variable in string object. You don't need to use + operator to format your output string. In the following example, a string variable is passed to string in the print function. This variable is evaluated by compiler and variable is replaced by value.

1. class StringExample{

2. var s1 = "Scala string example"

3. def show(){

4. println(s"This is $s1")

5. }

6. }

9. object MainObject{

10. def main(args:Array[String]){

11. var s = new StringExample()

12. s.show()

13. }

14. }

Output:

This is Scala string example

Scala String Interpolation Example By using f Method

The f method is used to format your string output. It is like printf function of c language which is used to produce formatted output. You can pass your variables of any type in the print function.

1. class StringExample{

2. var s1 = "Scala string example"

3. var version = 2.12

4. def show(){

5. println(f"This is $s1%s, scala version is $version%2.2f")

6. }

7. }

10. object MainObject{

11. def main(args:Array[String]){

12. var s = new StringExample()

13. s.show()

14. }

15. }

Output:

This is Scala string example, scala version is 2.12

Scala String Interpolation Example By using raw Method

The raw method of string interpolation is used to produce raw string. It does not interpret special char present in the string. Let's see an example.

1. class StringExample{

2. var s1 = "Scala \tstring \nexample"

3. var s2 = raw"Scala \tstring \nexample"

4. def show(){

5. println(s1)

6. println(s2)

7. }

8. }

10. object MainObject{

11. def main(args:Array[String]){

12. var s = new StringExample()

13. s.show()

14. }

15. }

Output:

Scala     string

example

Scala \nstring \nexample

Scala Exception Handling

Exception handling is a mechanism which is used to handle abnormal conditions. You can also avoid termination of your program unexpectedly.

Scala makes "checked vs unchecked" very simple. It doesn't have checked exceptions. All exceptions are unchecked in Scala, even SQLException and IOException.

Scala Program Example without Exception Handling

1. class ExceptionExample{

2. def divide(a:Int, b:Int) = {

3. a/b             // Exception occurred here

4. println("Rest of the code is executing...")

5. }

6. }

7. object MainObject{

8. def main(args:Array[String]){

9. var e = new ExceptionExample()

10. e.divide(100,0)

12. }

13. }

Output:

java.lang.ArithmeticException: / by zero

Scala Try Catch

Scala provides try and catch block to handle exception. The try block is used to enclose suspect code. The catch block is used to handle exception occurred in try block. You can have any number of try catch block in your program according to need.

Scala Try Catch Example

In the following program, we have enclosed our suspect code inside try block. After try block we have used a catch handler to catch exception. If any exception occurs, catch handler will handle it and program will not terminate abnormally.

1. class ExceptionExample{

2. def divide(a:Int, b:Int) = {

3. try{

4. a/b

5. }catch{

6. case e: ArithmeticException => println(e)

7. }

8. println("Rest of the code is executing...")

9. }

10. }

11. object MainObject{

12. def main(args:Array[String]){

13. var e = new ExceptionExample()

14. e.divide(100,0)

16. }

17. }

Output:

java.lang.ArithmeticException: / by zero

Rest of the code is executing...

Scala Try Catch Example 2

In this example, we have two cases in our catch handler. First case will handle only arithmetic type exception. Second case has Throwable class which is a super class in exception hierarchy. The second case is able to handle any type of exception in your program. Sometimes when you don't know about the type of exception, you can use super class.

1. class ExceptionExample{

2. def divide(a:Int, b:Int) = {

3. try{

4. a/b

5. var arr = Array(1,2)

6. arr(10)

7. }catch{

8. case e: ArithmeticException => println(e)

9. case ex: Throwable =>println("found a unknown exception"+ ex)

10. }

11. println("Rest of the code is executing...")

12. }

13. }

14. object MainObject{

15. def main(args:Array[String]){

16. var e = new ExceptionExample()

17. e.divide(100,10)

19. }

20. }

Output:

found a unknown exceptionjava.lang.ArrayIndexOutOfBoundsException: 10

Rest of the code is executing...

Scala Finally

The finally block is used to release resources during exception. Resources may be file, network connection, database connection etc. the finally block executes guaranteed. The following program illustrate the use of finally block.

Scala Finally Block Example

1. class ExceptionExample{

2. def divide(a:Int, b:Int) = {

3. try{

4. a/b

5. var arr = Array(1,2)

6. arr(10)

7. }catch{

8. case e: ArithmeticException => println(e)

9. case ex: Exception =>println(ex)

10. case th: Throwable=>println("found a unknown exception"+th)

11. }

12. finally{

13. println("Finaly block always executes")

14. }

15. println("Rest of the code is executing...")

16. }

17. }

20. object MainObject{

21. def main(args:Array[String]){

22. var e = new ExceptionExample()

23. e.divide(100,10)

25. }

26. }

Output:

java.lang.ArrayIndexOutOfBoundsException: 10

Finally block always executes

Rest of the code is executing...

Scala Throw keyword

You can throw exception explicitly in you code. Scala provides throw keyword to throw exception. The throw keyword mainly used to throw custom exception. An example is given below of using scala throw exception keyword.

Scala Throw Example

1. class ExceptionExample2{

2. def validate(age:Int)={

3. if(age<18)

4. throw new ArithmeticException("You are not eligible")

5. else println("You are eligible")

6. }

7. }

9. object MainObject{

10. def main(args:Array[String]){

11. var e = new ExceptionExample2()

12. e.validate(10)

14. }

15. }

Output:

java.lang.ArithmeticException: You are not eligible

Scala Throws Keyword

Scala provides throws keyword to declare exception. You can declare exception with method definition. It provides information to the caller function that this method may throw this exception. It helps to caller function to handle and enclose that code in try-catch block to avoid abnormal termination of program. In scala, you can either use throws keyword or throws annotation to declare exception.

Scala Throws Example

1. class ExceptionExample4{

2. @throws(classOf[NumberFormatException])

3. def validate()={

4. "abc".toInt

5. }

6. }

8. object MainObject{

9. def main(args:Array[String]){

10. var e = new ExceptionExample4()

11. try{

12. e.validate()

13. }catch{

14. case ex : NumberFormatException => println("Exception handeled here")

15. }

16. println("Rest of the code executing...")

17. }

18. }

Output:

Exception handeled here

Rest of the code executing...

Scala Custom Exception

In scala, you can create your own exception. It is also known as custom exceptions. You must extend Exception class while declaring custom exception class. You can create your own exception message in custom class. Let's see an example.

Scala Custom Exception Example

1. class InvalidAgeException(s:String) extends Exception(s){}

2. class ExceptionExample{

3. @throws(classOf[InvalidAgeException])

4. def validate(age:Int){

5. if(age<18){

6. throw new InvalidAgeException("Not eligible")

7. }else{

8. println("You are eligible")

9. }

10. }

11. }

12. object MainObject{

13. def main(args:Array[String]){

14. var e = new ExceptionExample()

15. try{

16. e.validate(5)

17. }catch{

18. case e : Exception => println("Exception Occured : "+e)

19. }

20. }

21. }

Output:

Exception Occured : InvalidAgeException: Not eligible

Scala Collection

Scala provides rich set of collection library. It contains classes and traits to collect data. These collections can be mutable or immutable. You can use them according to your requirement. Scala.collection.mutable package contains all the mutable collections. You can add, remove and update data while using this package.

Scala.collection.immutable contains all the immutable collections. It does not allow you to modify data. Scala imports this package by default. If you want mutable collection, you must import scala.collection.mutable package in your code.

Scala Immutable Collections Hierarchy

The scala.collection.immutable package contains all the immutable abstract classes and traits for collections.

Scala Traversable

It is a trait and used to traverse collection elements. It is a base trait for all scala collections.

It implements the methods which are common to all collections.

Some Significant Methods of Traversable Trait

Method and Description

def head: A

It returns the first element of collection.

def init: Traversable[A]

It returns all elements except last one.

def isEmpty: Boolean

It checks whether the collection is empty or not. It returns either true or false.

def last: A

It returns the last element of this collection.

def max: A

It returns the largest element of this collection.

def min: A

It returns smallest element of this collection

def size: Int

It is used to get size of this traversable and returns a number of elements present in this traversable.

def sum: A

It returns sum of all elements of this collection.

def tail: Traversable[A]

It returns all elements except first.

def toArray: Array[A]

It converts this collection to an array.

def toList: List[A]

It converts this collection to a list.

def toSeq: Seq[A]

It converts this collection to a sequence.

def toSet[B >: A]: immutable.Set[B]

It converts this collection to a set.

Scala Iterable

It is a next trait from the top of the hierarchy and a base trait for iterable collections. It extends traversable trait and provides important methods to concrete classes.

Scala Set

It is used to store unique elements in the set. It does not maintain any order for storing elements. You can apply various operations on them. It is defined in the Scala.collection.immutable package.

Scala Set Syntax

1. val variableName:Set[Type] = Set(element1, element2,... elementN) or

2. val variableName = Set(element1, element2,... elementN)

Scala Set Example

In this example, we have created a set. You can create an empty set also. Let's see how to create a set.

1. import scala.collection.immutable._

2. object MainObject{

3. def main(args:Array[String]){

4. val set1 = Set()                            // An empty set

5. val games = Set("Cricket","Football","Hocky","Golf")    // Creating a set with elements

6. println(set1)

7. println(games)

8. }

9. }

Output:

Set()     // an empty set

Set(Cricket,Football,Hocky,Golf)

Scala Set Example 2

In Scala, Set provides some predefined properties to get information about set. You can get first or last element of Set and many more. Let's see an example.

1. import scala.collection.immutable._

2. object MainObject{

3. def main(args:Array[String]){

4. val games = Set("Cricket","Football","Hocky","Golf")

5. println(games.head)             // Returns first element present in the set

6. println(games.tail)         // Returns all elements except first element.

7. println(games.isEmpty)          // Returns either true or false

8. }

9. }

Output:

Cricket

Set(Football, Hocky, Golf)

false

Scala Set Example: Merge two Set

You can merge two sets into a single set. Scala provides a predefined method to merge sets. In this example, ++ method is used to merge two sets.

1. import scala.collection.immutable._

2. object MainObject{

3. def main(args:Array[String]){

4. val games = Set("Cricket","Football","Hocky","Golf")

5. val alphabet = Set("A","B","C","D","E")

6. val mergeSet = games ++ alphabet            // Merging two sets

7. println("Elements in games set: "+games.size)   // Return size of collection

8. println("Elements in alphabet set: "+alphabet.size)

9. println("Elements in mergeSet: "+mergeSet.size)

10. println(mergeSet)

11. }

12. }

Output:

Elements in games set: 4

Elements in alphabet set: 5

Elements in mergeSet: 9

Set(E, Football, Golf, Hocky, A, B, C, Cricket, D)

This example also proves that the merge set does not maintain order to store elements.

Scala Set Example 2

You can check whether element is present in the set or not. The following example describe the use of contains() method.

1. import scala.collection.immutable._

2. object MainObject{

3. def main(args:Array[String]){

4. val games = Set("Cricket","Football","Hocky","Golf")

5. println(games)

6. println("Elements in set: "+games.size)

7. println("Golf exists in the set : "+games.contains("Golf"))

8. println("Racing exists in the set : "+games.contains("Racing"))

10. }

11. }

Output:

Set(Cricket, Football, Hocky, Golf)

Elements in set: 4

Golf exists in the set : true

Racing exists in the set : false

Scala Set Example: Adding and Removing Elements

You can add or remove elements from the set. You can add only when your code is mutable. In this example, we are adding and removing elements of the set.

1. import scala.collection.immutable._

2. object MainObject{

3. def main(args:Array[String]){

4. var games = Set("Cricket","Football","Hocky","Golf")

5. println(games)

6. games += "Racing"               // Adding new element

7. println(games)

8. games += "Cricket"              // Adding new element, it does not allow duplicacy.

9. println(games)

10. games -= "Golf"             // Removing element

11. println(games)

12. }

13. }

Output:

Set(Cricket, Football, Hocky, Golf)

Set(Football, Golf, Hocky, Cricket, Racing)

Set(Football, Golf, Hocky, Cricket, Racing)

Set(Football, Hocky, Cricket, Racing)

Scala Set Example: Iterating Set Elements using for loop

You can iterate set elements either by using for loop or foreach loop. You can also filter elements during iteration. In this example have used for loop to iterate set elements.

1. import scala.collection.immutable._

2. object MainObject{

3. def main(args:Array[String]){

4. var games = Set("Cricket","Football","Hocky","Golf")

5. for(game <- games){

6. println(game)

7. }

8. }

9. }

Output:

Cricket

Football

Hocky

Golf

Scala Set Example Iterating Elements using foreach loop

In this example, we are using foreach loop to iterate set elements.

1. import scala.collection.immutable._

2. object MainObject{

3. def main(args:Array[String]){

4. var games = Set("Cricket","Football","Hocky","Golf")

5. games.foreach((element:String)=> println(element))

6. }

7. }

Output:

Cricket

Football

Hocky

Golf

Scala Set Example: Set Operations

In scala Set, you can also use typical math operations like: intersection and union. In the following example we have used predefined methods to perform set operations.

1. import scala.collection.immutable._

2. object MainObject{

3. def main(args:Array[String]){

4. var games = Set("Cricket","Football","Hocky","Golf","C")

5. var alphabet = Set("A","B","C","D","E","Golf")

6. var setIntersection = games.intersect(alphabet)

7. println("Intersection by using intersect method: "+setIntersection)

8. println("Intersection by using & operator: "+(games & alphabet))

9. var setUnion = games.union(alphabet)

10. println(setUnion)

11. }

12. }

Output:

Intersection by using intersect method: Set(Golf, C)

Intersection by using & operator: Set(Golf, C)

Set(E, Football, Golf, Hocky, A, B, C, Cricket, D)

Scala SortedSet

In scala, SortedSet extends Set trait and provides sorted set elements. It is useful when you want sorted elements in the Set collection. You can sort integer values and string as well.

It is a trait and you can apply all the methods defined in the traversable trait and Set trait.

Scala SortedSet Example

In the following example, we have used SortedSet to store integer elements. It returns a Set after sorting elements.

1. import scala.collection.immutable.SortedSet

2. object MainObject{

3. def main(args:Array[String]){

4. var numbers: SortedSet[Int] = SortedSet(5,8,1,2,9,6,4,7,2)

5. numbers.foreach((element:Int)=> println(element))

6. }

7. }

Output:

1

2

4

5

6

7

8

9

Scala HashSet

HashSet is a sealed class. It extends AbstractSet and immutable Set trait. It uses hash code to store elements.

It neither maintains insertion order nor sorts the elements.

Scala HashSet Example

In the following example, we have created a HashSet to store elements. Here, foreach is used to iterate elements.

1. import scala.collection.immutable.HashSet

2. object MainObject{

3. def main(args:Array[String]){

4. var hashset = HashSet(4,2,8,0,6,3,45)

5. hashset.foreach((element:Int) => println(element+" "))

6. }

7. }

Output:

0

6

2

45

3

8

4

Scala BitSet

Bitsets are sets of non-negative integers which are represented as variable-size arrays of bits packed into 64-bit words. The memory footprint of a bitset is determined by the largest number stored in it. It extends Set trait.

Scala BitSet Example

1. import scala.collection.immutable._

2. object MainObject{

3. def main(args:Array[String]){

4. var numbers = BitSet(1,5,8,6,9,0)

5. numbers.foreach((element:Int) => println(element))

6. }

7. }

Output:

0

1

5

6

8

9

Scala BitSet Example: Adding and Removing Elements

You can perform basic operations like adding and deleting in the bitset. In the following example, we have applied these operations.

1. import scala.collection.immutable._

2. object MainObject{

3. def main(args:Array[String]){

4. var numbers = BitSet(1,5,8,6,9,0)

5. numbers.foreach((element:Int) => print(element+" "))

6. numbers += 20               // Adding an element

7. print("\nAfter adding 20: ")

8. numbers.foreach((element:Int) => print(element+" "))

9. numbers-=0              // Deleting an element

10. print("\nAfter deleting 0: ")

11. numbers.foreach((element:Int) => print(element+" "))

12. }

13. }

Output:

0 1 5 6 8 9

After adding 20: 0 1 5 6 8 9 20

After deleting 0: 1 5 6 8 9 20

Scala ListSet

In scala, ListSet class implements immutable sets using a list-based data structure. Elements are stored internally in reversed insertion order, which means the newest element is at the head of the list. It maintains insertion order.

This collection is suitable only for a small number of elements. You can create empty ListSet either by calling the constructor or by applying the function ListSet.empty. Its iterate and traversal methods visit elements in the same order in which they were first inserted.

Scala ListSet Example

1. import scala.collection.immutable._

2. object MainObject{

3. def main(args:Array[String]){

4. var listset = ListSet(4,2,8,0,6,3,45)

5. listset.foreach((element:Int) => println(element+" "))

6. }

7. }

Output:

4

2

8

0

6

3

45

Scala ListSet Example: Creating ListSet and Adding Elements

1. import scala.collection.immutable._

2. object MainObject{

3. def main(args:Array[String]){

4. var listset:ListSet[String] = new ListSet()                 // Creating empty ListSet by using constructor

5. var listset2:ListSet[String] = ListSet.empty                // Creating an empty listset

6. println("listset: "+listset)

7. println("listset2: "+listset2)

8. println("After adding new elements:")

9. listset+="India"            // Adding new element

10. listset2+="Russia"          // Adding new element

11. println("listset: "+listset)

12. println("listset2: "+listset2)

13. }

14. }

Output:

listset: ListSet()

listset2: ListSet()

After adding new elements:

listset: ListSet(India)

listset2: ListSet(Russia)

Scala Seq

Seq is a trait which represents indexed sequences that are guaranteed immutable. You can access elements by using their indexes. It maintains insertion order of elements.

Sequences support a number of methods to find occurrences of elements or subsequences. It returns a list.

Scala Seq Example

In the following example, we are creating Seq and accessing elements from Seq.

1. import scala.collection.immutable._

2. object MainObject{

3. def main(args:Array[String]){

4. var seq:Seq[Int] = Seq(52,85,1,8,3,2,7)

5. seq.foreach((element:Int) => print(element+" "))

6. println("\nAccessing element by using index")

7. println(seq(2))

8. }

9. }

Output:

52 85 1 8 3 2 7

Accessing element by using index

1

You can also access elements in reverse order by using reverse method. Below we have listed some commonly used method and their description.

Commonly used Methods of Seq

Method and Description

def contains[A1 >: A](elem: A1): Boolean

Check whether the given element present in this sequence.

def copyToArray(xs: Array[A], start: Int, len: Int): Unit

It copies the seq elements to an array.

def endsWith[B](that: GenSeq[B]): Boolean

It tests whether this sequence ends with the given sequence or not.

def head: A

It selects the first element of this seq collection.

def indexOf(elem: A): Int

It finds index of first occurrence of a value in this immutable sequence.

def isEmpty: Boolean

It tests whether this sequence is empty or not.

def lastIndexOf(elem: A): Int

It finds index of last occurrence of a value in this immutable sequence.

def reverse: Seq[A]

It returns new sequence with elements in reversed order.

Scala Seq Example

In this example, we have applied some predefined methods of Seq trait.

1. import scala.collection.immutable._

2. object MainObject{

3. def main(args:Array[String]){

4. var seq:Seq[Int] = Seq(52,85,1,8,3,2,7)

5. seq.foreach((element:Int) => print(element+" "))

6. println("\nis Empty: "+seq.isEmpty)

7. println("Ends with (2,7): "+ seq.endsWith(Seq(2,7)))

8. println("contains 8: "+ seq.contains(8))

9. println("last index of 3 : "+seq.lastIndexOf(3))

10. println("Reverse order of sequence: "+seq.reverse)

11. }

12. }

Output:

52 85 1 8 3 2 7

is Empty: false

Ends with (2,7): true

contains 8: true

last index of 3 : 4

Reverse order of sequence: List(7, 2, 3, 8, 1, 85, 52)

Scala Vector

Vector is a general-purpose, immutable data structure. It provides random access of elements. It is good for large collection of elements.

It extends an abstract class AbstractSeq and IndexedSeq trait.

Scala Vector Example

1. import scala.collection.immutable._

2. object MainObject{

3. def main(args:Array[String]){

4. var vector:Vector[Int] = Vector(5,8,3,6,9,4) //Or

5. var vector2 = Vector(5,2,6,3)

6. var vector3 = Vector.empty

7. println(vector)

8. println(vector2)

9. println(vector3)

10. }

11. }

Output:

Vector(5, 8, 3, 6, 9, 4)

Vector(5, 2, 6, 3)

Vector(

Scala Vector Example

In the following example, we have created a vector. You can also add new element and merge two vectors.

1. import scala.collection.immutable._

2. object MainObject{

3. def main(args:Array[String]){

4. var vector = Vector("Hocky","Cricket","Golf")

5. var vector2 = Vector("Swimming")

6. print("Vector Elements: ")

7. vector.foreach((element:String) => print(element+" "))

8. var newVector  = vector :+ "Racing"                             // Adding a new element into vector

9. print("\nVector Elements after adding: ")

10. newVector.foreach((element:String) => print(element+" "))

11. var mergeTwoVector = newVector ++ vector2                       // Merging two vector

12. print("\nVector Elements after merging: ")

13. mergeTwoVector.foreach((element:String) => print(element+" "))

14. var reverse = mergeTwoVector.reverse                            // Reversing vector elements

15. print("\nVector Elements after reversing: ")

16. reverse.foreach((element:String) => print(element+" "))

17. var sortedVector = mergeTwoVector.sorted                        // Sorting vector elements

18. print("\nVector Elements after sorting: ")

19. sortedVector.foreach((element:String) => print(element+" "))

20. }

21. }

Output:

Vector Elements: Hocky Cricket Golf

Vector Elements after adding: Hocky Cricket Golf Racing

Vector Elements after merging: Hocky Cricket Golf Racing Swimming

Vector Elements after reversing: Swimming Racing Golf Cricket Hocky

Vector Elements after sorting: Cricket Golf Hocky Racing Swimming

Scala List

List is used to store ordered elements. It extends LinearSeq trait. It is a class for immutable linked lists. This class is good for last-in-first-out (LIFO), stack-like access patterns.

It maintains order of elements and can contain duplicates elements also.

Scala List Example

In this example, we have created two lists. Here, both lists have different syntax to create list.

1. import scala.collection.immutable._

2. object MainObject{

3. def main(args:Array[String]){

4. var list = List(1,8,5,6,9,58,23,15,4)

5. var list2:List[Int] = List(1,8,5,6,9,58,23,15,4)

6. println(list)

7. println(list2)

8. }

9. }

Output:

List(1, 8, 5, 6, 9, 58, 23, 15, 4)

List(1, 8, 5, 6, 9, 58, 23, 15, 4)

Scala List Example: Applying Predefined Methods

1. import scala.collection.immutable._

2. object MainObject{

3. def main(args:Array[String]){

4. var list = List(1,8,5,6,9,58,23,15,4)

5. var list2 = List(88,100)

6. print("Elements: ")

7. list.foreach((element:Int) => print(element+" "))        // Iterating using foreach loop

8. print("\nElement at 2 index: "+list(2))             // Accessing element of 2 index

9. var list3 = list ++ list2                               // Merging two list

10. print("\nElement after merging list and list2: ")

11. list3.foreach((element:Int)=>print(element+" "))

12. var list4 = list3.sorted                            // Sorting list

13. print("\nElement after sorting list3: ")

14. list4.foreach((element:Int)=>print(element+" "))

15. var list5 = list3.reverse                           // Reversing list elements

16. print("\nElements in reverse order of list5: ")

17. list5.foreach((element:Int)=>print(element+" "))

19. }

20. }

Output:

Elements: 1 8 5 6 9 58 23 15 4

Element at 2 index: 5

Element after merging list and list2: 1 8 5 6 9 58 23 15 4 88 100

Element after sorting list3: 1 4 5 6 8 9 15 23 58 88 100

Elements in reverse order of list5: 100 88 4 15 23 58 9 6 5 8 1

Scala Queue

Queue implements a data structure that allows inserting and retrieving elements in a first-in-first-out (FIFO) manner.

In scala, Queue is implemented as a pair of lists. One is used to insert the elements and second to contain deleted elements. Elements are added to the first list and removed from the second list.

Scala Queue Example

1. import scala.collection.immutable._

2. object MainObject{

3. def main(args:Array[String]){

4. var queue = Queue(1,5,6,2,3,9,5,2,5)

5. var queue2:Queue[Int] = Queue(1,5,6,2,3,9,5,2,5)

6. println(queue)

7. println(queue2)

8. }

9. }

Output:

Queue(1, 5, 6, 2, 3, 9, 5, 2, 5)

Queue(1, 5, 6, 2, 3, 9, 5, 2, 5)

Scala Queue Example 2

1. import scala.collection.immutable._

2. object MainObject{

3. def main(args:Array[String]){

4. var queue = Queue(1,5,6,2,3,9,5,2,5)

5. print("Queue Elements: ")

6. queue.foreach((element:Int)=>print(element+" "))

7. var firstElement = queue.front

8. print("\nFirst element in the queue: "+ firstElement)

9. var enqueueQueue = queue.enqueue(100)

10. print("\nElement added in the queue: ")

11. enqueueQueue.foreach((element:Int)=>print(element+" "))

12. var dequeueQueue = queue.dequeue

13. print("\nElement deleted from this queue: "+ dequeueQueue)

14. }

15. }

Output:

Queue Elements: 1 5 6 2 3 9 5 2 5

First element in the queue: 1

Element added in the queue: 1 5 6 2 3 9 5 2 5 100

Element deleted from this queue: (1,Queue(5, 6, 2, 3, 9, 5, 2, 5))

Scala Stream

Stream is a lazy list. It evaluates elements only when they are required. This is a feature of scala. Scala supports lazy computation. It increases performance of your program.

Scala Stream Example

In the following program, we have created a stream.

1. object MainObject{

2. def main(args:Array[String]){

3. val stream = 100 #:: 200 #:: 85 #:: Stream.empty

4. println(stream)

5. }

6. }

Output:

Stream(100, ?)

In the output, you can see that second element is not evaluated. Here, a question mark is displayed in place of element. Scala does not evaluate list until it is required.

Scala Stream Example: Applying Predefined Methods

In the following example, we have used some predefined methods like toStream, which is used to iterate stream elements.

1. import scala.collection.immutable._

2. object MainObject{

3. def main(args:Array[String]){

4. var stream = 100 #:: 200 #:: 85 #:: Stream.empty

5. println(stream)

6. var stream2 = (1 to 10).toStream

7. println(stream2)

8. var firstElement = stream2.head

9. println(firstElement)

10. println(stream2.take(10))

11. println(stream.map{_*2})

12. }

13. }

Output:

Stream(100, ?)

Stream(1, ?)

1

Stream(1, ?)

Stream(200, ?)

Scala Maps

Map is used to store elements. It stores elements in pairs of key and values. In scala, you can create map by using two ways either by using comma separated pairs or by using rocket operator.

Scala maps Example

In the following example, we have both approaches to create map.

1. object MainObject{

2. def main(args:Array[String]){

3. var map = Map(("A","Apple"),("B","Ball"))

4. var map2 = Map("A"->"Aple","B"->"Ball")

5. var emptyMap:Map[String,String] = Map.empty[String,String]

6. println(map)

7. println(map2)

8. println("Empty Map: "+emptyMap)

9. }

10. }

Output:

Map(A -> Apple, B -> Ball)

Map(A -> Aple, B -> Ball)

Empty Map: Map()

Scala Map Example: Adding and Removing Elements

You can add and remove new elements in maps. Scala provides you lots of predefined method. You can use them to perform operations on the Maps. In the following example, we have created a new Map.

1. object MainObject{

2. def main(args:Array[String]){

3. var map = Map("A"->"Apple","B"->"Ball")             // Creating map

4. println(map("A"))                            // Accessing value by using key

5. var newMap = map+("C"->"Cat")                  // Adding a new element to map

6. println(newMap)

7. var removeElement = newMap - ("B")                // Removing an element from map

8. println(removeElement)

9. }

10. }

Output:

Apple

Map(A -> Apple, B -> Ball, C -> Cat)

Map(A -> Apple, C -> Cat)

Scala HashMap

HashMap is used to store element. It use hash code to store elements and return a map.

HashMap Example

In this example, we have created a HashMap.

1. import scala.collection.immutable._

2. object MainObject{

3. def main(args:Array[String]){

4. var hashMap = new HashMap()

5. var hashMap2 = HashMap("A"->"Apple","B"->"Ball","C"->"Cat")

6. println(hashMap)

7. println(hashMap2)

8. }

9. }

Output:

Map()

Map(A -> Apple, B -> Ball, C -> Cat)

Scala HashMap Example: Adding and Accessing Elements

In the following example, we have created a HashMap. this program add elements and access elements as well.

1. import scala.collection.immutable._

2. object MainObject{

3. def main(args:Array[String]){

4. var hashMap = HashMap("A"->"Apple","B"->"Ball","C"->"Cat")

5. hashMap.foreach {

6. case (key, value) => println (key + " -> " + value)       // Iterating elements

7. }

8. println(hashMap("B"))               // Accessing value by using key

9. var newHashMap = hashMap+("D"->"Doll")

10. newHashMap.foreach {

11. case (key, value) => println (key + " -> " + value)

12. }

14. }

15. }

Output:

A -> Apple

B -> Ball

C -> Cat

Ball

A -> Apple

B -> Ball

C -> Cat

D -> Doll

Scala ListMap

This class implements immutable maps by using a list-based data structure. It maintains insertion order and returns ListMap. This collection is suitable for small elements.

You can create empty ListMap either by calling its constructor or using ListMap.empty method.

Scala ListMap Example

In this example, we have created an empty ListMap and non-empty ListMap as well.

1. import scala.collection.immutable._

2. object MainObject{

3. def main(args:Array[String]){

4. var listMap = ListMap("Rice"->"100","Wheat"->"50","Gram"->"500")    // Creating listmap with elements

5. var emptyListMap = new ListMap()            // Creating an empty list map

6. var emptyListMap2 = ListMap.empty           // Creating an empty list map

7. println(listMap)

8. println(emptyListMap)

9. println(emptyListMap2)

10. }

11. }

Output:

ListMap(Rice -> 100, Wheat -> 50, Gram -> 500)

ListMap()

ListMap()

Scala ListMap Example: Applying Basic Operations

1. import scala.collection.immutable._

2. object MainObject{

3. def main(args:Array[String]){

4. var listMap = ListMap("Rice"->"100","Wheat"->"50","Gram"->"500")    // Creating listmap with elements

5. listMap.foreach{

6. case(key,value)=>println(key+"->"+value)

7. }

8. println(listMap("Gram"))

9. var newListMap = listMap+("Pulses"->"550")

10. newListMap.foreach {

11. case (key, value) => println (key + " -> " + value)

12. }

13. }

14. }

Output:

Rice->100

Wheat->50

Gram->500

500

Rice -> 100

Wheat -> 50

Gram -> 500

Pulses -> 550

Scala Tuples

A tuple is a collection of elements in ordered form. If there is no element present, it is called empty tuple. You can use tuple to store any type of data. You can store similar type or mix type data also. You can use it to return multiple values from a function

Scala Tuple Example

In the following example, we have created tuple of different types of elements.

1. object MainObject{

2. def main(args:Array[String]){

3. var tuple = (1,5,8,6,4)                     // Tuple of integer values

4. var tuple2 = ("Apple","Banana","Gavava")        // Tuple of string values

5. var tuple3 = (2.5,8.4,10.50)                // Tuple of float values

6. var tuple4 = (1,2.5,"India")                // Tuple of mix type values

7. println(tuple)

8. println(tuple2)

9. println(tuple3)

10. println(tuple4)

11. }

12. }

Output:

(1,5,8,6,4)

(Apple,Banana,Gavava)

(2.5,8.4,10.5)

(1,2.5,India)

Scala Tuple Example: Accessing Tuple Elements

In this example, we are accessing tuple elements by using index. Here, we are using productIterator for iterating tuple elements.

1. object MainObject{

2. def main(args:Array[String]){

3. var tupleValues = (1,2.5,"India")

4. println("iterating values: ")

5. tupleValues.productIterator.foreach(println)    // Iterating tuple values using productIterator

6. println("Accessing values: ")

7. println(tupleValues._1) // Fetching first value

8. println(tupleValues._2) // Fetching second value

9. }

10. }

Output:

iterating values:

1

2.5

India

Accessing values:

1

2.5

Scala Tuple Example: Function Return Multiple Values

You can return multiple values by using tuple. Function does not return multiple values but you can do this with the help of tuple. In the following example, we are describing this process.

1. object MainObject{

2. def main(args:Array[String]){

3. var tupleValues = tupleFunction()

4. println("Iterating values: ")

5. tupleValues.productIterator.foreach(println)    // Iterating tuple values using productIterator

6. }

7. def tupleFunction()={

8. var tuple = (1,2.5,"India")

9. tuple

10. }

11. }

Output:

Iterating values:

1

2.5

India

Scala File handling

Scala provides predefined methods to deal with file. You can create, open, write and read file. Scala provides a complete package scala.io for file handling.

In this chapter, we will discuss all these file operations in detail.

Scala Creating a File Example

Scala doesn't provide file writing methods. So, you have to use the Java PrintWriter or FileWriter methods.

1. import java.io._

2. val fileObject = new File("ScalaFile.txt" )     // Creating a file

3. val printWriter = new PrintWriter(fileObject)       // Passing reference of file to the printwriter

4. printWriter.write("Hello, This is scala file")  // Writing to the file

5. printWriter.close()             // Closing printwriter

The above code will create a text file ScalaFile.txt. After creating file printwriter is used to write content to this file.

Scala Reading File Example: Reading Each Charecter

1. import scala.io.Source

3. object MainObject{

4. def main(args:Array[String]){

5. val filename = "ScalaFile.txt"

6. val fileSource = Source.fromFile(filename)

7. while(fileSource.hasNext){

8. println(fileSource.next)

9. }

10. fileSource.close()

11. }

12. }

Output:

H

e

l

l

o

,

T

h

i

s

i

s

s

c

a

l

a

f

i

l

e

Scala provides file reading methods. In the above code, we have imported scala.io package and Source class is used to access file related methods like fromFile().

You must close file refernce after reading or writing file by using close() method.

Scala Reading a File Example: Reading Each Line

1. import scala.io.Source

2. object MainObject{

3. def main(args:Array[String]){

4. val filename = "ScalaFile.txt"

5. val fileSource = Source.fromFile(filename)

6. for(line<-fileSource.getLines){

7. println(line)

8. }

9. fileSource.close()

10. }

11. }

Output:

Hello, This is scala file

Apart from these for all kind of file handling operations, you need to use java.io package to access file methods.

Scala Multithreading

Multithreading is a process of executing multiple threads simultaneously. It allows you to perform multiple operations independently.

You can achieved multitasking by using Multithreading. Threads are lightweight sub-processes which occupy less memory. Multithreading are used to develop concurrent applications in Scala.

Scala does not provide any separate library for creating thread. If you are familiar with multithreading concept of Java, you will come to know that it is similar except the syntax of Scala language itself.

You can create thread either by extending Thread class or Runnable interface. Both provide a run method to provide specific implementation.

Scala Thread Life Cycle

Thread life cycle is a span of time in which thread starts and terminates. It has various phases like new, runnable, terminate, block etc. Thread class provides various methods to monitor thread's states.

The Scala thread states are as follows:

1. New

2. Runnable

3. Running

4. Non-Runnable (Blocked)

5. Terminated

1) New

This is the first state of thread. It is just before starting of new thread.

2) Runnable

This is the state when thread has been started but the thread scheduler has not selected it to be the running thread.

3) Running

The thread is in running state if the thread scheduler has selected it.

4) Non-Runnable (Blocked)

This is the state when the thread is still alive, but is currently not eligible to run due to waiting for input or resources.

5) Terminated

A thread is in terminated or dead state when its run() method exits.

Scala Thread

There are two ways to create a thread:

1. By extending Thread class

2. By implementing Runnable interface

Scala Thread Example by Extending Thread Class

The following example extends Thread class and overrides run method. The start() method is used to start thread.

1. class ThreadExample extends Thread{

2. override def run(){

3. println("Thread is running...");

4. }

5. }

6. object MainObject{

7. def main(args:Array[String]){

8. var t = new ThreadExample()

9. t.start()

10. }

11. }

Output:

Thread is running...

Scala Thread Example by Extending Runnable Interface

The following example implements Runnable interface and overrides run method. The start() method is used to start thread.

1. class ThreadExample extends Runnable{

2. override def run(){

3. println("Thread is running...")

4. }

5. }

6. object MainObject{

7. def main(args:Array[String]){

8. var e = new ThreadExample()

9. var t = new Thread(e)

10. t.start()

11. }

12. }

Output:

Thread is running...

Scala Interview Questions

1) What is Scala?

Scala is a general-purpose programming language. It supports object-oriented, functional and imperative programming approaches. It is a strong static type language. In Scala, everything is an object whether it is a function or a number. It was designed by Martin Odersky in 2004.

Scala Program Example

1. object MainObject{

2. def main(args:Array[String]){

3. print("Hello Scala")

4. }

5. }

2) What are the features of Scala?

There are following features in Scala:

- Type inference: In Scala, you don't require to mention data type and function return type explicitly.

- Singleton object: Scala uses a singleton object, which is essentially class with only one object in the source file.

- Immutability: Scala uses immutability concept. Immutable data helps to manage concurrency control which requires managing data.

- Lazy computation: In Scala, computation is lazy by default. You can declare a lazy variable by using the lazy keyword. It is used to increase performance.

- Case classes and Pattern matching: In Scala, case classes support pattern matching. So, you can write more logical code.

- Concurrency control: Scala provides a standard library which includes the actor model. You can write concurrency code by using the actor.

- String interpolation: In Scala, string interpolation allows users to embed variable references directly in processed string literals.

- Higher order function: In Scala, higher order function allows you to create function composition, lambda function or anonymous function, etc.

- Traits: A trait is like an interface with partial implementation. In Scala, the trait is a collection of abstract and non-abstract methods.

- Rich set of collection: Scala provides a rich set of collection library. It contains classes and traits to collect data. These collections can be mutable or immutable.

3) What is pattern matching?

Pattern matching is a feature of Scala. It works same as switch case in other languages. It matches the best case available in the pattern.

Example

1. object MainObject {

2. def main(args: Array[String]) {

3. var a = 1

4. a match{

5. case 1 => println("One")

6. case 2 => println("Two")

7. case _ => println("No")

8. }

9. }

10. }

4) What is for-comprehension in Scala?

In Scala, for loop is known as for-comprehensions. It can be used to iterate, filter and return an iterated collection. The for-comprehension looks a bit like a for-loop in imperative languages, except that it constructs a list of the results of all iterations.

Example

1. object MainObject {

2. def main(args: Array[String]) {

3. for( a <- 1 to 10 ){

4. println(a);

5. }

6. }

7. }

5) What is the breakable method in Scala?

In Scala, there is no break statement, but you can do it by using break method and importing Scala.util.control.Breaks._ package. It can break your code.

Example

1. import scala.util.control.Breaks._                  // Importing  package

2. object MainObject {

3. def main(args: Array[String]) {

4. breakable {                                 // Breakable method to avoid exception

5. for(i<-1 to 10 by 2){

6. if(i==7)

7. break                           // Break used here

8. else

9. println(i)

10. }

11. }

12. }

13. }

6) How to declare a function in Scala?

In Scala, functions are first-class values. You can store function value, pass a function as an argument and return function as a value from other function. You can create a function by using the def keyword. You must mention return type of parameters while defining a function and return type of a function is optional. If you don't specify the return type of a function, default return type is Unit.

Scala Function Declaration Syntax

1. def functionName(parameters : typeofparameters) : returntypeoffunction = {

2. // statements to be executed

3. }

7) Why do we use =(equal) operator in Scala function?

You can create a function with or without = (equal) operator. If you use it, the function will return value. If you don't use it, your function will not return anything and will work like the subroutine.

Example

1. object MainObject {

2. def main(args: Array[String]) {

3. var result = functionExample()      // Calling function

4. println(result)

5. }

6. def functionExample() = {           // Defining a function

7. var a = 10

8. a

9. }

10. }

8) What is the Function parameter with a default value in Scala?

Scala provides a feature to assign default values to function parameters. It helps in the scenario when you don't pass value during function calls. It uses default values of parameters.

Example

1. object MainObject {

2. def main(args: Array[String]) = {

3. var result1 = functionExample(15,2)     // Calling with two values

4. var result2 = functionExample(15)   // Calling with one value

5. var result3 = functionExample()     // Calling without any value

6. println(result1+"\n"+result2+"\n"+result3)

7. }

8. def functionExample(a:Int = 0, b:Int = 0):Int = {   // Parameters with default values as 0

9. a+b

10. }

11. }

9) What is a function named parameter in Scala?

In Scala function, you can specify the names of parameters while calling the function. You can pass named parameters in any order and can also pass values only.

Example

1. object MainObject {

2. def main(args: Array[String]) = {

3. var result1 = functionExample(a = 15, b = 2)    // Parameters names are passed during call

4. var result2 = functionExample(b = 15, a = 2)    // Parameters order have changed during call

5. var result3 = functionExample(15,2)             // Only values are passed during call

6. println(result1+"\n"+result2+"\n"+result3)

7. }

8. def functionExample(a:Int, b:Int):Int = {

9. a+b

10. }

11. }

10) What is a higher order function in Scala?

Higher order function is a function that either takes a function as an argument or returns a function. In other words, we can say a function which works with function is called a higher-order function.

Example

1. object MainObject {

2. def main(args: Array[String]) = {

3. functionExample(25, multiplyBy2)                   // Passing a function as parameter

4. }

5. def functionExample(a:Int, f:Int=>AnyVal):Unit = {

6. println(f(a))                                   // Calling that function

7. }

8. def multiplyBy2(a:Int):Int = {

9. a*2

10. }

11. }

11) What is function composition in Scala?

In Scala, functions can be composed from other functions. It is a process of composing in which a function represents the application of two composed functions.

Example

1. object MainObject {

2. def main(args: Array[String]) = {

3. var result = multiplyBy2(add2(10))      // Function composition

4. println(result)

5. }

6. def add2(a:Int):Int = {

7. a+2

8. }

10. def multiplyBy2(a:Int):Int = {

11. a*2

12. }

13. }

12) What is Anonymous (lambda) Function in Scala?

An anonymous function is a function that has no name but works as a function. It is good to create an anonymous function when you don't want to reuse it later. You can create anonymous function either by using ⇒ (rocket) or _ (underscore) wildcard in Scala.

Example

1. object MainObject {

2. def main(args: Array[String]) = {

3. var result1 = (a:Int, b:Int) => a+b        // Anonymous function by using => (rocket)

4. var result2 = (_:Int)+(_:Int)              // Anonymous function by using _ (underscore) wild card

5. println(result1(10,10))

6. println(result2(10,10))

7. }

8. }

13) What is a multiline expression in Scala?

Expressions those are written in multiple lines are called multiline expression. In Scala, be careful while using multiline expressions.

Example

1. def add1(a:Int, b:Int) = {

2. a

3. +b

4. }

The above program does not evaluate the complete expression and return b here.

14) What is function currying in Scala?

In Scala, the method may have multiple parameter lists. When a method is called with a fewer number of parameter lists, this will yield a function taking the missing parameter lists as its arguments.

Example

1. object MainObject {

2. def add(a:Int)(b:Int) = {

3. a+b

4. }

5. def main(args: Array[String]) = {

6. var result = add(10)(10)

7. println("10 + 10 = "+result)

8. var addIt = add(10)_

9. var result2 = addIt(3)

10. println("10 + 3 = "+result2)

11. }

12. }

15) What is a nexted function in Scala?

In Scala, you can define the function of variable length parameters. It allows you to pass any number of arguments at the time of calling the function.

Example

1. object MainObject {

2. def add(a:Int, b:Int, c:Int) = {

3. def add2(x:Int,y:Int) = {

4. x+y

5. }

6. add2(a,add2(b,c))

7. }

8. def main(args: Array[String]) = {

9. var result = add(10,10,10)

10. println(result)

11. }

12. }

16) What is an object in Scala?

The object is a real-world entity. It contains state and behavior. Laptop, car, cell phone are the real world objects. An object typically has two characteristics:

1) State: data values of an object are known as its state.

2) Behavior: functionality that an object performs is known as its behavior.

The object in Scala is an instance of a class. It is also known as runtime entity.

17) What is a class in Scala?

The class is a template or a blueprint. It is also known as a collection of objects of similar type.

In Scala, a class can contain:

1. Data member

2. Member method

3. Constructor

4. Block

5. Nested class

6. Superclass information, etc.

Example

1. class Student{

2. var id:Int = 0;                         // All fields must be initialized

3. var name:String = null;

4. }

5. object MainObject{

6. def main(args:Array[String]){

7. var s = new Student()               // Creating an object

8. println(s.id+" "+s.name);

9. }

10. }

18) What is an anonymous object in Scala?

In Scala, you can create an anonymous object. An object which has no reference name is called an anonymous object. It is good to create an anonymous object when you don't want to reuse it further.

Example

1. class Arithmetic{

2. def add(a:Int, b:Int){

3. var add = a+b;

4. println("sum = "+add);

5. }

6. }

8. object MainObject{

9. def main(args:Array[String]){

10. new Arithmetic().add(10,10);

12. }

13. }

19) What is a constructor in Scala?

In Scala, the constructor is not a special method. Scala provides primary and any number of auxiliary constructors. It is also known as default constructor.

In Scala, if you don't specify a primary constructor, the compiler creates a default primary constructor. All the statements of the class body treated as part of the constructor.

Scala Primary Constructor Example

1. class Student(id:Int, name:String){

2. def showDetails(){

3. println(id+" "+name);

4. }

5. }

7. object MainObject{

8. def main(args:Array[String]){

9. var s = new Student(101,"Rama");

10. s.showDetails()

11. }

12. }

20) What is method overloading in Scala?

Scala provides method overloading feature which allows us to define methods of the same name but having different parameters or data types. It helps to optimize code. You can achieve method overloading either by using different parameter list or different types of parameters.

Example

1. class Arithmetic{

2. def add(a:Int, b:Int){

3. var sum = a+b

4. println(sum)

5. }

6. def add(a:Int, b:Int, c:Int){

7. var sum = a+b+c

8. println(sum)

9. }

10. }

12. object MainObject{

13. def main(args:Array[String]){

14. var a  = new Arithmetic();

15. a.add(10,10);

16. a.add(10,10,10);

17. }

18. }

Table of Contents

SQL

What is Data?

RDBMS

Difference between DBMS and RDBMS

SQL Syntax

SQL Data Types

SQL Operators

SQL CREATE Database

SQL DROP Database

SQL RENAME Database

SQL Table

SQL TABLE Variable

SQL CREATE TABLE

SQL DROP TABLE

SQL DELETE TABLE

SQL RENAME TABLE

SQL TRUNCATE TABLE

SQL COPY TABLE

SQL TEMP TABLE

SQL ALTER TABLE

SQL SELECT

SQL SELECT UNIQUE

SQL SELECT DISTINCT

SQL SELECT COUNT

SQL SELECT TOP

SQL SELECT FIRST

SQL SELECT LAST

SQL SELECT RANDOM

SQL SELECT AS

SQL SELECT IN

SQL SELECT from Multiple Tables

SQL SELECT DATE

SQL SELECT SUM

SQL SELECT NULL

SQL Clause

SQL WHERE

SQL AND

SQL OR

SQL WITH CLAUSE

SQL ORDER BY Clause

SQL ORDER BY CLAUSE WITH ASCENDING ORDER

SQL Insert

SQL INSERT STATEMENT

SQL Update

SQL UPDATE DATE

SQL Delete

SQL JOIN

SQL Keys

SQL PRIMARY KEY

Unique Key in SQL

SQL vs NoSQL

SQL Interview Questions

SCALA

History of Scala

Features of Scala

Simple Program of Scala

Scala Variables and Data Types

Scala Conditional Expressions

Scala Pattern Matching

Scala while loop

Scala for loop

Scala Break

Scala Comments

Scala Functions

Scala Higher Order Functions

Scala Object and Class

Scala Singleton and Companion Object

Scala Case Classes and Case Object

Scala Constructor

Scala Method Overloading

Scala this

Scala Inheritance

Scala Method Overriding

Scala Field Overriding

Scala Final

Scala Abstract Class

Scala Trait

Scala Trait Mixins

Scala Access Modifier

Scala Array

Scala Multidimensional Array

Scala String

Scala String Methods

Scala String Interpolation

Scala Exception Handling

Scala Try Catch

Scala Finally

Scala Throw keyword

Scala Throws Keyword

Scala Custom Exception

Scala Collection

Scala Set

Scala HashSet

Scala BitSet

Scala ListSet

Scala Seq

Scala Vector

Scala List

Scala Queue

Scala Stream

Scala Maps

Scala HashMap

Scala ListMap

Scala Tuples

Scala Tuple Example: Function Return Multiple Values

Scala File handling

Scala Multithreading

Scala Thread

Scala Interview Questions