Oracle Database 12c: SQL Fundamentals (1z0-061) Free Practice Test

Question 1

The DBA issues this SQL command:
CREATE USER Scott
IDENTIFIED by tiger;
What privileges does the user Scott have at this point?

A. No privileges.

B. Only the CONNECT privilege.

C. All the privileges of a default user.

D. Only the SELECT privilege.

Correct Answer: A

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

Question 2

View the Exhibit and examine the structure of the products table.

Using the products table, you issue the following query to generate the names, current list price, and discounted list price for all those products whose list price falls below $10 after a discount of 25% is applied on it.

The query generates an error. What is the reason for the error?

A. The parenthesis should be added to enclose the entire expression.

B. The column alias should be put in uppercase and enclosed within double quotation marks in the where clause.

C. The double quotation marks should be removed from the column alias.

D. The column alias should be replaced with the expression in the where clause.

Correct Answer: D

Question 3

View the Exhibit and examine the structure of the CUSTOMERS table.

You have been asked to produce a report on the customers table showing the customers details sorted in descending order of the city and in the descending order of their income level in each city.
Which query would accomplish this task?

A. Option D

B. Option C

C. Option A

D. Option B

Correct Answer: C

Question 4

The STUDENT_GRADES table has these columns:
STUDENT_ID. NUMBER(12)
SEMESTER_END. DATE
GPA. NUMBER(4, 3)
The registrar has asked for a report on the average grade point average (GPA), sorted from the highest grade point average to each semester, starting from the earliest date.
Which statement accomplish this?

A. SELECT student_id, semester_end, gpaFROM student_gradesORDER BY gpa DESC, semester_end DESC;

B. SELECT student_id, semester_end, gpaFROM student_gradesORDER BY
semester_end, gpa DESC

C. SELECT student_id, semester_end, gpaFROM student_gradesORDER BY
semester_end, gpa ASC

D. SELECT student_id, semester_end, gpaFROM student_gradesORDER BY gpa DESC, semester_end ASC;

E. SELECT student_id, semester_end, gpaFROM student_gradesORDER BY
semester_end DESC, gpa DESC;

F. SELECT student_id, semester_end, gpaFROM student_gradesORDER BY gpa DESC, semester_end ASC;

Correct Answer: B

Question 5

Which four are types of functions available in SQL? (Choose 4)

A. integer

B. conversion

C. character

D. numeric

E. translation

F. calendar

G. string

H. date

Correct Answer: B,C,D,H

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

Question 6

Evaluate the SQL statement:
SELECT LPAD (salary, 10, '*')
FROM EMP
WHERE EMP_ID = 1001;
If the employee with the EMP_ID 1001 has a salary of 17000, what is displayed?

A. an error statement

B. **17000.00

C. ****170.00

D. 17000.00

E. 17000*****

Correct Answer: B

Question 7

View the Exhibit and examine the structures of the employees and departments tables.

You want to update the employees table as follows:
-Update only those employees who work in Boston or Seattle (locations 2900 and 2700).
-Set department_id for these employees to the department_id corresponding to London (location_id 2100).
-Set the employees' salary in iocation_id 2100 to 1.1 times the average salary of their department.
-Set the employees' commission in iocation_id 2100 to 1.5 times the average commission of their department.
You issue the following command:

What is the outcome?

A. It generates an error because multiple columns (SALARY, COMMISSION) cannot be specified together in an update statement.

B. It executes successfully and gives the correct result.

C. It generates an error because a subquery cannot have a join condition in an update statement.

D. It executes successfully but does not give the correct result.

Correct Answer: D

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

Question 8

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

The above query generates an error on execution.
Which clause in the above SQL statement causes the error?

A. ORDER BY

B. WHERE

C. SELECT

D. GROUP BY

Correct Answer: D

Question 9

Examine the structure of the MARKS table:
Exhibit:

Which two statements would execute successfully? (Choose two.)

A. SELECT SUM(DISTINCT NVL(subject1, 0)), MAX(subject1)FROM marksWHERE
subject1 > subject2;

B. SELECT SUM(subject1+subject2+subject3)FROM marksWHERE student_name IS NULL;

C. SELECT student_name, SUM(subject1)FROM marksWHERE student_name LIKE 'R%';

D. SELECT student_name, subject1FROM marksWHERE subject1 > AVG(subject1);

Correct Answer: A,B

Question 10

What is true about sequences?

A. The maximum value of descending sequence defaults to 1.

B. A sequence always increments by 1.

C. The minimum value of an ascending sequence defaults to 1.

D. The start value of the sequence is always 1.

Correct Answer: C

Question 11

Evaluate the following SQL statements:
Exhibit:

You issue the following command to create a view that displays the IDs and last names of the sales staff in the organization.
Exhibit:

Which two statements are true regarding the above view? (Choose two.)

A. It allows you to delete details of the existing sales staff from the EMPLOYEES table

B. It allows you to insert rows into the EMPLOYEES table

C. It allows you to insert IDs, last names, and job IDs of the sales staff from the view if it is used in multitable INSERT statements

D. It allows you to update job IDs of the existing sales staff to any other job ID in the EMPLOYEES table

Correct Answer: A,C

Question 12

You create a sequence as follows:
create sequence seq1 start with 1;
After selecting from it a few times, you want to reinitialize it to reissue the numbers already generated.
How can you do this?

A. You must drop and re-create the sequence.

B. Use the command ALTER SEQUENCE SEQ1 CYCLE; to reset the sequence to its starting value.

C. Use the command ALTER SEQUENCE SEQ1 START WITH 1; to reset the next value to 1.

D. You can't. Under no circumstances can numbers from a sequence be reissued once they have been used.

Correct Answer: A

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

Welcome to TestSimulate

Oracle Database 12c: SQL Fundamentals (1z0-061) Free Practice Test