On This Page
Preparation and Background Information
In this assignment we practice developing SQL queries.
To ensure your understanding of SQL queries, I suggest you complete the following tutorials from SQLCourse2.com.
- SELECT Statement
- ORDER BY clause
- Combining conditions and Boolean Operators
- IN and BETWEEN Conditional Operators
- Table Joins, a must
You do not need to work on the aggregate functions GROUP BY or HAVING at this time. We will cover these topics later.
After you feel confident with your query-generating ability, record a SQL query for each of the following problems. You must record each query in a separate file named queryX.txt, where the X is the number of the following problems. Do NOT record anything in the
queryX.txt files except the query or you may get a low score. For instance, do NOT put your name in the
queryX.txt file and do NOT copy the problem statement into the file.
Note that on this assignment you must work alone. If you have difficulty, please contact the instructor for assistance.
Note to old Mac Users: PHP does not seem to like Mac end of lines character ("\r"). So make sure you choose the option that uses the *nix end of line character ("\n") in your text editor before turning in an assignment. For instructions on settings for various text editors, see John Govsky's page: Text Editors for HTML and Script Editing
- First make sure you have completed the exercises from lesson 2 and have saved the files using the specified names exactly. Using the specified file names makes for easier grading.
- Using the form at the bottom of the page for Table Joins, a must, write a query using a join to determine which items were ordered by each of the customers in the customers table. Select the customerid, firstname, lastname, order_date, item, and price for everything each customer purchased in the "items_ordered" table. When you are satisfied with your SQL query, record it in query1.txt. Note that you can view this query as a link from the page.
- Write a second query using a join, selecting the customerid, firstname, state, and item ordered for everything each customer purchased in the "items_ordered" table but display the results sorted by state in descending order. When you are satisfied with your SQL query, record it in query2.txt. Note that the answer is available as a link from the page.
- Using the Artzy database, list the product number, price and quantity ordered for order number 2. Your query should produce results just like the following. When you are satisfied with your SQL query, record it in the file query3.txt.
- Using the Artzy database, list the supplier name, quantity ordered, date ordered and product name of every purchase orders. Order your results by supplier name and date ordered both in ascending order. Your query should produce results just like the following. When you are satisfied with your SQL query, record it in the file query4.txt.
| Brush Bros.
|| Brush, Big
| Brush Bros.
|| Brush, Small
| Canvas Co.
| Painters, LLC
|| Oil Paint
| Painters, LLC
|| Acry. Paint
- Zip your files and submit the zipped archive file to Blackboard as explained in the section of this document: What to Turn In.
Note: Please do not put any spaces or special characters like #, : or $ in any file or folder names.
The following are worth extra credit points:
- Using the Artzy database, list the supplier name, supplier ID, product name and product ID for all suppliers who are not supplying products. Your query must use a LEFT JOIN and check for NULL data, producing the following response: (1 point)
| Unused Supplier
When you are satisfied with your SQL query, record it in the file
query5.txt. Note that the word "NULL" may appear in phpMyAdmin rather than a blank element as shown above.
The instructor will evaluate your assignment using the following criteria. Each criteria represents a specific achievement of your assignment and has a scoring guide. The scoring guide explains the possible scores you can receive.
Some scoring guides have a list of indicators. These indicators are a sign of meeting, or a symptom of not meeting, the specific criterion. Note that a single indicator may not always be reliable or appropriate in a given context. However, as a group, they show the condition of meeting the criterion.
For information on grading policies, including interpretation of scores, see the course information page.
- 2: All lesson exercises attempted and turned in
- 1: Some lesson exercises completed and turned in
- 0: No lesson exercises completed or turned in
One point for each SQL query attempted that loads and runs, and one point for each correct query. (8 points possible)
- 2: README.txt file submitted with specified information included
- 1: README.txt submitted but some information was not included
- 0: No README.txt submitted
Maximum Score: 12, plus extra credit
What to Turn In
Submit your assignment following the instructions for homework. Create a single zip file with the following:
- All the exercise files from Lesson 2
- query1.txt with only the first query
- query2.txt with only the second query
- query3.txt with only the third query
- query4.txt with only the fourth query
- query5.txt with only the extra credit query (optional)
Note: Do NOT submit Word or RTF files, or anything but a text file. You can easily tell if a file is a text file by looking at it in Blackboard after uploading the file. If you can read the text, with out seeing extraneous characters or causing an application to load, then it is a text file. Submitting anything but a text file may result in a 0 (no credit) or low score for the assignment.
Last Updated: April 15 2012 @19:03:25