Handout for Mar 22, 2010

Databases 1 SQL QUERY Language Study Guide Learning Objectives, you should be able to: Trace SQL statements that use the following features and show the resulting table: o SELECT, FROM, WHERE o Comparison operators: < <= > >= <> = o AND, OR, IN, NOT IN, BETWEEN o LIKE and the wildcards * and ? o IS NULL, IS NOT NULL o ORDER BY, DESC o COUNT, SUM, AVG, MAX, MIN o Arithmetic operators: + ? * / o GROUP BY, HAVING o Subqueries o UNION o PRODUCT, INNER JOIN, LEFT JOIN, RIGHT JOIN Given a query expressed as an English statement, give the SQL query that carries it out, using any of the features listed above as needed Reading Assignment: Chapter 2, pages 123-150. Read my document titled SQL Query Examples for a description and examples of the SELECT-FROM-WHERE statement. Read my document titled Microsoft Access SQL-SELECT for screenshots on how to run SQL queries in Microsoft Access 2007. Procedure: You are going to learn how to extract information from a database using the SQL SELECT command. To do so you will interact with a Microsoft Access database using only SQL statements. Before you begin, have the following resources ready: o Your computer with the WPC data base (what you completed for Assignment AW.2.1) opened in Access 2007 o Your textbook opened to page 124 o My document SQL Query Examples o My document Microsoft Access SQL-SELECT o The README.doc for Assignment SQL2 Getting started: o Read page 123 and 124 of your textbook and its first SQL SELECT example. o Try the example out by entering and running it in the WPC database. o If you don?t remember how to enter SQL queries in Microsoft Access, refer to the screen shots in Microsoft Access SQL-SELECT. o Save the query. Forging on: o Use my document SQL Query Examples as your roadmap; I have liberally salted it with page numbers out of the textbook; my document has many more examples than the textbook. o Read my document and the textbook in tandem. o Use the cited page numbers in my document to keep synchronized with the textbook. o Do all the examples (both mine and the textbook?s) in Access and be sure to save them. Completing the Assignment: o The problems for Assignment SQL2 are ordered to correspond with the examples in my document. o Don?t wait until the end to tackle the assignment. o As you finish each section, turn to the assignment complete the corresponding problems. By working your way methodically through the textbook and my examples and periodically stopping to do the homework, you will learn the details of the SQL SELECT statement. Be sure to allow plenty of time to finish this. Graded Assignment SQL2: Submit the WPC database with the saved queries that you created above. Download and unzip the assignment starting file and follow the directions in the README.doc file.