Learning Objectives:
1. Understand how to write SQL statements and run queries to retrieve data from databases;
2. Master the basic usage of the SELECT statement;
3. Get familiar with the popular open-source database management system (DBMS) MySQL, and the associated software package XAMPP
Preparation:
a. Download the MS Word file HW4 – SQL – 2020 Fall - Answerbook.docx and save it using the naming convention outlined above. Open the file and record the required students’ details on p.1.
b. In you have not installed XAMPP, install it and make check it runs properly – detailed instructions were provided in the installation tutorial (in Canvas). Also follow the instructions on how to set up security.
c. Start XAMPP, start Apache and MySQL, and get into phpMyadmin (follow the instructions in the installation guide and lecture slides)C
d. Create an empty database named using your name initials (not full names) using the following naming convention LastIniFirstIni-MusicStar. For example, if your name is George Burdell, name the database BG-MusicStar.
Note: It is IMPORTANT to name the database with your own name. No credit will be given for screenshot submissions for which the names of the databases do not match the name(s) of the student(s).
e. Download the following SQL file from Canvas: MusicStar.sql. This file contains the commands to create the database tables and to add the records to the tables.
f. Import the MusicStar.sql into MySQL and execute the contents (see XAMPP instructional videos on Canvas to show you how this can be done). Verify that all the tables have been created and records added.
General Rules:
Each answer MUST be on a different page in the answer word document. An ‘answer’ includes both the relevant SQL and accompanying screenshot. See Query 1 in the answer document for an example of what the screenshot must look like. To help with pagination, each page has a heading. Be careful not to accidentally insert additional pages (only the first 14 pages of this document will be graded).
The SQL statement has to be placed above the screenshot (i.e., SQL statements will be placed towards the top of the page, under the page heading – e.g., Query 5).
The SQL statement must be correctly formatted – it cannot be a single line statement (with wrapping) in either your answer or in the screenshot. Indentation should be used – see lecture examples for formatting.
If you need to make your screenshot smaller to fit on the same page as the applicable SQL statement, the aspect ratio must be preserved to prevent distorting the image.
You must use the explicit JOIN notation for all your queries: “The implicit join notation is no longer considered a best practice. See here”
For inner joins, use the notation: INNER JOIN, rather than JOIN.
Alias for Tables can be used – see here. However, this is usually optional (mandatory use is noted below).
Fields in query results that report aggregate values must be renamed with an appropriate alias – e.g., an alias for average(GPA) could be ‘Average GPA’ - see here.
The font used for SQL statements in this document must be Times New Roman 12 pt.
All queries are equally weighted.
There may be records added to the MusicStar database in the future. The queries you author therefore need to be able to accommodate any such additions (without revision).
Do not include more than 10 records in the submitted screen shot. If necessary, you can limit the output to 10 rows by placing this command on the last line of your SQL statement: LIMIT 10
Do not include duplicate (redundant) information in your query output. For example, if asked to list customer names that have made a purchase, do not list the same customer more than once.
You must not include in your query output any fields that are not necessary to answer the query question.
Final Task: SUBMISSION
Once you are done with saving all query texts and result screenshots the main document (the docx file created in step a) of the Preliminary Tasks), submit this file.
How to take a screenshot
If you are using Windows, follow the steps below to take the screenshot:
Stay in the phpMyAdmin window and press Alt + PrintScreen at the same time. (Nothing will happen immediately when you press these keys). You can also use the utility Snipping Tool, which is installed on most PCs.
Switch to the MS Word document with your answers, left-click on the place in the document where you want the image to be inserted, and press Ctrl + V (or right-click on the mouse and select Paste). The phpMyAdmin screen should show up as a picture in your Word document.
Make sure the picture contains the database name (the left panel of the window) and the query result.
If you are using Mac OS X, follow the steps below to take the screenshot
Stay in the phpMyAdmin window and press Command-Shift-4, then press the Spacebar. The cursor will change to a camera and the application window below the cursor will be highlighted. When you have the cursor over the phpMyAdmin window, just click the mouse button while holding down the Control key at the same time. There will be a camera shutter sound and the screenshot will be placed on the clipboard.
Switch to the Word document, click on the place in the document where you want the image to be inserted, and press Command + V (or select Paste). The phpMyAdmin screen should show up as a picture in your Word document.
Make sure the picture contains the database name, the query, and the query result.
MusicStar is a Canadian-based online retailer of music. Music is sold by the track (a customer does not have to purchase the whole album). In a single purchase, a customer can buy multiple music tracks from different music artists, drawn from different albums. Each purchase produces a separate invoice, with each track being an invoice line item. In this respect, the Invoice is a header, with the details of what has been purchased listed in the associated line items.
Each track belongs to an album, which is associated with a specific music artist. Each track is also a member of a category of music – e.g. Jazz. Tracks can also be placed on playlists
The schema of this database (taken from the Designer tab) is shown graphically below. This image is helpful in highlighting the relationships between the tables.
The schema reflects the efficient storage of data (with each table focusing on data about a single ‘thing’), the relationships between data is also recorded via the use of foreign keys.
Tasks:
Write the appropriate SQL statements for each of the following queries, run them in MySQL (in phpMyAdmin), and save the query and results in your MS Word document in the form of a screenshot.
Query 1 (example – no credit)
List the id and name of customers who are from the United States and made a purchase of more than $20. Order the output by the customer’s id.
NOTE: The answer for this query is already included in “HW4_AnswerSheet.docx” as a sample answer. You do not have to do anything for this query. Use this format for all the other parts of the assignment (i.e., include text of query and screenshot after running the query – the name of your database should be visible).
Query 2
What was the date of the last sale of an album containing the track "Take It Or Leave It"? Restrict your query to sales within the following countries: Canada, The United States of America, and United Mexican States. Hint: You are required to use set inclusion when writing some of the conditions.
Query 3
Each track is classified by genre. Examples of music genre include rock, jazz, and reggae. List the names of all customers who have purchased reggae music. Order the report by customer’s last name.
Query 4
List the best customers who purchased Jazz (in terms of overall dollar sales of Jazz). Higher-value customers should be reported first.
Query 5
Albums are a set of tracks released by a music artist. The tracks the artist performs may, or may not, be composed by the performing artist/s. List the distinct genres of the tracks that Eric Clapton has performed on. However, exclude any tracks where Clapton did not either compose the track or co-compose the track with another composer.
Query 6
Report the number of tracks sold, and the total cost of those tracks. For this query, restrict the focus to only purchases made in the Canadian province of Alberta during 2009. Also exclude any tracks where the composer is unknown.
Hint: You are required to use ranges when writing some of the conditions.
Query 7
Report the number of tracks sold of each music genre. However, exclude any genre where the total number of tracks sold is less than 200. Order the report so the music genre with the most tracks sold is reported first. Hint: You will need to use HAVING to specify a group condition.
Query 8
List the names of employees who report to either Michael Mitchell or Nancy Edwards, or were hired after 2002. In addition, all employees in this report must live in Calgary, Alberta.
Query 9
List the name and unit price of tracks whose price is above the average unit price across all tracks. Order the report by track name. Order the report by the names of tracks. Hint: You are required to use a NESTED query.
Query 10
List the names of tracks that have never been sold, in alphabetical order. Hint: You are required to use a NESTED query. Hint: You are required to use the NOT IN combined Boolean operator.
Query 11
We will investigate the existence of track outliers in terms of the space (in bytes) required to store tracks. List the track name, track size (in bytes), and average track size (in bytes) of the track’s respective genre, A track outlier is defined as any track whose number of bytes in size is more than 5 standard deviations above the mean for the track genre it is a member of. Hint: You are required to use one of the JOIN commands, a NESTED query within the FROM clause, and aliases for variable and table name. You may find useful to review an example in MGT 2210 [#4] Advanced Queries.
Hint: you are required to use AVG() and STDDEV() functions