SQL> select avg(gpa), min(gpa), max(gpa), year 2 from students 3 group by year; AVG(GPA) MIN(GPA) MAX(GPA) YE ---------- ---------- ---------- -- 3.5925 2.98 3.87 Sr 3.06625 2.67 3.51 So 2.86333333 1.9 4 Fr 3.06333333 2.5 3.82 Jr SQL> select dept, sum(stoppoint) from sections 2 group by dept; DEPT SUM(STOPPOINT) ---- -------------- HIS 400 BUS 175 CSIT 300 MTH 550 PHL 50 POL 50 ENG 450 CHEM 299 SOC 50 BIO 100 MGMT 375 DEPT SUM(STOPPOINT) ---- -------------- REL 100 CSD 60 CSC 700 MKTG 150 ECN 300 PSY 100 17 rows selected. SQL> select dept, sum(stoppoint) from sections 2 where sum(stoppoint) 3 group by dept; where sum(stoppoint) * ERROR at line 2: ORA-00934: group function is not allowed here SQL> select dept, sum(stoppoint) from sections 2 group by dept 3 having sum(stoppoint); having sum(stoppoint) * ERROR at line 3: ORA-00920: invalid relational operator SQL> select dept, sum(stoppoint) from sections 2 group by dept 3 having sum(stoppoint) > 100; DEPT SUM(STOPPOINT) ---- -------------- HIS 400 BUS 175 CSIT 300 MTH 550 ENG 450 CHEM 299 MGMT 375 CSC 700 MKTG 150 ECN 300 10 rows selected. SQL> describe sections Name Null? Type ----------------------------------------- -------- ---------------------------- SECTIONID NOT NULL CHAR(5) DEPT NOT NULL CHAR(4) COURSE NOT NULL CHAR(3) SECTION NOT NULL CHAR(2) SEM NOT NULL CHAR(2) YEAR NOT NULL NUMBER(38) TIME CHAR(8) ROOM CHAR(8) PROFESSOR CHAR(15) STOPPOINT NUMBER(38) JUNK NUMBER(38) SQL> select dept, sum(stoppoint) from sections 2 where course >= 200 3 group by dept 4 having sum(stoppoint) > 100; DEPT SUM(STOPPOINT) ---- -------------- HIS 250 CSIT 170 MTH 125 MGMT 375 CSC 235 MKTG 150 ECN 150 7 rows selected. SQL> select dept, sum(stoppoint) from sections 2 where course >= 200 3 group by dept 4 having sum(stoppoint) > 100 5 order by sum(stoppoint); DEPT SUM(STOPPOINT) ---- -------------- MTH 125 ECN 150 MKTG 150 CSIT 170 CSC 235 HIS 250 MGMT 375 7 rows selected. SQL> select dept, sum(stoppoint) from sections 2 where course >= 200 3 group by dept 4 having count(*) > 2 5 order by sum(stoppoint); DEPT SUM(STOPPOINT) ---- -------------- CSD 60 BUS 75 ENG 75 CHEM 100 MTH 125 ECN 150 MKTG 150 CSIT 170 CSC 235 HIS 250 MGMT 375 11 rows selected. SQL> select max(gpa) from students; MAX(GPA) ---------- 4 SQL> studentid, last, first, gpa, from students SP2-0734: unknown command beginning "studentid,..." - rest of line ignored. SQL> where gpa = ( SP2-0734: unknown command beginning "where gpa ..." - rest of line ignored. SQL> select max(gpa) from students); select max(gpa) from students) * ERROR at line 1: ORA-00933: SQL command not properly ended SQL> select studentid, last, first, gpa from students 2 where gpa = ( 3 select max(gpa) from students); STUD LAST FIRST GPA ---- --------------- --------------- ---------- 1234 Student Sample 4 SQL> select studentid from students 2 where year = 'Fr'; STUD ---- 1111 1123 1818 3131 5555 1234 6 rows selected. SQL> select distinct (professor, sectionid) 2 from sections, enrollments 3 where sections.studentid = enrollments.studentid 4 and enrollments.studentid IN 5 (select studentid from students 6 where year = 'Fr'); select distinct (professor, sectionid) * ERROR at line 1: ORA-00907: missing right parenthesis SQL> select distinct professor, sectionid 2 from sections, enrollments 3 where sections.studentid = enrollments.studentid 4 and enrollments.studentid IN 5 (select studentid from students 6 where year = 'Fr'); where sections.studentid = enrollments.studentid * ERROR at line 3: ORA-00904: "SECTIONS"."STUDENTID": invalid identifier SQL> select distinct professor, sectionid 2 from sections, enrollments 3 where sections.sectionid = enrollments.sectionid 4 and enrollments.studentid IN 5 (select studentid from students 6 where year = 'Fr'); select distinct professor, sectionid * ERROR at line 1: ORA-00918: column ambiguously defined SQL> select distinct professor, sections.sectionid 2 from sections, enrollments 3 where sections.sectionid = enrollments.sectionid 4 and enrollments.studentid IN 5 (select studentid from students 6 where year = 'Fr'); PROFESSOR SECTI --------------- ----- Grant 62272 Redmond 70238 Elliott 80301 Beatty 80448 Leibiger 80511 Stevens 66415 Kolasa 70234 Young 80104 Mshomba 80436 Butler 80451 Turk 80311 PROFESSOR SECTI --------------- ----- Andrilli 80469 DiDio 80474 Ratkus 80496 Redmond 11111 Ballough 80403 Sude 80463 Beatty 80447 Collins 80491 Andrilli 80470 Burke 62341 Billa 66419 PROFESSOR SECTI --------------- ----- Kolasa 66422 Venkatesan 70232 Blum 80321 Shalhoub 80410 Fair 80503 DiDio 80202 Jones N 80406 Mshomba 80435 Kelly G 80494 31 rows selected. SQL> select distinct professor, sections.sectionid 2 from sections, enrollments 3 where sections.sectionid = enrollments.sectionid 4 and enrollments.studentid IN 5 (select studentid from students 6 where year = 'Fr') 7 order by professor; PROFESSOR SECTI --------------- ----- Andrilli 80469 Andrilli 80470 Ballough 80403 Beatty 80447 Beatty 80448 Billa 66419 Blum 80321 Burke 62341 Butler 80451 Collins 80491 DiDio 80202 PROFESSOR SECTI --------------- ----- DiDio 80474 Elliott 80301 Fair 80503 Grant 62272 Jones N 80406 Kelly G 80494 Kolasa 66422 Kolasa 70234 Leibiger 80511 Mshomba 80435 Mshomba 80436 PROFESSOR SECTI --------------- ----- Ratkus 80496 Redmond 11111 Redmond 70238 Shalhoub 80410 Stevens 66415 Sude 80463 Turk 80311 Venkatesan 70232 Young 80104 31 rows selected. SQL> spool off