Q1Question 1Examine the structure of the MEMBERS table below. Examine the SQL statement:SQL > SELECT city, last_name LNAME FROM MEMBERS ORDER BY 1, LNAME DESC; What would be the result execution? (Choose the best answer.) AIt displays all cities in descending order, within which the last names are further sorted in descending order. BIt fails because a column alias cannot be used in the ORDER BY clause. CIt fails because a column number and a column alias cannot be used together in the ORDER BY clause. DIt displays all cities in ascending order, within which the last names are further sorted in descending order. Answer and explanation Correct answer: D D
Q2Question 2Multiple answersView the exhibit and examine the structure of the CUSTOMERS table. Which two tasks would require subqueries or joins to be executed in a single statement? (Choose two.) Afinding the number of customers, in each city, whose credit limit is more than the average credit limit of all the customers Bfinding the average credit limit of male customers residing in 'Tokyo' or 'Sydney' Clisting of customers who do not have a credit limit and were born before 1980 Dfinding the number of customers, in each city, whose marital status is 'married'. Elisting of those customers, whose credit limit is the same as the credit limit of customers residing in the city 'Tokyo'. Answer and explanation Correct answers: A, E A, E
Q3Question 3You need to list the employees in DEPARTMENT_ID 20 days in a single row, ordered by HIRE_DATE Examine the sample output below. Which query will provide the required output? ABCDAnswer and explanation Correct answer: B B
Q4Question 4Which statement is true about SQL query processing in an Oracle database instance? (Choose the best answer.) ADuring parsing, a SQL statement containing literals in the WHERE clause that has been executed by any session and which is cached in memory, is always reused for the current execution. BDuring execution, the Oracle server may read data from storage if the required data is not already in memory. CDuring row source generation, rows that satisfy the query are retrieved from the database and stored in memory. DDuring optimization, execution plans are formulated based on the statistics gathered by the database instance, and the lowest cost plan is selected for execution. Answer and explanation Correct answer: B B
Q5Question 5Multiple answersWhich three statements are true about Structured Query Language (SQL)? (Choose three.) AIt best supports relational databases. BIt is used to define encapsulation and polymorphism for a relational table. CIt guarantees atomicity, consistency, isolation, and durability (ACID) features. DIt provides independence for logical data structures being manipulated from the underlying physical data storage. EIt requires that data be contained in hierarchical data storage. Answer and explanation Correct answers: A, D, E A, D, E
Q6Question 6Multiple answersWhich three statements are true regarding subqueries? (Choose three.) AThe ORDER BY Clause can be used in a subquery. BA subquery can be used in the FROM clause of a SELECT statement. CIf a subquery returns NULL, the main query may still return rows. DA subquery can be placed in a WHERE clause, a GROUP BY clause, or a HAVING clause. ELogical operators, such as AND, OR and NOT, cannot be used in the WHERE clause of a subquery. Answer and explanation Correct answers: A, B, C A, B, C
Q7Question 7Multiple answersWhich two statements best describe the benefits of using the WITH clause? (Choose two.) AIt can improve the performance of a large query by storing the result of a query block having the WITH clause in the session's temporary tablespace. BIt enables sessions to reuse the same query block in a SELECT statement, if it occurs more than once in a complex query. CIt enables sessions to store a query block permanently in memory and use it to create complex queries. DIt enables sessions to store the results of a query permanently. Answer and explanation Correct answers: A, B A, B
Q8Question 8Multiple answersWhich two statements are true regarding the SQL GROUP BY clause? (Choose two.) AYou can use a column alias in the GROUP BY clause. BUsing the WHERE clause after the GROUP BY clause excludes rows after creating groups. CThe GROUP BY clause is mandatory if you are using an aggregating function in the SELECT clause. DUsing the WHERE clause before the GROUP BY clause excludes rows before creating groups. EIf the SELECT clause has an aggregating function, then columns without an aggregating function in the SELECT clause should be included in the GROUP BY clause. Answer and explanation Correct answers: D, E D, E
Q9Question 9Examine the structure of the MEMBERS table. Which query can be used to display the last names and city names only for members from the states MO and Ml? ASELECT last_name, city FROM members WHERE state - MO' AND state ='MI'; BSELECT last_name, city FROM members WHERE state LIKE 'M%'; CSELECT last_name, city FROM members WHERE state IN ('MO', 'Ml'); DSELECT DISTINCT last_name, city FROM members WHERE state ='MO' OR state ='MI'; Answer and explanation Correct answer: C C
Q10Question 10Examine the SQL statement used to create the TRANSACTION table. The value 'A1' does not exist for trn _ID in this table. Which SQL statement successfully inserts a row into the table with the default value for START_DATE? AINSERT INTO transaction VALUES ('AT, DEFAULT, TO_DATE(DEFAULT+10)); BINSERT INTO transaction VALUES ('AT, DEFAULT, TO_DATE('SYSDATE+10')); CINSERT INTO transaction (trn_ID, end_date) VALUES ('AT, '10-DEC-2014'); DINSERT INTO transaction (trn_ID, start_date, end_date) VALUES ('AT,, '10-DEC-2014'); Answer and explanation Correct answer: C C