Sql Database Language
MIS 6326 (Database Management) ( AIM 6337 (Data Strategy & Management)
Assignment 2: Chapters 4 & 9
Due date: August 2, 2012
Using the Chapters 4 & 9 University Database create one “SELECT” SQL statement for each of the following question.[1] [Note, the SQL statement may include nested queries.]
Turn in SQL statements only. (You do not have to turn in the results of SQL statements.)
1. Get the faculty numbers and names of MS department professors whose salary is greater than 68000.
SELECT FacFirstName, FacLastName, FacSalary FROM Faculty WHERE FacName = *MS* AND FacSalary > 68000
2. Get the offering, the course number, and the course description of SUMMER 2010 offerings without an assigned instructor.
SELECT OfferNo, Course, CourseNo, CrsDesc FROM Offering , Course WHERE Offering. CourseNo = Course. CourseNo AND FacNo IS NULL AND offTerm = *SUMMER* AND OffYear = 2010
3. Get the student numbers, names, and majors of students who took course ‘IS480’ in SPRING 2010.
SELECT Student, StudNo, StdFirstName, StdLastName, StdMajor FROM Student, Enrollment, Offering WHERE Student. StdNo = Enrollment. StdNo AND Enrollment.OfferNo = Offering. OfferNo AND CourseNo = *IS480* AND OffTerm = *SPRING* AND OffYear = 2008
4. Get the student numbers, names, and majors of students who took Prof. Nicki Macon’s offering in SPRING 2010.
SELECT Student, StdNo, StdFirstName, StdLastName, StdMajor FROM Student, Enrollment, Offering, Faculty WHERE Student, StdNo = Enrollment. StdNo AND Enrllment, OfferNo = Offering. OfferNo AND Offering FacNo = Faculty. FacNo AND FacFirstName = *NICKI* AND FacLastName = *MACON* AND OffTerm = *SPRING* AND OffYear =2008
5. Get the student numbers of students who have taken IS courses.
SELECT DISTINCT StdNo FROM Enrollment, Offering WHERE Enrollment.OfferNo = Offering.
Assignment 2: Chapters 4 & 9
Due date: August 2, 2012
Using the Chapters 4 & 9 University Database create one “SELECT” SQL statement for each of the following question.[1] [Note, the SQL statement may include nested queries.]
Turn in SQL statements only. (You do not have to turn in the results of SQL statements.)
1. Get the faculty numbers and names of MS department professors whose salary is greater than 68000.
SELECT FacFirstName, FacLastName, FacSalary FROM Faculty WHERE FacName = *MS* AND FacSalary > 68000
2. Get the offering, the course number, and the course description of SUMMER 2010 offerings without an assigned instructor.
SELECT OfferNo, Course, CourseNo, CrsDesc FROM Offering , Course WHERE Offering. CourseNo = Course. CourseNo AND FacNo IS NULL AND offTerm = *SUMMER* AND OffYear = 2010
3. Get the student numbers, names, and majors of students who took course ‘IS480’ in SPRING 2010.
SELECT Student, StudNo, StdFirstName, StdLastName, StdMajor FROM Student, Enrollment, Offering WHERE Student. StdNo = Enrollment. StdNo AND Enrollment.OfferNo = Offering. OfferNo AND CourseNo = *IS480* AND OffTerm = *SPRING* AND OffYear = 2008
4. Get the student numbers, names, and majors of students who took Prof. Nicki Macon’s offering in SPRING 2010.
SELECT Student, StdNo, StdFirstName, StdLastName, StdMajor FROM Student, Enrollment, Offering, Faculty WHERE Student, StdNo = Enrollment. StdNo AND Enrllment, OfferNo = Offering. OfferNo AND Offering FacNo = Faculty. FacNo AND FacFirstName = *NICKI* AND FacLastName = *MACON* AND OffTerm = *SPRING* AND OffYear =2008
5. Get the student numbers of students who have taken IS courses.
SELECT DISTINCT StdNo FROM Enrollment, Offering WHERE Enrollment.OfferNo = Offering.