Q.1..Write a sql statements to do the following:

Write a query inner joining the SalesOrderDetail and Product tables. The tables are aliased with the following: SOD for SalesOrderDetail and P for Product. Return fields from Product table: Name, ListPrice, Size, ModifiedDate. Return fields from SalesOrderDetail: UnitPrice,OrderQty

Q.2: 

 

Write a ql statements to do the following:

 Using same join above but modify to only show UnitPrice from the SalesOrderDetail table greater than 1000

Q.3: 

 

Write sql statements to do the following

Modify query above and create new field with alias name “SalesOrderCount” which counts the number of SalesOrderDetail records for each Product from the SalesOrderDetail table. Only return the Product Name and Product Number including the new derived column. No Where clause condition. Hint: Group by Name and ProductNumber from Products table.

Q.4: 

Write sql statements to do the following

Modify query above and create new field with alias name “SalesOrderQuantity” which sums the quantity of orders for each Product from the SalesOrderDetail table. Only return the Product Name and Product Number and the new derived column. Do not use WHERE clause condition.

Hint: Group by Name and ProductNumber from Products table.

Q.5: 

Write sql statements to do the following:

Write a query that LEFT JOIN Contact on SalesOrderHeader table which will return all Contacts and any orders they might have. Return Contact LastName from Contact table and SalesOrderID from SalesOrderHeader table

Q.6:

Write sql statements to do the following:

Write a query that LEFT JOINS matching data between the Contact and Employee tables. Return fields from Contact table: FirstName, LastName. Return fields from Employee: MaritalStatus and HireDate. Only return record where EmployeeID = 1

Q7:

Write sql statements to do the following:

Write SELF JOIN on Employee table retrieve a list of employees and their managers. Give alias table name to first Employee table as e1 and second Employee table as e2.

Return the following columns: e1.EmployeeID as Employee, e1.Title as EmployeeTtitle, e2.EmployeeID as ManagerID, and e2.Title as ManagerTitle. 

Q.8:

Write sql statements to do the following:

Write query UNION to return Empoyees who’s hire date is equal or less then ‘1998-02-06’ or ContactID equals 1209,1030, or 1002. Return the following fields: EmplyeeID, ManagerID, Title and HireDate. Order by EmployeeID. 

Q.9:

Write sql statements to do the following:

Write query that returns a list of all the customers and the SalesOrderID for the orders that have been placed. Use the Customer and SalesOrderheader tables.

Return following columns:

Customer table: CustomerID

SalesOrderHeader table: SalesOrderID 

Hint: Use LEFT JOIN to get results and WHERE clause to filter customers who have placed order

Q.10:

Write sql statements to do the following:

Modify previous query to return a list of all the customers that have not placed an order. 

Hint: Use LEFT JOIN to get results and WHERE clause to filter customers who have not placed order