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

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

Correct Answer : 5
Explanation: The USING clause specifies which columns to test for equality when two tables are joined. It can be used instead of an ON clause in the JOIN operations that have an explicit join clause.
Syntax
USING ( Simple-column-Name [ , Simple-column-Name ]* )
The columns listed in the USING clause must be present in both of the two tables being joined. The USING clause will be transformed to an ON clause that checks for equality between the named columns in the two tables.
When a USING clause is specified, an asterisk (*) in the select list of the query will be expanded to the following list of columns (in this order):
All the columns in the USING clause
All the columns of the first (left) table that are not specified in the USING clause
All the columns of the second (right) table that are not specified in the USING clause
An asterisk qualified by a table name (for example, COUNTRIES.*) will be expanded to every column of that table that is not listed in the USING clause.
If a column in the USING clause 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 column in the USING clause point to the column in the second (right) table.

Examples : The following query performs an inner join between the COUNTRIES table and the CITIES table on the condition that COUNTRIES.COUNTRY is equal to CITIES.COUNTRY:
SELECT * FROM COUNTRIES JOIN CITIES USING (COUNTRY)
The next query is similar to the one above, but it has the additional join condition that COUNTRIES.COUNTRY_ISO_CODE is equal to CITIES.COUNTRY_ISO_CODE:
SELECT * FROM COUNTRIES JOIN CITIES USING (COUNTRY, COUNTRY_ISO_CODE)

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.

Correct Answers: 3
Explanation: You should remember that SQL uses sets as the foundation for most of its queries. So, the question is how can we use set theory to find the 2nd highest salary in the table above? Think about it on your own for a bit - even if you do not remember much about sets, the answer is very easy to understand and something that you might be able to come up with on your own. What if we try to exclude the highest salary value from the result set returned by the SQL that we run? If we remove the highest salary from a group of salary values, then we will have a new group of values whose highest salary is actually the 2nd highest in the original Employee table.

So, if we can somehow select the highest value from a result set that excludes the highest value, then we would actually be selecting the 2nd highest salary value. Think about that carefully and see if you can come up with the actual SQL yourself before you read the answer that we provide below. Here is a small hint to help you get started: you will have to use the "NOT IN" SQL operator.
Now, here is what the SQL will look like:
SELECT MAX(Salary) FROM Employee WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee )
Running the SQL above would return us "450?, which is of course the 2nd highest salary in the Employee table.

The SQL above first finds the highest salary value in the Employee table using "(select MAX(Salary) from Employee)". Then, adding the "WHERE Salary NOT IN" in front basically creates a new set of Salary values that does not include the highest Salary value. For instance, if the highest salary in the Employee table is 200,000 then that value will be excluded from the results using the "NOT IN" operator, and all values except for 200,000 will be retained in the results.

This now means that the highest value in this new result set will actually be the 2nd highest value in the Employee table. So, we then select the max Salary from the new result set, and that gives us 2nd highest Salary in the Employee table. And that is how the query above works.

An alternative solution using the not equals SQL operator

We can actually use the not equals operator - instead of the NOT IN operator as an alternative solution to this problem.

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

Correct Answer : 5
Explanation: Specify the name of the table to be dropped. Oracle Database automatically performs the following operations:
All rows from the table are dropped.
All table indexes and domain indexes are dropped, as well as any triggers defined on the table, regardless of who created them or whose schema contains them. If table is partitioned, then any corresponding local index partitions are also dropped.
All the storage tables of nested tables and LOBs of table are dropped.
When you drop a range-, hash-, or list-partitioned table, then the database drops all the table partitions. If you drop a composite-partitioned table, then all the partitions and subpartitions are also dropped.
When you drop a partitioned table with the PURGE keyword, the statement executes as a series of subtransactions, each of which drops a subset of partitions or subpartitions and their metadata. This division of the drop operation into subtransactions optimizes the processing of internal system resource consumption (for example, the library cache), especially for the dropping of very large partitioned tables. As soon as the first subtransaction commits, the table is marked UNUSABLE. If any of the subtransactions fails, the only operation allowed on the table is another DROP TABLE ... PURGE statement. Such a statement will resume work from where the previous DROP TABLE statement failed, assuming that you have corrected any errors that the previous operation encountered.
You can list the tables marked UNUSABLE by such a drop operation by querying the status column of the *_TABLES, *_PART_TABLES, *_ALL_TABLES, or *_OBJECT_TABLES data dictionary views, as appropriate.
For an index-organized table, any mapping tables defined on the index-organized table are dropped.
For a domain index, the appropriate drop routines are invoked. Please refer to Oracle Data Cartridge Developer's Guide for more information on these routines.
If any statistic types are associated with the table, then the database disassociates the statistics types with the FORCE clause and removes any user-defined statistics collected with the statistics type.
If the table is not part of a cluster, then the database returns all data blocks allocated to the table and its indexes to the tablespaces containing the table and its indexes.
To drop a cluster and all its the tables, use the DROP CLUSTER statement with the INCLUDING TABLES clause to avoid dropping each table individually. See DROP CLUSTER.
If the table is a base table for a view, a container or master table of a materialized view, or if it is referenced in a stored procedure, function, or package, then the database invalidates these dependent objects but does not drop them. You cannot use these objects unless you re-create the table or drop and re-create the objects so that they no longer depend on the table.
If you choose to re-create the table, then it must contain all the columns selected by the subqueries originally used to define the materialized views and all the columns referenced in the stored procedures, functions, or packages. Any users previously granted object privileges on the views, stored procedures, functions, or packages need not be regranted these privileges.
If the table is a master table for a materialized view, then the materialized view can still be queried, but it cannot be refreshed unless the table is re-created so that it contains all the columns selected by the defining query of the materialized view.
If the table has a materialized view log, then the database drops this log and any other direct-path INSERT refresh information associated with the table.
Restriction on Dropping Tables You cannot directly drop the storage table of a nested table. Instead, you must drop the nested table column using the ALTER TABLE ... DROP COLUMN clause.

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

Related Questions