lecture8:data manipulation in sql advanced sql queries
DESCRIPTION
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. - PowerPoint PPT PresentationTRANSCRIPT
Lec
ture
8Lecture8:Data Manipulation in SQLAdvanced SQL queries
Prepared by L. Nouf Almujally
Ref. Chapter5
1
Lec
ture
8
The Process of Database Design
Real World Domain
Conceptual model (ERD)
Relational Data Model
Create schema
(DDL)
Load Data(DML)
2
Lec
ture
8
Sample Data in Customer Table
custNo custName custSt custCity age
1 C1 Olaya St Jeddah 20
2 C2 Mains St Riyadh 30
3 C3 Mains Rd Riyadh 25
4 C4 Mains Rd Dammam
5 C5 Mains Rd Riyadh
3
Lec
ture
8
Sample Data in Product Table
prodNo prodNam
eprodDes price
100 P0 Food 100
101 P1 healthy food 100
102 P2 200
103 P3 self_raising flour,80%wheat
300
104 P4 network 80x 3004
Lec
ture
8
Sample Data in Orders Table
ordNo ordDate custNo prodNo 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
5
Lec
ture
8
Dept Number Dept Name Location Mail NumberD1 Computer Science Bundoora 39D2 Information Science Bendigo 30D3 Physics Bundoora 37D4 Chemistry Bendigo 35
DEPARTMENT
EMPLOYEE
Employee No. First Name Last Name Dept Number SalaryE1 Mandy Smith D1 50000E2 Daniel Hodges D2 45000E3 Shaskia Ramanthan D2 58000E4 Graham Burke D1 44000E5 Annie Nguyen D1 60000
6
Lec
ture
8
O_Id OrderDate OrderPrice 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
Table orders ( Example 3)
7
Lec
ture
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.
8
Lec
ture
8
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.
9
Lec
ture
8
SQL Examples of Joins ( 1)
• Simple JoinSELECT E.firstName, E.lastName, D.deptName FROM EMPLOYEE E, DEPARTMENT D
WHERE E.deptNumber = D.deptNumber;
Employee No. First Name Last Name Dept Number SalaryE1 Mandy Smith D1 50000E2 Daniel Hodges D2 45000E3 Shaskia Ramanthan D2 58000E4 Graham Burke D1 44000E5 Annie Nguyen D1 60000
Dept Number Dept Name Location Mail NumberD1 Computer Science Bundoora 39D2 Information Science Bendigo 30D3 Physics Bundoora 37D4 Chemistry Bendigo 35
11
Lec
ture
8
E.Firstname E.lastname D.deptNameMandy Smith Computer ScienceDaniel Hodges Information ScienceShaskia Ramanthan Information ScienceGraham Burke Computer ScienceAnnie Nguyen Computer Science
Employee No. First Name Last Name Dept Number Dept Name Location Mail Number SalaryE1 Mandy Smith D1 Computer Science Bundoora 39 50000E2 Daniel Hodges D2 Information Science Bendigo 30 45000E3 Shaskia Ramanthan D2 Physics Bundoora 37 58000E4 Graham Burke D1 Computer Science Bundoora 39 44000E5 Annie Nguyen D1 Computer Science Bundoora 39 60000
This is the result from the matching
This is the final result:
12
Lec
ture
8
SQL Examples of Joins ( 2 )• Joining more than two tablesSELECT 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 No. First Name Last Name Dept Number SalaryE1 Mandy Smith D1 50000E4 Graham Burke D1 44000E5 Annie Nguyen D1 60000E2 Daniel Hodges D2 45000E3 Shaskia Ramanthan D2 58000
Employee No. ProjNoE1 1E4 1E5 2E2 3E3 1
ProjNo Project Title1 Project A2 Project B3 Project C
EMPLOYEE
WORKS_ON
PROJECT
E.Firstname E.lastname P.projtitle
Mandy Smith Project A
Graham Burke Project A
Annie Nguyen Project B
Daniel Hodges Project C
Shaskia Ramanthan Project A
13
Lec
ture
8
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;
custNo
custName
custSt custCity age
1 C1 Olaya St Jeddah 20
2 C2 Mains St Riyadh 30
3 C3 Mains Rd Riyadh 25
4 C4 Mains Rd Dammam
5 C5 Mains Rd Riyadh
ordNo ordDate custNo prodNo 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
14
Lec
ture
8
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;
prodNo prodName
prodDes price
100 P0 Food 100
101 P1 healthy food 100
102 P2 200
103 P3 self_raising flour,80%wheat
300
104 P4 network 80x 300
ordNo ordDate custNo prodNo 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
sum(price*quantity)
400
15
Lec
ture
8
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.
16
Lec
ture
8
1 )Inner Join SQL Example
SELECT Student_Name, Advisor_NameFROM Students, Advisors
WHERE Students.Advisor_ID= Advisors.Advisor_ID;
17
Lec
ture
8
2 )Left Outer Join SQL Example
SELECT Student_Name, Advisor_NameFROM Students, Advisors
WHERE Students.Advisor_ID= Advisors.Advisor_ID (+);
18
Lec
ture
8
3 )Right Outer Join SQL Example
SELECT Student_Name, Advisor_NameFROM Students, Advisors
WHERE Students.Advisor_ID(+)= Advisors.Advisor_ID ;
Student_Name Advisor_NameStudent_1 advisor 1Student_5 advisor 3Student_7 advisor 3Student_9 advisor 1
Student_10 advisor 3null Advisor 5
19
Lec
ture
8
4 )Full Outer Join SQL Example
SELECT Student_Name, Advisor_NameFROM Students , Advisors
WHERE Students.Advisor_ID (+) = Advisors.Advisor_ID (+) ;
20
Lec
ture
8
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
prodNo sum100 14101 2102 1
prodNo sum100 14
22
Lec
ture
8
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);
AVG(price)
200
prodNo price103 300104 300
23
Lec
ture
8
Subquery
• Subquery with equality:
SELECT firstName, lastNameFROM EMPLOYEEWHERE deptNumber =(SELECT deptNumber
FROM DEPARTMENT WHERE mailNumber = 39);
deptNumberD1
firstName lastNameMandy SmithGraham BurkeAnnie Nguyene 24
Lec
ture
8
Subquery
• Subquery with aggregate function:
SELECT firstName, lastName, salary FROM EMPLOYEE WHERE salary > (SELECT avg(salary) FROM EMPLOYEE);
avg(salary)
51400
firstName lastName salary
Shaskia Raman than 58000Annie Nguyene 60000
25
Lec
ture
8
Subquery
• Nested Subquery (use of IN):
SELECT firstName, lastNameFROM EMPLOYEEWHERE deptNumber IN (SELECT deptNumber
FROM DEPARTMENT WHERE location = ‘Bundoora’);
deptNumber
D1
D3firstName lastName
Mandy Smith
Graham Burke
Annie Nguyene
Employee No. First Name Last Name Dept Number SalaryE1 Mandy Smith D1 50000E2 Daniel Hodges D2 45000E3 Shaskia Ramanthan D2 58000E4 Graham Burke D1 44000E5 Annie Nguyen D1 60000
Dept Number Dept Name Location Mail NumberD1 Computer Science Bundoora 39D2 Information Science Bendigo 30D3 Physics Bundoora 37D4 Chemistry Bendigo 35
26
Lec
ture
8
Subquery
• List the products ordered by customers living in Riyadh.
SELECT prodNo FROM orders WHERE custNo IN (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';
custNo
2
3
5
prodNo
100
102
27
Lec
ture
8
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
SELECT column_name FROM table_name WHERE EXISTS|NOT EXISTS ( subquery );
29
Lec
ture
8
Queries using EXISTS or NOT EXISTS
• ExampleSELECT firstName, lastName
FROM EMPLOYEE E WHERE EXISTS (SELECT * FROM DEPARTMENT D
WHERE E.deptNumber = D.deptNumber AND D.location = ‘Bendigo’);
Employee No. First Name Last Name Dept Number SalaryE1 Mandy Smith D1 50000E2 Daniel Hodges D2 45000E3 Shaskia Ramanthan D2 58000E4 Graham Burke D1 44000E5 Annie Nguyen D1 60000
Dept Number Dept Name Location Mail NumberD1 Computer Science Bundoora 39D2 Information Science Bendigo 30D3 Physics Bundoora 37D4 Chemistry Bendigo 35
firstName lastNameDaniel HodgesShaskia Ramanthan
30
Lec
ture
8
Example . EXISTS
• Find all customers who have ordered some products.SELECT * FROM customer cWHERE exists (SELECT *
FROM orders o WHERE o.custNo =c.custNo);
• If the subquery is not empty, then the exists condition is true.
custNo custName custSt custCity age
1 C1 Olaya St Jeddah 20
2 C2 Mains St Riyadh 30
3 C3 Mains Rd Riyadh 25
4 C4 Mains Rd Dammam
5 C5 Mains Rd Riyadh
ordNo ordDate custNo prodNo 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 1031
Lec
ture
8
Example . NOT EXISTS
• Find all customers such that no order made by them has a quantity less than 2.
SELECT * FROM customer cWHERE NOT EXISTS (SELECT *
FROM orders o WHERE o.custNo = c.custNo
AND quantity <2);
custNo custName custSt custCity age
1 C1 Olaya St Jeddah 20
2 C2 Mains St Riyadh 30
3 C3 Mains Rd Riyadh 25
4 C4 Mains Rd Dammam
5 C5 Mains Rd Riyadh
ordNo ordDate custNo prodNo 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
custNo custName custSt custCity age
3 C3 Mains Rd Riyadh 25
4 C4 Mains Rd Dammam
5 C5 Mains Rd Riyadh
32
Lec
ture
8
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 must1. 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_name1UNIONSELECT column_name(s) FROM table_name2
34
Lec
ture
8
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
SELECT column_name(s) FROM table_name1UNION ALLSELECT column_name(s) FROM table_name2
35
Lec
ture
8
UNION Example 1
• list all the different employees in Norway and USASELECT E_Name FROM Employees_Norway
UNIONSELECT E_Name FROM Employees_USA; E_Name
Hansen, OlaSvendson, Tove
Svendson, StephenPettersen, Kari
Turner, SallyKent, Clark
Scott, Stephen
E_ID E_Name
01 Hansen, Ola02 Svendson, Tove03 Svendson, Stephen04 Pettersen, Kari
E_ID E_Name01 Turner, Sally02 Kent, Clark03 Svendson, Stephen04 Scott, Stephen
"Employees_Norway" “Employees_USA”
36
Lec
ture
8
UNION Example 2
SELECT custNo FROM customer WHERE custCity=‘Riyadh' UNIONSELECT custNo FROM orders WHERE prodNo=102; // union of the two queries
custNo custName custSt custCity age
1 C1 Olaya St Jeddah 20
2 C2 Mains St Riyadh 30
3 C3 Mains Rd Riyadh 25
4 C4 Mains Rd Dammam
5 C5 Mains Rd Riyadh
ordNo ordDate custNo prodNo 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
custNo
2
3
5
37
Lec
ture
8
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
SELECT column_name(s) FROM table_name1MINUSSELECT column_name(s) FROM table_name2
38
Lec
ture
8
MINUS Example 1
SELECT prodNo FROM productMINUSSELECT prodNo FROM orders; //difference from the two queries
ordNo ordDate custNo prodNo 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
prodNo prodName prodDes price
100 P0 Food 100
101 P1 healthy food 100
102 P2 200
103 P3 self_raising flour,80%wheat
300
104 P4 network 80x 300
ProdNo
103
104
39
Lec
ture
8
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
SELECT column_name(s) FROM table_name1INTERSECTSELECT column_name(s) FROM table_name2
40
Lec
ture
8
INTERSECT
SELECT custNo FROM customer WHERE custCity=‘Riyadh' INTERSECTSELECT custNo FROM orders WHERE prodNo=102; // intersect of the two queries
custNo
custName custSt custCity age
1 C1 Olaya St Jeddah 20
2 C2 Mains St Riyadh 30
3 C3 Mains Rd Riyadh 25
4 C4 Mains Rd Dammam
5 C5 Mains Rd Riyadh
ordNo ordDate custNo prodNo 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
CustNo
2
41
Lec
ture
8
DEPENDENT
EMPLOYEE
Employee No. First Name Last Name Date of BirthE1 Joshua Smith 12-Jun-1998E3 Jay Ramanthan 04-Jan-1996E1 Jemima Smith 08-Sep-2000
Employee No. First Name Last Name Dept Number SalaryE1 Mandy Smith D1 50000E2 Daniel Hodges D2 45000E3 Shaskia Ramanthan D2 58000
Examples
42
Lec
ture
8
Examples
SELECT employeeNo, firstName, lastNameFROM EMPLOYEEUNIONSELECT employeeNo, firstName, lastNameFROM DEPENDENT;
SELECT employeeNoFROM EMPLOYEEINTERSECTSELECT employeeNoFROM DEPENDENT
43
Lec
ture
8
EMPLOYEE Table Example1
SELECT department_id, count(*), max(salary), min(salary)FROM employee GROUP BY department_id;
EMPLOYEE_ID LAST_NAME FIRST_NAME JOB_ID MANAGER_ID SALARY COMM DEPARTMENT_ID
7369 SMITH JOHN 667 7902 800 NULL 207499 ALLEN KEVIN 670 7698 1600 300 307505 DOYLE JEAN 671 7839 2850 NULL 307506 DENNIS LYNN 671 7839 2750 NULL 307507 BAKER LESLIE 671 7839 2200 NULL 407521 WARK CYNTHIA 670 7698 1250 500 30
45
Lec
ture
8
EMPLOYEE Table Example2
SELECT Employee_ID, FIRST_NAME,DEPARTMENT_ID FROM employeeWHERE salary=(SELECT max(salary) FROM employee);
EMPLOYEE_ID LAST_NAME FIRST_NAME JOB_ID MANAGER_ID SALARY COMM DEPARTMENT_ID
7369 SMITH JOHN 667 7902 800 NULL 207499 ALLEN KEVIN 670 7698 1600 300 307505 DOYLE JEAN 671 7839 2850 NULL 307506 DENNIS LYNN 671 7839 2750 NULL 307507 BAKER LESLIE 671 7839 2200 NULL 407521 WARK CYNTHIA 670 7698 1250 500 30
46
Lec
ture
8
EMPLOYEE Table Example3
SELECT Employee_IDFROM employeeWHERE department_id IN (SELECT department_idFROM department WHERE name=’SALES’);
DEPARTMENT
Department_ID Name Location_ID
10 ACCOUNTING 12220 RESEARCH 12430 SALES 12340 OPERATIONS 167
EMPLOYEE_ID LAST_NAME FIRST_NAME JOB_ID MANAGER_ID SALARY COMM DEPARTMENT_ID
7369 SMITH JOHN 667 7902 800 NULL 207499 ALLEN KEVIN 670 7698 1600 300 307505 DOYLE JEAN 671 7839 2850 NULL 307506 DENNIS LYNN 671 7839 2750 NULL 307507 BAKER LESLIE 671 7839 2200 NULL 407521 WARK CYNTHIA 670 7698 1250 500 30
47
Lec
ture
8
EMPLOYEE Table Example4
SELECT nameFROM department dWHERE NOT EXISTS (SELECT last_nameFROM employee eWHERE d.department_id=e.department_id);
DEPARTMENT
Department_ID Name Location_ID
10 ACCOUNTING 12220 RESEARCH 12430 SALES 12340 OPERATIONS 167
EMPLOYEE_ID LAST_NAME FIRST_NAME JOB_ID MANAGER_ID SALARY COMM DEPARTMENT_ID
7369 SMITH JOHN 667 7902 800 NULL 207499 ALLEN KEVIN 670 7698 1600 300 307505 DOYLE JEAN 671 7839 2850 NULL 307506 DENNIS LYNN 671 7839 2750 NULL 307507 BAKER LESLIE 671 7839 2200 NULL 407521 WARK CYNTHIA 670 7698 1250 500 30
48
Lec
ture
8
EMPLOYEE Table Example5
SELECT last_name, d.department_id, d.nameFROM employee e, department dWHERE e.department_id (+)= d.department_id AND d.department_id in (SELECT department_id FROM department WHERE name IN (‘RESEARCH’ , ’OPERATIONS’));
DEPARTMENT
Department_ID Name Location_ID
10 ACCOUNTING 12220 RESEARCH 12430 SALES 12340 OPERATIONS 167
EMPLOYEE_ID LAST_NAME FIRST_NAME JOB_ID MANAGER_ID SALARY COMM DEPARTMENT_ID
7369 SMITH JOHN 667 7902 800 NULL 207499 ALLEN KEVIN 670 7698 1600 300 307505 DOYLE JEAN 671 7839 2850 NULL 307506 DENNIS LYNN 671 7839 2750 NULL 307507 BAKER LESLIE 671 7839 2200 NULL 407521 WARK CYNTHIA 670 7698 1250 500 30
49
Lec
ture
8
EMPLOYEE Table Example6
SELECT employee_id, First_name, Last_name, SalaryFROM employeeWHERE last_name like ‘D%’;
EMPLOYEE_ID LAST_NAME FIRST_NAME JOB_ID MANAGER_ID SALARY COMM DEPARTMENT_ID
7369 SMITH JOHN 667 7902 800 NULL 207499 ALLEN KEVIN 670 7698 1600 300 307505 DOYLE JEAN 671 7839 2850 NULL 307506 DENNIS LYNN 671 7839 2750 NULL 307507 BAKER LESLIE 671 7839 2200 NULL 407521 WARK CYNTHIA 670 7698 1250 500 30
50