UNIT-III

1.Basic SQL querying (select and project) using

where clause :
The SQL WHERE clause is used to filter the results obtained by the DML statements
such as SELECT, UPDATE and DELETE etc. We can retrieve the data from a single
table or multiple tables(after join operation) using the WHERE clause.
WHERE keyword is used for fetching filtered data in a result set. It is used to fetch
data according to particular criteria. WHERE keyword can also be used to filter data
by matching patterns.
Syntax:
SELECT column1,column2 FROM table_name WHERE column_name operator
value;
Parameter Explanation:
1. column1,column2: fields in the table
2. table_name: name of table
3. column_name: name of field used for filtering the data
4. operator: operation to be considered for filtering
5. value: exact value or pattern to get related data in result
Example :
The table created is as shown below :−

ID NAME AGE ADDRESS SALARY

1 Ramesh 32 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 Hyderabad 4500.00

7 Muffy 24 Indore 10000.00

1 | Page
Query :
SELECT ID, NAME, SALARY FROM CUSTOMERS
WHERE SALARY > 2000;
Output :

ID NAME SALARY

4 Chaitali 6500.00

5 Hardik 8500.00

6 Komal 4500.00

7 Muffy 10000.00

2. Arithmetic & logical operations :

What is SQL Operator?
An SQL operator is a reserved word or a character used primarily in an SQL
statement's WHERE clause to perform operation(s), such as comparisons and
arithmetic operations. These Operators are used to specify conditions in an SQL
statement and to serve as conjunctions for multiple conditions in a statement.
SQL Arithmetic Operators :
SQL Arithmetic Operators are used to perform mathematical operations on the
numerical values. SQL provides following operators to perform mathematical
operations.
Here is a list of all the arithmetic operators available in SQL.

Operator Description Example

+ Addition 10 + 20 = 30

- Subtraction 20 - 30 = -10

* Multiplication 10 * 20 = 200

/ Division 20 / 10 = 2

% Modulus 5%2=1

2 | Page
SQL Comparison Operators :
SQL Comparison Operators test whether two given expressions are the same or not.
These operators are used in SQL conditional statements while comparing one
expression with another and they return a Boolean value which can be either TRUE
or FALSE. The result of an SQL comparison operation can be UNKNOWN when one
or another operand has it's value as NULL.
Here is a list of all the comparison operators available in SQL.

Operator Description Example

= Equal to 5 = 5 returns TRUE

!= Not equal 5 != 6 returns TRUE

<> Not equal 5 <> 4 returns TRUE

> Greater than 4 > 5 returns FALSE

< Less than 4 < 5 returns TRUE

>= Greater than or equal to 4 >= 5 returns FALSE

<= Less than or equal to 4 <= 5 returns TRUE

!< Not less than 4 !< 5 returns FALSE

!> Not greater than 4 !> 5 returns TRUE

3 | Page
SQL Logical Operators :
SQL Logical Operators are very similar to comparison operators and they test for the
truth of some given condition. These operators return a Boolean value which can be
either a TRUE or FALSE. The result of an SQL logical operation can be UNKNOWN
when one or another operand has it's value as NULL.
Here is a list of all the logical operators available in SQL.

Operator Description

ALL TRUE if all of a set of comparisons are TRUE.

AND TRUE if all the conditions separated by AND are TRUE.

ANY TRUE if any one of a set of comparisons are TRUE.

BETWEEN TRUE if the operand lies within the range of comparisons.

EXISTS TRUE if the subquery returns one or more records

IN TRUE if the operand is equal to one of a list of expressions.

LIKE TRUE if the operand matches a pattern specially with wildcard.

NOT Reverses the value of any other Boolean operator.

OR TRUE if any of the conditions separated by OR is TRUE

IS NULL TRUE if the expression value is NULL.

SOME TRUE if some of a set of comparisons are TRUE.

The UNIQUE operator searches every row of a specified table for

UNIQUE
uniqueness (no duplicates).

4 | Page
3.SQL functions(Date and Time, Numeric, String
conversion) :
SQL Date and Time Functions
In SQL, dates are complicated for newbies, since while working with a database, the
format of the data in the table must be matched with the input data to insert.
For storing a date or a date and time value in a database, MySQL offers the
following data types:

DATE format YYYY-MM-DD

DATETIME format: YYYY-MM-DD HH:MI: SS

TIMESTAMP format: YYYY-MM-DD HH:MI: SS

YEAR format YYYY or YY

some popular functions in SQL date functions.

1.NOW()
Returns the current date and time.

Query:
SELECT NOW();

Output:

2. CURDATE()
Returns the current date.

Query:
SELECT CURDATE();

Output:

5 | Page
3. CURTIME()
Returns the current time.

Query:
SELECT CURTIME();

Output:

1.
4. DATE()
Extracts the date part of a date or date/time expression. Example: For the below
table named ‘Test’

Id Name BirthTime

412
Pratik 1996-09-26 16:44:15.581
0

Query:
SELECT Name, DATE(BirthTime)
AS BirthDate FROM Test;

Output:

Name BirthDate

Pratik 1996-09-26

6 | Page
SQL Numeric Funtion :
SQL numeric functions are used primarily for numeric manipulation and/or
mathematical calculations. The following table details the numeric functions −

Sr.No. Function & Description

ABS()
1
Returns the absolute value of numeric expression.

ACOS()
2 Returns the arccosine of numeric expression. Returns NULL if the value is not
in the range -1 to 1.

ASIN()
3 Returns the arcsine of numeric expression. Returns NULL if value is not in the
range -1 to 1

ATAN()
4
Returns the arctangent of numeric expression.

ATN2()
5
Returns the arctangent of the two variables passed to it.

CEILING()
6 Returns the smallest (closest to negative infinity) integer value that is greater
than or equal to this value.

COS()
7
Returns the trigonometric cosine of the given value.

COT()
8
Returns the trigonometric cotangent of the given value.

DEGREES()
9
Returns numeric expression converted from radians to degrees.

EXP()
10 Returns the base of the natural logarithm (e) raised to the power of passed
numeric expression.

FLOOR()
11 Returns the largest integer value that is not greater than passed numeric
expression.

LOG()
12
Returns the natural logarithm of the passed numeric expression.

13 LOG10()

7 | Page
 Returns the base-10 logarithm of the passed numeric expression.

PI()
14
Returns the value of pi

POWER()
15
Returns the value of one expression raised to the power of another expression

RADIANS()
16
Returns the value of passed expression converted from degrees to radians.

RAND()
17
Returns the random value between 0 and 1.

ROUND()
18 Returns numeric expression rounded to an integer. Can be used to round an
expression to a number of decimal points

SIGN()
19
Returns the sign of a number, indicating whether it is positive, negative, or zero.

SIN()
20
Returns the sine of numeric expression given in radians.

SQRT()
21
Returns the non-negative square root of numeric expression.

TAN()
22
Returns the tangent of numeric expression expressed in radians

SQL STRING FUNCTIONS:

SQL string functions are used primarily for string manipulation. The following table
details the important string functions −

Sr.No. Function & Description

ASCII()
1
Returns numeric value of left-most character

CHAR()
2
Returns the character for each integer passed

CHARINDEX()
3
Returns the position of a substring within the given string.

CONCAT()
5
Returns concatenated string

8 | Page
 DIFFERENCE()
6 returns an integer value measuring the difference between the SOUNDEX()
values of two different expressions(strings).

ESCAPE()
7
Returns a text with escaped characters.

FORMAT()
8
Returns the formatted string.

LOWER()
11
Returns returns the lower case of the given string.

LTRIM()
12 Returns a string after removing all the white spaces and characters from the
string found on the left side.

NCHAR()
13
Returns the Unicode character based on the number code.

PATINDEX()
14
Returns the position of a pattern in a string.

REPLACE()
16 Returns a new string by replacing all the occurrences of the particular part of a
string (substring) with a specified string.

REPLICATE()
17
Returns the repeated value.

REVERSE()
18
Returns a reversed string.

RIGHT()
19
Returns the rightmost characters from the actual(current) string.

RTRIM()
20
Returns a string after removing all trailing blanks.

SOUNDEX()
21
Returns the Soundex string.

SPACE()
22
returns a string consisting of N number of space characters.

STR()
23
Returns a number as string.

STRING_AGG()
24 Concatenates the values of string expressions and places separator values
between them.

STRING_SPLIT()
25
Splits a string into rows of substrings.

9 | Page
 SUBSTRING()
27
Returns the part of the character.

4.Creating tables with Relationship :

a. One-to-One Relationship :
In this relationship, each row/record of the Let’s table is exactly related to one
row in the second table and vice versa.
Example :
Create tables “Student” and “Class_Details” with required columns or attributes as
mentioned in the above example.

One-to-one relationship between student and class_details

SQL Query :
SELECT students.Enrolment_No, students.Name, students.Roll_No,
class_details.Class, class_details.Division
FROM students
JOIN class_details ON students.Enrolment_No = class_details.Enrolment_No;
Output:

10 | P a g e
one-to-one relationship(vice-versa) :
In the definition, we saw that the one-to-one relationship is also vice-versa, this
means the class_details table is also related to exactly one record in students table.

Query :

SELECT class_details.Roll_No, class_details.Class, class_details.Division,

students.Enrolment_No, students.Name
FROM class_details
JOIN students ON class_details.Roll_No = students.Roll_No;

Output:

11 | P a g e
b. One-to-Many Relationship :
In this relationship, each row/record of the Let’s first table can be related to
multiple rows in the second table.
Example:
Create tables ‘parent‘ and ‘child‘ as mentioned in the above table and let’s see one-
to-many relation between these 2 tables.

SQL Query:
SELECT parent.parentID, parent.Name, parent.age, parent.Address,
child.ChildID, child.Name, child.age
FROM parent
JOIN child ON parent.parentID = child.ParentID

OUTPUT :

12 | P a g e
c. Many-to-Many Relationship :
In this relationship, multiple rowsthe /record of first table can be related to
multiple rows in the second table and vice versa.
Create Table:

Example of many-to-many relationships from faculty to department entities

Example of many-to-many relationship from department to faculty entities:

13 | P a g e
SQL Query:
SELECT faculty.FacultyID, faculty.Faculty_Name, faculty.Course,
department.DepartmentID, department.Department_Name,
department.Department_Grading
FROM faculty
JOIN department ON faculty.FacultyID = department.FacultyID

Output:

SQL Query:
SELECT department.departmentID, department.Department_Name,
department.Department_Grading, faculty.FacultyID, faculty.Faculty_Name AS
facultyName, faculty.Course
FROM department
JOIN faculty ON department.DepartmentID = faculty.DepartmentID;

Output:

14 | P a g e
5. implementation of key and integrity constraints :
What Are Integrity Constraints in SQL?
Constraints available in SQL are:
1. NOT NULL
2. UNIQUE
3. PRIMARY KEY
4. FOREIGN KEY
5. CHECK
6. DEFAULT
7. CREATE INDEX
1. NOT NULL
o NULL means empty, i.e., the value is not available.
o Whenever a table's column is declared as NOT NULL, then the value for that
column cannot be empty for any of the table's records.
o There must exist a value in the column to which the NOT NULL constraint is
applied.

15 | P a g e
Syntax to apply the NOT NULL constraint during table creation:
1. CREATE TABLE TableName (ColumnName1 datatype NOT NULL, ColumnN
ame2 datatype,…., ColumnNameN datatype);

Example:
Create a student table and apply a NOT NULL constraint on one of the table's
column while creating a table.

Query :
CREATE TABLE student(StudentID INT NOT NULL, Student_FirstName VARCHAR
(20), Student_LastName VARCHAR(20), Student_PhoneNumber VARCHAR(20), St
udent_Email_ID VARCHAR(40));

2. UNIQUE KEY :
o Duplicate values are not allowed in the columns to which the UNIQUE
constraint is applied.
o The column with the unique constraint will always contain a unique value.
o This constraint can be applied to one or more than one column of a table,
which means more than one unique constraint can exist on a single table.
o Using the UNIQUE constraint, you can also modify the already created tables.

Syntax to apply the UNIQUE constraint on a single column:

CREATE TABLE TableName (ColumnName1 datatype UNIQUE, ColumnName2 dat
atype,…., ColumnNameN datatype);

16 | P a g e
Example:
Create a student table and apply a UNIQUE constraint on one of the table's column
while creating a table.
mysql> CREATE TABLE student(StudentID INT UNIQUE, Student_FirstName VAR
CHAR(20), Student_LastName VARCHAR(20), Student_PhoneNumber VARCHAR(
20), Student_Email_ID VARCHAR(40));

OUTPUT :
To verify that the unique constraint is applied to the table's column and the student
table is created successfully, we will execute the following query:
1. mysql> DESC student;

3. PRIMARY KEY
o PRIMARY KEY Constraint is a combination of NOT NULL and Unique
constraints.
o NOT NULL constraint and a UNIQUE constraint together forms a PRIMARY
constraint.
o The column to which we have applied the primary constraint will always
contain a unique value and will not allow null values.
o

17 | P a g e
Syntax of primary key constraint during table creation:
1. CREATE TABLE TableName (ColumnName1 datatype PRIMARY KEY, Colu
mnName2 datatype,…., ColumnNameN datatype);

Example:
Create a student table and apply the PRIMARY KEY constraint while creating a
table.
QUERY :
mysql> CREATE TABLE student(StudentID INT PRIMARY KEY, Student_FirstNam
e VARCHAR(20), Student_LastName VARCHAR(20), Student_PhoneNumber VAR
CHAR(20), Student_Email_ID VARCHAR(40));

To verify that the primary key constraint is applied to the table's column and the
student table is created successfully, we will execute the following query:

OUTPUT :
mysql> DESC student;

18 | P a g e
4. FOREIGN KEY :
o A foreign key is used for referential integrity.
o When we have two tables, and one table takes reference from another table,
i.e., the same column is present in both the tables and that column acts as a
primary key in one table. That particular column will act as a foreign key in
another table.
Syntax to apply a foreign key constraint during table creation:
1. CREATE TABLE tablename(ColumnName1 Datatype(SIZE) PRIMARY KEY,
ColumnNameN Datatype(SIZE), FOREIGN KEY( ColumnName ) REFERENC
ES PARENT_TABLE_NAME(Primary_Key_ColumnName));

The FOREIGN KEY constraint is used to prevent actions that would destroy links
between tables.
A FOREIGN KEY is a field (or collection of fields) in one table, that refers to
the PRIMARY KEY in another table.
The table with the foreign key is called the child table, and the table with the primary
key is called the referenced or parent table.
Persons Table

PersonID LastName FirstName Age

1 Hansen Ola 30

2 Svendson Tove 23

19 | P a g e
 3 Pettersen Kari 20

Orders Table

OrderID OrderNumber PersonID

1 77895 3

2 44678 3

3 22456 2

4 24562 1

The "PersonID" column in the "Persons" table is the PRIMARY KEY in the "Persons"
table.
The "PersonID" column in the "Orders" table is a FOREIGN KEY in the "Orders"
table.
The FOREIGN KEY constraint prevents invalid data from being inserted into the
foreign key column, because it has to be one of the values contained in the parent
table.
SQL FOREIGN KEY on CREATE TABLE
The following SQL creates a FOREIGN KEY on the "PersonID" column when the
"Orders" table is created:
Query :
CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

5. CHECK

20 | P a g e
 o Whenever a check constraint is applied to the table's column, and the user
wants to insert the value in it, then the value will first be checked for certain
conditions before inserting the value into that column.
Syntax to apply check constraint on a single column:
1. CREATE TABLE TableName (ColumnName1 datatype CHECK (ColumnNam
e1 Condition), ColumnName2 datatype,…., ColumnNameN datatype);
Example:
Create a student table and apply CHECK constraint to check for the age less than or
equal to 15 while creating a table.
Query:
CREATE TABLE student(StudentID INT, Student_FirstName VARCHAR(20), Stude
nt_LastName VARCHAR(20), Student_PhoneNumber VARCHAR(20), Student_Ema
il_ID VARCHAR(40), Age INT CHECK( Age <= 15));

To verify that the check constraint is applied to the student table's column, we will
execute the following query:
Output :
1. mysql> DESC student;

6. DEFAULT :
Whenever a default constraint is applied to the table's column, and the user has not
specified the value to be inserted in it, then the default value which was specified
while applying the default constraint will be inserted into that particular column.
Syntax to apply default constraint during table creation:

21 | P a g e
 1. CREATE TABLE TableName (ColumnName1 datatype DEFAULT Value, Colu
mnName2 datatype,…., ColumnNameN datatype);
Example:
Create a student table and apply the default constraint while creating a table.
Query:
CREATE TABLE student(StudentID INT, Student_FirstName VARCHAR(20), Stude
nt_LastName VARCHAR(20), Student_PhoneNumber VARCHAR(20), Student_Ema
il_ID VARCHAR(40) DEFAULT "anuja.k8@gmail.com");

To verify that the default constraint is applied to the student table's column, we will
execute the following query:
Output :
1. mysql> DESC student;

7. CREATE INDEX :
CREATE INDEX constraint is used to create an index on the table. Indexes are not
visible to the user, but they help the user to speed up the searching speed or
retrieval of data from the database.
Syntax to create an index on single column:
1. CREATE INDEX IndexName ON TableName (ColumnName 1);
Example:
Create an index on the student table and apply the default constraint while creating a
table.
Query:
CREATE INDEX idx_StudentID ON student (StudentID);

22 | P a g e
To verify that the create index constraint is applied to the student table's column, we
will execute the following query:
1. mysql> DESC student;

6. Nested Query :
In SQL, a nested query involves a query that is placed within another query. Output
of the inner query is used by the outer query. A nested query has two SELECT
statements: one for the inner query and another for the outer query.

Syntax of Nested Queries :

23 | P a g e
The basic syntax of a nested query involves placing one query inside of another
query. Inner query or subquery is executed first and returns a set of values that are
then used by the outer query. The syntax for a nested query is as follows:
SELECT column1, column2, ...
FROM table1
WHERE column1 IN ( SELECT column1
FROM table2
WHERE condition );

Types of Nested Queries in SQL :

Subqueries can be either correlated or non-correlated

Non-correlated (or Independent) Nested Queries :

Non-correlated (or Independent) subqueries are executed independently of the
outer query. Their results are passed to the outer query.
Execution Order in Independent Nested Queries :
In independent nested queries, the execution order is from the innermost query to
the outer query. An outer query won't be executed until its inner query completes its
execution. The outer query uses the result of the inner query.
Operators Used in Independent Nested Queries :
IN Operator

24 | P a g e
This operator checks if a column value in the outer query's result is present in the
inner query's result. The final result will have rows that satisfy the IN condition.
NOT IN Operator
This operator checks if a column value in the outer query's result is not present in the
inner query's result. The final result will have rows that satisfy the NOT IN condition.
ALL Operator
This operator compares a value of the outer query's result with all the values of the
inner query's result and returns the row if it matches all the values.
ANY Operator
This operator compares a value of the outer query's result with all the inner query's
result values and returns the row if there is a match with any value.

Correlated Nested Queries :

Correlated subqueries are executed once for each row of the outer query. They use
values from the outer query to return results.
Execution Order in Co-related Nested Queries :
In correlated nested queries, the inner query uses values from the outer query, and
the execution order is different from that of independent nested queries.
 First, the outer query selects the first row.
 Inner query uses the value of the selected row. It executes its query and
returns a result set.
 Outer query uses the result set returned by the inner query. It determines
whether the selected row should be included in the final output.
 Steps 2 and 3 are repeated for each row in the outer query's result set.
 This process can be resource-intensive. It may lead to performance issues if
the query is not optimized properly.
Operators Used in Co-related Nested Queries :
In co-related nested queries, the following operators can be used
EXISTS Operator
This operator checks whether a subquery returns any row. If it returns at least one
row. EXISTS operator returns true, and the outer query continues to execute. If the

25 | P a g e
subquery returns no row, the EXISTS operator returns false, and the outer query
stops execution.
NOT EXISTS Operator
This operator checks whether a subquery returns no rows. If the subquery returns no
row, the NOT EXISTS operator returns true, and the outer query continues to
execute. If the subquery returns at least one row, the NOT EXISTS operator returns
false, and the outer query stops execution.
ANY Operator
This operator compares a value of the outer query's result with one or more values
returned by the inner query. If the comparison is true for any one of the values
returned by the inner query, the row is included in the final result.
ALL Operator
This operator compares a value of the outer query's result with all the values
returned by the inner query. Only if the comparison is true for all the values returned
by the inner query, the row is included in the final result.
These operators are used to create co-related nested queries that depend on values
from the outer query for execution.
Examples :
Consider the following sample table to execute nested queries on these.
Table: employees table

emp_id emp_name dept_id

1 John 1

2 Mary 2

3 Bob 1

4 Alice 3

5 Tom 1

Table: departments table

dept_id dept_name

1 Sales

2 Marketing

26 | P a g e
 3 Finance

Table: sales table

sale_id emp_id sale_amt

1 1 1000

2 2 2000

3 3 3000

4 1 4000

5 5 5000

6 3 6000

7 2 7000

Example 1: Find the names of all employees in the Sales department.

Query :
SELECT emp_name
FROM employees
WHERE dept_id IN (SELECT dept_id
FROM departments
WHERE dept_name = 'Sales');
Output

emp_name

John

Bob

Tom

Example 2: Find the names of all employees who have made a sale
Query

27 | P a g e
 SELECT emp_name
FROM employees
WHERE EXISTS (SELECT emp_id
FROM sales
WHERE employees.emp_id = sales.emp_id);
Output

emp_name

John

Mary

Bob

Alice

Tom
This query selects all employees from the "employees" table where there exists a
sale record in the "sales" table for that employee.
Example 3: Find the names of all employees who have made sales greater
than $1000.
Query
SELECT emp_name
FROM employees
WHERE emp_id = ALL (SELECT emp_id
FROM sales
WHERE sale_amt > 1000);
Output

emp_name

John

Mary

Bob

Alice

Tom

28 | P a g e
This query selects all employees from the "employees" table. With the condition that
where their emp_id equals all the emp_ids in the "sales" table where the sale
amount is greater than $1000. Since all employees have made a sale greater
than $1000, all employee names are returned.

7.SUB Queries :

An SQL Subquery, is a SELECT query within another query. It is also known

as Inner query or Nested query and the query containing it is the outer query.
The outer query can contain the SELECT, INSERT, UPDATE, and DELETE
statements. We can use the subquery as a column expression, as a condition in SQL
clauses, and with operators like =, >, <, >=, <=, IN, BETWEEN, etc.

Rules to be followed :
 Subqueries must be enclosed within parentheses.
 Subqueries can be nested within another subquery.
 A subquery must contain the SELECT query and the FROM clause always.
 A subquery consists of all the clauses an ordinary SELECT clause can
contain: GROUP BY, WHERE, HAVING, DISTINCT, TOP/LIMIT, etc. However,
an ORDER BY clause is only used when a TOP clause is specified. It can't
include COMPUTE or FOR BROWSE clause.

29 | P a g e
  A subquery can return a single value, a single row, a single column, or a
whole table. They are called scalar subqueries.

Subqueries 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 ]
[WHERE]);

Example :
table named CUSTOMERS −

ID NAME AGE ADDRESS SALARY

1 Ramesh 32 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 Hyderabad 4500.00

7 Muffy 24 Indore 10000.00

Query :
SELECT * FROM CUSTOMERS

30 | P a g e
WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE SALARY > 4500);

Output :

ID NAME AGE ADDRESS SALARY

4 Chaitali 25 Mumbai 6500.00

5 Hardik 27 Bhopal 8500.00

7 Muffy 24 Indore 10000.00

Subqueries with the INSERT Statement :

We can also use the subqueries along with the INSERT statements. The data
returned by the subquery is inserted into another table.
The basic syntax is as follows :−
INSERT INTO table_name [ (column1 [, column2 ]) ]
SELECT [ *|column1 [, column2 ] FROM table1 [, table2 ]
[ WHERE VALUE OPERATOR ]

Subqueries with the UPDATE Statement :

A subquery can also be used with the UPDATE statement. You can update single or
multiple columns in a table using a subquery.
The basic syntax is as follows −
UPDATE table
SET column_name = new_value

31 | P a g e
[WHERE OPERATOR [VALUE](SELECT COLUMN_NAME FROM TABLE_NAME
[WHERE]);

Subqueries with the DELETE Statement :

The subquery can be used with the DELETE statement as well; 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)];

8.Grouping :
The SQL GROUP BY clause is used in conjunction with the SELECT statement to
arrange identical data into groups. This clause follows the WHERE clause in a
SELECT statement and precedes the ORDER BY and HAVING clauses (if they
exist).
The main purpose of grouping the records of a table based on particular columns is
to perform calculations on these groups. Therefore, The GROUP BY clause is
typically used with aggregate functions such as SUM(), COUNT(), AVG(), MAX(), or
MIN() etc.
Syntax :
the basic syntax of the SQL GROUP BY clause −
SELECT column_name(s)
FROM table_name
GROUP BY column_name(s);

32 | P a g e
Where, column_name(s) refers to the name of one or more columns in the table
that we want to group the data by and the table_name refers to the name of the
table that we want to retrieve data from.

GROUP BY Clause with Aggregate Functions :

Typically, we group the record of a table to perform calculations on them. Therefore,
the SQL GROUP BY clause is often used with the aggregate functions such as
SUM(), AVG(), MIN(), MAX(), COUNT(), etc.
Example :
The table Name Customers :

ID NAME AGE ADDRESS SALARY

1 Ramesh 32 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 Hyderabad 4500.00

7 Muffy 24 Indore 10000.00

Example-1:-
The following SQL query groups the CUSTOMERS table based on AGE and counts
the number of records in each group
Query :
SELECT AGE, COUNT(Name) FROM CUSTOMERS GROUP BY AGE;
Output :

AGE COUNT(Name)

32 1

25 2

23 1

27 1

22 1

33 | P a g e
 24 1

Example-2:
In the following query, we are finding the highest salary for each age −
Query :
SELECT AGE, MAX(salary) AS MAX_SALARY
FROM CUSTOMERS GROUP BY AGE;
Output :

AGE MAX_SALARY

32 2000.00

25 6500.00

23 2000.00

27 8500.00

22 4500.00

24 10000.00

GROUP BY Clause on Single Columns :

When we use the GROUP BY clause with a single column, all the rows in the table
that have the same value in that particular column will be merged into a single
record.
Example :
In the following example we are grouping the above created CUSTOMERS table by
the ADDRESS column and calculating the average salary of the customer from each
city −
Query :
SELECT ADDRESS, AVG(SALARY) as AVG_SALARY
FROM CUSTOMERS GROUP BY ADDRESS;
Output :
This would produce the following result −

34 | P a g e
 ADDRESS AVG_SALARY

Ahmedabad 2000.000000

Delhi 1500.000000

Kota 2000.000000

Mumbai 6500.000000

Bhopal 8500.000000

Hyderabad 4500.000000

Indore 10000.000000

GROUP BY Clause with Multiple Columns :

When we use the GROUP BY clause with multiple columns, all the rows in the table
that have the same values in all of the specified columns will be merged into a single
group.
Example :
In the following query we are grouping the records of the CUSTOMERS table based
on the columns ADDRESS and AGE and –

Query :
SELECT ADDRESS, AGE, SUM(SALARY) AS TOTAL_SALARY
FROM CUSTOMERS GROUP BY ADDRESS, AGE;
Output :

ADDRESS AGE TOTAL_SALARY

Ahmedabad 32 2000.00

Delhi 25 1500.00

Kota 23 2000.00

Mumbai 25 6500.00

Bhopal 27 8500.00

35 | P a g e
 Hyderabad 22 4500.00

Indore 24 10000.00

9.Aggregation :

SQL Aggregate functions are functions where the values of multiple rows are
grouped as input on certain criteria to form a single value result of more significant
meaning.
It is used to summarize data, by combining multiple values to form a single result.
SQL Aggregate functions are mostly used with the GROUP BY clause of the
SELECT statement.
Various Aggregate Functions :
1. Count()
2. Sum()

36 | P a g e
 3. Avg()
4. Min()
5. Max()
Aggregate Functions in SQL :
1.Count():
 Count(*): Returns the total number of records .i.e 6.
 Count(salary): Return the number of Non-Null values over the column salary.
i.e 5.
 Count(Distinct Salary): Return the number of distinct Non-Null values over
the column salary .i.e 5.
2.Sum():
 sum(salary): Sum all Non-Null values of Column salary i.e., 3120.
 sum(Distinct salary): Sum of all distinct Non-Null values i.e., 3120..
3. Avg():
 Avg(salary) = Sum(salary) / count(salary) = 3120 / 5 = 624
 Avg(Distinct salary) = sum(Distinct salary) / Count(Distinct Salary) = 3120 /
5 = 624
4.Min():
 Min(salary): Minimum value in the salary column except NULL i.e., 403.
5.Max():
 Max(salary): Maximum value in the salary i.e., 802.
Example : Employee Table :

Id Name Salary

1 A 802

2 B 403

3 C 604

4 D 705

5 E 606

37 | P a g e
 Id Name Salary

6 F NULL

Queries :
Count the number of employees
SELECT COUNT(*) AS TotalEmployees FROM Employee;

Calculate the total salary

SELECT SUM(Salary) AS TotalSalary FROM Employee;

Find the average salary

SELECT AVG(Salary) AS AverageSalary FROM Employee;

Get the highest salary

SELECT MAX(Salary) AS HighestSalary FROM Employee;

Determine the lowest salary

SELECT MIN(Salary) AS LowestSalary FROM Employee;

Output :
TotalEmployees
6
TotalSalary
3120
AverageSalary
624
HighestSalary
802
LowestSalary
403

10. Ordering
The SQL ORDER BY Clause :
The SQL ORDER BY clause is used to sort the data in either ascending or
descending order, based on one or more columns.
ORDER BY is used with the SQL SELECT statement and is usually specified after
the WHERE, HAVING, and GROUP BY clauses.
Syntax
The basic syntax of the ORDER BY clause is as follows −

38 | P a g e
SELECT column-list
FROM table_name
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

ORDER BY Clause with ASC :

We can sort the result-set of a query in ascending order (based on one or more
columns) using the SQL ORDER BY clause by specifying ASC as the sort order.
Example :
CUSTOMERS

ID NAME AGE ADDRESS SALARY

1 Ramesh 32 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 Hyderabad 4500.00

7 Muffy 24 Indore 10000.00

QUERY :
SELECT * FROM CUSTOMERS ORDER BY NAME ASC;
Output :

ID NAME AGE ADDRESS SALARY

4 Chaitali 25 Mumbai 6500.00

5 Hardik 27 Bhopal 8500.00

3 Kaushik 23 Kota 2000.00

2 Khilan 25 Delhi 1500.00

6 Komal 22 Hyderabad 4500.00

7 Muffy 24 Indore 10000.00

39 | P a g e
1 Ramesh 32 Ahmedabad 2000.00

ORDER BY Clause with DESC :

To sort the result-set of a query in descending order (based on one or more
columns), we need to use the ORDER BY clause by specifying DESC as the sort
order.
Example :
SELECT * FROM CUSTOMERS ORDER BY NAME DESC;
Output :

ID NAME AGE ADDRESS SALARY

1 Ramesh 32 Ahmedabad 2000.00

7 Muffy 24 Indore 10000.00

6 Komal 22 Hyderabad 4500.00

2 Khilan 25 Delhi 1500.00

3 Kaushik 23 Kota 2000.00

5 Hardik 27 Bhopal 8500.00

4 Chaitali 25 Mumbai 6500.00

11. implementation of different types of joins :

40 | P a g e
1. Natural Join:
o A natural join is the set of tuples of all combinations in R and S that are equal
on their common attribute names.

o It is denoted by ⋈.

Example: Let's use the above EMPLOYEE table and SALARY table:
Input:
1. ∏EMP_NAME, SALARY (EMPLOYEE ⋈ SALARY)

Output:

EMP_NAME SALARY

Stephan 50000

Jack 30000

Harry 25000

2. Outer Join:
41 | P a g e
The outer join operation is an extension of the join operation. It is used to deal with
missing information.
Example:
EMPLOYEE

EMP_NAME STREET CITY

Ram Civil line Mumbai

Shyam Park street Kolkata

Ravi M.G. Street Delhi

Hari Nehru nagar Hyderabad

FACT_WORKERS

EMP_NAME BRANCH SALARY

Ram Infosys 10000

Shyam Wipro 20000

Kuber HCL 30000

Hari TCS 50000

Input:
1. (EMPLOYEE ⋈ FACT_WORKERS)

Output:

EMP_NAME STREET CITY BRANCH SALARY

Ram Civil line Mumbai Infosys 10000

Shyam Park street Kolkata Wipro 20000

Hari Nehru nagar Hyderabad TCS 50000

An outer join is basically of three types:

42 | P a g e
 a. Left outer join

b. Right outer join

c. Full outer join

a. Left outer join:

o Left outer join contains the set of tuples of all combinations in R and S that are
equal on their common attribute names.

o In the left outer join, tuples in R have no matching tuples in S.

o It is denoted by ⟕.

Example: Using the above EMPLOYEE table and FACT_WORKERS table

Input:
1. EMPLOYEE ⟕ FACT_WORKERS

EMP_NAME STREET CITY BRANCH SALARY

Ram Civil line Mumbai Infosys 10000

Shyam Park street Kolkata Wipro 20000

Hari Nehru street Hyderabad TCS 50000

Ravi M.G. Street Delhi NULL NULL

b. Right outer join:

o Right outer join contains the set of tuples of all combinations in R and S that
are equal on their common attribute names.

o In right outer join, tuples in S have no matching tuples in R.

o It is denoted by ⟖.

Example: Using the above EMPLOYEE table and FACT_WORKERS Relation

Input:
1. EMPLOYEE ⟖ FACT_WORKERS

Output:

43 | P a g e
 EMP_NAME BRANCH SALARY STREET CITY

Ram Infosys 10000 Civil line Mumbai

Shyam Wipro 20000 Park street Kolkata

Hari TCS 50000 Nehru street Hyderabad

Kuber HCL 30000 NULL NULL

c. Full outer join:

o Full outer join is like a left or right join except that it contains all rows from both
tables.

o In full outer join, tuples in R that have no matching tuples in S and tuples in S
that have no matching tuples in R in their common attribute name.

o It is denoted by ⟗.

Example: Using the above EMPLOYEE table and FACT_WORKERS table

Input:
1. EMPLOYEE ⟗ FACT_WORKERS

Output:

EMP_NAME STREET CITY BRANCH SALARY

Ram Civil line Mumbai Infosys 10000

Shyam Park street Kolkata Wipro 20000

Hari Nehru street Hyderabad TCS 50000

Ravi M.G. Street Delhi NULL NULL

Kuber NULL NULL HCL 30000

3. Equi join:

44 | P a g e
It is also known as an inner join. It is the most common join. It is based on matched
data as per the equality condition. The equi join uses the comparison operator(=).
Example:
CUSTOMER RELATION

CLASS_ID NAME

1 John

2 Harry

3 Jackson

PRODUCT

PRODUCT_ID CITY

1 Delhi

2 Mumbai

3 Noida

Input:
1. CUSTOMER ⋈ PRODUCT

Output:

CLASS_ID NAME PRODUCT_ID CITY

1 John 1 Delhi

2 Harry 2 Mumbai

3 Harry 3 Noida

12. view(updatable and non-updatable) :

45 | P a g e
Updatable View :
An updatable view is a special case of a deletable view. A deletable view becomes
an updatable view when at least one of its columns is updatable. A column of a view
is updatable when all of the following rules are true: The view is deletable.
An updatable view is a special case of a deletable view. A deletable view becomes
an updatable view when at least one of its columns is updatable.
A column of a view is updatable when all of the following rules are true:
 The view is deletable.
 The column resolves to a column of a table (not using a dereference
operation) and the READ ONLY option is not specified.
 All the corresponding columns of the operands of a UNION ALL have exactly
matching data types (including length or precision and scale) and matching
default values if the fullselect of the view includes a UNION ALL.
The following example uses constant values that cannot be updated. However, the
view is a deletable view and at least one of its columns is updatable. Therefore, it is
an updatable view.
Syntax :
CREATE VIEW updatable_view
(number, current_date, current_time, temperature)
AS
SELECT number, CURRENT DATE, CURRENT TIME, temperature)
FROM weather.forecast
WHERE number = 300

Example :

46 | P a g e
Customers :

ID NAME AGE ADDRESS SALARY

1 Ramesh 32 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 Hyderabad 4500.00

7 Muffy 24 Indore 10000.00

QUERY :
UPDATE CUSTOMERS_VIEW
SET AGE = 35 WHERE name = 'Ramesh';

OUTPUT :

ID NAME AGE ADDRESS SALARY

1 Ramesh 35 Ahmedabad 2000.00

NON UPDATABEL VIEW :

If a view is not updatable, statements such UPDATE , DELETE , and INSERT are
illegal and are rejected.

13. Relational set operations :

47 | P a g e
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:

48 | P a g e
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;
Output :

ID NAME

1 Jack

2 Harry

3 Jackson

4 Stephan

5 David

2. Union All :

49 | P a g e
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.
Advertisement
Union All query will be like:
1. SELECT * FROM First
2. UNION ALL
3. SELECT * FROM Second;
Output :

ID NAME

1 Jack

2 Harry

3 Jackson

3 Jackson

4 Stephan

5 David

50 | P a g e
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;
Output :

ID NAME

3 Jackson

4. Minus
51 | P a g e
 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;
Output :

ID NAME

1 Jack

2 Harry

52 | P a g e