SQL Data Manipulation II

1. SQL Data Manipulation II Chapter 5 CIS 458 Sungchul Hong

2. Logical Operator • AND, OR, NOT

3. Logical Operator • DeMorgan’s Law • NOT (A AND B)  • NOT (A OR B)  • NOT (A AND B AND C) 

4. Row Selection (WHERE clause) • Comparison • Range • Set membership • Pattern match • Null

5. Comparison Search Condition • SELECT staffNo, fName, lName, position, salary • FROM Staff • WHERE salary > 10000;

6. SQL Comparison Operators • = equals • <> is not equal to • < is less than • > is greater than • <= is less than or equal to • >= is greater than or equal to

7. Order of Evaluation • An expression is evaluated left to right • Subexpressions in brackets are evaluated first. • NOTs are evaluated before ANDs and Ors • ANDs are evaluated before ORs

8. Compound Comparison Search • SELECT * • FROM Branch • WHERE city = ‘London’ OR city = ‘Glasgow’;

9. Range Search Condition • BETWEEN / NOT BETWEEN • SELET staffNo, fName, lName, position, salary • FROM Staff • WHERE salary BETWEEN 20000 AND 30000; • SELET staffNo, fName, lName, position, salary • FROM Staff • WHERE salary >= 20000 AND salary <= 30000;

10. Set Membership Search Condition • IN / NOT IN • SELECT staffNO, fName, lName, positon • FROM Staff • WHERE position IN (‘Manager’, ‘Supervisor’); • SELECT staffNO, fName, lName, positon • FROM Staff • WHERE position =‘Manager’ OR position = ‘Supervisor’;

11. Pattern Match Search • LIKE / NOT LIKE • % (* in MS): wildcard eg) LIKE ‘H*’ • _: single character eg) LIKE ‘H___’ • If the search string can include the pattern-matching character itself, then • LIKE ’15#%’ ESCAPE ‘#’ • SELECT ownerNo, fName, lName, address, telNO • FROM PrivateOwner • WHERE address LIKE ‘*Glasgow*’;

12. NULL Search Condition • IS NULL / IS NOT NULL • SELECT clientNO, viewDate • FROM Viewing • WHERE propertyNO = ‘PG4’ AND comment IS NULL; • *

13. Sorting Results • ORDER BY column identifiers ( ASC or DESC) • SELECT staffNO, fName, lName, salary • FROM Staff • ORDER BY salary DESC;

14. Multiple Column Ordering • SELECT properyNO, type, rooms, rent • FROM PropertyForRent • ORDER BY type, rent DESC;

15. Aggregate Functions • COUNT: numeric/non-numeric • COUNT(*): counts all the rows • DISTINCT: do not count duplicate values • SUM: numeric • AVG: numeric • MIN: numeric/non-numeric • MAX: numeric/non-numeric

16. COUNT • SELECT COUNT(*) AS count • FROM PropertyForRent • WHERE rent > 350; • SELECT COUNT (DISTINCT propertyNO) AS count • FROM Viewing • WHERE viewDate BETWEEN ‘1-May-01’ AND ’31-May-01’;

17. Count & SUM • SELECT COUNT (StaffNo) AS count, SUM(salary) AS sum • FROM Staff • WHERE position = ‘Manager’;

18. MIN, MAX, and AVG • SELECT MIN(salary), MAX(salary), AVG(salary) AS avg • FROM Staff;

19. Grouping • GROUP BY • In SELECT list, only column names, aggregate functions, constants, and expression of previous combination. • All column names in the SELECT list must appear in the GROUP BY clause unless the name is used only in an aggregate function. • Each item in the SELECT list must be single-valued per group.

20. GROUP BY • Find the number of staff working in each branch and the sum of their salaries. • SELECT branchNO, COUNT(staffNo) AS count, SUM(salary) AS sum • FROM Staff • GROUP BY branchNO • ORDER BY branchNO; • SELECT branchNO, (SELECT COUNT (staffNo) AS count • FROM Staff s • WHERE s.branchNo = b.branchNO), • (SELECT SUM(salary) AS sum • FROM Staff s • WHERE s.branchNo = b.branchNo) • FROM Branch b • ORDER BY branchNO;

21. GROUP BY • The result of GROUP BY

22. GROUP BY b s

23. GROUP BY & HAVING • For each branch office with more than one member of staff, find the number of staff working in each branch and the sum of their salaries. SELECT branchNo, COUNT(staffNo) AS count, SUM (salary) AS sum From Staff GROUP BY branchNO HAVING COUNT(staffNO)>1 ORDER BY branchNO;

24. Subqueries • Inner SELECT statement • Subquery or nested query • SELECT, INSERT, UPDATE, and DELETE • A scalar subquery returns a single column and a single row. • A row subquery returns multiple columns, but again only a single row. • A table subquery returns on or more columns and multiple rows.

25. Subquery with Equality • List the staff who work in the branch at ‘163 Main St’ • SELECT staffNO, fName, lName, position • FROM Staff • WHERE branchNO = (SELECT branchNO • FROM Branch • WHERE street = ‘163 Main St’);

26. Subquery with an Aggregate Function • List all staff whose salary is greater than the average salary, and show by how much their salary is greater than the average. • SELECT staffNO, fName, lName, position, salary – (SELECT AVG (salary) FROM Staff) AS salDiff • FROM Staff • WHERE salary > (SELECT AVG(salary) FROM Staff);

27. Subquery Rules • The ORDER BY clause may not be used in a subquery. • The subquery SELECT list must consist of a single column name or expression, except for subqueries that use the keyword EXISTS. • BY default, column names in a subquery refer to the table name in the FROM clause of the subquery. It is possible to refer to a table in a FROM clause of an outer query by qualifying the column name. • When a subquery is one of the two operands involved in a comparison, the subquery must appear on the right-hand side of the comparison.

28. Nested subqueries: Use of IN • List the properties that are handled by staff who work in the branch at ‘163 Main St’. • SELECT propertyNo, street, city, postcode, type, rooms, rent • FROM PropertyForRent • WHERE staffNo IN (SELECT staffNO • FROM Staff • WHERE branchNO = (SELECT branchNO • FROM Branch • WHERE street = ‘163 Main St’));

29. Use of ANY/SOME • Find all staff whose salary is larger than the salary of at least one member of staff at branch B003. • SELECT staffNO, fName, lName, position, salary • FROM Staff • WHERE salary > SOME (SELECT salary • FROM Staff • WHERE branchNo = ‘B003’);

30. Use of ALL • Find all staff whose salary is larger than the salary of every member of staff at branch B003. • SELECT staffNO, fName, lName, position, salary • FROM Staff • WHERE salary > ALL (SELECT salary • FROM Staff • WHERE branchNo =‘B003’);

31. Multi-Table Queries • List the names of all clients who have viewed a property along with any comment supplied. • SELECT c.clientNo, fName, lName, proertyNo, comment • FROM Client c, Viewing v • WHERE c.clientNo = v.clientNo

32. Database Updates • INSERT • UPDATE • DELETE

33. INSERT • INSERT INTO Staff • VALUES (‘SG16’, ‘Alan’, ‘Brown’, ‘Assistant’, ‘M’, DATE ‘1957-05-25’, 8300, ‘B003’)

34. UPDATE • UPDATE Staff • SET salary = salary*1.03

35. DELETE • DELETE FROM Viewing • WHERE propertyNo = ‘PG4’;