Find majors that have at least 5 students SELECT major countsid FROM student

Find majors that have at least 5 students select

This preview shows page 26 - 30 out of 30 pages.

Find majors that have at least 5 students: SELECT major, count(sid) FROM student GROUP BY major HAVING count(sid) > 5; Sometime the aggregates are not required to display: SELECT major FROM student GROUP BY major HAVING count(sid) > 5; Two grouping fields: SELECT sex, major, count(sid) FROM student GROUP BY sex, major having count(sid)>1;
Image of page 26
Compute Subtotals from a Join Compute the number of courses taken by each student: SELECT sid, sname, COUNT(cid) FROM student NATURAL JOIN registration GROUP BY sid, sname; Compute the number of students advised by each faculty advisor: SELECT fid, fname, COUNT(sid) FROM faculty NATURAL JOIN student GROUP BY fid, fname;
Image of page 27
With HAVING Find faculty who advises more than one student SELECT fid, fname, COUNT(sid) FROM faculty NATURAL JOIN student GROUP BY fid, fname HAVING count(sid)>1;
Image of page 28
Examples University database: Student: SID, Sname, Sex, Major, GPA, FID Account: SID, Balance Faculty: FID, Fname, Phone Course: CID, Cname, Units, FID Registration: SID, CID
Image of page 29
Questions Q1: Display College of Business students’ ID and name. Q2: Display students’ ID and name who owe university more than $2000. Q3: Display faculty’s name and phone if the student’s GPA is lower than 2.0. Q4: Display faculty’s name and phone if the faculty advises at least 2 student. Q5: Display students’ ID and name who are taking at least 2 course. Q6: Display students’ ID and name who are taking 464 and GPA < 2.0. Q7: Display CID, Cname and the number of students in each course. Q8: Display SID, Sname, TotalUnits
Image of page 30

You've reached the end of your free preview.

Want to read all 30 pages?

  • Spring '14
  • B MAK
  • FID, sid cid

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture