Exercise 3
You can ignore Question 1 and Question 2 if you already have Northwind database loaded.
Create a Database named
northwind.Download the SQL file from this link and run the code present in it inside the
northwinddatabase. (Make sure that the tables are created insidenorthwinddatabase only)Select the contact name, customer id, and company name of all Customers in London
Select all available columns in the Suppliers tables that have a FAX number.
Select a list of customers id’s from the Orders table with required dates between Jan 1, 1997 and Jan 1, 1998 and with freight under 100 units.
Select a list of company names and contact names of all the Owners from the Customer table from Mexico, Sweden and Germany.
Count the number of discontinued products in the Products table.
Select a list of category names and descriptions of all categories beginning with 'Co' from the Categories table.
Select all the company names, city, country and postal code from the Suppliers table with the word 'rue' in their address. The list should be ordered alphabetically by company name.
Select the product id and the total quantities ordered for each product id in the Order Details table.
Select the customer name and customer address of all customers with orders that shipped using Speedy Express.
Select a list of Suppliers containing company name, contact name, contact title and region description.
Select all product names from the Products table that are condiments.
Select a list of customer names who have no orders in the Orders table.
Insert a new shipper named 'Amazon' to the Shippers table using SQL.
Change the company name from 'Amazon' to 'Amazon Prime Shipping' in the Shippers table using SQL.
Select a complete list of company names from the Shippers table. Include freight totals rounded to the nearest whole number for each shipper from the Orders table for those shippers with orders.
Select all employee first and last names from the Employees table by combining the 2 columns aliased as 'DisplayName'. The combined format should be 'LastName, FirstName'.
Select a list of products from the Products table along with the total units in stock for each product. Give the computed column a name using the alias, 'TotalUnits'. Include only products with TotalUnits greater than 100.
Last updated