Structured Query Language Part I

1. CIS 218 Structured Query LanguagePart I Chapter Three

2. Structured Query Language • Structured Query Language • Acronym: SQL • Pronounced as “S-Q-L” [“Ess-Que-El”] • Also pronounced as “Sequel” • Originally developed by IBM in 1970s • SEQUEL language

3. SQL Defined • SQL is not a programming language • SQL is comprised of: • A data definition language (DDL) • Used to define structures • Tables, columns, etc. • A data manipulation language (DML) • Used to create, add and delete data rows • Used to retrieve (query) data

4. SQL Queries • SELECT is the best known SQL statement • SELECT will retrieve information from the database that matches specified criteria • Uses SELECT/FROM/WHERE syntax SELECT col1, col2, … FROM tableName WHERE criteria;

5. Displaying All Columns • To show all of the column values for the rows that match the specified criteria, use an asterisk ( * ) SELECT * FROM Tech • Returns all columns and all rows

6. A Query Creates a Relation • A query pulls information from one or more relations • A query creates (temporarily) a new relation • A “virtual” table

7. Displaying a Subset of Columns • To show specific columns, list the column names in the SELECT clause SELECT First, Last FROM Tech • Returns two columns and all rows

8. Displaying a Subset of Rows • To filter rows, add a WHERE clause SELECT * FROM Tech WHERE PayScale = 2 • Returns all columns • Returns only the rows for Techs who have a PayScale of 2.

9. Practice • Display all columns for all jobs • Display the TicketNum, Date, and Description for all jobs • Display all columns for jobs performed by TechID 1 • Display the TicketNum, Date, and Description for jobs performed by TechID 1

10. Showing Unique Values • The DISTINCT keyword • Added to the SELECT statement to prevent rows with duplicate values from displaying SELECT DISTINCT Location FROM Job

11. Specifying Search Criteria • The WHERE clause stipulates the matching criteria for the records that are to be displayed SELECT EmpName FROM EMPLOYEE WHERE DeptID = 15

12. 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 “<=“

13. Strings and Dates • Strings need to be quoted SELECT * FROM Job WHERE Description = “Upgrade Office” • Dates are represented with the “#” sign SELECT * FROM Job WHERE Date > #4/2/2007#

14. NULL values • IS NULL • matches empty valuesSELECT * FROM JobWHERE TechID IS NULL • IS NOT NULL • matches non-empty valuesSELECT * FROM JobWHERE TechID IS NOT NULL

15. Logical Operators • Multiple matching criteria may be specified using • AND • Both conditions must be true • OR • Either condition may be true

16. Operator Examples SELECT First, Last FROM Tech WHERE PayScale > 1 AND PayScale < 3 SELECT Description FROM Job WHERE Date > #1/1/2008# OR TechID = 1

17. Practice • Display the dates and descriptions for all jobs before 1/1/2008. • Display the dates and descriptions for all jobs between 1/1/2007 and 1/1/2008. • Display the dates and descriptions for all jobs performed by techs 1 and 2. • Display the dates and descriptions for all jobs performed by techs 1, 2 or 3.

18. A List of Values • The WHERE clause may include the IN keyword • Specifies that a particular column value must be included in a list of values • Alternative to logical OR SELECT * FROM Job WHERE TechID IN (1, 2, 3);

19. The Logical NOT Operator • Any criteria statement may be preceded by a NOT operator • All information will be shown except the information matching the specified criteria SELECT * FROM Job WHERE TechID NOT IN (1, 2, 3);

20. Finding Data in a Range of Values • BETWEEN keyword • Allows a user to specify a minimum and maximum value SELECT * FROM Tech WHERE PayScale BETWEEN 1 AND 3; • Alternative to: • PayScale >= 1 AND PayScale <= 3

21. Wildcard Searches • SQL LIKE keyword • Allows searches on partial data values • LIKE can be paired with wildcards to find rows matching a string value • Multiple character wildcard character • asterisk (*) • Single character wildcard character • question mark (?)

22. Wildcard Search Examples SELECT First, Last FROM Tech WHERE Last LIKE ‘B????' SELECT * FROM Job WHERE Description LIKE ‘*printer*’

23. Sorting the Results • Query results may be sorted using the ORDER BY clause SELECT * FROM Job ORDER BY Description • Use DESC to sort in descending order ORDER BY Description DESC

24. Built-in SQL 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 • 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

25. Built-in Function Examples SELECT COUNT(TechID) FROM Job SELECT MIN(PayScale) AS Minimum_Scale, MAX(PayScale) AS Maximum_Scale, AVG(PayScale) AS Average_Scale FROM Tech

26. Providing Subtotals • Subtotals may be calculated by using the GROUP BY clause • The HAVING clause may be used to restrict which data is displayed SELECT TechID, COUNT(*) AS NumberOfJobs FROM Job GROUP BY TechID HAVING COUNT(*) >= 2;

27. Retrieving Information from Multiple Tables • Joins • Combines data across tables • SELECT Date, Description, First, Last • FROM Tech, Job • WHERE Tech. ID = Job.TechID • SELECT Date, Description, First, Last • FROM Tech, Job • WHERE Tech. ID = Job.TechID • AND Last = “Bowers”

28. Practice • Display the names of techs whose first names start with A • Display the names of all techs, sorted by last name • Count how many jobs have been completed • Display the names of the techs who completed jobs in 2007.