[Jun 2016] oracle database 11g sql fundamentals 1 1z0-051 pdf

Downloadable of 1Z0-051 practice exam materials and interactive bootcamp for Oracle certification for candidates, Real Success Guaranteed with Updated 1Z0-051 pdf dumps vce Materials. 100% PASS Oracle Database: SQL Fundamentals I exam Today!

2016 Jun 1Z0-051 Study Guide Questions:

Q71. - (Topic 1) 

Which view should a user query to display the columns associated with the constraints on a table owned by the user? 

A. USER_CONSTRAINTS 

B. USER_OBJECTS 

C. ALL_CONSTRAINTS 

D. USER_CONS_COLUMNS 

E. USER_COLUMNS 

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 


Q72. - (Topic 2) 

Examine the SQL statement that creates ORDERS table: 

CREATE TABLE orders (SER_NO NUMBER UNIQUE, ORDER_ID NUMBER, ORDER_DATE DATE NOT NULL, STATUS VARCHAR2(10) CHECK (status IN ('CREDIT', 'CASH')), PROD_ID NUMBER REFERENCES PRODUCTS(PRODUCT_ID), ORD_TOTAL NUMBER, PRIMARY KEY (order_id, order_date)); 

For which columns would an index be automatically created when you execute the above SQL statement? (Choose two.) 

A. SER_NO 

B. ORDER_ID 

C. STATUS 

D. PROD_ID 

E. ORD_TOTAL 

F. composite index on ORDER_ID and ORDER_DATE 

Answer: A,F 

Explanation: Index exist for UNIQUE and PRIMARY KEY constraints 

Incorrect Answer: BORDER_ID is neither UNIQUE nor PRIMARY KEY CSTATUS is neither UNIQUE nor PRIMARY KEY DPROD_ID is neither UNIQUE nor PRIMARY KEY EORD_TOTAL is neither UNIQUE nor PRIMARY KEY 

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


Q73. - (Topic 2) 

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


Which two tasks would require subqueries? (Choose two.) 

A. Display the minimum list price for each product status. 

B. Display all suppliers whose list price is less than 1000. 

C. Display the number of products whose list price is more than the average list price. 

D. Display the total number of products supplied by supplier 102 and have product status as 'obsolete'. 

E. Display all products whose minimum list price is more than the average list price of products and have the status 'orderable'. 

Answer: C,E 


1Z0-051  practice exam

Update 1z0-051 practice test:

Q74. - (Topic 2) 

In which two cases would you use an outer join? (Choose two.) 

A. The tables being joined have NOT NULL columns. 

B. The tables being joined have only matched data. 

C. The columns being joined have NULL values. 

D. The tables being joined have only unmatched data. 

E. The tables being joined have both matched and unmatched data. 

F. Only when the tables have a primary key/foreign key relationship. 

Answer: C,E 

Explanation: 

You use an outer join to also see rows that do not meet the join condition. 

Incorrect Answer: Ameet a join condition Bmeet a join condition Dmeet non join condition only Fdoes not take into consideration of primary key and foreign key relationship 

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


Q75. - (Topic 2) 

Examine the data in the LIST_PRICE and MIN_PRICE columns of the PRODUCTS table: 


Which two expressions give the same output? (Choose two.) 

A. NVL(NULLIF(list_price, min_price), 0) 

B. NVL(COALESCE(list_price, min_price), 0) 

C. NVL2(COALESCE(list_price, min_price), min_price, 0) 

D. COALESCE(NVL2(list_price, list_price, min_price), 0) 

Answer: B,D 

Explanation: 

Using the COALESCE Function 

The advantage of the COALESCE function over the NVL function is that the COALESCE 

function can take multiple alternate values. 

If the first expression is not null, the COALESCE function returns that expression; 

otherwise, it does a COALESCE of the remaining expressions. 

Using the COALESCE Function 

The COALESCE function returns the first non-null expression in the list. 

Syntax 

COALESCE (expr1, expr2, ... exprn) In the syntax: 

expr1 returns this expression if it is not null 

expr2 returns this expression if the first expression is null and this expression is not null 

exprn returns this expression if the preceding expressions are null Note that all expressions must be of the same data type. 


Q76. - (Topic 2) 

The STUDENT_GRADES table has these columns: 

STUDENT_ID NUMBER(12) 

SEMESTER_END DATE 

GPA NUMBER(4,3) 

The registrar requested a report listing the students' grade point averages (GPA) sorted from highest grade point average to lowest. 

Which statement produces a report that displays the student ID and GPA in the sorted order requested by the registrar? 

A. SELECT student_id, gpa FROM student_grades ORDER BY gpa ASC; 

B. SELECT student_id, gpa FROM student_grades SORT ORDER BY gpa ASC; 

C. SELECT student_id, gpa FROM student_grades SORT ORDER BY gpa; 

D. SELECT student_id, gpa FROM student_grades ORDER BY gpa; 

E. SELECT student_id, gpa FROM student_grades SORT ORDER BY gpa DESC; 

F. SELECT student_id, gpa FROM student_grades ORDER BY gpa DESC; 

Answer: F 

Explanation: 

sorted by highest to lowest is DESCENDING order 

Incorrect Answer: Aresult in ascending order Bwrong syntax with SORT keyword Cwrong syntax with SORT keyword Ddefault value for ORDER by is in ascending order Ewrong syntax with SORT keyword 

Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 2-22 


1Z0-051  practice exam

Refined 1z0-051 free dumps pdf:

Q77. - (Topic 2) 

Examine the structure of the PROMOTIONS table: 


The management wants to see a report of unique promotion costs in each promotion category. 

Which query would achieve the required result? 

A. SELECT DISTINCT promo_cost, promo_category FROM promotions; 

B. SELECT promo_category, DISTINCT promo_cost FROM promotions; 

C. SELECT DISTINCT promo_cost, DISTINCT promo_category FROM promotions; 

D. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1; 

Answer: D 


Q78. - (Topic 1) 

View the Exhibit and examine the structure of the PROMOTIONS, SALES, and CUSTOMER tables. 


You need to generate a report showing the promo name along with the customer name for all products that were sold during their promo campaign and before 30th October 2007. 

You issue the following query: 

Which statement is true regarding the above query? 

A. It executes successfully and gives the required result. 

B. It executes successfully but does not give the required result. 

C. It produces an error because the join order of the tables is incorrect. 

D. It produces an error because equijoin and nonequijoin conditions cannot be used in the same SELECT statement. 

Answer: B 


Q79. - (Topic 1) 

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


There is only one customer with the CUST_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 (1,'10-mar-2007', 'direct', (SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND credit_limit=600), 1000); 

B. 

INSERT INTO orders (order_id,order_date,order_mode, 

(SELECT customer_id 

FROM customers 

WHERE cust_last_name='Roberts' AND 

credit_limit=600),order_total) 

VALUES(1,'10-mar-2007', 'direct', &&customer_id, 1000); 

C. 

INSERT INTO(SELECT o.order_id, o.order_date,o.order_mode,c.customer_id, o.order_total FROM orders o, customers c WHERE o.customer_id = c.customer_id AND c.cust_last_name='Roberts' ANDc.credit_limit=600 ) VALUES (1,'10-mar-2007', 'direct',(SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND credit_limit=600), 1000); 

D. 

INSERT INTO orders (order_id,order_date,order_mode, 

(SELECT customer_id 

FROM customers 

WHERE cust_last_name='Roberts' AND 

credit_limit=600),order_total) 

VALUES(1,'10-mar-2007', 'direct', &customer_id, 1000); 

Answer: A 


Q80. - (Topic 2) 

You want to display the date for the first Monday of the next month and issue the following command: 

SQL>SELECT TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE),'MON'), 'dd "is the first Monday for"fmmonth rrrr') FROM DUAL; 

What is the outcome? 

A. It executes successfully and returns the correct result. 

B. It executes successfully but does not return the correct result. 

C. It generates an error because TO_CHAR should be replaced with TO_DATE. 

D. It generates an error because rrrr should be replaced by rr in the format string. 

E. It generates an error because fm and double quotation marks should not be used in the format string. 

Answer: A 

Explanation: 

NEXT_DAY(date, 'char'): Finds the date of the next specified day of the week ('char') following date. The value of char may be a number representing a day or a character string. 

LAST_DAY(date): Finds the date of the last day of the month that contains date The second innermost function is evaluated next. TO_CHAR('28-OCT-2009', 'fmMonth') converts the given date based on the Month format mask and returns the character string October. The fm modifier trims trailing blank spaces from the name of the month. 



see more 1Z0-051 dumps