Database Management Systems & Programming

1. Database Management Systems & Programming LIS 558 - Week 6 Structured Query Language Faculty of Information & Media Studies Summer 2000

2. Class Outline • SQL -- What is it? • Basic SQL select statements • Using expressions and functions to convert data into information • Using multiple criteria for searching • Producing output in a specific order • Providing summary data from groups of records • Displaying data from more than one table • Showing data that uses the results of another query as criteria

3. SQL - Structured Query Language • SQL is a non-procedural data access language that is used primarily in programming by embedding it in other languages • SQL is not a user-level language • SQL accepts one or more relations as input and produces a single relation as output • SQL provides functions for data definition (creates database and table structures), data management (enter, edit, delete data), and data query (convert data into information)

4. SQL • Developed in mid 1970s by IBM; endorsed by ANSI (American National Standards Institute) as the language of choice for manipulating relational databases • Language used by DB2, SQL/DS, ORACLE, INGRES, SYBASE, dBase, Paradox, Access (each with its own dialect) • Computer systems are able to exchange data by passing SQL requests and responses to one another

5. General SQL Query Syntax SELECT columns to extract FROM tables containing columns WHERE search criteria to restrict rows that are returned GROUP BY summarizes query results by groups HAVING search criteria to restrict groups that are returned ORDER BY sorts results by one or more columns required optional, must be in this order if any or all are used • Preceding is the order in which clauses should appear • Order of processing is as follows: From, Where, Group by, Having, Order by, Select

6. Projections SELECT Name, Salary FROM Employee SELECT Office, EmpID, Name FROM Employee

7. Unique Projections SELECT DISTINCT Name, Salary FROM Employee SELECT DISTINCT Office, Salary FROM Employee appears only once

8. Selections SELECT EmpID, Name, Office, Salary FROM Employee WHERE Office = ‘Brussels’ SELECT * FROM Employee WHERE Office = ‘Brussels’

9. Combining Selections and Projections SELECT Name, Salary FROM Employee WHERE Office = ‘Brussels’ SELECT Name, Office, Salary FROM Employee WHERE EmpID = 35 Single quotes necessary around text and dates (but not values) in criteria.

10. Comparison Search Conditions SELECT Name, Salary FROM Employee WHERE Salary >= 2500 SELECT EmpID, Name, Office FROM Employee WHERE Name <> ‘Anne Abel’

11. Comparison Operators Equal to any member of the list IN( list ) BETWEEN low AND high Greater than or equal to one value, and less than or equal to another Matches the following pattern LIKE * a string of zero or more characters ? a string of one character Missing value IS NULL Reverses preceding operators NOT Comparison Search Conditions

12. Examples of Search Conditions SELECT Name, Salary FROM Employee WHERE Office IN (‘Brussels’, ‘Tokyo’) SELECT Name, Office, Salary FROM Employee WHERE Name Like ‘*am’ SELECT Name, Office, Salary FROM Employee WHERE Office is Null SELECT Name, Office, Salary FROM Employee WHERE Name Like ‘Ab?’ SELECT Name, Office, Salary FROM Employee WHERE Office NOT IN (‘Toronto’) SELECT Name, Office, Salary FROM Employee WHERE Salary between 2000 and 3000

13. Compound Comparison Search Conditions SELECT Name, Salary FROM Employee WHERE (Office IN (‘Brussels’, ‘Tokyo’) or Salary is Null)and HireDate <= ‘7/15/99’ AND means that ALL conditions must be met SELECT Name, Office, Salary FROM Employee WHERE Name NOT Like ‘*Abel’ and Salary >= 3100 SELECT Name, Office, Salary FROM Employee WHERE Office IN (‘Toronto’) or Name = ‘Anne Abel’ OR means that ANY condition may be met when operators are combined, brackets are evaluated first (inner to outer) SELECT Name, Office, Salary FROM Employee WHERE (Salary between 2000 and 3000 and Office <> ‘Tokyo’) or (Name like ‘Gor*’ and EmpID > 20)

14. Sorting SELECT Name, Salary FROM Employee WHERE Office = ‘Tokyo’ ORDER BY Salary, Name SELECT Name, Office, Salary FROM Employee WHERE Salary >= 2000 ORDER BY EmpID DESC

15. Expressions using Arithmetic SELECT Name, Salary, Commission/Salary FROM Salesperson WHERE Commission > .05*Salary SELECT ItemName, Price*1.15 FROM Product current system date SELECT ContactName, CompanyName FROM Customer WHERE Paid is null and OrderDate >= ‘1/1/99’ ORDER BY Date-InvoiceDate SELECT Name, (Date-Birthdate)/365 FROM Employee ORDER BY Name SELECT EmpID, Hiredate+90 FROM Employee ORDER BY Name

16. SQL Built-in Functions SELECT Count(*) FROM Employee SELECT Count(Distinct Office) FROM Employee SELECT Sum(Salary) FROM Employee SELECT Max(HireDate) FROM Employee SELECT Min(Name) FROM Employee SELECT Avg(Salary) FROM Employee

17. Aggregate Functions and Grouping SELECT Office, Status, Max(Salary) FROM Employee GROUP BY Office, Status SELECT Office, Count(*) FROM Employee GROUP BY Office To view the groupings, you must also select them!

18. More Grouping Functions SELECT Category, Avg(Price), Min(Quantity), Sum(Price*Quantity) FROM Product WHERE SupplierID in (1, 2) or SupplierID is null GROUP BY Category ORDER BY Avg(Price)

19. Restrict Groups with “Having” SELECT Category, Avg(Price), Min(Quantity), Sum(Price*Quantity) FROM Product WHERE SupplierID in (‘1’, ‘2’) or SupplierID is null GROUP BY Category HAVING Min(Quantity) < 5 The ‘WHERE’ clause is always evaluated before the ‘HAVING’ clause.

20. Another ‘Having’ example SELECT Office, Max(Salary) FROM Employee WHERE Status = ‘full-time’ GROUP BY Office HAVING Count(*) > 1 ORDER BY Office

21. Subqueries Subquery is always evaluated before the main query. SELECT ProdID, Price, Qty FROM Product WHERE SupID IN (SELECT SupplierID FROM Supplier WHERE City = ‘London’) SELECT ProdID FROM Product WHERE Qty > (SELECT Avg(Qty) FROM Product WHERE SupID IN (‘1’, ‘2’))

22. Nested Subqueries Determine the number of policies sold in area 100 of type 45: SELECT Count(Unique PolicyNum) FROM PolicyPlan WHERE PlanCode = ‘45’ and PolicyNum IN (SELECT PolicyNum FROM Commission WHERE AgentNum IN (SELECT AgentNum FROM Agent WHERE Area = 100))

23. Join (Natural Join) SELECT Product ProdID, Product ProdDesc, SupplierSupplierName FROM Product, Supplier WHERE ProductSupID = SupplierSupplierID Table names required in Select statement only if there’s a possibility of ambiguity

24. Renaming Attributes and Relations with an Alias SELECT SupplierName, avg(price) As “Average Price”, count(*) As “# of Items” FROM Product P, Supplier S WHERE PSupID = SSupplierIDand Category = ‘Accessories’ GROUP BY SupplierName ORDER BY SupplierID alias

25. ‘Exists’ and ‘not exists’ in Subqueries Which employees have dependents of the same sex as themselves? Which employees have no dependents? SELECT Name FROM Employee E WHERE EXISTS (SELECT * FROM Dependent D WHERE E.EmpID = D.EmpID and E.Sex = D.DepSex) SELECT Name FROM Employee E WHERE NOT EXISTS (SELECT * FROM Dependent D WHERE E.EmpID = D.EmpID)