Agenda

1. Agenda • TMA01 • M876 Block 3 – Using SQL

2. Structured Query Language - SQL • A non-procedural language to • Create database and relation structures. • Perform basic data management tasks, such as insertion, modification and deletion of data from the relations. • Perform both simple and complex queries to transform the raw data into information. • ISO standard  Portable. • Easy to learn.

3. DDL and DML • Two major types of SQL command • Data Definition Language (DDL) • Commands to define the database including creating, altering and dropping tables, and establishing constraints and creating views. • E.g. CREATE TABLE, ALTER TABLE, CREATE VIEW, CREATE INDEX and so on. • Data Manipulation Language(DML) • Commands to insert, update, delete and retrieve data from the database. • E.g. INSERT, UPDATE, DELETE, SELECT and so on.

4. Terminology

5. Table Name Column Name Row

6. General form of a SELECT statement SELECT [DISTINCT | ALL] {*|column_expression[AS new_name]][,…]} FROM table_name [alias][,…] [WHERE condition] [GROUP BY column list][HAVING condiion] [ORDER BY coluum list] Remarks: SELECT Specifies which columns are to appear in the output FROM Specifies the table or tables to be used. WHERE Filters the rows subject to some conditions. GROUP BY Form groups of rows with the same column value. HAVING Filters the groups subject to some condition. ORDER BY Specifies the order of the output.

8. Retrieve all columns, all rows List full details of all staff. SELECT sno, fname, lname, address, tel_no, position, sex, dob, salary, nin, bno FROM staff;  SELECT * FROM staff;

9. Retrieve specific columns, all rows Produce a list of salaries for all staff, showing only the staff number, the first and last names, and the salary details. SELECT sno, fname, lname, salary FROM staff;

10. Use of DISTINCT List the property numbers of all properties that have been viewed. SELECT pno FROM viewing; SELECT DISTINCT(pno) FROM viewing;

11. Calculated fields Produce a list of monthly salaries for all staff, showing the staff number, the first and last names and the salary details. SELECT sno, fname, lname, salary/12 AS monthly_salary FROM staff;

12. Comparison search condition List all staff with a salary greater than 10,000. SELECT sno, fname, lname, position, salary FROM staff WHERE salary > 10000;

13. Compound comparison search condition List addresses of all branch offices in London or Glasgow. SELECT bno, street, area, city, pcode FROM branch WHERE city = ‘London’ OR city = ‘Glasgow’;

14. Range search condition (BETWEEN/NOT BETWEEN) List all staff with a salary between 20,000 and 30,000. SELECT sno, fname, lname, position, salary FROM staff WHERE salary BETWEEN 20000 AND 30000;

15. Set membership search condition (IN/NOT IN) List all Managers and Deputy Managers. SELECT sno, fname, lname, position FROM staff WHERE position IN (‘Manager’,’Deputy’);

16. Pattern match search condition (LIKE/NOT LIKE) Find all staff with the string ‘Glasgow’ in their address. (Note: % precent character represents any sequence of zero or more characters _ underscore character represents any single character SELECT sno, fname, lname, address, salary FROM staff WHERE address LIKE ‘%Glasgow%’;

17. NULL search condition (IS NULL/IS NOT NULL) List the details of all viewings on property PG4 where a comment has not been supplied. SELECT rno, date FROM viewing WHERE pno = ‘PG4’ AND comment IS NULL;

18. Single column ordering Produce a list of salaries for all staff, arranged in descending order of salary. SELECT sno, fname, lname, salary FROM staff ORDER BY salary DESC;

19. Multiple column ordering Produce an abbreviation list of properties arranged in order of property type and then in descending order of rent. SELECT pno, type, rooms, rent FROM property_for_rent ORDER BY type, rent DESC;

20. Use of COUNT(*) How many properties cost more than 350 per month to rent? SELECT COUNT(*) AS count FROM property_for_rent WHERE rent > 350;

21. Use of COUNT(DISTINCT) How many different properties were viewed in May 1998? SELECT COUNT(DISTINCT pno) AS count FROM viewing WHERE date BETWEEN ‘1-May-98’ AND ‘31-May-98’;

22. Use of COUNT and SUM Find the total number of Managers and the sum of their salaries. SELECT COUNT(sno) AS count, SUM(salary) AS sum FROM staff WHERE position = ‘Manager’;

23. Use of MIN, MAX, AVG Find the minimum, maximum, and average staff salary. SELECT MIN(salary) AS min, MAX(salary) as max, AVG(salary) AS avg FROM staff;

24. Use of GROUP BY Find the number of staff working in each branch and the sum of their salaries. SELECT bno, COUNT(sno) AS count, SUM(salary) AS sum FROM staff GROUP BY bno ORDER BY bno;

25. Mutiple grouping columns Find the number of properties handled by each staff members. SELECT s.bno, s.sno, COUNT(*) AS count FROM staff s, property_for_rent p WHERE s.sno = p.sno GROUP BY s.bno, s.sno ORDER BY s.bno, s.sno;

26. Use of HAVING Find 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 bno, COUNT(sno) AS count, SUM(salary) AS sum FROM staff GROUP BY bno HAVING COUNT(sno) > 1 ORDER BY bno;

27. Using a subquery with equality List the staff who work in the branch at ‘163 Main St’. SELECT sno, fname, lname, position FROM staff WHERE bno = (SELECT bno FROM branch WHERE street = ‘163 Main St’);

28. Using a subquery with an aggregate function List all staff whose salary is greater than the average salary, and list by how much their salaries is greater than the average. SELECT sno, fname, lname, position, salary - (SELECT AVG(salary) FROM staff) AS sal_diff FROM staff WHERE salary > (SELECT AVG(salary) FROM staff);

29. Nested subqueries Use of IN List the properties that are handled by staff who work in the branch at ‘163 Main St’. SELECT pno, street, area, city, pcode, type, rooms, rent FROM property_for_rent WHERE sno IN (SELECT sno FROM staff WHERE bno = (SELECT bno FROM branch WHERE street = ‘163 Main St’));

30. Simple Join List the names of all renters who have viewed a property along with any comment supplied. SELECT r.rno, fname, lname, pno, comment FROM renter r, viewing v WHERE r.rno = v.rno;

31. Sorting a join For each branch office, list the names of staff who manage properties, and the properties they manage. SELECT s.bno, s.sno, fname, lname, pno FROM staff s, property_for_rent p WHERE s.sno = p.sno;

32. Sorting a join (Cont.) SELECT s.bno, s.sno, fname, lname, pno FROM staff s, property_for_rent p WHERE s.sno = p.sno ORDER BY s.bno, s.sno, pno;

33. Three-table join For each branch, list the staff who manage properties, including the city in which the branch is located and the properties they manage. SELECT b.bno, b.city, s.sno, fname, lname, pno FROM branch b, staff s, property_for_rent p WHERE b.bno = s.bno AND s.sno = p.sno ORDER BY b.bno, s.sno, pno;

34. Left outer join List the branch offices and properties that are in the same city along with any unmatched branches. SELECT b.*, p.* FROM branch1 b LEFT JOIN property_for_rent1 p ON b.bcity = p.pcity;

35. Right outer join List the branch offices and properties that are in the same city and any unmatched properties. SELECT b.*, p.* FROM branch1 b RIGHT JOIN property_for_rent1 p ON b.bcity = p.pcity;

36. Full outer join List the branch offices and properties that are in the same city and any unmatched branches or properties. SELECT b.*, p.* FROM branch1 b FULL JOIN property_for_rent1 p ON b.bcity = p.pcity;

37. Query using EXISTS Find all staff who work in a London branch. SELECT sno, fname, lname, position FROM staff s WHERE EXISTS (SELECT ‘Y' FROM branch b WHERE s.bno = b.bno AND city = ‘London’);

38. Use of UNION Construct a list of all areas where there is either a branch office or a rental property. (SELECT area FROM branch WHERE area IS NOT NULL) UNION (SELECT area FROM property_for_rent WHERE area IS NOT NULL);

39. Use of INTERSECT Construct a list of all cities where there is both a branch office and a rental property. (SELECT city FROM branch) INTERSECT (SELECT city FROM property_for_rent);

40. Use of EXCEPT Construct a list of all cities where there is a branch office but no rental properties. (SELECT city FROM branch) EXCEPT (SELECT city FROM property_for_rent);

41. Add data to the database (INSERT) INSERT INTO table_name [(column_list)] VALUES (data_value_list)

42. INSERT … VALUES Insert a new record into the Staff table supplying data for all columns. INSERT INTO staff VALUES (‘SG16’, ‘Alan’, ‘Brown’, ’67 Endrick Rd, Glosgow G32 8QX’, ‘0141-211-3001’, ‘Assistant’, ’M’, DATE ‘1975-05025’, 8300, ‘WN848391H’, ‘B3’);

43. INSERT uisng defaults Insert a new record into the Staff table supplying data for all mandatory columns: Sno, FName, LName, Position, Salary, and Bno. INSERT INTO staff (sno, fname, lname, position, salary, bno) VALUES (‘SG44’, ‘Anne’, ‘Jones’, ‘Assistant’, 8100, ‘B3’);

44. Modify data in the database (UPDATE) UPDATE table_name SET column_name1 = data_value1 [, column_name2 = data_value2 …] [WHERE search_condition]

45. UPDATE all rows Give all staff a 3% pay increase. UPDATE staff SET salary = salary * 1.03;

46. UPDATE specific rows Give all Managers a 5% pay increase. UPDATE staff SET salary = salary * 1.05 WHERE position = ‘Manager’;

47. UPDATE multiple columns Promote David Ford (Sno = ‘SG14’) to Manager and change his salary to £18,000. UPDATE staff SET position = ‘Manager’, salary = 18000 WHERE sno = ‘SG14’;

48. Deleting data from the database (DELETE) DELETE FROM table_name [WHERE search_condition]

49. DELETE specific rows Delete all viewings that relate to property PG4. DELETE FROM viewing WHERE pno = ‘PG4’;

50. DELETE all rows Delete all records from the Viewing table. DELETE FROM viewing;