Oracle Database 11g: SQL Fundamentals I
Exam Number: 1Z0-051 
Associated Oracle Certification:
Oracle 11g DBA OCA
Oracle 10g DBA OCA
Oracle9i DBA OCA
Oracle PL/SQL Developer Certified Associate 
Exam Price: $95 US
Duration: 120 minutes
Number of Questions: 70
Passing Score: 60%

1. You need to extract details of those products in  the SALES table where the PROD_ID column contains the string ‘_D123′.
Which WHERE clause could  be used in the SELECT statement to get the required output?
A. WHERE prod_id LIKE ‘%_D123%’ ESCAPE ‘_’
B. WHERE prod_id LIKE ‘%\_D123%’ ESCAPE ‘\’
C. WHERE prod_id LIKE ‘%_D123%’ ESCAPE ‘%_’
D. WHERE prod_id LIKE ‘%\_D123%’ ESCAPE ‘\_’
Answer: B

2. View the Exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS, and TIMES tables.
The PROD_ID column is the foreign key in the SALES table, which references the PRODUCTS table. Similarly, the CUST_ID and TIME_ID columns are also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively.
Evaluate the following CREATE TABLE command:
CREATE TABLE new_sales(prod_id, cust_id, order_date DEFAULT SYSDATE)
AS
SELECT prod_id, cust_id, time_id
FROM sales;
Which statement is true regarding the above command?
A. The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the column definition.
B. The NEW_SALES table would get created and all the NOT NULL constraints defined on the specified columns would be passed to the new table.
C. The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.
D. The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the specified columns would be passed to the new table.
Answer: B

3. 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
 
1Z0-051 Exam Recommended Training and Preparation
Oracle Database 11g: SQL Fundamentals I
OR
Oracle Database 11g: SQL Fundamentals l Ed 1 LWC
OR
Oracle Database 11g: Introduction to SQL
OR
Oracle Database 11g: Introduction to SQL Ed 1 LWC
Note: The Oracle Database 11g: Introduction to SQL course covers additional topics that are not covered on 1Z0-051 exam.
Exam 1Z0-051 has been validated against Oracle Database 10g and Oracle Database 11g. Training on either version will be appropriate preparation.

The Related Posts