1Z0-051 | Most up-to-date 1Z0-051 Exam Study Guides With New Update Exam Questions

Q1. - (Topic 1) 

View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables. There is only one customer with the cus_last_name column having value Roberts. Which INSERT statement should be used to add a row into the ORDERS table for the customer whose CUST_LAST_NAME is Roberts and CREDIT_LIMIT is 600? 

A. INSERT INTO orders VALUES (l.'10-mar-2007 'direct'. (SELECT customerid FROM customers WHERE cust_last_iiame='Roberts' AND credit_limit=600). 1000); 

B. INSERT INTO orders (order_id.order_date.order_mode. (SELECT customer id FROM customers WHERE cust_last_iiame='Roberts' AND redit_limit=600).order_total) VALUES(L'10-mar-2007'. 'direct', &&customer_id, 1000): 

C. INSERT INTO(SELECT o.order_id. o.order_date.o.order_modex.customer_id. 

o.ordertotal FROM orders o. customers c WHERE o.customer_id = c.customerid AND c.cust_la$t_name-RoberTs' ANDc.credit_liinit=600) VALUES (L'10-mar-2007 'direct'.( SELECT customer_id FROM customers WHERE cust_last_iiame='Roberts' AND credit_limit=600). 1000); 

D. INSERT INTO orders (order_id.order_date.order_mode. 

(SELECT customer_id 

FROM customers 

WHERE cust_last_iiame='Roberts' AND 

credit_limit=600).order_total) 

VALUES(l.'10-mar-2007 'direct'. &customer_id. 1000): 

Answer: A 

Q2. - (Topic 1) 

View the Exhibit and examine the structure of the PROMOTIONS table. Examine the following two SQL statements: 

Which statement is true regarding the above two SQL statements? 

A. statement 1 gives an error, statement 2 executes successfully 

B. statement 2 gives an error, statement 1 executes successfully 

C. statement 1 and statement 2 execute successfully and give the same output 

D. statement 1 and statement 2 execute successfully and give a different output 

Answer: D 

Q3. - (Topic 2) 

View the Exhibit and examine the data in the PROMO_NAME and PROMO_END_DATE columns of the PROMOTIONS table, and the required output format. 

Which two queries give the correct result? (Choose two.) 

A. SELECT promo_name, TO_CHAR(promo_end_date,'Day') ', ' 

TO_CHAR(promo_end_date,'Month') ' ' 

TO_CHAR(promo_end_date,'DD, YYYY') AS last_day 

FROM promotions; 

B. SELECT promo_name,TO_CHAR (promo_end_date,'fxDay') ', ' 

TO_CHAR(promo_end_date,'fxMonth') ' ' 

TO_CHAR(promo_end_date,'fxDD, YYYY') AS last_day 

FROM promotions; 

C. SELECT promo_name, TRIM(TO_CHAR(promo_end_date,'Day')) ', ' TRIM(TO_CHAR 

(promo_end_date,'Month')) ' ' 

TRIM(TO_CHAR(promo_end_date,'DD, YYYY')) AS last_day 

FROM promotions; 

D. SELECTpromo_name,TO_CHAR(promo_end_date,'fmDay')',' 

TO_CHAR(promo_end_date,'fmMonth') ' ' 

TO_CHAR(promo_end_date,'fmDD, YYYY') AS last_day 

FROM promotions; 

Answer: C,D 

Q4. - (Topic 2) 

Examine the structure of the EMPLOYEES table: 

EMPLOYEE_ID NUMBER Primary Key 

FIRST_NAME VARCHAR2(25) 

LAST_NAME VARCHAR2(25) 

Which three statements insert a row into the table? (Choose three.) 

A. INSERT INTO employees VALUES ( NULL, 'John', 'Smith'); 

B. INSERT INTO employees( first_name, last_name) VALUES( 'John', 'Smith'); 

C. INSERT INTO employees VALUES ( 1000, 'John', NULL); 

D. INSERT INTO employees (first_name, last_name, employee_id) VALUES ( 1000, 'John', 'Smith'); 

E. INSERT INTO employees (employee_id) VALUES (1000); 

F. INSERT INTO employees (employee_id, first_name, last_name) VALUES ( 1000, 'John', ' '); 

Answer: C,E,F 

Explanation: EMPLOYEE_ID is a primary key. Incorrect Answer: AEMPLOYEE_ID cannot be null BEMPLOYEE_ID cannot be null Dmismatch of field_name with datatype 

Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-11 

Q5. - (Topic 1) 

The SQL statements executed in a user session as follows: Exhibit: 

Which two statements describe the consequence of issuing the ROLLBACK TO SAVE POINT a command in the session? (Choose two.) 

A. Both the DELETE statements and the UPDATE statement are rolled back 

B. The rollback generates an error 

C. Only the DELETE statements are rolled back 

D. Only the seconds DELETE statement is rolled back 

E. No SQL statements are rolled back 

Answer: B,E 

Q6. - (Topic 2) 

Evaluate the SQL statement 

DROP TABLE DEPT: 

Which four statements are true of the SQL statement? (Choose four) 

A. You cannot roll back this statement. 

B. All pending transactions are committed. 

C. All views based on the DEPT table are deleted. 

D. All indexes based on the DEPT table are dropped. 

E. All data in the table is deleted, and the table structure is also deleted. 

F. All data in the table is deleted, but the structure of the table is retained. 

G. All synonyms based on the DEPT table are deleted. 

Answer: A,B,D,E 

Explanation: 

You cannot roll back DROP TABLE statement. All pending transactions related on this 

table are committed. If the table is dropped, Oracle automatically drops any index, trigger 

and constraint associated with the table as well. All data in the table is deleted, and the 

table structure is also deleted. 

Incorrect Answers 

C:All views based on the DEPT table become invalid, but they are not deleted. 

F:All data in the table is deleted, and the table structure is also deleted. Command 

TRUNCATE deletes all data in the table, but does not delete the structure of the table. 

G:All synonyms based on the DEPT table are not deleted after dropping the table. 

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 225 Chapter 5: Creating Oracle Database Objects 

Q7. - (Topic 1) 

Which statement is true regarding the INTERSECT operator? 

A. It ignores NULL values 

B. The number of columns and data types must be identical for all SELECT statements in the query 

C. The names of columns in all SELECT statements must be identical 

D. Reversing the order of the intersected tables the result 

Answer: B 

Explanation: 

INTERSECT Returns only the rows that occur in both queries’ result sets, sorting them and 

removing duplicates. 

The columns in the queries that make up a compound query can have different names, but 

the output result set will use the names of the columns in the first query. 

Q8. - (Topic 1) 

See the Exhibit and examine the structure of ORD table: Exhibit: 

Evaluate the following SQL statements that are executed in a user session in the specified order: 

CREATE SEQUENCE ord_seq; 

SELECT ord_seq.nextval 

FROM dual; 

INSERT INTO ord 

VALUES (ord_seq.CURRVAL, ’25-jan-2007,101); 

UPDATE ord 

SET ord_no= ord_seq.NEXTVAL 

WHERE cust_id =101; 

What would be the outcome of the above statements? 

A. All the statements would execute successfully and the ORD_NO column would contain the value 2 for the CUST_ID 101. 

B. The CREATE SEQUENCE command would not execute because the minimum value and maximum value for the sequence have not been specified. 

C. The CREATE SEQUENCE command would not execute because the starting value of the sequence and the increment value have not been specified. 

D. All the statements would execute successfully and the ORD_NO column would have the value 20 for the CUST_ID 101 because the default CACHE value is 20. 

Answer: A 

Q9. - (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 ); 

Answer: C 

Q10. - (Topic 1) 

You need to display the first names of all customers from the CUSTOMERS table that contain the character 'e' and have the character 'a' in the second last position. 

Which query would give the required output? 

A. 

SELECT cust_first_name FROM customers WHERE INSTR(cust_first_name, 'e')<>0 AND SUBSTR(cust_first_name, -2, 1)='a' 

B. 

SELECT cust_first_name FROM customers WHERE INSTR(cust_first_name, 'e')<>'' AND SUBSTR(cust_first_name, -2, 1)='a' 

C. 

SELECT cust_first_name FROM customers WHERE INSTR(cust_first_name, 'e')IS NOT NULL AND SUBSTR(cust_first_name, 1,-2)='a' 

D. 

SELECT cust_first_name FROM customers WHERE INSTR(cust_first_name, 'e')<>0 AND SUBSTR(cust_first_name, LENGTH(cust_first_name),-2)='a' 

Answer: A 

Explanation: 

The SUBSTR(string, start position, number of characters) function accepts three 

parameters and returns a string consisting of the number of characters extracted from the 

source string, beginning at the specified start position: 

substr('http://www.domain.com',12,6) = domain 

The position at which the first character of the returned string begins. 

When position is 0 (zero), then it is treated as 1. 

When position is positive, then the function counts from the beginning of string to find the 

first character. 

When position is negative, then the function counts backward from the end of string. 

substring_length 

The length of the returned string. SUBSTR calculates lengths using characters as defined 

by the input character set. SUBSTRB uses bytes instead of characters. SUBSTRC uses 

Unicode complete characters. 

SUBSTR2 uses UCS2 code points. SUBSTR4 uses UCS4 code points. 

When you do not specify a value for this argument, then the function 

The INSTR(source string, search item, [start position],[nth occurrence of search item]) 

function returns a number that represents the position in the source string, beginning from 

the given start position, where the nth occurrence of the search item begins: 

instr('http://www.domain.com','.',1,2) = 18