SQL part 3 examples

How many class offerings were offered in FALL 2012?

SELECT COUNT(OfferNo) FROM Offering WHERE OffTerm = 'FALL' AND OffYear = 2012

Get the student numbers of students who have taken FIN courses.

SELECT DISTINCT StdNo from Enrollment, Offering WHERE Enrollment.OfferNo = Offering.OfferNo AND Offering.CourseNo LIKE 'FIN%';

Get the course numbers of courses taken by IS or ACCT freshman ('FR') students.

SELECT DISTINCT o.CourseNo FROM Enrollment as e, Offering as o, Student as s WHERE e.OfferNo = o.OfferNo AND e.StdNo = s.StdNo AND StdMajor IN ('IS', 'ACCT') AND StdClass = 'FR'

Get the number of offerings offered for each academic year/term.

SELECT OffTerm, OffYear, COUNT(OfferNo) as NoOffering FROM Offering GROUP BY OffTerm, OffYear

Get the highest StdGPA among junior ('JR') and senior ('SR') students by majors. Only list the majors with average StdGPA greater than or equal to 3.0.

SELECT StdMajor, MAX(StdGPA) as MaxGPA FROM Student WHERE StdClass IN ('JR', 'SR') GROUP BY StdMajor HAVING AVG(StdGPA) > 3.0

Get the student numbers and names of non-ACCT major students (i.e., students who do not major in ACCT) whose GPA is greater than 3.7.

SELECT StdNo, StdFirstName, StdLastName, StdGPA from Student WHERE StdMajor <> 'Acct' AND StdGpa > 3.700

Get the offering number, the course number, and the course description of SUMMER 2013 offerings without an assigned instructor.

SELECT o.OfferNo, c.CourseNo, c.CrsDesc FROM Offering as o, Course as c WHERE o.CourseNo = c.CourseNo AND o.FacNo IS NULL AND o.OffTerm = 'SUMMER' AND o.OffYear = 2013

Get the student numbers, names, and classes of students who took Prof. Nicki Macon's offering in WINTER 2013.

SELECT s.StdNo, StdFirstName, StdLastName, StdClass FROM Student as s, Offering as o, Faculty as f, Enrollment as e WHERE s.StdNo = e.StdNo AND o.OfferNo = e.OfferNo AND f.FacNo = o.FacNo AND FacFirstName = 'Nicki' AND FacLastName = 'Macon' AND OffTerm = 'WINTER' AND OffYear = '2013'

Get the student numbers, names, majors, and GPAs of students who took course 'FIN450' in 2013.

SELECT s.StdNo, StdFirstName, StdLastName, StdGPA FROM Student as s INNER JOIN Enrollment as e ON s.StdNo = e.StdNo INNER JOIN Offering as o ON o.OfferNo = e.OfferNo WHERE o.CourseNo = 'FIN450' AND o.OffYear = 2013;

List the course number, offering number, number of students, and average grade of students enrolled in SPRING 2013 course offerings in which more than 5 students are enrolled. Sort the result by course number in the ascending order and next offering number in the descending order

Select CourseNo, o.OfferNo, COUNT(e.StdNo), AVG(e.EnrGrade) FROM Enrollment as e, Offering as o WHERE e.OfferNo = o.OfferNo AND o.OffTerm = 'SPRING' AND o.OffYear='2013' GROUP BY CourseNo, o.OfferNo HAVING COUNT(StdNo) > 5 ORDER BY CourseNo ASC, e.OfferNo DESC

CREATE TABLE IF NOT EXISTS `Faculty

create faculty table

on DELETE NULL

delete 1 make other null

ON DELETE CASCADE

delete from 1 table deletes all info from other tables

INSERT INTO `Course` (`CourseNo`, `CrsDesc`, `CrsUnits`) VALUES

insert information into table course with criterias such as course No course description and course units

FacLastName` varchar(30) NOT NULL

word with up to 30 character and empty spaces are used