Join Queries  «Prev  Next»

Inner Join and Outer Join Quiz

Queries using the IN clause or an Outer Join
Each question is worth one point. Select the best answer (or answers) for each question, then click Submit.
 
1. Which query contains a syntax error?
Please select the best answer.
  A. 
SELECT p.product_name
FROM product p
WHERE product_id IN (SELECT p.product_id FROM product p)
  B. 
SELECT c.firstname
FROM customer c
WHERE (c.cust_id, 'Dog') IN (
  SELECT cs.cust_id, p.product_name
  FROM product p, customer_sale cs, sale_item sale
  WHERE cs.sales_id = sale.sales_id
    AND p.product_id = sale.product_id
)
  C. 
SELECT created_by_user, COUNT(*)
FROM pet_care_log
WHERE created_by_user IN (
  SELECT DISTINCT updated_by_user, sale_price
  FROM product
)
GROUP BY created_by_user
ORDER BY created_by_user
  D. 
SELECT last_update_date, COUNT(*)
FROM product
WHERE (updated_by_user, TRUNC(last_update_date)) IN (
  SELECT DISTINCT created_by_user, TRUNC(log_datetime)
  FROM pet_care_log
)
GROUP BY last_update_date
ORDER BY 1

2. Which query correctly uses an outer JOIN that involves the PRODUCT table and another table?
Please select all the correct answers.
  A. 
SELECT p.product_name
FROM product p, pet_care_log pcl
WHERE p.product_id(+) = pcl.product_id(+)
  B. 
SELECT cs.cust_id, p.product_name
FROM product p(+), customer_sale cs, sale_item sale
WHERE cs.sales_id = sale.sales_id
  AND p.product_id = sale.product_id
  C. 
SELECT COUNT(*)
FROM pet_care_log
WHERE product_id IN (
  SELECT si.product_id
  FROM product p, sale_item si
  WHERE p.product_id(+) = si.product_id
)
  D. 
SELECT s.sales_id, s.sale_amount, p.sale_price
FROM sale_item s, product p
WHERE p.product_id = s.product_id(+)

3. What kind of query is best used to create a report listing customers by zip code, and including their associated purchase order numbers if they have made any orders?
Please select the best answer.
  A. A query with an IN clause that uses one column
  B. A query with an outer JOIN between two tables
  C. A query with an IN clause that uses two columns
  D. A query with an inner JOIN between two tables

4. Which SQL join returns only the matching rows from both tables?
Please select the best answer.
  A. INNER JOIN
  B. LEFT JOIN
  C. RIGHT JOIN
  D. FULL OUTER JOIN