Lab05 and Lab06
ieas2010
lab_5.rtfPage 1 of 12
Delete this text and type your name here
Lab 5 : 40 Total Points Possible
You will need to log into Apex at https://iacademy.oracle.com " https://iacademy.oracle.com in order to complete this assignment.
Sections 5 - 7 Programming with SQL
Section 5 Objectives : Using Complex SQL and Aggregate Data
Use GROUP BY and HAVING Clauses
Vocabulary :
Directions: Identify the vocabulary word for each definition below. (1 Point each)
1. Used to specify which groups are to be displayed; restricts groups that do not meet group criteria.
Answer:
2. Divides the rows in a table into groups.
Answer:
3. Operator that returns all rows from both tables and eliminates duplicates.
Answer:
Try It / Solve It :
In order to be successful with this lab, y ou must review the ‘Oracle Student Tables’ file located under ‘Videos and Resources’ (multiple tabs across bottom of worksheet). This file will help you to understand the data that is being manipulated in the SQL statements .
I am providing a ‘copy’ (first 2 0 rows) of the ‘Results from Apex’ for each question. Hopefully, this will help you see if you are on track with your results. At the top of y our SQ L window, you can increase the number of rows displayed. You will provide the SQL statement you used to produce shown results AND you will provide a screen shot of your results. In order to receive points, your screenshot MUST include your workspace number.
1. Create a query that will return the average of the maximum employee salaries in each department. You will be gathering data from the employees table. Execute the query in Oracle Application Express (Apex). (2 Points)
Select Statement You Used :
My example of correct results from Apex (copy) : YOU MUST DELETE MY SCREEN SHOT BELOW AND INSERT YOUR OWN. YOUR SCREEN SHO T MUST SHOW YOUR WORSPACE AND BE CORRECT RESULTS IN ORDER TO RECEIVE CREDIT. (1 Point)
2. Create a query that will display the employee_id and department_id from the employees table and a second query displaying employee_id, job_id, start_date, and department_id from the job_history table. You will combine the results as one single output. Make sure you suppress duplicates in the output. Execute the query in Oracle Application Express (Apex). (2 Points)
Select Statement You Used :
My example of correct results from Apex (copy) : YOU MUST DELETE MY SCREEN SHOT BELOW AND INSERT YOUR OWN. YOUR SCREEN SHO T MUST SHOW YOUR WORSPACE AND BE CORRECT RESULTS IN ORDER TO RECEIVE CREDIT. (1 Point)
3. Create a query that will display an employee's department_id, manager_id, and job_id as well as the SUM of salaries. You are gathering data from the employees table. Use Grouping Sets to show the following groupings:
- department_id, manager_id, job_id,
- manager_id, job_id
- department_id, manager_id
Execute the query in Oracle Application Express (Apex). (2 Points)
Select Statement You Used :
My example of correct results from Apex (copy) : YOU MUST DELETE MY SCREEN SHOT BELOW AND INSERT YOUR OWN. YOUR SCREEN SHO T MUST SHOW YOUR WORSPACE AND BE CORRECT RESULTS IN ORDER TO RECEIVE CREDIT. (1 Point)
4. Rewrite the following query using alternate options in the WHERE clause that will accomplish the same result as shown below (Hint: Use < comparison operator to specify the track is any track less than 3). Execute the query in Oracle Application Express (Apex). (2 Points)
SELECT MAX(song_id)
FROM d_track_listings
WHERE track IN ( 1, 2, 3);
Select Statement You Used :
My example of correct results from Apex (copy) : YOU MUST DELETE MY SCREEN SHOT BELOW AND INSERT YOUR OWN. YOUR SCREEN SHO T MUST SHOW YOUR WORSPACE AND BE CORRECT RESULTS IN ORDER TO RECEIVE CREDIT. (1 Point)
Section 6 Objectives : Creating Subqueries
Create and execute single and multiple row subqueries
Try It / Solve It :
In order to be successful with this lab, y ou must review the ‘Oracle Student Tables’ file located under ‘Videos and Resources’ (multiple tabs across bottom of worksheet). This file will help you to understand the data that is being manipulated in the SQL statements .
I am providing a ‘copy’ (first 20 rows) of the ‘Results from Apex’ for each question. Hopefully, this will help you see if you are on track with you r results. At the top of your SQL window, you can increase the number of rows displayed. You will provide the SQL statement you used to produce shown results AND you will provide a screen shot of your results. In order to receive points, your screenshot MUST include your workspace number.
1. Create a query that displays the first_name and last_name for all employees who have the same department ID as the IT Department. Use a column alias so that first_name displays as First Name and last_name displays as Last Name. You will be gathering data from the employees and departments tables. Execute the query in Oracle Application Express (Apex). (2 Points)
Select Statement You Used :
My example of correct results from Apex (copy) : YOU MUST DELETE MY SCREEN SHOT BELOW AND INSERT YOUR OWN. YOUR SCREEN SHO T MUST SHOW YOUR WORSPACE AND BE CORRECT RESULTS IN ORDER TO RECEIVE CREDIT. (1 Point)
2. Create a query that displays the first name and last name of employees who have the same job id as Rajs and was hired after Davies. Use a column alias so that first_name displays as First Name and last_name displays as Last Name. You will be gathering data from the employees table. Execute the query in Oracle Application Express (Apex). (2 Points)
Select Statement You Used :
My example of correct results from Apex (copy) : YOU MUST DELETE MY SCREEN SHOT BELOW AND INSERT YOUR OWN. YOUR SCREEN SHO T MUST SHOW YOUR WORSPACE AND BE CORRECT RESULTS IN ORDER TO RECEIVE CREDIT. (1 Point)
3. Create a query that will find and display the last names of all employees whose salaries are the same as the minimum salary for any department. Use a column alias so that last_name displays as Last Name. You will be gathering data from the employees table. Execute the query in Oracle Application Express (Apex). (2 Points)
Select Statement You Used :
My example of correct results from Apex (copy) : YOU MUST DELETE MY SCREEN SHOT BELOW AND INSERT YOUR OWN. YOUR SCREEN SHO T MUST SHOW YOUR WORSPACE AND BE CORRECT RESULTS IN ORDER TO RECEIVE CREDIT. (1 Point)
Section 7 Objectives : Updating Column Values and Deleting Rows
Use the INSERT statement
Vocabulary:
Directions: Identify the vocabulary word for each definition below. (1 Point each)
1. Modifies existing rows in a table.
Answer:
2. Ensures the data adheres to a predefined set of rules.
Answer:
3. Removes ONE row from a table.
Answer:
Try It / Solve It :
You will need to log into Apex at https://iacademy.oracle.com " https://iacademy.oracle.com in order to complete this portion of the assignment.
NOTE: You will need to execute the SQL statement below in order to copy the ‘customers’ table (f_customers). This will leave the f_customers table as is, while you modify the copy (copy_f_customers).
CREATE TABLE copy_f_customers
AS (SELECT * FROM f_customers);
Table copies will not inherit the associated primary-to-foreign-key integrity rules (relationship constraints) of the original tables. The column data types, however, are inherited in the copied tables.
If a change is not possible, give an explanation as to why it is not possible.
1. Insert the new customers shown below to the copy_f_customers table. Execute in Oracle Application Express (Apex). (3 Points)
ID
FIRST_
NAME
LAST_
NAME
ADDRESS
CITY
STATE
ZIP
PHONE_ NUMBER
511
Kathy
Harper
2 Willy Way
Los Angeles
CA
98008
8586667641
225
Daniel
Spode
1923 Silverado
Denver
CO
80219
4258879009
230
Adam
Zurn
5 Admiral Way
Seattle
WA
7193343523
Select Statement s You Used :
Using Select * from copy_f_customers, provide a screen shot of your inserted records.
Why are you not able to insert Adam Zurn?(1 Point)
Your Answer:
My example of correct results from Apex (copy) : YOU MUST DELETE MY SCREEN SHOT BELOW AND INSERT YOUR OWN. YOUR SCREEN SHO T MUST SHOW YOUR WORSPACE AND BE CORRECT RESULTS IN ORDER TO RECEIVE CREDIT. (1 Point)
2. Make a copy of the f_staffs table. Name the copy copy_f_staffs. Execute: Select * from copy_f_staffs to view contents of the table. Sue Doe has been an outstanding Global Foods staff member and has been given a salary raise. She will now be paid the same as Bob Miller. Update her record in copy_f_staffs so that she is paid the same salary as Bob Miller. Execute the query in Oracle Application Express (Apex). (2 Points)
Select Statement You Used :
Using Select * from copy_f_staffs, provide a screen shot of your updated record .
My example of correct results from Apex (copy) : YOU MUST DELETE MY SCREEN SHOT BELOW AND INSERT YOUR OWN. YOUR SCREEN SHO T MUST SHOW YOUR WORSPACE AND BE CORRECT RESULTS IN ORDER TO RECEIVE CREDIT. (1 Point)
3. Execute the following SQL statement. Explain your results. Execute the query in Oracle Application Express (Apex). (2 Points)
DELETE from departments
WHERE department_id = 60;
Explain the integrity constraint error
Your Answer:
4. Sue Doe has decided to go back to college and does not have the time to work and go to school. Delete her from the Global Fast Foods staff (copy_f_staffs table). Verify that the change was made. Execute the query in Oracle Application Express (Apex). (2 Points)
Select Statement You Used :
Using Select * from copy_f_staffs , provide a screen shot of your table after deleting Sue Doe.
My example of correct results from Apex (copy) : YOU MUST DELETE MY SCREEN SHOT BELOW AND INSERT YOUR OWN. YOUR SCREEN SHO T MUST SHOW YOUR WORSPACE AND BE CORRECT RESULTS IN ORDER TO RECEIVE CREDIT. (1 Point)
