Lecture8:Data Manipulation in SQL Advanced SQL queries

1. Lecture8:Data Manipulation in SQLAdvanced SQL queries Lecture8 Ref. Chapter5 Prepared by L. NoufAlmujally

2. The Process of Database Design Lecture8

3. Sample Data in Customer Table Lecture8

4. Sample Data in Product Table Lecture8

5. Sample Data in Orders Table Lecture8

6. EMPLOYEE DEPARTMENT Lecture8

7. Table orders ( Example 3) Lecture8

8. JOIN • Often two or more tables are needed at the same time to find all required data • These tables must be "joined" together • The formal JOIN basically, • it computes a new table from those to be joined, • the new table contains data in the matching rows of the individual tables. Lecture8

9. Types of JOIN • Different SQL JOINs • types of JOIN:. • JOIN: Return rows when there is at least one match in both tables ( INNER JOIN is the same as JOIN) • LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table • RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table • Full Outer Joins : retains rows that area unmatched in both the tables. NOTE: In all the above outer joins, the displayed unmatched columns are filled with NULLS. Lecture8

10. Types of JOIN Lecture8

11. SQL Examples of Joins ( 1) • Simple Join SELECT E.firstName, E.lastName, D.deptName FROM EMPLOYEE E, DEPARTMENT D WHERE E.deptNumber = D.deptNumber; Lecture8

12. This is the result from the matching This is the final result: Lecture8

13. SQL Examples of Joins ( 2 ) • Joining more than two tables SELECT E.firstName, E.lastName, P.projTitle FROM EMPLOYEE E, WORKS_ON W, PROJECT P WHERE E.employeeNo = W.employeeNo AND W.projNo = P.projNo; EMPLOYEE WORKS_ON Lecture8 PROJECT

14. SQL Examples of Joins ( 3 ) • List customers (by customer number, name and address) who have ordered the product 100. SELECT c.custNo, custName, custSt, custCity FROM customer c, orders o WHERE c.custNo=o.custNo AND prodNo=100; Lecture8

15. SQL Examples of Joins ( 4 ) • Find the total price of the products ordered by customer 1. SELECT sum(price*quantity) FROM orders, product WHERE orders.prodNo = product.prodNo AND custNo = 1; Lecture8

16. Outer Joins in Oracle SQL • Put an (+) on the potentially deficient side, ie the side where nulls may be added • The (+) operator is placed in the join condition next to the table that is allowed to have NULL values. • Example (Left Outer Join): List all customers, and the products ordered if they have ordered some products. SELECT c.custNo, o.prodNo, quantity FROM customer c, orders o WHERE c.custNo = o.custNo (+); • Note: • a table may be outer joined with only one other table. • Which table column to use is important, eg, in above example, do not use o.custNo in place of c.custNo in the SELECT list. Lecture8

17. 1) Inner Join SQL Example SELECT Student_Name, Advisor_Name FROM Students, Advisors WHEREStudents.Advisor_ID= Advisors.Advisor_ID; Lecture8

18. 2) Left Outer Join SQL Example SELECT Student_Name, Advisor_Name FROM Students, Advisors WHEREStudents.Advisor_ID= Advisors.Advisor_ID(+); Lecture8

19. 3) Right Outer Join SQL Example SELECT Student_Name, Advisor_Name FROM Students, Advisors WHEREStudents.Advisor_ID(+)= Advisors.Advisor_ID; Lecture8

20. 4) Full Outer Join SQL Example SELECT Student_Name, Advisor_Name FROM Students , Advisors WHEREStudents.Advisor_ID (+)= Advisors.Advisor_ID (+) ; Lecture8

21. Lecture8

22. Nested Queries (1) • Query results are tables, which can also be queried. SELECT * FROM (SELECT prodNo, sum(quantity) AS sum FROM orders GROUP BY prodNo); WHERE sum>10; Equivalent to SELECT prodNo, sum(quantity) as sum FROM orders GROUP BY prodNo HAVING sum(quantity)>10; • The inner query is referred to as a subquery Lecture8

23. Nested Queries (2) • If the query result is a single value, it can be treated as a value, and be compared with other values. Example: Find products with price more than average SELECT prodNo, price FROM product WHERE price > (SELECT AVG(price) FROM product); Lecture8

24. Subquery • Subquery with equality: SELECT firstName, lastName FROM EMPLOYEE WHERE deptNumber =(SELECT deptNumber FROM DEPARTMENT WHERE mailNumber = 39); Lecture8

25. Subquery • Subquery with aggregate function: SELECT firstName, lastName, salary FROM EMPLOYEE WHERE salary > (SELECT avg(salary) FROM EMPLOYEE); Lecture8

26. Subquery • Nested Subquery (use of IN): SELECT firstName, lastName FROM EMPLOYEE WHERE deptNumberIN(SELECT deptNumber FROM DEPARTMENT WHERE location = ‘Bundoora’); Lecture8

27. Subquery • List the products ordered by customers living in Riyadh. SELECT prodNo FROM orders WHERE custNoIN (SELECT custNo FROM customer WHERE custCity=‘Riyadh'); - This query is equivalent to SELECT prodNo FROM orders o, customer c WHERE o.custNo =c.custNo AND custCity = ‘Riyadh'; Lecture8

28. Lecture8

29. Queries using EXISTS or NOT EXISTS Queries using EXISTS • Designed for use only with subqueries • EXISTS return true if there exists at least one row in the result table returned by the subquery, it is false if the subquery returns an empty result table. • Syntax Lecture8 SELECT column_name FROM table_nameWHERE EXISTS|NOT EXISTS( subquery );

30. Queries using EXISTS or NOT EXISTS • Example SELECT firstName, lastName FROM EMPLOYEE E WHERE EXISTS (SELECT * FROM DEPARTMENT D WHERE E.deptNumber = D.deptNumber AND D.location = ‘Bendigo’); Lecture8

31. Example . EXISTS • Find all customers who have ordered some products. SELECT * FROM customer c WHERE exists (SELECT * FROM orders o WHERE o.custNo =c.custNo); • If the subquery is not empty, then the exists condition is true. Lecture8

32. Example . NOT EXISTS • Find all customers such that no order made by them has a quantity less than 2. SELECT * FROM customer c WHERE NOT EXISTS (SELECT * FROM orders o WHERE o.custNo = c.custNo AND quantity <2); Lecture8

33. Lecture8

34. UNION • The UNION operator is used to combine the result-set of two or more SELECT statements. • Notice that each SELECT statement within the UNION must • have the same number of columns. • The columns must also have similar data types. • the columns in each SELECT statement must be in the same order. • Combines the results of two SELECT statements into one result set, and then eliminates any duplicate rows from that result set. • SQL UNION Syntax Lecture8 • SELECT column_name(s) FROM table_name1UNIONSELECT column_name(s) FROM table_name2

35. UNION • Note: The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL. • UNION ALL Combines the results of two SELECT statements into one result set. • SQL UNION ALL Syntax Lecture8 • SELECT column_name(s) FROM table_name1UNION ALLSELECT column_name(s) FROM table_name2

36. UNION Example 1 “Employees_USA” "Employees_Norway" • list all the different employees in Norway and USA SELECT E_Name FROM Employees_NorwayUNION SELECT E_Name FROM Employees_USA; Lecture8

37. UNION Example 2 SELECT custNo FROM customer WHERE custCity=‘Riyadh' UNION SELECT custNo FROM orders WHERE prodNo=102; // union of the two queries Lecture8

38. MINUS • the MINUS operator returns only unique rows returned by the first query but not by the second. • Takes the result set of one SELECT statement, and removes those rows that are also returned by a second SELECT statement. • SQL MINUS Syntax Lecture8 • SELECT column_name(s) FROM table_name1MINUSSELECT column_name(s) FROM table_name2

39. MINUS Example 1 SELECT prodNo FROM product MINUS SELECT prodNo FROM orders; //difference from the two queries Lecture8

40. INTERSECT •  the INTERSECT operator returns only those rows returned by both queries. • Returns only those rows that are returned by each of two SELECT statements. • SQL INTERSECT Syntax Lecture8 • SELECT column_name(s) FROM table_name1INTERSECTSELECT column_name(s) FROM table_name2

41. INTERSECT SELECT custNo FROM customer WHERE custCity=‘Riyadh' INTERSECT SELECT custNo FROM orders WHERE prodNo=102; // intersect of the two queries Lecture8

42. Examples EMPLOYEE Lecture8 DEPENDENT

43. Examples SELECT employeeNo, firstName, lastName FROMEMPLOYEE UNION SELECT employeeNo, firstName, lastName FROMDEPENDENT; SELECT employeeNo FROMEMPLOYEE INTERSECT SELECT employeeNo FROMDEPENDENT Lecture8

44. Lecture8

45. EMPLOYEE Table Example1 SELECT department_id, count(*), max(salary), min(salary) FROM employee GROUP BY department_id; Lecture8

46. EMPLOYEE Table Example2 SELECT Employee_ID, FIRST_NAME,DEPARTMENT_ID FROM employee WHERE salary=(SELECT max(salary) FROM employee); Lecture8

47. EMPLOYEE Table Example3 SELECT Employee_ID FROM employee WHERE department_id IN (SELECT department_id FROM department WHERE name=’SALES’); Lecture8

48. EMPLOYEE Table Example4 SELECT name FROM department d WHERE NOT EXISTS (SELECT last_name FROM employee e WHERE d.department_id=e.department_id); Lecture8

49. EMPLOYEE Table Example5 SELECT last_name, d.department_id, d.name FROM employee e, department d WHERE e.department_id (+)= d.department_id AND d.department_id in (SELECT department_id FROM department WHERE name IN (‘RESEARCH’ , ’OPERATIONS’)); Lecture8

50. EMPLOYEE Table Example6 SELECT employee_id, First_name, Last_name, Salary FROM employee WHERE last_name like ‘D%’; Lecture8