Scribd
Upload
26 views4 pages

Mini Project 6

The document contains the results of several SQL queries on university data. The first query returns the distinct first and last names of students enrolled in offers 1234 or 5555. The second query returns the average grade for courses with the prefix "IS" in 2002 that had more than one enrollment. The third and fourth queries return the number of offerings by faculty rank and department in 2003 that had two or more offerings. The final query returns the average grade by offer number and major for grades above 3.2.

Uploaded by

anukruthi Pulimi02
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
26 views4 pages

Mini Project 6

The document contains the results of several SQL queries on university data. The first query returns the distinct first and last names of students enrolled in offers 1234 or 5555. The second query returns the average grade for courses with the prefix "IS" in 2002 that had more than one enrollment. The third and fourth queries return the number of offerings by faculty rank and department in 2003 that had two or more offerings. The final query returns the average grade by offer number and major for grades above 3.2.

Uploaded by

anukruthi Pulimi02
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 4

Execute:

> SELECT DISTINCT StdFirstName, StdLastName


FROM University.student
JOIN University.enrollment ON University.student.StdSSN = University.enrollment.StdSSN
WHERE University.enrollment.OfferNo = 1234 OR University.enrollment.OfferNo = 5555

+ ----------------- + ---------------- +
| StdFirstName | StdLastName |
+ ----------------- + ---------------- +
| HOMER | WELLS |
| BOB | NORBERT |
| CANDY | KENDALL |
| WALLY | KENDALL |
| JOE | ESTRADA |
| MARIAH | DODGE |
| TESS | DODGE |
+ ----------------- + ---------------- +
7 rows

Execute:
> SELECT
o.CourseNo,
o.OfferNo,
AVG(e.EnrGrade) AS AvgGrade
FROM
University.offering o
JOIN
University.enrollment e ON o.OfferNo = e.OfferNo
JOIN
University.course c ON o.CourseNo = c.CourseNo
WHERE
o.OffTerm = 'FALL'
AND o.OffYear = 2002
AND c.CourseNo LIKE 'IS%'
AND e.OfferNo=o.OfferNo
GROUP BY
o.CourseNo,
e.OfferNo
HAVING
COUNT(*) > 1
ORDER BY
o.CourseNo ASC,
AvgGrade DESC
+ ------------- + ------------ + ------------- +
| CourseNo | OfferNo | AvgGrade |
+ ------------- + ------------ + ------------- +
| IS320 | 1234 | 3.38333 |
| IS320 | 4321 | 3.30000 |
+ ------------- + ------------ + ------------- +
2 rows

Execute:
> SELECT
f.FacRank,
f.FacDept,
COUNT(*) AS NumOfferings
FROM
University.faculty f
JOIN
University.offering o ON f.FacSSN = o.FacSSN
WHERE
o.OffYear = 2003
GROUP BY
f.FacRank,
f.FacDept
HAVING
COUNT(*) >= 2

+ ------------ + ------------ + ----------------- +


| FacRank | FacDept | NumOfferings |
+ ------------ + ------------ + ----------------- +
| ASST | MS |2 |
| PROF | FIN |2 |
| ASSC | FIN |2 |
| ASSC | MS |2 |
+ ------------ + ------------ + ----------------- +
4 rows

Execute:
> SELECT
f.FacRank,
f.FacDept,
COUNT(*) AS NumOfferings
FROM
University.offering o
JOIN
University.faculty f ON o.FacSSN = f.FacSSN
WHERE
o.OffYear = 2003
GROUP BY
f.FacRank,
f.FacDept
HAVING
COUNT(*) >= 2

+ ------------ + ------------ + ----------------- +


| FacRank | FacDept | NumOfferings |
+ ------------ + ------------ + ----------------- +
| ASST | MS |2 |
| PROF | FIN |2 |
| ASSC | FIN |2 |
| ASSC | MS |2 |
+ ------------ + ------------ + ----------------- +
4 rows

Execute:
> SELECT o.OfferNo, s.StdMajor, AVG(e.EnrGrade) AS AvgGrade
FROM University.offering o
JOIN University.enrollment e ON o.OfferNo = e.OfferNo
JOIN University.student s ON e.StdSSN = s.StdSSN
GROUP BY o.OfferNo, s.StdMajor
HAVING AVG(e.EnrGrade) > 3.2
ORDER BY o.OfferNo ASC

+ ------------ + ------------- + ------------- +


| OfferNo | StdMajor | AvgGrade |
+ ------------ + ------------- + ------------- +
| 1234 | ACCT | 3.45000 |
| 1234 | IS | 3.43333 |
| 4321 | FIN | 3.35000 |
| 4321 | IS | 3.27500 |
| 5678 | FIN | 3.40000 |
| 5679 | ACCT | 3.30000 |
| 5679 | FIN | 3.75000 |
| 6666 | IS | 3.60000 |
| 7777 | IS | 3.50000 |
| 9876 | ACCT | 3.25000 |
| 9876 | FIN | 3.45000 |
| 9876 | IS | 3.26667 |
+ ------------ + ------------- + ------------- +
12 rows

You might also like