Exam : Oracle 1Z0 051

Exam : Oracle 1Z0-051
Title : Oracle Database: SQL Fundamentals I
1. View the Exhibit to examine the description for the SALES table.
Which views can have all DML operations performed on it? (Choose all that apply.)
A. CREATE VIEW v3
AS SELECT * from SALES
WHERE cust_id = 2034
WITH CHECK OPTION;
B. CREATE VIEW v1
AS SELECT * from SALES
WHERE time_id <= SYSDATE - 2*365
WITH CHECK OPTION;
C. CREATE VIEW v2
AS SELECT prod_id, cust_id, time_id from SALES
WHERE time_id <= SYSDATE - 2*365
WITH CHECK OPTION;
D. CREATE VIEW v4
AS SELECT prod_id, cust_id, SUM(quantity_sold) from SALES
WHERE time_id <= SYSDATE - 2*365
GROUP BY prod_id, cust_id
WITH CHECK OPTION;
Answer: AB
2. View the Exhibit and examine the structure of the CUSTOMERS table.
Which two tasks would require subqueries or joins to be executed in a single statement? (Choose two.)
A. listing of customers who do not have a credit limit and were born before 1980
B. finding the number of customers, in each city, whose marital status is 'married'
C. finding the average credit limit of male customers residing in 'Tokyo' or 'Sydney'
D. listing of those customers whose credit limit is the same as the credit limit of customers residing in the city 'Tokyo'
E. finding the number of customers, in each city, whose credit limit is more than the average credit limit of all the customers
Answer: DE
3. Which two statements are true regarding the USING and ON clauses in table joins? (Choose two.)
A. Both USING and ON clauses can be used for equijoins and nonequijoins.
B. A maximum of one pair of columns can be joined between two tables using the ON clause.
C. The ON clause can be used to join tables on columns that have different names but compatible data types.
D. The WHERE clause can be used to apply additional conditions in SELECT statements containing the ON or the USING clause.
Answer: CD
4. You need to extract details of those products in the SALES table where the PROD_ID column co