Unit - 4

Advanced SQL
 Set Operators
SET operators are special type of operators which are used to combine
the result of two queries.
Operators covered under SET operators are:
• UNION

• UNION ALL

• INTERSECT

• MINUS

There are certain rules which must be followed to perform operations

using SET operators in SQL. Rules are as follows:
• The number and order of columns must be the same.

• Data types must be compatible.

 1. UNION:
• UNION will be used to combine the result of two select statements.
• Duplicate rows will be eliminated from the results obtained after
performing the UNION operation.
Example:
SELECT *FROM t_employees UNION SELECT *FROM t2_employees;
• Here, in a single query, we have written two SELECT queries. The
first SELECT query will fetch the records from the t_employees table
and perform a UNION operation with the records fetched by the
second SELECT query from the t2_employees table.
• Since we have performed union operation between both the tables,
so only the records from the first and second table are displayed
except for the duplicate records.
 2. UNION ALL
• This operator combines all the records from both the queries.
• Duplicate rows will be not be eliminated from the results obtained
after performing the UNION ALL operation.
Example:
SELECT *FROM t_employees UNION ALL SELECT *FROM t2_employees;
• Here, in a single query, we have written two SELECT queries. The
first SELECT query will fetch the records from the t_employees table
and perform UNION ALL operation with the records fetched by the
second SELECT query from the t2_employees table.
• Since we have performed union all operation between both the
tables, so all the records from the first and second table are
displayed, including the duplicate records.
 3. INTERSECT:
• It is used to combine two SELECT statements, but it only returns the
records which are common from both SELECT statements.
Example:
SELECT *FROM t_employees INTERSECT SELECT *FROM t2_employees;
• Here, in a single query, we have written two SELECT queries. The
first SELECT query will fetch the records from the t_employees table
and perform INTERSECT operation with the records fetched by the
second SELECT query from the t2_employees table.
• Since we have performed intersect operation between both the
tables, so only the common records from both the tables are
displayed.
4. MINUS
• It displays the rows which are present in the first query but absent
in the second query with no duplicates.
Example:
SELECT *FROM t_employees MINUS SELECT *FROM t2_employees;
• Here, in a single query, we have written two SELECT queries. The
first SELECT query will fetch the records from the t_employees table
and perform MINUS operation with the records fetched by the
second SELECT query from the t2_employees table.
• Since we have performed Minus operation between both the
tables, so only the unmatched records from both the tables are
displayed.
 SQL Joins
• The SQL JOIN is a command clause that combines records from
two or more tables in a database.
• It is a means of combining data in fields from two tables by using
values common to each table or based on a related column
between those tables.
• If you're working with databases, at some point in your work you
will likely need to use SQL JOINs.
• The purpose of JOINs in SQL is to access data from multiple tables
based on logical relationships between them.
• JOINS are used to fetch data from database tables and represent
the result dataset as a separate table.
 Cross Join:
• If we use the cross join to combine two different tables, then we will
get the Cartesian product of the sets of rows from the joined table.
• When each row of the first table is combined with each row from the
second table, it is known as Cartesian join or cross join.
• After performing the cross join operation, the total number of rows
present in the final table will be equal to the product of the number
of rows present in table 1 and the number of rows present in table
2.
Syntax:
SELECT TableName1.columnName1, TableName2.columnName2 FRO
M TableName1 CROSS JOIN TableName2 ON TableName1.ColumnNam
e = TableName2.ColumnName;
Example:
SELECT * FROM MatchScore CROSS JOIN Departments;
We have used the SELECT command with the asterisk to retrieve all the columns
present in the MatchScore and Departments table.
Then we have used the CROSS JOIN keyword to perform the cross join operation
on the MatchScore and Departments table.
Since there are 4 records in the MatchScore and 3 records in the Departments
table, after performing the cross join operation, we will get 12 rows.
Natural Join:
• Natural join is an SQL join operation that creates join on the base of
the common columns in the tables.
• To perform natural join there must be one common
attribute(Column) between two tables. Natural join will retrieve
from multiple relations.
Syntax :
SELECT *
FROM TABLE1
NATURAL JOIN TABLE2;
Features of Natural Join :
• It will perform the Cartesian product.

• It finds consistent tuples and deletes inconsistent tuples.

• Then it deletes the duplicate attributes.

Query to implement SQL Natural Join :
SELECT *
FROM EMPLOYEE
NATURAL JOIN DEPARTMENT;
Difference between the WHERE and ON clause:
• The way both of these clauses can be used to help join data is
through defining the condition on which the two tables are joined.
Let's use an example data set of facebook friends and linkedin
connections.

• We want to see the people who are both our friend and our
connection.
• So in this case it would only be Matt. Let's now query using a variety
of defining the JOIN condition.
All three of these queries produce the same correct result:
SELECT *
FROM facebook
JOIN linkedin
ON facebook.name = linkedin.name

SELECT *
FROM facebook
JOIN linkedin
WHERE facebook.name = linkedin.name

SELECT *
FROM facebook, linkedin
WHERE facebook.name = linkedin.name
• The first two are types of explicit joins and the last is an implicit join.
• An explicit JOIN explicitly tells you how to JOIN the data by
specifying the type of JOIN and the join condition in the ON clause.
• An Implicit JOIN does not specify the JOIN type and use the WHERE
clause to define the join condition.
Outer joins:
Outer joins are joins that return matched values and
unmatched values from either or both tables. There A B

are a few types of outer joins:

• LEFT JOIN returns only unmatched rows from the
left table, as well as matched rows in both tables. A B

• RIGHT JOIN returns only unmatched rows from the

right table , as well as matched rows in both tables.
• FULL OUTER JOIN returns unmatched rows from
A B
both tables, as well as matched rows in both tables.
Note: LEFT JOIN is also refered to as OUTER LEFT
JOIN. RIGHT JOIN is also refered to as OUTER
RIGHT JOIN. FULL OUTER JOIN is also refered to
as OUTER JOIN.
Sample Data Set(Example):
• Imagine that you are teaching an American Literature class.
• You have ten students, and you want each of them to read a
different book from a list of pre-approved classic American
novels.
• Some students have chosen the book they will read, while
others have not done so yet.
• You have created a table that lists the students along with
their student ID numbers, and another table that lists books
with their title, author, ISBN, and the ID of the student who
will be reading the book, if someone has chosen it.
To do an outer join on our sample data, we
could use the following query:
SELECT students.name, books.title
FROM students
FULL OUTER JOIN books ON
students.student_id=books.student_
id;
• In this example, we are selecting the names
from the students table and the book titles
from the books table.
• Records are matched using
the student_id column in both tables.
• With the full outer join, we are able to see all
of the students, including those who have
not chosen a book yet. We can also see all of
the books, including those that have not yet
been chosen.
 SQL Functions (Date and Time)
The following table lists the most important built-in
date functions in MySQL:
Function Description

NOW() Returns the current date and time

CURDATE() Returns the current date

CURTIME() Returns the current time

DATE() Extracts the date part of a date or date/time expression

Example:
SELECT NOW () AS Current_Date_Time;
The following table lists the most important built-in
date functions in SQL Server:
Function​ Description​

GETDATE()​ Returns the current date and time​

DATEPART()​ Returns a single part of a date/time​
DATEADD()​ Adds or subtracts a specified time interval from a date​
DATEDIFF()​ Returns the time between two dates​
CONVERT()​ Displays date/time data in different formats​
 Numeric
SQL numeric functions are used primarily for numeric manipulation and/or mathematical
calculations.
Sr.No. Function & Description
ABS()Returns the absolute value of numeric expression( ABS changes negative values
1 to positive values. ABS has no effect on zero or positive values)

ACOS()Returns the arccosine of numeric expression. The specified number must be

2 between -1 to 1, otherwise this function returns NULL.

3 EXP() The EXP() function returns e raised to the power of a specified number.

4 FLOOR() The FLOOR() function returns the largest integer value that is smaller than or equal to a
number. [SELECT FLOOR(25.75) AS FloorValue;  25]

5 FORMAT()Returns a numeric expression rounded to a number of decimal places.

GREATEST()Returns the largest value of the input expressions[SELECT

6
GREATEST(3, 12, 34, 8, 25)  34]
INTERVAL() Syntax: INTERVAL(N,N1,N2,N3,...) It returns 0 if 1st number is less than the 2nd
7 number and 1 if 1st number is less than the 3rd number and so on or -1 if 1st number is NULL. All
arguments are treated as an integer.
Sr.No. Function & Description
LEAST()Returns the minimum-valued input when given two
8
or more.
LOG()Returns the natural logarithm of the passed numeric
9
expression.
LOG10()Returns the base-10 logarithm of the passed
10
numeric expression.
MOD()Returns the remainder of one expression by diving
11
by another expression.
OCT()Returns the string representation of the octal value of
12 the passed numeric expression. Returns NULL if passed
value is NULL.
13 PI()Returns the value of pi
 String
Function Description LTRIM Removes leading spaces from a string

ASCII Returns the ASCII value for the specific NCHAR Returns the Unicode character based on
character the number code
CHAR Returns the character based on the ASCII REVERSE Reverses a string and returns the result
code
RIGHT Extracts a number of characters from a
CONCAT Adds two or more strings together
string (starting from right) [SELECT RIGHT("SQL
LEFT Extracts a number of characters from a Tutorial is cool", 4) AS ExtractString;]
string (starting from left) [SELECT LEFT('SQL
Tutorial', 3) AS ExtractString;] RTRIM Removes trailing spaces from a string

LEN Returns the length of a string STR Returns a number as string

LOWER Converts a string to lower-case UPPER Converts a string to upper-case

 Conversion
• The CONVERT() function converts a value (of any type) into a
specified datatype.
Syntax:
CONVERT(data_type(length), expression, style)
Value Description
data_type Required. The datatype to convert expression to. Can be one of the following:
bigint, int, smallint, tinyint, bit, decimal, numeric, money, smallmoney, float,
real, datetime, smalldatetime, char, varchar, text, nchar, nvarchar, ntext,
binary, varbinary, or image
(length) Optional. The length of the resulting data type (for char, varchar, nchar,
nvarchar, binary and varbinary)
expression Required. The value to convert to another data type
style Optional. The format used to convert between data types, such as a date or
string format. Can be one of the following values:

Converting datetime to character:

Without With century Input/Output Standard
century
0 100 mon dd yyyy hh:miAM/PM Default
1 101 mm/dd/yyyy US
2 102 yyyy.mm.dd ANSI
3 103 dd/mm/yyyy British/French
4 104 dd.mm.yyyy German
5 105 dd-mm-yyyy Italian
6 106 dd mon yyyy -
7 107 Mon dd, yyyy -
8 108 hh:mm:ss -
9 109 mon dd yyyy hh:mi:ss:mmmAM (or Default + millisec
PM)
10 110 mm-dd-yyyy USA
Example:
SELECT CONVERT(varchar, 25.65);
SELECT CONVERT(datetime, '2017-08-25');
SELECT CONVERT(varchar, '2017-08-25', 101);
 Sub Queries
• 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.
Where Subqueries:
SELECT ord_num, ord_amount, advance_amount, cust_code, agent_code
FROM orders
WHERE agent_code = (
SELECT agent_code
FROM agents
WHERE agent_name = 'Alex'
);
In Subqueries:
SELECT ord_num, ord_amount, ord_date, cust_code, agent_code
FROM orders
WHERE agent_code IN(
SELECT agent_code FROM agents
WHERE woring_area = 'Mumbai'
);
Not in operator:
SELECT ord_num, ord_amount, ord_date, cust_code,
agent_code
FROM orders
WHERE agent_code NOT IN(
SELECT agent_code FROM agents
WHERE working_area='Mumbai'
);
 Multirow subquery operators: Any and All
The ANY and ALL operators allow you to perform a comparison between a
single column value and a range of other values.
The SQL ANY Operator:
• returns a Boolean value as a result
• returns TRUE if ANY of the subquery values meet the condition
ANY means that the condition will be true if the operation is true for any of
the values in the range.
Example:
The following SQL statement lists the ProductName if it finds ANY records in
the OrderDetails table has Quantity equal to 10 (this will return TRUE
because the Quantity column has some values of 10):
SELECT ProductName
FROM Products
WHERE ProductID = ANY
(SELECT ProductID
FROM OrderDetails
WHERE Quantity = 10);
 The SQL ALL Operator:
• returns a Boolean value as a result
• returns TRUE if ALL of the subquery values meet the condition
• is used with SELECT, WHERE and HAVING statements
ALL means that the condition will be true only if the operation is true for all
values in the range.
Example:
The following SQL statement lists the ProductName if ALL the records in the
OrderDetails table has Quantity equal to 10. This will of course return FALSE
because the Quantity column has many different values (not only the value
of 10):
SELECT ProductName
FROM Products
WHERE ProductID = ALL
(SELECT ProductID
FROM OrderDetails
WHERE Quantity = 10);
From Subqueries:
A subquery can also be used in the FROM clause to return multiple
rows and columns.
The results returned by such a subquery are referred to a derived
table.
A derived table is useful when you want to work with a subset of data
from one or more tables without needing to create a view or
temporary table.
Attribute list Sub queries:
• The Select Statement uses the attribute list to indicate what columns to
project in the resulting set.
• Those columns can be attributes of base tables, computed attributes or
the result of an aggregate function(MIN, MAX, COUNT, SUM, and AVG).
• The attribute list can also include a subquery expression, also known as
an inline subquery.
• A subquery in the attribute list must return one value otherwise an error
is raised.
• List the difference between each product’s price and the average product
price.
Example:
SELECT P CODE, P PRICE, (SELECT AVG(P PRICE) FROM PRODUCT)
AS AVGPRICE,
P PRICE-(SELECT AVG(P PRICE) FROM PRODUCT) AS DIFF
FROM PRODUCT;
Correlated Subqueries:
• SQL Correlated Subqueries are used to select data from a table
referenced in the outer query.
• The subquery is known as a correlated because the subquery is
related to the outer query.
• In this type of queries, a table alias (also called a correlation name)
must be used to specify which table reference is to be used.
• Uncorrelated subquery executes the subquery first and provides the
value to the outer query, whereas correlated subquery references a
column in the outer query and executes the subquery once for each
row in the outer query.
Example:
SELECT a.ord_num,a.ord_amount,a.cust_code,a.agent_code
FROM orders a
WHERE a.agent_code=(
SELECT b.agent_code
FROM agents b WHERE b.agent_name='Alex');
The simplified form of above code is:
SELECT
a.ord_num,a.ord_amount,a.cust_code,a.agent_code
FROM orders a
WHERE a.agent_code='A003';
 Sequence
A sequence object can be defined as a user-defined object which is
bound to the schema(the “blueprint” of a database which describes
how the data may relate to other tables or other data models.) that
generates a numeric values sequence according to the increment
value that is defined at the time of the creation of the sequence.
The numeric values sequence that is generated can be in a descending
or ascending order depending upon an already preset incremental
value and it can also be started again (cycle) when it reaches its
termination limit.
A sequence is a database object that allows the automatic
generation of values, such as check numbers. Sequences are ideally
suited to the task of generating unique key values. Applications can
use sequences to avoid possible concurrency and performance
problems resulting from column values used to track numbers.
Syntax:
CREATE SEQUENCE sequence_name
START WITH initial_value
INCREMENT BY increment_value
MINVALUE minimum value
MAXVALUE maximum value
CYCLE|NOCYCLE ;

sequence_name: Name of the sequence.

initial_value: starting value from where the sequence starts.

Initial_value should be greater than or equal
to minimum value and less than equal to maximum value.

increment_value: Value by which sequence will increment itself.

Increment_value can be positive or negative.
minimum_value: Minimum value of the sequence.
maximum_value: Maximum value of the sequence.

cycle: When sequence reaches its set_limit

it starts from beginning.

nocycle: An exception will be thrown

if sequence exceeds its max_value.
Example:
CREATE SEQUENCE sequence_1
start with 1
increment by 1
minvalue 0
maxvalue 100
cycle;
Above query will create a sequence named sequence_1.Sequence will
start from 1 and will be incremented by 1 having maximum value 100.
Sequence will repeat itself from start value after exceeding 100.