Exam Code: 1Z0-051 (Practice Exam Latest Test Questions VCE PDF)
Exam Name: Oracle Database: SQL Fundamentals I
Certification Provider: Oracle
Free Today! Guaranteed Training- Pass 1Z0-051 Exam.

2016 May 1Z0-051 Study Guide Questions:

Q131. - (Topic 1) 

Examine the structure of the EMPLOYEES table: 


You want to create a SQL script file that contains an INSERT statement. When the script is run, the INSERT statement should insert a row with the specified values into the EMPLOYEES table. The INSERT statement should pass values to the table columns as specified below:

 

Which INSERT statement meets the above requirements? 

A. INSERT INTO employees VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did); 

B. INSERT INTO employees VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did IN (20,50)); 

C. INSERT INTO (SELECT * FROM employees WHERE department_id IN (20,50)) VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did); 

D. INSERT INTO (SELECT * FROM employees WHERE department_id IN (20,50) WITH CHECK OPTION) 

VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did); 

E. INSERT INTO (SELECT * FROM employees WHERE (department_id = 20 AND department_id = 50) WITH CHECK OPTION ) VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did); 

Answer: D 


Q132. - (Topic 1) 

Which two statements are true regarding single row functions? (Choose two.) 

A. They can be nested only to two levels 

B. They always return a single result row for every row of a queried table 

C. Arguments can only be column values or constant 

D. They can return a data type value different from the one that is referenced 

E. They accept only a single argument 

Answer: B,D 

Explanation: 

A function is a program written to optionally accept input parameters, perform an operation, or return a single value. A function returns only one value per execution. Three important components form the basis of defining a function. The first is the input parameter list. It specifies zero or more arguments that may be passed to a function as input for processing. These arguments or parameters may be of differing data types, and some are mandatory while others may be optional. The second component is the data type of its resultant value. Upon execution, only one value is returned by the function. The third encapsulates the details of the processing performed by the function and contains the program code that optionally manipulates the input parameters, performs calculations and operations, and generates a return value. 


Q133. - (Topic 2) 

View the Exhibits and examine the structures of the COSTS and PROMOTIONS tables. 


Evaluate the following SQL statement: 

SQL> SELECT prod_id FROM costs WHERE promo_id IN (SELECT promo_id FROM promotions WHERE promo_cost < ALL (SELECT MAX(promo_cost) FROM promotions GROUP BY (promo_end_datepromo_ begin_date))); 

What would be the outcome of the above SQL statement? 

A. It displays prod IDs in the promo with the lowest cost. 

B. It displays prod IDs in the promos with the lowest cost in the same time interval. 

C. It displays prod IDs in the promos with the highest cost in the same time interval. 

D. It displays prod IDs in the promos with cost less than the highest cost in the same time interval. 

Answer: D 


2passeasy.com

Refresh oracle database 11g sql fundamentals 1 1z0-051:

Q134. - (Topic 1) 

Which two statements are true regarding constraints? (Choose two.) 

A. A constraint can be disabled even if the constraint column contains data 

B. A constraint is enforced only for the INSERT operation on a table 

C. A foreign key cannot contain NULL values 

D. All constraints can be defined at the column level as well as the table level 

E. A columns with the UNIQUE constraint can contain NULL values 

Answer: A,E 


Q135. - (Topic 1) 

Which statement is true regarding the COALESCE function? 

A. It can have a maximum of five expressions in a list. 

B. It returns the highest NOT NULL value in the list for all rows. 

C. It requires that all expressions in the list must be of the same data type. 

D. It requires that at least one of the expressions in the list must have a NOT NULL value. 

Answer: C 

Explanation: 

The COALESCE Function The COALESCE function returns the first nonnull value from its parameter list. If all its parameters are null, then null is returned. The COALESCE function takes two mandatory parameters and any number of optional parameters. The syntax is COALESCE(expr1, expr2,…,exprn), where expr1 is returned if it is not null, else expr2 if it is not null, and so on. COALESCE is a general form of the NVL function, as the following two equations illustrate: COALESCE(expr1,expr2) = NVL(expr1,expr2) COALESCE(expr1,expr2,expr3) = NVL(expr1,NVL(expr2,expr3)) The data type COALESCE returns if a not null value is found is the same as that of the first not null parameter. To avoid an “ORA-00932: inconsistent data types” error, all not null parameters must have data types compatible with the first not null parameter. 


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


2passeasy.com

Tested 1z0-051 dumps free pdf:

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


Q138. - (Topic 2) 

What is true about updates through a view? 

A. You cannot update a view with group functions. 

B. When you update a view group functions are automatically computed. 

C. When you update a view only the constraints on the underlying table will be in effect. 

D. When you update a view the constraints on the views always override the constraints on the underlying tables. 

Answer: A 


Q139. - (Topic 2) 

View the Exhibits and examine the structures of the PRODUCTS and SALES tables. Which two SQL statements would give the same output? (Choose two.) 

A. 

SELECT prod_id FROM products INTERSECT SELECT prod_id FROM sales; 

B. 

SELECT prod_id FROM products MINUS SELECT prod_id FROM sales; 

C. 

SELECT DISTINCT p.prod_id FROM products p JOIN sales s ON p.prod_id=s.prod_id; 

D. 

SELECT DISTINCT p.prod_id FROM products p JOIN sales s ON p.prod_id <> s.prod_id; 

Answer: A,C 


Q140. - (Topic 1) 

Examine the statement: 

Create synonym emp for hr.employees; 

What happens when you issue the statement? 

A. An error is generated. 

B. You will have two identical tables in the HR schema with different names. 

C. You create a table called employees in the HR schema based on you EMP table. 

D. You create an alternative name for the employees table in the HR schema in your own schema. 

Answer: D