[May 2016] exam 1z0-051

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:

Q171. - (Topic 1) 

Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables: 


Which MERGE statement is valid? 

A. 

MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET 

B. name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT value S(e.employee_id, e.first_name ||', '||e.last_name); 

C. 

MERGE new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET 

D. name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT valueS(e.employee_id, e.first_name ||', '||e.last_name); 

E. 

MERGE INTO new_employees cUSING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET 

F. name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT value S(e.employee_id, e.first_name ||', '||e.last_name); 

G. 

MERGE new_employees c FROM employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET 

H. name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT INTO new_employees valueS(e.employee_id, e.first_name ||', '||e.last_name); 

Answer: A 

Explanation: Explanation: this is the correct MERGE statement syntax 

Incorrect Answer: Bit should MERGE INTO table_name Cit should be WHEN MATCHED THEN Dit should MERGE INTO table_name Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 8-29 


Q172. - (Topic 2) 

Examine the description of the EMPLOYEES table: 

EMP_ID NUMBER(4) NOT NULL LAST_NAME VARCHAR2(30) NOT NULL FIRST_NAME VARCHAR2(30) DEPT_ID NUMBER(2) 

Which statement produces the number of different departments that have employees with last name Smith? 

A. SELECT COUNT(*) FROM employees WHERE last_name='Smith' 

B. SELECT COUNT (dept_id) FROM employees WHERE last_name='Smith' 

C. SELECT DISTINCT(COUNT(dept_id)) FROM employees WHERE last_name='Smith' 

D. SELECT COUNT(DISTINCT dept_id) FROM employees WHERE last_name='Smith' 

E. SELECT UNIQUE(dept_id) FROM employees WHERE last_name='Smith' 

Answer: D 


Q173. - (Topic 1) 

You need to design a student registration database that contains several tables storing academic information. 

The STUDENTS table stores information about a student. The STUDENT_GRADES table stores information about the student's grades. Both of the tables have a column named STUDENT_ID. The STUDENT_ID column in the STUDENTS table is a primary key. 

You need to create a foreign key on the STUDENT_ID column of the STUDENT_GRADES table that points to the STUDENT_ID column of the STUDENTS table. Which statement creates the foreign key? 

A. CREATE TABLE student_grades (student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3), CONSTRAINT student_id_fk REFERENCES (student_id) FOREIGN KEY students(student_id)); 

B. CREATE TABLE student_grades(student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3), student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id)); 

C. CREATE TABLE student_grades(student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3), CONSTRAINT FOREIGN KEY (student_id) REFERENCES students(student_id)); 

D. CREATE TABLE student_grades(student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3), CONSTRAINT student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id)); 

Answer: D 

Explanation: CONSTRAINT name FOREIGN KEY (column_name) REFERENCES table_name (column_name); 

Incorrect Answer: Ainvalid syntax Binvalid syntax Cinvalid syntax 

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


1Z0-051  exam guide

Abreast of the times 1z0-051 books:

Q174. - (Topic 1) 

Which two statements are true regarding sub queries? (Choose two.) 

A. A sub query can retrieve zero or more rows. 

B. Only two sub queries can be placed at one level. 

C. A sub query can be used only in SQL query statements. 

D. A sub query can appeal* on either side of a comparison operator. 

E. There is no limit on the number of sub query levels in the WHERE clause of a SELECT statement. 

Answer: A,D 


Q175. - (Topic 1) 

View the Exhibits and examine the structures of the PRODUCTS SALES and CUSTOMERS tables. 


You need to generate a report that gives details of the customer's last name, name of the product, and the quantity sold for all customers in Tokyo'. Which two queries give the required result? (Choose two.) 

A. 

SELECT c.cust_last_name,p.prod_name, s.quantity_sold FROM sales s JOIN products p 

USING(prod_id) 

JOIN customers c 

USING(cust_id) 

WHERE c.cust_city='Tokyo' 

B. 

SELECT c.cust_last_name, p.prod_name, s.quantity_sold 

FROM products p JOIN sales s JOIN customers c 

ON(p.prod_id=s.prod_id) 

ON(s.cust_id=c.cust_id) 

WHERE c.cust_city='Tokyo' 

C. 

SELECT c.cust_last_name, p.prod_name, s.quantity_sold 

FROM products p JOIN sales s 

ON(p.prod_id=s.prod_id) 

JOIN customers c 

ON(s.cust_id=c.cust_id) 

AND c.cust_city='Tokyo' 

D. 

SELECT c.cust_id,c.cust_last_name,p.prod_id, p.prod_name, s.quantity_sold FROM 

products p JOIN sales s 

USING(prod_id) 

JOIN customers c 

USING(cust_id) 

WHERE c.cust_city='Tokyo' 

Answer: A,C 


Q176. - (Topic 1) 

Evaluate the following SQL statements: Exhibit: 


You issue the following command to create a view that displays the IDs and last names of the sales staff in the organization. 

Exhibit: 


Which two statements are true regarding the above view? (Choose two.) 

A. It allows you to update job IDs of the existing sales staff to any other job ID in the EMPLOYEES table 

B. It allows you to delete details of the existing sales staff from the EMPLOYEES table 

C. It allows you to insert rows into the EMPLOYEES table 

D. It allows you to insert IDs, last names, and job IDs of the sales staff from the view if it is used in multitable INSERT statements 

Answer: B,D 


1Z0-051  exam guide

Vivid 1z0-051 study material pdf:

Q177. - (Topic 1) 

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


The above query generates an error on execution. 

Which clause in the above SQL statement causes the error? 


A. WHERE 

B. SELECT 

C. GROUP BY 

D. ORDER BY 

Answer: C 


Q178. - (Topic 2) 

Which SQL statement would you use to remove a view called EMP_DEPT_VU from your schema? 

A. DROP emp_dept_vu; 

B. DELETE emp_dept_vu; 

C. REMOVE emp_dept_vu; 

D. DROP VIEW emp_dept_vu; 

E. DELETE VIEW emp_dept_vu; 

F. REMOVE VIEW emp_dept_vu; 

Answer: D 

Explanation: 

DROP VIEW viewname; 

Incorrect Answer: ANot a valid drop view statement BNot a valid drop view statement CNot a valid drop view statement ENot a valid drop view statement FNot a valid drop view statement 

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


Q179. - (Topic 2) 

View the Exhibit and examine the data in the EMPLOYEES table: 

You want to display all the employee names and their corresponding manager names. 

Evaluate the following query: 

SQL> SELECT e.employee_name "EMP NAME", m.employee_name "MGR NAME" 

FROM employees e ______________ employees m 

ON e.manager_id = m.employee_id; 

Which JOIN option can be used in the blank in the above query to get the required output? 

Exhibit: 

A. only inner JOIN 

B. only FULL OUTER JOIN 

C. only LEFT OUTER JOIN 

D. only RIGHT OUTER JOIN 

Answer: C 


Q180. - (Topic 1) 

Which arithmetic operations can be performed on a column by using a SQL function that is built into Oracle database? (Choose three.) 

A. addition 

B. subtraction 

C. raising to a power 

D. finding the quotient 

E. finding the lowest value 

Answer: A,C,E 



see more 1Z0-051 dumps