Question
Peereval
Type I
Show students whom Andrew Agassi has evaluated.
Show the student's full name (concatenated and with a column alias)
and major.
Sort by ID.
Use a Type I subquery.
First, show subquery output (the list) and then the outer query
with subquery output.
PEEREVALEVAL ITEM SCORES PEEREVALEVAL ITEMS P EVAL SCORE ID NUMBER F EVAL ID F EVAL ITEM ID P EVAL ITEM_ID VARCHAR2 (10 BYTE SCORE PK EVAL ITEM SCORE (EVAL SCORE ID FK_EVAL ID (EVAL ID NUMBER VARCHAR2 (10 BYTE) NUMBER EVAL DESCRIP VARCHAR2 (35 BYTE PK EVAL ITEMS ID (EVAL ITEM ID) PK-EVAL-ITEMS_ID (EVAL-ITEM-ID) ? FK EVAL ITEM ID (EVAL ITEM ID) ? PK-EVAL-ITEM-SCORE (EVAL-SCORE-ID) PEEREVALATTENDANCES PEEREVALEVALUATIONS PEEREVALSTUDENTS PFATTND WKSP ID VARCHAR2 (2 BYTE) PF ATTND STDID NUMBER P EVAL ID F EVALUATORID VARCHAR2 (10 BYTE) F EVALUATEEID VARCHAR2 (10 BYTE P STDID VARCHAR2 (10 BYTE) VARCHAR2 (12 BYTE) VARCHAR2 (13 BYTE) VARCHAR2 (6 BYTE) VARCHAR2 (10 BYTE) STDFNAME STDLNAME STDMAJOR STD TEAMID VARCHAR2 (8 BYTE) PK_ATTEND (ATTND WKSP ID, ATTND STDID) EVALSEMESTER VARCHAR2 (10 BYTE EVALYEAR FK_STUDENTS (ATTND STDID) FK_WORKSHOPS (ATTND WKSP ID) NUMBER (,0) F PK_EVAL (EVAL ID) FK_EVALUATEEID (EVALUATEEID) PK_STDID (STDID) FKTEAM ID ISTDTEAM ID) PK-STDIDISTDID) ? PK ATTENDUATTNDWKSPID, ATTND_STDID) ?FK-EVALUATORI D EVALUATOR! D) ? PK-EVAL (EVAL-ID) - - ? PEEREVALWORKSHOPS P WKSP ID VARCHAR2 (2 BYTE *WKSP NAME VARCHAR2 (30 BYTE PK_WORKSHOPS (WKSP ID) PK,WORKSHOPS (WKSPID) PEEREVALTEAMS P TEAMID U TEAM NAME VARCHAR2 (30 BYTE F PROJECTID VARCHAR2 (8 BYTE) PEEREVALPROJECTS ? P PROJECTID VARCHAR2 (15 BYTE) VARCHAR2 (15 BYTE) *PROJ NAME VARCHAR2 (45 BYTE PK_TEAMID (TEAMID) ? PROJ CLIENT VARCHAR2 (35 BYTE) PK_PROJECTID (PROJECTID) PK-PROJECTID (PROJECTID) UNIQ-TEAM(TEAM-NAME) FKPROJID (PROJECTID PK-TEAMID (TEAMID) UNIQ-TEAM(TEAM-NAME) ? ? ?
Explanation / Answer
If you need any help, please give me comment...
SELECT STDID, CONCAT(STDFNAME, ' ', STDLNAME) AS FULLNAME, STDMAJOR
FROM PEEREVALSSTUDENTS
WHERE STDID IN (
SELECT EVALUATEEID
FROM PEEREVALEVALUATIONS E, PEEREVALSSTUDENTS S
WHERE E.EVALUATORID = S.STDID AND S.STDFNAME = 'Andrew' AND S.STDLNAME = 'Agassi'
)
ORDER BY STDID;
