Question 7: Find the number and name of each customer that currently has an order on file for a Rocking Horse.
Question 8: List the item number, description, and category for each pair of items that are in the same category. (For example, one such pair would be item CD33 and item DL51, because the category for both items is TOY.) Your results will have 31 rows.
Question 11: Using queries with a UNION, list the order number and order date for each order that either was placed by Almondton General Store or that contains an order line for a Fire Engine.
Question 12: Repeat #11 using a JOIN
Question 14: List the order number and order date for each order that was placed by Almondton General Store and that does not contain an order line for a Fire Engine.
Question 15: List all employees with their ID, last name, first name, job title, and date of birth. Sort the list by their last name, then their first name.
Question 16: How many people work in each department? To answer this you will use the following tables and JOIN them: HumanResources.Department HumanResources.EmployeeDepartmentHistory
Look at the HumanResources.EmployeeDepartmentHistory table first. How do you know which department someone is currently in? (Look at StartDate and EndDate.) Start working by writing and executing a query that will give you a list of employees and their current department. HINT: This should give you the same number of total rows that you get when selecting all the rows from HumanResources.Employee.
After you have a good list, modify this to get a count for each department just using the HumanResources.EmployeeDepartmentHistory table. This should give you 16 rows. Then join the HumanResources.Department table to get the department name.
In your result, show the department ID, the department name (Name), and the number of employees. This should be in alphabetical order by the department name.
TAL Database Diagram REP 8 REP_NUM LAST_NAME FIRST_NAME CUSTOMER CUSTOMER_NUM CUSTOMER_NAME STREET STREET CITY CITY STATE STATE POSTAL CODE POSTAL CODE BALANCE COMMISSION CREDIT_LIMIT REP_NUM go RATE ORDERS 9 ORDER_NUM ORDER_DATE CUSTOMER_NUM co ORDER_LINE ORDER_NUM 8 ITEM_NUM NUM_ORDERED QUOTED_PRICE ITEM 8 ITEM_NUM DESCRIPTION ON_HAND CATEGORY STOREHOUSE PRICE JobCandidate (Human Resources) 8 JobCandidatels BusinessEntityID Resume ModifiedDate EmployeePayHistory (Human Resources) 8 Business EntityID RateChangeDate Employee (Human Resource: 8 Business EntityID NationalIDNumber Rate PayFrequency ModifiedDate LoginID OrganizationNode OrganizationLevel Job Title BirthDate MaritalStatus Gender Hire Date Salaried Flag Vacation Hours SickLeave Hours CurrentFlag rowguid ModifiedDate 1-1 Phone NumberType (Person) PhoneNumberTypeID Name Modified Date PersonPhone (Person) Business EntityID 8 Phone Number Phone NumberTypeID ModifiedDate Password (Person) Business EntityID Password Hash PasswordSalt rowguid Modified Date Person (Person) Business EntityID PersonType NameStyle Title mo 10 First Name Business Entity (Person) Business EntityID rowguid ModifiedDate Middle Name Last Name Suffix EmailPromotion Additional Contactinfo Demographics rowguid Modified Date Business EntityContact (Person) Business EntityID 8 PersonlD 8 ContactTypeID rowguid Modified Date Email Address (Person) 8 Business EntityID 9 EmailAddressID EmailAddress rowguid Modified Date 1-2 EmployeeDepartment Histor 9 Business EntityID 8 DepartmentID 9 ShiftID Sales Person (Sales) 7 Business EntityID TerritoryID Sales Quota StartDate Bonus End Date CommissionPct ModifiedDate Sales YTD SalesLast Year rowguid Modified Date Department (Human Resources) 8 DepartmentID Name GroupName Modified Date Address (Person) Y AddressID AddressLine1 AddressLine2 City State ProvincelD State Province (Person) Y StateProvincel State ProvinceCode CountryRegionCode IsOnlyStateProvince Flag Name TerritoryID rowguid PostalCode SpatialLocation rowguid ModifiedDate ModifiedDate Country Region (Person) 8 Country Region Code Name ModifiedDate 2-1 Contact Type (Person) ContactTypeID Name Modified Date Business Entity Address (Person) 9 Business EntityID AddressID AddressTypeID rowguid ModifiedDate Address Type (Person) AddressTypeID Name rowguid Modified Date TAL Database Diagram REP 8 REP_NUM LAST_NAME FIRST_NAME CUSTOMER CUSTOMER_NUM CUSTOMER_NAME STREET STREET CITY CITY STATE STATE POSTAL CODE POSTAL CODE BALANCE COMMISSION CREDIT_LIMIT REP_NUM go RATE ORDERS 9 ORDER_NUM ORDER_DATE CUSTOMER_NUM co ORDER_LINE ORDER_NUM 8 ITEM_NUM NUM_ORDERED QUOTED_PRICE ITEM 8 ITEM_NUM DESCRIPTION ON_HAND CATEGORY STOREHOUSE PRICE JobCandidate (Human Resources) 8 JobCandidatels BusinessEntityID Resume ModifiedDate EmployeePayHistory (Human Resources) 8 Business EntityID RateChangeDate Employee (Human Resource: 8 Business EntityID NationalIDNumber Rate PayFrequency ModifiedDate LoginID OrganizationNode OrganizationLevel Job Title BirthDate MaritalStatus Gender Hire Date Salaried Flag Vacation Hours SickLeave Hours CurrentFlag rowguid ModifiedDate 1-1 Phone NumberType (Person) PhoneNumberTypeID Name Modified Date PersonPhone (Person) Business EntityID 8 Phone Number Phone NumberTypeID ModifiedDate Password (Person) Business EntityID Password Hash PasswordSalt rowguid Modified Date Person (Person) Business EntityID PersonType NameStyle Title mo 10 First Name Business Entity (Person) Business EntityID rowguid ModifiedDate Middle Name Last Name Suffix EmailPromotion Additional Contactinfo Demographics rowguid Modified Date Business EntityContact (Person) Business EntityID 8 PersonlD 8 ContactTypeID rowguid Modified Date Email Address (Person) 8 Business EntityID 9 EmailAddressID EmailAddress rowguid Modified Date 1-2 EmployeeDepartment Histor 9 Business EntityID 8 DepartmentID 9 ShiftID Sales Person (Sales) 7 Business EntityID TerritoryID Sales Quota StartDate Bonus End Date CommissionPct ModifiedDate Sales YTD SalesLast Year rowguid Modified Date Department (Human Resources) 8 DepartmentID Name GroupName Modified Date Address (Person) Y AddressID AddressLine1 AddressLine2 City State ProvincelD State Province (Person) Y StateProvincel State ProvinceCode CountryRegionCode IsOnlyStateProvince Flag Name TerritoryID rowguid PostalCode SpatialLocation rowguid ModifiedDate ModifiedDate Country Region (Person) 8 Country Region Code Name ModifiedDate 2-1 Contact Type (Person) ContactTypeID Name Modified Date Business Entity Address (Person) 9 Business EntityID AddressID AddressTypeID rowguid ModifiedDate Address Type (Person) AddressTypeID Name rowguid Modified Date
