Reference no: EM131168048
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
Construct an estimator of the mean and median
: You are interested in purchasing a home in Santa Barbara. To determine if it is a good time to buy, you collect the selling prices of all homes in the city over the past three years, giving you 497 observations. Let us denote observation 't' as P..
|
Describe such circumstances whether true or false
: The following two sentences were devised by the logician Saul Kripke. While not intrinsically paradoxical, they could be paradoxical under certain circumstances. Describe such circumstances.
|
Create a table that includes a rotating schedule
: Create a table that includes a rotating schedule for the 12 months of security testing. Include columns that identify time estimations for each test listed.
|
Confidence interval estimate of the population mean
: In Calculating a 95% confidence interval estimate of the population mean based on your sample of 100 which yielded a sample mean of 110.27 and a sample standard of 28.95, What "t" value will you use in your equation?
|
Create advanced queries in a database
: 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.
|
Which presidential and parliamentary democracy differ
: Presidential democracies lack party control while parliamentary democracies have strong party control. Now that I have described the four keys differences, explain what some of them mean.
|
Standard deviation greater
: Based on the sample results, the researcher concludes that the pulse rates of men have a standard deviation greater than 10 beats per minutes. Use a 0.05 significance level to test the researcher's claim.
|
Explain how it leads to a contradiction
: Let n be "the smallest integer not describable in fewer than 12 English words." (Note that the total number of strings consisting of 11 or fewer English words is finite.)
|
Different model forms
: Total marketing effort is a term used to describe the critical decision factors that affect demand: price, advertising, distribution, and product quality. Let the variable x represent total marketing effort. A typical model that is used to predict..
|