Ask PL-SQL Expert

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

PL-SQL, Programming

  • Category:- PL-SQL
  • Reference No.:- M91923461

Have any Question?


Related Questions in PL-SQL

Complete the following tasksin microsoft access create the

Complete the following tasks: In Microsoft Access, create the database and tables that you identified in W3 Assignment 2. In Microsoft Word, write the SQL statements to create the database and tables. Write SQL statement ...

Purpose of the assessment with ulo mapping the purpose of

Purpose of the assessment (with ULO Mapping) The purpose of this assignment is to develop skills in managing data in databases and to gain understanding of data model development and implementation using a commercially a ...

Continuing the project you have worked on in weeks 1-4 in

Continuing the project you have worked on in Weeks 1-4, in this final week, complete the following tasks: Refine your database and SQL statements by incorporating your instructor's feedback. Verify that the database comp ...

For this assignment you will be provided a database backup

For this assignment, you will be provided a database backup for a database called FinanceDB. You will have to restore this backup to your own version of SQL Server. All of the questions in this assignment relate to the F ...

Assignment - queries functions and triggersaimthe aims of

Assignment - Queries, Functions and Triggers Aim The aims of this assignment are to: formulate SQL queries; populate an RDBMS with a real dataset, and analyse the data; design test data for testing SQL queries; create SQ ...

  • 4,153,160 Questions Asked
  • 13,132 Experts
  • 2,558,936 Questions Answered

Ask Experts for help!!

Looking for Assignment Help?

Start excelling in your Courses, Get help with Assignment

Write us your full requirement for evaluation and you will receive response within 20 minutes turnaround time.

Ask Now Help with Problems, Get a Best Answer

Why might a bank avoid the use of interest rate swaps even

Why might a bank avoid the use of interest rate swaps, even when the institution is exposed to significant interest rate

Describe the difference between zero coupon bonds and

Describe the difference between zero coupon bonds and coupon bonds. Under what conditions will a coupon bond sell at a p

Compute the present value of an annuity of 880 per year

Compute the present value of an annuity of $ 880 per year for 16 years, given a discount rate of 6 percent per annum. As

Compute the present value of an 1150 payment made in ten

Compute the present value of an $1,150 payment made in ten years when the discount rate is 12 percent. (Do not round int

Compute the present value of an annuity of 699 per year

Compute the present value of an annuity of $ 699 per year for 19 years, given a discount rate of 6 percent per annum. As