Oracle 1Z0-051 Free Practice Questions

Q1. - (Topic 2) 

Examine the structure of the SHIPMENTS table: 

You want to generate a report that displays the PO_ID and the penalty amount to be paid if 

the 

SHIPMENT_DATE is later than one month from the PO_DATE. The penalty is $20 per day. 

Evaluate the following two queries: 

Which statement is true regarding the above commands? 

A. Both execute successfully and give correct results. 

B. Only the first query executes successfully but gives a wrong result. 

C. Only the first query executes successfully and gives the correct result. 

D. Only the second query executes successfully but gives a wrong result. 

E. Only the second query executes successfully and gives the correct result. 

View Answer

Q2. - (Topic 1) 

Examine the structure and data of the CUSTJTRANS table: 

CUSTJRANS 

Name Null? Type 

CUSTNO NOT NULL CHAR(2) TRANSDATE DATE TRANSAMT NUMBER(6.2) CUSTNO TRANSDATE TRANSAMT 

11 01-JAN-07 1000 

22 01-FEB-07 2000 

33 01-MAR-07 3000 

Dates are stored in the default date format dd-mon-rr in the CUSTJTRANS table. Which three SQL statements would execute successfully? (Choose three.) 

A. SELECT transdate + '10' FROM custjrans; 

B. SELECT * FROM custjrans WHERE transdate = '01-01-07': 

C. SELECT transamt FROM custjrans WHERE custno > '11': 

D. SELECT * FROM custjrans WHERE transdate='01-JANUARY-07': 

E. SELECT custno - 'A' FROM custjrans WHERE transamt > 2000: 

View Answer

Q3. - (Topic 1) 

Which three statements are true regarding the data types in Oracle Database 10g/11g? (Choose three.) 

A. The BLOB data type column is used to store binary data in an operating system file 

B. The minimum column width that can be specified for a VARCHAR2 data type column is one 

C. A TIMESTAMP data type column stores only time values with fractional seconds 

D. The value for a CHAR data type column is blank-padded to the maximum defined column width 

E. Only One LONG column can be used per table 

View Answer

Q4. - (Topic 1) 

You are currently located in Singapore and have connected to a remote database in Chicago. You issue the following command: 

Exhibit: 

PROMOTIONS is the public synonym for the public database link for the PROMOTIONS table. 

What is the outcome? 

A. Number of days since the promo started based on the current Chicago data and time 

B. Number of days since the promo started based on the current Singapore data and time. 

C. An error because the WHERE condition specified is invalid 

D. An error because the ROUND function specified is invalid 

View Answer

Q5. - (Topic 1) 

Which two statements are true regarding the ORDER BY clause? (Choose two.) 

A. It is executed first in the query execution. 

B. It must be the last clause in the SELECT statement. 

C. It cannot be used in a SELECT statement containing a HAVING clause. 

D. You cannot specify a column name followed by an expression in this clause. 

E. You can specify a combination of numeric positions and column names in this clause. 

View Answer

Q6. - (Topic 1) 

View the Exhibit and examine the description for the PRODUCTS and SALES table. 

PROD_ID is a primary key in the PRODUCTS table and foreign key in the SALES table. You want to remove all the rows from the PRODUCTS table for which no sale was done for the last three years. Which is the valid DELETE statement? 

A. 

DELETE FROM products WHERE prod_id = (SELECT prod_id FROM sales WHERE time_id - 3*365 = SYSDATE ); 

B. 

DELETE FROM products WHERE prod_id = (SELECT prod_id FROM sales WHERE SYSDATE >= time_id - 3*365 ); 

C. 

DELETE FROM products WHERE prod_id IN (SELECT prod_id FROM sales WHERE SYSDATE - 3*365 >= time_id); 

D. 

DELETE FROM products WHERE prod_id IN (SELECT prod_id FROM sales WHERE time_id >= SYSDATE - 3*365 ); 

View Answer

Q7. - (Topic 2) 

Examine the structure of the TRANSACTIONS table: 

Name Null Type 

TRANS_ID NOT NULL NUMBER(3) 

CUST_NAME VARCHAR2(30) 

TRANS_DATE TIMESTAMP 

TRANS_AMT NUMBER(10,2) 

You want to display the date, time, and transaction amount of transactions that where done before 12 noon. 

The value zero should be displayed for transactions where the transaction amount has not been entered. 

Which query gives the required result? 

A. 

SELECT TO_CHAR(trans_date,'dd-mon-yyyy hh24:mi:ss'), 

TO_CHAR(trans_amt,'$99999999D99') 

FROM transactions 

WHERE TO_NUMBER(TO_DATE(trans_date,'hh24')) < 12 AND 

COALESCE(trans_amt,NULL)<>NULL; 

B. 

SELECT TO_CHAR(trans_date,'dd-mon-yyyy hh24:mi:ss'), 

NVL(TO_CHAR(trans_amt,'$99999999D99'),0) 

FROM transactions 

WHERE TO_CHAR(trans_date,'hh24') < 12; 

C. 

SELECT TO_CHAR(trans_date,'dd-mon-yyyy hh24:mi:ss'), 

COALESCE(TO_NUMBER(trans_amt,'$99999999.99'),0) 

FROM transactions 

WHERE TO_DATE(trans_date,'hh24') < 12; 

D. 

SELECT TO_DATE (trans_date,'dd-mon-yyyy hh24:mi:ss'), 

NVL2(trans_amt,TO_NUMBER(trans_amt,'$99999999.99'), 0) 

FROM transactions 

WHERE TO_DATE(trans_date,'hh24') < 12; 

View Answer

Q8. - (Topic 1) 

Evaluate the following SQL statements: Exhibit: 

You issue the following command to create a view that displays the IDs and last names of the sales staff in the organization. 

Exhibit: 

Which two statements are true regarding the above view? (Choose two.) 

A. It allows you to update job IDs of the existing sales staff to any other job ID in the EMPLOYEES table 

B. It allows you to delete details of the existing sales staff from the EMPLOYEES table 

C. It allows you to insert rows into the EMPLOYEES table 

D. It allows you to insert IDs, last names, and job IDs of the sales staff from the view if it is used in multitable INSERT statements 

View Answer

Q9. - (Topic 1) 

Which view should a user query to display the columns associated with the constraints on a table owned by the user? 

A. USER_CONSTRAINTS 

B. USER_OBJECTS 

C. ALL_CONSTRAINTS 

D. USER_CONS_COLUMNS 

E. USER_COLUMNS 

View Answer

Q10. - (Topic 2) 

View the Exhibit and examine the structure of the PRODUCTS table. 

You want to display the category with the maximum number of items. You issue the following query: 

SQL>SELECT COUNT(*),prod_category_id FROM products GROUP BY prod_category_id HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM products); 

What is the outcome? 

A. It executes successfully and gives the correct output. 

B. It executes successfully but does not give the correct output. 

C. It generates an error because the subquery does not have a GROUP BY clause. 

D. It generates an error because = is not valid and should be replaced by the IN operator. 

View Answer

Q11. - (Topic 2) 

Which two statements are true regarding views? (Choose two.) 

A. A simple view in which column aliases have been used cannot be updated. 

B. Rows cannot be deleted through a view if the view definition contains the DISTINCT keyword. 

C. Rows added through a view are deleted from the table automatically when the view is dropped. 

D. The OR REPLACE option is used to change the definition of an existing view without dropping and recreating it. 

E. The WITH CHECK OPTION constraint can be used in a view definition to restrict the columns displayed through the view. 

View Answer

Q12. - (Topic 1) 

Examine the structure of the EMPLOYEES table: 

Which INSERT statement is valid? 

A. 

INSERT INTO employees (employee_id, first_name, last_name, hire_date) VALUES ( 1000, ‘John’, ‘Smith’, ‘01/01/01’); 

B. 

INSERT INTO employees(employee_id, first_name, last_name, hire_date) VALUES ( 1000, ‘John’, ‘Smith’, ’01 January 01’); 

C. 

INSERT INTO employees(employee_id, first_name, last_name, Hire_date) VALUES ( 1000, ‘John’, ‘Smith’, To_date(‘01/01/01’)); 

D. 

INSERT INTO employees(employee_id, first_name, last_name, hire_date) VALUES ( 1000, ‘John’, ‘Smith’, 01-Jan-01); 

View Answer

Q13. - (Topic 2) 

Which three statements are true regarding views? (Choose three.) 

A. Views can be created only from tables. 

B. Views can be created from tables or other views. 

C. Only simple views can use indexes existing on the underlying tables. 

D. Both simple and complex views can use indexes existing on the underlying tables. 

E. Complex views can be created only on multiple tables that exist in the same schema. 

F. Complex views can be created on multiple tables that exist in the same or different schemas. 

View Answer

Q14. - (Topic 2) 

The DBA issues this SQL command: 

CREATE USER scott IDENTIFIED by tiger; 

What privileges does the user Scott have at this point? 

A. no privileges 

B. only the SELECT privilege 

C. only the CONNECT privilege 

D. all the privileges of a default user 

View Answer

Q15. - (Topic 2) 

Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables: 

EMPLOYEES 

EMPLOYEE_ID NUMBER Primary Key 

FIRST_NAME VARCHAR2(25) 

LAST_NAME VARCHAR2(25) 

HIRE_DATE DATE 

NEW_EMPLOYEES 

EMPLOYEE_ID NUMBER Primary Key 

NAME VARCHAR2(60) 

Which MERGE statement is valid? 

A. MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES (e.employee_id, e.first_name ||', '||e.last_name); 

B. MERGE new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES (e.employee_id, e.first_name ||', '||e.last_name); 

C. MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES(e.employee_id, e.first_name ||', '||e.last_name); 

D. MERGE new_employees c FROM employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT INTO new_employees VALUES (e.employee_id, 

e.first_name ||', '||e.last_name); 

View Answer