Northwind database questions and answers pdf

Database Systems

Take Home Exercise - 5 of Lesson - 5

June 2018 Task 1: Using Northwind Database, write SQL statements for the following data retrieval operations.

MITS4003 Exercise 5

Copyright © 2015-2018 VIT, All Rights Reserved. 2

Note: You may need to refer to the schema for the exact field names while framing the queries to the following questions. While framing the question, the words ‘code’, ‘number’ or ‘ID’ may have been synonymously used. For instance, ‘Customer Number’ when used may refer to a field called CustomerID. Similarly, wherever the word ‘name’ is used, appropriate interpretation may be needed based on the schema – for instance if ‘customer name’ is required to be printed, you may need to retrieve CompanyName field from the Customers Table; likewise, when ‘employee name’ is required (without any further qualification, you may retrieve the lastname field of Employees table. 5.1 Find out all the customers and their orders and filter the result on customer's city with 'Vancouver', use Inner Join. 5.2 Display orders which are placed by customers, use Left Outer Join. 5.3 Display supplier names and order ID for all orders, use Inner Join. 5.4 Get supplier name, order ID and product name for all orders supplied by "Norske Meierier", use Inner Join. 5.5 List the shipper name and the number of orders shipped by each shipper, use Inner Join. 5.6 Display all customers details (who have placed orders) and orders details which are placed by customers, use Right Outer Join. 5.7 Display the output of Employee having initial of first name + Dot + last name like “D.Trump”.

5.8 Retrieve the initials of first and last name of employee and display it in Upper case having Dot in between like “D.T”.

5.9 Retrieve three characters from the employees' first names, starting at the third character (should be in lower case) and next two characters must be upper case (example: If first name is “Nancy” then output should be nCY).

5.10 Find total order amount from Order Details table based on Orderid, using formula (unitprice – discount) * quantity, use Sum and Group by. Display output in orderwise of Orderid.

5.11 Display the past dates respectively 3 years ago, 11 months ago and 5 days ago to the current.

5.12 Display the day differences from Orders table between following:

Order date and required date Order date and shipped date Shipped date and required date