Lab Exercise 1

Instructions:

In this lab exercise, you are required to construct SQL statements toper form data selection within the North wind database, which was provided in Tutorial 3.

In this lab exercise, you must provide solutions for 5 tasks listed below. Your submission should be in a MS Word document and include the following:

• A cover page.
• Solutions for each of the 5 tasks. Each solution must include three parts:
  • SQL Statement: The query written in plain text.
  • Explanation: A brief explanation of how the SQL statement works (approximately 150 words).
  • Screenshot of Results: A screenshot showing the result of running the SQL statement on your computer.

The total score for this lab exercise is 10 marks. Each task is worth upto 2 marks (SQL statement: 0.5 mark; explanation: 1 mark; screenshot: 0.5 mark).

Task 1:

Construct an SQL statement to retrieve the names and postal codes of all customers who are located in London.

Task 2:

Construct an SQL statement to find the total number of orders placed by each customer. Display the customer ID and the total count of orders.

Task 3:

Construct an SQL statement to find all employees who were hired after January 1,2010. Display their employee ID, name, and hire date.

Task 4:

Construct an SQL statement to retrieve the names of customers and the names of the products they have ordered. The result should display the customer name and the corresponding product name.

Task 5:

Construct an SQL statement to retrieve the names of employees and the territories they manage. The result should display the employee's first name, last name, and territory description.