UNIT-2

Introduction to SQL

3.1 Introduction
SQL fully abbreviated as Structured Query Language can be defined as a domain-
specific language used to manage the relational databases and performs different operations
on the data stored in them. SQL is used as their standard database language by all the
relational database management systems like Oracle, Informix, Posgres, SQL server, MySQL,
MS Access, and Sybase.
3.1. 1 Characteristics of SQL
 SQL is easy to learn.


 SQL is used to access data from relational database management systems. 

 SQL can execute queries against the database. 

 SQL is used to describe the data. 

 SQL is used to define the data in the database and manipulate it when needed. 

 SQL is used to create and drop the database and table. 

 SQL is used to create a view, stored procedure, function in a database. 
 SQL allows users to set permissions on tables, procedures, and views. 

3.1.2 Advantages of SQL: There are the following advantages of SQL:

 High speed: Using the SQL queries, the user can quickly and efficiently retrieve a large
amount of records from a database.
 No coding needed: In the standard SQL, it is very easy to manage the database system. It
doesn't require a substantial amount of code to manage the database system.
 Well defined standards: Long established are used by the SQL databases that are being
used by ISO and ANSI.
 Portability: SQL can be used in laptop, PCs, server and even some mobile phones.

 Interactive language: SQL is a domain language used to communicate with the database.
It is also used to receive answers to the complex questions in seconds.
 Multiple data view: Using the SQL language, the users can make different views of the

database structure.

3.2 SQL DATA TYPE

SQL Data type is used to define the values that a column can contain. Every column is required
to have a name and data type in the database table. Different data types in SQL Are as follow.
1. Binary data types
2. Exact Numeric Data type
3. Approximate Numeric Data type
4. Character String Data type
5. Date and time Data type

1. Binary Data types: There are Three types of binary Datatypes which are given below
 Data Type Description
Binary It has a maximum length of 8000 bytes. It contains fixed-length binary data.
varbinary It has a maximum length of 8000 bytes. It contains variable-length binary data.
Image It has a maximum length of 2,147,483,647 bytes.

2. Approximate Numeric Data type: The subtypes are given below:

Data type From To Description

float -1.79E + 308 1.79E + 308 It is used to specify a floating-point value e.g. 6.2, 2.9 etc.
real -3.40e + 38 3.40E + 38 It specifies a single precision floating point number

3. Exact Numeric Data type: 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 Data type: The subtypes are given below:

Data
Description
type
It has a maximum length of 8000 characters. It contains Fixed-length non-unicode
char
characters.

It has a maximum length of 8000 characters. It contains variable-length non-

varchar
unicode characters.
It has a maximum length of 2,147,483,647 characters. It contains variable-length
text
non-unicode characters.

5. Date and time Data type: The subtypes are given below:

Data type 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.
timestampIt stores the year, month, day, hour, minute, and the second value.

2
What is MySQL: MySQL is a fast, easy to use relational database. It is currently the most
popular open-source database. It is very commonly used in conjunction with PHP scripts to
create powerful and dynamic server-side applications.
MySQL is used for many small and big businesses. It is developed, marketed and supported by
MySQL AB, a Swedish company. It is written in C and C++.

3.3 REASONS OF POPULARITY/ CHARACTERISTICS OF MYSQL: MySQL is

becoming so popular because of these following reasons:
 MySQL is an open-source database so you don't have to pay a single penny to use it. 

 MySQL is a very powerful program so it can handle a large set of functionality of the most
expensive and powerful database packages.
 MySQL is customizable because it is an open source database and the open-source GPL
license facilitates programmers to modify the SQL software according to their own specific
environment.

 MySQL is quicker than other databases so it can work well even with the large data set. 

 MySQL supports many operating systems with many languages like PHP, PERL, C, C++, JAVA,
etc.
 MySQL uses a standard form of the well-known SQL data language.


 MySQL is very friendly with PHP, the most popular language for web development.

MySQL supports large databases, up to 50 million rows or more in a table. The default file size
limit for a table is 4GB, but you can increase this (if your operating system can handle it) to a
theoretical limit of 8 million terabytes (TB).

Date and Time Data Type:

Data Type Maximum Size Explanation

DATE Range from '1000-01-01' to '9999-12-31'. Displayed as 'yyyy-mm-dd'.
Values range from '1000-01-01 00:00:00' to Displayed as 'yyyy-mm-dd
DATETIME '9999-12-31 23:59:59'. hh:mm:ss'.
Values range from '1970-01-01 00:00:01' UTC Displayed as 'YYYY-MM-DD
TIMESTAMP(m) to '2038-01-19 03:14:07' TC. HH:MM:SS'.
TIME Values range from '-838:59:59' to '838:59:59'. Displayed as 'HH:MM:SS'.
YEAR[(2|4)] Year value as 2 digits or 4 digits. Default is 4 digits.
3.6 SQL COMMAND
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. SQL can perform various tasks like

create a table, add data to tables, drop the table, modify the table, set permission for users.

7
Structured Query Language(SQL) as we all know is the database language by the use of which
we can perform certain operations on the existing database and also we can use this language
to create a database. SQL uses certain commands like Create, Drop, Insert etc. to carry out the
required tasks. These SQL commands are mainly categorized into four categories as discussed
below:

3.6.1 DDL (Data Definition Language) : DDL or Data Definition Language actually consists of

the SQL commands that can be used to define the database schema. It simply deals with

descriptions of the database schema and is used to create and modify the structure of

database objects in database.

1. CREATE – is used to create the database or its objects (like table, index, function, views,
store procedure and triggers).
Syntax:
CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,....]);
Example:
CREATE TABLE EMPLOYEE(Name VARCHAR2(20), Email VARCHAR2(100), DOB DATE);

2. DROP – is used to delete objects from the database.

Syntax
DROP TABLE ;
Example
DROP TABLE EMPLOYEE;

3. TRUNCATE–is used to remove all records from a table, including all spaces allocated for
the records are removed, it does not disturb the structure of table, i.e after truncate your table
with all attributes will be exist but all the rows in table will be deleted.
Syntax:
TRUNCATE TABLE table_name;
Example:
TRUNCATE TABLE EMPLOYEE;
8
4. COMMENT –is used to add comments to the data dictionary. This is similar to comment
we add in program block.
/* add comment here*/

5. RENAME –is used to rename an objects like database, tables, views etc. existing in the
database.
Syntax: Rename Object old_name to new_name;
Example: Rename table emp to employee;

6. ALTER-is used to alter the structure of the table, It is used to alter the structure of the
table. This change could be either to modify the characteristics of an existing attribute or
probably to add a new attribute or to remove the existing attributes.
Syntax:
 ALTER TABLE command to add a New Column in an existing table is as

follows. ALTER TABLE table_name ADD (column_name datatype);


 ALTER TABLE command to DROP COLUMN in an existing table is as follows.

ALTER TABLE table_name DROP COLUMN column_name;


 ALTER TABLE command to RENAME ATTRIBUTE in an existing table is as follows.

ALTER TABLE table_name Rename old-column_name to new-column-name;


 ALTER TABLE command to change the DATA TYPE of a column in a table is as

follows. ALTER TABLE table_name MODIFY (column_name datatype);


 ALTER TABLE command to add a NOT NULL constraint to a column in a table is as

follows. ALTER TABLE table_name MODIFY column_name datatype NOT NULL;


 ALTER TABLE to ADD UNIQUE CONSTRAINT to a table is as follows.
ALTER TABLE table_name ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1,
column2...);
 ALTER TABLE command to ADD CHECK CONSTRAINT to a table is as follows. ALTER

TABLE table_name ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);


 ALTER TABLE command to ADD PRIMARY KEY constraint to a table is as follows.

ALTER TABLE table_name ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1,

column2...);
 ALTER TABLE command to DROP CONSTRAINT from a table is as follows.

ALTER TABLE table_name DROP CONSTRAINT MyUniqueConstraint;

3.6.2 DML (Data Manipulation Language) : The SQL commands that deals with the
manipulation of data present in database belong to DML or Data Manipulation Language and
this includes most of the SQL statements.
1. SELECT – is Data Manipulation Language command used to retrieve data from the tables
of selected database. It retrieves the data and displays the retrieved data on console.
Syntax:
SELECT expressions FROM TABLES WHERE conditions;
For example:
SELECT emp_name FROM employee WHERE age > 20;

2. INSERT – is Data Manipulation Language command used to insert data into a table. It can
insert a single record at a time in a table.
Syntax:
INSERT INTO TABLE_NAME (col1, col2, …... col N) VALUES (value1, value2, ….. valueN);

Or INSERT INTO TABLE_NAME VALUES (value1, value2, value3, .... valueN);

For example:
INSERT INTO javatpoint (Author, Subject) VALUES ("Sonoo", "DBMS");

3. UPDATE – is Data Manipulation Language command used to update existing data within a
table. It can modify the any values or attributes for the particular tuple or row based on
specified condition.
Syntax:
UPDATE table_name SET [column_name1= value1,...column_nameN = valueN] [WHERE
CONDITION]
For example:
UPDATE students SET User_Name = 'Sonoo' WHERE Student_Id = '3'

4. DELETE – is Data Manipulation Language command used to delete records from a

database table. It deletes the one or more record from tables depends on a conditions.
Syntax:DELETE FROM table_name [WHERE condition];
For example: DELETE FROM JBook WHERE Author="Sonoo";

3.6.3 DCL (Data Control Language) : DCL includes commands such as GRANT and REVOKE
which mainly deals with the rights, permissions and other controls of the database system.
1. GRANT-gives user’s access privileges to database. It is also used to create a database user
with some privileges.
Example
GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USER;

2. REVOKE-withdraw user’s access privileges given by using the GRANT command.

Example:
REVOKE SELECT, UPDATE ON MY_TABLE FROM USER1, USER2;
3.6.4 TCL (transaction Control Language) : TCL commands deals with the transaction
within the database.
1. COMMIT– commits a Transaction, is used to save all the transactions to the database.
Syntax:
COMMIT;
Example:

10
 DELETE FROM CUSTOMERS WHERE AGE = 25;
COMMIT;
2. ROLLBACK– rollbacks a transaction in case of any error occurs. Rollback command is used
to undo transactions that have not already been saved to the database. It can rollback all
transaction which are not yet committed.
Syntax:
ROLLBACK;
Example:
DELETE FROM CUSTOMERS WHERE AGE = 25;
ROLLBACK;
3. SAVEPOINT–sets a savepoint within a transaction.

4. SET TRANSACTION–specify characteristics for the transaction.

3.7 VIEWS IN SQL

Views in SQL are a virtual table. A view also contains rows and columns. They have their own
existence in database But they don’t save any records in a view. To create the view, we can
select the fields from one or more tables present in the database. A view can either have
specific rows based on certain condition or all the rows of a table. we can add SQL functions,
WHERE, and JOIN statements to a view and present the data as if the data were coming from
one single table.
Student_Detail Student_Marks

STU_IDNAME ADDRESS STU_IDNAME MARKSAGE

1 StephanDelhi 1 Stephan 97 19
2 Kathrin Noida 2 Kathrin 86 21
3 David Ghaziabad 3 David 74 18
4 Alina Gurugram 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:
CREATE VIEW view_name AS SELECT column1, column2.....FROM table_name WHERE condit

ion;
2. Creating View from a single table: In this example, we create a View named DetailsView
from the table Student_Detail.
CREATE VIEW DetailsView AS SELECT NAME, ADDRESS FROM Student_Details WHERE
STU_ID < 4;
Just like table query, we can query the view to view the data: SELECT * FROM DetailsView;

11
Output: NAME ADDRESS
Stephan Delhi
Kathrin Noida
David Ghaziabad
2. Creating View from multiple tables: View from multiple tables can be created by simply

include multiple tables in the SELECT statement.

Example, a view is created named MarksView from two tables Student_Detail and
Student_Marks.
CREATE VIEW MarksView AS SELECT Student_Detail.NAME, Student_Detail.ADDRESS, Studen
t_Marks.MARKS FROM Student_Detail, Student_Mark WHERE Student_Detail.NAME = Student
_Marks.NAME;
To display data of View MarksView: 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 DROP VIEW view_name;
Example: To delete the View MarksView, we can do this as: DROP VIEW MarksView;

3.8 NESTED QUERY

A Subquery or Inner query or a Nested query is a query within another SQL query and
embedded within the WHERE clause. A subquery is used to return data that will be used in the
main query as a condition to further restrict the data to be retrieved. Subqueries can be used
with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <,
>, >=, <=, IN, BETWEEN, etc. sub queries can retrieve the data from single or multiple tables.
There are a few rules that subqueries must follow
 Subqueries must be enclosed within parentheses.

 A subquery can have only one column in the SELECT clause, unless multiple columns are
in the main query for the subquery to compare its selected columns.
 An ORDER BY command cannot be used in a subquery, although the main query can use an
ORDER BY. The GROUP BY command can be used to perform the same function as the ORDER
BY in a subquery.

 Subqueries that return more than one row can only be used with multiple value operators
such as the IN operator.
12
 The SELECT list cannot include any references to values that evaluate to a BLOB, ARRAY,
CLOB, or NCLOB.
 A subquery cannot be immediately enclosed in a set function.

 The BETWEEN operator cannot be used with a subquery. However, the BETWEEN
operator can be used within the subquery.
1. Sub queries with the SELECT Statement: Subqueries are most frequently used with the
SELECT statement. The basic syntax is as follows
SELECT column_name [, column_name ] FROM table1 [, table2 ] WHERE column_name
OPERATOR (SELECT column_name [, column_name ] FROM table1 [, table2 ]
Example:
[WHERE])

Consider the CUSTOMERS table having the following records –

ID NAME AGE ADDRESS SALARY
1 Ramesh 35 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 MP 4500.00
7 Muffy 24 Indore 10000.00
Now, let us check the following subquery with a SELECT statement.

SQL> SELECT * FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE SALARY
> 4500) ;

This would produce the following result.

ID NAME AGE ADDRESS SALARY
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
7 Muffy 24 Indore 10000.00
3. Subqueries with the INSERT Statement: Sub queries also can be used with INSERT
statements. The INSERT statement uses the data returned from the subquery to insert into
another table. The selected data in the sub query can be modified with any of the character,
date or number functions. The basic syntax is as follows.
INSERT INTO table_name [(column1 [, column2 ])] Operator (SELECT [ *|column1 [, column2
] FROM table1 [, table2 ] [ WHERE VALUE OPERATOR ])
Example: Consider a table CUSTOMERS_BKP with similar structure as CUSTOMERS table.
Now to copy the complete CUSTOMERS table into the CUSTOMERS_BKP table, you can use the
following syntax.
SQL> INSERT INTO CUSTOMERS_BKP SELECT * FROM CUSTOMERS WHERE ID IN (SELECT
ID FROM CUSTOMERS) ;

13
4. Subqueries with the UPDATE Statement: The subquery can be used in conjunction with
the UPDATE statement. Either single or multiple columns in a table can be updated when
using a subquery with the UPDATE statement. The basic syntax is as follows.
UPDATE table SET column_name = new_value [ WHERE OPERATOR [ VALUE ] (SELECT
COLUMN_NAME FROM TABLE_NAME [ WHERE) ] )
Example: Assuming, we have CUSTOMERS_BKP table available which is backup of
CUSTOMERS table. The following example updates SALARY by 0.25 times in the CUSTOMERS
table for all the customers whose AGE is greater than or equal to 27.
SQL> UPDATE CUSTOMERS SET SALARY = SALARY * 0.25 WHERE AGE IN (SELECT AGE
FROM CUSTOMERS_BKP WHERE AGE >= 27 );
This would impact two rows and finally CUSTOMERS table would have the following records.

ID NAME AGE ADDRESS SALARY

1 Ramesh 35 Ahmedabad 2500.00
2 Khilan 25 Delhi 1500.00
3 kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 MP 4500.00
7 Muffy 24 Indore 10000.00
5. Subqueries with the DELETE Statement: The subquery can be used in conjunction with

the DELETE statement like with any other statements mentioned above. The basic syntax is as

follows.

DELETE FROM TABLE_NAME [ WHERE OPERATOR [ VALUE ] (SELECT COLUMN_NAME

FROM TABLE_NAME [ WHERE) ])
Example: Assuming, we have a CUSTOMERS_BKP table available which is a backup of the
CUSTOMERS table. The following example deletes the records from the CUSTOMERS table for
all the customers whose AGE is greater than or equal to 27.
SQL> DELETE FROM CUSTOMERS WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP
WHERE AGE >= 27 );
This would impact two rows and finally the CUSTOMERS table would have the following
records.
ID NAME AGE ADDRESS SALARY
2 Khilan 25 Delhi 1500.00
3 kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 MP 4500.00
7 Muffy 24 Indore 10000.00 14

3.10 SQL CLAUSES

Clauses are used to apply some condition or constraints on the query The following are the
various SQL clauses:
1. Where Clause
 2. Order By Clause
3. Group By Clause
4. Having Clause
5. Distinct Clause

3.10.1 MySQL WHERE Clause: MySQL WHERE Clause is used with SELECT, INSERT, UPDATE
and DELETE clause to filter the results. It specifies a specific position where you have to do
the operation.
Syntax: WHERE conditions;
Conditions: It specifies the conditions that must be fulfilled for records to be selected.

17
 SELECT * FROM officers WHERE address = 'Mau';

MySQL WHERE Clause with AND condition: In this example, we are retrieving data from the
table "officers" with AND condition.
SELECT * FROM officers WHERE address = 'Lucknow' AND officer_id < 5;

3.10.2 ORDER BY: The ORDER BY clause sorts the result-set in ascending or descending
order. It sorts the records in ascending order by default. DESC keyword is used to sort the
records in descending order.
Syntax: ELECT column1, column2 FROM table_name WHERE condition 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
SELECT * FROM CUSTOMER ORDER BY NAME;

Example: Sorting Results in Descending Order

SELECT * FROM CUSTOMER ORDER BY NAME DESC;

3.10.3 GROUP BY Clause: The MYSQL GROUP BY Clause is used to collect data from multiple
records and group the result by one or more column. It is generally used in a SELECT
statement. You can also use some aggregate functions like COUNT, SUM, MIN, MAX, AVG etc.
on the grouped column.
Syntax: SELECT expression1, expression2,... expression_n, aggregate_function (expression)
FROM tables [WHERE conditions] GROUP BY expression1, expression2, ... expression_n;
Parameters: expression1, expression2, ... expression_n: It specifies the expressions that
are not encapsulated within an aggregate function and must be included in the GROUP BY
clause.
Aggregate function: It specifies a function such as SUM, COUNT, MIN, MAX, or AVG etc.
tables: It specifies the tables, from where you want to retrieve the records. There must be at
least one table listed in the FROM clause.
WHERE conditions: It is optional. It specifies the conditions that must be fulfilled for the
records to be selected.
 SQL GROUP BY statement is used to arrange identical data into groups. The GROUP BY
 statement is used with the SQL SELECT statement.
 The GROUP BY statement follows the WHERE clause in a SELECT statement and precedes
 the ORDER BY clause.
 The GROUP BY statement is used with aggregation function. 

Syntax: ELECT column FROM table_name WHERE conditions GROUP BY column ORDER BY
column
3.10.4 MySQL HAVING Clause: MySQL HAVING Clause is used with GROUP BY clause. It
always returns the rows where condition is TRUE.
Syntax:

18
SELECT expression1, expression2, ... expression_n, aggregate_function (expression) FROM ta
bles [WHERE conditions] GROUP BY expression1, expression2, ... expression_n HAVING cond
ition;
Parameters
Aggregate function: It specifies any one of the aggregate function such as SUM, COUNT, MIN,

MAX, or AVG.
expression1, expression2, ... expression_n: It specifies the expressions that are not
encapsulated within an aggregate function and must be included in the GROUP BY clause.
WHERE conditions: It is optional. It specifies the conditions for the records to be selected.
HAVING condition: It is used to restrict the groups of returned rows. It shows only those
groups in result set whose conditions are TRUE.
Execute the following query:
SELECT emp_name, SUM(working_hours) AS "Total working hours" FROM employees GROU

P BY emp_name HAVING SUM(working_hours) > 5;

3.10. 5: MySQL Distinct Clause: MySQL DISTINCT clause is used to remove duplicate records
from the table and fetch only the unique records. The DISTINCT clause is only used with the
SELECT statement.
Syntax:
SELECT DISTINCT expressions FROM tables [WHERE conditions];
Parameters
expressions: specify the columns or calculations that you want to retrieve.

tables: specify the name of the tables from where you retrieve records. There must be at least
one table listed in the FROM clause.
WHERE conditions: It is optional. It specifies the conditions that must be met for the records
to be selected.
Note:
 If you put only one expression in the DISTINCT clause, the query will return the unique
values for that expression. 
 If you put more than one expression in the DISTINCT clause, the query will retrieve unique
 combinations for the expressions listed. 
 In MySQL, the DISTINCT clause doesn't ignore NULL values. So if you are using the
DISTINCT clause in your SQL statement, your result set will include NULL as a distinct
value.
Use the following query: SELECT DISTINCT address FROM officers;

3.11 SET OPERATIONS:

SQL supports few Set operations which can be performed on the table data. These are used to get
meaningful results from data stored in the table, under different special conditions. Or The SQL Set

19
operation is used to combine the two or more SQL SELECT statements. Four different types of SET
operations, are

1. UNION
2. UNION ALL
3. INTERSECT
4. MINUS The Second table,

The First table,

ID Name ID Name
1 Abhi 2 adam
2 Adam 3 Chester
3.11.1 UNION Operation: UNION is used to combine the results of two or more SELECT
statements. However it will eliminate duplicate rows from its resultset. In case of union,
number of columns and datatype must be same in both the tables, on which UNION operation
is being applied.
Select * from First Union Select * from Second
The result set table will look like,
ID NAME
1 abhi
2 adam
3 Chester
3.11.2 UNION ALL: This operation is similar to Union. But it also shows the duplicate rows.

Select * from First Union All Select * from Second

The result set table will look like,

ID NAME
1 Abhi
2 Adam
2 Adam
3 Chester
3.11.3 INTERSECT: Intersect operation is used to combine two SELECT statements, but it
only returns the records which are common from both SELECT statements. In case of
Intersect the number of columns and datatype must be same. But remember MySQL does
not support INTERSECT operator.
Select * from First INTERSECT Select * from Second

The result set table will look like

ID NAME
2 adam

20
3.11.4 MINUS: The Minus operation combines results of two SELECT statements and return only
those in the final result, which belongs to the first set of the result.
Select * from First MINUS Select * from Second
The result set table will look like

ID NAME
2 adam
3.12 SET MEMBERSHIP: To Check whether any
values is a member of some set or not set membership is used. In SQL set membership is used
with IN and NOT IN operator which allows you to easily test if an expression matches any
value in a list of values. The IN operator checks a value within a set of values separated by
commas and retrieve the rows from the table which are matching. The IN returns 1 when the
search value present within the range otherwise returns 0. Consider Example with Relations
SavingAccount(Laccno, SCust_Name, Balance)
Loan Account (Laccno, LCust_Name, Balance)
1. Find list of all customer name which are having Loan account as well saving account
Select * from SavingAccount where SCust_Name IN (Select * from LoanAccount)
2. Find list of Customer Belonging to any ne of following city.
SELECT * FROM Customers WHERE Country IN ('Germany', 'France', 'UK');
3.13 SET COMPARISON: Set comparison is SQL is used to compare the values in two set.
Using arithmetic comparison operator we can just compare two values but if we want to
compare more than one elements of two sets then set comparison can be used with SOME and
ALL operator.
First we will understand how All and Some operatorwork. Remember SOME operator will
match any element of Set A with SOME elements of Set B. and ALL Operator will match any
element of Set A with ALL elements of Set B. Consider two Sets
X={100, 200, 300} And Y = {300, 400, 500}

Condition for Some Result for Some Condition for All Result for All
Operator Operator Operator Operator
A<SOMEB TRUE A<ALLB TRUE
A <= SOME B TRUE A<=ALLB TRUE
A >= SOME B TRUE A>=ALLB FALSE
A>SOMEB FALSE A>ALLB FALSE
A <> SOME B TRUE A<>ALLB FALSE
A=SOMEB TRUE A=ALLB FALSE
Consider the example with following relations

Branch(bname, bcity, assets)

1. Find the name of all branches that have assets greater than those of some branches

located in nashik.

21
 Select bname from branch where assets > SOME (Select assets from Branch where
bcity=’Nashik’)
2. Find the name of all branches that have assets greater than those of ALL branches
located in nashik.
Select bname from branch where assets > ALL (Select assets from Branch where

bcity=’Nashik’)

3.14 SQL JOINS

The SQL Joins clause is used to combine records from two or more tables in a database. A
JOIN is a means for combining fields from two tables by using values common to each. A SQL
Join statement is used to combine data or rows from two or more tables based on a common
field between them.
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.

 JOINS allow us to combine data from more than one table into a single result set.

 JOINS have better performance compared to sub queries

 INNER JOINS only return rows that meet the given criteria.

 OUTER JOINS can also return rows where no matches have been found. The unmatched
rows are returned with the NULL keyword.
Let us Discus Different types of Joins with two tables Order & Customer
1. INNER JOIN

2. LEFT JOIN
3. RIGHT JOIN
4. FULL JOIN
Table 1: Customer

customer_id first_name last_name address city

1 George Washington 3200 Mt Vernon Hwy Mount Vernon
2 John Adams 1250 Hancock St Quincy
3 Thomas Jefferson 931 Thomas Jefferson Pkwy Charlottesville
4 James Madison 11350 Constitution Hwy Orange
5 James Monroe 2050 James Monroe Parkway Charlottesville

order_id order_
Table 2: Order
date amount customer_id
1 07/04/1776 $234.56 1
2 03/14/1760 $78.50 3
3 05/23/1784 $124.00 2

22
 4 09/03/1790 $65.50 3
5 07/21/1795 $25.50 10
6 11/27/1787 $14.40 9
1. INNER JOIN: The INNER JOIN keyword selects all rows from both the tables as long as the

condition satisfies. This keyword will create the result-set by combining all rows from both the

tables where the condition satisfies i.e value of the common field will be same. Syntax: Select

t1.attribute, t2.attribute from Table1 t1 INNER JOIN Table t2 ON t1.matching_Column=

t2.matching_Column

Select c.first_name, c.last_name,o. order_date,

o.order_amount from customers c inner join orders o
on c.customer_id = o.customer_id
The Output of This Query will be

first_name last_name order_date order_amount

George Washington 07/4/1776 $234.56
John Adams 05/23/1784 $124.00
Thomas Jefferson 03/14/1760 $78.50
Thomas Jefferson 09/03/1790 $65.50
2. LEFT JOIN: it is also called as Left Outer Join. This join returns all the rows of the table on
the left side of the join and matching rows for the table on the right side of join. The rows for
which there is no matching row on right side, the result-set will contain null. LEFT JOIN is also
known as LEFT OUTER JOIN.
Syntax: Select t1.attribute, t2.attribute from Table1 t1 LEFT JOIN Table t2 ON

t1.matching_Column= t2.matching_Column

Select c.first_name, c.last_name,o. order_date,

o.order_amount from customers c Left join orders o
on c.customer_id = o.customer_id

The Output of This Query will be

order_date order_amount
first_name last_

name
George Washington 07/04/1776 $234.56
John Adams 05/23/1784 $124.00
Thomas Jefferson 03/14/1760 $78.50
Thomas Jefferson 09/03/1790 $65.50
James Madison NULL NULL
James Monroe NULL NULL
3. RIGHT JOIN: it is also called as Right Outer Join. RIGHT JOIN is similar to LEFT JOIN. This

join returns all the rows of the table on the right side of the join and matching rows for the
table on the left side of join. The rows for which there is no matching row on left side, the
result-set will contain null. RIGHT JOIN is also known as RIGHT OUTER JOIN.
Syntax: Select t1.attribute, t2.attribute from Table1 t1 RIGHT JOIN Table t2 ON

t1.matching_Column= t2.matching_Column

Select c.first_name, c.last_name,o. order_date,

o.order_amount from customers c Right join orders o
on c.customer_id = o.customer_id

The Output of This Query will be

first_name last_name order_date order _amount
George Washington 07/04/1776 $234.56
Thomas Jefferson 03/14/1760 $78.50
John Adams 05/23/1784 $124.00
Thomas Jefferson 09/03/1790 $65.50
NULL NULL 07/21/1795 $25.50
NULL NULL 11/27/1787 $14.40
4. FULL JOIN: FULL JOIN creates the result-set by combining result of both LEFT JOIN and
RIGHT JOIN. The result-set will contain all the rows from both the tables. The rows for which
there is no matching, the result-set will contain NULL values.
Syntax: Select t1.attribute, t2.attribute from Table1 t1 FULL JOIN Table t2 ON

t1.matching_Column= t2.matching_Column

Select c.first_name, c.last_name,o. order_date,

o.order_amount from customers c Full join orders o
On c.customer_id = o.customer_id

The Output of This Query will be

first_name last_name order_date order_amount

George Washington 07/04/1776 $234.56
Thomas Jefferson 03/14/1760 $78.50
John Adams 05/23/1784 $124.00
Thomas Jefferson 09/03/1790 $65.50
NULL NULL 07/21/1795 $25.50
NULL NULL 11/27/1787 $14.40
James Madison NULL NULL
James Monroe NULL NULL
3.15 AGGREGATE FUNCTIONS: In database management an aggregate function is a
function where the values of multiple rows are grouped together as input on certain criteria
to form a single value of more significant meaning. Different aggregate functions are as follow.

24
1. COUNT Function: 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. It uses the COUNT(*) that

returns the count of all the rows in a specified table. COUNT(*) considers duplicate and

Null.

Example: SELECT COUNT(*) FROM PRODUCT_MAST;

2. SUM Function: Sum function is used to calculate the sum of all selected columns. It
works on numeric fields only.
Example: SELECT SUM(COST) FROM PRODUCT_MAST;
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.
Example: SELECT AVG(COST) FROM PRODUCT_MAST;
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.
Example: SELECT MAX(RATE) FROM PRODUCT_MAST;
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.
Example: SELECT MIN(RATE) FROM PRODUCT_MAST;