[Solved] SQL Lab Tutorial

Lab Objective

This lab will allow you to use SQL Server Management Studio to create 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).
• AdventureWorks database file
• 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 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.

4. Query 1

**** select ALL fields

Select *

from HumanResources.Employee

Insert screenshot below:

5. Query 2

***Note ManagerID is NULL***

select EmployeeID, ContactID, ManagerID, HireDate, SalariedFlag

from HumanResources.Employee

where EmployeeID = 109

Insert screenshot below:

6. Query 3

*** Use like to search for data that matches pattern

**** % is wild card in SQL Server ****and specifies that both conditions must be true

select FirstName, LastName, Phone

from Person.Contact

where LastName like Mo%

and FirstName like G%

order by LastName

Insert screenshot below:

7. Query 4

**** Perform calculations in Select clause

select ProductID, OrderQty, UnitPrice, OrderQty*UnitPrice as [Extended Price], LineTotal

from Sales.SalesOrderDetail

where OrderQty*UnitPrice > 20000

Insert screenshot below:

8. Query 5

**** select specific row(s)

select EmployeeID, ContactID, ManagerID, HireDate, SalariedFlag

from HumanResources.Employee

where EmployeeID = 108

Insert screenshot below:

9. Query 6

**** compare dates

select EmployeeID, ContactID, ManagerID, HireDate, SalariedFlag

from HumanResources.Employee

where HireDate > 2002-01-01

Insert screenshot below:

• Now submit the file with your screenshots to the dropbox.