oracle 1z0-071 practice test

Oracle Database SQL Exam

Last exam update: May 12 ,2024
Page 1 out of 25. Viewing questions 1-15 out of 318

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

User Votes:
A 1 votes
50%
B 1 votes
50%
C
50%
D 1 votes
50%
E
50%
F
50%
Discussions
vote your answer:
A
B
C
D
E
F
0 / 1000

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

User Votes:
A
50%
B
50%
C 1 votes
50%
D
50%
Discussions
vote your answer:
A
B
C
D
0 / 1000

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

User Votes:
A
50%
B
50%
C
50%
D
50%
Discussions
vote your answer:
A
B
C
D
0 / 1000

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

User Votes:
A
50%
B
50%
C
50%
D
50%
Discussions
vote your answer:
A
B
C
D
0 / 1000

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

User Votes:
A
50%
B
50%
C 1 votes
50%
D 1 votes
50%
Discussions
vote your answer:
A
B
C
D
0 / 1000

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

User Votes:
A
50%
B
50%
C 1 votes
50%
D
50%
E 1 votes
50%
Discussions
vote your answer:
A
B
C
D
E
0 / 1000

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

User Votes:
A
50%
B
50%
C 1 votes
50%
D
50%
Discussions
vote your answer:
A
B
C
D
0 / 1000

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

User Votes:
A 1 votes
50%
B
50%
C
50%
D
50%
E 1 votes
50%
Discussions
vote your answer:
A
B
C
D
E
0 / 1000

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

User Votes:
A 1 votes
50%
B
50%
C
50%
D
50%
Discussions
vote your answer:
A
B
C
D
0 / 1000

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

User Votes:
A 1 votes
50%
B
50%
C
50%
D 1 votes
50%
E 1 votes
50%
F
50%
Discussions
vote your answer:
A
B
C
D
E
F
0 / 1000

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

User Votes:
A 1 votes
50%
B
50%
C
50%
D
50%
Discussions
vote your answer:
A
B
C
D
0 / 1000

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

User Votes:
A
50%
B
50%
C
50%
D 1 votes
50%
Discussions
vote your answer:
A
B
C
D
0 / 1000

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

User Votes:
A
50%
B
50%
C
50%
D 1 votes
50%
E
50%
F 1 votes
50%
Discussions
vote your answer:
A
B
C
D
E
F
0 / 1000

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

User Votes:
A 1 votes
50%
B
50%
C
50%
D 1 votes
50%
E
50%
F 1 votes
50%
Discussions
vote your answer:
A
B
C
D
E
F
0 / 1000

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

User Votes:
A 1 votes
50%
B
50%
C
50%
D
50%
E 1 votes
50%
F 1 votes
50%
G 1 votes
50%
Discussions
vote your answer:
A
B
C
D
E
F
G
0 / 1000
To page 2