This page was exported from Braindump2go Free Exam Dumps with PDF and VCE Collection [ https://www.mcitpdump.com ] Export date:Thu Apr 25 6:59:11 2024 / +0000 GMT ___________________________________________________ Title: [2016-June-NEW]Free Download Braindump2go 1Z0-051 Exam PDF and VCE Dumps[NQ51-NQ60] --------------------------------------------------- 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 51 - NEW QUESTION 60: QUESTION 51View the Exhibit and examine the data in the PROMOTIONS table.You need to display all promo categories that do not have 'discount' in their subcategory.Which two SQL statements give the required result? (Choose two.)  A.    SELECT promo_categoryFROM promotionsMINUSSELECT promo_categoryFROM promotionsWHERE promo_subcategory = 'discount';B.    SELECT promo_categoryFROM promotionsINTERSECTSELECT promo_categoryFROM promotionsWHERE promo_subcategory = 'discount';C.    SELECT promo_categoryFROM promotionsMINUSSELECT promo_categoryFROM promotionsWHERE promo_subcategory <> 'discount';D.    SELECT promo_categoryFROM promotionsINTERSECTSELECT promo_categoryFROM promotionsWHERE promo_subcategory <> 'discount'; Answer: AD QUESTION 52View the Exhibit and examine the structure of the CUSTOMERS and CUST_HISTORY tables.The CUSTOMERS table contains the current location of all currently active customers. The CUST_HISTORY table stores historical details relating to any changes in the location of all current as well as previous customers who are no longer active with the company.You need to find those customers who have never changed their address.Which SET operator would you use to get the required output?  A.    MINUSB.    UNIONC.    INTERSECTD.    UNION ALL Answer: A QUESTION 53Which statement is true regarding the UNION operator? A.    The number of columns selected in all SELECT statements need to be the sameB.    Names of all columns must be identical across all SELECT statementsC.    By default, the output is not sortedD.    NULL values are not ignored during duplicate checking Answer: AExplanation:The SQL UNION query allows you to combine the result sets of two or more SQL SELECT statements. It removes duplicate rows between the various SELECT statements. Each SQL SELECT statement within the UNION query must have the same number of fields in the result sets with similar data types. QUESTION 54View 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 productsINTERSECTSELECT prod_id FROM sales;B.    SELECT prod_id FROM productsMINUSSELECT prod_id FROM sales;C.    SELECT DISTINCT p.prod_idFROM products p JOIN sales sON p.prod_id=s.prod_id;D.    SELECT DISTINCT p.prod_idFROM products p JOIN sales sON p.prod_id <> s.prod_id; Answer: AC QUESTION 55View the Exhibit and evaluate structures of the SALES, PRODUCTS, and COSTS tables. Evaluate the following SQL statement: Which statement is true regarding the above compound query? A.    It produces an error.B.    It shows products that were sold and have a cost recorded.C.    It shows products that were sold but have no cost recorded.D.    It shows products that have a cost recorded irrespective of sales. Answer: C QUESTION 56Evaluate the following SQL statement:SQL> SELECT promo_id, promo_categoryFROM promotionsWHERE promo_category = 'Internet' ORDER BY 2 DESCUNIONSELECT promo_id, promo_categoryFROM promotionsWHERE promo_category = 'TV'UNIONSELECT promo_id, promo_categoryFROM promotionsWHERE promo_category ='Radio';Which statement is true regarding the outcome of the above query? A.    It executes successfully and displays rows in the descending order of PROMO_CATEGORY.B.    It produces an error because positional notation cannot be used in the ORDER BY clause with SET operators.C.    It executes successfully but ignores the ORDER BY clause because it is not located at the end of the compound statement.D.    It produces an error because the ORDER BY clause should appear only at the end of a compound query-that is, with the last SELECT statement. Answer: DExplanation:Using the ORDER BY Clause in Set OperationsThe ORDER BY clause can appear only once at the end of the compound query. Component queries cannot have individual ORDER BY clauses. The ORDER BY clause recognizes only the columns of the first SELECT query.By default, the first column of the first SELECT query is used to sort the output in an ascending order. QUESTION 57Evaluate the following SQL statement:SQL> SELECT cust_id, cust_last_name "Last Name"FROM customersWHERE country_id = 10UNIONSELECT cust_id CUST_NO, cust_last_nameFROM customersWHERE country_id = 30;Which ORDER BY clauses are valid for the above query? (Choose all that apply.) A.    ORDER BY 2,1B.    ORDER BY CUST_NOC.    ORDER BY 2,cust_idD.    ORDER BY "CUST_NO"E.    ORDER BY "Last Name" Answer: ACEExplanation:Using the ORDER BY Clause in Set Operations- The ORDER BY clause can appear only once at the end of the compound query.- Component queries cannot have individual ORDER BY clauses.- The ORDER BY clause recognizes only the columns of the first SELECT query.- By default, the first column of the first SELECT query is used to sort the output in an ascending order. QUESTION 58View the Exhibit and examine the structure of the ORDERS and CUSTOMERS tables.Evaluate the following SQL command:SQL> SELECT o.order_id, c.cust_name, o.order_total, c.credit_limitFROM orders o JOIN customers cUSING (customer_id)WHERE o.order_total > c.credit_limitFOR UPDATEORDER BY o.order_id;Which two statements are true regarding the outcome of the above query? (Choose two.)  A.    It locks all the rows that satisfy the condition in the statement.B.    It locks only the columns that satisfy the condition in both the tables.C.    The locks are released only when a COMMIT or ROLLBACK is issued.D.    The locks are released after a DML statement is executed on the locked rows. Answer: ACExplanation:FOR UPDATE Clause in a SELECT StatementLocks the rows in the EMPLOYEES table where job_id is SA_REP. Lock is released only when you issue a ROLLBACK or a COMMIT. If the SELECT statement attempts to lock a row that is locked by another user, the database waits until the row is available, and then returns the results of the SELECTstatementSELECT employee_id, salary, commission_pct, job_idFROM employeesWHERE job_id = 'SA_REP'FOR UPDATEORDER BY employee_id; QUESTION 59Which statements are true regarding the FOR UPDATE clause in a SELECT statement? (Choose all that apply.) A.    It locks only the columns specified in the SELECT list.B.    It locks the rows that satisfy the condition in the SELECT statement.C.    It can be used only in SELECT statements that are based on a single table.D.    It can be used in SELECT statements that are based on a single or multiple tables.E.    After it is enforced by a SELECT statement, no other query can access the same rows until a COMMIT or ROLLBACK is issued. Answer: BDExplanation:FOR UPDATE Clause in a SELECT StatementLocks the rows in the EMPLOYEES table where job_id is SA_REP. Lock is released only when you issue a ROLLBACK or a COMMIT. If the SELECT statement attempts to lock a row that is locked by another user, the database waits until the row is available, and then returns the results of the SELECT statement.FOR UPDATE Clause in a SELECT StatementWhen you issue a SELECT statement against the database to query some records, no locks are placed on the selected rows. In general, this is required because the number of records locked at any given time is (by default) kept to the absolute minimum: only those records that have been changed but not yet committed are locked. Even then, others will be able to read those records as they appeared before the change (the "before image" of the data). There are times, however, when you may want to lock a set of records even before you change them in your program. Oracle offers the FOR UPDATE clause of the SELECT statement to perform this locking. When you issue a SELECT...FOR UPDATE statement, the relational database management system (RDBMS) automatically obtains exclusive row-level locks on all the rows identified by the SELECT statement, thereby holding the records "for your changes only." No one else will be able to change any of these records until you perform a ROLLBACK or a COMMIT. You can append the optional keyword NOWAIT to the FOR UPDATE clause to tell the Oracle server not to wait if the table has been locked by another user. In this case, control will be returned immediately to your program or to your SQL Developer environment so that you can perform other work, or simply wait for a period of time before trying again. Without the NOWAIT clause, your process will block until the table is available, when the locks are released by the other user through the issue of a COMMIT or a ROLLBACK command. QUESTION 60View the Exhibit and examine the structure of the CUSTOMERS table.NEW_CUSTOMERS is a new table with the columns CUST_ID, CUST_NAME and CUST_CITY that have the same data types and size as the corresponding columns in the CUSTOMERS table.Evaluate the following INSERT statement: The INSERT statement fails when executed. What could be the reason?  A.    The VALUES clause cannot be used in an INSERT with a subquery.B.    Column names in the NEW_CUSTOMERS and CUSTOMERS tables do not match.C.    The WHERE clause cannot be used in a subquery embedded in an INSERT statement.D.    The total number of columns in the NEW_CUSTOMERS table does not match the total number of columns in the CUSTOMERS table. Answer: AExplanation:Copying Rows from Another TableWrite your INSERT statement with a subquery:Do not use the VALUES clause.Match the number of columns in the INSERT clause to those in the subquery. Inserts all the rows returned by the subquery in the table, sales_reps. 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! --------------------------------------------------- Images: --------------------------------------------------- --------------------------------------------------- Post date: 2016-06-03 04:01:42 Post date GMT: 2016-06-03 04:01:42 Post modified date: 2016-06-03 04:01:42 Post modified date GMT: 2016-06-03 04:01:42 ____________________________________________________________________________________________ Export of Post and Page as text file has been powered by [ Universal Post Manager ] plugin from www.gconverters.com