A2-SQL Queries

On This Page Preparation and Background Information Specifications Extra Credit Grading Criteria (12) What to Turn In 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 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 ^ top Specifications 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. 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 just like the following. When you are satisfied with your SQL query, record it in the file query4.txt. 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 Zip your files and submit the zipped archive file to Blackboard as explained in the section of this document: What to Turn In. ^ 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 the file query5.txt. 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 information page. Lesson Exercises 2: All lesson exercises attempted and turned in 1: Some lesson exercises completed and turned in 0: No lesson exercises completed or turned in SQL Queries One point for each SQL query attempted that loads and runs, and one point for each correct query. (8 points possible) README.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: 12, plus extra credit ^ top What to Turn In Submit your assignment following the instructions for homework. Create a single zip file with the following: README.txt file 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) for the assignment. ^ top Home | Blackboard | Syllabus | Expectations | Schedule Project | Help | FAQ's | HowTo's | Links Last Updated: March 07 2010 @13:56:57