Practice: SECTION 9
Section 9.1
1. In the SQL query shown below, which of the following is true about this query?
___True____ a. Kimberly Grant would not appear in the results set.
___False____ b. The GROUP BY clause has an error because the manager_id is not
listed in the SELECT clause.
___ False ____ c. Only salaries greater than 16001 will be in the result set.
___ False ____ d. Names beginning with Ki will appear after names beginning with
Ko.
___ False ____ e. Last names such as King and Kochhar will be returned even if they
don’t have salaries > 16000.
SELECT last_name, MAX(salary)
FROM employees
WHERE last_name LIKE 'K%'
GROUP BY manager_id, last_name
HAVING MAX(salary) >16000
ORDER BY last_name DESC ;
2. Each of the following SQL queries has an error. Find the error and correct it. Use
Oracle Application Express to verify that your corrections produce the desired results.
a. SELECT manager_id
FROM employees
WHERE AVG(salary) <16000
GROUP BY manager_id;
Error!
Seharusnya:
b. SELECT cd_number, COUNT(title)
FROM d_cds
WHERE cd_number < 93;
Error!
Seharusnya:
c. SELECT ID, MAX(ID), artist AS Artist
FROM d_songs
WHERE duration IN('3 min', '6 min', '10 min')
HAVING ID < 50
GROUP by ID;
Error!
Seharusnya:
d. SELECT loc_type, rental_fee AS Fee
FROM d_venues
WHERE id <100
GROUP BY "Fee"
ORDER BY 2;
Error!
Seharusnya:
3. Rewrite the following query to accomplish the same result:
SELECT DISTINCT MAX(song_id)
FROM d_track_listings
WHERE track IN ( 1, 2, 3);
4. Indicate True or False
__True___ a. If you include a group function and any other individual columns in a
SELECT clause, then each individual column must also appear in the GROUP BY
clause.
__False___ b. You can use a column alias in the GROUP BY clause.
__ False ___ c. The GROUP BY clause always includes a group function.
5. Write a query that will return both the maximum and minimum average salary
grouped by department from the employees table.
6. Write a query that will return the average of the maximum salaries in each department
for the employees table.
Section 9.2
1. Within the Employees table, each manager_id is the manager of one or more
employees who each have a job_id and earn a salary. For each manager, what is the
total salary earned by all of the employees within each job_id? Write a query to
display the Manag-er_id, job_id, and total salary. Include in the result the subtotal
salary for each manager and a grand total of all salaries.
2. Amend the previous query to also include a subtotal salary for each job_id regardless
of the manager_id.
3. Using GROUPING SETS, write a query to show the following groupings:
department_id, manager_id, job_id
manager_id, job_id
department_id, manager_id
Section 9.3
1. Name the different Set operators?
- UNION : Returns all rows from both tables, after eliminating duplicates.
- UNION ALL : Returns all rows from both tables, without eliminating
duplicates.
- INTERSECT : Returns all rows common to both table.
- MINUS : Returns all rows found in one table but not the other.
2. Write one query to return the employee_id, job_id, hire_date, and department_id of
all employees and a second query listing employee_id, job_id, start_date, and
department_id from the job_history table and combine the results as one single
output. Make sure you suppress duplicates in the output.
3. Amend the previous statement to not suppress duplicates and examine the output.
How many extra rows did you get returned and which were they? Sort the output by
employ-ee_id to make it easier to spot.
4. List all employees who have not changed jobs even once. (Such employees are not
found in the job_history table)
5. List the employees that HAVE changed their jobs at least once.
6. Using the UNION operator, write a query that displays the employee_id, job_id, and
salary of ALL present and past employees. If a salary is not found, then just display a
0 (zero) in its place.
