F28DM: DATABASE MANAGEMENT SYSTEMS Queries in the Relational Model: SQL

1. F28DM: DATABASE MANAGEMENT SYSTEMSQueries in the Relational Model: SQL sql

2. TOPICS • SQL Basics with single table • Multi-table queries • Nested queries • Aggregate functions • Views and security sql

3. Employee Tables (From the Company ER diagram ) Employee Project Department WorksOn Location Deadline Relational Model

4. Querying In SQL The basic form of query is : SELECT List of attributes FROM List of tables WHERE condition SELECT firstnames, lastname, dateofBirth FROM DBEmployee WHERE gender = ‘F’; This picks out the names and date of birth for all female employees NB Single quotes for text and dates. Case IS important for text sql

5. Querying In SQL SELECT firstnames, lastname, dateofBirth FROM DBEmployee WHERE gender = ‘F’; Results will be the highligted cells, arranged in the order given in the SELECT line sql

6. More on Querying • The WHERE clause can be dropped if you want data from all rows in a single table SELECT firstnames, lastnName, dateofBirth FROM DBEmployee • We can retrieve all the columns by use of * SELECT * FROM DBEmployee WHERE gender = ‘F’; • Use AND, OR, brackets etc for multiple conditions SELECT * FROM DBEmployee WHERE gender = ‘M’ AND lastname = ‘Johnson’; sql

7. Ordering • Ordering is useful for ALL queries which return more than a few lines. You can’t rely on a query to return the results in the order that you expect. (This assuming a ‘salary’ column exists). SELECT * FROM DBEmployee ORDER BY salary DESC, lastname ASC • This returns the results ordered first in descending order of salary and then in alphabetical order of name. • ASCis the default, so can be omitted sql

8. Duplicates in Retrievals • In general, a query returns the result including duplicated rows • To eliminate these include the word DISTINCT SELECT gender FROM DBEmployee returns many rows containing ‘M’ or ‘F’ SELECT DISTINCT gender FROM DBEmployee returns only two rows : ‘M’ and ‘F’ sql

9. Using Explicit Sets We can write explicit sets using IN SELECT firstnames, lastname FROM DBEmployee WHERE empdNum IN (1,2,3) sql

10. Nulls in Queries • Give the names of employees without supervisors • SELECT firstnames, lastname FROM DBEmployee • WHERE supssn IS NULL; • Or, for those with supervisors: • WHERE supssn IS NOT NULL sql

11. Wildcard Characters and Pattern Matching SELECT ssn FROM DBEmployee WHERE (lastName LIKE 'A%') AND (firstNames LIKE '_on') picks the employees whose last names begin with A and whose first names have three letters, the last two of which are "on" - e.g. "Ron" and "Don". "%" is a wildcard character which matches any number of characters, "_" is a wildcard character which matches a single character NB Other DBMS may use different wildcard characters sql

12. Range tests • We can limit the values of a column to lie within a specific range: • SELECT firstNames, lastName FROM DBEmployee • WHERE empdNum BETWEEN 5 AND 7 //inclusive • which picks the employees whose work for departments 5, 6 and 7. sql

13. Functions in MySQL • Look up the MySQL Reference, chapter 12 on ‘Functions and Operators’ to find out about built in functions. • For example, you could use a numerical function ROUND to round the result of a calculation, or the string function SUBSTR to extract part of a string. There are various date and time functions too. sql

14. Functions examples • To compare text ignoring case: SELECT * FROM DBEmployee WHERE UPPER(lastName) = ‘GORDON’; • Rounding calculation results SELECT ROUND(AVG(salary),2) FROM DBEmployee • Using a date function to get date in format dd.mm.yyyy SELECT DATE_FORMAT (dateOfBirth,GET_FORMAT(DATE,'EUR')) FROM DBEmployee; sql

15. Parameters • Many queries will be more versatile if you could change the value searched for at run-time. • Later, we will see how to do this. sql

16. User defined variables in MySQL • You can use user-defined variables in MySQL to remember a value from a query and use it in a later query: SELECT @min_salary:=MIN(salary) FROM DBEmployee; SELECT * FROM DBEmployee WHERE salary=@min_salary; • Of course, this is now a MySQL-specific query • You could do the same thing with subqueries or by creating views (covered later) or temporary tables. sql

17. Removing or updating rows Removing Tuples DELETE FROM DBEmployee WHERE lastname = ‘Brown’; removes any employees named ‘Brown‘. How would you remove all employees? Updating Tuples The first query would set ALL lastnames to Brown! Bad idea! UPDATE DBEmployee SET lastname = ‘Brown’; UPDATE DBDepartment SET mgrssn = 666 WHERE dNum = 2; sql

18. MULTI_TABLE QUERIES sql

19. Multi-Table Queries • List the last name and department name of employees who earn less than 30000. SELECT lastname, dName FROM DBEmployee, DbDepartment WHERE empdNum = dNum AND salary < 30000; • The conditions include a link on the foreign keys and restrictions on the data. • I like to have all the foreign keys restrictions first, nearest the list of tables sql

20. 2 table query - correct SELECT lastname, dName FROM DBEmployee, DbDepartment WHERE empdNum = dNum AND salary < 30000; sql

21. Comparison with programming In programming terms, the effect of this is: for each record in the Employee table for each record in the Department table if ( (empdNum == dNum ) && (salary < 30000 ) ) print lastname and department name sql

22. 2 table query - incorrect • List the last name and department name of employees who earn less than 30000. SELECT lastname, dName FROM DBEmployee, DbDepartment WHERE salary < 30000; Missing foreign key restriction between the tables – all Employee records will be linked with all Department records – so ALL names will be linked with all departments sql

23. 2 table query - incorrect sql

24. Many to many tables • To find the names of the projects that Gordon Smith works on, we need • The Employee table, to restrict to Gordon Smith (WHERE lastname = ‘Smith’ etc) • The Project table, to find the project name (SELECT pName) • The WorksOn table, which provides the vital links between the first 2 tables – who works on what. (WHERE ssn = wssn AND pNum = wpNum) sql

25. Many to many sql

26. Many to many SELECT pName FROM DBProject, DBWorksOn, DBEmployee WHERE ssn = wssn AND pNum = wpNum AND lastname = ‘Smith' AND firstNames = ‘Gordon’; sql

27. Explicit joins – inner joins • An alternative way explicitly states the join, using INNER JOIN instead of WHERE • An inner join pulls out all matching data from both tables. • This style separates the foreign keys used to join the tables from restrictions on data values. However it becomes far more complex when multiple joins are used • Both are allowed in SQL2 but not used often (except in MS Access) – don’t use inner joins in the coursework sql

28. Alternative way of using inner join • Implicit join: SELECT lastname, dName FROM DBEmployee, DbDepartment WHERE empdNum = dNum AND salary < 30000; • Explicit join: SELECT lastname, dName FROM DBEmployeeINNER JOIN DbDepartment ON empdNum = dNum WHERE salary < 30000; sql

29. Outer joins • Inner joins only pull out the data where an entry exists in both tables • Suppose we want all employee names, plus, if they are in a department, the department name. • Left outer join pulls out all records from LH table, plus info from RH table if it exists – fills with null otherwise • You also get Right outer joins, and outer joins SELECT name, dName FROM DBEmployee LEFT OUTER JOIN DBDepartment ON empdNum= dNum; sql

30. Left outer join In an ordinary join, the middle row with null department would not be included sql

31. Identifying Attributes Using the Table Name • Attributes can be identified using the table name and the ‘dot’ operator. This is essential if the attribute name is identical in both tables. • Suppose project name and department name are both in columns called ‘name’ • List the names of all projects in the payroll dept SELECT DBProject.name tablename essential FROM DBProject, DbDepartment WHERE (DBDepartment.dNum = DBProject.pdNum) tablenames above are optional AND (DBDepartment.name = ‘Payroll‘ tablename essential sql

32. Table aliases 1 • Alternatively you can use aliases for table names, usually one or two letters SELECT P.name FROM DBProject P, DBDepartment D WHERE (D.dNum = P.pdNum) AND (D.dname = ‘Payroll') sql

33. Table aliases 2 • Table aliases are necessary if the same table is used more than once in a query • Example, print out the ssn and name of the people supervised by ‘Johnson’: SELECT E.ssn, E.name FROM DBEmployee E, DBEmployee S WHERE E.supssn = S.ssn AND S.name = ‘Johnson’; sql

34. Set Operators : Union • SQL supports the union operator, automatically removing duplicates (unlike other SQL queries) SELECT forename, surname FROM Student UNION SELECT FName, lName FROM Staff; • Note both sides of the UNION must have exactly the same columns • i.e. the same number of columns, and each column must be of the same domain • the column names can differ as above sql

35. Set Difference and Intersection • Set difference and intersection are supported to varying degrees in different DBMS. • For MySQL, we need to use subqueries to achieve these. sql

36. NESTED QUERIES sql

37. Many to many recap SELECT pName FROM DBProject, DBWorksOn, DBEmployee WHERE ssn = wssnAND pNum = wpNum AND lastname = ‘Smith' AND firstNames = ‘Gordon’; sql

38. Nested Queries : Set Containment • In the where clause we can test whether a value is related to the result of a nested sub-query. • Find the names of all the projects that Gordon Smith works on SELECT pName FROM DBProject WHERE pNum IN (SELECT wPNum FROM DBWorksOn, DBEmployee WHERE ssn= wssn AND lastname = ‘Smith' AND firstNames = ‘Gordon’); • The sub-query (in brackets) is evaluated as a set of project numbers, and then a test for inclusion is made. • Many people find this approach easier to understand. However, using sub-queries can be inefficient. sql

39. IN is more useful for negative queries, which cannot be rewritten in this way. e.g. Find the names of the projects that Gordon Smith does not work on by replacing IN above by NOT IN in the previous example ? why could we not simply replace the last line above with lastname <> ‘Smith’ AND firstNames <> ‘Gordon’ sql

40. Existence Tests • To test for the existence of something in the database, we test whether an empty set is returned as the result of a query: • Find the names of all projects with deadlines • i.e. where there are some deadlines for this project SELECT pName FROM DBProject WHERE EXISTS (SELECT * FROM DBDeadline WHERE DBProject.pNum =DBDeadline.dLinepNum) sql

41. Existence Tests • We don’t need to specify particular attributes in the subquery as we are simply interested in whether a row exists or not • This query can also be rewritten as a join, or by using IN • Negative existence is more useful. i.e. Employees without dependents : replace with NOT EXISTS sql

42. Set Containment • We may want to test for whether one set is wholly included in another • E.g. Give the name of employees who work on all Department 5's project • This sounds easy in English but, depending on your DBMS, can be hard. • In MySQL, we can use double negatives • We want the employee if there aren’t any projects in department 5 that the employee does not work on sql

43. Set Containment Has To Be Done Like This SELECT lastname, firstNames FROM DBEmployee WHERE NOT EXISTS Find all the projects in dept 5 ( (SELECT pNum FROM DBProject WHERE pdNum = 5 ) MINUS Find all the projects that the Employee works on ( SELECT wpNum FROM DBWorksOn WHERE ssn = wssn ) ) If there aren’t any projects in department 5 that the employee does not work on – we select this person sql

44. AGGREGATE FUNCTIONS sql

45. Aggregate Functions • We can write SELECT AVG (salary) FROM DBEmployee • or SELECTCOUNT (distinct supssn) FROM DBEmployee which returns the number of supervisors SUM, MIN & MAX are also available • All of these return one value derived from all the values in a column sql

46. Group By Clause SELECT COUNT(*) numProj, pdNum FROM DBProject GROUP BY pdNum produces the number of projects in each department Note the renaming of the count in the result table. In Standard SQL, anyattribute which is appears in the GROUP BY line must also appear in the SELECT line sql

47. Nested aggregate query SELECT avg (numProj) FROM (SELECT COUNT(*) numProj, pdNum FROM DBProject GROUP BY pdNum); produces the average number of projects in each department sql

48. Having Clauses SELECT AVG(salary), empdNum, count(*) FROM DBEmployee WHERE gender = ‘M’ GROUP BY empdNum HAVING COUNT ( * ) > 2 • Now the query is restricted to departments with more than two male employees. HAVING is used with GROUP BY to restrict the groups going into the final result – here which depts WHERE filters the rows going into the result sql

49. Transactions - definition • Database transactions are logical units of work which must ALL be performed to maintain data integrity • E.g. Move money from one account to another • UPDATE Account SET balance = balance – 100WHERE accountNo = 123; • UPDATE Account SET balance = balance + 100WHERE accountNo = 124; • Another example would be a purchase where we create an order, decrease stock quantity, add payment. sql

50. TRANSACTIONS sql