· It accepts a value from the inner query to complete its SELECT statement.-outer query
· An inner query that is nested within an outer query-subquery
· Another name for a subquery-inner query
1. What is the purpose of using a subquery?
To find the intermediate information we need to extract information we want. E.g. extracting right part in WHERE/HAVING/FROM clause.
2. What is a subquery?
An inner query that is nested within an outer query
3. What DJs on Demand d_play_list_items song_id’s have the same event_id as song_id 45?
WHERE event_id IN(SELECT event_id FROM d_play_list_items WHERE song_id =45);
4. Which events in the DJs on Demand database cost more than event_id = 100?
SELECT id, name
WHERE cost > (SELECT cost FROM d_events WHERE id = 100);
5. Find the track number of the song that has the same CD number as “Party Music for All Occasions.”
WHERE cd_number = (SELECT cd_number FROM d_cds WHERE title = 'Party Music for All Occasions');
6. List the DJs on Demand events whose theme code is the same as the code for “Tropical.”
SELECT id, name
WHERE theme_code = (SELECT code FROM d_themes WHERE description = 'Tropical');
7. What are the names of the Global Fast Foods staff members whose salaries are greater than the staff member whose ID is 12?
WHERE salary > (SELECT salary FROM f_staffs WHERE id = 12);
8. What are the names of the Global Fast Foods staff members whose staff types are not the same as Bob Miller’s?
WHERE staff_type != (SELECT staff_type FROM f_staffs WHERE first_name = 'Bob' AND last_name ='Miller');
9. Which Oracle employees have the same department ID as the IT department?
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'IT');
10. What are the department names of the Oracle departments that have the same location ID as Seattle?
WHERE location_id = ( SELECT location_id FROM locations WHERE city = 'Seattle');
11. Indicate whether the statement regarding subqueries is True or False.
a. It is good programming practice to place a subquery on the right side of the comparison operator.
TRUE- this is a guideline, but it doesn’t break it.
b. A subquery can reference a table that is not included in the outer query’s FROM clause.
c. Single-row subqueries can return multiple values to the outer query.