SQL :

 SQL is used by developers and DBAs (Database

Administrators) in writing Data Integration Scripts.

 It is used to deal with analytical queries to analyze the data

and get instincts from it.

 Retrieving Information

 Modification/Manipulation of data and database table such as

Insertion, Deletion and Updation.

Characteristics of SQL
o SQL is easy to learn.
o SQL is used to access data from relational database
management systems.
o SQL can execute queries against the database.
o SQL is used to describe the data.
o SQL is used to define the data in the database and manipulate
it when needed.
o SQL is used to create and drop the database and table.
o SQL is used to create a view, stored procedure, function in a
database.
o SQL allows users to set permissions on tables, procedures, and
views.

Rules to write commands:

1. Table names cannot exceed 20 characters.
2. Name of the table must be unique.
3. Field names also must be unique.
4. The field list and filed length must be enclosed in
parentheses.
5. The user must specify the field length and type.
6. The field definitions must be separated with commas.
7. SQL statements must end with a semicolon..

SQL (Structured Query Language) is used to perform operations on

the records stored in the database, such as updating records,
inserting records, deleting records, creating and modifying database
tables, views, etc.

SQL is not a database system, but it is a query language.

Suppose you want to perform the queries of SQL language on the

stored data in the database. You are required to install any database
management system in your systems, for
example, Oracle, MySQL, MongoDB, PostgreSQL, SQL Server, DB2,
etc.

What is SQL?
SQL is a short-form of the structured query language, and it is
pronounced as S-Q-L or sometimes as See-Quell.

This database language is mainly designed for maintaining the data

in relational database management systems. It is a special tool used
by data professionals for handling structured data (data which is
stored in the form of tables). It is also designed for stream
processing in RDSMS.

You can easily create and manipulate the database, access and
modify the table rows and columns, etc. This query language
became the standard of ANSI in the year of 1986 and ISO in the year
of 1987.

Why SQL?
Nowadays, SQL is widely used in data science and analytics.
Following are the reasons which explain why it is widely used:
 o The basic use of SQL for data professionals and SQL users is to
insert, update, and delete the data from the relational
database.
o SQL allows the data professionals and users to retrieve the
data from the relational database management systems.
o It also helps them to describe the structured data.
o It allows SQL users to create, drop, and manipulate the
database and its tables.
o It also helps in creating the view, stored procedure, and
functions in the relational database.
o It allows you to define the data and modify that stored data in
the relational database.
o It also allows SQL users to set the permissions or constraints
on table columns, views, and stored procedures.

History of SQL
"A Relational Model of Data for Large Shared Data Banks" was a
paper which was published by the great computer scientist "E.F.
Codd" in 1970.

The IBM researchers Raymond Boyce and Donald Chamberlin

originally developed the SEQUEL (Structured English Query
Language) after learning from the paper given by E.F. Codd. They
both developed the SQL at the San Jose Research laboratory of IBM
Corporation in 1970.

At the end of the 1970s, relational software Inc. developed their own
first SQL using the concepts of E.F. Codd, Raymond Boyce, and
Donald Chamberlin. This SQL was totally based on RDBMS.
Relational Software Inc., which is now known as Oracle Corporation,
introduced the Oracle V2 in June 1979, which is the first
implementation of SQL language. This Oracle V2 version operates on
VAX computers.
Process of SQL
When we are executing the command of SQL on any Relational
database management system, then the system automatically finds
the best routine to carry out our request, and the SQL engine
determines how to interpret that particular command.

Structured Query Language contains the following four components

in its process:

o Query Dispatcher
o Optimization Engines
o Classic Query Engine
o SQL Query Engine, etc.

A classic query engine allows data professionals and users to

maintain non-SQL queries. The architecture of SQL is shown in the
following diagram:
Some SQL Commands
The SQL commands help in creating and managing the database.
The most common SQL commands which are highly used are
mentioned below:

1. CREATE command
2. UPDATE command
3. DELETE command
4. SELECT command
5. DROP command
6. INSERT command

CREATE Command
This command helps in creating the new database, new table, table
view, and other objects of the database.

UPDATE Command
This command helps in updating or changing the stored data in the
database.

DELETE Command
This command helps in removing or erasing the saved records from
the database tables. It erases single or multiple tuples from the
tables of the database.

SELECT Command
This command helps in accessing the single or multiple rows from
one or multiple tables of the database. We can also use this
command with the WHERE clause.
DROP Command
This command helps in deleting the entire table, table view, and
other objects from the database.

INSERT Command
This command helps in inserting the data or records into the
database tables. We can easily insert the records in single as well as
multiple rows of the table.

SQL vs No-SQL

The following table describes the differences between the SQL and
NoSQL, which are necessary to understand:

SQL No-SQL

1. SQL is a relational 1. While No-SQL is a non-relational

database management or distributed database
system. management system.
2. The query language used 2. The query language used in the
in this database system is a No-SQL database systems is a
structured query language. non-declarative query language.

3. The schema of SQL 3. The schema of No-SQL

databases is predefined, databases is a dynamic schema
fixed, and static. for unstructured data.

4. These databases are 4. These databases are

vertically scalable. horizontally scalable.

5. The database type of 5. The database type of No-SQL is

SQL is in the form of tables, in the form of documents, key-
i.e., in the form of rows and value, and graphs.
columns.

6. It follows the ACID 6. It follows the BASE model.

model.

7. Complex queries are 7. NoSQL databases cannot handle

easily managed in the SQL complex queries.
database.

8. This database is not the 8. While No-SQL database is a

best choice for storing perfect option for storing
hierarchical data. hierarchical data.

9. All SQL databases 9. Many No-SQL databases do not

require object-relational require object-relational mapping.
mapping.
 10. Gauges, CircleCI, 10. Airbnb, Uber, and Kickstarter
Hootsuite, etc., are the top are the top enterprises that are
enterprises that are using using this query language.
this query language.

11. SQLite, Ms-SQL, Oracle, 11. Redis, MongoDB, Hbase,

PostgreSQL, and MySQL are BigTable, CouchDB, and Cassandra
examples of SQL database are examples of NoSQL database
systems. systems.

Advantages of SQL
SQL provides various advantages which make it more popular in the
field of data science. It is a perfect query language which allows
data professionals and users to communicate with the database.
Following are the best advantages or benefits of Structured Query
Language:

1. No programming needed

SQL does not require a large number of coding lines for managing
the database systems. We can easily access and maintain the
database by using simple SQL syntactical rules. These simple rules
make the SQL user-friendly.

2. High-Speed Query Processing

A large amount of data is accessed quickly and efficiently from the

database by using SQL queries. Insertion, deletion, and updation
operations on data are also performed in less time.

3. Standardized Language
SQL follows the long-established standards of ISO and ANSI, which
offer a uniform platform across the globe to all its users.

4. Portability

The structured query language can be easily used in desktop

computers, laptops, tablets, and even smartphones. It can also be
used with other applications according to the user's requirements.

5. Interactive language

We can easily learn and understand the SQL language. We can also
use this language for communicating with the database because it is
a simple query language. This language is also used for receiving
the answers to complex queries in a few seconds.

6. More than one Data View

The SQL language also helps in making the multiple views of the
database structure for the different database users.

Disadvantages of SQL
With the advantages of SQL, it also has some disadvantages, which
are as follows:

1. Cost

The operation cost of some SQL versions is high. That's why some
programmers cannot use the Structured Query Language.

2. Interface is Complex

Another big disadvantage is that the interface of Structured query

language is difficult, which makes it difficult for SQL users to use and
manage it.

3. Partial Database control

The business rules are hidden. So, the data professionals and users
who are using this query language cannot have full database
control.
 SQL Syntax
When you want to do some operations on the data in the database,
then you must have to write the query in the predefined syntax of
SQL.

The syntax of the structured query language is a unique set of rules

and guidelines, which is not case-sensitive. Its Syntax is defined and
maintained by the ISO and ANSI standards.

Following are some most important points about the SQL syntax
which are to remember:

o You can write the keywords of SQL in both uppercase and

lowercase, but writing the SQL keywords in uppercase
improves the readability of the SQL query.
o SQL statements or syntax are dependent on text lines. We can
place a single SQL statement on one or multiple text lines.
o You can perform most of the action in a database with SQL
statements.
o SQL syntax depends on relational algebra and tuple relational
calculus.

SQL Statement
SQL statements tell the database what operation you want to
perform on the structured data and what information you would like
to access from the database.

The statements of SQL are very simple and easy to use and
understand. They are like plain English but with a particular syntax.

Simple Example of SQL statement:

1. SELECT "column_name" FROM "table_name";

Each SQL statement begins with any of the SQL keywords and ends
with the semicolon (;). The semicolon is used in the SQL for
separating the multiple Sql statements which are going to execute
in the same call. In this SQL tutorial, we will use the semicolon (;) at
the end of each SQL query or statement.

Most Important SQL Commands and Statements

1. Select Statement
2. Update Statement
3. Delete Statement
4. Create Table Statement
5. Alter Table Statement
6. Drop Table Statement
7. Create Database Statement
8. Drop Database Statement
9. Insert Into Statement
10. Truncate Table Statement
11. Describe Statement
12. Distinct Clause
13. Commit Statement
14. Rollback Statement
15. Create Index Statement
16. Drop Index Statement
17. Use Statement

Let's discuss each statement in short one by one with syntax

and one example:

1. SELECT Statement
This SQL statement reads the data from the SQL database and
shows it as the output to the database user.

Syntax of SELECT Statement:

1. SELECT column_name1, column_name2, .…, column_nameN
2. [ FROM table_name ]
3. [ WHERE condition ]
4. [ ORDER BY order_column_name1 [ ASC | DESC ], .... ];

Example of SELECT Statement:

1. SELECT Emp_ID, First_Name, Last_Name, Salary, City

2. FROM Employee_details
3. WHERE Salary = 100000
4. ORDER BY Last_Name

This example shows the Emp_ID, First_Name, Last_Name,

Salary, and City of those employees from
the Employee_details table whose Salary is 100000. The output
shows all the specified details according to the ascending
alphabetical order of Last_Name.

3. UPDATE Statement
This SQL statement changes or modifies the stored data in the SQL
database.

Syntax of UPDATE Statement:

1. UPDATE table_name
2. SET column_name1 = new_value_1, column_name2 = new_val
ue_2, ...., column_nameN = new_value_N
3. [ WHERE CONDITION ];

Example of UPDATE Statement:

1. UPDATE Employee_details
2. SET Salary = 100000
3. WHERE Emp_ID = 10;
 This example changes the Salary of those employees of
the Employee_details table whose Emp_ID is 10 in the table.

3. DELETE Statement
This SQL statement deletes the stored data from the SQL database.

Syntax of DELETE Statement:

1. DELETE FROM table_name

2. [ WHERE CONDITION ];

Example of DELETE Statement:

1. DELETE FROM Employee_details

2. WHERE First_Name = 'Sumit';

This example deletes the record of those employees from

the Employee_details table whose First_Name is Sumit in the
table.

4. CREATE TABLE Statement

This SQL statement creates the new table in the SQL database.

Syntax of CREATE TABLE Statement:

1. CREATE TABLE table_name

2. (
3. column_name1 data_type [column1 constraint(s)],
4. column_name2 data_type [column2 constraint(s)],
5. .....
6. .....,
7. column_nameN data_type [columnN constraint(s)],
8. PRIMARY KEY(one or more col)
9. );
 Example of CREATE TABLE Statement:

1. CREATE TABLE Employee_details(

2. Emp_Id NUMBER(4) NOT NULL,
3. First_name VARCHAR(30),
4. Last_name VARCHAR(30),
5. Salary Money,
6. City VARCHAR(30),
7. PRIMARY KEY (Emp_Id)
8. );

This example creates the table Employee_details with five

columns or fields in the SQL database. The fields in the table
are Emp_Id, First_Name, Last_Name, Salary, and City.
The Emp_Id column in the table acts as a primary key, which
means that the Emp_Id column cannot contain duplicate values and
null values.

5. ALTER TABLE Statement

This SQL statement adds, deletes, and modifies the columns of the
table in the SQL database.

Syntax of ALTER TABLE Statement:

1. ALTER TABLE table_name ADD column_name datatype[(size)

];

The above SQL alter statement adds the column with its datatype in
the existing database table.

1. ALTER TABLE table_name MODIFY column_name column_dat

atype[(size)];

The above 'SQL alter statement' renames the old column name to
the new column name of the existing database table.
1. ALTER TABLE table_name DROP COLUMN column_name;

The above SQL alter statement deletes the column of the existing
database table.

Example of ALTER TABLE Statement:

1. ALTER TABLE Employee_details

2. ADD Designation VARCHAR(18);

This example adds the new field whose name is Designation with
size 18 in the Employee_details table of the SQL database.

6. DROP TABLE Statement

This SQL statement deletes or removes the table and the structure,
views, permissions, and triggers associated with that table.

Syntax of DROP TABLE Statement:

1. DROP TABLE [ IF EXISTS ]

2. table_name1, table_name2, ……, table_nameN;

The above syntax of the drop statement deletes specified tables

completely if they exist in the database.

Example of DROP TABLE Statement:

1. DROP TABLE Employee_details;

This example drops the Employee_details table if it exists in the

SQL database. This removes the complete information if available in
the table.

7. CREATE DATABASE Statement

This SQL statement creates the new database in the database
management system.

Syntax of CREATE DATABASE Statement:

1. CREATE DATABASE database_name;

Example of CREATE DATABASE Statement:

1. CREATE DATABASE Company;

The above example creates the company database in the system.

8. DROP DATABASE Statement

This SQL statement deletes the existing database with all the data
tables and views from the database management system.

Syntax of DROP DATABASE Statement:

1. DROP DATABASE database_name;

Example of DROP DATABASE Statement:

1. DROP DATABASE Company;

The above example deletes the company database from the system.

9. INSERT INTO Statement

This SQL statement inserts the data or records in the existing table
of the SQL database. This statement can easily insert single and
multiple records in a single query statement.

Syntax of insert a single record:

1. INSERT INTO table_name

2. (
3. column_name1,
4. column_name2, .…,
5. column_nameN
6. )
7. VALUES
8. (value_1,
9. value_2, ..…,
10. value_N
11. );

Example of insert a single record:

1. INSERT INTO Employee_details

2. (
3. Emp_ID,
4. First_name,
5. Last_name,
6. Salary,
7. City
8. )
9. VALUES
10. (101,
11. Akhil,
12. Sharma,
13. 40000,
14. Bangalore
15. );

This example inserts 101 in the first column, Akhil in the second
column, Sharma in the third column, 40000 in the fourth column,
and Bangalore in the last column of the table Employee_details.

Syntax of inserting a multiple records in a single query:

1. INSERT INTO table_name

2. ( column_name1, column_name2, .…, column_nameN)
3. VALUES (value_1, value_2, ..…, value_N), (value_1, value_2, ..
…, value_N),….;
 Example of inserting multiple records in a single query:

1. INSERT INTO Employee_details

2. ( Emp_ID, First_name, Last_name, Salary, City )
3. VALUES (101, Amit, Gupta, 50000, Mumbai), (101, John, Agga
rwal, 45000, Calcutta), (101, Sidhu, Arora, 55000, Mumbai);

This example inserts the records of three employees in

the Employee_details table in the single query statement.

10. TRUNCATE TABLE Statement

This SQL statement deletes all the stored records from the table of
the SQL database.

Syntax of TRUNCATE TABLE Statement:

1. TRUNCATE TABLE table_name;

Example of TRUNCATE TABLE Statement:

1. TRUNCATE TABLE Employee_details;

This example deletes the record of all employees from the

Employee_details table of the database.

11. DESCRIBE Statement

This SQL statement tells something about the specified table or view
in the query.

Syntax of DESCRIBE Statement:

1. DESCRIBE table_name | view_name;

Example of DESCRIBE Statement:

1. DESCRIBE Employee_details;
 This example explains the structure and other details about
the Employee_details table.

12. DISTINCT Clause

This SQL statement shows the distinct values from the specified
columns of the database table. This statement is used with
the SELECT keyword.

Syntax of DISTINCT Clause:

1. SELECT DISTINCT column_name1, column_name2, ...

2. FROM table_name;

Example of DISTINCT Clause:

1. SELECT DISTINCT City, Salary

2. FROM Employee_details;

This example shows the distinct values of

the City and Salary column from the Employee_details table.

13. COMMIT Statement

This SQL statement saves the changes permanently, which are done
in the transaction of the SQL database.

Syntax of COMMIT Statement:

1. COMMIT

Example of COMMIT Statement:

1. DELETE FROM Employee_details

2. WHERE salary = 30000;
3. COMMIT;
 This example deletes the records of those employees
whose Salary is 30000 and then saves the changes permanently in
the database.

14. ROLLBACK Statement

This SQL statement undo the transactions and operations which are
not yet saved to the SQL database.

Syntax of ROLLBACK Statement:

1. ROLLBACK

Example of ROLLBACK Statement:

1. DELETE FROM Employee_details

2. WHERE City = Mumbai;
3. ROLLBACK;

This example deletes the records of those employees

whose City is Mumbai and then undo the changes in the database.

15. CREATE INDEX Statement

This SQL statement creates the new index in the SQL database
table.

Syntax of CREATE INDEX Statement:

1. CREATE INDEX index_name

2. ON table_name ( column_name1, column_name2, …, column_n
ameN );

Example of CREATE INDEX Statement:

1. CREATE INDEX idx_First_Name

2. ON employee_details (First_Name);
 This example creates an index idx_First_Name on
the First_Name column of the Employee_details table.

16. DROP INDEX Statement

This SQL statement deletes the existing index of the SQL database
table.

Syntax of DROP INDEX Statement:

1. DROP INDEX index_name;

Example of DROP INDEX Statement:

1. DROP INDEX idx_First_Name;

This example deletes the index idx_First_Name from the SQL

database.

17. USE Statement

This SQL statement selects the existing SQL database. Before
performing the operations on the database table, you have to select
the database from the multiple existing databases.

Syntax of USE Statement:

1. USE database_name;

Example of USE DATABASE Statement:

1. USE Company;

This example uses the company database.

SQL Datatype
 o SQL Datatype is used to define the values that a column can
contain.
o Every column is required to have a name and data type in the
database table.

Datatype of SQL:

1. Binary Datatypes
There are Three types of binary Datatypes which are given below:

Data Description
Type

binary It has a maximum length of 8000 bytes. It contains

fixed-length binary data.

varbina It has a maximum length of 8000 bytes. It contains

ry variable-length binary data.
 image It has a maximum length of 2,147,483,647 bytes.
It contains variable-length binary data.

2. Approximate Numeric Datatype :

The subtypes are given below:

Data From To Description

type

float -1.79E + 1.79E + It is used to specify a floating-point

308 308 value e.g. 6.2, 2.9 etc.

real -3.40e + 3.40E + It specifies a single precision floating

38 38 point number

3. Exact Numeric Datatype

The subtypes are given below:

Data type Description

int It is used to specify an integer value.

smallint It is used to specify small integer value.

bit It has the number of bits to store.

decimal It specifies a numeric value that can have a decimal

number.
numeric It is used to specify a numeric value.

4. Character String Datatype

The subtypes are given below:

Data type Description

char It has a maximum length of 8000

characters. It contains Fixed-length non-
unicode characters.

varcha It has a maximum length of 8000

r characters. It contains variable-length
non-unicode characters.

text It has a maximum length of

2,147,483,647 characters. It contains
variable-length non-unicode characters.

5. Date and time Datatypes

The subtypes are given below:

Datatype Description

date It is used to store the year, month, and days value.

time It is used to store the hour, minute, and second values.

timestam It stores the year, month, day, hour, minute, and the second
p value.

SQL Commands
o SQL commands are instructions. It is used to communicate
with the database. It is also used to perform specific tasks,
functions, and queries of data.
o SQL can perform various tasks like create a table, add data to
tables, drop the table, modify the table, set permission for
users.

Types of SQL Commands

There are five types of SQL commands: DDL, DML, DCL, TCL, and
DQL.
1. Data Definition Language (DDL)
o DDL changes the structure of the table like creating a table,
deleting a table, altering a table, etc.
o All the command of DDL are auto-committed that means it
permanently save all the changes in the database.

Here are some commands that come under DDL:

o CREATE
o ALTER
o DROP
o TRUNCATE

a. CREATE It is used to create a new table in the database.

Syntax:
1. CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,....])
;

Example:

1. CREATE TABLE EMPLOYEE(Name VARCHAR2(20), Email VARCH

AR2(100), DOB DATE);

b. DROP: It is used to delete both the structure and record stored in

the table.

Syntax

1. DROP TABLE table_name;

Example

1. DROP TABLE EMPLOYEE;

c. ALTER: It is used to alter the structure of the database. This

change could be either to modify the characteristics of an existing
attribute or probably to add a new attribute.

Syntax:

To add a new column in the table

1. ALTER TABLE table_name ADD column_name COLUMN-

definition;

To modify existing column in the table:

1. ALTER TABLE table_name MODIFY(column_definitions....);

EXAMPLE

1. ALTER TABLE STU_DETAILS ADD(ADDRESS VARCHAR2(20));

2. ALTER TABLE STU_DETAILS MODIFY (NAME VARCHAR2(20));
 d. TRUNCATE: It is used to delete all the rows from the table and
free the space containing the table.

Syntax:

1. TRUNCATE TABLE table_name;

Example:

1. TRUNCATE TABLE EMPLOYEE;

2. Data Manipulation Language

o DML commands are used to modify the database. It is
responsible for all form of changes in the database.
o The command of DML is not auto-committed that means it
can't permanently save all the changes in the database. They
can be rollback.

Here are some commands that come under DML:

o INSERT
o UPDATE
o DELETE

a. INSERT: The INSERT statement is a SQL query. It is used to insert

data into the row of a table.

Syntax:

1. INSERT INTO TABLE_NAME

2. (col1, col2, col3,.... col N)
3. VALUES (value1, value2, value3, .... valueN);

Or

1. INSERT INTO TABLE_NAME

2. VALUES (value1, value2, value3, .... valueN);
 For example:

1. INSERT INTO javatpoint (Author, Subject) VALUES ("Sonoo", "D

BMS");

b. UPDATE: This command is used to update or modify the value of

a column in the table.

Syntax:

1. UPDATE table_name SET [column_name1= value1,...column_n

ameN = valueN] [WHERE CONDITION]

For example:

1. UPDATE students
2. SET User_Name = 'Sonoo'
3. WHERE Student_Id = '3'

c. DELETE: It is used to remove one or more row from a table.

Syntax:

1. DELETE FROM table_name [WHERE condition];

For example:

1. DELETE FROM javatpoint

2. WHERE Author="Sonoo";

3. Data Control Language

DCL commands are used to grant and take back authority from any
database user.

Here are some commands that come under DCL:

o Grant
o Revoke
 a. Grant: It is used to give user access privileges to a database.

Example

1. GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTH

ER_USER;

b. Revoke: It is used to take back permissions from the user.

Example

1. REVOKE SELECT, UPDATE ON MY_TABLE FROM USER1, USER2;

4. Transaction Control Language

TCL commands can only use with DML commands like INSERT,
DELETE and UPDATE only.

These operations are automatically committed in the database

that's why they cannot be used while creating tables or dropping
them.

Here are some commands that come under TCL:

o COMMIT
o ROLLBACK
o SAVEPOINT

a. Commit: Commit command is used to save all the transactions

to the database.

Syntax:

1. COMMIT;

Example:

1. DELETE FROM CUSTOMERS

2. WHERE AGE = 25;
3. COMMIT;

b. Rollback: Rollback command is used to undo transactions that

have not already been saved to the database.

Syntax:

1. ROLLBACK;

Example:

1. DELETE FROM CUSTOMERS

2. WHERE AGE = 25;
3. ROLLBACK;

c. SAVEPOINT: It is used to roll the transaction back to a certain

point without rolling back the entire transaction.

Syntax:

1. SAVEPOINT SAVEPOINT_NAME;

5. Data Query Language

DQL is used to fetch the data from the database.

It uses only one command:

o SELECT

a. SELECT: This is the same as the projection operation of relational

algebra. It is used to select the attribute based on the condition
described by WHERE clause.

Syntax:

1. SELECT expressions
2. FROM TABLES
3. WHERE conditions;
 For example:

1. SELECT emp_name
2. FROM employee
3. WHERE age > 20;

SQL Operator
There are various types of SQL operator:

SQL Arithmetic Operators

Let's assume 'variable a' and 'variable b'. Here, 'a' contains 20 and 'b' contains 10.

Operator Description Example

+ It adds the value of both operands. a+b will
give 30

- It is used to subtract the right-hand operand a-b will

from the left-hand operand. give 10

* It is used to multiply the value of both a*b will

operands. give 200

/ It is used to divide the left-hand operand by a/b will

the right-hand operand. give 2

% It is used to divide the left-hand operand by a%b will

the right-hand operand and returns give 0
reminder.

SQL Comparison Operators:

Let's assume 'variable a' and 'variable b'. Here, 'a' contains 20 and 'b' contains 10.

Operator Description Example

= It checks if two operands values are equal or (a=b) is

not, if the values are queal then condition not true
becomes true.

!= It checks if two operands values are equal or (a!=b) is

not, if values are not equal, then condition true
becomes true.

<> It checks if two operands values are equal or (a<>b) is

 not, if values are not equal then condition true
becomes true.

> It checks if the left operand value is greater (a>b) is

than right operand value, if yes then condition not true
becomes true.

< It checks if the left operand value is less than (a<b) is

right operand value, if yes then condition true
becomes true.

>= It checks if the left operand value is greater (a>=b) is

than or equal to the right operand value, if yes not true
then condition becomes true.

<= It checks if the left operand value is less than or (a<=b) is

equal to the right operand value, if yes then true
condition becomes true.

!< It checks if the left operand value is not less (a!=b) is

than the right operand value, if yes then not true
condition becomes true.

!> It checks if the left operand value is not greater (a!>b) is

than the right operand value, if yes then true
condition becomes true.

SQL Logical Operators

There is the list of logical operator used in SQL:

Operator Description
ALL It compares a value to all values in another value set.

AND It allows the existence of multiple conditions in an SQL

statement.

ANY It compares the values in the list according to the condition.

BETWEE It is used to search for values that are within a set of values.
N

IN It compares a value to that specified list value.

NOT It reverses the meaning of any logical operator.

OR It combines multiple conditions in SQL statements.

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

table.

LIKE It compares a value to similar values using wildcard operator.

SQL Table
o SQL Table is a collection of data which is organized in terms of
rows and columns. In DBMS, the table is known as relation and
row as a tuple.
 o Table is a simple form of data storage. A table is also
considered as a convenient representation of relations.

Let's see an example of the EMPLOYEE table:

EMP_ID EMP_NAME CITY PHONE_NO

1 Kristen Washington 7289201223

2 Anna Franklin 9378282882

3 Jackson Bristol 9264783838

4 Kellan California 7254728346

5 Ashley Hawaii 9638482678

In the above table, "EMPLOYEE" is the table name, "EMP_ID",

"EMP_NAME", "CITY", "PHONE_NO" are the column names. The
combination of data of multiple columns forms a row, e.g., 1,
"Kristen", "Washington" and 7289201223 are the data of one row.

Operation on Table
1. Create table
2. Drop table
3. Delete table
4. Rename table

SQL Create Table

SQL create table is used to create a table in the database. To define
the table, you should define the name of the table and also define
its columns and column's data type.
 Syntax

1. create table "table_name"

2. ("column1" "data type",
3. "column2" "data type",
4. "column3" "data type",
5. ...
6. "columnN" "data type");

SQL SELECT Statement

In SQL, the SELECT statement is used to query or retrieve data from
a table in the database. The returns data is stored in a table, and
the result table is known as result-set.

Syntax

1. SELECT column1, column2, ...

2. FROM table_name;

Here, the expression is the field name of the table that you want to
select data from.

Use the following syntax to select all the fields available in the table:

1. SELECT * FROM table_name;

Example:

EMPLOYEE

EMP_ID EMP_NAME CITY PHONE_NO SALARY

1 Kristen Chicago 9737287378 150000

2 Russell Austin 9262738271 200000

3 Angelina Denver 9232673822 600000

 4 Robert Washingto 9367238263 350000
n

5 Christian Los angels 7253847382 260000

To fetch the EMP_ID of all the employees, use the following query:

1. SELECT EMP_ID FROM EMPLOYEE;

Output

EMP_ID

To fetch the EMP_NAME and SALARY, use the following query:

1. SELECT EMP_NAME, SALARY FROM EMPLOYEE;

EMP_NAME SALARY

Kristen 150000

Russell 200000

Angelina 600000

Robert 350000

Christian 260000
 To fetch all the fields from the EMPLOYEE table, use the following
query:

1. SELECT * FROM EMPLOYEE

Output

EMP_ID EMP_NAME CITY PHONE_NO SALARY

1 Kristen Chicago 9737287378 150000

2 Russell Austin 9262738271 200000

3 Angelina Denver 9232673822 600000

4 Robert Washingto 9367238263 350000

n

5 Christian Los angels 7253847382 260000

xample

1. SQL> CREATE TABLE EMPLOYEE (

2. EMP_ID INT NOT NULL,
3. EMP_NAME VARCHAR (25) NOT NULL,
4. PHONE_NO INT NOT NULL,
5. ADDRESS CHAR (30),
6. PRIMARY KEY (ID)
7. );

If you create the table successfully, you can verify the table by
looking at the message by the SQL server. Else you can use DESC
command as follows:

SQL> DESC EMPLOYEE;

Field Type Null Key Default Extra

 EMP_ID int(11) NO PRI NULL

EMP_NAME varchar(25) NO NULL

PHONE_NO NO int(11) NULL

ADDRESS YES NULL char(30)

o 4 rows in set (0.35 sec)

Now you have an EMPLOYEE table in the database, and you can use
the stored information related to the employees.

Drop table
A SQL drop table is used to delete a table definition and all the data
from a table. When this command is executed, all the information
available in the table is lost forever, so you have to very careful
while using this command.

Syntax

1. DROP TABLE "table_name";

Firstly, you need to verify the EMPLOYEE table using the following
command:

1. SQL> DESC EMPLOYEE;

Field Type Null Key Default Extra

 EMP_ID int(11) NO PRI NULL

EMP_NAME varchar(25) NO NULL

PHONE_NO NO int(11) NULL

ADDRESS YES NULL char(30)

o 4 rows in set (0.35 sec)

This table shows that EMPLOYEE table is available in the database,

so we can drop it as follows:

1. SQL>DROP TABLE EMPLOYEE;

Now, we can check whether the table exists or not using the
following command:

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

As this shows that the table is dropped, so it doesn't display it.

SQL DELETE table

In SQL, DELETE statement is used to delete rows from a table. We
can use WHERE condition to delete a specific row from a table. If
you want to delete all the records from the table, then you don't
need to use the WHERE clause.

Syntax

1. DELETE FROM table_name WHERE condition;

Example

Suppose, the EMPLOYEE table having the following records:

 EMP_ID EMP_NAME CITY PHONE_NO SALARY

1 Kristen Chicago 9737287378 150000

2 Russell Austin 9262738271 200000

3 Denzel Boston 7353662627 100000

4 Angelina Denver 9232673822 600000

5 Robert Washington 9367238263 350000

6 Christian Los angels 7253847382 260000

The following query will DELETE an employee whose ID is 2.

1. SQL> DELETE FROM EMPLOYEE

2. WHERE EMP_ID = 3;

Now, the EMPLOYEE table would have the following records.

EMP_ID EMP_NAME CITY PHONE_NO SALARY

1 Kristen Chicago 9737287378 150000

2 Russell Austin 9262738271 200000

4 Angelina Denver 9232673822 600000

5 Robert Washington 9367238263 350000

 6 Christian Los angels 7253847382 260000

If you don't specify the WHERE condition, it will remove all the rows
from the table.

1. DELETE FROM EMPLOYEE;

Now, the EMPLOYEE table would not have any records.

SQL INSERT Statement

The SQL INSERT statement is used to insert a single or multiple data
in a table. In SQL, You can insert the data in two ways:

1. Without specifying column name

2. By specifying column name

Sample Table
EMPLOYEE

EMP_ID EMP_NAME CITY SALARY AGE

1 Angelina Chicago 200000 30

2 Robert Austin 300000 26

3 Christian Denver 100000 42

4 Kristen Washington 500000 29

 5 Russell Los angels 200000 36

1. Without specifying column name

If you want to specify all column values, you can specify or ignore
the column values.

Syntax

1. INSERT INTO TABLE_NAME

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

Query

1. INSERT INTO EMPLOYEE VALUES (6, 'Marry', 'Canada', 600000,

48);

Output: After executing this query, the EMPLOYEE table will look like:

EMP_ID EMP_NAME CITY SALARY AGE

1 Angelina Chicago 200000 30

2 Robert Austin 300000 26

3 Christian Denver 100000 42

4 Kristen Washington 500000 29

5 Russell Los angels 200000 36

6 Marry Canada 600000 48

 2. By specifying column name
To insert partial column values, you must have to specify the
column names.

Syntax

1. INSERT INTO TABLE_NAME

2. [(col1, col2, col3,.... col N)]
3. VALUES (value1, value2, value 3, .... Value N);

Query

1. INSERT INTO EMPLOYEE (EMP_ID, EMP_NAME, AGE) VALUES (7,

'Jack', 40);

Output: After executing this query, the table will look like:

EMP_ID EMP_NAME CITY SALARY AGE

1 Angelina Chicago 200000 30

2 Robert Austin 300000 26

3 Christian Denver 100000 42

4 Kristen Washington 500000 29

5 Russell Los angels 200000 36

6 Marry Canada 600000 48

7 Jack null null 40

 Note: In SQL INSERT query, if you add values for all columns then there is no
need to specify the column name. But, you must be sure that you are entering
the values in the same order as the column exists.

SQL Update Statement

The SQL UPDATE statement is used to modify the data that is
already in the database. The condition in the WHERE clause decides
that which row is to be updated.

Syntax

1. UPDATE table_name
2. SET column1 = value1, column2 = value2, ...
3. WHERE condition;

Sample Table
EMPLOYEE

EMP_ID EMP_NAME CITY SALARY AGE

1 Angelina Chicago 200000 30

2 Robert Austin 300000 26

3 Christian Denver 100000 42

4 Kristen Washington 500000 29

5 Russell Los angels 200000 36

 6 Marry Canada 600000 48

Updating single record

Update the column EMP_NAME and set the value to 'Emma' in the
row where SALARY is 500000.

Syntax

1. UPDATE table_name
2. SET column_name = value
3. WHERE condition;

Query

1. UPDATE EMPLOYEE
2. SET EMP_NAME = 'Emma'
3. WHERE SALARY = 500000;

Output: After executing this query, the EMPLOYEE table will look like:

EMP_ID EMP_NAME CITY SALARY AGE

1 Angelina Chicago 200000 30

2 Robert Austin 300000 26

3 Christian Denver 100000 42

4 Emma Washington 500000 29

 5 Russell Los angels 200000 36

6 Marry Canada 600000 48

Updating multiple records

If you want to update multiple columns, you should separate each
field assigned with a comma. In the EMPLOYEE table, update the
column EMP_NAME to 'Kevin' and CITY to 'Boston' where EMP_ID is 5.

Syntax

1. UPDATE table_name
2. SET column_name = value1, column_name2 = value2
3. WHERE condition;

Query

1. UPDATE EMPLOYEE
2. SET EMP_NAME = 'Kevin', City = 'Boston'
3. WHERE EMP_ID = 5;

Output

EMP_ID EMP_NAME CITY SALARY AGE

1 Angelina Chicago 200000 30

2 Robert Austin 300000 26

3 Christian Denver 100000 42

4 Kristen Washington 500000 29

 5 Kevin Boston 200000 36

6 Marry Canada 600000 48

Without use of WHERE clause

If you want to update all row from a table, then you don't need to
use the WHERE clause. In the EMPLOYEE table, update the column
EMP_NAME as 'Harry'.

Syntax

1. UPDATE table_name
2. SET column_name = value1;

Query

1. UPDATE EMPLOYEE
2. SET EMP_NAME = 'Harry';

Output

EMP_ID EMP_NAME CITY SALARY AGE

1 Harry Chicago 200000 30

2 Harry Austin 300000 26

3 Harry Denver 100000 42

4 Harry Washington 500000 29

 5 Harry Los angels 200000 36

6 Harry Canada 600000 48

SQL DELETE Statement

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

Syntax

1. DELETE FROM table_name WHERE some_condition;

Sample Table
EMPLOYEE

EMP_ID EMP_NAME CITY SALARY AGE

1 Angelina Chicago 200000 30

2 Robert Austin 300000 26

3 Christian Denver 100000 42

4 Kristen Washington 500000 29

5 Russell Los angels 200000 36

 6 Marry Canada 600000 48

Deleting Single Record

Delete the row from the table EMPLOYEE where EMP_NAME =
'Kristen'. This will delete only the fourth row.

Query

1. DELETE FROM EMPLOYEE

2. WHERE EMP_NAME = 'Kristen';

Output: After executing this query, the EMPLOYEE table will look like:

EMP_ID EMP_NAME CITY SALARY AGE

1 Angelina Chicago 200000 30

2 Robert Austin 300000 26

3 Christian Denver 100000 42

5 Russell Los angels 200000 36

6 Marry Canada 600000 48

Deleting Multiple Record

Delete the row from the EMPLOYEE table where AGE is 30. This will
delete two rows(first and third row).

Query
1. DELETE FROM EMPLOYEE WHERE AGE= 30;

Output: After executing this query, the EMPLOYEE table will look like:

EMP_ID EMP_NAME CITY SALARY AGE

2 Robert Austin 300000 26

3 Christian Denver 100000 42

5 Russell Los angels 200000 36

6 Marry Canada 600000 48

Delete all of the records

Delete all the row from the EMPLOYEE table. After this, no records
left to display. The EMPLOYEE table will become empty.

Syntax

1. DELETE * FROM table_name;

2. or
3. DELETE FROM table_name;

Query

1. DELETE FROM EMPLOYEE;

Output: After executing this query, the EMPLOYEE table will look like:

EMP_ID EMP_NAME CITY SALARY AGE

Note: Using the condition in the WHERE clause, we can delete single as well
as multiple records. If you want to delete all the records from the table, then
you don't need to use the WHERE clause.

Views in SQL
o Views in SQL are considered as a virtual table. A view also
contains rows and columns.
o To create the view, we can select the fields from one or more
tables present in the database.
o A view can either have specific rows based on certain condition
or all the rows of a table.

Sample table:
Student_Detail

STU_ID NAME ADDRESS

1 Stephan Delhi

2 Kathrin Noida

3 David Ghaziabad

4 Alina Gurugram

Student_Marks

STU_ID NAME MARKS AGE

 1 Stephan 97 19

2 Kathrin 86 21

3 David 74 18

4 Alina 90 20

5 John 96 18

1. Creating view
A view can be created using the CREATE VIEW statement. We can
create a view from a single table or multiple tables.

Syntax:

1. CREATE VIEW view_name AS

2. SELECT column1, column2.....
3. FROM table_name
4. WHERE condition;

2. Creating View from a single table

In this example, we create a View named DetailsView from the table
Student_Detail.

Query:

1. CREATE VIEW DetailsView AS

2. SELECT NAME, ADDRESS
3. FROM Student_Details
4. WHERE STU_ID < 4;
 Just like table query, we can query the view to view the data.

1. SELECT * FROM DetailsView;

Output:

NAME ADDRESS

Stephan Delhi

Kathrin Noida

David Ghaziabad

3. Creating View from multiple tables

View from multiple tables can be created by simply include multiple
tables in the SELECT statement.

In the given example, a view is created named MarksView from two

tables Student_Detail and Student_Marks.

Query:

1. CREATE VIEW MarksView AS

2. SELECT Student_Detail.NAME, Student_Detail.ADDRESS, Stude
nt_Marks.MARKS
3. FROM Student_Detail, Student_Mark
4. WHERE Student_Detail.NAME = Student_Marks.NAME;

To display data of View MarksView:

1. SELECT * FROM MarksView;

NAME ADDRESS MARKS

 Stephan Delhi 97

Kathrin Noida 86

David Ghaziabad 74

Alina Gurugram 90

4. Deleting View
A view can be deleted using the Drop View statement.

Syntax

1. DROP VIEW view_name;

Example:

If we want to delete the View MarksView, we can do this as:

1. DROP VIEW MarksView;

SQL Index
o Indexes are special lookup tables. It is used to retrieve data
from the database very fast.
o An Index is used to speed up select queries and where clauses.
But it shows down the data input with insert and update
statements. Indexes can be created or dropped without
affecting the data.
 o An index in a database is just like an index in the back of a
book.
o For example: When you reference all pages in a book that
discusses a certain topic, you first have to refer to the index,
which alphabetically lists all the topics and then referred to one
or more specific page numbers.

1. Create Index statement

It is used to create an index on a table. It allows duplicate value.

Syntax

1. CREATE INDEX index_name

2. ON table_name (column1, column2, ...);

Example

1. CREATE INDEX idx_name

2. ON Persons (LastName, FirstName);

2. Unique Index statement

It is used to create a unique index on a table. It does not allow
duplicate value.

Syntax

1. CREATE UNIQUE INDEX index_name

2. ON table_name (column1, column2, ...);

Example

1. CREATE UNIQUE INDEX websites_idx

2. ON websites (site_name);
 3. Drop Index Statement
It is used to delete an index in a table.

Syntax

1. DROP INDEX index_name;

Example

1. DROP INDEX websites_idx;

SQL Sub Query

A Subquery is a query within another SQL query and embedded
within the WHERE clause.

Important Rule:

o A subquery can be placed in a number of SQL clauses like

WHERE clause, FROM clause, HAVING clause.
o You can use Subquery with SELECT, UPDATE, INSERT, DELETE
statements along with the operators like =, <, >, >=, <=, IN,
BETWEEN, etc.
o A subquery is a query within another query. The outer query is
known as the main query, and the inner query is known as a
subquery.
o Subqueries are on the right side of the comparison operator.
o A subquery is enclosed in parentheses.
o In the Subquery, ORDER BY command cannot be used. But
GROUP BY command can be used to perform the same function
as ORDER BY command.
 1. Subqueries with the Select Statement
SQL subqueries are most frequently used with the Select statement.

Syntax

1. SELECT column_name
2. FROM table_name
3. WHERE column_name expression operator
4. ( SELECT column_name from table_name WHERE ... );

Example

Consider the EMPLOYEE table have the following records:

ID NAME AGE ADDRESS SALARY

1 John 20 US 2000.00

2 Stephan 26 Dubai 1500.00

3 David 27 Bangkok 2000.00

4 Alina 29 UK 6500.00

5 Kathrin 34 Bangalore 8500.00

6 Harry 42 China 4500.00

7 Jackson 25 Mizoram 10000.00

The subquery with a SELECT statement will be:

1. SELECT *
2. FROM EMPLOYEE
3. WHERE ID IN (SELECT ID
4. FROM EMPLOYEE
5. WHERE SALARY > 4500);

This would produce the following result:

ID NAME AGE ADDRESS SALARY

4 Alina 29 UK 6500.00

5 Kathrin 34 Bangalore 8500.00

7 Jackson 25 Mizoram 10000.00

2. Subqueries with the INSERT Statement

o SQL subquery can also be used with the Insert statement. In
the insert statement, data returned from the subquery is used
to insert into another table.
o In the subquery, the selected data can be modified with any of
the character, date functions.

Syntax:

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

2. SELECT *
3. FROM table_name
4. WHERE VALUE OPERATOR

Example
 Consider a table EMPLOYEE_BKP with similar as EMPLOYEE.

Now use the following syntax to copy the complete EMPLOYEE table
into the EMPLOYEE_BKP table.

1. INSERT INTO EMPLOYEE_BKP

2. SELECT * FROM EMPLOYEE
3. WHERE ID IN (SELECT ID
4. FROM EMPLOYEE);

3. Subqueries with the UPDATE Statement

The subquery of SQL can be used in conjunction with the Update
statement. When a subquery is used with the Update statement,
then either single or multiple columns in a table can be updated.

Syntax

1. UPDATE table
2. SET column_name = new_value
3. WHERE VALUE OPERATOR
4. (SELECT COLUMN_NAME
5. FROM TABLE_NAME
6. WHERE condition);

Example

Let's assume we have an EMPLOYEE_BKP table available which is

backup of EMPLOYEE table. The given example updates the SALARY
by .25 times in the EMPLOYEE table for all employee whose AGE is
greater than or equal to 29.

1. UPDATE EMPLOYEE
2. SET SALARY = SALARY * 0.25
3. WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP
4. WHERE AGE >= 29);
 This would impact three rows, and finally, the EMPLOYEE table would have the following
records.

ID NAME AGE ADDRESS SALARY

1 John 20 US 2000.00

2 Stepha 26 Dubai 1500.00

n

3 David 27 Bangkok 2000.00

4 Alina 29 UK 1625.00

5 Kathrin 34 Bangalore 2125.00

6 Harry 42 China 1125.00

7 Jackso 25 Mizoram 10000.00

n

4. Subqueries with the DELETE Statement

The subquery of SQL can be used in conjunction with the Delete
statement just like any other statements mentioned above.

Syntax

1. DELETE FROM TABLE_NAME

2. WHERE VALUE OPERATOR
3. (SELECT COLUMN_NAME
4. FROM TABLE_NAME
5. WHERE condition);

Example

Let's assume we have an EMPLOYEE_BKP table available which is

backup of EMPLOYEE table. The given example deletes the records
from the EMPLOYEE table for all EMPLOYEE whose AGE is greater
than or equal to 29.

1. DELETE FROM EMPLOYEE

2. WHERE AGE IN (SELECT AGE FROM EMPLOYEE_BKP
3. WHERE AGE >= 29 );

This would impact three rows, and finally, the EMPLOYEE table would have the following
records.

ID NAME AGE ADDRESS SALARY

1 John 20 US 2000.00

2 Stephan 26 Dubai 1500.00

3 David 27 Bangkok 2000.00

7 Jackson 25 Mizoram 10000.00

SQL Clauses
The following are the various SQL clauses:
 1. GROUP BY
o SQL GROUP BY statement is used to arrange identical
data into groups. The GROUP BY statement is used with
the SQL SELECT statement.
o The GROUP BY statement follows the WHERE clause in a
SELECT statement and precedes the ORDER BY clause.
o The GROUP BY statement is used with aggregation
function.

Syntax

1. SELECT column
2. FROM table_name
3. WHERE conditions
4. GROUP BY column
5. ORDER BY column

Sample table:
 PRODUCT_MAST

PRODUCT COMPANY QTY RATE COST

Item1 Com1 2 10 20

Item2 Com2 3 25 75

Item3 Com1 2 30 60

Item4 Com3 5 10 50

Item5 Com2 2 20 40

Item6 Cpm1 3 25 75

Item7 Com1 5 30 150

Item8 Com1 3 10 30

Item9 Com2 2 25 50

Item10 Com3 4 30 120

Example:

1. SELECT COMPANY, COUNT(*)

2. FROM PRODUCT_MAST
3. GROUP BY COMPANY;

Output:
Com1 5
Com2 3
Com3 2

2. HAVING
o HAVING clause is used to specify a search condition for a
group or an aggregate.
o Having is used in a GROUP BY clause. If you are not
using GROUP BY clause then you can use HAVING
function like a WHERE clause.

Syntax:

1. SELECT column1, column2

2. FROM table_name
3. WHERE conditions
4. GROUP BY column1, column2
5. HAVING conditions
6. ORDER BY column1, column2;

Example:

1. SELECT COMPANY, COUNT(*)

2. FROM PRODUCT_MAST
3. GROUP BY COMPANY
4. HAVING COUNT(*)>2;

Output:
Com1 5
Com2 3
 3. ORDER BY
o The ORDER BY clause sorts the result-set in ascending or
descending order.
o It sorts the records in ascending order by default. DESC
keyword is used to sort the records in descending order.

Syntax:

1. SELECT column1, column2

2. FROM table_name
3. WHERE condition
4. ORDER BY column1, column2... ASC|DESC;

Where

ASC: It is used to sort the result set in ascending order by

expression.

DESC: It sorts the result set in descending order by

expression.

Example: Sorting Results in Ascending Order

Table:

CUSTOMER

CUSTOMER_ID NAME ADDRESS

12 Kathrin US

23 David Bangkok
 34 Alina Dubai

45 John UK

56 Harry US

Enter the following SQL statement:

1. SELECT *
2. FROM CUSTOMER
3. ORDER BY NAME;

Output:

CUSTOMER_ID NAME ADDRESS

34 Alina Dubai

23 David Bangkok

56 Harry US

45 John UK

12 Kathrin US

Example: Sorting Results in Descending Order

Using the above CUSTOMER table
1. SELECT *
2. FROM CUSTOMER
3. ORDER BY NAME DESC;

Output:

CUSTOMER_ID NAME ADDRESS

12 Kathrin US

45 John UK

56 Harry US

23 David Bangkok

34 Alina Dubai

SQL Aggregate Functions

o SQL aggregation function is used to perform the
calculations on multiple rows of a single column of a
table. It returns a single value.
o It is also used to summarize the data.
 Types of SQL Aggregation Function

1. COUNT FUNCTION
o COUNT function is used to Count the number of rows in a
database table. It can work on both numeric and non-
numeric data types.
o COUNT function uses the COUNT(*) that returns the
count of all the rows in a specified table. COUNT(*)
considers duplicate and Null.

Syntax

1. COUNT(*)
2. or
3. COUNT( [ALL|DISTINCT] expression )

Sample table:

PRODUCT_MAST
 PRODUCT COMPANY QTY RATE COST

Item1 Com1 2 10 20

Item2 Com2 3 25 75

Item3 Com1 2 30 60

Item4 Com3 5 10 50

Item5 Com2 2 20 40

Item6 Cpm1 3 25 75

Item7 Com1 5 30 150

Item8 Com1 3 10 30

Item9 Com2 2 25 50

Item10 Com3 4 30 120

Example: COUNT()

1. SELECT COUNT(*)
2. FROM PRODUCT_MAST;

Output:
 10

Example: COUNT with WHERE

1. SELECT COUNT(*)
2. FROM PRODUCT_MAST;
3. WHERE RATE>=20;

Output:
7

Example: COUNT() with DISTINCT

1. SELECT COUNT(DISTINCT COMPANY)

2. FROM PRODUCT_MAST;

Output:
3

Example: COUNT() with GROUP BY

1. SELECT COMPANY, COUNT(*)

2. FROM PRODUCT_MAST
3. GROUP BY COMPANY;

Output:
Com1 5
Com2 3
Com3 2

Example: COUNT() with HAVING

1. SELECT COMPANY, COUNT(*)

2. FROM PRODUCT_MAST
3. GROUP BY COMPANY
4. HAVING COUNT(*)>2;

Output:
Com1 5
Com2 3

2. SUM Function
Sum function is used to calculate the sum of all selected
columns. It works on numeric fields only.

Syntax

1. SUM()
2. or
3. SUM( [ALL|DISTINCT] expression )

Example: SUM()

1. SELECT SUM(COST)
2. FROM PRODUCT_MAST;

Output:
670

Example: SUM() with WHERE

1. SELECT SUM(COST)
2. FROM PRODUCT_MAST
3. WHERE QTY>3;

Output:
320

Example: SUM() with GROUP BY

1. SELECT SUM(COST)
2. FROM PRODUCT_MAST
3. WHERE QTY>3
4. GROUP BY COMPANY;

Output:
Com1 150
Com2 170

Example: SUM() with HAVING

1. SELECT COMPANY, SUM(COST)

2. FROM PRODUCT_MAST
3. GROUP BY COMPANY
4. HAVING SUM(COST)>=170;

Output:
Com1 335
Com3 170

3. AVG function
The AVG function is used to calculate the average value of
the numeric type. AVG function returns the average of all
non-Null values.

Syntax

1. AVG()
2. or
3. AVG( [ALL|DISTINCT] expression )

Example:
1. SELECT AVG(COST)
2. FROM PRODUCT_MAST;

Output:
67.00

4. MAX Function
MAX function is used to find the maximum value of a certain
column. This function determines the largest value of all
selected values of a column.

Syntax

1. MAX()
2. or
3. MAX( [ALL|DISTINCT] expression )

Example:

1. SELECT MAX(RATE)
2. FROM PRODUCT_MAST;
30

5. MIN Function
MIN function is used to find the minimum value of a certain
column. This function determines the smallest value of all
selected values of a column.

Syntax

1. MIN()
2. or
3. MIN( [ALL|DISTINCT] expression )
 Example:

1. SELECT MIN(RATE)
2. FROM PRODUCT_MAST;

Output:
10

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

In SQL, JOIN clause is used to combine the records from two

or more tables in a database.

Types of SQL JOIN

1. INNER JOIN
2. LEFT JOIN
3. RIGHT JOIN
4. FULL JOIN

Sample Table
EMPLOYEE

EMP_ID EMP_NAME CITY SALARY AGE

1 Angelina Chicago 200000 30

2 Robert Austin 300000 26

3 Christian Denver 100000 42

4 Kristen Washington 500000 29

5 Russell Los angels 200000 36

6 Marry Canada 600000 48

PROJECT

PROJECT_NO EMP_ID DEPARTMENT

101 1 Testing

102 2 Development

103 3 Designing

104 4 Development

1. INNER JOIN
In SQL, INNER JOIN selects records that have matching values
in both tables as long as the condition is satisfied. It returns
the combination of all rows from both the tables where the
condition satisfies.
 Syntax

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

,....
2. FROM table1
3. INNER JOIN table2
4. ON table1.matching_column = table2.matching_column;

Query

1. SELECT EMPLOYEE.EMP_NAME, [Link]

2. FROM EMPLOYEE
3. INNER JOIN PROJECT
4. ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;

Output

EMP_NAME DEPARTMENT

Angelina Testing

Robert Development

Christian Designing

Kristen Development
 2. LEFT JOIN
The SQL left join returns all the values from left table and the
matching values from the right table. If there is no matching
join value, it will return NULL.

Syntax

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

,....
2. FROM table1
3. LEFT JOIN table2
4. ON table1.matching_column = table2.matching_column;

Query

1. SELECT EMPLOYEE.EMP_NAME, [Link]

2. FROM EMPLOYEE
3. LEFT JOIN PROJECT
4. ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;

Output

EMP_NAME DEPARTMENT

Angelina Testing

Robert Development

Christian Designing
 Kristen Development

Russell NULL

Marry NULL

3. RIGHT JOIN
In SQL, RIGHT JOIN returns all the values from the values
from the rows of right table and the matched values from the
left table. If there is no matching in both tables, it will return
NULL.

Syntax

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

,....
2. FROM table1
3. RIGHT JOIN table2
4. ON table1.matching_column = table2.matching_column;

Query

1. SELECT EMPLOYEE.EMP_NAME, [Link]

2. FROM EMPLOYEE
3. RIGHT JOIN PROJECT
4. ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;
 Output

EMP_NAME DEPARTMENT

Angelina Testing

Robert Development

Christian Designing

Kristen Development

4. FULL JOIN
In SQL, FULL JOIN is the result of a combination of both left
and right outer join. Join tables have all the records from both
tables. It puts NULL on the place of matches not found.

Syntax

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

,....
2. FROM table1
3. FULL JOIN table2
4. ON table1.matching_column = table2.matching_column;

Query
1. SELECT EMPLOYEE.EMP_NAME, [Link]
2. FROM EMPLOYEE
3. FULL JOIN PROJECT
4. ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;

Output

EMP_NAME DEPARTMENT

Angelina Testing

Robert Development

Christian Designing

Kristen Development

Russell NULL

Marry NULL

SQL Set Operation

The SQL Set operation is used to combine the two or more SQL
SELECT statements.

Types of Set Operation

1. Union
2. UnionAll
3. Intersect
4. Minus

1. Union
o The SQL Union operation is used to combine the result of two
or more SQL SELECT queries.
o In the union operation, all the number of datatype and columns
must be same in both the tables on which UNION operation is
being applied.
o The union operation eliminates the duplicate rows from its
resultset.

Syntax
1. SELECT column_name FROM table1
2. UNION
3. SELECT column_name FROM table2;

Example:

The First table

ID NAME

1 Jack

2 Harry

3 Jackson

The Second table

ID NAME

3 Jackson

4 Stephan

5 David

Union SQL query will be:

1. SELECT * FROM First

2. UNION
3. SELECT * FROM Second;
 The resultset table will look like:

ID NAME

1 Jack

2 Harry

3 Jackson

4 Stephan

5 David

2. Union All
Union All operation is equal to the Union operation. It returns the set
without removing duplication and sorting the data.

Syntax:

1. SELECT column_name FROM table1

2. UNION ALL
3. SELECT column_name FROM table2;

Example: Using the above First and Second table.

Union All query will be like:

1. SELECT * FROM First

2. UNION ALL
3. SELECT * FROM Second;

The resultset table will look like:

 ID NAME

1 Jack

2 Harry

3 Jackson

3 Jackson

4 Stephan

5 David

3. Intersect
o It is used to combine two SELECT statements. The Intersect
operation returns the common rows from both the SELECT
statements.
o In the Intersect operation, the number of datatype and
columns must be the same.
o It has no duplicates and it arranges the data in ascending order
by default.

Syntax

1. SELECT column_name FROM table1

2. INTERSECT
3. SELECT column_name FROM table2;

Example:

Using the above First and Second table.

 Intersect query will be:

1. SELECT * FROM First

2. INTERSECT
3. SELECT * FROM Second;

The resultset table will look like:

ID NAME

3 Jackson

4. Minus
o It combines the result of two SELECT statements. Minus
operator is used to display the rows which are present in the
first query but absent in the second query.
o It has no duplicates and data arranged in ascending order by
default.

Syntax:

1. SELECT column_name FROM table1

2. MINUS
3. SELECT column_name FROM table2;

Example

Using the above First and Second table.

Minus query will be:

1. SELECT * FROM First

2. MINUS
3. SELECT * FROM Second;
The resultset table will look like:

ID NAME

1 Jack

2 Harry