Q:1. WHICH STATEMENT ABOUT MULTIPLE COLUMN SUBQUERIES IS TRUE?

ANS: A NON-PAIR WISE COMPARISON PRODUCES
A CROSS PRODUCT.

Q:2. THE EMPLOYEE TABLE CONTAINS THESE COLUMNS:

FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)

EVALUATE THESE TWO SQL
STATEMENT

SELECT CONTACT(FIRST_NAME, LAST_NAME) LENGTH(CONTACT (FIRST_NAME, LAST_NAME)) FROM EMPLOYEE
WHERE UPPER(LAST_NAME) LIKE В‘%JВ’ OR
UPPER(LAST_NAME) LIKE В‘%KВ’
OR UPPER(LAST_NAME) LIKE В‘%LВ’;


SELECT INTCAP(FIRST)NAME), INITCAP(LAST_NAME),
LENGTH(LAST_NAME) +
LENGTH(FIRST_NAME) FROM EMPLOYEE
WHERE INITCAP(SUBSTR(LAST_NAME,1,1)) IN (В‘JВ’,В’KВ’,В’LВ’);

HOW WILL THE RESULTS DIFFER?


ANS: THE
STATEMENT WILL RETRIVE SAME DATA FROM THE DATABASE BUT WILL DISPLAY IT DIFFERENTLY.

Q:3. YOU RECEIVED AN INTEGRATED CONSTRAINT ERROR BECAUSE A
CHILD RECORD WAS FOUND WHAT COULD YOU DO TO MAKE THE SATEMENT EXECUTE ?

ANS: DELETE THE CHILD RECORD FIRST.


Q:4.WHICH STATEMENT DESCRIBES
THE USE OF A GROUP FUNCTION?

ANS:A GROUP FUNCTION PRODUCES ON RESULT FROM MANY ROWS PER GROUP.


Q:5.THE EMPLOYEE TABLE CONTAINS THESE
COLUMNS:

ID NUMBER(9) PK
LAST _NAME VARCHAR2(25) NN
DEPT_ID NUMBER(9)

EVALUATE THIS SQL SCRIPT

DEFINE ID_2 = 93004
SELECT *
FROM
EMPLOYE WHERE ID = (%ID_2)

WHICH CHANGES SHOULD YOU MAKE TO SCRIPT SO THAT IT WILL EXECUTE?

ANS:NO CHANGE IS NEEDED.

Q:6.EVALULATE THIS
SQL STATEMENT

SELECT E.ID(.15*E.SALARY) + (.25 * E.BONUS)) (S.SALE_AMOUNT *
(.15* E.COMMISSION_PCT))
FROM EMPLOYEE, SALES
WHERE E.ID =
S.EMP_ID;

WHAT WOULD HAPPEN IF YOU REMOVED ALL PARENTHESES FROM CALCULATION?

ANS: RESULT WILL BE SAME.

Q:7.WHICH IS NOT AN SQL * PLUS
COMMAND?

ANS: UPDATE.

Q:8.WHEN SELECTING DATA WHICH STATEMENT IS VALID ABOUT PROJECTION?

ANS: PROJECT ALLOWS YOU TO CHOOSE COLUMNS.


Q:9.GIVE THIS EXECUTABLE SECTION OF THE PL/SQL BLOCK:

BEGIN
FOR EMPLOYEE_RECORD IN SALARY_CURSOR
LOOP
EMPLOYEE_ID_TABLE(EMPLOYEE) :=
EMPLOYEE_RECORD.LAST_NAME;
ENDLOOP
CLOSE SALARY_CURSOR;
END;

WHY DOES THIS SECTION CAUSE AN ERROR?

ANS:THE CURSOR DOES NOT NEED TO BE
EXPLICITY CLOSED.


Q:10.THE EMPLOYEE TABLE CONTAINS THESE COLUMNS:

ID NUMBER(9)
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)

COMMISSION NUMBER(7,12)

YOU NEED TO DISPLAY THE CURRENT COMMISSION FOR ALL EMPLOYEES DESIRED RESULTS ARE
1. DISPLAY THE COMMISSION MULTIPIED
BY 1.5
2. EXCLUDE EMPLOYEE WITH ZERO COMMISSION
3. DISPLAY A ZERO FOR EMPLOYEE WITH NULL COMMISSION VALUE

EVALUATE THIS SQL STATEMENT


SELECT ID, LAST_NAME, FIRST_NAME, COMMISSION * 1.5 FROM EMPLOYEE
WHERE COMMISSION 0;

WHICH OF THE DESIRED RESULTS DOES THE STATEMENT
PROVIDE?

ANS: TWO OF THE DESIRED RESULTS.

---