RelationalDBDesign
RelationalDBDesign
SiteMap
DB Features
Oracle SQL
Join Queries
«Prev
Next»
SQL Extensions
Database Components
Database SW Conclusion
Select Statement
Hierarchical Retrieval Connect
Union Intersect Minus
Advanced Group Operators
Intersect Union Minus
Data Manipulation
Concat Substr Length
Decode NVL Functions
Date Comparison
Character String Functions
to_char, round, trunc
round, trunc Functions
Join Table Queries
Inner Join Outer Join-Quiz
Advanced Queries Conclusion
SQL Plus Environment
Sql Plus TextEditor
SQL Plus Worksheet
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