Quiz about Certification Exam, Oracle, Oracle Database SQL

Question 1

The STORES table has a column START_ DATE of data type DATE, containing the date the row was
inserted.
You only want to display details of rows where START_ DATE is within the last 25 months.
Which WHERE clause can be used?
A) WHERE MONTHS_ BETWEEN (SYSDATE, start_ date) <= 25
B) WHERE MONTHS_ BETWEEN (start_ date, SYSDATE) <= 25
C) WHERE TO_ NUMBER (start_ date - SYSDATE) <= 25
D) WHERE ADD_ MONTHS (start_ date, 25) <= SYSDATE

Accepted Answer

Option A is correct.

Question 2

Choose the best answer.
Examine the description of the EMPLOYEES table:

$\"Certification$

Which query is valid?

Accepted Answer

SELECT dept_id, join_date,SUM(salary) FROM employees GROUP BY dept_id, join_date;

Answer

SELECT depe_id,join_date,SUM(salary) FROM employees GROUP BY dept_id:

Answer

SELECT dept_id,MAX(AVG(salary)) FROM employees GROUP BY dept_id;

Answer

SELECT dept_id,AVG(MAX(salary)) FROM employees GROUP BY dapt_id;

Question 3

Which three are true about the CREATE TABLE command?

Accepted Answer

It can include the CREATE...INDEX statement for creating an index to enforce the primary key
constraint.

Accepted Answer

The owner of the table should have space quota available on the tablespace where the table is
defined.

Accepted Answer

It implicitly executes a commit.

Answer

It implicitly rolls back any pending transactions.

Answer

A user must have the CREATE ANY TABLE privilege to create tables.

Answer

The owner of the table must have the UNLIMITED TABLESPACE system privilege.

Question 4

The CUSTOMERS table has a CUST_CREDT_LIMIT column of data type number.
Which two queries execute successfully?

Accepted Answer

SLECT NVL(TO_CHAR(cust_credit_limit * .15),'Not available') from customers;

Accepted Answer

SELECT NVL2(cust_credit_limit,TO_CHAR(cust_credit_limit * .15),'NOT Available') FROM
customers;

Answer

SELECT TO_CHAR(NVL(cust_credit_limit * .15,'Not Available')) FROM customers;

Answer

SELECT NVL2(cust_credit_limit * .15,'Not Available') FROM customers;

Answer

SELECT NVL(cust_credit_limit * .15,'Not Available') FROM customers;

Question 5

Choose two
Examine the description of the PRODUCT DETALS table:

$\"Certification$

Accepted Answer

PRODUCT_ID can be assigned the PEIMARY KEY constraint.

Accepted Answer

PRODUCT_PRICE can be used in an arithmetic expression even if it has no value stored in it

Answer

EXPIRY_DATE cannot be used in arithmetic expressions.

Answer

EXPIRY_DATE contains the SYSDATE by default if no date is assigned to it

Answer

PRODUCT_PRICE contains the value zero by default if no value is assigned to it.

Answer

PRODUCT_NAME cannot contain duplicate values.

Question 6

The CUSTOMERS table has a CUST_LAST_NAME column of data type VARCHAR2.
The table has two rows whose COST_LAST_MANE values are Anderson and Ausson.
Which query produces output for CUST_LAST_SAME containing Oder for the first row and Aus for the
second?

Accepted Answer

SELECT REPLACE (REPLACE(cust_last_name,'son',''),'An','O') FROM customers;

Answer

SELECT REPLACE (TRIM(TRALING'son' FROM cust_last_name),'An','O') FROM customers;

Answer

SELECT INITCAP (REPLACE(TRIM('son' FROM cust_last_name),'An','O')) FROM customers;

Answer

SELECT REPLACE (SUBSTR(cust_last_name,-3),'An','O') FROM customers;

Question 7

Which three statements are true about indexes and their administration in an Oracle database?

Accepted Answer

An index can be created as part of a CREATE TABLE statement.

Accepted Answer

A DROP INDEX statement always prevents updates to the table during the drop operation

Accepted Answer

A descending index is a type of function-based index

Answer

An INVISIBLE index is not maintained when Data Manipulation Language (DML) is performed on its
underlying table.

Answer

A UNIQUE and non-unique index can be created on the same table column

Answer

If a query filters on an indexed column then it will always be used during execution of the query

Question 8

Which three are true about granting object privileges on tables, views, and sequences?

Accepted Answer

UPDATE can be granted only on tables and views.

Accepted Answer

REFERENCES can be granted only on tables and views.

Accepted Answer

ALTER can be granted only on tables and sequences.

Answer

DELETE can be granted on tables, views, and sequences.

Answer

INSERT can be granted on tables, views, and sequences.

Answer

SELECT can be granted only on tables and views.

Question 9

The EMPLOYEES table contains columns EMP_ID of data type NUMBER and HIRE_DATE of data type
DATE
You want to display the date of the first Monday after the completion of six months since hiring.
The NLS_TERRITORY parameter is set to AMERICA in the session and, therefore, Sunday is the first
day of the week Which query can be used?

Accepted Answer

SELECT emp_id,NEXT_DAY(ADD_MONTHS(hite_date,6),'MONDAY') FROM employees;

Answer

SELECT emp_id,ADD_MONTHS(hire_date,6), NEXT_DAY('MONDAY') FROM employees;

Answer

SELECT emp_id,NEXT_DAY(MONTHS_BETWEEN(hire_date,SYSDATE),6) FROM employees;

Answer

SELECT emp_id,NEXT_DAY(ADD_MONTHS(hire_date,6),1) FROM employees;

Question 10

Which three statements are true about views in an Oracle database?

Accepted Answer

Rows inserted into a table using a view are retained in the table if the view is dropped

Accepted Answer

Views have no segment.

Accepted Answer

A view can be created that refers to a non-existent table in its defining query.

Answer

A SELECT statement cannot contain a where clause when querying a view containing a WHERE
clause in its defining query

Answer

Views can join tables only if they belong to the same schema.

Answer

Views have no object number.

Quiz Oracle Database SQL

Free Test - Simulator Oracle Database SQL

Oracle Database SQL Practice test unlocks all online simulator questions

What to expect from our Oracle Database SQL practice tests and how to prepare for any exam?