RelationalDBDesign RelationalDBDesign


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