Scenario 8

Directions

This scenario uses the Certadv.Empdata and Certadv.Newsals data sets. Write an SQL query that does the following:
  • Create a temporary table Work.Raise.
  • Include the following variables in your report: EmpID, LastName, Salary, NewSalary, and Raise.
  • Join both Certadv.Empdata and Certadv.Newsals based on the EmpID column to display only matching rows of the data.
  • Create a new column named Raise whose value is the difference between NewSalary and Salary. Format the column as DOLLAR10.2
  • Display only the rows where the value of Raise is greater than 3000 and order the query by the EmpID.
  • Include the row numbers in your report.

Test Your Code

  1. What is the employee’s last name where the value of Raise is $5,141.86?
  2. What is the value of Raise for employee ID number E0028 and last name of Lichtenstein?

Exam Objective

Generate detailed reports by working with a single, 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