[go: up one dir, main page]
Scribd
Upload
36 views2 pages

Lab Manual Week 05

This document provides instructions for a lab on joins in SQL Server. It explains different types of joins like inner, outer, left and right joins. It lists tasks for students to perform joins on tables from the Northwind database and more advanced tasks like returning customers with and without orders, total orders by customer and generating copies of employee records.

Uploaded by

waliaslam2002
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
36 views2 pages

Lab Manual Week 05

This document provides instructions for a lab on joins in SQL Server. It explains different types of joins like inner, outer, left and right joins. It lists tasks for students to perform joins on tables from the Northwind database and more advanced tasks like returning customers with and without orders, total orders by customer and generating copies of employee records.

Uploaded by

waliaslam2002
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 2

CS 262L- Database Systems

Lab Manual 5

Instructor:
• Mr. Nazeef Ul Haq and Mr. Nauman Babar
Learning Objectives:
• Understanding of JOINS in SQL Server.

Helping Material:
1. JOINS In SQL Server:
A JOIN clause is used to combine rows from two or more tables, based on a related column between them
SQL server provides the following joins:
• Cross Join
• Natural Join
• Equi Join
• Inner Join
• Left Outer Join
• Right Outer Join
• Full Outer Join

2. JOINS SYNTAX:

SELECT ColumnName(s)
FROM Table_1,Table_2
OR
SELECT ColumnName(s)
FROM Table_1 CROSS JOIN Table_2

SELECT column_name(s)
FROM table1 INNER JOIN/ CROSS JOIN/ NATURAL JOIN/ LEFT JOIN/ RIGHT JOIN/ FULL OUTER
JOIN table2
ON table1.column_name = table2.column_name;

Page 1 of 2
Lab Tasks + Home Tasks:
• Perform all JOIN queries on any table using Northwind Schema.
• Perform self-cross join and see if there is any difference between cross join and self cross join.
• SELECT Customers.custid, Customers.companyname, Orders.orderid, Orders.orderdate
FROM Sales.Customers AS C
INNER JOIN Sales.Orders AS O
ON Customers.custid = Orders.custid;
Advanced Tasks:
• Return customers and their orders, including customers who placed no orders (CustomerID, OrderID,
OrderDate)
• Report only those customer IDs who never placed any order. (CustomerID, OrderID, OrderDate)
• Report those customers who placed orders on July,1997. (CustomerID, OrderID, OrderDate)
• Report the total orders of each customer. (customerID, totalorders)
• Write a query to generate a five copies of each employee. (EmployeeID, FirstName, LastName)
• Write a query that returns a row for each employee and day in the range 04-07-1996 through 04-08-
1997. (EmployeeID, Date)
• Return US customers, and for each customer return the total number of orders and total quantities.
(CustomerID, Totalorders, totalquantity)
• Write a query that returns all customers in the output, but matches them with their respective orders
only if they were placed on July 04,1997. (CustomerID, CompanyName, OrderID, Orderdate)
• Are there any employees who are older than their managers?
• List that names of those employees and their ages. (EmployeeName, Age, Manager Age)
• List the names of products which were ordered on 8th August 1997. (ProductName, OrderDate)
• List the addresses, cities, countries of all orders which were serviced by Anne and were shipped late.
(Address, City, Country)
• List all countries to which beverages have been shipped. (Country)

HackerRank Tasks:
All remaining queries of easy module.
What to Submit:
You are required to submit the following files.
• .SQL file
• Notepad file
• Pdf report.

Page 2 of 2

You might also like