RelationalDBDesign
SiteMap
Database Analysis
Database Design
Extended DB Features
Oracle SQL Extensions
Join Queries
«Prev
Next»
SQL Extensions
Pet Store Schema
Database Components
Oracle DB Structure
Data Dictionary Views
SQL Plus DB Logging
Oracle SQL is Different
Database SW Conclusion
Oracle Extensions - Quiz
Select Statement
Query Operators Conditions
Hierarchical Retrieval Connect
Union Intersect Minus
SQL Group Operator
Advanced Group Operators
Oracle Pseudo Columns
Oracle Extension Conclusion
Data Manipulation
Character String Functions
Concat Substr Length
Upper initcap rtrim
to_char, round, trunc
Standard Date Format
Valid Time Related Functions
Decode NVL Functions
Function Extension Implementations
Join Table Queries
In Clause Special Extensions
Outer Join Syntax
Outer Join - Example
Inner Join Outer Join-Quiz
Correlated Sub Queries
Correlated Subquery Example
Subquery with Exists
Change IN to Exists
Oracle exists clause
From Clause Query
Advanced Queries Conclusion
SQL Plus Environment
Using Environment
SQL Plus Query
SQL Plus Text Editor
Working Query Files
SQL*Plus File location
Setting Column Attributes
Settings for Reports
Saving Output with Spool
SQL Plus environment
Creating Table Structures
Create Table Command
Tablespaces Data Ciles
Setting Storage Space
Primary Key Unique Constraints
Check Unique Constraints
Adding Modifying Column
Disable Constraint Commands
Table Constraints
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