MGS 8020 Business Intelligence
Assignment 3

Please use the SQL (in MS Word) to answer the following questions. You may use subquery but all questions need to be completed in ONE SQL. You need to use the following tables (STUDENT and GPA) to answer Q1 – Q4.

[pic]

1. What is the output if you have the following SQL? (10 points) Select * from STUDENT;

Uncondition SQL that lists all the students from the student table with the respective columns. Select * from Student$

2. Please find the average GPA for each student who lives in the following locations: (40 points)
| Atlanta, GA | United States |
| New Orleans, LA |United States |
| Seoul | Korea |

is the CITY in Korea Wasn’t sure which way the question was asking for the average GPA so I did both. Average by the city
SELECT Student$.City, AVG(GPA$.GPA) as 'City GPA'
FROM GPA$ INNER JOIN Student$ ON GPA$.Student_ID = Student$.Student_ID
WHERE Student$.City IN ('Atlanta','New Orleans','Seoul') group by student$.city

Average for the student, who lives in those cities SELECT student$.Student_Name, AVG(GPA$.GPA)
FROM GPA$ INNER JOIN Student$ ON GPA$.Student_ID = Student$.Student_ID WHERE Student$.City IN ('Atlanta','New Orleans','Seoul') group by student$.Student_Name

3. Please find the student (Student ID and Student Name) who has the highest GPA in MGS 8020 (Course Name) in Fall 2006. (25 points) select Student$.Student_ID, Student$.Student_Name from GPA$ INNER JOIN Student$ ON GPA$.Student_ID = Student$.Student_ID where Course_ID ='MGS 8020' and Year = '2006' and Semester = 'Fall' and gpa >= all (select GPA$.GPA from GPA$ where Course_ID = 'MGS 8020') 4. List all the students (Student ID and Student Name) who enrolled in MGS 8020 in Spring 2009 (25 points) select Student$.Student_ID, Student$.Student_Name from GPA$ INNER JOIN Student$ ON GPA$.Student_ID = Student$.Student_ID where Course_ID ='MGS 8020' and Year = '2009' and Semester = 'Spring'

In One SQL Query:

Select * from Student$

SELECT Student$.City, AVG(GPA$.GPA) as 'City GPA'
FROM GPA$ INNER JOIN Student$ ON GPA$.Student_ID = Student$.Student_ID
WHERE Student$.City IN ('Atlanta','New Orleans','Seoul') group by student$.city

SELECT student$.Student_Name, AVG(GPA$.GPA)
FROM GPA$ INNER JOIN Student$ ON GPA$.Student_ID = Student$.Student_ID WHERE Student$.City IN ('Atlanta','New Orleans','Seoul') group by student$.Student_Name

select Student$.Student_ID, Student$.Student_Name from GPA$ INNER JOIN Student$ ON GPA$.Student_ID = Student$.Student_ID where Course_ID ='MGS 8020' and Year = '2006' and Semester = 'Fall' and gpa >= all (select GPA$.GPA from GPA$ where Course_ID = 'MGS 8020')

select Student$.Student_ID, Student$.Student_Name from GPA$ INNER JOIN Student$ ON GPA$.Student_ID = Student$.Student_ID where Course_ID ='MGS 8020' and Year = '2009' and Semester = 'Spring'