1Z0-051 | Printable Oracle 1Z0-051 practice test

Q91. - (Topic 1) 

Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables: 

Which DELETE statement is valid? 

A. DELETE FROM employeesWHERE employee_id = (SELECT employee_id FROM employees); 

B. DELETE * FROM employeesWHERE employee_id=(SELECT employee_id FROM new_employees); 

C. DELETE FROM employeesWHERE employee_id IN (SELECT employee_id FROM new_employees WHERE name = ‘Carrey’); 

D. DELETE * FROM employeesWHERE employee_id IN (SELECT employee_id FROM new_employees WHERE name = ‘Carrey’); 

Answer: C 

Q92. - (Topic 2) 

Which two statements are true about WHERE and HAVING clauses? (Choose two) 

A. A WHERE clause can be used to restrict both rows and groups. 

B. A WHERE clause can be used to restrict rows only. 

C. A HAVING clause can be used to restrict both rows and groups. 

D. A HAVING clause can be used to restrict groups only. 

E. A WHERE clause CANNOT be used in a query of the query uses a HAVING clause. 

F. A HAVING clause CANNOT be used in sub queries. 

Answer: B,D Explanation: 

B: WHERE clause cannot be use to restrict groups 

WHERE clause cannot be use when there is group functions. 

D: A HAVING clause can only e used to restrict GROUPS. 

Note: HAVING clause to specify which groups are to be displayed and thus further restrict the groups on the basis of aggregate information. The Oracle server performs the following steps when you use the Having clause 

1. 

rows are grouped 

2. 

the group function is applied to the group 

3. 

the group that match the criteria in the Having clause are displayed. 

Incorrect Answers : 

A. Where clause cannot be use to restrict groups 

C. A HAVING clause can only e used to restrict GROUPS. 

E. WHERE clause cannot be use when there is group function, instead HAVING is to be use. 

F. There is no constraint to use HAVING clause in a sub queries. 

Refer: Introduction to Oracle9i: SQL, Oracle University Student Guide, Aggregating Data using Group Functions, p. 5-20 

Q93. - (Topic 1) 

What is true regarding sub queries? 

A. The inner query always sorts the results of the outer query 

B. The outer query always sorts the results of the inner query 

C. The outer query must return a value to the outer query 

D. The inner query returns a value to the outer query 

E. The inner query must always return a value or the outer query will give an error 

Answer: D 

Explanation: The inner query returns a value to the outer query. If the inner query does not return a value, the outer query does not return a result 

Q94. - (Topic 2) 

View the Exhibit and examine the structure and data in the INVOICE table. 

Which two SQL statements would execute successfully? (Choose two.) 

A. SELECT AVG(inv_date ) FROM invoice; 

B. SELECT MAX(inv_date),MIN(cust_id) FROM invoice; 

C. SELECT MAX(AVG(SYSDATE - inv_date)) FROM invoice; 

D. SELECT AVG( inv_date - SYSDATE), AVG(inv_amt) FROM invoice; 

Answer: B,D 

Explanation: 

Using the AVG and SUM Functions You can use the AVG, SUM, MIN, and MAX functions against the columns that can store numeric data. The example in the slide displays the average, highest, lowest, and sum of monthly salaries for all sales representatives Using the MIN and MAX Functions You can use the MAX and MIN functions for numeric, character, and date data types. The example in the slide displays the most junior and most senior employees. 

Q95. - (Topic 1) 

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

Using the PROMOTIONS table, you need to find out the names and cost of all the promos done on 'TV' and 'internet' that ended in the time interval 15th March '00 to 15th October '00. 

Which two queries would give the required result? (Choose two.) 

A. SELECT promo_name, promo_cost FROM promotions WHERE promo_category IN ('TV', 'internet') AND promo_end_date BETWEEN '15-MAR-00' AND '15-OCT-00' 

B. SELECT promo_name, promo_cost FROM promotions WHERE promo_category = 'TV' OR promo_category ='internet' AND promo_end_date >='15-MAR-00' OR promo_end_date <='15-OCT-00' 

C. SELECT promo_name, promo_cost FROM promotions WHERE (promo_category BETWEEN 'TV' AND 'internet') AND (promo_end_date IN ('15-MAR-00','15-OCT-00')); 

D. SELECT promo_name, promo_cost FROM promotions WHERE (promo_category = 'TV' OR promo_category ='internet') AND (promo_end_date >='15-MAR-00' AND promo_end_date <='15-OCT-00'); 

Answer: A,D 

Q96. - (Topic 1) 

Examine the structure of the EMPLOYEES table: 

You want to create a SQL script file that contains an INSERT statement. When the script is run, the INSERT statement should insert a row with the specified values into the EMPLOYEES table. The INSERT statement should pass values to the table columns as specified below:

Which INSERT statement meets the above requirements? 

A. INSERT INTO employees VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did); 

B. INSERT INTO employees VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did IN (20,50)); 

C. INSERT INTO (SELECT * FROM employees WHERE department_id IN (20,50)) VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did); 

D. INSERT INTO (SELECT * FROM employees WHERE department_id IN (20,50) WITH CHECK OPTION) 

VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did); 

E. INSERT INTO (SELECT * FROM employees WHERE (department_id = 20 AND department_id = 50) WITH CHECK OPTION ) VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did); 

Answer: D 

Q97. - (Topic 2) 

What is true about updates through a view? 

A. You cannot update a view with group functions. 

B. When you update a view group functions are automatically computed. 

C. When you update a view only the constraints on the underlying table will be in effect. 

D. When you update a view the constraints on the views always override the constraints on the underlying tables. 

Answer: A 

Q98. - (Topic 2) 

Evaluate the SQL statement: 

SELECT LPAD (salary,10,’*’) 

FROM EMP 

WHERE EMP_ID = 1001; 

If the employee with the EMP_ID 1001 has a salary of 17000, what is displayed? 

A. 17000.00 

B. 17000***** 

C. ****170.00 

D. **17000.00 

E. an error statement 

Answer: D 

Q99. - (Topic 1) 

Examine the structure of the INVOICE table: Exhibit: 

Which two SQL statements would execute successfully? (Choose two.) 

A. SELECT inv_no,NVL2(inv_date,'Pending','Incomplete') FROM invoice; 

B. SELECT inv_no,NVL2(inv_amt,inv_date,'Not Available') FROM invoice; 

C. SELECT inv_no,NVL2(inv_date,sysdate-inv_date,sysdate) FROM invoice; 

D. SELECT inv_no,NVL2(inv_amt,inv_amt*.25,'Not Available') FROM invoice; 

Answer: A,C 

Explanation: 

The NVL2 Function 

The NVL2 function provides an enhancement to NVL but serves a very similar purpose. It evaluates whether a column or expression of any data type is null or not. 5-6 The NVL function If the first term is not null, the second parameter is returned, else the third parameter is returned. Recall that the NVL function is different since it returns the original term if it is not null. The NVL2 function takes three mandatory parameters. Its syntax is NVL2(original, ifnotnull, ifnull), where original represents the term being tested. Ifnotnull is returned if original is not null, and ifnull is returned if original is null. The data types of the ifnotnull and ifnull parameters must be compatible, and they cannot be of type LONG. They must either be of the same type, or it must be possible to convert ifnull to the type of the ifnotnull parameter. The data type returned by the NVL2 function is the same as that of the ifnotnull parameter. 

Q100. - (Topic 1) 

Which two statements are true regarding working with dates? (Choose two.) 

A. The default internal storage of dates is in the numeric format 

B. The RR date format automatically calculates the century from the SYSDATE function but allows the user to enter the century if required 

C. The default internal storage of dates is in the character format 

D. The RR date format automatically calculates the century from the SYSDATE function and does not allow the user to enter the century 

Answer: A,B 

Explanation: 

Working with Dates The Oracle Database stores dates in an internal numeric format, representing the century, year, month, day, hours, minutes, and seconds. The default display and input format for any date is DD-MON-RR. RR Date Format The RR date format is similar to the YY element, but you can use it to specify different centuries. Use the RR date format element instead of YY so that the century of the return value varies according to the specified two digit year and the last two digits of the current year. The table in the slide summarizes the behavior of the RR element. 

untitled Note the values shown in the last two rows of the above table. As we approach the middle of the century, then the RR behavior is probably not what you want. This data is stored internally as follows: CENTURY YEAR MONTH DAY HOUR MINUTE SECOND 19 87 06 17 17 10 43