Free 1z0-071 sample questions

Examine 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.)

• It displays all cities in descending order, within which the last names are further sorted in descending order.
• It fails because a column alias cannot be used in the ORDER BY clause.
• It fails because a column number and a column alias cannot be used together in the ORDER BY clause.
• It displays all cities in ascending order, within which the last names are further sorted in descending order.

View 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.)

• finding the number of customers, in each city, whose credit limit is more than the average credit limit of all the customers
• finding the average credit limit of male customers residing in 'Tokyo' or 'Sydney'
• listing of customers who do not have a credit limit and were born before 1980
• finding the number of customers, in each city, whose marital status is 'married'.
• listing of those customers, whose credit limit is the same as the credit limit of customers residing in the city 'Tokyo'.

You 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?

Which statement is true about SQL query processing in an Oracle database instance? (Choose the best answer.)

• During 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.
• During execution, the Oracle server may read data from storage if the required data is not already in memory.
• During row source generation, rows that satisfy the query are retrieved from the database and stored in memory.
• During optimization, execution plans are formulated based on the statistics gathered by the database instance, and the lowest cost plan is selected for execution.

Which three statements are true about Structured Query Language (SQL)? (Choose three.)

• It best supports relational databases.
• It is used to define encapsulation and polymorphism for a relational table.
• It guarantees atomicity, consistency, isolation, and durability (ACID) features.
• It provides independence for logical data structures being manipulated from the underlying physical data storage.
• It requires that data be contained in hierarchical data storage.

Which three statements are true regarding subqueries? (Choose three.)

• The ORDER BY Clause can be used in a subquery.
• A subquery can be used in the FROM clause of a SELECT statement.
• If a subquery returns NULL, the main query may still return rows.
• A subquery can be placed in a WHERE clause, a GROUP BY clause, or a HAVING clause.
• Logical operators, such as AND, OR and NOT, cannot be used in the WHERE clause of a subquery.

Which two statements best describe the benefits of using the WITH clause? (Choose two.)

• It 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.
• It enables sessions to reuse the same query block in a SELECT statement, if it occurs more than once in a complex query.
• It enables sessions to store a query block permanently in memory and use it to create complex queries.
• It enables sessions to store the results of a query permanently.

Which two statements are true regarding the SQL GROUP BY clause? (Choose two.)

• You can use a column alias in the GROUP BY clause.
• Using the WHERE clause after the GROUP BY clause excludes rows after creating groups.
• The GROUP BY clause is mandatory if you are using an aggregating function in the SELECT clause.
• Using the WHERE clause before the GROUP BY clause excludes rows before creating groups.
• If 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.

Examine 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?

• SELECT last_name, city FROM members WHERE state - MO' AND state ='MI';
• SELECT last_name, city FROM members WHERE state LIKE 'M%';
• SELECT last_name, city FROM members WHERE state IN ('MO', 'Ml');
• SELECT DISTINCT last_name, city FROM members WHERE state ='MO' OR state ='MI';

Examine 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?

• INSERT INTO transaction VALUES ('AT, DEFAULT, TO_DATE(DEFAULT+10));
• INSERT INTO transaction VALUES ('AT, DEFAULT, TO_DATE('SYSDATE+10'));
• INSERT INTO transaction (trn_ID, end_date) VALUES ('AT, '10-DEC-2014');
• INSERT INTO transaction (trn_ID, start_date, end_date) VALUES ('AT,, '10-DEC-2014');