Scenario 10
Directions
This scenario uses the
Certadv.EmpAdd, Certadv.EmpOrg, and Certadv.EmpPh data sets. Write
an SQL query that does the following:
-
Create a view named Work.PhoneList that contains the following columns:
-
Department with a format of $25.
-
EmployeeName with a format of $25.
-
PhoneNumber with a format of $16. and a label of Home Phone
-
-
Use the data found in the following tables. Column names that you need from each data set are in parentheses.
-
certadv.empadd (EmployeeID, EmployeeName)
-
certadv.emporg (EmployeeID, Department)
-
certadv.empph (EmployeeID, PhoneNumber, PhoneType)
-
Use an alias for each table for simpler programming. Use 'a' for certadv.empadd, 'o' for certadv.emporg, and 'p' for certadv.empph.
-
-
Include only those phone numbers in the view where the PhoneType was
Homeand where EmployeeID matches across all three tables. -
Use the Work.PhoneList view to create an SQL query:
-
The query must contain only the EmployeeName and PhoneNumber columns.
-
Subset the data to include only those employees who are in the Sales Management department.
-
Order the query by EmployeeName.
-
Add a title to the report: Sales Management Department Home Phone Numbers.
-
Display row numbers for the report.
-
Test Your Code
-
How many observations are displayed in the query result?
-
What is the value for Name in observation 6?
Exam Objective
Construct in-line views
within an SQL procedure step.
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.