PROG2110- Assignment 3 MySQL Queries Northwind Db Solved

Requirements:

You will be using the Northwind database (provided as script file). Write a single SQL script file with:

- Comment header meeting the same requirements as those for any submitted SET source code - A single comment with the question number for each solution you provide in the script
- A single SQL statement (solution) for each question in this assignment. The statement may be

written on several lines for clarity.
- The script file must be completely and successfully executed in MySQL. Assume that the

Northwind database is already installed.

Questions:

Display the CustomerID, ContactName, Country and City (in that order) in the Customers table.
Display the countries that are in the Customers table in alphabetical order. Display each country

name only once.
What are the CompanyName and City of all customers in Germany?
Display the CustomerID and ContactName for each customer that does not have a Fax number.
How many products are in the Products tables?
Display the ProductID, ProductName and UnitPrice for each product in the Products table.
Display the ProductName, UnitsInStock and UnitPrice (in that order) for all the products that

cost most than $20 (assuming the unit price is in dollars). Make sure that the list is in UnitPrice

descending order (most expensive at the top of the list).
How many products are discontinued? (Discontinued products are indicated by a value of -1).
Display the CategoryName and ProductName (in that order) for each product.
From the Employees table, combine the Title, FirstName and LastName to display a column

called Salutation.
Display a list of TerritoryDescriptions with their corresponding RegionDescriptions.
For each order detail line, display the OrderID, CustomerID, ProductID and Quantity.
For each order detail line, display the OrderID, CustomerID, ProductID and Extended Price. (The

extended price is the product of UnitPrice and Quantity. Make sure the column is called

“Extended Price”.)
For each order, display the OrderID, OrderDate, CompanyName (Customers) and Employee

Name (combination of first name and last name from Employees).
Display the CustomerID and CustomerName of all Customers that have ever had an order.

(continued on next page)
Display the CustomerID and CustomerName of all Customers that have never had an order.
Add a new region, called ‘Europe’, to the Region table.
Remove the region called ‘Europe’ from the Region table.
For the company called ‘Ernst Handel’, change the name of the contact person to Hans Schmidt.
Increase each UnitPrice in the Products table by $1 (assuming the unit price is in $’s).
Create a new category of products called “Discontinued”.
For each discontinued product, change its category to “Discontinued”.
Submit:
- The script file, as specified above

NM