AdwentureWorks2012 database creation instructions Mock test tasks

SQL Mock test	2021

Mock test

1. 
   AdwentureWorks2012 database creation instructions
   
2. 
   Mock test tasks
   

1. AdwentureWorks2012 database creation instructions

1. 
   Download from the Intranet file AdventureWorks2012.txt
   
2. 
   Rename AdventureWorks2012.txt to AdventureWorks2012.bak
   
3. 
   Open SQL Server Management Studio and select “Restore Database …” right click menu option
   

4. 
   In the opened window select option Device and press the button as specified on the screenshot below:
   

5. 
   In the opened dialog locate downloaded AdventureWorks2012.bak file and select it.
   

6. 
   Then press “OK” button as shown below
   

7. 
   If everything goes fine you should see the following message:
   

8. 
   Now you need to press Refresh button in Object Explorer window and you should see database: AdventureWorks2012
   

9. 
   Execute SQL: use [AdventureWorks2012] EXEC sp_changedbowner 'sa'
   

ALTER AUTHORIZATION ON DATABASE::AdventureWorks2012 TO sa;

2. Mock test tasks

1. 
   Count how many different JobTitles exist among employees. (Consider using: HumanResources.Employee)
   
2. 
   Retrieve all information from Product table regarding all products not containing Metal in their names. (Consider using: Production.Product)
   
3. 
   Show JobTitle, HireDate, FirstName, LastName of those employees who were hired before first of January 2002. Order records by HireDate. (Consider using: HumanResources.Employee, person.Person)
   
4. 
   Display all product names and corresponding culture names. (Consider using: Production.Product, production.ProductModelProductDescriptionCulture, Production.Culture)
   
5. 
   Show JobTitle, BirthDate, Age of Employee, FirstName, LastName of those Employees who are older than 60. Order records by years. (Consider using: HumanResources.Employee, person.Person, DATEDIFF() function)
   
6. 
   Show Rates of Employees’ salary and corresponding JobTitles when Rate is lower than average rate. (Consider using: HumanResources.EmployeePayHistory, HumanResources.Employee)
   
7. 
   Show the number of products per subcategory sorted in descending order only for those subcategories, which have more than 20 products. (Consider using: Production.Product, Production.ProductSubcategory)
   

8. 
   Create views on any two queries and comment whether they are updatable or not.
   

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. (Consider using: Person.Person, HumanResources.EmployeePayHistory, HumanResources.EmployeeDepartmentHistory, HumanResources.Department)
   

10. 
    Retrieve the BusinessEntityID, LoginID, JobTitle for those employees who are job candidates as well. (Use tables: HumanResources.Employee, HumanResources.JobCandidate)