sql for FLOVODOH

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 or AdventureWorks R2 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.

1. Attach the AdventureWorks database to SQL Server 2008.

1. You will now create the following queries using Microsoft SQL Server 2008. For each query create a screenshot to show your results.

1. Query 1 -

**** select ALL fields

Select *

from HumanResources.Employee

Insert screenshot below:

1. Query 2- (Use the correct query for your database version)

For AdventureWorks

***Note ManagerID is NULL***

select EmployeeID, ContactID, ManagerID, HireDate, SalariedFlag

from HumanResources.Employee

where EmployeeID = 109

For AdventureWorkR2

***Note ManagerID is NULL***

select BusinessEntityID, LoginID, OrganizationNode, HireDate, SalariedFlag

from HumanResources.Employee

where BusinessEntityID = 109

Insert screenshot below:

1. Query 3- - (Use the correct query for your database version)

For AdventureWorks

*** 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

For AdventureWorks R2

*** 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

from Person.Person

where LastName like 'Mo%'

and FirstName like 'G%'

order by LastName

Insert screenshot below:

1. 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:

1. Query 5-(Use the correct query for your database version)

For AdventureWorks

**** select specific row(s)

select EmployeeID, ContactID, ManagerID, HireDate, SalariedFlag

from HumanResources.Employee

where EmployeeID = 108

For AdventureWorks R2

**** select specific row(s)

Select BusinessEntityID, LoginID, OrganizationNode, HireDate, SalariedFlag

from HumanResources.Employee

where BusinessEntityID = 108

Insert screenshot below:

1. Query 6--(Use the correct query for your database version)

For AdventureWorks

**** compare dates

select EmployeeID, ContactID, ManagerID, HireDate, SalariedFlag

from HumanResources.Employee

where HireDate > '2002-01-01'

For AdventureWorks R2

**** compare dates

select BusinessEntityID,LoginID, OrganizationNode, HireDate, SalariedFlag

from HumanResources.Employee

where HireDate > '2002-01-01'

Insert screenshot below:

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