Lecture 8 Data Manipulation in SQL Advanced SQL

Lecture 8: Data Manipulation in SQL Advanced SQL queries Ref. Chapter 5 1 Prepared by L. Nouf Almujally

Real World Domain Conceptual model (ERD) Relational Data Model Create schema (DDL) Load Data (DML) Lecture 8 The Process of Database Design 2

cust. No cust. Name cust. St cust. City age 1 C 1 Olaya St Jeddah 20 2 C 2 Mains St Riyadh 30 3 C 3 Mains Rd Riyadh 25 4 C 4 Mains Rd Dammam 5 C 5 Mains Rd Riyadh Lecture 8 Sample Data in Customer Table 3

prod. No prod. Name prod. Des price 100 P 0 Food 100 101 P 1 healthy food 100 102 P 2 103 P 3 self_raising flour, 80%wheat 300 104 P 4 network 80 x 300 Lecture 8 Sample Data in Product Table 200 4

ord. No ord. Date cust. No prod. No quantity 1 01 -jan-2003 1 100 2 2 02 -jan-2003 1 101 1 3 01 -jan-2003 2 102 1 4 01 -jan-2003 3 100 2 5 03 -jan-2003 1 101 1 6 06 -mar-2003 2 100 10 Lecture 8 Sample Data in Orders Table 5

Lecture 8 EMPLOYEE DEPARTMENT 6

Table orders ( Example 3) Order. Date Order. Price Customer 1 2008/11/12 1000 Nora 2 2008/10/23 1600 Sara 3 2008/09/02 700 Nora 4 2008/09/03 300 Nora 5 2008/08/30 2000 Yara 6 2008/10/04 100 Sara Lecture 8 O_Id 7

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

Types of JOIN ( 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. Lecture 8 • Different SQL JOINs • types of JOIN: . • JOIN: Return rows when there is at least one match in both tables NOTE: In all the above outer joins, the displayed unmatched columns are filled with NULLS. 9

Lecture 8 Types of JOIN 10

SQL Examples of Joins ( 1) • Simple Join Lecture 8 SELECT E. first. Name, E. last. Name, D. dept. Name FROM EMPLOYEE E, DEPARTMENT D WHERE E. dept. Number = D. dept. Number; 11

Lecture 8 This is the result from the matching This is the final result: E. Firstname E. lastname D. dept. Name Mandy Smith Computer Science Daniel Hodges Information Science Shaskia Ramanthan Information Science Graham Burke Computer Science Annie Nguyen Computer Science 12

SQL Examples of Joins ( 2 ) • Joining more than two tables Lecture 8 SELECT E. first. Name, E. last. Name, P. proj. Title FROM EMPLOYEE E, WORKS_ON W, PROJECT P WHERE E. employee. No = W. employee. No AND W. proj. No = P. proj. No; EMPLOYEE WORKS_ON PROJECT E. Firstname E. lastname P. projtitle Mandy Smith Project A Daniel Hodges Project A Shaskia Ramanthan Project B Graham Burke Project C Annie Nguyen Project A 13

SQL Examples of Joins ( 3 ) ordered the product 100. SELECT c. cust. No, cust. Name, cust. St, cust. City FROM customer c, orders o WHERE c. cust. No=o. cust. No AND prod. No=100; cust. N o cust. Na me cust. St cust. City age 1 C 1 Olaya St Jeddah 20 2 C 2 Mains St Riyadh 30 3 C 3 Mains Rd Riyadh 25 4 C 4 Mains Rd Dammam 5 C 5 Mains Rd Riyadh ord. No ord. Date cust. No prod. No quantity 1 01 -jan-2003 1 100 2 2 02 -jan-2003 1 101 1 3 01 -jan-2003 2 102 1 4 01 -jan-2003 3 100 2 5 03 -jan-2003 1 101 1 6 06 -mar-2003 2 100 10 Lecture 8 • List customers (by customer number, name and address) who have 14

SQL Examples of Joins ( 4 ) • Find the total price of the products ordered by customer 1. sum(price*quantity) prod. No prod. N prod. Des ame price 100 P 0 Food 101 P 1 healthy food ord. No ord. Date cust. No prod. No quantity 100 1 01 -jan-2003 1 100 2 02 -jan-2003 1 101 1 3 01 -jan-2003 2 102 1 4 01 -jan-2003 3 100 2 5 03 -jan-2003 1 101 1 6 06 -mar-2003 2 100 10 102 P 2 200 103 P 3 self_raising 300 flour, 80%whea t 104 P 4 network 80 x 400 Lecture 8 SELECT sum(price*quantity) FROM orders, product WHERE orders. prod. No = product. prod. No AND cust. No = 1; 300 15

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. Lecture 8 • SELECT c. cust. No, o. prod. No, quantity FROM customer c, orders o WHERE c. cust. No = o. cust. No (+); • 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. cust. No in place of c. cust. No in the SELECT list. 16

SELECT Student_Name, Advisor_Name FROM Students, Advisors WHERE Students. Advisor_ID= Advisors. Advisor_ID; Lecture 8 1) Inner Join SQL Example 17

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

SELECT Student_Name, Advisor_Name FROM Students, Advisors WHERE Students. Advisor_ID(+)= Advisors. Advisor_ID ; Student_Name Advisor_Name Student_1 advisor 1 Student_5 advisor 3 Student_7 advisor 3 Student_9 advisor 1 Student_10 advisor 3 null Advisor 5 Lecture 8 3) Right Outer Join SQL Example 19

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

21 Lecture 8

Nested Queries (1) SELECT * FROM (SELECT prod. No, sum(quantity) AS sum FROM orders GROUP BY prod. No); WHERE sum>10; Equivalent to SELECT prod. No, sum(quantity) as sum FROM orders GROUP BY prod. No HAVING sum(quantity)>10; prod. No sum 100 14 101 2 102 1 prod. No sum 100 14 • The inner query is referred to as a subquery Lecture 8 • Query results are tables, which can also be queried. 22

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

Subquery SELECT first. Name, last. Name FROM EMPLOYEE WHERE dept. Number =(SELECT dept. Number FROM DEPARTMENT WHERE mail. Number = 39); dept. Number D 1 first. Name last. Name Mandy Smith Graham Burke Annie Nguyene Lecture 8 • Subquery with equality: 24

Subquery • Subquery with aggregate function: FROM EMPLOYEE WHERE salary > (SELECT avg(salary) FROM EMPLOYEE); avg(salary) first. Name last. Name salary 51400 Shaskia Raman than 58000 Annie Nguyene 60000 Lecture 8 SELECT first. Name, last. Name, salary 25

Subquery Lecture 8 • Nested Subquery (use of IN): SELECT first. Name, last. Name dept. Number FROM EMPLOYEE D 1 WHERE dept. Number IN (SELECT dept. Number D 3 FROM DEPARTMENT first. Name last. Name Mandy Smith WHERE location = ‘Bundoora’); Graham Burke Annie Nguyene 26

Subquery • List the products ordered by customers living in Riyadh. - This query is equivalent to SELECT prod. No FROM orders o, customer c WHERE o. cust. No =c. cust. No AND cust. City = ‘Riyadh'; 100 102 cust. No Lecture 8 SELECT prod. No FROM orders WHERE cust. No IN (SELECT cust. No FROM customer WHERE cust. City =‘Riyadh'); prod. No 2 3 5 27

28 Lecture 8

Queries using EXISTS or NOT 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. Lecture 8 Queries using EXISTS • Syntax SELECT column_name FROM table_name WHERE EXISTS|NOT EXISTS ( subquery ); 29

Queries using EXISTS or NOT EXISTS first. Name last. Name Daniel Hodges SELECT first. Name, last. Name Shaskia Ramanthan FROM EMPLOYEE E WHERE EXISTS (SELECT * FROM DEPARTMENT D WHERE E. dept. Number = D. dept. Number AND D. location = ‘Bendigo’); Lecture 8 • Example 30

Example. EXISTS cust. No cust. Name cust. St cust. City age 1 C 1 Olaya St Jeddah 20 2 C 2 Mains St Riyadh 30 3 C 3 Mains Rd Riyadh 25 4 C 4 Mains Rd Dammam 5 C 5 Mains Rd Riyadh Lecture 8 • Find all customers who have ordered some products. SELECT * FROM customer c WHERE exists (SELECT * FROM orders o WHERE o. cust. No =c. cust. No); ord. No ord. Date cust. No prod. No quantity 1 01 -jan-2003 1 100 2 2 02 -jan-2003 1 101 1 3 01 -jan-2003 2 102 1 4 01 -jan-2003 3 100 2 5 03 -jan-2003 1 101 1 6 06 -mar-2003 2 100 10 • If the subquery is not empty, then the exists condition is true. 31

Example. NOT EXISTS 1 C 1 Olaya St Jeddah 20 2 C 2 Mains St Riyadh 30 Riyadh 25 SELECT * 3 C 3 Mains Rd 4 C 4 Mains Rd FROM customer c 5 C 5 Mains Rd WHERE NOT EXISTS (SELECT * FROM orders o WHERE o. cust. No = c. cust. No ord. Date cust. No prod. No quantity AND quantity <2); 1 01 -jan-2003 1 100 2 2 02 -jan-2003 1 101 1 3 01 -jan-2003 2 102 1 age 4 01 -jan-2003 3 100 2 cust. No 5 03 -jan-2003 1 101 1 3 6 06 -mar-2003 2 100 10 Dammam Riyadh cust. Name cust. St cust. City age C 3 Mains Rd Riyadh 25 Lecture 8 • Find all customers such that no order made by them has a quantity less than 2. cust. No cust. Name cust. St cust. City 32

33 Lecture 8

• 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 1. have the same number of columns. 2. The columns must also have similar data types. 3. 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 SELECT column_name(s) FROM table_name 1 UNION SELECT column_name(s) FROM table_name 2 Lecture 8 UNION 34

UNION • SQL UNION ALL Syntax Lecture 8 • 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. SELECT column_name(s) FROM table_name 1 UNION ALL SELECT column_name(s) FROM table_name 2 35

"Employees_Norway" “Employees_USA” E_ID E_Name 01 Hansen, Ola 01 Turner, Sally 02 Svendson, Tove 02 Kent, Clark 03 Svendson, Stephen 04 Pettersen, Kari 04 Scott, Stephen • list all the different employees in Norway and USA SELECT E_Name FROM Employees_Norway UNION SELECT E_Name FROM Employees_USA; E_Name Hansen, Ola Svendson, Tove Svendson, Stephen Pettersen, Kari Turner, Sally Kent, Clark Scott, Stephen Lecture 8 UNION Example 1 36

SELECT cust. No FROM customer cust. No 2 WHERE cust. City=‘Riyadh' 3 UNION 5 SELECT cust. No FROM orders WHERE prod. No=102; // union of the two queries cust. No cust. Name cust. St cust. City age 1 C 1 Olaya St Jeddah 2 C 2 Mains St 3 C 3 Mains Rd ord. No ord. Date cust. No prod. No quantity 20 1 01 -jan-2003 1 100 2 Riyadh 30 2 02 -jan-2003 1 101 1 Riyadh 25 3 01 -jan-2003 2 102 1 4 01 -jan-2003 3 100 2 5 03 -jan-2003 1 101 1 6 06 -mar-2003 2 100 10 4 C 4 Mains Rd Dammam 5 C 5 Mains Rd Riyadh Lecture 8 UNION Example 2 37

• 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. Lecture 8 MINUS • SQL MINUS Syntax SELECT column_name(s) FROM table_name 1 MINUS SELECT column_name(s) FROM table_name 2 38

MINUS Example 1 Prod. No Lecture 8 103 SELECT prod. No FROM product 104 MINUS SELECT prod. No FROM orders; //difference from the two queries prod. No prod. Name prod. Des price 100 P 0 Food 100 101 P 1 healthy food 100 102 P 2 103 P 3 self_raising flour, 80%wheat 300 104 P 4 network 80 x 300 200 ord. No ord. Date cust. No prod. No quantity 1 01 -jan-2003 1 100 2 2 02 -jan-2003 1 101 1 3 01 -jan-2003 2 102 1 4 01 -jan-2003 3 100 2 5 03 -jan-2003 1 101 1 6 06 -mar-2003 2 100 10 39

INTERSECT • SQL INTERSECT Syntax Lecture 8 • the INTERSECT operator returns only those rows returned by both queries. • Returns only those rows that are returned by each of two SELECT statements. SELECT column_name(s) FROM table_name 1 INTERSECT SELECT column_name(s) FROM table_name 2 40

SELECT cust. No FROM customer Cust. No WHERE cust. City=‘Riyadh' 2 INTERSECT SELECT cust. No FROM orders WHERE prod. No=102; // intersect of the two queries cust. N o cust. Name cust. St cust. City age 1 C 1 Olaya St Jeddah 20 2 C 2 Mains St Riyadh 30 3 C 3 Mains Rd Riyadh 25 4 C 4 Mains Rd Dammam 5 C 5 Mains Rd Riyadh ord. No ord. Date cust. No prod. No quantit y 1 01 -jan-2003 1 100 2 2 02 -jan-2003 1 101 1 3 01 -jan-2003 2 102 1 4 01 -jan-2003 3 100 2 5 03 -jan-2003 1 101 1 6 06 -mar-2003 2 100 10 Lecture 8 INTERSECT 41

Examples Lecture 8 EMPLOYEE DEPENDENT 42

Examples employee. No, first. Name, last. Name EMPLOYEE employee. No, first. Name, last. Name DEPENDENT; Lecture 8 SELECT FROM UNION SELECT FROM SELECT employee. No FROM EMPLOYEE INTERSECT SELECT employee. No FROM DEPENDENT 43

44 Lecture 8

EMPLOYEE_ID LAST_NAME FIRST_NAME JOB_ID MANAGER_ID SALARY COMM DEPARTMENT_ID 7369 7499 7505 7506 7507 7521 SMITH ALLEN DOYLE DENNIS BAKER WARK JOHN KEVIN JEAN LYNN LESLIE CYNTHIA 667 670 671 671 670 7902 7698 7839 7698 800 1600 2850 2750 2200 1250 NULL 300 NULL 500 20 30 30 30 40 30 Lecture 8 EMPLOYEE Table Example 1 SELECT department_id, count(*), max(salary), min(salary) FROM employee GROUP BY department_id; 45

EMPLOYEE_ID LAST_NAME FIRST_NAME JOB_ID MANAGER_ID SALARY COMM DEPARTMENT_ID 7369 7499 7505 7506 7507 7521 SMITH ALLEN DOYLE DENNIS BAKER WARK JOHN KEVIN JEAN LYNN LESLIE CYNTHIA 667 670 671 671 670 7902 7698 7839 7698 800 1600 2850 2750 2200 1250 NULL 300 NULL 500 20 30 30 30 40 30 Lecture 8 EMPLOYEE Table Example 2 SELECT Employee_ID, FIRST_NAME, DEPARTMENT_ID FROM employee WHERE salary=(SELECT max(salary) FROM employee); 46

EMPLOYEE_ID LAST_NAME FIRST_NAME JOB_ID MANAGER_ID SALARY COMM DEPARTMENT_ID 7369 7499 7505 7506 7507 7521 SMITH ALLEN DOYLE DENNIS BAKER WARK JOHN KEVIN JEAN LYNN LESLIE CYNTHIA 667 670 671 671 670 7902 7698 7839 7698 800 1600 2850 2750 2200 1250 NULL 300 NULL 500 20 30 30 30 40 30 DEPARTMENT Department_ID Name Location_ID 10 ACCOUNTING 122 20 RESEARCH 124 30 SALES 123 OPERATIONS 167 40 SELECT Employee_ID FROM employee WHERE department_id IN (SELECT department_id FROM department WHERE name=’SALES’); Lecture 8 EMPLOYEE Table Example 3 47

EMPLOYEE_ID LAST_NAME FIRST_NAME JOB_ID MANAGER_ID SALARY COMM DEPARTMENT_ID 7369 7499 7505 7506 7507 7521 SMITH ALLEN DOYLE DENNIS BAKER WARK JOHN KEVIN JEAN LYNN LESLIE CYNTHIA 667 670 671 671 670 7902 7698 7839 7698 800 1600 2850 2750 2200 1250 NULL 300 NULL 500 20 30 30 30 40 30 DEPARTMENT Department_ID Name Location_ID 10 ACCOUNTING 122 20 RESEARCH 124 SALES 123 OPERATIONS 167 SELECT name 30 40 FROM department d WHERE NOT EXISTS (SELECT last_name FROM employee e WHERE d. department_id=e. department_id); Lecture 8 EMPLOYEE Table Example 4 48

EMPLOYEE_ID LAST_NAME FIRST_NAME JOB_ID MANAGER_ID SALARY COMM DEPARTMENT_ID 7369 7499 7505 7506 7507 7521 SMITH ALLEN DOYLE DENNIS BAKER WARK JOHN KEVIN JEAN LYNN LESLIE CYNTHIA 667 670 671 671 670 7902 7698 7839 7698 800 1600 2850 2750 2200 1250 NULL 300 NULL 500 20 30 30 30 40 30 DEPARTMENT Department_ID Name Location_ID 10 ACCOUNTING 122 20 RESEARCH 124 30 SALES 123 40 OPERATIONS 167 Lecture 8 EMPLOYEE Table Example 5 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 49 department_id FROM department WHERE name IN (‘RESEARCH’ , ’OPERATIONS’));

EMPLOYEE_ID LAST_NAME FIRST_NAME JOB_ID MANAGER_ID SALARY COMM DEPARTMENT_ID 7369 7499 7505 7506 7507 7521 SMITH ALLEN DOYLE DENNIS BAKER WARK JOHN KEVIN JEAN LYNN LESLIE CYNTHIA 667 670 671 671 670 7902 7698 7839 7698 800 1600 2850 2750 2200 1250 NULL 300 NULL 500 20 30 30 30 40 30 Lecture 8 EMPLOYEE Table Example 6 SELECT employee_id, First_name, Last_name, Salary FROM employee WHERE last_name like ‘D%’; 50

51 Lecture 8

References Lecture 8 • “Database Systems: A Practical Approach to Design, Implementation and Management. ” Thomas Connolly, Carolyn Begg. 5 th Edition, Addison-Wesley, 2009. 52