Description
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
- Click start, navigate to all programs and select Microsoft SQL Server 2008, and then click on SQL Server Management Studio.
- Attach the AdventureWorks database to SQL Server 2008.
- You will now create the following queries using Microsoft SQL Server 2008. For each query create a screenshot to show your results.
- Query 1 –
**** select ALL fields
Select *
from HumanResources.Employee
Insert screenshot below:
- Query 2–
***Note ManagerID is NULL***
select EmployeeID, ContactID, ManagerID, HireDate, SalariedFlag
from HumanResources.Employee
where EmployeeID = 109
Insert screenshot below:
- 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:
- 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:
- Query 5–
**** select specific row(s)
select EmployeeID, ContactID, ManagerID, HireDate, SalariedFlag
from HumanResources.Employee
where EmployeeID = 108
Insert screenshot below:
- 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.
