A2: SQL Queries

On This Page Overview Specifications Extra Credit Grading Criteria (10) What to Turn In Overview During this assignment, you will: Create SQL queries for a single-table Create SQL queries multiple tables Note that on this assignment you must work alone. For more information, see Working Together. ^ top Specifications 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 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. Each query must be 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. 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 this query is available as a link from the Table Joins, a must page. Repeat the query 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 like the following. When you are satisfied with your SQL query, record it in a query3.txt file. ProductID PriceEach Quantity  4  6.25  3 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 in ascending order. Your query should produce results like the following. When you are satisfied with your SQL query, record it in a query4.txt file. SupplierName Qty DateOrdered ProductName  Brush Bros.  25  2001-10-15  Brush, Big  Brush Bros.  25  2001-10-16  Brush, Small  Canvas Co.  20  2001-10-02  Canvas  Painters, LLC  25  2001-10-22  Oil Paint  Painters, LLC  25  2001-10-23  Acry. Paint ^ top Extra Credit 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) SupplierName ID ProductName ID  Unused Supplier  4     When you are satisfied with your SQL query, record it in a query5.txt file. Note that the word "NULL" may appear in phpMyAdmin rather than a blank element as shown above. ^ top Grading Criteria 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 Syllabus. SQL Queries One point for each SQL query attempted that loads and runs, and one point for each correct query. (8 points possible) REAME.txt File 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: 10, plus extra credit ^ top What to Turn In Submit your assignment following the instructions for homework. Include the following items for grading: README.txt file 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) The queryX.txt files must load and run in phpMyAdmin as submitted. If you use the wrong name or they do not load, you may receive a 0 for the query. 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 WebCT 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) for the assignment. ^ top Home | WebCT | Announcements | Course info | Expectations | Schedule Project | Help | FAQ's | HowTo's | Links Last Updated: March 01 2006 @12:32:05