Database systems

MITS4003 Database Systems

Take Home Exercise - 4 of Lesson - 4

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

MITS4003 Exercise 4

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. 4.1 Display records from customer table, whose company name starts with alphabet ‘r’ or succeeding alphabets of ‘r’ (up to alphabet ‘z’).

4.2 Retrive records from customer table, whose company name and contact person name starts with alphabet ‘m’ or succeeding alphabets of ‘m’ (up to alphabet ‘z’).

4.3 Display only top two records from customer table.

4.4 List out employee details, which are not living in city, that have ‘le’ character anywhere in the name of city.

4.5 Select the employees with a first name that starts with any character and second character as ’a’, followed by any characters from the employee table.

4.6 Select the Employees with a last name equal to "Davolio" or "King" using ‘IN’.

4.7 Display all the orders detail that have orderdate in between '1997-09-25' to '1997-12-30'.

4.8 Find the count of orders and the maximum freight grouped by EmployeeID. Rows should be filtered out of the results if the maximum freight of a group is less than 800 or the ShippedDate of an order IS NULL.

4.9 List out information about the latest order for each customer.