Advanced SQL Lab - Tutorial

Lab Objective

This lab will allow you to use SQL Server Management Studio to create advanced queries in a database. This software allows you to interact with the database directly. This tool allows a Database Administrator to manage and maintain the database.

Required Materials

• SQL Server 2008 (express or full version).
• AdventureWorksdatabase file or AdventureWorksR2 database file
• Advanced SQL Lab - Tutorial (this document)

Lab Steps

1. Click start, navigate to all programs and select Microsoft SQL Server 2008, and then click on SQL Server Management Studio.

2. Attach the AdventureWorks or AdventureWorks R2 database to SQL Server 2008.

3. You will now create the following queries using Microsoft SQL Server 2008. For each query create a screenshot to show your results. Make sure you use the correct version of each query for your database.

4. Query 1- for AdventureWorks
****note error-have group function and row data in
**** same Select clause-requires Group By
select min(Rate), EmployeeID
from HumanResources.EmployeePayHistory
group by EmployeeID

Query 1- for AdventureWorksR2
****note error-have group function and row data in
**** same Select clause-requires Group By
select min(Rate), ID
fromHumanResources.EmployeePayHistory
group by BusinessEntityID

Insert screenshot below:

5. Query 2-for AdventureWorks

*** SubQuery! Only have to run one query...
select EmployeeID, Rate
from HumanResources.EmployeePayHistory
where Rate = (Select min(Rate) from HumanResources.EmployeePayHistory)

Query 2- for AdventureWorks R2
*** SubQuery! Only have to run one query...
selectBusinessEntityID, Rate
fromHumanResources.EmployeePayHistory
where Rate = (Select min(Rate) from HumanResources.EmployeePayHistory)

Insert screenshot below:

6. Query 3- for either database

****execute query with min, max in place of avg
select avg(Rate)
from HumanResources.EmployeePayHistory

Insert screenshot below:

7. Query 4- for AdventureWorks

**** only display first 3 from results list
***enter ASC to see top 3 lowest paid employees!
select top 3 Rate, EmployeeID
from HumanResources.EmployeePayHistory
Order by Rate Desc

Query 4-for AdventureWorksR2
**** only display first 3 from results list
***enter ASC to see top 3 lowest paid employees!
select top 3 Rate, BusinessEntityID
fromHumanResources.EmployeePayHistory
Order by Rate Desc

Insert screenshot below:

8. Query 5-for AdventureWorks

****Note Alias. INNER JOIN returns only those rows that have matches in ***both tables.
SELECT Person.Contact.FirstName + ' ' + Person.Contact.LastName AS [Full Name] FROM Person.Contact INNER JOIN HumanResources.Employee ON Person.Contact.ContactID = HumanResources.Employee.ContactID

Query 5- for AdventureWorksR2
****Note Alias. INNER JOIN returns only those rows that have matches in ***both tables.
SELECT Person.Person.FirstName + ' ' + Person.Person.LastName AS [Full Name] FROM Person.Person INNER JOIN HumanResources.Employee
ON Person.Person.BusinessEntityID = HumanResources.Employee.BusinessEntityID

Insert screenshot below:

9. Query 6-for AdventureWorks
***When need data from 3 tables, need 2 sets of join conditions. Need to ***join first two tables and join result to third table.
SELECT HumanResources.Employee.EmployeeID, Person.Contact.FirstName, Person.Contact.LastName, Person.Contact.Phone, HumanResources.EmployeePayHistory.Rate FROM Person.Contact INNER JOIN HumanResources.Employee ON Person.Contact.ContactID = HumanResources.Employee.ContactID INNER JOIN HumanResources.EmployeePayHistory ON HumanResources.Employee.EmployeeID = HumanResources.EmployeePayHistory.EmployeeID WHERE HumanResources.EmployeePayHistory.Rate > 50 ORDER BY HumanResources.EmployeePayHistory.Rate DESC

Query 6-for AdventureWorksR2
***When need data from 3 tables, need 2 sets of join conditions. Need to ***join first two tables and join result to third table.
SELECTHumanResources.Employee.BusinessEntityID, Person.Person.FirstName, Person.Person.LastName, HumanResources.EmployeePayHistory.Rate
FROM Person.Person
INNER JOIN HumanResources.Employee
ON Person.Person.BusinessEntityID= HumanResources.Employee.BusinessEntityID
INNER JOIN HumanResources.EmployeePayHistory ON HumanResources.Employee.BusinessEntityID = HumanResources.EmployeePayHistory.BusinessEntityID WHERE HumanResources.EmployeePayHistory.Rate> 50 ORDER BY HumanResources.EmployeePayHistory.Rate DESC

Attachment:- SQL-Assignment.rar