Simple Queries
Prerequisites
For this lab, you must have the “classicmodels” database. If you do not have the classicmodels database setup, click here to download the database script file. After downloading, you have to run the classicmodels.sql file in SQL. The database schema is as follows:
Objective
You will be writing SQL queries for this assignment. This assignment tests your understanding of SQL queries and SQL clauses.
Instructions
Write SQL queries to accomplish the following tasks:
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.
Display the first name, last name, and city name of all customers from Germany. The output columns should display as: “First Name,” “Last Name,” and “City.” The output should be sorted by “Last Name” (ascending).
Display each of the unique values of the status field in the orders table. The output should be sorted alphabetically, ascending. Hint: The output should show exactly six rows.
Display all fields from the payments table for payments made on or after January 1, 2005. The output should be sorted by the payment date from highest to lowest.
Display the last Name, first Name, email address, and job title of all employees working out of the San Francisco office. The output should be sorted by “Last Name.”
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).
Last updated
