Oracle Database: SQL Fundamentals I (1Z1-051) Free Practice Test

Question 1

Evaluate the following SQL query; What would be the outcome?

A. 100

B. 200

C. 160

D. 16

E. 150

Correct Answer: C

Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).

Question 2

Examine the data in the ORD_ITEMS table:
ORD_NO ITEM_NO QTY
1 111 10
1 222 20
1 333 30
2 333 30
2 444 40
3 111 40
Evaluate the following query:
SQL>SELECT item_no, AVG(qty)
FROM ord_items
HAVING AVG(qty) > MIN(qty) * 2
GROUP BY item_no;
Which statement is true regarding the outcome of the above query?

A. It displays the item nos with their average quantity where the average quantity is more than double the overall minimum quantity of all the items in the table.

B. It displays the item nos with their average quantity where the average quantity is more than double the minimum quantity of that item in the table.

C. It gives an error because all the aggregate functions used in the HAVING clause must be specified in the SELECT list.

D. It gives an error because the HAVING clause should be specified after the GROUP BY clause.

Correct Answer: B

Question 3

See the Exhibit and examine the structure and data in the INVOICE table:
Exhibit: Which two SQL statements would executes successfully? (Choose two.)

A. SELECT MAX(inv_date),MIN(cust_id) FROM invoice;

B. SELECT MAX(AVG(SYSDATE - inv_date)) FROM invoice;

C. SELECT (AVG(inv_date) FROM invoice;

D. SELECT AVG(inv_date - SYSDATE),AVG(inv)amt) FROM invoice;

Correct Answer: A,D

Question 4

You need to create a table named ORDERS that contain four columns:
1.an ORDER_ID column of number data type
2.a CUSTOMER_ID column of number data type
3.an ORDER_STATUS column that contains a character data type
4.a DATE_ORDERED column to contain the date the order was placed.
When a row is inserted into the table, if no value is provided when the order was placed, today's date should be used instead.
Which statement accomplishes this?

A. CREATE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status VARCHAR2 (10),date_ordered DATE DEFAULT SYSDATE);

B. CREATE OR REPLACE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status VARCHAR2 (10),date_ordered DATE = SYSDATE);

C. CREATE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status VARCHAR2 (10),date_ordered DATE = SYSDATE);

D. CREATE OR REPLACE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status VARCHAR2 (10),date_ordered DATE DEFAULT SYSDATE);

E. CREATE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status NUMBER (10),date_ordered DATE DEFAULT SYSDATE);

F. CREATE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status NUMBER (10),date_ordered DATE = SYSDATE);

Correct Answer: A

Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).

Question 5

View the Exhibit and examine the structure of the CUSTOMERS table. Evaluate the following SQL statement:

Which statement is true regarding the outcome of the above query?

A. It returns an error because the BETWEEN operator cannot be used in the HAVING clause.

B. It executes successfully.

C. It returns an error because WHERE and HAVING clauses cannot be used in the same SELECT statement.

D. It returns an error because WHERE and HAVING clauses cannot be used to apply conditions on the same column.

Correct Answer: B

Question 6

See the Exhibit and examine the structure of the PROMOTIONS table: Exhibit:

Using the PROMOTIONS table, you need to find out the average cost for all promos in the range
$0-2000 and $2000-5000 in category A.
You issue the following SQL statements:
Exhibit:

What would be the outcome?

A. It executes successfully and gives the required result

B. It generates an error because CASE cannot be used with group functions

C. It generates an error because multiple conditions cannot be specified for the WHEN clause

D. It generates an error because NULL cannot be specified as a return value

Correct Answer: A

Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).

Question 7

Examine the description of the EMPLOYEES table:
EMP_ID NUMBER(4) NOT NULL
LAST_NAME VARCHAR2(30) NOT NULL
FIRST_NAME VARCHAR2(30)
DEPT_ID NUMBER(2)
Which statement produces the number of different departments that have employees with last name Smith?

A. SELECT DISTINCT(COUNT(dept_id)) FROM employees WHERE last_name='Smith';

B. SELECT COUNT(DISTINCT dept_id) FROM employees WHERE last_name='Smith';

C. SELECT COUNT (dept_id) FROM employees WHERE last_name='Smith';

D. SELECT UNIQUE(dept_id) FROM employees WHERE last_name='Smith';

E. SELECT COUNT(*) FROM employees WHERE last_name='Smith';

Correct Answer: B

Question 8

You work as a database administrator at ABC.com. You study the exhibit carefully.
Exhibit:

Evaluate the following query: Exhibit:

The above query produces an error on execution. What is the reason for the error?

A. The MIDPOINT +100 expression gives an error because CUST_CREDIT_LIMIT contains NULL values

B. An alias cannot be used in an expression

C. The alias NAME should not be enclosed within double quotation marks

D. The alias MIDPOINT should be enclosed within double quotation marks for the CUST_CREDIT_LIMIT/2 expression

Correct Answer: B

Question 9

The STUDENT_GRADES table has these columns:
STUDENT_ID NUMBER(12)
SEMESTER_END DATE
GPA NUMBER(4,3)
Which statement finds the highest grade point average (GPA) per semester?

A. SELECT MAX(gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL;

B. SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL GROUP BY semester_end;

C. SELECT MAX(gpa) GROUP BY semester_end WHERE gpa IS NOT NULL FROM student_grades;

D. SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL;

E. SELECT (gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL;

Correct Answer: B

Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).

Question 10

See the exhibit and examine the structure of the CUSTOMERS and GRADES tables:

You need to display names and grades of customers who have the highest credit limit.
Which two SQL statements would accomplish the task? (Choose two.)

A. SELECT custname, grade
FROM customers, grades
WHERE (SELECT MAX(cust_credit_limit)
FROM customers) BETWEEN startval and endval;

B. SELECT custname, grade
FROM customers, grades
WHERE cust_credit_limit = (SELECT MAX(cust_credit_limit)
FROM customers)
AND cust_credit_limit BETWEEN startval AND endval;

C. SELECT custname, grade
FROM customers , grades
WHERE cust_credit_limit IN (SELECT MAX(cust_credit_limit)
FROM customers)
AND MAX(cust_credit_limit) BETWEEN startval AND endval;

D. SELECT custname, grade
FROM customers, grades
WHERE (SELECT MAX(cust_credit_limit)
FROM customers) BETWEEN startval and endval
AND cust_credit_limit BETWEEN startval AND endval;

Correct Answer: B,D

Question 11

Which three statements/commands would cause a transaction to end? (Choose three.)

A. SAVEPOINT

B. COMMIT

C. ROLLBACK

D. SELECT

E. CREATE

Correct Answer: B,C,E

Welcome to TestSimulate

Oracle Database: SQL Fundamentals I (1Z1-051) Free Practice Test