SQL> set linesize 120 SQL> set pagesize 60 SQL> SQL> Select avg(gpa) 2 From students; AVG(GPA) ---------- 3.0635 SQL> SQL> SQL> Select max(gpa) 2 From students; MAX(GPA) ---------- 3.87 SQL> SQL> SQL> Select min(gpa) 2 From students; MIN(GPA) ---------- 1.9 SQL> SQL> SQL> SQL> Select count(*) 2 From students; COUNT(*) ---------- 20 SQL> SQL> SQL> SQL> Select count(distinct major) 2 From students; COUNT(DISTINCTMAJOR) -------------------- 6 SQL> SQL> SQL> SQL> Select count(major) 2 From students; COUNT(MAJOR) ------------ 20 SQL> Select avg(gpa) 2 From students 3 Where major = 'CSC'; AVG(GPA) ---------- 2.9525 SQL> Select last, gpa 2 From students 3 Where major = 'CSC'; LAST GPA --------------- ---------- Webber 3.51 Hill 3.87 Bradley 2.12 Hardaway 2.31 SQL> SQL> SQL> Select major, avg(gpa) 2 From students; Select major, avg(gpa) * ERROR at line 1: ORA-00937: not a single-group group function SQL> Select major, avg(gpa) 2 From students 3 Group by major; MAJO AVG(GPA) ---- ---------- HIS 3.12333333 CHEM 3.38666667 MGMT 2.97666667 BIO 2.706 CSC 2.9525 MKTG 3.735 6 rows selected. SQL> Select major, avg(gpa), min(gpa), max(gpa) 2 From students 3 Group by major; MAJO AVG(GPA) MIN(GPA) MAX(GPA) ---- ---------- ---------- ---------- HIS 3.12333333 2.5 3.87 CHEM 3.38666667 3.1 3.85 MGMT 2.97666667 2.85 3.1 BIO 2.706 1.9 3.1 CSC 2.9525 2.12 3.87 MKTG 3.735 3.65 3.82 6 rows selected. SQL> Select year, major, avg(gpa) 2 From students 3 Group by year, major; YE MAJO AVG(GPA) -- ---- ---------- So CSC 3.51 Fr BIO 1.9 Sr MGMT 2.98 Jr HIS 2.5 Sr MKTG 3.735 So MGMT 2.975 Sr CSC 3.87 Fr CSC 2.215 Fr CHEM 3.85 Fr HIS 3 So CHEM 3.155 So BIO 2.92 Jr BIO 2.87 Sr HIS 3.87 14 rows selected. SQL> Select year, major, avg(gpa) 2 From students 3 Group by year, major 4 Order by major, year; YE MAJO AVG(GPA) -- ---- ---------- Fr BIO 1.9 Jr BIO 2.87 So BIO 2.92 Fr CHEM 3.85 So CHEM 3.155 Fr CSC 2.215 So CSC 3.51 Sr CSC 3.87 Fr HIS 3 Jr HIS 2.5 Sr HIS 3.87 So MGMT 2.975 Sr MGMT 2.98 Sr MKTG 3.735 14 rows selected. SQL> Select year, major, avg(gpa) 2 From students 3 Order by major, year 4 Group by year, major; Group by year, major * ERROR at line 4: ORA-00933: SQL command not properly ended SQL> Select major, avg(gpa) 2 From students 3 Where year = 'Sr' 4 Group by major; MAJO AVG(GPA) ---- ---------- HIS 3.87 MGMT 2.98 MKTG 3.735 CSC 3.87 SQL> Select major, avg(gpa) 2 From students 3 Group by major 4 Where year = 'Sr'; Where year = 'Sr' * ERROR at line 4: ORA-00933: SQL command not properly ended SQL> Select major, count(*) 2 From students 3 Group by major; MAJO COUNT(*) ---- ---------- HIS 3 CHEM 3 MGMT 3 BIO 5 CSC 4 MKTG 2 6 rows selected. SQL> Select major, avg(gpa) 2 From students 3 Group by major 4 Having count(*) > 3; MAJO AVG(GPA) ---- ---------- BIO 2.706 CSC 2.9525 SQL> Select major, avg(gpa) 2 From students 3 where count(*) > 3 4 Group by major; where count(*) > 3 * ERROR at line 3: ORA-00934: group function is not allowed here SQL> Select major, avg(gpa) 2 From students 3 Group by major 4 Having avg(gpa) > 3.0; MAJO AVG(GPA) ---- ---------- HIS 3.12333333 CHEM 3.38666667 MKTG 3.735 SQL> Select major, avg(gpa) 2 From students 3 Group by major 4 Having gpa > 3.0; Having gpa > 3.0 * ERROR at line 4: ORA-00979: not a GROUP BY expression SQL> Select avg(gpa) from students; AVG(GPA) ---------- 3.0635 SQL> Select * from students 2 Where gpa > 3 (Select avg(gpa) from students) 4 Order by gpa desc; STUD FIRST M LAST YE MAJO NUMCREDITSEARNED GPA HOMETOWN BALANCEDUE ---- --------------- - --------------- -- ---- ---------------- ---------- -------------------- ---------- 7070 Tim D Duncan Sr HIS 99 3.87 San Antonio 10 1117 Grant P Hill Sr CSC 107 3.87 Pennsauken 0 3131 Shane D Battier Fr CHEM 15 3.85 Vorhees 0 1115 Shaq O O'Neal Sr MKTG 94 3.82 Vorhees 0 6161 Michael S Jordan Sr MKTG 110 3.65 Wilmington 0 1093 Chris T Webber So CSC 45 3.51 Haddonfield 2500 1119 Marcus S Camby So CHEM 48 3.21 Pennsauken 50 7171 Paul P Pierce So CHEM 35 3.1 Boston 100 1011 Ason J Kidd So MGMT 40 3.1 Oakland 0 2222 Larry R Hughes So BIO 13 3.1 Philadelphia 0 10 rows selected. SQL> Select studentid from students where year = 'Fr'; STUD ---- 1111 1123 1818 3131 5555 SQL> Select distinct sectionid from enrollments 2 Where studentid IN ( 3 Select studentid from students where year = 'Fr' ) 4 Order by sectionid; SECTI ----- 11111 62272 62341 66415 66419 66422 70232 70234 70238 80104 80202 80301 80311 80321 80403 80406 80410 80435 80436 80447 80448 80451 80463 80469 80470 80474 80491 80494 80496 80503 80511 31 rows selected. SQL> spool off