NOTE: In answering some of the following questions you will need to use T-SQL statements learned under previous learning lessons.
Questions
1. Create a VIEW dbo.vw_Commissions to display the commissions earned last year by all sales employees. Round the result set to two decimal places and do
Unit 10 [IT234: Database Foundations]
not include any salesperson who did not earn a commission. Include the salesperson name, the commission earned, and the job title. Concatenate the salesperson first and last names.
2. Using nested queries (correlated query) to display the total Last Year Sales converted in Mexican pesos that occurred in July 1, 2005. Only nested/correlated queries will be accepted. Round the result set to two decimal places Unit10_ConvertedCommissions.sql
HINTS:
• Observe the ToCurrency column and the value corresponding to the Mexican Pesos
• Use a wildcard in the Name of the currency
• Make sure you use the correct convention for the date field
• Use the proper aggregate function
3. Display the Salesperson who earned the highest commission last year. Include the sales person name (concatenated First and Last Name), the Job Title, and the total commissions earned. Name the query Unit10_HighestCommission.sql
4. List all orders placed between August 10 and August 20 in 2005. List the order date, orders and the territory. Name the query Unit10_OrdersPalcedInAugust2005.sql