oracle 1z0-071 practice test
Oracle Database SQL Exam
Last exam update: Jul 20 ,2024
Question 1
Which three statements are true about single-row functions? (Choose three.)
- A. They can be nested to any level
- B. The data type returned can be different from the data type of the argument
- C. They can accept only one argument
- D. The argument can be a column name, variable, literal or an expression
- E. They can be used only in the WHERE clause of a SELECT statement
- F. They return a single result row per table
Answer:
A B D
Question 2
View the Exhibit and examine the structure of the CUSTOMERS table.
Using the CUSTOMERS table, you must generate a report that displays a credit limit increase of 15% for all customers.
Customers with no credit limit should have Not Available displayed.
Which SQL statement would produce the required result?
- A. SELECT NVL(TO_CHAR(cust_credit_limit*.15),‘Not Available’) “NEW CREDIT” FROM customers;
- B. SELECT TO_CHAR(NVL(cust_credit_limit*.15,‘Not Available’)) “NEW CREDIT” FROM customers;
- C. SELECT NVL(cust_credit_limit*.15,‘Not Available’) “NEW CREDIT” FROM customers;
- D. SELECT NVL(cust_credit_limit,Not Available)*.15 NEW CREDIT FROM customers;
Answer:
A
Question 3
Examine the structure of the INVOICE table.
Which two SQL statements would execute successfully?
- A. SELECT inv_no, NVL2(inv_date, 'Pending', 'Incomplete') FROM invoice;
- B. SELECT inv_no, NVL2(inv_amt, inv_date, 'Not Available') FROM invoice;
- C. SELECT inv_no, NVL2(inv_date, sysdate-inv_date, sysdate) FROM invoice;
- D. SELECT inv_no, NVL2(inv_amt, inv_amt*.25, 'Not Available') FROM invoice;
Answer:
A C
Question 4
View the Exhibit and examine the structure of ORDERS and ORDER_ITEMS tables.
ORDER_ID is the primary key in the ORDERS table. It is also the foreign key in the ORDER_ITEMS table wherein it is
created with the ON DELETE CASCADE option. Which DELETE statement would execute successfully?
- A. DELETE orders o, order_items i WHERE o.order_id = i.order_id;
- B. DELETE FROM orders WHERE (SELECT order_id FROM order_items);
- C. DELETE orders WHERE order_total < 1000;
- D. DELETE order_id FROM orders WHERE order_total < 1000;
Answer:
C
Question 5
View the Exhibit and examine the structure in the DEPARTMENTS tables. (Choose two.)
Examine this SQL statement:
SELECT department_id "DEPT_ID", department_name, 'b' FROM departments
WHERE departments_id=90
UNION
SELECT department_id, department_name DEPT_NAME, 'a' FROM departments
WHERE department_id=10
Which two ORDER BY clauses can be used to sort the output?
- A. ORDER BY DEPT_NAME;
- B. ORDER BY DEPT_ID;
- C. ORDER BY 'b';
- D. ORDER BY 3;
Answer:
B D
Question 6
Which two statements are true regarding the UNION and UNION ALL operators? (Choose two.)
- A. The output is sorted by the UNION ALL operator
- B. The names of columns selected in each SELECT statement must be identical
- C. The number of columns selected in each SELECT statement must be identical
- D. Duplicates are eliminated automatically by the UNION ALL operator
- E. NULLS are not ignored during duplicate checking
Answer:
C E
Question 7
You must write a query that prompts users for column names and conditions every time it is executed.
The user must be prompted only once for the table name.
Which statement achieves those objectives?
- A. SELECT &col1, '&col2' FROM &table WHERE &&condition = '&cond';
- B. SELECT &col1, &col2 FROM "&table" WHERE &condition = &cond;
- C. SELECT &col1, &col2 FROM &&table WHERE &condition = &cond;
- D. SELECT &col1, &col2 FROM &&table WHERE &condition = &&cond
Answer:
C
Question 8
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?
- A. finding the number of customers, in each city, whose credit limit is more than the average credit limit of all the customers
- B. finding the average credit limit of male customers residing in 'Tokyo' or 'Sydney'
- C. listing of customers who do not have a credit limit and were born before 1980
- D. finding the number of customers, in each city, whose marital status is 'married'.
- E. listing of those customers, whose credit limit is the same as the credit limit of customers residing in the city 'Tokyo'.
Answer:
A E
Question 9
View the Exhibit and examine the structure of CUSTOMERS table.
Using the CUSTOMERS table, you need to generate a report that shows an increase in the credit limit by 15% for all
customers. Customers whose credit limit has not been entered should have the message "Not Available" displayed.
Which SQL statement would produce the required result?
- A. SELECT NVL (TO CHAR(cust_credit_limit * .15), 'Not Available') "NEW CREDIT" FROM customers;
- B. SELECT TO_CHAR (NVL(cust_credit_limit * .15), 'Not Available') "NEW CREDIT" FROM customers;
- C. SELECT NVL(cust_credit_limit * .15), 'Not Available') "NEW CREDIT" FROM customers;
- D. SELECT NVL(cust_credit_limit), 'Not Available') "NEW CREDIT" FROM customers;
Answer:
A
Question 10
Which three statements are true about Structured Query Language (SQL)? (Choose three.)
- A. It best supports relational databases.
- B. It is used to define encapsulation and polymorphism for a relational table.
- C. It is the only language that can be used for both relational and object-oriented databases.
- D. It guarantees atomicity, consistency, isolation, and durability (ACID) features.
- E. It provides independence for logical data structures being manipulated from the underlying physical data storage.
- F. It requires that data be contained in hierarchical data storage.
Answer:
A D E
Question 11
A non-correlated subquery can be defined as __________. (Choose the best answer.)
- A. A set of one or more sequential queries in which generally the result of the inner query is used as the search value in the outer query.
- B. A set of sequential queries, all of which must return values from the same table.
- C. A set of sequential queries, all of which must always return a single value.
- D. A SELECT statement that can be embedded in a clause of another SELECT statement only.
Answer:
A
Question 12
Examine the structure of the MEMBERS table:
Name Null? Type
------------------ --------------- ------------------------------
MEMBER_ID NOT NULL VARCHAR2 (6)
FIRST_NAME VARCHAR2 (50)
LAST_NAME NOT NULL VARCHAR2 (50) ADDRESS VARCHAR2 (50)
You execute the SQL statement:
SQL > SELECT member_id, ' ' , first_name, ' ' , last_name "ID FIRSTNAME LASTNAME " FROM members;
What is the outcome?
- A. It fails because the alias name specified after the column names is invalid.
- B. It fails because the space specified in single quotation marks after the first two column names is invalid.
- C. It executes successfully and displays the column details in a single column with only the alias column heading.
- D. It executes successfully and displays the column details in three separate columns and replaces only the last column heading with the alias.
Answer:
D
Question 13
Examine the description of the PRODUCT_DETAILS table:
Which two statements are true? (Choose two.)
- A. PRODUCT_PRICE contains the value zero by default if no value is assigned to it.
- B. PRODUCT_PRICE can be used in an arithmetic expression even if it has no value stored in it.
- C. EXPIRY_DATE cannot be used in arithmetic expressions.
- D. PRODUCT_ID can be assigned the PRIMARY KEY constraint.
- E. EXPIRY_DATE contains the SYSDATE by default if no date is assigned to it.
- F. PRODUCT_NAME cannot contain duplicate values.
Answer:
B D
Question 14
Which three statements are true reading subqueries? (Choose three.)
- A. A Main query can have many subqueries.
- B. A subquery can have more than one main query.
- C. The subquery and main query must retrieve date from the same table.
- D. The subquery and main query can retrieve data from different tables.
- E. Only one column or expression can be compared between the subquery and main query.
- F. Multiple columns or expressions can be compared between the subquery and main query.
Answer:
A D F
Question 15
Which four statements are true regarding primary and foreign key constraints and the effect they can have on table data?
(Choose four.)
- A. It is possible for child rows that have a foreign key to remain in the child table at the time the parent row is deleted
- B. Only the primary key can be defined at the column and table level
- C. The foreign key columns and parent table primary key columns must have the same names
- D. A table can have only one primary key and one foreign key
- E. A table can have only one primary key but multiple foreign keys
- F. Primary key and foreign key constraints can be defined at both the column and table level
- G. It is possible for child rows that have a foreign key to be deleted automatically from the child table at the time the parent row is deleted
Answer:
C E F G