1. Which is the subset of SQL commands used to manipulate Oracle Database structures, including tables?

Data Definition Language (DDL)

2. Explain UNION, MINUS, UNION ALL and INTERSECT?

UNION - returns all distinct rows selected by either query

MINUS - returns all distinct rows selected by the first query but not by the second.

UNION ALL - returns all rows selected by either query, including all duplicates.

INTERSECT - returns all distinct rows selected by both queries.

3. What operator tests column for the absence of data?

IS NULL operator

4. SELECT statements :

SELECT column_name(s) FROM table_name
SELECT DISTINCT column_name(s) FROM table_name
SELECT column FROM table WHERE column operator value
SELECT column FROM table WHERE column LIKE pattern
SELECT column,SUM(column) FROM table GROUP BY column
SELECT column,SUM(column) FROM table GROUP BY column HAVING SUM(column) condition value

Note that single quotes around text values and numeric values should not be enclosed in quotes. Double quotes may be acceptable in some databases.

5. Locking - mechanisms intended to prevent destructive interaction between users accessing data. Locks are used to achieve.

6. Correlated Subquery

          Correlated Subquery is a subquery that is evaluated once for each row processed by the parent statement. Parent statement can be Select, Update or Delete. Use CRSQ to answer multipart questions whose answer depends on the value in each row processed by parent statement.

7. Read the following code:

22. CREATE OR REPLACE TRIGGER update_show_gross
23. {trigger information}
24. BEGIN
25. {additional code}
26. END;

The trigger code should only execute when the column, COST_PER_TICKET, is greater than $3. Which trigger information will you add?

•  WHEN (new.cost_per_ticket > 3.75)
•  WHEN (:new.cost_per_ticket > 3.75
•  WHERE (new.cost_per_ticket > 3.75)
•  WHERE (:new.cost_per_ticket > 3.75)

8. What occurs if a procedure or function terminates with failure without being handled?

•  Any DML statements issued by the construct are still pending and can be committed or rolled back.
•  Any DML statements issued by the construct are committed
•  Unless a GOTO statement is used to continue processing within the BEGIN section, the construct terminates.
•  The construct rolls back any DML statements issued and returns the unhandled exception to the calling environment.

9. What operator performs pattern matching?

LIKE operator.

10. What is the use of DESC in SQL?

DESC has two purposes. It is used to describe a schema as well as to retrieve rows from table in descending order.

Explanation : The query SELECT * FROM EMP ORDER BY ENAME DESC will display the output sorted on ENAME in descending order.