26 Fundamentals of Subqueries

·         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?

SELECT song_id

FROM d_play_list_items

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

FROM d_events

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.”

SELECT track

FROM d_track_listings

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

FROM d_events

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?

SELECT first_name,last_name

FROM f_staffs

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?

SELECT first_name,last_name

FROM f_staffs

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?

SELECT first_name,last_name

FROM employees

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?

SELECT department_name

FROM departments

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.

TRUE

c.  Single-row subqueries can return multiple values to the outer query.

FALSE