Uploaded bySreedhar Chowdam
DBMS Nested & Sub Queries Set operations
Nested Queries Sub Queries Set Operations
DBMS Nested & Sub Queries Set operations
- 1.
- 2. 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. 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. 4 Nested & SubQueries: 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. 5 Nested & SubQueries: Example • Find Products with the Highest Price Sold SELECT MAX(price) FROM products SELECT product_name, price FROM products WHERE price = ( );
- 6. 6 Nested & SubQueries: Example • 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. 7 Nested & SubQueries: Example • Find Products that Have Never Been Sold
- 8. 8 Nested & SubQueries: Example • Find Products that Have Never Been Sold SELECT product_id FROM sales SELECT product_name FROM products WHERE product_id NOT IN ( );
- 9. 9 Nested & SubQueries: Example • Find Products with Total Sales Greater Than 3
- 10. 10 Nested & SubQueries: Example • 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. 11 Nested & SubQueries: Example • Find Products with a Price Higher Than the Average Price SELECT AVG(price) FROM products SELECT product_name FROM products WHERE price > ( );
- 12.
- 13. 13 Select * fromA UNION Select * from B Select * from A UNION ALL Select * from B
- 14.
- 15. 15 MINUS Select * fromA INTERSECT Select * from B Select * from A MINUS Select * from B
- 16. 16 INTERSECT Operator • INTERSECTis 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. 17 MINUS Operator • MINUSoperator 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 Select * from Second ID Name 1 Henry
- 18. 18 Join clause • Joinis 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
- 19.
- 20. 20 Inner Join select candidates.id,candidates.name, employes.id,employes.name fromcandidates inner Join employes on employes.name=candidates.name;
- 21. 21 select candidates.id,candidates.name, employes.id,employes.name from candidates inner Joinemployes on employes.name=candidates.name;
- 22.
- 23.
- 24. 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;
- 25. 26 select stcourse6.courseid,stdnt6.name , stdnt6.age fromstdnt6 inner join stcourse6 on stdnt6.rollno = stcourse6.rollno;
- 26. 29 LEFT JOIN OrLeft 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;
- 27. 30 select candidates.id,candidates.name, employes.id,employes.name from candidates Left Joinemployes on employes.name=candidates.name;
- 28. 31 select stdnt6.name,stcourse6.coursei d from stdnt6 leftjoin stcourse6 on stcourse6.rollno=stdnt6.rollno;
- 29. 32 Right Join • RIGHTJOIN: 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
- 30. 33 select candidates.id,candidates.name, employes.id,employes.name from candidates right Joinemployes on employes.name=candidates.name;
- 31. 34 select stdnt6.name,stcourse6.coursei d from stdnt6 right Joinstcourse6 on stcourse6.rollno=stdnt6.rollno;
- 32. 35 Full Join • FULLJOIN: 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
- 33. 36 select candidates.id,candidates.name, employes.id,employes.name from candidates full Joinemployes on employes.name=candidates.name;
- 34. 37 select stdnt6.name,stcourse6.courseid from stdnt6 fullJoin stcourse6 on stcourse6.rollno=stdnt6.rollno;
- 35.
- 36. 39 •Set Operations: ▪ Union,Union All, Intersect, Minus •Join Operations: ▪ Inner Join, Left Join, Right Join, Full Join
- 37. 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 Select * from Second ID Name 1 Henry 2 Albert 3 Sudarshan Select * from First Union ALL Select * from Second ID Name 1 Henry 2 Albert 2 Albert 3 Sudarshan
- 38. 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 Select * from Second ID Name 2 Albert
- 39. 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 Select * from Second ID Name 1 Henry
- 40. select candidates.id,candidates.name, employes.id,employes.name from candidates innerJoin employes on employes.name=candidates.name;
- 41. select candidates.id,candidates.name, employes.id,employes.name from candidates LeftJoin employes on employes.name=candidates.name;
- 42. select candidates.id,candidates.name, employes.id,employes.name from candidates RightJoin employes on employes.name=candidates.name;
- 43. select candidates.id,candidates.name, employes.id,employes.name from candidates fullJoin employes on employes.name=candidates.name;
- 44. 47 Unit IV • SchemaRefinement (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)