[2016-June-NEW]2016 Latest Braindump2go 1Z0-051 Questions Instant Download[NQ31-NQ40] June 3, 2016 lead2pass 2016 June Oracle Official: 1Z0-051: Oracle Database 11g: SQL Fundamentals I Exam Questions New Updated Today! Braindump2go.com Offers 1Z0-051 PDF and VCE Dumps 303q for Free Downloading! NEW QUESTION 31 – NEW QUESTION 40: QUESTION 31View 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 mON e.manager_id = m.employee_id;Which JOIN option can be used in the blank in the above query to get the required output?A. only inner JOINB. only FULL OUTER JOINC. only LEFT OUTER JOIND. only RIGHT OUTER JOIN Answer: C QUESTION 32View the Exhibit and examine the structure of the PRODUCT, COMPONENT, and PDT_COMP tables.In PRODUCT table, PDTNO is the primary key.In COMPONENT table, COMPNO is the primary key.In PDT_COMP table, (PDTNO,COMPNO) is the primary key, PDTNO is the foreign key referencingPDTNO in PRODUCT table and COMPNO is the foreign key referencing the COMPNO in COMPONENT table.You want to generate a report listing the product names and their corresponding component names, if the component names and product names exist.Evaluate the following query:SQL>SELECT pdtno,pdtname, compno,compnameFROM product _____________ pdt_compUSING (pdtno) ____________ component USING(compno)WHERE compname IS NOT NULL;Which combination of joins used in the blanks in the above query gives the correct output? A. JOIN; JOINB. FULL OUTER JOIN; FULL OUTER JOINC. RIGHT OUTER JOIN; LEFT OUTER JOIND. LEFT OUTER JOIN; RIGHT OUTER JOIN Answer: C QUESTION 33View the Exhibit and examine the structure of the SALES and PRODUCTS tables.In the SALES table, PROD_ID is the foreign key referencing PROD_ID in the PRODUCTS table,You want to list each product ID and the number of times it has been sold.Evaluate the following query:SQL>SELECT p.prod_id, COUNT(s.prod_id)FROM products p _____________ sales sON p.prod_id = s.prod_idGROUP BY p.prod_id;Which two JOIN options can be used in the blank in the above query to get the required output? (Choose two.) A. JOINB. FULL OUTER JOINC. LEFT OUTER JOIND. RIGHT OUTER JOIN Answer: BC QUESTION 34Which two statements about sub queries are true? (Choose two.) A. A sub query should retrieve only one row.B. A sub query can retrieve zero or more rows.C. A sub query can be used only in SQL query statements.D. Sub queries CANNOT be nested by more than two levels.E. A sub query CANNOT be used in an SQL query statement that uses group functions.F. When a sub query is used with an inequality comparison operator in the outer SQL statement, the column list in the SELECT clause of the sub query should contain only one column. Answer: BFExplanation: sub query can retrieve zero or more rows, sub query is used with an inequality comparison operator in the outer SQL statement, and the column list in the SELECT clause of the sub query should contain only one column.Incorrect answer:Asub query can retrieve zero or more rowsCsub query is not SQL query statementDsub query can be nestedEgroup function can be use with sub query QUESTION 35Where can subqueries be used? (Choose all that apply.) A. field names in the SELECT statementB. the FROM clause in the SELECT statementC. the HAVING clause in the SELECT statementD. the GROUP BY clause in the SELECT statementE. the WHERE clause in only the SELECT statementF. the WHERE clause in SELECT as well as all DML statements Answer: ABCFExplanation: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 clauseThe following are optional clauses:WHEREGROUP BYHAVINGThe 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. QUESTION 36Which 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: ABFExplanation:SUBQUERIES can be used in the SELECT list and in the FROM, WHERE, and HAVING clausesof a query.A subquery can have any of the usual clauses for selection and projection. The following are required clauses:A SELECT listA FROM clauseThe following are optional clauses:WHEREGROUP BYHAVINGThe 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. QUESTION 37View the Exhibits and examine PRODUCTS and SALES tables.You issue the following query to display product name and the number of times the product has been sold:SQL>SELECT p.prod_name, i.item_cntFROM (SELECT prod_id, COUNT(*) item_cntFROM salesGROUP BY prod_id) i RIGHT OUTER JOIN products pON i.prod_id = p.prod_id;What happens when the above statement is executed? A. The statement executes successfully and produces the required output.B. The statement produces an error because ITEM_CNT cannot be displayed in the outer query.C. The statement produces an error because a subquery in the FROM clause and outer-joins cannot be used together.D. The statement produces an error because the GROUP BY clause cannot be used in a subquery in the FROM clause. Answer: A QUESTION 38View 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 havethe status ‘orderable’. Answer: CE QUESTION 39Which statement is true regarding subqueries? A. The LIKE operator cannot be used with single- row subqueries.B. The NOT IN operator is equivalent to IS NULL with single- row subqueries.C. =ANY and =ALL operators have the same functionality in multiple- row subqueries.D. The NOT operator can be used with IN, ANY, and ALL operators in multiple- row subqueries. Answer: DExplanation:Using the ANY Operator in Multiple-Row SubqueriesThe ANY operator (and its synonym, the SOME operator) compares a value to each value returned by a subquery.<ANY means less than the maximum.>ANY means more than the minimum.=ANY is equivalent to INUsing the ALL Operator in Multiple-Row SubqueriesThe ALL operator compares a value to every value returned by a subquery.>ALL means more than the maximum and<ALL means less than the minimum.The NOT operator can be used with IN, ANY, and ALL operators. QUESTION 40Which three statements are true about multiple-row subqueries? (Choose three.) A. They can contain a subquery within a subquery.B. They can return multiple columns as well as rows.C. They cannot contain a subquery within a subquery.D. They can return only one column but multiple rows.E. They can contain group functions and GROUP BY and HAVING clauses.F. They can contain group functions and the GROUP BY clause, but not the HAVING clause. Answer: ABE 2016 Valid Oracle 1Z0-051 Exam Study Materials: 1.| Latest 1Z0-051 PDF and VCE Dumps 303Q&As from Braindump2go: http://www.braindump2go.com/1z0-051.html [100% Exam Pass Guaranteed!] 2.| NEW 1Z0-051 Exam Questions and Answers: https://drive.google.com/folderview?id=0B75b5xYLjSSNVGxLT202clFMbjA&usp=sharing MORE Practice is the Most Important IF You want to PASS 1Z0-051 Exam 100%! ————— Braindump2go.com————— Pass All IT Exams at the first Try!