MySQL Hands-On #1: Simple Queries

• The original .docx version of this assignment can be found here

Prerequisites:

• Before attempting these exercises, run the script named mysqlsampledatabase.sql found at the root directory of this project

Deliverables:

• create a respective .sql file as a submission for each Part listed below.
• The database schema is:

Part 1

• Write a query to display the name, product line, and buy price of all products.
• The output columns should display as Name, Product Line, and Buy Price.
• The output should display the most expensive items first.

Part 2

• Write a query to display the first name, last name, and city for all customers from Germany.
• Columns should display as First Name, Last Name, and City.
• The output should be sorted by the customer’s last name (ascending).

Part 3

• Write a query to display each of the unique values of the status field in the orders table.
• The output should be sorted alphabetically increasing.
• Hint: the output should show exactly 6 rows.

Part 4

• Select all fields from the payments table for payments made on or after January 1, 2005.
• Output should be sorted by increasing payment date.

Part 5

• Write a query to display all Last Name, First Name, Email and Job Title of all employees working out of the San Francisco office.
• Output should be sorted by last name.

Part 6

• Write a query to display the Name, Product Line, Scale, and Vendor of all of the car products – both classic and vintage.
• The output should display all vintage cars first (sorted alphabetically by name), and all classic cars last (also sorted alphabetically by name).