Oracle Advanced SQL and PL/SQL Developer Certification Questions and Answers (Dumps and Practice Questions)

Question : You issue the following command to drop the products table:
SQL> DROP TABLE products;
Which three statements are true about the implication of this command?
A. All data along with the table structure is deleted.
B. A pending transaction in the session is committed.
C. All indexes on the table remain but they are invalidated.
D. All views and synonyms remain but they are invalidated.
E. All data in the table is deleted but the table structure remains.

1. A,B,C
2. B,C,E
3. A,C,E
4. D,E,F
4. A,B,D


Correct Answer : 5
Explanation: To drop a table that you no longer need, use the DROP TABLE statement. The table must be contained in your schema or you must have the DROP ANY TABLE system privilege.

Caution:
Before dropping a table, familiarize yourself with the consequences of doing so:
*Dropping a table removes the table definition from the data dictionary. All rows of the table are no longer accessible.
*All indexes and triggers associated with a table are dropped.
*All views and PL/SQL program units dependent on a dropped table remain, yet become invalid (not usable). See "Managing Object Dependencies" for information about how the database manages dependencies.
*All synonyms for a dropped table remain, but return an error when used.
*All extents allocated for a table that is dropped are returned to the free space of the tablespace and can be used by any other object requiring new extents or new objects. All rows corresponding to a clustered table are deleted from the blocks of the cluster. Clustered tables are the subject of Chapter 20, "Managing Clusters".

The following statement drops the hr.int_admin_emp table:

DROP TABLE hr.int_admin_emp;
If the table to be dropped contains any primary or unique keys referenced by foreign keys of other tables and you intend to drop the FOREIGN KEY constraints of the child tables, then include the CASCADE clause in the DROP TABLE statement, as shown below:
DROP TABLE hr.admin_emp CASCADE CONSTRAINTS;
When you drop a table, normally the database does not immediately release the space associated with the table. Rather, the database renames the table and places it in a recycle bin, where it can later be recovered with the FLASHBACK TABLE statement if you find that you dropped the table in error. If you should want to immediately release the space associated with the table at the time you issue the DROP TABLE statement, include the PURGE clause as shown in the following statement:
DROP TABLE hr.admin_emp PURGE;
Perhaps instead of dropping a table, you want to truncate it. The TRUNCATE statement provides a fast, efficient method for deleting all rows from a table, but it does not affect any structures associated with the table being truncated (column definitions, constraints, triggers, and so forth) or authorizations.






Question : Evaluate the following SQL statement:
Which statement is true regarding the above query if one of the values generated by the subquery is null?

1. It produces an error.
2. It executes but returns no rows.
3. It generates output for null as well as the other values produced by the subquery.
4. It ignores the null value and generates output for the other values produced by the subquery.


Correct Answer : 3

Explanation: On the surface, it may appear that the SQL clauses IN and EXISTS are interchangeable. However, they're quite different in how they handle NULL values and may give different results. The problem comes from the fact that, in an Oracle database, a NULL value means unknown, so any comparison or operation against a NULL value is also NULL, and any test that returns NULL is always ignored. For example, neither one of these queries return any rows:
select 'true' from dual where 1 = null; select 'true' from dual where 1 != null;
The value 1 is neither equal nor not equal to NULL. Only IS NULL would return true on a NULL value and return a row.

select 'true' from dual where 1 is null; select 'true' from dual where null is null;

When you use IN, you're telling SQL to take a value and compare it against every value or set of values in a list using =. If any NULL values exist, a row will not be returned--even if both values are NULL.
select 'true' from dual where null in (null); select 'true' from dual where (null,null) in ((null,null)); select 'true' from dual where (1,null) in ((1,null));

An IN is functionally equivalent to the = ANY clause: select 'true' from dual where null = ANY (null); select 'true' from dual where (null,null) = ANY ((null,null)); select 'true' from dual where (1,null) = ANY ((1,null));
When you use an equivalent form of EXISTS, SQL counts rows and ignores the value(s) in the subquery--even if you return NULL.
select 'true' from dual where exists (select null from dual); select 'true' from dual where exists (select 0 from dual where null is null);

The IN and EXISTS are logically the same. The IN clause compares values returned by the subquery and filters out rows in the outer query; the EXISTS clause compares values and filters out rows inside the subquery. In the case of NULL values, the resulting set of rows is the same.
select ename from emp where empno in (select mgr from emp); select ename from emp e where exists (select 0 from emp where mgr = e.empno);

But problems arise when the logic is reversed to use NOT IN and NOT EXISTS, which return different sets of rows (the first query returns 0 rows; the second returns the intended data--they aren't the same query): select ename from emp where empno not in (select mgr from emp); select ename from emp e where not exists (select 0 from emp where mgr = e.empno);
The NOT IN clause is virtually equivalent to comparing each value with = and failing if any test is FALSE or NULL. For example:
select 'true' from dual where 1 not in (null,2); select 'true' from dual where 1 != null and 1 != 2; select 'true' from dual where (1,2) not in ((2,3),(2,null));
select 'true' from dual where (1,null) not in ((1,2),(2,3));
These queries don't return any rows. The second is more obvious, 1 != NULL is NULL, so the whole WHERE condition is false for that row. While these would work:
select 'true' from dual where 1 not in (2,3); select 'true' from dual where 1 != 2 and 1 != 3;

You can still use the NOT IN query from before, as long as you prevent NULL from being returned in the results (again, these both work, but I'm assuming empno is not null, which is a good assumption in this case): select ename from emp where empno not in (select mgr from emp where mgr is not null); select ename from emp where empno not in (select nvl(mgr,0) from emp);
By understanding the difference between IN, EXISTS, NOT IN, and NOT EXISTS, you can avoid a very common problem when NULLs appear in the data of a subquery.





Question :

1. A
2. B
3. C
4. D

Correct Answer : 4
Explanation: NATURAL JOIN operation , A NATURAL JOIN is a JOIN operation that creates an implicit join clause for you based on the common columns in the two tables being joined. Common columns are columns that have the same name in both tables.
A NATURAL JOIN can be an INNER join, a LEFT OUTER join, or a RIGHT OUTER join. The default is INNER join.
If the SELECT statement in which the NATURAL JOIN operation appears has an asterisk (*) in the select list, the asterisk will be expanded to the following list of columns (in this order): All the common columns
Every column in the first (left) table that is not a common column
Every column in the second (right) table that is not a common column
An asterisk qualified by a table name (for example, COUNTRIES.*) will be expanded to every column of that table that is not a common column.
If a common column is referenced without being qualified by a table name, the column reference points to the column in the first (left) table if the join is an INNER JOIN or a LEFT OUTER JOIN. If it is a RIGHT OUTER JOIN, unqualified references to a common column point to the column in the second (right) table.
Syntax : TableExpression NATURAL [ { LEFT | RIGHT } [ OUTER ] | INNER ] JOIN { TableViewOrFunctionExpression | ( TableExpression ) }
Examples : If the tables COUNTRIES and CITIES have two common columns named COUNTRY and COUNTRY_ISO_CODE, the following two SELECT statements are equivalent:

SELECT * FROM COUNTRIES NATURAL JOIN CITIES
SELECT * FROM COUNTRIES JOIN CITIES
USING (COUNTRY, COUNTRY_ISO_CODE)
The following example is similar to the one above, but it also preserves unmatched rows from the first (left) table:
SELECT * FROM COUNTRIES NATURAL LEFT JOIN CITIES

Related Questions

---

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


1. A
2. B
3. C
4. D


Question : Which two statements are true regarding single row functions?
A. MOD: returns the quotient of a division
B. TRUNC: can be used with number and date values
C. CONCAT: can be used to combine any number of values
D. SYSDATE: returns the database server current date and time
E. INSTR: can be used to find only the first occurrence of a character in a string
F. TRIM: can be used to remove all the occurrences of a character from a string

1. A,C
2. A,B
3. D,E
4. B,F
5. B,D




Question : Which two statements are true regarding the count function?
A. The count function can be used only for CHAR, VARCHAR2, and NUMBER data types.
B. Count (*) returns the number of rows including duplicate rows and rows containing null value in any of the columns.
C. Count (cust_id) returns the number of rows including rows with duplicate customer IDs and NULL value in the CUST_ID column.
D. Count (distinct inv_amt) returns the number of rows excluding rows containing duplicates and NULL values in the INV_AMT column.
E. A select statement using the COUNT function with a DISTINCT keyword cannot have a where clause.

1. A,C
2. A,B
3. D,E
4. B,E
5. B,D



Question : View the Exhibits and examine the
structures of the products, sales,
and customers tables.
You need to generate a
report that gives details of the customer's
last name, name of the product,
and the quantity sold
for a customers in 'Tokyo'.
Which two queries
give the required result?

1. A,D
2. A,E
3. B,C
4. B,D
5. A,C



Question : View the Exhibit and examine the structure of the products table.
Evaluate the following query:
What would be the outcome of executing the above SQL statement?

1. It produces an error.
2. It shows the names of all products in the table.
3. It shows the names of products whose list price is the second highest in the table.
4. It shows the names of all products whose list price is less than the maximum list price.



Question : You issued the following command:
SQL> DROP TABLE employees;
Which three statements are true?
A. All uncommitted transactions are committed.
B. All indexes and constraints defined on the table being dropped are also dropped.
C. Sequences used in the employees table become invalid.
D. The space used by the employees table is reclaimed immediately.
E. The employees table can be recovered using the rollback command.
F. The employees table is moved to the recycle bin.

1. A,C,D
2. A,B,F
3. C,D,E
4. B,D,E
5. B,C,F