Database Homework 5-Advanced SQL  Solution

Note: This document was updated on 3/28/22 to address permissions issue for links in problem 1.

Homework 5: Advanced SQL 

Problem 1: Create a DB and load data 

1.1. First create the database and load the sample data we are providing. To make your job easier, we have provided all the SQL statements needed to accomplish this in the files create_objects.sql and load_data.sql. Download these files using the preceding Canvas links, and upload them to your EC2 instance to a location/folder where you have read, write, and execute privileges. In section, we will cover in full detail how to transfer the files and change folder/file permissions so that the scripts are executable.

Start mysql from the directory where you placed the files and execute the following commands at the mysql prompt in this order:

source create_objects.sql; source load_data.sql;


Query OK, 1 row affected (0.01 sec)

Note: These scripts were sourced from https://www.sqlservertutorial.net/sql-serversample-database/ and adapted for MySQL.

No need to share any screenshots for this portion; we will know you were successful if you are able to complete the next set of tasks. (5 points)

1.2. Execute the following commands:

DESCRIBE TABLE orders; (5 points)
DESCRIBE TABLE order_items; (5 points)
DESCRIBE TABLE staffs; (5 points)
SELECT COUNT(*) FROM customers; (5 points)






2.1. Provide the SQL statement used. (15 points)


SELECT
first_name, last_name, email
FROM
customers JOIN
orders ON orders.customer_id = customers.customer_id
WHERE
ORDER BY first_name , last_name ASC;



2.2. Provide a screenshot showing the last 10 rows of the results obtained by the SQL statement shown above. Make sure it includes the line that indicates how many total rows were returned. (10 points)




3.1. Provide the SQL statement used. (15 points)

SELECT
customers.first_name, customers.last_name, customers.email,
order_id, order_date, store_name,
t.state
FROM
(SELECT
orders.*, stores.store_name, stores.state FROM
orders
JOIN stores ON stores.store_id = orders.store_id
WHERE
stores.state = 'CA'
orders
JOIN stores ON stores.store_id = orders.store_id
WHERE
stores.state = 'NY'
JOIN
customers ON t.customer_id = customers.customer_id;



3.2. Provide a screenshot showing the full result set. Make sure it includes the line that indicates how many rows were returned. (10 points)



Problem 4: Prepare complete contact list for Marketing Team (Points: 15)

In preparation for an upcoming grand opening event for a new BikeStores location, the marketing team has tasked you with putting together a SINGLE list of all customers and employees. The list should contain the following: id, person’s full name, last_name, first_name, email, phone number, and a flag designating if the person is a customer (flag = ‘Y’ or ‘N’). Additionally, the list should be ordered in ascending order by person last_name. (Hint: Ensure that you only include active employees)
The report headers should be labeled as such:
● person_id
● person_full_name
● person_last_name
● person_first_name
● person_email
● person_phone_no
● person_is_customer
4.1 Provide the SQL statement used. (10 points)

SELECT
staff_id AS person_id,
CONCAT(first_name, ' ', last_name) AS person_full_name, last_name AS person_last_name, first_name AS person_first_name, email AS person_email, phone AS person_phone_no,
'N' AS person_is_customer
FROM
staffs
WHERE
active = 1
UNION SELECT
customer_id AS person_id,
CONCAT(first_name, ' ', last_name) AS person_full_name, last_name AS person_last_name, first_name AS person_first_name, email AS person_email, phone AS person_phone_no,
'Y' AS person_is_customer
FROM
customers
ORDER BY person_last_name;



4.2 Provide a screenshot of the header row with column names and the last 10 rows from the results set. Ensure the line indicating the “rows in set” is visible. (5 points)




Problem 5: Manipulating query output that contains NULL values (Points: 10)

This problem involves the use of a MySQL function to substitute a string for NULL values in the query result set (Hint: Greg covered this as part of lecture). Query the customers table for the first 5 records (customer_id 1-5) – use an ORDER BY statement to properly display records. Include the following fields in your result set: customer_id, first_name, last_name, phone, and email. For the phone field, if a customer does not have a phone number (e.g. NULL), instead of NULL in the result set, you should display ‘not on file’.
5.1 Provide the SQL statement used. (5 points)

SELECT
customer_id, first_name, last_name,
IFNULL(phone, 'not on file') AS phone, email
FROM
customers
ORDER BY customer_id
LIMIT 5;




5.2 Provide a screenshot of the entire results set. (5 points)


Bonus – Problem 6: Retrieve a staff report (Points: 10)

Create a SQL statement that shows the first_name, last_name, store_id, email, phone of every manager as well as the number of active staff members reporting to them (tip: A manager for a store reports to Fabiola Jackson). Note: If you wish to include Fabiola Jackon in the results set, you are free to do so, but we will not deduct any points if you do not.

6.1. Provide the SQL statement used. (5 points)

SELECT
s1.first_name, s1.last_name, s1.store_id, s1.email, s1.phone,
COUNT(s2.staff_id) AS num_active_staffs
FROM
staffs s1 JOIN
staffs s2 ON s1.staff_id = s2.manager_id
GROUP BY s1.first_name , s1.last_name , s1.store_id , s1.email , s1.phone;



6.2. Provide a screenshot showing the full result set. Make sure it includes the line that indicates how many rows were returned. (5 points)