Scenario 9

Directions

This scenario uses the Certadv.Salesstaff data set. Write an SQL query that does the following:
  • Create a report that displays the total salary and total count for each unique JobTitle.
    • Use a SAS function to concatenate a string that combines the constant text 'Total Paid to All' with the value of JobTitle and the constant text 'Staff'. For example, the string should be displayed in the output as 'Total Paid to All Sales Rep. I Staff'.
    • Find the total salary for each unique JobTitle. Use the DOLLAR14. format and a label of TotalSalary.
    • Use a function to count the total number of sales representatives in each unique JobTitle. Use the COMMA14. format and a label of Total.
    • Summarize the data in the Certadv.Salesstaff data set for those rows that have a unique JobTitle.

Test Your Code

  1. What is the total number of Sales Rep. III staff?
  2. What is the total paid to all Sales Rep. II staff?

Exam Objective

Generate summary reports by working with a single table, joining tables, or using set operators in SQL.
Last updated: October 16, 2019
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset