Prepare and execute each of the queries listed using the \"Adventureworks2012\"
ID: 3762129 • Letter: P
Question
Prepare and execute each of the queries listed using the "Adventureworks2012" database in SQL:
Server.http://msftdbprodsamples.codeplex.com/releases/view/93587
When all of your queries are complete, cut and paste the SQL Syntax into a word document. In order to see what the column names are, you need to click on the table and then Columns to see the field names. Make sure to include column headings that make sense in the queries (use the as “Field Name” after the field selected).
Single-table queries.
1. List the FirstName, LastName, and Title of all employees in the Adventureworks2012 database. Sort by last name – you should get 19,972 records.
2. Select the sales for each Sales Person, from the Sales.Store table where the sales person ID is equal to 279, 281, or 288). Sort by the Sales Person ID, and then by the Business Name (make sure to list Sales Person ID, and Store Name). You should get 199 records.
3. For each Vendor with a credit rating of 2 or higher, list the Vendors Name, Account Number, and Credit Rating. Sort by the credit rating (descending), and then vendor name. You should get 20 records.
4. List the Sales Tax Rate, State Province ID, Tax Type, and Name for all sales tax rates of 7.50 or higher, and with a state province ID greater than 10. Put the list in order of tax rate and then company name. You should get 9 records.
5. List the Business ID, Pay Rate Change Date, Pay Rate, and Pay Frequency for all businesses with a pay rate greater than 10.00 and a pay frequency of 2. Put the list in order by rate, then by the rate change date. You should get 130 records.
6. Write a query to answer the following question. How many employees (from the HumanResources.Employee table, have an organizational level equal to three (3)? You should get 66 records.
7. Write a query to answer the following question. How many parts from the Production.BillOfMaterials table have a ComponentID equal to or greater than 500, and less than or equal to 525. Sort by the component ID and then by the product assembly ID. List the Product Assembly ID, the Component ID, the BOM Level, Start date, and Modified Date. You should get 145 records.
8. Write a query using the sales.salesterritory table that lists all territories that have current year to date sales greater than last year sales. Sort by sales ytd. List the territory ID, Country region code, year to date sales, and last year sales. You should get 7 records.
9. Write a query to determine which SuperiorCard credit cards have an expiration year of 2005 or earlier. List the credit card ID, card type, the expiration month and year, sorted by expiration month and year, descending. (Use the Sales.CreditCard table). You should bet 1,201 records.
10. Write a query to find the average unit price for all sales from the Sales.SalesOrderDetail table. You will only have 1 record which contains the overall average unit price.
Explanation / Answer
1.
Select FirstName as "First Name", LastName as "Last Name" , Title as Title from AdventureTable order by LastName;
2.
Select sales as "Sales per person", SalesPersonsID as "Sales Persons ID", StoreName as "Store Name"
from Store where salesPersonID in (279, 281, 288) group by (SalesPersonID, BusinessName);
3.
Select VendorName as "Vendor Name", AccountNumber as "Account Number", CreditRating as "Credit Rating" where
CreditRating >= 2 group by CreditRating Desc, VendorName;
4.
Select SalesTaxRate as "Rate of Sales Tax", StateProvinceID as of the "State Province", TaxType as "Type of Tax", CompanyName as "Name of the Company"
from SalesTable where SalesTaxRate >= 7.5 and StateProvinceID >=10 order by alesTaxRate, CompanyName;
5.
Select BusinessID as "Identification of the Business", PayRateChangeDate as "Pay Rate Change Date", PayRate as "Pay Rate",
PayFrequency as "Pay Frequency" where PayRate > 10.00 and PayFrequency = 2 order by PayRate, PayRateChangeDate;
6.
use HumanResources; -- change database to Human Resources
Select count(EmployeeName) from EmployeeTable where organizationLevel = 3;
7.
use Production; -- change database (DB) to Production DB
Select count(ComponentID), productAssembleID as "Product Assemble ID" , componentID as "Component ID",
BOMLevel as "Bill Of Material Level", StartDate as "Start Date", ModifiedDate as "Modified Date"
from BillOfMaterialsTable where componentID >= 500 and componentID <= 525
order by componentID, productAssembleID;
9.
use Sales;
select cardNumber, cardID, cardType, expirationMonth, expirationYear from CreditCardTable
where cardType = SuperiorCard and expirationYear <= 2005 order by expirationMonth, expirationYear descending;
10.
use Sales;
select average(unitPrice) from SalesOrderDetailTable ;