View the exhibit for the structure of the STUDENT and FACULTY tables. STUDENT
NameNull?Type
-------------------------------------------------- STUDENT_IDNOT NULLNUMBER(2) STUDENT_NAMEVARCHAR2(20) FACULTY_IDVARCHAR2(2) LOCATION_IDNUMBER(2) FACULTY
NameNull?Type
-------------------------------------------------- FACULTY_IDNOT NULLNUMBER(2) FACULTY_NAMEVARCHAR2(20) LOCATION_IDNUMBER(2)
You need to display the faculty name followed by the number of students handled by the faculty at the base location.
Examine the following two SQL statements: Statement 1
SQL>SELECT faculty_name, COUNT(student_id) FROM student JOIN faculty
USING (faculty_id, location_id) GROUP BY faculty_name; Statement 2
SQL>SELECT faculty_name, COUNT(student_id)
FROM student NATURAL JOIN faculty GROUP BY faculty_name;
Which statement is true regarding the outcome?

1. A. Only statement 2 executes successfully and gives the required result.
2. B. Only statement 1 executes successfully and gives the required result.
3. C. Both statements 1 and 2 execute successfully and give different results.
4. D. Both statements 1 and 2 execute successfully and give the same required result.

Correct Answer: B

Which three statements are true about the ALTER TABLE....DROP COLUMN.... command?

1. A. A column can be dropped only if it does not contain any data.
2. B. A column can be dropped only if another column exists in the table.
3. C. A dropped column can be rolled back.
4. D. The column in a composite PRIMARY KEY with the CASCADE option can be dropped.
5. E. A parent key column in the table cannot be dropped.

Correct Answer: BDE

View the exhibits and examine the structures of the COSTS and PROMOTIONS tables.


Evaluate the following SQL statement: SQL> SELECT prod_id FROM costs
WHERE promo_id IN (SELECT promo_id FROM promotions WHERE promo_cost < ALL
(SELECT MAX(promo_cost) FROM promotions GROUP BY (promo_end_date- promo_begin_date)));
What would be the outcome of the above SQL statement?

1. A. It displays prod IDs in the promo with the lowest cost.
2. B. It displays prod IDs in the promos with the lowest cost in the same time interval.
3. C. It displays prod IDs in the promos with the highest cost in the same time interval.
4. D. It displays prod IDs in the promos which cost less than the highest cost in the same time interval.

Correct Answer: D

Evaluate the following statement. INSERT ALL
WHEN order_total < 10000 THEN INTO small_orders
WHEN order_total > 10000 AND order_total < 20000 THEN INTO medium_orders
WHEN order_total > 200000 THEN INTO large_orders
SELECT order_id, order_total, customer_id FROM orders;
Which statement is true regarding the evaluation of rows returned by the subquery in the INSERT statement?

1. A. Each row is evaluated by the first WHEN clause and if the condition is false then the row would be evaluated by the subsequent when clauses.
2. B. All rows are evaluated by all the three WHEN clauses.
3. C. Each row is evaluated by the first WHEN clause and if the condition is true, then the row would be evaluated by the subsequent when clauses.
4. D. The INSERT statement will return an error because the ELSE clause is missing.

Correct Answer: B

Examine the SQL statement used to create the TRANSACTION table. (Choose the best answer.)
SQL > CREATE TABLE transaction (trn_id char(2) primary key,
Start_date date DEFAULT SYSDATE, End_date date NOT NULL);
The value 'A1' does not exist for trn_id in this table.
Which SQL statement successfully inserts a row into the table with the default value for START_DATE?

1. A. INSERT INTO transaction VALUES ('A1', DEFAULT, TO_DATE(DEFAULT+10))
2. B. INSERT INTO transaction VALUES ('A1', DEFAULT, TO_DATE('SYSDATE+10'))
3. C. INSERT INTO transaction (trn_id, end_date) VALUES ('A1', '10-DEC-2014')
4. D. INSERT INTO transaction (trn_id, start_date, end_date) VALUES ('A1', , '10-DEC-2014')

Correct Answer: C