Structured Query Language

1. Structured Query Language Chapter Three Part 2 – Queries

2. Sample DB details • PROJECT (ProjectID, Name, Department, MaxHours) • EMPLOYEE (EmployeeNumber, Name, Phone, Department) • ASSIGNMENT (ProjectID, EmployeeNum, HoursWorked)

4. Referential Integrity Constraints for Sample DB • ProjectID in ASSIGNMENT must exist in ProjectID in PROJECT • EmployeeNum in ASSIGNMENT must exist in EmployeeNumber in EMPLOYEE

5. Business Rules for Sample DB • If a PROJECT row is deleted, then all of the ASSIGNMENT rows that are connected to the delete PROJECT row also will be deleted. • If an EMPLOYEE row is deleted and that row is connected to any ASSIGNMENT, the EMPLOYEE row deletion will be disallowed. (It must first be re-assigned to another employee).

6. SQL for Data Retrieval (Queries) • SELECT is the best known SQL statement • SELECT will retrieve information from the database that matches the specified criteria SELECT EmpName FROM Emp;

7. The Results of a Query is a Relation • A query pulls information from one or more relations and creates (temporarily) a new relation • This allows for a query to: • Create a new relation • Feed information to another query (as a “sub-query”)

8. Displaying All Columns: * • To show all of the column values for the rows that match the specified criteria, use an * SELECT * FROM Emp;

9. Showing a Row Only Once: DISTINCT • A qualifier may be added to the SELECT statement to inhibit duplicate rows from displaying SELECT DISTINCT DeptID FROM Emp;

10. Specifying Search Criteria: WHERE • The WHERE clause stipulates the matching criteria for the record that are to be displayed SELECT EmpName FROM Emp WHERE DeptID = 15;

11. Match Criteria • The WHERE clause match criteria may include • Equals “=“ • Not Equals “<>” • Greater than “>” • Less than “<“ • Greater than or Equal to “>=“ • Less than or Equal to “<=“

12. Match Operators • Multiple matching criteria may be specified using • AND • Representing an intersection of the data sets • OR • Representing a union of the data sets

13. Operator Examples SELECT EmpName FROM Emp WHERE DeptID < 7 OR DeptID > 12;

14. Operator Examples • SELECT EmpName • FROM Emp • WHERE DeptID = 9 • AND SalaryCode <= 23;

15. A List of Values • The WHERE clause may specify that a particular column value must be included in a list of values SELECT EmpName FROM Emp WHERE DeptID IN (4, 8, 9);

16. The Logical NOT Operator • Any criteria statement may be preceded by a NOT operator which is to say that all information will be shown except that information matching the specified criteria SELECT EmpName FROM Emp WHERE DeptID NOT IN (4, 8, 9);

17. Finding Data Matching a Range of Values: BETWEEN • SQL provides a BETWEEN statement that allows a user to specify a minimum and maximum value on one line SELECT EmpName FROM Emp WHERE SalaryCode BETWEEN 10 AND 45;

18. Allowing for Wildcard Searches: LIKE • Sometimes it may be advantageous to find rows matching a string value using wildcards • Single character wildcard character is an underscore (_) • Multiple character wildcard character is a percent sign (%)

19. Wildcard Search Examples SELECT EmpID FROM Emp WHERE EmpName LIKE ‘Kr%’; SELECT EmpID FROM Emp WHERE Phone LIKE ‘616-___-____’;

20. Sorting the Results:ORDER BY • The results may be sorted using the ORDER BY clause SELECT * FROM Emp ORDER BY EmpName;

21. Built-in SQL Functions • SQL provides several built-in functions • COUNT • Counts the number of rows that match the specified criteria • MIN • Finds the minimum value for a specific column for those rows matching the criteria • MAX • Finds the maximum value for a specific column for those rows matching the criteria

22. Built-in SQL Functions (continued) • SUM • Calculates the sum for a specific column for those rows matching the criteria • AVG • Calculates the numerical average of a specific column for those rows matching the criteria

23. Built-in Function Examples SELECT COUNT(DISTINCT DeptID) FROM Emp; SELECT MIN(Hours), MAX(Hours), AVG(Hours) FROM Project WHERE ProjID > 7;

24. Providing Subtotals:GROUP BY • Subtotals may be calculated by using the GROUP BY clause SELECT DeptID, COUNT(*) FROM Emp GROUP BY DeptID HAVING Count(*) > 3;

25. Retrieving Information from Multiple Tables • SubQueries • As stated earlier, the result of a query is a relation. As a result, a query may feed another query. This is called a subquery • Joins • Another way of combining data is by using a Join • Join [also called an Inner Join] • Left Outer Join • Right Outer Join

26. Subquery SELECT EmpName FROM Emp WHERE DeptID in (SELECT DeptID FROM Department WHERE DeptName LIKE ‘Accounts%’);

27. Join SELECT EmpName FROM Emp, Department WHERE Emp.DeptID = Department.DeptID AND Department.DeptName LIKE ‘Account%’;

28. Left Outer Join • Left Outer Join SELECT EmpName FROM Emp LEFT JOIN Department WHERE Emp.DeptID = Department.DeptID AND Department.DeptName LIKE ‘Account%’;

29. Right Outer Join • Right Outer Join SELECT EmpName FROM Department RIGHT JOIN Emp WHERE Emp.DeptID = Department.DeptID AND Department.DeptName LIKE ‘Account%’;