It is more faster and easier to pass the Oracle 1Z0-051 exam by using Download Oracle Oracle Database: SQL Fundamentals I questuins and answers. Immediate access to the Up to the minute 1Z0-051 Exam and find the same core area 1Z0-051 questions with professionally verified answers, then PASS your exam with a high score now.

2017 Jan 1z0-051 dumps free pdf:

Q1. - (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:


Q2. - (Topic 2) 

The DBA issues this SQL command: 

CREATE USER Scott 

IDENTIFIED by tiger; 

What privileges does the user Scott have at this point? 

A. No privileges. 

B. Only the SELECT privilege. 

C. Only the CONNECT privilege. 

D. All the privileges of a default user. 

Answer:

Explanation: 

There are no privileges for the user Scott at this point. They are not added themselves to 

the user immediately after creation. The DBA needs to grant all privileges explicitly. 

Incorrect Answers 

B:There are no privileges for the user Scott at this point. SELECT privilege needs to be 

added to the user Scott. 

C:There are no privileges for the user Scott at this point. CONNECT privilege needs to be 

added to the user Scott. 

D:There is no default user in Oracle. 

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

Chapter 8: User Access in Oracle 


Q3. - (Topic 1) 

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

Using the CUSTOMERS table, you need to generate a report that shown the average credit limit for customers in WASHINGTON and NEW YORK. 

Which SQL statement would produce the required result? 

A. 

SELECT cust_city, AVG(cust_credit_limit) 

FROM customers 

WHERE cust_city IN ('WASHINGTON','NEW YORK') 

GROUP BY cust_credit_limit, cust_city; 

B. 

SELECT cust_city, AVG(cust_credit_limit) 

FROM customers 

WHERE cust_city IN ('WASHINGTON','NEW YORK') 

GROUP BY cust_city,cust_credit_limit; 

C. 

SELECT cust_city, AVG(cust_credit_limit) 

FROM customers 

WHERE cust_city IN ('WASHINGTON','NEW YORK') 

GROUP BY cust_city; 

D. 

SELECT cust_city, AVG(NVL(cust_credit_limit,0)) 

FROM customers 

WHERE cust_city IN ('WASHINGTON','NEW YORK'); 

Answer:

Explanation: 

Creating Groups of Data: GROUP BY Clause Syntax You can use the GROUP BY clause to divide the rows in a table into groups. You can then use the group functions to return summary information for each group. In the syntax: group_by_expression Specifies the columns whose values determine the basis for grouping rows Guidelines 

If you include a group function in a SELECT clause, you cannot select individual results as well, unless the individual column appears in the GROUP BY clause. You receive an error message if you fail to include the column list in the GROUP BY clause. 

Using a WHERE clause, you can exclude rows before dividing them into groups. 

You must include the columns in the GROUP BY clause. 

You cannot use a column alias in the GROUP BY clause. 


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


Q5. - (Topic 2) 

Which describes the default behavior when you create a table? 

A. The table is accessible to all users. 

B. Tables are created in the public schema. 

C. Tables are created in your schema. 

D. Tables are created in the DBA schema. 

E. You must specify the schema when the table is created. 

Answer:

Explanation: 

sorted by highest to lowest is DESCENDING order 

Incorrect Answer: Agrant the table privilege to PUBLIC Blogin as sysoper Dlogin as DBA or sysdba Eno such option is allow. 

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


Rebirth free dumps for 1z0-051:

Q6. - (Topic 1) 

View the Exhibit and examine the structure of the CUSTOMERS table. Evaluate the following SQL statement: 

Which statement is true regarding the outcome of the above query? 

A. It executes successfully. 

B. It returns an error because the BETWEEN operator cannot be used in the HAVING clause. 

C. It returns an error because WHERE and HAVING clauses cannot be used in the same SELECT statement. 

D. It returns an error because WHERE and HAVING clauses cannot be used to apply conditions on the same column. 

Answer:


Q7. - (Topic 1) 

You work as a database administrator at ABC.com. You study the exhibit carefully. Exhibit 

Using the PROMOTIONS table, you need to display the names of all promos done after 

January 1, 2001 starting with the latest promo. 

Which query would give the required result? (Choose all that apply.) 

A. SELECT promo_name,promo_begin_date 

FROM promotions 

WHERE promo_begin_date > '01-JAN-01' 

ORDER BY 1 DESC; 

B. SELECT promo_name,promo_begin_date "START DATE" 

FROM promotions 

WHERE promo_begin_date > '01-JAN-01' 

ORDER BY "START DATE" DESC; 

C. SELECT promo_name,promo_begin_date 

FROM promotions 

WHERE promo_begin_date > '01-JAN-01' 

ORDER BY 2 DESC; 

D. SELECT promo_name,promo_begin_date 

FROM promotions 

WHERE promo_begin_date > '01-JAN-01' 

ORDER BY promo_name DESC; 

Answer: B,C 


Q8. - (Topic 2) 

Examine the structure of the PROMOS table: 

You want to display the list of promo names with the message 'Same Day' for promos that started and ended on the same day. 

Which query gives the correct output? 

A. SELECT promo_name, NVL(NULLIF(promo_start_date, promo_end_date), 'Same Day') 

FROM promos; 

B. SELECT promo_name, NVL(TRUNC(promo_end_date - promo_start_date), 'Same 

Day') FROM promos; 

C. SELECT promo_name, NVL2(TO_CHAR(TRUNC(promo_end_date-promo_start_date)), 

NULL,'Same Day') 

FROM promos; 

D. SELECT promo_name, DECODE((NULLIF(promo_start_date, promo_end_date)), 

NULL,'Same day') FROM promos; 

Answer:

Explanation: 

The NULLIF Function The NULLIF function tests two terms for equality. If they are equal the function returns a null, else it returns the first of the two terms tested. The NULLIF function takes two mandatory parameters of any data type. The syntax is NULLIF(ifunequal, comparison_term), where the parameters ifunequal and comparison_term are compared. If they are identical, then NULL is returned. If they differ, the ifunequal parameter is returned ANSWER A - date and String incompatibl;a datatypes for NVL function The Date TRUNC Function The date TRUNC function performs a truncation operation on a date value based on a specified date precision format. The date TRUNC function takes one mandatory and one optional parameter. Its syntax is TRUNC(source date, [date precision format]). The source date parameter represents any value that can be implicitly converted into a date item. The date precision format parameter specifies the degree of truncation and is optional. If it is absent, the default degree of truncation is day. This means that any time component 


Q9. - (Topic 1) 

View the Exhibit for the structure of the STUDENT and FACULTY tables. 

You need to display the faculty name followed by the number of students handled by the faculty at the base location. Examine the following two SQL statements: 

Which statement is true regarding the outcome? 

A. Only statement 1 executes successfully and gives the required result. 

B. Only statement 2 executes successfully and gives the required result. 

C. Both statements 1 and 2 execute successfully and give different results. 

D. Both statements 1 and 2 execute successfully and give the same required result. 

Answer:


Q10. - (Topic 2) 

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

Renske Ladwig Jason Mallin Samuel McCain Allan MCEwen Irene Mikkilineni Julia Nayer 

You need to display customers' second names where the second name starts with "Mc" or "MC." 

Which query gives the required output? 

A. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1) 

FROM customers 

WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1))='Mc' 

B. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1) 

FROM customers 

WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) LIKE 'Mc%' 

C. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1) 

FROM customers 

WHERE SUBSTR(cust_name, INSTR(cust_name,' ')+1) LIKE INITCAP('MC%'); 

D. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1) 

FROM customers 

WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) = INITCAP('MC%'); 

Answer: