Sql Database Language

Only available on StudyMode
  • Download(s) : 51
  • Published : February 7, 2013
Open Document
Text Preview
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. OfferNo
AND CourseNo LIKE *IS&*

6.Get the course numbers of courses taken by FIN or IS senior (‘SR’) students.

SELECT DISTINCT CourseNo
FROM Enrollment, Offering, Student
WHERE Enrollment. OfferNo =
Offering. OfferNo
AND Enrollment. StdNo = Student, StdNo
AND (StdMajor = *FIN* OR StuMajor = *IS*)
AND StdClass = *SR*

7.How many class offerings have not been assigned to any faculty member?

SELECT COUNT(*) AS NoOfOffs
FROM Offering
WHERE FacNo IS NULL

8.Get the highest GPA of IS students by class. Only list the classes with average GPA greater than 3.05.

SELECT StdClass, MAX(StdGPA) AS MaxGPA
From Student
WHERE StdMajor = *IS*
GROUP BY StdClass
HAVING AVE(StdGPA) > 3.05

9.Get the number of offerings and unique courses by offering year and term. (Note: This query may require the use of COUNT(DISTINCT...), which does not work in MS Access. Even if you use MS Access, write a standard SQL script for this question.)

SELECT OffYear, OffTerm, COUNT(*) AS NumOfferings,
COUNT(DISTINCT CourseNo) AS NumCourses
FROM Offering
GROUP BY OffYear, OffTerms
10.List the course number, offering number, and average grade of students enrolled in SPRING 2010 IS course offerings in which more than 6 student is enrolled. Sort the result by course number in ascending order and next average grade in descending order.

SELECT CourseNo, Enrollment. OfferNo,
AVG(EnrGrade) AS AvgGrade
FROM Enrollment, Offering
WHERE Enrollment.OfferNo =
Offering.OfferNo
AND OffTerm = *SPRING*
AND OffYear = 2008
AND CourseNo LIKE *IS%*
GROUP BY CourseNo, Enrollment.OfferNo
HAVING COUNT(*) > 6
ORDER BY CourseNo, AVG(EnrGrade) DESC

11.List the names of faculty members and the course numbers for which the faculty member teaches the same course as his or her supervisor in 2010.

SELECT FacFirstName, FacLastName,
01, CourseNo
FROM...
tracking img