The Only Tip You Need: 1z0 051 dumps

Proper study guides for Up to date Oracle Oracle Database: SQL Fundamentals I certified begins with Oracle 1z0 051 pdf preparation products which designed to deliver the Tested 1z0 051 dumps questions by making you pass the 1z0 051 dumps pdf test at your first time. Try the free 1z0 051 dumps pdf demo right now.

♥♥ 2021 NEW RECOMMEND ♥♥

Free VCE & PDF File for Oracle 1Z0-051 Real Exam (Full Version!)

★ Pass on Your First TRY ★ 100% Money Back Guarantee ★ Realistic Practice Exam Questions

Free Instant Download NEW 1Z0-051 Exam Dumps (PDF & VCE):
Available on: http://www.surepassexam.com/1Z0-051-exam-dumps.html

Q1. - (Topic 1)

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

You have to generate a report that displays the promo name and start date for all promos that started after the last promo in the 'INTERNET' category.

Which query would give you the required output?

SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date > ALL (SELECT MAX(promo_begin_date) FROM promotions )AND promo_category = 'INTERNET'

SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date IN (SELECT promo_begin_date FROM promotions WHERE promo_category='INTERNET');

SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date > ALL (SELECT promo_begin_date FROM promotions WHERE promo_category = 'INTERNET');

SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date > ANY (SELECT promo_begin_date FROM promotions WHERE promo_category = 'INTERNET');

Answer: C

Q2. - (Topic 2)

The EMPLOYEES table contains these columns:

EMPLOYEE_ID NUMBER(4)

ENAME VARCHAR2 (25)

JOB_ID VARCHAR2(10)

Which SQL statement will return the ENAME, length of the ENAME, and the numeric position of the letter "a" in the ENAME column, for those employees whose ENAME ends with a the letter "n"?

A. SELECT ENAME, LENGTH(ENAME), INSTR(ENAME, 'a') FROM EMPLOYEES WHERE SUBSTR(ENAME, -1, 1) = 'n'

B. SELECT ENAME, LENGTH(ENAME), INSTR(ENAME, ,-1,1) FROM EMPLOYEES WHERE SUBSTR(ENAME, -1, 1) = 'n'

C. SELECT ENAME, LENGTH(ENAME), SUBSTR(ENAME, -1,1) FROM EMPLOYEES WHERE INSTR(ENAME, 1, 1) = 'n'

D. SELECT ENAME, LENGTH(ENAME), SUBSTR(ENAME, -1,1) FROM EMPLOYEES WHERE INSTR(ENAME, -1, 1) = 'n'

Answer: A

Explanation:

INSTR is a character function return the numeric position of a named string.

INSTR(NAMED,’a’)

Incorrect Answer:

BDid not return a numeric position for ‘a’.

CDid not return a numeric position for ‘a’.

DDid not return a numeric position for ‘a’.

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

Q3. - (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

Q4. - (Topic 1)

Evaluate this SQL statement:

SELECT e.emp_name, d.dept_name

FROM employees e

JOIN departments d

USING (department_id)

WHERE d.department_id NOT IN (10,40)

ORDER BY dept_name;

The statement fails when executed. Which change fixes the error?

A. remove the ORDER BY clause

B. remove the table alias prefix from the WHERE clause

C. remove the table alias from the SELECT clause

D. prefix the column in the USING clause with the table alias

E. prefix the column in the ORDER BY clause with the table alias

F. replace the condition

”d.department_id NOT IN (10,40)”

in the WHERE clause with

”d.department_id <> 10 AND d.department_id <> 40”

Answer: B

Q5. - (Topic 2)

Examine the data in the PROMO_BEGIN_DATE column of the PROMOTIONS table:

PROMO_BEGIN _DATE

04-jan-00

10-jan-00

15-dec-99

18-oct-98

22-aug-99

You want to display the number of promotions started in 1999 and 2000.

Which query gives the correct output?

SELECT SUM(DECODE(SUBSTR(promo_begin_date,8),'00',1,0)) "2000",

SUM(DECODE(SUBSTR

(promo_begin_date,8),'99',1,0)) "1999"

FROM promotions;

SELECT SUM(CASE TO_CHAR(promo_begin_date,'yyyy') WHEN '99' THEN 1 ELSE 0

END) "1999",SUM(CASE TO_CHAR(promo_begin_date,'yyyy') WHEN '00' THEN 1 ELSE

0 END) "2000"

FROM promotions;

SELECT COUNT(CASE TO_CHAR(promo_begin_date,'yyyy') WHEN '99' THEN 1 ELSE 0 END) "1999", COUNT(CASE TO_CHAR(promo_begin_date,'yyyy') WHEN '00' THEN 1 ELSE 0 END) "2000" FROM promotions;

SELECT COUNT(DECODE(SUBSTR(TO_CHAR(promo_begin_date,'yyyy'), 8), '1999', 1,

0)) "1999", COUNT(DECODE(SUBSTR(TO_CHAR(promo_begin_date,'yyyy'), 8),'2000', 1,

0)) "2000"

FROM promotions;

Answer: A

Q6. - (Topic 1)

What is true about sequences?

A. The start value of the sequence is always 1.

B. A sequence always increments by 1.

C. The minimum value of an ascending sequence defaults to 1.

D. The maximum value of descending sequence defaults to 1.

Answer: C

Q7. - (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.

Answer: B,E

Q8. - (Topic 1)

The user Alice wants to grant all users query privileges on her DEPT table. Which SQL statement accomplishes this?

A. GRANT select ON dept TO ALL_USERS;

B. GRANT select ON dept TO ALL;

C. GRANT QUERY ON dept TO ALL_USERS

D. GRANT select ON dept TO PUBLIC;

Answer: D

Explanation: view the columns associated with the constraint names in the USER_CONS_COLUMNS view.

Incorrect Answer: Atable to view all constraints definition and names Bshow all object name belong to user Cdoes not display column associated Eno such view

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

Q9. - (Topic 2)

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

A. Subqueries can contain GROUP BY and ORDER BY clauses.

B. Main query and subquery can get data from different tables.

C. Main query and subquery must get data from the same tables.

D. Subqueries can contain ORDER BY but not the GROUP BY clause.

E. Only one column or expression can be compared between the main query and subquery.

F. Multiple columns or expressions can be compared between the main query and subquery.

Answer: A,B,F

Explanation:

SUBQUERIES can be used in the SELECT list and in the FROM, WHERE, and HAVING

clauses of a query.

A subquery can have any of the usual clauses for selection and projection. The following

are required clauses:

A SELECT list

A FROM clause

The following are optional clauses: WHERE GROUP BY HAVING

The subquery (or subqueries) within a statement must be executed before the parent query that calls it, in order that the results of the subquery can be passed to the parent.

Q10. - (Topic 1)

You need to perform these tasks:

. Create and assign a MANAGER role to Blake and Clark . Grant CREATE TABLE and CREATE VIEW privileges to Blake and Clark

Which set of SQL statements achieves the desired results?

A. CREATE ROLE manager;

GRANT create table, create view

TO manager;

GRANT manager TO BLAKE,CLARK;

B. CREATE ROLE manager;

GRANT create table, create voew

TO manager;

GRANT manager ROLE TO BLAKE,CLARK;

C. GRANT manager ROLE TO BLAKE,CLARK;

GRANT create table, create voew

TO BLAKE CLARK;

***MISSING***

Answer: A

Explanation: Result of commands:

To see all sample questions go to: 1Z0-051 test prep

Related 1Z0-051 posts: