DBMS Nested & Sub Queries Set operations

1
DATABASE MANAGEMENT
SYSTEM
Unit - III

2
Unit III
• SQL: Basic SQL querying (select and project) using where clause,
arithmetic & logical operations,
• SQL functions (Date and Time, Numeric, String conversion).
• Creating tables with relationship, implementation of key and integrity
constraints, grouping, aggregation, ordering,
•nested queries, sub queries,
•implementation of different types of joins,
•relational set operations

3
U3: Nested & Sub Queries
• Nested Queries:
▪ A nested query is a query that can be executed within
another query but doesn't need to be in a WHERE
clause (it can be in SELECT or other parts).
• Subqueries:
▪ A subquery is a query inside another query. It’s often
used in WHERE, HAVING, or FROM clauses.

4
Nested & Sub Queries: Example
CREATE TABLE products (
product_id NUMBER PRIMARY KEY,
product_name VARCHAR2(100),
price NUMBER
);
CREATE TABLE sales (
sale_id NUMBER PRIMARY KEY,
product_id NUMBER,
sale_date DATE,
quantity NUMBER,
FOREIGN KEY (product_id) REFERENCES products(product_id)
);

5
• Find Products with the Highest Price Sold
SELECT MAX(price)
FROM products
SELECT product_name, price
FROM products
WHERE price = (
);

6
• Find Products That Were Sold on 12-02-2025
SELECT product_id
FROM sales
WHERE sale_date = TO_DATE('2025-02-12', 'YYYY-MM-DD')
SELECT product_id, product_name
FROM products
WHERE product_id IN (
);

7
• Find Products that Have Never Been Sold

8
• Find Products that Have Never Been Sold
SELECT product_id
FROM sales
SELECT product_name
FROM products
WHERE product_id NOT IN (
);

9
• Find Products with Total Sales Greater Than 3

10
• Find Products with Total Sales Greater Than 3
SELECT product_id
FROM sales
GROUP BY product_id
HAVING SUM(quantity) > 3
SELECT product_id, product_name
FROM products
WHERE product_id IN (
);

11
• Find Products with a Price Higher Than the Average Price
SELECT AVG(price)
FROM products
SELECT product_name
FROM products
WHERE price > (
);

12
Set operations
•Union
•Intersect
•Minus

13
Select * from A
UNION
Select * from B
Select * from A
UNION ALL
Select * from B

15
MINUS
Select * from A
INTERSECT
Select * from B
Select * from A
MINUS
Select * from B

16
INTERSECT Operator
• INTERSECT is used to combine two SELECT statements,
but returns rows only from the first SELECT statement that
are identical to a row in the second SELECT statement
First
ID Name
1 Henry
2 Albert
Second
ID Name
2 Albert
3 Sudarshan
Select * from First
INTERSECT
Select * from Second
ID Name
2 Albert

17
MINUS Operator
• MINUS operator is used to return all rows in the first SELECT
statement that are not returned by the second SELECT statement.
First
ID Name
1 Henry
2 Albert
Second
ID Name
2 Albert
3 Sudarshan
Select * from First
MINUS
ID Name
1 Henry

18
Join clause
• Join is used to fetch data from two or more tables, which is
joined to appear as single set of data.
• Following are the types of JOIN:
▪ Inner
▪ Outer
▪ Left
▪ Right

20
Inner Join
select candidates.id,candidates.name,
employes.id,employes.name
from candidates
inner Join employes on
employes.name=candidates.name;

21
select
candidates.id,candidates.name,
from candidates

25
Inner Join (or) Equi join
• INNER JOIN keyword selects all rows from both the tables as long
as the condition satisfies
• Syntax:
SELECT column-name-list FROM
table-n1 INNER JOIN table-n2
WHERE table-n1.column-name = table-n2.column-name;

26
select
stcourse6.courseid,stdnt6.name
, stdnt6.age from stdnt6
inner join stcourse6
on stdnt6.rollno =
stcourse6.rollno;

29
LEFT JOIN Or Left Outer Join
• returns all rows of the table on the left side of the join and
matching rows for the table on the right side of join.
• Rows for which there is no matching row on right side, result-set
will contain null.
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1 LEFT JOIN table2
ON table1.matching_column = table2.matching_column;

30
select
from candidates
Left Join employes on

31
select
stdnt6.name,stcourse6.coursei
d from stdnt6
left join stcourse6 on
stcourse6.rollno=stdnt6.rollno;

32
Right Join
• RIGHT 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

33
select
from candidates
right Join employes on

34
select
stdnt6.name,stcourse6.coursei
d
from stdnt6
right Join stcourse6 on

35
Full Join
• 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

36
select
from candidates
full Join employes on

37
select
stdnt6.name,stcourse6.courseid
from stdnt6 full Join stcourse6
on

39
•Set Operations:
▪ Union, Union All, Intersect, Minus
•Join Operations:
▪ Inner Join, Left Join, Right Join, Full Join

40
Set Operation: UNION
• Used to combine the result-set of two or more SELECT
statements. Example:
First
ID Name
1 Henry
2 Albert
Second
ID Name
2 Albert
3 Sudarshan
Select * from First
Union
ID Name
1 Henry
2 Albert
3 Sudarshan
Select * from First
Union ALL
ID Name
1 Henry
2 Albert
2 Albert
3 Sudarshan

41
Set Operation: INTERSECT
• INTERSECT is used to combine two SELECT statements,
but returns rows only from the first SELECT statement that
are identical to a row in the second SELECT statement
First
ID Name
1 Henry
2 Albert
Second
ID Name
2 Albert
3 Sudarshan
Select * from First
INTERSECT
ID Name
2 Albert

42
Set Operation: MINUS
• Combines result of two select statements and return only those
result which belongs to first set of result.
First
ID Name
1 Henry
2 Albert
Second
ID Name
2 Albert
3 Sudarshan
Select * from First
MINUS
ID Name
1 Henry

from candidates

from candidates
Left Join employes on

from candidates
Right Join employes on

from candidates
full Join employes on

47
Unit IV
• Schema Refinement (Normalization): Purpose of
Normalization or schema refinement,
• concept of functional dependency,
• normal forms based on functional dependency,
• (1NF, 2NF and 3 NF),
• Boyce-Codd normal form(BCNF),
• Fourth normal form(4NF),
• Fifth Normal Form (5NF)

DBMS Nested & Sub Queries Set operations

More Related Content

What's hot

Similar to DBMS Nested & Sub Queries Set operations

More from Sreedhar Chowdam

Recently uploaded

DBMS Nested & Sub Queries Set operations