1.Under which circumstance should you create an index on a table?

Ans:Two columns are consistently used
in the WHERE clause join condition of SELECT statements.

2.The PRODUCT table contains these columns:
ID NUMBER(7) PK
SALE_PRICE NUMBER(7,2)


Evaluate these two SQL statements:

1. SELECT MAX(sale_price), MIN(sale_price), AVG(sale_price) FROM product;

2. SELECT
ROUND(MAX(sale_price),2), ROUND(MIN(sale_price),2), ROUND(AVG(sale_price),2) FROM product
GROUP BY sale_price;

How will the results differ?


Ans: Statement 1 will display three values; statement 2 will display three values for each sale price.

3.Which query will you use to display
the names of all the tables you can access?

ANS:SELEC object_name
FROM all_objects
WHERE object_type = TABLE;

4.Review this SQL
statement:
SELECT ename, empno, sal
FROM emp WHERE deptno = (SELECT deptno FROM dept
WHERE UPPER(loc) = UPPER &loc))
When executing this
statement, which two could result?

Ans: The statement executes successfully if the LOC column in the DEPT table has unique values.
The
statement fails if the values returned from a multiple-row subquery are compared with an equality operator in the main query.

5.Which statement
would you use to remove the EMPLOYEE_ID_PK PRIMARY KEY constraint and all depending constraints from the EMPLOYEE table?

Ans: ALTER TABLE
employee
DROP PRIMARY KEYCASCADE;

6:Evaluate this PL/SQL block:
DECLARE
v_lower NUMBER := 2;
v_upper NUMBER := 100;
v_count NUMBER :=
1;
BEGIN
FOR i IN v_lower..v_lower LOOP
INSERT INTO test(results)
VALUES (v_count);
v_count := v_count + 1;
END LOOP;
END;

How
many times will the executable statements inside the FOR LOOP execute?

Ans: 1

7: Given this PL/SQL block:

BEGIN
INSERT INTO
employee(salary, last_name, first_name)
VALUES(35000, Wagner, Madeline);
SAVEPOINT save_a;
INSERT INTO employee(salary, last_name, first_name)

VALUES(40000, Southall, David);
SAVEPOINT save_b;
DELETE FROM employee
WHERE dept_no = 10;
SAVEPOINT save_c;
INSERT INTO
employee(salary, last_name, first_name)
VALUES(25000, Brown, Bert);
ROLLBACK TO SAVEPOINT save_c;
INSERT INTO employee(salary, last_name,
first_name)
VALUE(32000, Dean, Mike);
ROLLBACK TO SAVEPOINT save_b;
COMMIT;
END;

Which two changes to the database will be made
permanent? (Choose two.)

Ans: INSERT INTO employee(salary, last_name, first_name)
VALUES(40000, Southall, David);
INSERT INTO
employee(salary, last_name, first_name)
VALUES(35000, Wagner, Madeline);

8.The SALE table contains these columns:
ID NUMBER(9) PK

SALE_DATE DATE

You need to create a SQL script to prompt the user for an id number. The date of sale for the id number provided should be
updated with todays date.

Which SQL*Plus script would you use to achieve the desired results?

Ans: UPDATE sale
SET sale_date = sysdate

WHERE id = &id

9.Which SELECT statement is an equijoin query between two tables?

Ans: SELECT region.region_name, employee.salaryFROM
region, employee WHERE
region.id =employee.region_no

10.You are proposing to management that all employees receive a 10% salary increase and
receive a commission that is 20% of this newly increased salary. You are writing a report that will display the proposed salary, commission, and
total compensation.Evaluate these SQL statements:

1. SELECT sal * 1.1 "Salary", sal * 1.1 * .2 "Commission", sal * 1.1 + sal * 1.1 * .2
"Compensation"
FROM emp;

2. SELECT sal * 1.1 "Salary", sal * 1.1 * .2 "Commission", (sal * 1.1) + (sal * .2) "Compensation"
FROM
emp;

3. SELECT sal * 1.1 "Salary", sal * .2 "Commission", sal * 1.1 + sal * 1.1 * .2 "Compensation" FROM emp;

What are the results of
these statements?

Ans: Only statement one returns the desired result.

---