Oracle SQL2

Question 1

You need to modify the STUDENTS table to add a primary key on the STUDENT_ID column. The table is currently empty. Which statement accomplishes this task?

Accepted Answer

ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id);

Answer

ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY student_id;

Answer

ALTER TABLE students MODIFY CONSTRAINT stud_id_pk PRIMARY KEY (student_id);

Answer

ALTER TABLE students ADD PRIMARY KEY student_id;

Answer

ALTER TABLE students ADD CONSTRAINT PRIMARY KEY (student_id);

Question 2

An entity-relationship diagram shows data modeled into
(Choose the best answer.)

Accepted Answer

Two-dimensional tables

Answer

Hierarchical structures

Answer

Object-oriented structures

Answer

Multidimensional tables

Question 3

Which actions are allowed on a table that is marked as read-only?

Accepted Answer

Dropping a table

Accepted Answer

Dropping a constraint

Accepted Answer

Dropping an index

Answer

Truncating a table

Answer

Inserting new data

Question 4

SQL is a set-oriented language. Which of these features is a consequence of this?
(Choose the best answer.)

Accepted Answer

One statement can affect multiple rows.

Answer

Individual rows must have a unique identifier.

Answer

SQL statements can be placed within blocks of code in other languages, such as Java and PL/SQL.

Answer

Sets of users can be managed in groups.

Question 5

Which statements are true regarding the WHERE and HAVING clauses in a SELECT statement?
(Choose all that apply.)

Accepted Answer

The HAVING clause can be used with aggregate functions in subqueries.

Accepted Answer

The WHERE clause can be used to exclude rows before dividing them into groups.

Answer

The aggregate functions and columns used in the HAVING clause must be specified in the SELECT list of the query.

Answer

The WHERE clause can be used to exclude rows after dividing them into groups.

Answer

The WHERE and HAVING clauses can be used in the same statement only if they are applied to different columns in the table.

Question 6

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

Accepted Answer

ROLLBACK

Accepted Answer

CREATE

Accepted Answer

COMMIT

Answer

SELECT

Answer

SAVEPOINT

Question 7

What value is returned after executing the following statement? Take note that 01-JAN-2009 occurs on a Thursday.

SELECT NEXT_DAY('01-JAN-2009','wed') FROM DUAL;

(Choose the best answer.)

Accepted Answer

07-JAN-2009

Answer

Wednesday

Answer

31-JAN-2009

Answer

None of the above

Question 8

Which two statements about the evaluation of clauses in a SELECT statement are true? 
(Choose two.)

Accepted Answer

The Oracle Server will evaluate a GROUP BY clause before a HAVING clause.

Accepted Answer

The Oracle Server will evaluate a WHERE clause before a GROUP BY clause.

Answer

The Oracle Server will evaluate an ORDER BY clause before a WHERE clause.

Answer

The Oracle Server will evaluate an ORDER BY clause before a HAVING clause.

Answer

The Oracle Server will evaluate a HAVING clause before a WHERE clause.

Question 9

Which types of constraint require an index?
(Choose all that apply.)

Accepted Answer

PRIMARY KEY

Accepted Answer

UNIQUE

Answer

CHECK

Answer

NOT NULL

Question 10

Which SQL statement will replace the last two characters of last_name with 'XX' in the employees table when executed?

Accepted Answer

SELECT CONCAT(SUBSTR(last_name, 1,LENGTH(last_name)-2), 'XX') new_col FROM employees;

Answer

SELECT REPLACE(last_name, SUBSTR(last_name, LENGTH(last_name)-1), 'XX') new_col FROM employees;

Answer

SELECT REPLACE(SUBSTR(last_name, LENGTH(last_name)-1), 'XX') new_col FROM employees;

Answer

SELECT RTRIM(last_name, SUBSTR(last_name, LENGTH(last_name)-1)) || 'XX' new_col FROM employees;

Question 11

What value is returned after executing the following statement?

SELECT TO_CHAR(1234.49, '999999.9') FROM DUAL;

(Choose the best answer.)

Accepted Answer

1234.5

Answer

001234.5

Answer

None of the above

Answer

1234.49

Question 12

CREATE TABLE(
 emp_id NUMBER NOT NULL,
 emp_name VARCHAR2(40) NOT NULL,
 emp_image LONG
);

Which two statements are true regarding SQL statements that can be executed on the EMP_DETAIL table?
(Choose two.)

Accepted Answer

You cannot add a new column to the table with LONG as the data type

Accepted Answer

An EMP_IMAGE column cannot be included in the ORDER BY clause

Answer

An EMP_IMAGE column can be included in the GROUP BY clause

Answer

You can alter the table to include the NOT NULL constraint on the EMP_IMAGE column

Question 13

If SYS DATE returns 12-JUL-2016, what is returned by the following statement?

SELECT TO_CHAR(TO_DATE(TO_CHAR(SYS DATE,'DD'),'DD'),'YEAR') FROM DUAL;

(Choose the best answer.)

Accepted Answer

TWO THOUSAND SIXTEEN

Answer

12-JUL-2016

Answer

None of the above

Answer

2016

Question 14

Which two operators are not allowed when using an outer join operator in the query?

Accepted Answer

IN

Accepted Answer

OR

Answer

AND

Answer

=

Question 15

Which statement is true regarding the INTERSECT operator?

Accepted Answer

The number of columns and data types must be identical for all SELECT statements in the query

Answer

It ignores NULL values

Answer

The names of columns in all SELECT statements must be identical

Answer

Reversing the order of the intersected tables the result

Question 16

What are the distinguishing characteristics of a public synonym rather than a private synonym?
(Choose two correct answers.)

Accepted Answer

Public synonyms can have the same names as tables or views.

Accepted Answer

Public synonyms can be accessed by name without a schema name qualifier.

Answer

Public synonyms can be selected from without needing any permissions.

Answer

Public synonyms are always visible to all users.

Question 17

You need to calculate the number of days from 1st January 2007 till date . Dates are stored in the default format of dd-mon-rr.

Which two SQL statements would give the required output?

(Choose two.)

Accepted Answer

SELECT SYSDATE – TO_DATE(’01-JANUARY-2007′) FROM DUAL;

Accepted Answer

SELECT SYSDATE – TO_DATE(’01/JANUARY/2007′) FROM DUAL;

Answer

SELECT TO_DATE(SYSDATE, ‘DD/MONTH/YYYY’) – ’01/JANUARY/2007′ FROM DUAL;

Answer

SELECT SYSDATE – ’01-JAN-2007′ FROM DUAL;

Answer

SELECT TO_CHAR(SYSDATE, ‘DD-MON-YYYY’) – ’01-JAN-2007′ FROM DUAL;

Question 18

Which expressions do not return NULL values?
(Choose all that apply.)

Accepted Answer

select null||'test'||null as “Test” from dual;

Accepted Answer

select 'this is a '||null||'test with nulls' from dual;

Answer

select ((10 + 20) * 50) + null from dual;

Answer

select null/0 from dual;

Question 19

The DBA issues this SQL command: CREATE USER scott IDENTIFIED by tiger;

What privileges does the user Scott have at this point?

Accepted Answer

no privileges

Answer

all the privileges of a default user

Answer

only the SELECT privilege

Answer

only the CONNECT privilege

Question 20

If an UPDATE or DELETE command has a WHERE clause that gives it a scope of several rows, what will happen if there is an error part way through execution?

The command is one of several in a multistatement transaction.
(Choose the best answer.)

Accepted Answer

Whatever work the command had done before hitting the error will be rolled back, but work done already by the transaction will remain.

Answer

The command will skip the row that caused the error and continue.

Answer

The whole transaction will be rolled back.

Answer

The command will stop at the error, and the rows that have been updated or deleted will remain updated or deleted.

Question 21

If a programmer does not request a type of lock when updating many rows in one table, what lock or locks will they be given?

(Choose the best answer.)

Accepted Answer

An exclusive lock on each row and a shared lock on the table. This maximizes concurrency safely.

Answer

No locks at all. The default level of locking is NONE in order to maximize concurrency.

Answer

An exclusive lock on the table. This is the fastest method when many rows are being updated.

Answer

Shared locks on the table and on each row. This is the safest (although not the fastest) method.

Oracle SQL2

Create a free account to finish this quiz

Questions in this quiz (21)

Information

Feedback

What do you think of the new look?