Assignment 2 Questions -SP2-2019 PDF

Preview

Summary

Database Fundamentals Assignment 2 2019 SP2 Instructions: 1. Download the Adventure Works database from the Assignments tab of the course website and attach it to your SQL Server (as per practicals) 2. Use the provided UML/Database diagram of the Adventure works database or create your own database ...

Description

Database Fundamentals Assignment 2 2019 SP2 Instructions: 1. Download the Adventure Works database from the Assignments tab of the course website and attach it to your SQL Server (as per practicals) 2. Use the provided UML/Database diagram of the Adventure works database or create your own database diagrams to help you answer the following questions. Note that the diagram is from 2008 and has some minor differences. To find out information about specific table attributes/columns either right click on them in the database diagram to view the properties window, or open the table up in the design view and select the column to view the properties. Each column (and FK relationship) has a description that tells you the purpose of the data if you need assistance. You can also consult the schema library. 3. Write your queries to the questions below and paste your final SQL query into the corresponding .SQL file along with your username. Once you have completed all your files, zip them into a single folder and name using your university network/username (e.g. wazza001). 4. DUE DATE: 2nd June @11:55 pm

--Q1001-Write a query to display the First and Last name of all employees that currently work in the production department as part of the manufacturing group who started work before the end of 2002 select FirstName, LastName from AdventureWorksDB.Person.Person inner join AdventureWorksDB.HumanResources.Employee on Person.BusinessEntityID = Employee.BusinessEntityID inner join AdventureWorksDB.HumanResources.EmployeeDepartmentHistory on Employee.BusinessEntityID = EmployeeDepartmentHistory.BusinessEntityID where Employee.HireDate < '2003' and EmployeeDepartmentHistory.DepartmentID = '7'

--Q1002-Write a query to display the name of stores, cities and countries of all stores worldwide except stores located in the United States, United Kingdom or Canada - these countries are blacklisted select Store.Name, Address.City, CountryRegion.Name from Sales.Store inner join Person.BusinessEntityAddress on Store.BusinessEntityID = BusinessEntityAddress.BusinessEntityID inner join Person.Address on BusinessEntityAddress.AddressID = Address.AddressID inner join Person.StateProvince on cast(Address.StateProvinceID as nvarchar)= cast(StateProvince.StateProvinceCode as nvarchar) inner join person.CountryRegion on StateProvince.CountryRegionCode = CountryRegion.CountryRegionCode where BusinessEntityAddress.AddressTypeID = '3' and CountryRegion.Name not in ('United States', 'United Kingdom', 'Canada')

--Q1003- Products come in various colours. List the colours of the various products and the number of products for each of those colours if there are at least 30 items that came in that colour. Where the colour is not specified, display N/A SELECT ISNULL(pro.Color, 'N/A') AS Color, COUNT (Distinct pro.ProductID) AS Count_Of_Products FROM Production.Product pro GROUP BY pro.Color HAVING COUNT(Distinct pro.ProductID) >= 30 order by Count_Of_Products desc

--Q1004-Find the First and Last Name of employees who have the same First name but different Last Name to that of customers (no duplicates) SELECT DISTINCT per.FirstName, per.LastName FROM Person.Person per inner join HumanResources.Employee e on e.BusinessEntityID = per.BusinessEntityID AND per.FirstName IN ( SELECT p.FirstName FROM Person.Person p inner join Sales.Customer cus on cus.PersonID = p.BusinessEntityID ) AND per.LastName NOT IN ( SELECT p.LastName FROM Person.Person p inner join Sales.Customer cus on cus.PersonID = p.BusinessEntityID

--Q1005 - List the ProductModel name and Name for all Customers who ordered a product which was the most recent to start selling. No duplicate records! --Q1006 - Find the First and Last name as well as the bonus amount of all sales people who have a bonus less than the average bonus of salespersons in the German territory. SELECT per.FirstName , per.LastName , sal.Bonus FROM Person.Person per inner join Sales.SalesPerson sal on sal.BusinessEntityID = per.BusinessEntityID AND sal.Bonus < ( SELECT AVG(s.Bonus) FROM Sales.SalesPerson s inner join Sales.SalesTerritory t on s.TerritoryID = t.TerritoryID AND t.Name = 'Germany' );

--Q1007-Find the most common job title(s) and the number of employees with that title select a.JobTitle ,a.NumOfEmployees from ( SELECT e.JobTitle JobTitle , COUNT(e.BusinessEntityID) AS NumOfEmployees FROM HumanResources.Employee e GROUP BY e.JobTitle) a where a.NumOfEmployees = (select max(max_cou.NumOfEmployees) from ( SELECT (COUNT(e.BusinessEntityID)) AS NumOfEmployees FROM HumanResources.Employee e GROUP BY e.JobTitle ) max_cou)

--Q1008 - Find the name of products that have illustrations (no duplicates) SELECT DISTINCT p.Name FROM Production.Product p inner join Production.ProductModelIllustration i on p.ProductModelID = i.ProductModelID;

--Q1009 Find the second highest pay rate of all employees and the number of employees on this pay rate select t.rate , num.NumberOfEmployees from (select p.rate rate , rank() over (order by p.rate desc)as ran from HumanResources.EmployeePayHistory p) t inner join ( select rate , count(BusinessEntityID) NumberOfEmployees from HumanResources.EmployeePayHistory group by rate ) num on t.rate = num.rate and t.ran = 2

--Q1010- Find the month, year and number of employees hired for the month and year in which the most employees were hired in the second half of the year. select YEAR(HireDate) year_hire , MONTH(HireDate) month_hire , COUNT(BusinessEntityID) NumberOfEmployees from HumanResources.Employee

group by YEAR(HireDate), MONTH(HireDate)...