Average time to complete: 3 hours
Click this link to access an online database that you will use for this exercise. Click on the table names to see the data they contain.
This database contains information about customers and the orders they place. Take some time to review the data to see if you can understand what it means. Some of the columns may not be immediately obvious to you. If you don’t understand something about the data, post a question in Teams in the #General channel. It’s important that you understand the data, because otherwise you will struggle to be successful writing queries that answer questions about it.
Create queries that answer the questions below and test them using the online database.
After testing your .sql files using the online database, create a GitHub submission repository by clicking the “GitHub Submission Repo” link at the top of this page. Use a text editor such as Notepad (not Microsoft Word) to save each query in a separate file named q1.sql, q2.sql, etc. Upload all of your .sql files in a group to the submission repository (you can just drag and drop them into the browser to upload). Wait about 1 minute, then refresh the GitHub page to review an autograder report showing the correctness of your submission.
Scroll all the way down to view the complete report, including an estimated correctness score. Note that the score given by the autograder is approximate, and your final grade may be adjusted by the instructor if there are logic errors in your query that managed to produce correct results.
Here’s a short video I made that illustrates the submission process. I made it for a different class, but the process is the same.
The first 3 submissions are “free.” After that, each submission will cost you 1 point on your grade. Note that the score given by the autograder report is approximate, and your final grade may be adjusted by the instructor if there are logic errors in your query that managed to produce correct results.
Tip: After submitting, if you want to review the test results again or resubmit, you can click the “GitHub Submission Repo” link at the top of this page to access your submission repo.
When you are ready to finalize your submission, create a PDF report (see Report Specification below) with the name report.pdf and upload it to your submission repo. Note that this final upload will count against your 3 free submissions.
Begin by reading each question carefully. Think carefully about any ambiguity in the query specification, and consider the possible interpretations. You may wish to consult the instructor or other students to ensure that you are interpreting the question correctly.
List the date, order number, and name of orders sent to addresses in Monroe, Washington, and Denver, Colorado, sorted by ascending date.
List all employees that have a supervisor, sorted by ascending last name. For each employee, list the last name and a value labeled ‘BonusAmount’. To compute the bonus amount, multiply the employee’s commission rate by 1000.
List all orders being shipped to addresses on Main Street (in any city) in February 2015, sorted by ascending order number.
Note: Write date values like this: ‘2015-01-22’ (YYYY-MM-DD, in single quotes).
For each city in Washington State, list the maximum balance of customers, in a column labeled MAXBALANCE. Sort by descending maximum balance, then by ascending city name.
For each product manufacturer, list the total number of products offered for sale (labeled ‘ProductCount’), and the total retail value of that inventory (labeled ‘InventoryPrice’). Sort by manufacturer.
Note: In the product table, the prodqoh column means “product quantity on hand.”
Create a report using a word processor. See “Formatting Requirements” in the course syllabus for the formatting requirements for the report. Give a three-part answer for each query, using the following headings for each query:
Write a short paragraph of one or two sentences that restates the original specification more precisely by using table and column names and specifying any formulas to be used. Make sure that your restatement does not alter the meaning of the original specification, but merely clarifies it.
For example, here is an analysis for Query 1 (feel free to use it verbatim):
Display the ORDDATE, ORDNO, and ORDNAME values from records in table ORDERTBL that have an ORDCITY value of ‘Monroe’ and ORDSTATE value of ‘WA’, or an ORDCITY value of ‘Denver’ and ORDSTATE value of ‘CO’, sorted in ascending order by ORDDATE.
Paste the SQL query into the document, formatted using the Format button in the online database. Make sure that your query is single-spaced (not double-spaced).
Copy the table of query results and paste into the document. Use Word’s table formatting features (“Autofit > Contents”) to make the table fit appropriately.
Include the following statement at the end of your report, specifying any debugging help received. Note that you do not need to report discussions concerning the interpretation of the assignment specifications.
“By affixing my signature below, I certify that the accompanying work represents my own intellectual effort. Furthermore, I have received no outside help other than what is documented below.”
Signed: ________ (Type your name here)
Help Report:
| Date | Person Helping | Nature of Help | Time Spent |