Oracle iacademy 3

Page 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

Use Set Operators
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)