1) Peereval Type I Using a Type I query, show students who have done an evaluati
ID: 3705400 • Letter: 1
Question
1) Peereval
Type I
Using a Type I query, show students who have done an evaluation
(i.e., has been an evaluator).
Show the student's ID and name and the team NAME (not the team ID)
Use a Type I subquery.
Think about which table(s) should be in the subquery to get the
list you need to compare against in the outer query.
2) Peereval
Type I
Using a Type I query, show students who have NOT done an
evaluation (i.e., have not been an evaluator).
Show the student's ID and name and the team NAME (not the team ID).
Use a Type I subquery.
Think about which table(s) should be in the subquery to get the
list you need to compare against in the outer query.
3) Peereval
Type I
a) Using a Type I query, show students who have NOT done an
evaluation (i.e., have not been an evaluator).
Show the student's ID and name and the team NAME (not the team ID).
B) Use a Type I subquery.
Think about which table(s) should be in the subquery to get the
list you need to compare against in the outer query.
4) Peereval
a) Type I
Show students who have attended more than two workshop.
Show the student's ID and full name concatenated with the
column alias "More than 2 workshops"
Sort by student ID.
B) Use a Type I subquery.
First, show a subquery that gives students who have attended
more than two workshops. Use that subquery in the outer query.
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
Hi,
Assuming type 1 is usual join query and type 2 is subquery.
Ans 1- TYPE 1
SELECT STDID,STDFNAME,STDLNAME,TEAM_NAME FROM PEEREVAL_STUDENTS PS
JOIN PEEREVAL_TEAMS PT ON PS.STD_TEAMID=PT.TEAMID
JOIN PEEREVAL_ATTENDANCES PA ON PS.STDID!=PA.ATTND_STDID;
TYPE 2-
SELECT STDID,STDFNAME,STDLNAME,TEAM_NAME FROM PEEREVAL_STUDENTS PS
JOIN PEEREVAL_TEAMS PT ON PS.STD_TEAMID=PT.TEAMID AND PS.STDID NOT IN(SELECT ATTND_STDID FROM PEEREVAL_ATTENDANCES;
Ans 2-
TYPE 1
SELECT STDID,STDFNAME,STDLNAME,TEAM_NAME FROM PEEREVAL_STUDENTS PS
JOIN PEEREVAL_TEAMS PT ON PS.STD_TEAMID=PT.TEAMID
JOIN PEEREVAL_EVALUATION PE ON PE.EVALUATORID;
TYPE 2-
SELECT STDID,STDFNAME,STDLNAME,TEAM_NAME FROM PEEREVAL_STUDENTS PS
JOIN PEEREVAL_TEAMS PT ON PS.STD_TEAMID=PT.TEAMID AND PS.STDID NOT IN(SELECT EVALUATORID FROM PEEREVAL_EVALUATIONS;
Ans 3- same as 2
Ans 4-
SELECT STDID,COUNT(ATTND_WKSP_ID) ,STDLNAME||STDFNAME AS " More than 2 workshops" FROM PEEREVAL_STUDENTS PS JOIN PEEREVAL_ATTENDANCES GROUP BY STDID HAVING COUNT(ATTND_WKSP_ID)> 2;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.