1 / 52

Lecture8:Data Manipulation in SQL Advanced SQL queries

Lecture8:Data Manipulation in SQL Advanced SQL queries. Ref. Chapter5. Prepared by L. Nouf Almujally. The Process of Database Design. Sample Data in Customer Table. Sample Data in Product Table. Sample Data in Orders Table. EMPLOYEE. DEPARTMENT. Table orders ( Example 3). JOIN.

Download Presentation

Lecture8:Data Manipulation in SQL Advanced SQL queries

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related