Hassan Tariq ADVANCE SQL MULTIPLE TABLES SQL provides

Hassan Tariq ADVANCE SQL

MULTIPLE TABLES: • SQL provides a convenient operation to retrieve information from multiple tables. • This operation is called join. • The join operation will combine the tables into one large table with all possible combinations (Math: Cartesian Product), and then it will filter the rows of this combined table to yield useful information. Hassan Tariq – DB CS-C

TYPES OF JOINS • 1 INNER JOIN • 2 LEFT OUTER JOIN RIGHT OUTER JOIN FULL OUTER JOIN • 3 • 4 HASSAN TARIQ – FAST NUCES Hassan Tariq – DB CS-C

INNER JOIN / NATURAL JOIN Hassan Tariq – DB CS-C

LEFT OUTER JOIN Hassan Tariq – DB CS-C

RIGHT OUTER JOIN Hassan Tariq – DB CS-C

RIGHT OUTER JOIN Hassan Tariq – DB CS-C

FULL OUTER JOIN

JOINS IN SQL � Connect two or more tables: Product PName Price Category Manufacturer Gizmo $19. 99 Gadgets Gizmo. Works Powergizmo $29. 99 Gadgets Gizmo. Works Single. Touch $149. 99 Photography Canon Multi. Touch $203. 99 Household Hitachi Company What is the Connection between them ? CName Stock. Price Country Gizmo. Works 25 USA Canon 65 Japan Hitachi 15 Japan 9

JOINS Product (pname, price, category, manufacturer) Company (cname, stock. Price, country) Find all products under $200 manufactured in Japan; return their names and prices. Join between Product and Company SELECT PName, Price FROM Product, Company WHERE Manufacturer=CName AND Country=‘Japan’ AND Price <= 200 Hassan Tariq – DB CS-C 10

JOINS IN SQL Product Company PName Price Category Manufacturer Cname Stock. Price Country Gizmo $19. 99 Gadgets Gizmo. Works 25 USA Powergizmo $29. 99 Gadgets Gizmo. Works Canon 65 Japan Single. Touch $149. 99 Photography Canon Hitachi 15 Japan Multi. Touch $203. 99 Household Hitachi SELECT PName, Price FROM Product, Company WHERE Manufacturer=CName AND Country=‘Japan’ AND Price <= 200 PName Price Single. Touch $149. 99 Hassan Tariq – DB CS-C 11

JOINS Product (pname, price, category, manufacturer) Company (cname, stock. Price, country) Find all countries that manufacture some product in the ‘Gadgets’ category. SELECT Country FROM Product, Company WHERE Manufacturer=CName AND Category=‘Gadgets’ Hassan Tariq – DB CS-C 12

JOINS IN SQL Product Company PName Price Category Manufacturer Cname Stock. Price Country Gizmo $19. 99 Gadgets Gizmo. Works 25 USA Powergizmo $29. 99 Gadgets Gizmo. Works Canon 65 Japan Single. Touch $149. 99 Photography Canon Hitachi 15 Japan Multi. Touch $203. 99 Household Hitachi SELECT Country FROM Product, Company WHERE Manufacturer=CName AND Category=‘Gadgets’ Country What is the problem ? What’s the solution ? ? ? Hassan Tariq – DB CS-C 13

NATURAL JOIN / INNER JOIN A Natural Join is a join operation that joins two tables by their common column. This operation is similar to the setting relation of two tables. SELECT a. comcol, a. col 1, b. col 2, expr 1, expr 2 ; FROM table 1 a, table 2 b ; WHERE a. comcol = b. comcol Hassan Tariq – DB CS-C

NATURAL JOIN / INNER JOIN eg. 25 Make a list of students and the instruments they learn. (Natural Join) id name class 980 1 id Same id Joi n Student id name class type 980 1 Music type 9801 Product Hassan Tariq – DB CS-C

NATURAL JOIN / INNER JOIN eg. 25 Make a list of students and the instruments they learn. (Natural Join) SELECT s. class, s. name, s. id, m. type ; FROM student s, music m ; WHERE s. id=m. id ORDER BY class, name Result Hassan Tariq – DB CS-C

NATURAL JOIN eg. 26 Find the number of students learning piano in each class. Three Parts : (1) Natural Join. (2) Condition: m. type="Piano" (3) GROUP BY class Hassan Tariq – DB CS-C

NATURAL JOIN eg. 26 Student Joi n Conditio m. type= n Product "Piano" Group By clas s Music Hassan Tariq – DB CS-C

NATURAL JOIN eg. 26 Find the number of students learning piano in each class. SELECT s. class, COUNT(*) ; FROM student s, music m ; WHERE s. id=m. id AND m. type="Piano" ; GROUP BY class ORDER BY class Result Hassan Tariq – DB CS-C

OUTER JOIN An Outer Join is a join operation that includes rows that have a match, plus rows that do not have a match in the other table. • • Used when rows from one table should be part of the result there are no related rows in a second table Direction must be specified – – Left/Right specify which table has the rows which should always be included Full specifies that rows from both tables should Hassan Tariq – DB CS-C be included even if no match between rows

LEFT OUTER JOIN Left Outer Join returns all matched rows, plus all unmatched rows from the table on the left of the join clause (use nulls in fields of non-matching tuples) SELECT s. sid, s. name, r. bid FROM Sailors s LEFT OUTER JOIN Reserves r ON s. sid = r. sid Returns all sailors & information on whether they have reserved boats Hassan Tariq – DB CS-C

SELECT S. SID, S. NAME, R. BID FROM SAILORS S LEFT OUTER JOIN RESERVES R ON S. SID = R. SID Hassan Tariq – DB CS-C

RIGHT OUTER JOIN Right Outer Join returns all matched rows, plus all unmatched rows from the table on the right of the join clause SELECT r. sid, b. bid, b. name FROM Reserves r RIGHT OUTER JOIN Boats b ON r. bid = b. bid Returns all boats & information on which ones are reserved. Hassan Tariq – DB CS-C

SELECT R. SID, B. BID, B. NAME FROM RESERVES R RIGHT OUTER JOIN BOATS B ON R. BID = B. BID Hassan Tariq – DB CS-C

FULL OUTER JOIN Full Outer Join returns all (matched or unmatched) rows from the tables on both sides of the join clause SELECT r. sid, b. bid, b. name FROM Reserves r FULL OUTER JOIN Boats b ON r. bid = b. bid Returns all boats & all information on reservations Hassan Tariq – DB CS-C

SELECT R. SID, B. BID, B. NAME FROM RESERVES R FULL OUTER JOIN BOATS B ON R. BID = B. BID Note: in this case it is the same as the ROJ because bid is a foreign key in reserves, so all reservations must have a corresponding tuple in boats. Hassan Tariq – DB CS-C

CARTESIAN JOIN � Typically used to generate lots of data quickly � Match each row from table 1 with every row from table 2 � Result is (table 1 row count)*(table 2 row count) � Using table list: SELECT * FROM Publishers, Titles � Using CROSS JOIN keywords: SELECT * FROM Publishers CROSS JOIN Titles Hassan Tariq – DB CS-C

SUBQUERIES � A subquery must be enclosed in the parenthesis. � A subquery must be put in the right hand of the comparison operator � A subquery cannot contain a ORDER-BY clause. � A query can contain more than one sub-queries. Hassan Tariq – DB CS-C

SUBQUERIES � 3 Subquery Types 1. 2. 3. � Single-row subquery - where the subquery returns only one row. Multiple-row subquery - where the subquery returns multiple rows. Multiple column subquery - where the subquery returns multiple columns. Another name for these query types is: Correlated Subquery. Hassan Tariq – DB CS-C

SUBQUERIES Correlated Subqueries � � � Are dependent on their outer query* Will be executed many times while it’s outer queries is being processed, running once for each row selected by the outer query. Can be in the HAVING OR WHERE clauses Hassan Tariq – DB CS-C

SUBQUERIES SELECT s. store_name, sl. store_sales, FROM store s, sales sl WHERE s. store_key = sl. store_key and sl. store_sales >=( SELCECT AVG(store_sales) FROM sales) Hassan Tariq – DB CS-C