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.
 
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_NAMEFROM PRODUCT P(+), CUSTOMER_SALE CS, SALE_ITEM SALEWHERE CS.SALES_ID = SALE.SALES_ID AND P.PRODUCT_ID = SALE.PRODUCT_ID
  C. SELECT COUNT(*)FROM PET_CARE_LOGWHERE 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_PRICEFROM SALE_ITEM S, PRODUCT PWHERE 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
  E. Left outer join
  F. Outer join
  G. Sub-query
  H. Inner join

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