Q1. The STUDENT_GRADES table has these columns:
STUDENT_ID NUMBER(12)SEMESTER_END DATEGPA NUMBER(4,3)
The registrar has requested a report listing the students' grade point averages (GPA), sorted from highest grade point average to lowest within each semester, starting from the earliest date. Which statement accomplishes this?

A.
SELECT student_id, semester_end, gpaFROM student_gradesORDER BY semester_end DESC, gpa DESC;

B.
SELECT student_id, semester_end, gpaFROM student_gradesORDER BY semester_end ASC, gpa ASC;

C.
SELECT student_id, semester_end, gpaFROM student_gradesORDER BY semester_end, gpa DESC;

D.
SELECT student_id, semester_end, gpaFROM student_gradesORDER BY gpa DESC, semester_end DESC;

E.
SELECT student_id, semester_end, gpaFROM student_gradesORDER BY gpa DESC, semester_end ASC;


Q2. You define a multiple-row subquery in the WHERE clause of an SQL query with a comparison operator "=".

What happens when the main query is executed?

A.
The main query executes with the first value returned by the subquery.
B.
The main query executes with the last value returned by the subquery.
C.
The main query executes with all the values returned by the subquery.
D.
The main query fails because the multiple-row subquery cannot be used with the comparison operator "=".
E.
You cannot define a multiple-row subquery in the WHERE clause of a SQL query.


Q3. Which SELECT statement should you use to extract the year from the system date and display it in the format "1998"?

A.
SELECT TO_CHAR(SYSDATE,'yyyy') FROM dual;

B.
SELECT TO_DATE(SYSDATE,'yyyy') FROM dual;

C.
SELECT DECODE(SUBSTR(SYSDATE, 8), 'YYYY') FROM dual;

D.
SELECT DECODE(SUBSTR(SYSDATE, 8), 'year') FROM dual;

E.
SELECT TO_CHAR(SUBSTR(SYSDATE, 8,2),'yyyy') FROM dual;

Q4. Evaluate this SQL statement:
SELECT e.employee_id, (.15* e.salary) + (.5 * e.commission_pct) + (s.sales_amount * (.35 * e.bonus)) AS CALC_VALUEFROM employees e, sales sWHERE e.employee_id = s.emp_id;
What will happen if you remove all the parentheses from the calculation?

A.
The value displayed in the CALC_VALUE column will be lower.
B.
The value displayed in the CALC_VALUE column will be higher.
C.
There will be no difference in the value displayed in the CALC_VALUE column.
D.
An error will be reported.

Dear Robert,

please create the tables and make your tests. That is what everybody has to do to find the correct answer - or somebody takes an hour of time to read your threads.

Regards
Manfred Peter
(Alligator Company)
http:/www.alligatorsql.com