Oracle SQL

Question 1

What is true about the WITH GRANT OPTION clause?

Accepted Answer

It allows the grantee to grant object privileges to other users and roles.

Answer

It allows privileges on specified columns of tables.

Answer

It is used to grant an object privilege on a foreign key column.

Answer

It allows a grantee DBA privileges.

Answer

It is required syntax for object privileges.

Question 2

Which single-row function could you use to return a specific portion of a character string?

Accepted Answer

SUBSTR

Answer

INSTR

Answer

LEAST

Answer

LPAD

Question 3

Where can sub queries be used?

Accepted Answer

field names in the SELECT statement

Accepted Answer

the FROM clause in the SELECT statement

Accepted Answer

the WHERE clause in SELECT as well as all DML statements

Accepted Answer

the HAVING clause in the SELECT statement

Answer

the WHERE clause in only the SELECT statement

Answer

the GROUP BY clause in the SELECT statement

Question 4

In the following SELECT statement, which component is a literal?

SELECT 'Employee Name: ' || ename
FROM emp WHERE deptno = 10;

Accepted Answer

Employee Name:

Accepted Answer

10

Answer

||

Answer

ename

Question 5

The EMPLOYEES table has EMPLOYEE_ID, DEPARTMENT_ID, and FULL_NAME columns. The DEPARTMENTS table has DEPARTMENT_ID and DEPARTMENT_NAME columns.

Which two of the following queries return the department ID, name, and employee name, listing department names even if there is no employee assigned to that department?

Accepted Answer

SELECT d.department_id, d.department_name, e.full_name FROM departments d LEFT OUTER JOIN employees eON (d.department_id = e.department_id);

Accepted Answer

SELECT department_id, department_name, full_name FROM departments NATURAL LEFT JOIN employees;

Answer

SELECT d.department_id, d.department_name, e.full_name FROM departments d NATURAL LEFT OUTER JOIN employees e;

Answer

SELECT d.department_id, d.department_name, e.full_name FROM departments d LEFT OUTER JOIN employees eUSING (d.department_id);

Question 6

Evaluate the SQL statement DROP TABLE DEPT:

Which four statements are true of the SQL statement?

Accepted Answer

All indexes based on the DEPT table are dropped.

Accepted Answer

You cannot roll back this statement.

Accepted Answer

All pending transactions are committed.

Accepted Answer

All data in the table is deleted, and the table structure is also deleted.

Answer

All data in the table is deleted, but the structure of the table is retained.

Answer

All views based on the DEPT table are deleted.

Answer

All synonyms based on the DEPT table are deleted.

Question 7

Which statement is true regarding sub queries?

Accepted Answer

The NOT operator can be used with IN, ANY, and ALL operators in multiple- row subqueries.

Answer

The NOT IN operator is equivalent to IS NULL with single- row subqueries.

Answer

The LIKE operator cannot be used with single- row subqueries.

Answer

=ANY and =ALL operators have the same functionality in multiple- row subqueries.

Question 8

Which statement is true regarding tables?

Accepted Answer

The same table name can be used in different schemas in the same database.

Answer

A table name can be of any length.

Answer

A column that has a DEFAULT value cannot store null values.

Answer

A table and a view can have the same name in the same schema.

Answer

A table and a synonym can have the same name in the same schema.

Answer

A table can have any number of columns.

Question 9

Which date components does the CURRENT_TIMESTAMP function display?

Accepted Answer

Session date, session time, and session time zone offset

Answer

Session date and session time zone offset

Answer

Session date and session time

Answer

Session time zone offset

Question 10

SELECT ROUND(45.953, -1), TRUNC(45.936, 2)
FROM dual;

Which values are displayed?

Accepted Answer

50 and 45.93

Answer

46 and 45

Answer

50 and 45.9

Answer

46 and 45.93

Answer

45.95 and 45.93

Answer

45 and 45.93

Question 11

When a DML statement executes, what happens?

Accepted Answer

Both data and undo blocks are updated in the database buffer cache, and the updates also go to the log buffer.

Answer

The redo log buffer is updated with information needed to redo the transaction, and the undo blocks are updated with information needed to reverse the transaction.

Answer

The old version of the data is written to an undo segment, and the new version is written to the data segments and the redo log buffer.

Answer

Both the data and the undo blocks on disk are updated, and the changes are written out to the redo stream.

Question 12

A query can have a sub query embedded within it. Under what circumstances could there be more than one sub query?

Accepted Answer

Subqueries can be embedded within each other with no practical limitations on depth.

Answer

It is possible to embed a single-row subquery inside a multiple-row subquery, but not the other way around.

Answer

The outer query can have multiple inner queries, but they must not be embedded within each other.

Answer

The outer query can include an inner query. It is not possible to have another query within the inner query.

Question 13

Choose the SQL statement that has no syntax error and is valid.

Accepted Answer

SELECT department_id, SUM(salary)
FROM employees
WHERE department_id <> 50
GROUP BY department_id
HAVING COUNT(*) > 30;

Answer

SELECT department_id, SUM(salary) sum_sal
FROM employees
WHERE department_id <> 50
AND sum_sal > 3000
GROUP BY department_id;

Answer

SELECT department_id, SUM(salary) sum_sal
FROM employees
WHERE department_id <> 50
GROUP BY department_id
HAVING sum_sal > 3000;

Answer

SELECT department_id, SUM(salary)
FROM employees
WHERE department_id <> 50
AND SUM(salary) > 3000
GROUP BY department_id;

Question 14

Several object types share the same namespace, and therefore cannot have the same name in the same schema.

Which of the following object types is not in the same namespace as the others?
(Choose the best answer.)

Accepted Answer

Index

Answer

View

Answer

Synonym

Answer

PL/SQL stored procedure

Answer

Table

Question 15

View the Exhibit and examine the structure and data in the INVOICE table. Which two SQL statements would execute successfully?

Accepted Answer

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

Accepted Answer

SELECT AVG( inv_date – SYSDATE), AVG(inv_amt)
FROM invoice;

Answer

SELECT AVG(inv_date )
FROM invoice;

Answer

SELECT MAX(AVG(SYSDATE – inv_date))
FROM invoice;

Question 16

Which statements are true?

Accepted Answer

The HAVING clause is optional when the GROUP BY clause is used.

Answer

A group function can be used only if the GROUP BY clause is present.

Answer

Group functions along with nonaggregated columns can appear in the SELECT clause as long as a GROUP BY clause and a HAVING clause are present.

Answer

The HAVING clause and the GROUP BY clause are mutually exclusive; you can use only one clause in a SELECT statement.

Question 17

You are granted the CREATE VIEW privilege. What does this allow you to do?

Accepted Answer

Create a view in your schema.

Answer

Create a table view.

Answer

Create a view in any schema.

Answer

Create a sequence view in any schema.

Answer

Create a view that is accessible by everyone.

Answer

Create a view only of it is based on tables that you created.

Question 18

Column alias names cannot be used in which clause?

Accepted Answer

WHERE clause

Answer

SELECT clause

Answer

ORDER BY clause

Answer

None of the above

Question 19

Which of the following statements are syntactically correct?
(Choose all that apply.)

Accepted Answer

SELECT JH.JOB_ID FROM JOB_HISTORY JH RIGHT OUTER JOIN JOBS J ON JH.JOB_ID=J.JOB_ID;

Answer

None of the above

Answer

SELECT JOB_ID FROM JOB_HISTORY JH RIGHT OUTER JOIN JOBS J ON (JH.JOB_ID=J.JOB_ID);

Answer

SELECT JOB_HISTORY.JOB_ID FROM JOB_HISTORY OUTER JOIN JOBS ON JOB_HISTORY.JOB_ID=JOBS.JOB_ID;

Oracle SQL

Create a free account to finish this quiz

Questions in this quiz (19)

Information

Feedback

What do you think of the new look?