1Z0-051 | Regenerate 1Z0-051 Exam Study Guides With New Update Exam Questions

Q101. - (Topic 1) 

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

Using the PROMOTIONS table, you need to find out the average cost for all promos in the 

range $0-2000 and $2000-5000 in category A. 

You issue the following SQL statements: 

Exhibit: 

What would be the outcome? 

A. It generates an error because multiple conditions cannot be specified for the WHEN clause 

B. It executes successfully and gives the required result 

C. It generates an error because CASE cannot be used with group functions 

D. It generates an error because NULL cannot be specified as a return value 

Answer: B 

Explanation: 

CASE Expression Facilitates conditional inquiries by doing the work of an IF-THEN-ELSE statement: CASE expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr] END 

Q102. - (Topic 2) 

EMPLOYEES and DEPARTMENTS data: EMPLOYEES 

DEPARTMENTS 

On the EMPLOYEES table, EMPLOYEE_ID is the primary key. MGR_ID is the ID managers and refers to the EMPLOYEE_ID. 

On the DEPARTMENTS table DEPARTMENT_ID is the primary key. 

Evaluate this UPDATE statement. 

UPDATE employees SET mgr_id = (SELECT mgr_id FROMemployees WHERE dept_id= (SELECT department_id FROM departments WHERE department_name = 'Administration')), Salary = (SELECT salary 

FROM employees 

WHERE emp_name = 'Smith') 

WHERE job_id = 'IT_ADMIN' 

What happens when the statement is executed? 

A. The statement executes successfully, leaves the manager ID as the existing value, and changes the salary to 4000 for the employees with ID 103 and 105. 

B. The statement executes successfully, changes the manager ID to NULL, and changes the salary to 4000 for the employees with ID 103 and 105. 

C. The statement executes successfully, changes the manager ID to NULL, and changes the salary to 3000 for the employees with ID 103 and 105. 

D. The statement fails because there is more than one row matching the employee name Smith. 

E. The statement fails because there is more than one row matching the IT_ADMIN job ID in the EMPLOYEES table. 

F. The statement fails because there is no 'Administration' department in the DEPARTMENTS table. 

Answer: D 

Explanation: 

'=' is use in the statement and sub query will return more than one row. 

Employees table has 2 row matching the employee name Smith. 

The update statement will fail. 

Incorrect Answers : 

A. The Update statement will fail no update was done. 

B. The update statement will fail no update was done. 

C. The update statement will fail no update was done. 

E. The update statement will fail but not due to job_it='IT_ADMIN' 

F. The update statement will fail but not due to department_id='Administration' 

Refer: Introduction to Oracle9i: SQL, Oracle University Student Guide, Sub queries, p. 6-12 

Q103. - (Topic 2) 

View the Exhibit and examine the structure of CUSTOMERS and SALES tables. 

Evaluate the following SQL statement: 

UPDATE (SELECT prod_id, cust_id, quantity_sold, time_id 

FROM sales) 

SET time_id = '22-MAR-2007' 

WHERE cust_id = (SELECT cust_id 

FROM customers 

WHERE cust_last_name = 'Roberts' AND 

credit_limit = 600); 

Which statement is true regarding the execution of the above UPDATE statement? 

A. It would not execute because two tables cannot be used in a single UPDATE statement. 

B. It would not execute because the SELECT statement cannot be used in place of the table name. 

C. It would execute and restrict modifications to only the columns specified in the SELECT statement. 

D. It would not execute because a subquery cannot be used in the WHERE clause of an UPDATE statement. 

Answer: C 

Explanation: 

One UPDATE statement can change rows in only one table, but it can change any number of rows in that table. 

Q104. - (Topic 1) 

Evaluate these two SQL statements: 

SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY salary DESC; 

SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY 2 DESC; 

What is true about them? 

A. The two statements produce identical results. 

B. The second statement returns a syntax error. 

C. There is no need to specify DESC because the results are sorted in descending order by default. 

D. The two statements can be made to produce identical results by adding a column alias for the salary column in the second SQL statement. 

Answer: A 

Explanation: Explanation: the two statement produce identical results as ORDER BY 2 will take the second column as sorting column. 

Incorrect Answer: Bthere is no syntax error Cresult are sorted in ascending order by default DORDER BY 2 will take the second column as sorting column. Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 2-22 

Q105. - (Topic 2) 

Which are iSQL*Plus commands? (Choose all that apply.) 

A. INSERT 

B. UPDATE 

C. SELECT 

D. DESCRIBE 

E. DELETE 

F. RENAME 

Answer: D 

Explanation: 

The only SQL*Plus command in this list : DESCRIBE. It cannot be used as SQL command. This command returns a description of tablename, including all columns in that table, the datatype for each column and an indication of whether the column permits storage of NULL values. 

Incorrect Answer: A INSERT is not a SQL*PLUS command B UPDATE is not a SQL*PLUS command C SELECT is not a SQL*PLUS command E DELETE is not a SQL*PLUS command F RENAME is not a SQL*PLUS command 

Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 7 

Q106. - (Topic 1) 

Which three statements/commands would cause a transaction to end? (Choose three.) 

A. COMMIT 

B. SELECT 

C. CREATE 

D. ROLLBACK 

E. SAVEPOINT 

Answer: A,C,D 

Q107. - (Topic 2) 

Which is an iSQL*Plus command? 

A. INSERT 

B. UPDATE 

C. SELECT 

D. DESCRIBE 

E. DELETE 

F. RENAME 

Answer: D 

Explanation: Explanation: The only SQL*Plus command in this list : DESCRIBE. It cannot be used as SQL command. This command returns a description of table name, including all columns in that table, the datatype for each column and an indication of whether the column permits storage of NULL values. Incorrect Answer: A INSERT is not a SQL*PLUS command B UPDATE is not a SQL*PLUS command C SELECT is not a SQL*PLUS command E DELETE is not a SQL*PLUS command F RENAME is not a SQL*PLUS command 

Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 7 

Q108. - (Topic 1) 

See the Exhibits and examine the structures of PRODUCTS, SALES and CUSTOMERS table: 

You issue the following query: 

Which statement is true regarding the outcome of this query? 

A. It produces an error because the NATURAL join can be used only with two tables 

B. It produces an error because a column used in the NATURAL join cannot have a qualifier 

C. It produces an error because all columns used in the NATURAL join should have a qualifier 

D. It executes successfully 

Answer: B 

Explanation: 

Creating Joins with the USING Clause 

Natural joins use all columns with matching names and data types to join the tables. The USING clause can be used to specify only those columns that should be used for an equijoin. 

The Natural JOIN USING Clause 

The format of the syntax for the natural JOIN USING clause is as follows: SELECT table1.column, table2.column FROM table1 JOIN table2 USING (join_column1, join_column2…); While the pure natural join contains the NATURAL keyword in its syntax, the JOIN…USING syntax does not. An error is raised if the keywords NATURAL and USING occur in the same join clause. The JOIN…USING clause allows one or more equijoin columns to be explicitly specified in brackets after the USING keyword. This avoids the shortcomings associated with the pure natural join. Many situations demand that tables be joined only on certain columns, and this format caters to this requirement. 

Q109. - (Topic 1) 

Which two are true about aggregate functions? (Choose two.) 

A. You can use aggregate functions in any clause of a SELECT statement. 

B. You can use aggregate functions only in the column list of the select clause and in the WHERE clause of a SELECT statement. 

C. You can mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns. 

D. You can pass column names, expressions, constants, or functions as parameter to an aggregate function. 

E. You can use aggregate functions on a table, only by grouping the whole table as one single group. 

F. You cannot group the rows of a table by more than one column while using aggregate functions. 

Answer: A,D 

Q110. - (Topic 2) 

Examine the structure of the PRODUCTS table: 

You want to display the names of the products that have the highest total value for UNIT_PRICE *QTY_IN_HAND. 

Which SQL statement gives the required output? 

A. 

SELECT prod_name FROM products WHERE (unit_price * qty_in_hand) = (SELECT MAX(unit_price * qty_in_hand) FROM products); 

B. 

SELECT prod_name FROM products WHERE (unit_price * qty_in_hand) = (SELECT MAX(unit_price * qty_in_hand) FROM products GROUP BY prod_name); 

C. 

SELECT prod_name FROM products GROUP BY prod_name HAVING MAX(unit_price * qty_in_hand) = (SELECT MAX(unit_price * qty_in_hand) FROM products GROUP BY prod_name); 

D. 

SELECT prod_name 

FROM products 

WHERE (unit_price * qty_in_hand) = (SELECT MAX(SUM(unit_price * qty_in_hand)) 

FROM products) 

GROUP BY prod_name; 

Answer: A