1Z0-051 | A Review Of Practical 1Z0-051 vce

Q61. - (Topic 2)

Examine the structure of the ORDERS table:

You want to find the total value of all the orders for each year and issue the following command:

SQL>SELECT TO_CHAR(order_date,'rr'), SUM(order_total)

FROM orders

GROUP BY TO_CHAR(order_date,'yyyy');

Which statement is true regarding the outcome?

A. It executes successfully and gives the correct output.

B. It gives an error because the TO_CHAR function is not valid.

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

D. It gives an error because the data type conversion in the SELECT list does not match the data type conversion in the GROUP BY clause.

Answer: D

Q62. - (Topic 1)

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: A,B

Explanation:

Creating a View You can create a view by embedding a subquery in the CREATE VIEW statement. In the syntax: CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view [(alias[, alias]...)] AS subquery [WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY [CONSTRAINT constraint]]; OR REPLACE Re-creates the view if it already exists FORCE Creates the view regardless of whether or not the base tables exist NOFORCE Creates the view only if the base tables exist (This is the default.) View Is the name of the view alias Specifies names for the expressions selected by the view’s query (The number of aliases must match the number of expressions selected by the view.) subquery Is a complete SELECT statement (You can use aliases for the columns in the SELECT list.) WITH CHECK OPTION Specifies that only those rows that are accessible to the view can be inserted or updated ANSWER D constraint Is the name assigned to the CHECK OPTION constraint WITH READ ONLY Ensures that no DML operations can be performed on this view Rules for Performing DML Operations on a View You cannot add data through a view if the view includes: Group functions A GROUP BY clause The DISTINCT keyword The pseudocolumn ROWNUM keyword Columns defined by expressions NOT NULL columns in the base tables that are not selected by the view – ANSWER C

Q63. - (Topic 1)

Examine the structure of the STUDENTS table:

You need to create a report of the 10 students who achieved the highest ranking in the course INT SQL and who completed the course in the year 1999.

Which SQL statement accomplishes this task?

A. SELECT student_ id, marks, ROWNUM "Rank"

FROM students

WHERE ROWNUM <= 10

AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99

AND course_id = 'INT_SQL'

ORDER BY marks DESC;

B. SELECT student_id, marks, ROWID "Rank"

FROM students

WHERE ROWID <= 10

AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99'

AND course_id = 'INT_SQL'

ORDER BY marks;

C. SELECT student_id, marks, ROWNUM "Rank"

FROM (SELECT student_id, marks

FROM students

WHERE ROWNUM <= 10

AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-

99'

AND course_id = 'INT_SQL'

ORDER BY marks DESC);

D. SELECT student_id, marks, ROWNUM "Rank”

FROM (SELECT student_id, marks

FROM students

WHERE (finish_date BETWEEN ’01-JAN-99 AND ’31-DEC-99’

AND course_id = ‘INT_SQL’

ORDER BY marks DESC)

WHERE ROWNUM <= 10 ;

E. SELECTstudent id, marks, ROWNUM “Rank”

FROM(SELECT student_id, marks

FROM students

ORDER BY marks)

WHEREROWNUM <= 10

ANDfinish date BETWEEN ’01-JAN-99’ AND ’31-DEC-99’

ANDcourse_id = ‘INT_SQL’;

Answer: D

Q64. - (Topic 2)

Examine the data in the CUST_NAME column of the CUSTOMERS table. CUST_NAME

Lex De Haan Renske Ladwig Jose Manuel Urman

Jason Mallin

You want to extract only those customer names that have three names and display the * symbol in place of the

first name as follows:

CUST NAME

*** De Haan

**** Manuel Urman

Which two queries give the required output? (Choose two.)

SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name),'*')

"CUST NAME" FROM customers

WHERE INSTR(cust_name, ' ',1,2)<>0;

SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name),'*')

"CUST NAME" FROM customers

WHERE INSTR(cust_name, ' ',-1,2)<>0;

SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name)-INSTR(cust_name,''),'*') "CUST NAME"

FROM customers

WHERE INSTR(cust_name, ' ',-1,-2)<>0;

SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name)-INSTR(cust_name,' '),'*') "CUST NAME"

FROM customers

WHERE INSTR(cust_name, ' ',1,2)<>0 ;

Answer: A,B

Q65. - (Topic 2)

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

You want to display only those product names with their list prices where the list price is at least double the minimum price. The report should start with the product name having the maximum list price satisfying this

condition.

Evaluate the following SQL statement:

SQL>SELECT prod_name,prod_list_price FROM products WHERE prod_list_price >= 2 * prod_min_price

Which ORDER BY clauses can be added to the above SQL statement to get the correct output?

(Choose all that apply.)

A. ORDER BY prod_list_price DESC, prod_name;

B. ORDER BY (2*prod_min_price)DESC, prod_name;

C. ORDER BY prod_name, (2*prod_min_price)DESC;

D. ORDER BY prod_name DESC, prod_list_price DESC;

E. ORDER BY prod_list_price DESC, prod_name DESC;

Answer: A,E

Explanation:

Using the ORDER BY Clause The order of rows that are returned in a query result is undefined. The ORDER BY clause can be used to sort the rows. However, if you use the ORDER BY clause, it must be the last clause of the SQL statement. Further, you can specify an expression, an alias, or a column position as the sort condition. Syntax SELECT expr FROM table [WHERE condition(s)] [ORDER BY {column, expr, numeric_position} [ASC|DESC]]; In the syntax: ORDER BY specifies the order in which the retrieved rows are displayed ASC orders the rows in ascending order (This is the default order.)

DESC orders the rows in descending order If the ORDER BY clause is not used, the sort order is undefined, and the Oracle server may not fetch rows in the same order for the same query twice. Use the ORDER BY clause to display the rows in a specific order. Note: Use the keywords NULLS FIRST or NULLS LAST to specify whether returned rows containing null values should appear first or last in the ordering sequence.

Q66. - (Topic 1)

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

A. INSERT

B. UPDATE C. SELECT

D. DESCRIBE

E. DELETE

F. RENAME

Answer: D

Explanation:

Describe is a valid iSQL*Plus/ SQL*Plus command.

INSERT, UPDATE & DELETE are SQL DML Statements. A SELECT is an ANSI Standard

SQL Statement not an iSQL*Plus Statement.

RENAME is a DDL Statement.

Q67. - (Topic 1)

You need to calculate the number of days from 1st January 2007 till date . Dates are stored in the default format of dd-mon-rr. Which two SQL statements would give the required output? (Choose two.)

A. SELECT SYSDATE - '01-JAN-2007' FROM DUAL:

B. SELECT SYSDATE - TOJDATE(X)1/JANUARY/2007") FROM DUAL:

C. SELECT SYSDATE - TOJDATE('01-JANUARY-2007') FROM DUAL:

D. SELECT TO_CHAR(SYSDATE. 'DD-MON-YYYY') - '01-JAN-2007' FROM DUAL:

E. SELECT TO_DATE(SYSDATE. *DD/MONTH/YYYY') - '01/JANUARY/2007' FROM DUAL:

Answer: B,C

Q68. - (Topic 2)

Examine the structure of the EMPLOYEES and DEPARTMENTS tables:

You want to create a report displaying employee last names, department names, and locations. Which query should you use to create an equi-join?

A. SELECT last_name, department_name, location_id FROM employees , departments ;

B. SELECT employees.last_name, departments.department_name,

departments.location_id FROM employees e, departments D WHERE e.department_id =d.department_id;

C. SELECT e.last_name, d.department_name, d.location_id FROM employees e, departments D WHERE manager_id =manager_id;

D. SELECT e.last_name, d.department_name, d.location_id FROM employees e, departments D WHERE e.department_id =d.department_id;

Answer: D

Explanation:

Equijoins are also called simple joins or inner joins. Equijoin involve primary key and foreign key.

Incorrect Answer: Athere is no join B invalid syntax Cdoes not involve the join in the primary and foreign key

Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 4-8

Q69. - (Topic 2)

What is true about the WITH GRANT OPTION clause?

A. It allows a grantee DBA privileges.

B. It is required syntax for object privileges.

C. It allows privileges on specified columns of tables.

D. It is used to grant an object privilege on a foreign key column.

E. It allows the grantee to grant object privileges to other users and roles.

Answer: E Explanation:

The GRANT command with the WITH GRANT OPTION clause allows the grantee to grant

object privileges to other users and roles.

Incorrect Answers

A:The WITH GRANT OPTION does not allow a grantee DBA privileges.

B:It is not required syntax for object privileges. It is optional clause of GRANT command.

C:GRANT command does not allows privileges on columns of tables.

D:It is not used to grant an object privilege on a foreign key column.

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 356-365

Chapter 8: User Access in Oracle

Q70. - (Topic 1)

View the Exhibit and examine the description for the CUSTOMERS table.

You want to update the CUST_INCOME_LEVEL and CUST_CREDIT_LIMIT columns for the customer with the CUST_ID 2360. You want the value for the CUST_INCOME_LEVEL to have the same value as that of the customer with the CUST_ID 2560 and the CUST_CREDIT_LIMIT to have the same value as that of the customer with CUST_ID 2566.

Which UPDATE statement will accomplish the task?

UPDATE customers SET cust_income_level = (SELECT cust_income_level FROM customers WHERE cust_id = 2560), cust_credit_limit = (SELECT cust_credit_limit FROM customers WHERE cust_id = 2566) WHERE cust_id=2360;

UPDATE customers SET (cust_income_level,cust_credit_limit) = (SELECT cust_income_level, cust_credit_limit FROM customers WHERE cust_id=2560 OR cust_id=2566) WHERE cust_id=2360;

UPDATE customers SET (cust_income_level,cust_credit_limit) = (SELECT cust_income_level, cust_credit_limit FROM customers WHERE cust_id IN(2560, 2566) WHERE cust_id=2360;

UPDATE customers SET (cust_income_level,cust_credit_limit) = (SELECT cust_income_level, cust_credit_limit FROM customers WHERE cust_id=2560 AND cust_id=2566) WHERE cust_id=2360;

Answer: A

Explanation:

Updating Two Columns with a Subquery

You can update multiple columns in the SET clause of an UPDATE statement by writing

multiple subqueries. The syntax is as follows:

UPDATE table

SET column =

(SELECT column

FROM table

WHERE condition)

[ ,

column =

(SELECT column

FROM table

WHERE condition)]

[WHERE condition ] ;

1Z0-051 | A Review Of Practical 1Z0-051 vce

View the entire exam for free: 1Z0-051 Exam Questions List

Related 1Z0-051 posts: