SQL Chap 8 - Managing Views

Chapter 8 - Managing Views

OCA/OCP: Introduction to Oracle9i SQL Study Guide
by Chip Dawes and Biju Thomas


Review Questions


1. A view created with which option makes sure that rows added to the base table through the view are accessible to the view?


A. WHERE
B. WITH READ ONLY
C. WITH CHECK OPTION
D. CREATE OR REPLACE VIEW
----
WITH CHECK OPTION makes sure that the new rows added or the rows updated are accessible to the view. The WHERE clause in the view definition limits the rows selected in the view from the base table.
Ans: C.


2. A view is created using the following code. What operations are permitted on the view?


CREATE VIEW USA_STATES
AS SELECT * FROM STATE
WHERE CNT_CODE = 1
WITH READ ONLY;

A. SELECT
B. SELECT, UPDATE
C. SELECT, DELETE
D. SELECT, INSERT

---
When the view is created with the READ ONLY option, only reads are allowed from the view.
Ans: A.


3. How do you remove the view USA_STATES from the schema?


A. ALTER VIEW USA_STATES REMOVE;
B. DROP VIEW USA_STATES;
C. DROP VIEW USA_STATES CASCADE;
D. DROP USA_STATES;

-----
A view is dropped using the DROP VIEW view_name; command.
Ans: B.


4. Which data dictionary view has information on the columns in a view that are updatable?


A. USER_VIEWS
B. USER_UPDATABLE_COLUMNS
C. USER_COLUMNS
D. USER_COLUMNS_UPDATABLE
-----
The USER_UPDATABLE_COLUMNS view shows the columns that can be updated.
Ans: B.


5. Which option in view creation creates a view even if there are syntax errors?


A. CREATE FORCE VIEW ...
B. CREATE OR REPLACE VIEW ...
C. CREATE OR REPLACE VIEW FORCE ...
D. CREATE VIEW ... IGNORE ERRORS

----
The CREATE FORCE VIEW statement creates an invalid view, even if there are syntax errors. Normally, a view will not be created if there are compilation errors.
Ans: A.


6. In a join view, on how many base tables can you perform a DML operation (UPDATE/INSERT/DELETE) in a single step?


A. One
B. The number of base tables in the view definition
C. The number of base tables minus one
D. None

----
You can perform an INSERT, UPDATE, or DELETE operation on the columns involving only one base table at a time. There are also some restrictions on the DML operations you perform on a join view.
Ans: A.


7. The following code is used to define a view. The EMP table does not have a primary key or any other constraints.


CREATE VIEW MYVIEW AS
SELECT DISTINCT ENAME, SALARY
FROM EMP
WHERE DEPT_ID = 10;

Which operations are allowed on the view?

A. SELECT, INSERT, UPDATE, DELETE
B. SELECT, UPDATE
C. SELECT, INSERT, DELETE
D. SELECT
E. SELECT, UPDATE, DELETE
----
Since the view definition includes a DISTINCT clause, only queries are allowed on the view.
Ans: D.


8. Which two statements are used to modify a view definition?


A. ALTER VIEW
B. CREATE OR REPLACE VIEW
C. REPLACE VIEW
D. CREATE FORCE VIEW
E. CREATE OR REPLACE FORCE VIEW
-----
The OR REPLACE option in the CREATE VIEW statement is used to modify the definition of the view. The FORCE option can be used to create the view with errors. The ALTER VIEW statement is used to compile a view or to add or modify constraints on the view.
Ans: B, E.


9. You create a view based on the EMPLOYEES table using the following SQL.


CREATE VIEW MYVIEW AS SELECT * FROM EMPLOYEES;

You modify the table to add a column named EMP_SSN. What do you need to do to have this new column appear in the view?

A. Nothing, since the view definition is selecting all columns, the new column will appear in the view automatically.
B. Recompile the view using ALTER VIEW MYVIEW RECOMPILE.
C. Re-create the view using CREATE OR REPLACE VIEW.
D. Add the column to the view using ALTER VIEW MYVIEW ADD EMP_SSN.
----
When you modify the base table, the view becomes invalid. Recompiling the view will make it valid, but the new column will not be available in the view. This is because when you create the view using *, Oracle expands the column names and stores the column names in the dictionary.
Ans: C


10. You can view the constraints on the objects in your schema in the USER_CONSTRAINTS dictionary view. The CONSTRAINT_TYPE column shows the type of constraint. What is the type of constraint created when you create a view with the WITH CHECK OPTION clause?


A. R
B. C
C. V
D. F

----
The constraint type will be V for the constraints created on views with the WITH CHECK OPTION clause.
Ans: C.


11. Which types of constraints can be created on a view?


A. Check, NOT NULL
B. Primary key, foreign key, unique key
C. Check, NOT NULL, primary key, foreign key, unique key
D. No constraints can be created on a view.

----
You can create primary key, foreign key, and unique key constraints on a view. The constraints on views are not enforced by Oracle. To enforce a constraint it must be defined on a table.

Ans: B.


12. Which is a valid status of a constraint created on a view?


A. DISABLE VALIDATE
B. DISABLE NOVALIDATE
C. ENABLE NOVALIDATE
D. All of the above
----
Since the constraints on the view are not enforced by Oracle, the only valid status of a constraint can be DISABLE NOVALIDATE. You must specify this status when creating constraints on a view.
Ans: B.


13. The SALARY column of the EMPLOYEE table is defined as NUMBER (8,2), and the COMMISSION_PCT column is defined as NUMBER(2,2). A view is created with the following code.


CREATE VIEW EMP_COMM AS
SELECT LAST_NAME,
SALARY * NVL(COMMISSION_PCT,0) Commission
FROM EMPLOYEES;

What is the datatype of the COMMISSION column in the view?

A. NUMBER (8,2)
B. NUMBER (10,2)
C. NUMBER
D. FLOAT
----
When numeric operations are performed using numeric datatypes in the view definition, the resulting column will be a floating datatype, which is NUMBER without any precision or scale.
Ans: C.


14. Which clause in the SELECT statement is not supported in a view definition subquery?


A. GROUP BY
B. HAVING
C. CUBE
D. FOR UPDATE OF
E. ORDER BY
-----
The FOR UPDATE OF clause is not supported in the view definition. The FOR UPDATE clause locks the rows, so it is not allowed.
Ans: D.


15. The EMPLOYEE table has the following columns:


EMP_ID NUMBER (4)
EMP_NAME VARCHAR2 (30)
SALARY NUMBER (5,2)
DEPT_ID VARCHAR2 (2)

Which query will show the top-five highest paid employees?

A. 
SELECT * FROM
(SELECT EMP_NAME, SALARY
 FROM   EMPLOYEES
 ORDER BY SALARY ASC)
WHERE ROWNUM <= 5;

B. 
SELECT EMP_NAME, SALARY FROM
(SELECT *
 FROM   EMPLOYEES
 ORDER BY SALARY DESC)
WHERE ROWNUM < 5;

C. 
SELECT * FROM
(SELECT EMP_NAME, SALARY
 FROM   EMPLOYEES
 ORDER BY SALARY DESC)
WHERE ROWNUM <= 5;

D. 
SELECT EMP_NAME, SALARY
(SELECT *
 FROM   EMPLOYEES
 ORDER BY SALARY DESC)
WHERE ROWNUM = 5;

----
The top five salaries can be found using an inline view with the ORDER BY clause. Oracle9i optimizer understands the top-'n' rows query.
Ans: C.


16. The EMPLOYEE table has the following columns:


EMP_ID NUMBER (4) PRIMARY KEY
EMP_NAME VARCHAR2 (30)
SALARY NUMBER (5,2)
DEPT_ID VARCHAR2 (2)

A view is defined using the following SQL.

CREATE VIEW EMP_IN_DEPT10 AS
SELECT * FROM EMPLOYEE
WHERE DEPT_ID = 'HR';

Which INSERT statement will succeed through the view?

A.
INSERT INTO EMP_IN_DEPT10 VALUES (1000,
'JOHN',1500,'HR');

B.
INSERT INTO EMP_IN_DEPT10 VALUES (1001,
NULL,1700,'AM');

C.
INSERT INTO EMP_IN_DEPT10 VALUES (1002,
'BILL',2500,'AC');

D.
All of the above
----
The view is based on a single table and the only constraint on the table is the primary key. Although the view defined with a WHERE clause, we have not enforced that check while using DML statements through the WITH CHECK OPTION clause.
Ans: D.


17. To be able to modify a join view, the view definition should not contain which of the following in the top-level query? (Choose all that apply.)


A. DISTINCT operator
B. ORDER BY clause
C. Aggregate functions such as SUM, AVG, and COUNT
D. WHERE clause
E. GROUP BY clause
F. ROWNUM pseudo-column
----
To be able to update a base table using the view, the view definition should not have a DISTINCT clause, GROUP BY clause, START WITH clause, CONNECT BY clause, ROWNUM, set operators (UNION, UNION ALL, INTERSECT, or MINUS), or subquery in the SELECT clause.
Ans: A, C, E, F


18. What is an inline view?


A. A subquery appearing in the WHERE clause
B. A subquery appearing in the FROM clause
C. A view created using the same column names of the base table
D. A view created with an ORDER BY clause
-----
A subquery appearing in the FROM clause of the SELECT statement is similar to defining and using a view, hence the name inline view. The subquery in the FROM clause is enclosed in parentheses and may be given an alias name. The columns selected in the subquery can be referenced in the parent query, just as you would select from any normal table or view.
Ans: B.


19. Which of the following two statements are true?


A. A view can be created before creating the base table.
B. A view cannot be created before creating the base table.
C. A view will become invalid if the base table's column referred to in the view is altered.
D. A view will become invalid if any column in the base table is altered.
----
The CREATE FORCE VIEW statement can be used to create a view before its base table is created. Any modification to the table will invalidate the view. Use the ALTER VIEW COMPILE statement to recompile the view.
Ans: A, D.


20. Which pseudo-column (with an inline view) can be used to get the top-n rows from a table?


A. ROWID
B. ROW_ID
C. ROWNUM
D. ROW_NUM
----
The ROWNUM pseudo-column gives a record number for each row returned. The row number is assigned as the record is fetched; the number is not stored in the database.

Ans: C.

SQL Chap 7 - Managing Tables and Constraints

Chapter 7 - Managing Tables and Constraints


OCA/OCP: Introduction to Oracle9i SQL Study Guide
by Chip Dawes and Biju Thomas

Review Questions


1. The STATE table has the following constraints (the constraint status is shown in parentheses):

Primary key           pk_state (enabled)
Foreign key           COUNTRY table-fk_state (enabled)
Check constraint      ck_cnt_code (disabled)
Check constraint      ck_st_code (enabled)
Not null constraint   nn_st_name (enabled)

You execute the following SQL:
CREATE TABLE STATE_NEW AS SELECT * FROM STATE;
How many constraints will there be in the new table?

A. 0
B. 1
C. 3
D. 5
E. 2

----
When you create a table using CTAS (CREATE TABLE AS), only the NOT NULL constraints are copied.
Ans: B.


2. Which line of code has an error?


1 CREATE TABLE FRUITS_VEGETABLES
2 (FRUIT_TYPE VARCHAR2,
3 FRUIT_NAME CHAR (20),
4 QUANTITY NUMBER);

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

---
A VARCHAR2 datatype should always specify the maximum length of the column.
Ans: B.


3. Which statement successfully adds a new column ORDER_DATE to the table ORDERS?


A. ALTER TABLE ORDERS ADD COLUMN ORDER_DATE DATE;
B. ALTER TABLE ORDERS ADD ORDER_DATE (DATE);
C. ALTER TABLE ORDERS ADD ORDER_DATE DATE;
D. ALTER TABLE ORDERS NEW COLUMN ORDER_DATE TYPE DATE;

----
The correct statement is C. When adding only one column, the column definition need not be enclosed in parentheses.
Ans: C


4. What are the special characters allowed in a table name? (Choose two answers.)


A. &
B. #
C. @
D. $

------
Only three special characters ($, _, and #) are allowed in the table names along with letters and numbers.
Ans: B, D.


5. Consider the following statement:

CREATE TABLE MY_TABLE (
1ST_COLUMN NUMBER,
2ND_COLUMN VARCHAR2 (20));

Which of the following best describes this statement?

A. Tables cannot be created without a defining a primary key. The table definition here is missing the primary key.
B. The reserved word COLUMN cannot be part of the column name.
C. The column names are invalid.
D. There is no maximum length specified for the first column definition. You must always specify a length for character and numeric columns.
E. There is no error in the statement.

----
All identifiers (column names, table names, and so on) must begin with an alphabetic character. An identifier can contain alphabetic characters, numbers, and the special characters $, #, and _.
Ans: C


6. Which dictionary view would you query to list only the tables you own?


A. ALL_TABLES
B. DBA_TABLES
C. USER_TABLES
D. USR_TABLES
----
The USER_TABLES view provides information on the tables owned by the user who has logged on that session. DBA_TABLES will have all the tables in the database, and ALL_TABLES will have the tables owned by you as well as the tables to which you have access. USR_TABLES is not a valid dictionary view.
Ans: C.


7. The STATE table has six rows. You issue the following command:


ALTER TABLE STATE ADD UPDATE_DT DATE DEFAULT SYSDATE;

Which of the following is correct?

A. A new column, UPDATE_DT, is added to the STATE table, and its contents for the existing rows are NULL.
B. Since the table is not empty, you cannot add a new column.
C. The DEFAULT value cannot be provided if the table has rows.
D. A new column, UPDATE_DT, is added to STATE and is populated with the current system date and time.
----

When a default value is specified in the new column added, the column values for the existing rows are populated with the default value.
Ans: D.


8. The HIRING table has the following data:


EMPNO HIREDATE
--------- ----------
1021 12-DEC-00
3400 24-JAN-01
2398 30-JUN-01

What will be result of the following query?

SELECT hiredate+1 FROM hiring WHERE empno = 3400;

A. 4-FEB-01
B. 25-JAN-01
C. N-02
D. None of the above
----
In date arithmetic, adding 1 is equivalent to adding 24 hours. To add 6 hours to a date value with time, add 0.25.
Ans: B


9. What is the default length of a CHAR datatype column, if no length is specified in the table definition?


A. 256
B. 1000
C. 64
D. 1
E. You must always specify a length for CHAR columns.
-----
If you do not specify a length for a CHAR datatype column, the default length of 1 is assumed.
Ans: D


10. Which statement will remove the column UPDATE_DT from table STATE?


A. ALTER TABLE STATE DROP COLUMN UPDATE_DT;
B. ALTER TABLE STATE REMOVE COLUMN UPDATE_DT;
C. DROP COLUMN UPDATE_DT FROM STATE;
D. ALTER TABLE STATE SET UNUSED COLUMN UPDATE_DT;
E. You cannot drop a column from the table.
----
You can use the DROP COLUMN clause with the ALTER TABLE statement to drop a column. There is no separate DROP COLUMN statement or a REMOVE clause in the ALTER TABLE statement. The SET UNUSED clause is used to mark the column as unused. This column can be dropped later using the DROP UNUSED COLUMNS clause.
Ans: A


11. Which option is not available in Oracle when modifying tables?


A. Add new columns
B. Rename existing column
C. Drop existing column
D. All of the above
----
You cannot rename an existing column using the ALTER TABLE statement. To rename the column, you must re-create the table with the new name.
Ans: B.


12. Which one of the following statements will create a primary key for the CITY table with columns STATE_CD and CITY_CD?


A. CREATE PRIMARY KEY ON CITY (STATE_CD, CITY_CD);
B. CREATE CONSTRAINT PK_CITY PRIMARY KEY ON CITY (STATE_CD, CITY_CD);
C. ALTER TABLE CITY ADD CONSTRAINT PK_CITY PRIMARY KEY (STATE_CD, CITY_CD);
D. ALTER TABLE CITY ADD PRIMARY KEY (STATE_CD, CITY_CD);
E. ALTER TABLE CITY ADD PRIMARY KEY CONSTRAINT PK_CITY ON (STATE_CD, CITY_CD);
---
The ALTER TABLE statement is used to create and remove constraints. Option D would work if the keyword CONSTRAINT were included between ADD and PRIMARY.
Ans: C


13. Which of the following check constraints will raise an error? (Choose all that apply.)

A. CONSTRAINT ck_gender CHECK (gender IN ('M', 'F'))
B. CONSTRAINT ck_old_order CHECK (order_date > (SYSDATE - 30))
C. CONSTRAINT ck_vendor CHECK (vendor_id IN (SELECT vendor_id FROM vendors))
D. CONSTRAINT ck_profit CHECK (gross_amt > net_amt)
----
Ans: B, C. Check constraints cannot reference the SYSDATE function or other tables.


14. Consider the datatypes DATE, TIMESTAMP (TS), TIMESTAMP WITH LOCAL TIME ZONE (TSLTZ), INTERVAL YEAR TO MONTH (IY2M), INTERVAL DAY TO SECOND (ID2S). Which operations are not allowed by the Oracle9i database? (Choose all that apply.)


A. DATE + DATE
B. TSLTZ - DATE
C. TSLTZ + IY2M
D. TS * 5
E. ID2S / 2
F. IY2M + IY2M
G. ID2S + IY2M
H. DATE - IY2M

----
You cannot add two DATE datatypes, but you can subtract to find the difference in days. Multiplication and division operators are permitted only on INTERVAL datatypes. When adding or subtracting INTERVAL datatypes, both INTERVAL datatypes should be of the same category.
Ans: A, D, G.


15. A constraint is created with the DEFERRABLE INITIALLY IMMEDIATE clause. What does this mean?


A. Constraint checking is done only at commit time.
B. Constraint checking is done after each SQL statement is executed, but you can change this behavior by specifying SET CONSTRAINTS ALL DEFERRED.
C. Existing rows in the table are immediately checked for constraint violation.
D. The constraint is immediately checked in a DML operation, but subsequent constraint verification is done at commit time.
---
DEFERRABLE specifies that the constraint can be deferred using the SET CONSTRAINTS command. INITIALLY IMMEDIATE specifies that the constraint's default behavior is to validate the constraint for each SQL statement executed.
Ans: B.


16. What is the default precision for fractional seconds in a TIMESTAMP datatype column?


A. 0
B. 2
C. 6
D. 9
----
Ans: C. The default precision is 6 digits. The precision can range from 0 to 9.


17. Which datatype stores the time zone information along with the date value?


A. TIMESTAMP
B. TIMESTAMP WITH LOCAL TIME ZONE
C. TIMESTAMP WITH TIME ZONE
D. DATE
E. Both options B and C
-----
Only TIMESTAMP WITH TIME ZONE stores the time zone information as a displacement from UTC. TIMESTAMP WITH LOCAL TIME ZONE adjusts the time to database's time zone before storing it.
Ans: C.


18. You have a large job that will load many thousands of rows into your ORDERS table. To speed up the loading process, you want to temporarily stop enforcing the foreign key constraint FK_ORDERS. Which of the following statements will satisfy your requirement?


A. ALTER CONSTRAINT FK_ORDERS DISABLE;
B. ALTER TABLE ORDERS DISABLE FOREIGN KEY FK_ORDERS;
C. ALTER TABLE ORDERS DISABLE CONSTRAINT FK_ORDERS;
D. ALTER TABLE ORDERS DISABLE ALL CONSTRAINTS;
----
You can disable constraints by specifying its constraint name. You may enable the constraint after the load and avoid the constraint checking while enabling using the ALTER TABLE ORDERS MODIFY CONSTRAINT FK_ORDERS ENABLE NOVALIDATE; command.
Ans: C


19. You are connected to the database as user JOHN. You need to rename a table named NORDERS to NEW_ORDERS, owned by SMITH. Consider the following two statements:


1. RENAME SMITH.NORDERS TO NEW_ORDERS;
2. ALTER TABLE SMITH.NORDERS RENAME TO NEW_ORDERS;

Which of the following is correct?

A. Statement 1 will work; statement 2 will not.
B. Statements 1 and 2 will work.
C. Statement 1 will not work; statement 2 will work.
D. Statements 1 and 2 will not work

----
RENAME can be used to rename objects owned the user. ALTER TABLE should be used to rename tables owned by another user. To do so, you must have the ALTER privilege on the table or the ALTER ANY TABLE privilege.
Ans: C.


20. Which two declarations define the maximum length of a CHAR datatype column in bytes?


A. CHAR (20)
B. CHAR (20) BYTE
C. CHAR (20 BYTE)
D. BYTE (20 CHAR)
E. CHAR BYTE (20)
---
Ans: A, C. The maximum lengths of CHAR and VARCHAR2 columns can be defined in characters or bytes. BYTE is the default.

SQL Chap 6 - Modifying Data

Chapter 6 - Modifying Data

OCA/OCP: Introduction to Oracle9i SQL Study Guide
by Chip Dawes and Biju Thomas

Review Questions


1. Which of the following statements will succeed?

(1)
merge into product_descriptions p
using (select product_id, language_id
             ,translated_name
from products_for_2003) p2003
where (p.product_id = p2003.product_id)
when matched then update
set p.language=p2003.language_id
   ,p.translated_name = p2003.translated_name
when not matched then insert
   (p.product_id, p.language_id
    ,p.translated_name)
   values (p2003.product_id,p2003.language_id
          ,p2003.translated_name);

(2)
merge into product_descriptions p
using (select product_id, language_id
             ,translated_name
from products_for_2003) p2003
on (p.product_id = p2003.product_id)
when matched then update
set p.language=p2003.language_id
   ,p.translated_name = p2003.translated_name
when not matched then insert
   (p.product_id, p.language_id
    ,p.translated_name)
   values (p2003.product_id,p2003.language_id
          ,p2003.translated_name);
(3)
merge into product_descriptions p
using (select product_id, language_id
             ,translated_name
from products_for_2003) p2003
join on (p.product_id = p2003.product_id)
when matched then update
set p.language=p2003.language_id
   ,p.translated_name = p2003.translated_name
when not matched then insert
   (p.product_id, p.language_id
    ,p.translated_name)
   values (p2003.product_id,p2003.language_id
          ,p2003.translated_name);

A. Statement 1
B. Statement 2
C. Statement 3
D. They all fail.

----
The correct syntax uses an ON clause as in option B. The WHERE in option A and the JOIN ON clause in option C are not valid.
Ans: B.


2. Which of the following statements will not implicitly begin a transaction?


A. INSERT
B. UPDATE
C. DELETE
D. SELECT FOR UPDATE
E. None of the above; they all implicitly begin a transaction.

----
If a transaction is not currently open, any INSERT, UPDATE, MERGE, DELETE, SELECT FOR UPDATE, or LOCK statement will implicitly begin a transaction.
Ans: E.


3. If Julio executes a LOCK TABLE IN SHARE ROW EXCLUSIVE MODE statement, with which of the following statements will Marisa not wait for Julio's commit or rollback?


A. INSERT
B. SELECT FOR UPDATE
C. LOCK TABLE IN SHARE MODE
D. LOCK TABLE IN EXCLUSIVE MODE
E. None of the above; all will wait.

---
The row share exclusive mode will block other share, exclusive, and row exclusive locks, but not row share locks.
Ans: B


4. Which of the following statements does not end a transaction?


A. LOCK TABLE IN EXCLUSIVE MODE
B. COMMIT
C. ALTER USER
D. CREATE INDEX

----
COMMIT, ROLLBACK, and any DDL statement ends a transaction. DDL is automatically committed. LOCK TABLE is DML, like INSERT, UPDATE, DELETE, or MERGE, and requires a commit or rollback.
Ans: A.


5. Choose the maximum number of tables into which rows can be inserted via a single INSERT statement.

A. 1
B. 2
C. No more than 16
D. Unlimited

----
A single INSERT statement can insert data into an unlimited number of tables. This multiple-table insert capability is new in Oracle9i.
Ans: D.


6. Can you execute an ALTER INDEX REBUILD while there are uncommitted updates on a table?


A. No, it will always fail with a resource busy error.
B. Yes, but you must specify the keyword WAIT to wait for the commit or rollback.
C. Yes, the row exclusive locks from the UPDATE statements only block other changes to the same rows.
D. Yes, but only if the updates do not change the indexed columns.

----
The row exclusive locks from the update will block all DDL, including DDL on the indexes—it does not matter which columns the index is on. You cannot specify WAIT on DDL.
Ans: A


7. Which of the following statements will begin a transaction using transaction-level read consistency?

A. ALTER SESSION USE TRANSACTION CONSISTENCY;
B. BEGIN TRANSACTION USING TRANSACTION CONSISTENCY;
C. BEGIN SERIALIZABLE TRANSACTION;
D. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

----
Transaction-level consistency is obtained with a serializable isolation level. An isolation level of read committed identifies statement-level read consistency.
Ans: D.


8. Which of the following statements will improve the performance of a full-table scan on the PROCESS_ORDER_STAGE table?

A. DELETE FROM process_order_stages;
B. TRUNCATE TABLE process_order_stage;
C. CREATE INDEX ord_idx2 ON process_order_stage (customer_id);
D. ALTER SESSION SET hash_area_size 16613376;

-----
A TRUNCATE operation will reset the high-water mark on a table, so when a full-table scan (that scans to the high-water mark) is executed against the table, it will run very fast. Delete operations do not affect the high-water mark or full-scan performance. Indexes and hash_area_size do not affect full-scan performance.

Ans: B.


9. The following table shows two concurrent transactions. What happens at time point 9?

-------------------------------------------------
Session A               Time         Session B 
 

UPDATE customers SET     6
region='H' WHERE
state='43' and
county='046';            
   
                         7          UPDATE customers
                                    SET mgr=4567
                                    WHERE state='47' and
                                    county='072';

UPDATE customers SET     8
region='H' WHERE
state='47' and
county='072';

   
                         9          UPDATE customers
                                    SET mgr=4567
                                    WHERE state='43' and
                                    county='046';

------------------------------------------------------------

A. Session B will wait for session A to commit or roll back.
B. Session A will wait for session B to commit or roll back.
C. A deadlock will occur, and both sessions will hang until the DBA kills one or until one of the users cancels their statement.
D. A deadlock will occur, and Oracle will cancel one of the statements.
E. Both sessions are not updating the same column, so no waiting or deadlocks will occur.

-----
At time point 8, session A will wait for session B. At time point 9, a deadlock will occur; Oracle will recognize it and cancel one of the statements. Oracle locks to the granularity of a row, so even though the columns are different, the locks will still block each other.

Ans: D


10. The following table shows two concurrent transactions. Which statement about the result returned in session A at time point 16 is most true?

-------------------------------------------------------------------
Session A                       Time              Session B 
-------------------------------------------------------------------
SELECT SUM(deposit_amt)         12
FROM transaction_log
WHERE deposit_date >
TRUNC(SYSDATE);           
   
                                13     INSERT INTO transaction_log
                                       (deposit_date, deposit_amt)
                                       VALUES (SYSDATE, 6247.00);

                                14     COMMIT; 
 
Table scan for the active       15
SELECT reaches the data 
block where session B's row 
was inserted.

Table scan complete results     16
returned
---------------------------------------------------------------------

A. The results would include the changes committed by transaction B at time point 14.

B. The results would not include the changes committed by transaction B at time point 14.

C. The results would include the changes committed by transaction B at time point 14 if the two sessions were connected to the database as the same user.

D. Session A would raise a "snapshot too old" exception.
----------
Statement-level read consistency would ensure that the data visible to each statement does not change while the statement is executing. The "snapshot too old" exception might be raised if there were a lot of other transactions committing to the database between time points 12 and 16, but if this exception were raised, the table scan would neither complete nor return results.
Ans: B.


11. The following table shows two concurrent transactions. Which statement about the results returned in session A at time points 16 and 18 is most true?

-----------------------------------------------------------------------
Session A                      Time                     Session B 
------------------------------------------------------------------------
SET TRANSACTION ISOLATION      11
LEVEL READ CONSISTENT;


SELECT SUM(deposit_amt)        12
FROM transaction_log
WHERE deposit_date >
TRUNC(SYSDATE);

   
                               13           INSERT INTO transaction_log
                                            (deposit_date, deposit_amt)
                                            VALUES (SYSDATE, 6247.00);


                               14             COMMIT; 
 
Table scan for the active      15
SELECT reaches the data block 
where session B's row was 
inserted.
   
Table scan complete,           16
results returned.
   

SELECT SUM(deposit_amt)        17
FROM transaction_log
WHERE deposit_date >
TRUNC(SYSDATE);

   
Table scan complete,           18 
results returned.
--------------------------------------------------------------------------

A. The results would be identical.
B. The results would be different.
C. The results would be identical only if the two sessions were connected to the database as the same user.
D. Both statements would include the data committed by transaction B at time point 14.

-------
The read-consistent isolation level is statement-level read consistency, so each statement sees the committed data that existed at the beginning of the statement. The committed data at time point 17 includes session B's commit at time point 14.
Ans: B.


12. The following table shows two concurrent transactions. Which statement about the results returned in session A at time point 16 and 18 is most true?

 
----------------------------------------------------------------------------
Session A                        Time                     Session B 
----------------------------------------------------------------------------
SET TRANSACTION ISOLATION        11
LEVEL SERIALIZABLE;


SELECT SUM(deposit_amt)          12
FROM transaction_log
WHERE deposit_date >
TRUNC(SYSDATE);

                                 13           INSERT INTO transaction_log
                                              (deposit_date, deposit_amt)
                                              VALUES (SYSDATE, 6247.00);

                                 14           COMMIT; 
 
Table scan for the active        15 
SELECT reaches the data block
where session B's row was 
inserted.

   
Table scan complete results      16
returned.
   
SELECT SUM(deposit_amt)          17
FROM transaction_log
WHERE deposit_date >
TRUNC(SYSDATE);

   
Table scan complete results      18
returned.
---------------------------------------------------------------------------

A. The results would be identical.
B. The results would be different.
C. The results would be identical only if the two sessions were connected to the database as the same user.
D. Both statements would include the data committed by transaction B at time point 14.

---
The serializable isolation level is transaction-level read-consistency, so both of session A's SELECT statements see the same data image. Neither would include the changes committed at time point 14.
Ans: A.


13. You have a DELETE statement that will generate a large amount of undo. One rollback segment, named RB_LARGE, is larger than the others. How would you force the use of this rollback segment for the DELETE operation?


A. ALTER SESSION USE ROLLBACK SEGMENT rb_large;
B. SET TRANSACTION USE ROLLBACK SEGMENT rb_large;
C. BEGIN WORK USING ROLLBACK SEGMENT rb_large
D. You cannot force the use of a specific rollback segment.

----
The SET TRANSACTION statement can be used to force the use of a specific rollback segment, provided that the SET TRANSACTION statement begins the transaction.
Ans: B.


14. The following table describes the DEPARTMENTS table.

--------------------------------------------------------------
Column Name    dept_id   dept_name   mgr_id   location_id
--------------------------------------------------------------
Key Type       pk
NULLs/Unique   NN
FK Table 
Datatype       NUMBER    VARCHAR2    NUMBER   NUMBER
Length         4         30          6        4
Default Value  None      None        None     None
------------------------------------------------------------- 

Which of the following INSERT statements will raise an exception?

A.
INSERT INTO departments (dept_id, dept_name, location_
id) VALUES(280,'Security',1700);

B.
INSERT INTO departments
VALUES(280,'Security',1700);

C.
INSERT INTO departments
VALUES(280,'Corporate Giving',266,1700);

D.
None of these statements will raise an exception.

----
Option B will raise an exception because there are not enough column values for the implicit column list (all columns).

Ans: B


15. The SALES table contains the following data:


SELECT channel_id, COUNT(*)
FROM sales
WHERE channel_id IN ('T','I')
GROUP BY channel_id;

C COUNT(*)
- ----------
T 12000
I 24000

How many rows will be inserted into the NEW_CHANNEL_SALES table with the following SQL statement?

INSERT FIRST
 WHEN channel_id ='C' THEN
   INTO catalog_sales (prod_id,time_id,promo_id
                       ,amount_sold)
       VALUES (prod_id,time_id,promo_id,amount_sold)
 WHEN channel_id ='I' THEN
   INTO internet_sales (prod_id,time_id,promo_id
                        ,amount_sold)
       VALUES (prod_id,time_id,promo_id,amount_sold)
 WHEN  channel_id IN ('I','T') THEN
   INTO new_channel_sales (prod_id,time_id,promo_id
                           ,amount_sold)
       VALUES (prod_id,time_id,promo_id,amount_sold)
SELECT channel_id,prod_id,time_id,promo_id,amount_sold
FROM sales;

A. 0
B. 12,000
C. 24,000
D. 36,000

-----
The FIRST clause tells Oracle to execute only the first WHEN clause that evaluates to TRUE. This statement will insert 24,000 rows into the INTERNET_SALES table and 0 rows into the NEW_CHANNEL_ SALES table. If the ALL clause were used, 36,000 rows would be inserted into the NEW_CHANNEL_SALES table.
Ans: A.


16. How many rows will be counted in the last SQL statement that follows?

SELECT COUNT(*) FROM emp;
  120 returned

INSERT INTO emp (emp_id)
   VALUES (140);
SAVEPOINT emp140;

INSERT INTO emp (emp_id)
   VALUES (141);
INSERT INTO emp (emp_id)
   VALUES (142);
INSERT INTO emp (emp_id)
   VALUES (143);
TRUNCATE TABLE emp;
INSERT INTO emp (emp_id)
   VALUES (144);

ROLLBACK;

SELECT COUNT(*) FROM emp;

A. 121
B. 1
C. 0
D. 143
----
The TRUNCATE statement is DDL and performs an implicit commit. After the TRUNCATE statement, there are 0 rows in the table. The one row that was inserted was removed when the ROLLBACK statement was executed.
Ans: C


17. Which of the following statements will raise an exception in a transaction that starts with SET TRANSACTION READ ONLY?


A. ALTER SYSTEM
B. SELECT
C. ALTER USER
D. SET ROLE
---
A read-only transaction will raise an exception if data is changed. Altering a user will change data.
Ans: C


18. Which of the following statements will raise an exception?


A. LOCK TABLE SALES IN EXCLUSIVE MODE;

B. LOCK TABLE SALES IN ROW SHARE EXCLUSIVE MODE;

C. LOCK TABLE SALES IN SHARE ROW EXCLUSIVE MODE;

D. LOCK TABLE SALES IN ROW EXCLUSIVE MODE;
---
There are five types of table locks: row share, row exclusive, share, share row exclusive, and exclusive. Row share exclusive mode does not exist.
Ans: B.


19. Which of the following INSERT statements will raise an exception?


A.
INSERT INTO EMP SELECT * FROM NEW_EMP;

B.
INSERT FIRST WHEN DEPT_NO IN (12,14) THEN INSERT INTO
EMP SELECT * FROM NEW_EMP;

C.
INSERT FIRST WHEN DEPT_NO IN (12,14) THEN INTO EMP
SELECT * FROM NEW_EMP;

D.
INSERT INTO ALL WHEN DEPT_NO IN (12,14) THEN INTO EMP
SELECT * FROM NEW_EMP;
----
The keywords INSERT INTO are required in single-table INSERT statements, but are not valid in multiple-table INSERT statements.
Ans: B.


20. What will the salary of employee Arsinoe be at the completion of the following SQL statements?

UPDATE emp
  SET salary = 1000
  WHERE name = 'Arsinoe';
SAVEPOINT Point_A

UPDATE emp
  SET salary = salary * 1.1
  WHERE name = 'Arsinoe';
SAVEPOINT Point_B;

UPDATE emp
  SET salary = salary * 1.1
  WHERE name = 'Berenike';
SAVEPOINT point_C;

ROLLBACK TO SAVEPOINT point_b;
COMMIT;

UPDATE emp
  SET salary = 1500
  WHERE name = 'Arsinoe';
SAVEPOINT point_d;

ROLLBACK TO point_d;

COMMIT;

A. 1000
B. 1100
C. 1111
D. 1500
----
The final rollback (to point_d) will roll the changes back to just after setting the salary to 1500.
Ans: D.

Merging Rows into a Table

Merging Rows into a Table

The MERGE statement is used to both update and insert rows in a table. The MERGE statement has a join specification that describes how to determine if an update or insert should be executed. Figure 6.4 shows the syntax of the MERGE statement.


The WHEN MATCHED predicate specifies how to update the existing rows. The WHEN NOT MATCHED predicate specifies how to create rows that do not exist.

In the following example, we have a new pricing sheet for products in category 33. This new pricing data has been loaded into the NEW_PRICES table. We need to update the PRODUCT_INFORMATION table with these new prices. The NEW_PRICES table contains updates to existing rows in the PRODUCT_INFORMATION table as well as new products. The new products need to be inserted and the existing products need to be updated.

   SELECT product_id,category_id,list_price,min_price
   FROM oe.product_information
   WHERE category_id=33;

   PRODUCT_ID CATEGORY_ID LIST_PRICE  MIN_PRICE
   ---------- ----------- ---------- ----------
         2986          33        125        111
         3163          33         35         29
         3165          33         40         34
         3167          33         55         47
         3216          33         30         26
         3220          33         45         36

   SELECT *
   FROM new_prices;

   PRODUCT_ID LIST_PRICE  MIN_PRICE
   ---------- ---------- ----------
         2986        135        121
         3163         40         32
         3164         40         35
         3165         40         37
         3166         50         45
         3167         55         50
         3216         30         26
         3220         45         36

We use the MERGE statement to perform an update/insert of the new pricing data into the PRODUCT_INFORMATION table, as follows:

   MERGE INTO oe.product_information pi
   USING (SELECT product_id, list_price, min_price
       FROM new_prices) NP
   ON (pi.product_id = np.product_id)
   WHEN MATCHED THEN UPDATE SET pi.list_price =np.list_price
                               ,pi.min_price = np.min_price
   WHEN NOT MATCHED THEN INSERT (pi.product_id,pi.category_id
                               ,pi.list_price,pi.min_price)
     VALUES (np.product_id, 33,np.list_price, np.min_price);

   PRODUCT_ID CATEGORY_ID LIST_PRICE  MIN_PRICE
   ---------- ----------- ---------- ----------
         2986          33        135        121 (updated)
         3163          33         40         32 (updated)
         3164          33         40         35 (inserted)
         3165          33         40         37 (updated)
         3166          33         50         45 (inserted)
         3167          33         55         50 (updated)
         3216          33         30         26 (updated)
         3220          33         45         36 (updated)

SQL Chap 5 - Joins and Subqueries

Chapter 5 - Joins and Subqueries

OCA/OCP: Introduction to Oracle9i SQL Study Guide
by Chip Dawes and Biju Thomas


Review Questions

1. Which line of code has an error?

A. SELECT dname, ename
B. FROM emp e, dept d
C. WHERE emp.deptno = dept.deptno
D. ORDER BY 1, 2;
----
When table aliases are defined, you should qualify the column names with the table alias only. In this case, the table name cannot be used to qualify column names. The line in option C should read WHERE e.deptno = d.deptno.
Ans: C.


2. What will be the result of the following query?


SELECT c.cust_id, c.cust_name, o.ord_date, o.prod_id
FROM customers c, orders o
WHERE c.cust_id = o.cust_id (+);

A. List all the customer names in the CUSTOMERS table and the orders they made from the ORDERS table, even if the customer has not placed an order

B. List only the names of customer from the CUSTOMERS table who have placed an order in the ORDERS table

C. List all orders from the ORDERS table, even if there is no valid customer record in the CUSTOMERS table

D. For each record in the CUSTOMERS table, list the information from the ORDERS table
----

An outer-join operator (+) indicates an outer join and is used to display the records, even if there are no corresponding records in the table mentioned on the other side of the operator. Here, the outer-join operator is next to the ORDERS table, so even if there are no corresponding orders from a customer, the result set will have the customer ID and name.
Ans: A.


3. The CUSTOMERS and ORDERS tables have the following data:

SQL> SELECT * FROM customers;

CUST_ CUST_NAME            PHONE           CITY
----- -------------------- --------------- -----------
A0101 Abraham Taylor Jr.                   Fort Worth
B0134 Betty Baylor         972-555-5555    Dallas
B0135 Brian King                           Chicago

SQL> SELECT * FROM orders;

ORD_DATE     PROD_ID CUST_ID   QUANTITY      PRICE
--------- ---------- ------- ---------- ----------
20-FEB-00       1741 B0134            5       65.5
02-FEB-00       1001 B0134           25    2065.85
02-FEB-00       1001 B0135            3      247.9

When the following query is executed, what will be the value of PROD_ID and ORD_DATE for the customer Abraham Taylor Jr.?

SELECT c.cust_id, c.cust_name, o.ord_date, o.prod_id
FROM customers c, orders o
WHERE c.cust_id = o.cust_id (+);

A. NULL, 01-JAN-01
B. NULL, NULL
C. 1001, 02-FEB-00
D. The query will not return customer Abraham Taylor Jr.

----
When an outer join returns values from a table that does not have corresponding records, a NULL is returned.
Ans: B


4. When using ANSI join syntax, which clause is used to specify a join condition?


A. JOIN
B. USING
C. ON
D. WHERE

----
The join condition is specified in the ON clause. The JOIN clause specifies the table to be joined. The USING clause specifies the column names that should be used in the join. The WHERE clause is used to specify additional search criteria to restrict the rows returned.
Ans: C


5. The EMPLOYEES table has EMPLOYEE_ID, DEPARTMENT_ID, and FULL_NAME columns. The DEPARTMENTS table has DEPARTMENT_ID and DEPARTMENT_NAME columns. Which two of the following queries return the department ID, name, and employee name, listing department names even if there is no employee assigned to that department?


A.
SELECT d.department_id, d.department_name, e.full_name
FROM departments d
NATURAL LEFT OUTER JOIN employees e;

B.
SELECT department_id, department_name, full_name
FROM departments
NATURAL LEFT JOIN employees;

C.
SELECT d.department_id, d.department_name, e.full_name
FROM departments d
LEFT OUTER JOIN employees e
USING (d.department_id);

D.
SELECT d.department_id, d.department_name, e.full_name
FROM departments d
LEFT OUTER JOIN employees e
ON (d.department_id = e.department_id);
----
Option A does not work because you cannot qualify column names when using a natural join. Option B works, because the only common column between these two tables is DEPARTMENT_ID. The keyword OUTER is optional. Option C does not work, again because you cannot qualify column names when specifying the USING clause. Option D specifies the join condition explicitly in the ON clause.
Ans: B, D


6. Which two operators are not allowed when using an outer-join operator in the query?


A. OR
B. AND
C. IN
D. =
-----
OR and IN are not allowed in the WHERE clause on the columns where an outer-join operator is specified. You can use AND and = in the outer join.
Ans: A, C.


7. Which two operators are used to add more joining conditions in a multiple-table query?


A. NOT
B. OR
C. AND
D. Comma (,)
----
The operators OR and AND are used to add more joining conditions to the query. NOT is a negation operator, and a comma is used to separate column names and table names.

Ans: B, C


8. The columns of the EMPLOYEES, DEPARTMENTS, and JOBS tables are shown below.

-------------------------------------------
Table       Column Names    Datatype 
-------------------------------------------
EMPLOYEES    EMPLOYEE_ID     NUMBER (6)
             FIRST_NAME      VARCHAR2 (25)
             LAST_NAME       VARCHAR2 (25)
             SALARY          NUMBER (8,2)
             JOB_ID          VARCHAR2 (10)
             MANAGER_ID      NUMBER (6)
             DEPARTMENT_ID   NUMBER (2)
------------------------------------------- 
DEPARTMENTS  DEPARTMENT_ID   NUMBER (2)
             DEPARTMENT_NAME VARCHAR2 (30)
             MANAGER_ID      NUMBER (6)
             LOCATION_ID     NUMBER (4)
--------------------------------------------
JOBS         JOB_ID          VARCHAR2 (10)
             JOB_TITLE       VARCHAR2 (30)
---------------------------------------------

Which assertion about the following query is correct?

SELECT e.last_name, d.department_name, j.job_title
FROM jobs j
INNER JOIN employees e
ON (e.department_id = d.department_id)
JOIN departments d
ON (j.job_id = e.job_id);

A. The query returns all the rows from EMPLOYEE table, where there is a corresponding record in the JOBS table and DEPARTMENTS table.
B. The query fails with an invalid column name error.
C. The query fails because line 3 specifies INNER JOIN, which is not a valid syntax.
D. The query fails because line 5 does not specify the keyword INNER.
E. The query fails because the column names are qualified with the table alias.
---------
The query fails because the d.department_id column is referenced before the DEPARTMENTS table is specified in the JOIN clause. A column can be referenced only after its table is specified.
Ans: B


9. The columns of the EMPLOYEES and DEPARTMENTS tables are shown in question 8. Consider the following three queries using those tables.


1. SELECT last_name, department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

2. SELECT last_name, department_name
FROM employees NATURAL JOIN departments;

3. SELECT last_name, department_name
FROM employees JOIN departments
USING (department_id);

Which of the following assertions best describes the results?

A. Queries 1, 2, and 3 produce the same results.
B. Queries 2 and 3 produce the same result; query 1 produces a different result.
C. Queries 1, 2, and 3 produce different results.
D. Queries 1 and 3 produce the same result; query 2 produces a different result.

------
Since DEPARTMENT_ID and MANAGER_ID are common columns in the EMPLOYEES and DEPARTMENTS tables, a natural join will relate these two tables using the two common columns.
Ans: D


10. The data in the STATE table is as shown:

SQL> SELECT * FROM state;

  CNT_CODE ST_CODE ST_NAME
---------- ------- ------------
         1 TX      TEXAS
         1 CA      CALIFORNIA
        91 TN      TAMIL NADU
         1 TN      TENNESSE
        91 KL      KERALA

Consider the following query:

SELECT cnt_code
FROM   state
WHERE  st_name = (SELECT st_name FROM state
                  WHERE  st_code = 'TN');

Which of the following assertions best describes the results?

A. The query will return the CNT_CODE for the ST_CODE value 'TN'.
B. The query will fail and will not return any rows.
C. The query will display 1 and 91 as CNT_CODE values.
D. The query will fail because an alias name is not used.

--------
There are two records in the STATE table with the ST_CODE value as 'TN'. Since we are using a single-row operator for the subquery, it will fail. Option C would be correct if it used the IN operator instead of = for the subquery.

Ans: B


11. The data in the STATE table is shown in question 10. The data in the CITY table is as shown below.

SQL> SELECT * FROM city;

  CNT_CODE ST   CTY_CODE CTY_NAME
---------- -- ---------- -------------
         1 TX       1001 DALLAS
        91 TN       2243 MADRAS
         1 CA       8099 LOS ANGELES

What is the result of the following query?

SELECT st_name "State Name"
FROM   state
WHERE  (cnt_code, st_code) =
       (SELECT cnt_code, st_code
        FROM   city
        WHERE  cty_name = 'DALLAS');

A. TEXAS
B. The query will fail because CNT_CODE and ST_CODE are not in the WHERE clause of the subquery.
C. The query will fail because more than one column appears in the WHERE clause.
D. TX
-----
The query will succeed, because there is only one row in the city table with the CTY_NAME value 'DALLAS'.
Ans A.


12. Which line of the code below has an error?

1 SELECT department_id, count(*)
2 FROM employees
3 GROUP BY department_id
4 HAVING COUNT(department_id) =
5 (SELECT max(count(department_id))
6 FROM employees
7 GROUP BY department_id);

A. Line 3
B. Line 4
C. Line 5
D. Line 7
E. No error
-----
Ans: E. There is no error in the statement. The query will return the department number where the most employees are working.

Notes: See following test under HR schema

SQL> select count(department_id) from employees;

COUNT(DEPARTMENT_ID)
--------------------
                 106

SQL> select count(department_id) from employees group by department_id;

COUNT(DEPARTMENT_ID)
--------------------
                   1
                   2
                   6
                   1
                  45
                   5
                   1
                  34
                   3
                   6
                   2
                   0

12 rows selected.

SQL> select max(count(department_id)) from employees group by department_id;

MAX(COUNT(DEPARTMENT_ID))
-------------------------
                       45

13. Which query is a correlated subquery?

A.
select cty_name from city
 where  st_code in (select st_code from state
 where st_name = 'TENNESSE'
 and  city.cnt_code = state.cnt_code);

B.
select cty_name
 from   city
 where  st_code in (select st_code from state
 where st_name = 'TENNESSE');

C.
select cty_name
 from city, state
 where  city.st_code = state.st_code
 and    city.cnt_code = state.cnt_code
 and    st_name = 'TENNESSE';

D.
select cty_name
 from city, state
 where  city.st_code = state.st_code (+)
 and    city.cnt_code = state.cnt_code (+)
 and    st_name = 'TENNESSE';

-----
A subquery is correlated when a reference is made to a column from a table in the parent statement.
Ans: A.


14. The COUNTRY table has the following data:

SQL> SELECT * FROM country;

  CNT_CODE CNT_NAME          CONTINENT
---------- ----------------- ---------- 
         1 UNITED STATES     N.AMERICA
        91 INDIA             ASIA
        65 SINGAPORE         ASIA

What value is returned from the subquery when you execute the following?

SELECT CNT_NAME
FROM country
WHERE CNT_CODE =
(SELECT MAX(cnt_code) FROM country);

A. INDIA
B. 65
C. 91
D. SINGAPORE
-----
The subquery returns 91 to the main query.
Ans: C


15. Which line in the following query contains an error?

1 SELECT deptno, ename, sal
2 FROM   emp e1
3 WHERE  sal = (SELECT MAX(sal) FROM emp
4               WHERE  deptno = e1.deptno
5               ORDER BY deptno);

A. Line 2
B. Line 3
C. Line 4
D. Line 5
----
You cannot have an ORDER BY clause in the subquery used in a WHERE clause.
Ans: D.


16. Consider the following query:

    SELECT deptno, ename, salary salary, average,
    salary-average difference
    FROM   emp,
    (SELECT deptno dno, AVG(salary) average FROM emp
     GROUP BY deptno)
    WHERE  deptno = dno
    ORDER BY 1, 2;

Which of the following statements is correct?

A. The query will fail because no alias name is provided for the subquery.
B. The query will fail because a column selected inside the subquery is referenced outside the scope of the subquery.
C. The query will work without errors.
D. GROUP BY cannot be used inside a subquery.
----
The query will work fine, producing the difference between employee's salary and average salary in the department. You do not need to use the alias names because the column names returned from the subquery are different from the column names returned by the parent query.
Ans: C


17. The COUNTRY table has the following data:

SQL> SELECT * FROM country;

  CNT_CODE CNT_NAME             CONTINENT
---------- -------------------- ---------- 
         1 UNITED STATES        N.AMERICA
        91 INDIA                ASIA
        65 SINGAPORE            ASIA

What will be the result of the following query?

INSERT INTO (SELECT cnt_code FROM country
             WHERE continent = 'ASIA')
VALUES (971, 'SAUDI ARABIA', 'ASIA');

A. One row will be inserted into COUNTRY table.
B. WITH CHECK OPTION is missing in the subquery.
C. The query will fail because the VALUES clause is invalid.
D. The WHERE clause cannot appear in the subqueries used in INSERT statements.
----
Because only one column is selected in the subquery to which we are doing the insert, only one column value should be supplied in the VALUES clause. The VALUES clause can have only CNT_CODE value (971).
Ans: C


18. In ANSI SQL, a self-join can be represented by using which of the following? (Choose the best answer.)


A. NATURAL JOIN clause
B. CROSS JOIN clause
C. JOIN .. USING clause
D. JOIN ON clause
E. All of the above
-----
D. NATURAL JOIN and JOIN ... USING clauses will not allow alias names to be used. Since a self-join is getting data from the same table, you must include alias names and qualify column names.

Ans D.


19. Consider the following queries:

1. SELECT last_name, salary,
        (SELECT (MAX(sq.salary) - e.salary)
        FROM   employees sq
        WHERE  sq.department_id = e.department_id) DSAL
FROM   employees e
WHERE  department_id = 20;

2. SELECT last_name, salary, msalary - salary dsal
FROM   employees e,
       (SELECT department_id, MAX(salary) msalary
        FROM   employees
        GROUP BY department_id) sq
WHERE e.department_id = sq.department_id
AND   e.department_id = 20;

3. SELECT last_name, salary, msalary - salary dsal
FROM   employees e INNER JOIN
       (SELECT department_id, MAX(salary) msalary
        FROM   employees
        GROUP BY department_id) sq
ON      e.department_id = sq.department_id
WHERE   e.department_id = 20;

4. SELECT last_name, salary, msalary - salary dsal
FROM   employees INNER JOIN
       (SELECT department_id, MAX(salary) msalary
        FROM   employees
        GROUP BY department_id) sq
USING   (department_id)
WHERE   department_id = 20;

Which of the following assertions best describes the results?

A. Queries 1 and 2 produce identical results, and queries 3 and 4 produce identical results, but queries 1 and 3 produce different results.

B. Queries 1, 2, 3, and 4 produce identical results.

C. Queries 1, 2, and 3 produce identical results; query 4 will produce errors.

D. Queries 1 and 3 produce identical results; queries 2 and 4 will produce errors.

E. Queries 1, 2, 3, and 4 produce different results.

F. Queries 1 and 2 are valid SQL; queries 3 and 4 are not valid.

-------
All four queries produce the same result. The first query uses a scalar subquery in the SELECT clause. The rest of queries use an inline view. All of the queries display the last name, salary, and difference of salary from the highest salary in the department for all employees in department 20.
Ans: B


20. The columns of the EMPLOYEES and DEPARTMENTS tables are shown in question 8. Which query will show us the top-five highly paid employees in the company?


A.
SELECT last_name, salary
FROM employees
WHERE ROWNUM <= 5
ORDER BY salary DESC;

B.
SELECT last_name, salary
FROM (SELECT *
FROM employees
WHERE ROWNUM <= 5
ORDER BY salary DESC )
WHERE ROWNUM <= 5;

C.
SELECT * FROM
(SELECT last_name, salary
FROM employees
ORDER BY salary)
WHERE ROWNUM <= 5;

D.
SELECT * FROM
(SELECT last_name, salary
FROM employees
ORDER BY salary DESC)
WHERE ROWNUM <= 5;

-----
To find the top-n rows, you can select the necessary columns in an inline view with an ORDER BY DESC clause. An outer query limiting the rows to n will give the result.
Ans: D