all
You are given an AdventureWorks2012 database file located in MS SQL server. Your task to create the following queries and save them in one file under name YourID_DBSDTest1 on desktop folder DBSD.
Number all your queries.
1. Count how many different jobtitles exist among employees.. [5 marks]
Select count (jobtitle) from HumanResources.Employee
2. Retrieve all information from Product table regarding all products not containing Metal in their names.
Select * from Production.Product
Where name not like '%metal%'
3. Show JobTitle, HireDate, FirstName, LastName of those employees who were hired before first of January 2002. Order records by HireDate.
Select jobtitle, HireDate, pp.FirstName, pp.LastName from HumanResources.Employee he
join person.Person pp on he.BusinessEntityID=pp.BusinessEntityID
where hiredate<'2002-01-01'
order by HireDate
4. Display all product names and corresponding culture names.
Select pp.Name as product, pc.Name as model
from Production.Product pp
join production.ProductModelProductDescriptionCulture pm on pp.ProductModelID=pm.ProductModelID
join Production.Culture pc on pm.CultureID=pc.CultureID
5. Show JobTitle, BirthDate, Age of Employee, FirstName, LastName of those Employees who are older that 60. Order records by years.
Select jobtitle, BirthDate, DATEDIFF(YEAR, BirthDate, GETDATE()) as years, pp.FirstName, pp.LastName from HumanResources.Employee he
join person.Person pp on he.BusinessEntityID=pp.BusinessEntityID
where DATEDIFF(YEAR, BirthDate, GETDATE())>60
order by years
6. Show Rates of Employees’ salary and corresponding JobTitle when Rate is lower than average rate.
select e.JobTitle, rate from HumanResources.EmployeePayHistory eph join HumanResources.Employee e on
eph.BusinessEntityID=e.BusinessEntityID
where rate<(select avg(eph.Rate) from HumanResources.EmployeePayHistory eph)
7. Show the number of products per subcategory sorted in descending order only for those subcategories, which have more than 20 products.
Select count(pp.productID), ps.Name
from Production.Product pp join Production.ProductSubcategory ps
on pp.ProductSubcategoryID=ps.ProductSubcategoryID
group by ps.Name
having count(pp.productID)>20
order by count(pp.productID) desc
8. Create views on any two queries and comment whether they are updatable or not. [10 marks]
9. Show list of employees ordered by department, employee rate and minimum rate for department this employee works at. For each employee show the difference between lowest rate in the department where employee works and his own salary.
Select pp.FirstName, pp.LastName, rate, min(rate) over (partition by hd.name) as [minimum in its department], rate-min(rate) over (partition by hd.name) as difference from Person.Person pp
join HumanResources.EmployeePayHistory hep
on pp.BusinessEntityID=hep.BusinessEntityID
join HumanResources.EmployeeDepartmentHistory hed
on hed.BusinessEntityID=pp.BusinessEntityID
join HumanResources.Department hd
on hd.DepartmentID=hed.DepartmentID
order by hd.Name, rate
10. Retrieve the BusinessEntityID, LoginID, JobTitle for those employees who are job candidates as well. You should use INTERSECT operator to combine the two queries and CTE.
WITH
cteCandidates (BusinessEntityID)
AS
(
SELECT BusinessEntityID
FROM HumanResources.Employee
INTERSECT
SELECT BusinessEntityID
FROM HumanResources.JobCandidate
)
SELECT
c.BusinessEntityID,
e.LoginID,
e.JobTitle
FROM
HumanResources.Employee AS e
INNER JOIN cteCandidates AS c
ON e.BusinessEntityID = c.BusinessEntityID
ORDER BY
c.BusinessEntityID;
11.
CW Assessment Criteria
Component
Weight (marks)
Query 1
5
Count function is used
3
Alias name to new column is given
1
Where clause is written correctly
1
Query 2
5
Like operator is correct
4
Used */all
1
Query 3
10
Join
4
Where
3
Order
2
All required attributes
1
Query 4
10
Join 1
4
Join 2
4
Order
1
All attributes
1
Query 5
10
DATEDIFF correctly used
3
Join
3
Where clause
1
Ordered by year
1
All required attributes
1
Aliases for attributes and tables
1
Query 6
10
Inner query is correct
5
Join
3
All required attributes
1
Aliases for attributes and tables
1
Query 7
10
count
2
Join
2
Having
2
Group
1
Order in descending
1
All required attributes
1
Aliases for attributes and tables
1
Query 8
10
View is created (2)
2
Clear statement on updatability and reasoning (2)
3
Query 9
15
Partition
4
Difference
4
Joins
4
All required attributes
1
Aliases for attributes and tables
1
Ordered by 2 fields
1
Query 10
15
Inner query
4
Intersect
4
Inner join
4
Order
1
All required attributes
1
Aliases for attributes and tables
1