Oracle Crusher

Question 1

A view and a synonym are created as follows:

create view dept_v as select * from dept;
create synonym dept_s for dept_v;

Subsequently the table DEPT is dropped. What will happen if you query the synonym DEPT_S?

Accepted Answer

There will be an error because the view will be invalid

Answer

There will not be an error if you first recompile the view with the command ALTER VIEW DEPT_V COMPILE FORCE;

Answer

There will be an error because the view will be dropped implicitly when the table was dropped.

Answer

There will not be an error because the synonym addresses the view, which still exists, but there will be no rows returned

Answer

There will be an error because the synonym will be invalid

Question 2

Which two statements are true regarding the DELETE and TRUNCATE commands?

Accepted Answer

DELETE can be used to remove only rows from only one table at a time

Accepted Answer

DELETE and TRUNCATE can be used on a table that is a parent of a referential integrity constraint having ON DELETE rule

Answer

DELETE can be used to remove only rows from multiple tables at a time

Answer

DELETE can be used to remove data from specific columns as well as complete rows

Answer

DELETE can be used only on a table that is a parent of a referential integrity constraint

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

User JOHN updates some rows but does not commit the changes. User ROOPESH queries the row that JOHN updated.

Which of the following statements is true?

Accepted Answer

ROOPESH will see the old versions of the rows

Answer

ROOPESH will see the state of the data as it was when JOHN last created a SAVEPOINT.

Answer

ROOPESH will be able to see the new values, but only if he logs in as JOHN.

Answer

ROOPESH will not be able to see the rows because they will be locked.

Question 5

The user Alice wants to grant all users query privileges on her DEPT table. Which SQL statement accomplishes this?

Accepted Answer

GRANT select ON dept
TO PUBLIC

Answer

GRANT select ON dept
TO ALL_USERS

Answer

GRANT QUERY ON dept
TO ALL_USERS

Answer

GRANT select ON dept
TO ALL

Question 6

Which comparison operator cannot be used with multiple-row subqueries?

Accepted Answer

All the above can be used

Answer

NOT IN

Answer

ANY

Answer

ALL

Answer

IN

Question 7

SELECT COUNT(DISTINCT SUBSTR(first_name, 1,1))
FROM employees;

Accepted Answer

The query will execute successfully without any modifications

Answer

A single-row function nested inside a group function is now allowed

Answer

The GROUP BY clause is required to successfully run this query

Answer

Removing the DISTINCT qualifier will fix the error in the query

Question 8

Which two statements are true regarding single row functions?
(Choose two.)

Accepted Answer

They can return a data type value different from the one that is referenced

Accepted Answer

They always return a single result row for every row of a queried table

Answer

They accept only a single argument

Answer

They can be nested only to two levels

Answer

Arguments can only be column values or constant

Question 9

View the Exhibit and examine the structure of the PRODUCTS table. You want to display only those product names with their list prices where the list price is at least double the minimum price.

The report should start with the product name having the maximum list price satisfying this condition.

Evaluate the following SQL statement:

SQL>SELECT prod_name, prod_list_price
FROM products
WHERE prod_list_price >= 2 * prod_min_price

Which ORDER BY clauses can be added to the above SQL statement to get the correct output?
(Choose all that apply.)

Table PRODUCTS
prod_id NUMBER(6) NOT NULL
prod_name VARCHAR2(50) NOT NULL
prod_list_price NUMBER(8,2) NOT NULL

Accepted Answer

ORDER BY prod_list_price DESC, prod_name;

Accepted Answer

ORDER BY prod_list_price DESC, prod_name DESC;

Answer

ORDER BY prod_name DESC, prod_list_price DESC;

Answer

ORDER BY prod_name, (2*prod_min_price)DESC;

Answer

ORDER BY (2*prod_min_price)DESC, prod_name;

Question 10

Consider the following three SQL statements. Choose the most appropriate option.

1. DELETE FROM CITY WHERE CNT_CODE = 1;
2. DELETE CITY WHERE CNT_CODE = 1;
3. DELETE (SELECT * FROM CITY WHERE CNT_CODE = 1);

Accepted Answer

Statements 1, 2, and 3 will produce the same result.

Answer

Statements 1 and 2 will produce the same result; statement 3 will produce a different result.

Answer

Statements 1, 2, and 3 will produce different results.

Answer

Statements 1 and 2 will produce the same result; statement 3 will error out.

Question 11

You need to display the first names of all customers from the customers table that contain the character 'e' and have the character 'a' in the second last position.

Which query would give the required output?

Accepted Answer

SELECT cust_first_name
FROM customers
WHERE INSTR(cust_first_name, 'e')<>0 AND 
	 SUBSTR(cust_first_name, -2, 1) = 'a';

Answer

SELECT cust_first_name
FROM customers
WHERE INSTR(cust_first_name, 'e')<>'' AND 
	 SUBSTR(cust_first_name, -2, 1) = 'a';

Answer

SELECT cust_first_name
FROM customers
WHERE INSTR(cust_first_name, 'e') IS NOT NULL AND 
	 SUBSTR(cust_first_name, -2, 1) = 'a';

Answer

SELECT cust_first_name
FROM customers
WHERE INSTR(cust_first_name, 'e')<> 0 AND 
	 SUBSTR(cust_first_name, LENGTH(cust_first_name), -2) = 'a';

Question 12

Which component is configured at database startup and cannot be dynamicall

Accepted Answer

Redo log buffer

Answer

Java pool

Answer

Shared pool

Answer

Streams pool

Answer

None of the above

Question 13

Top N analysis requires _____ and _____. 
(Choose two.)

Accepted Answer

an ORDER BY clause

Accepted Answer

an inline view and an outer query

Answer

the use of rowid

Answer

only an inline view

Answer

a GROUP BY clause

Question 14

Study these statements:
CREATE TABLE tab1 (c1 NUMBER(1), c2 DATE);
ALTER SESSION SET nls_date_format='dd-mm-yy';
INSERT INTO tab1 VALUES (1.1,'31-01-07');

Will the insert succeed?
(Choose the best answer)

Accepted Answer

The insert will succeed

Answer

The insert will fail for both reason A and B

Answer

The insert will fail because the 1.1 is too long

Answer

The insert will fail because the '31-01-07' is a string, not a date

Question 15

View the Exhibit to examine the description for the SALES table. Which views can have all DML operations performed on it?

Table SALES
prod_id NUMBER NOT NULL
cust_id NUMBER NOT NULL

Accepted Answer

CREATE VIEW v1 
AS SELECT * FROM SALES
WHERE time_id <= SYSDATE – 2*365
WITH CHECK OPTION;

Accepted Answer

CREATE VIEW v3
AS SELECT * FROM SALES
WHERE cust_id = 2034
WITH CHECK OPTION;

Answer

CREATE VIEW v2 
AS SELECT prod_id, cust_id, time_id FROM SALES
WHERE time_id <= SYSDATE – 2*365
WITH CHECK OPTION;

Answer

CREATE VIEW v4 
AS SELECT prod_id, cust_id, SUM(quantity_sold) FROM SALES
WHERE time_id <= SYSDATE – 2*365
GROUP BY prod_id, cust_id
WITH CHECK OPTION;

Question 16

Which two of the following conditions are equivalent to each other?

Accepted Answer

WHERE COMMISSION_PCT IS NULL

Accepted Answer

WHERE NOT(COMMISSION_PCT IS NOT NULL)

Answer

WHERE COMMISSION_PCT IN (NULL)

Answer

WHERE COMMISSION_PCT = NULL

Question 17

Which statement adds a column called salary to the employees table having 100 rows, which cannot contain null?

Accepted Answer

ALTER TABLE EMPLOYEES
ADD SALARY NUMBER(8,2) DEFAULT 0 NOT NULL;

Answer

ALTER TABLE EMPLOYEES
ADD SALARY NUMBER(8,2) NOT NULL;

Answer

ALTER TABLE EMPLOYEES
ADD SALARY NUMBER(8,2) DEFAULT NOT NULL;

Answer

ALTER TABLE EMPLOYEES
ADD SALARY NUMBER(8,2) DEFAULT CONSTRAINT p_nn NOT NULL;

Oracle Crusher

Create a free account to finish this quiz

Questions in this quiz (17)

Information

Feedback

What do you think of the new look?