Tech Assignment 3

Assignment #3 hints

Q1. The average GPA of all the courses that the student Victoria Hanson has taken from the

Management department. The query should return student name, department, and the average

GPA.

1) Columns in SELECT clause

: Name, Department, GPA (AVG function should be applied)

2) Tables in FROM clause

: Courses_Taken_t, Student_Information_t

3) INNER JOIN between Courses_Taken_t, Student_Information_t with Primary key and Foreign key

4) WHERE clause for Name and Department restriction

5) GROUP BY clause for grouping the columns in SELECT clause except GPA column since AVG() function already applied to GPA column

Q2. The average GPA of all the courses that the student Amy Earhart has taken in her major. The

query should return student name, major, average GPA of all courses that she has taken in her

major.

1) Columns in SELECT clause

: Name, Major, GPA (ROUND() and AVG() functions should be applied)

2) Tables in FROM clause

: Courses_Taken_t, Student_Information_t

3) INNER JOIN between Courses_Taken_t, Student_Information_t with Primary key and Foreign key

4) WHERE clause for Name and Department restriction

5) GROUP BY clause for grouping the columns in SELECT clause except GPA column since ROUND() and AVG() function already applied to GPA column

Q3. Student’s name, minor, Course name, department, percent score, and GPA for the course

where the student Sonia Hatfield got the lowest percent score of all the courses she took in her

minor. If there is a tie, then the query should return the required information for all courses in her

minor which are tied for the lowest percent score.

1) Columns in SELECT clause (use TOP 1 key word in SELECT statement)

: Name, Minor, [Course Name], [Percent Score], GPA

2) Tables in FROM clause

: Courses_t, Courses_Taken_t, Student_Information_t

3) INNER JOIN between Courses_t, Courses_Taken_t with Primary key and Foreign key, INNER JOIN between Courses_Taken_t, Student_Information_t with Primary key and Foreign key

4) WHERE clause for Name and Department restriction

5) ORDER BY statement for sorting by Percent Score

Q4. Student name, Major, Course name, department, GPA, professor name, and professor contact

number of all courses taken in the student’s major where the student Bill Reilly got a 4.0.

1) Columns in SELECT clause (use TOP 1 key word in SELECT statement)

: Student_Information_t.Name, Major, [Course Name], Courses_Taken_t.Department, GPA, Professor_Information_t.Name AS [Professor Name], [Contact Number]

2) Tables in FROM clause

: Courses_t, Courses_Taken_t, Student_Information_t, Professor_Information_t

3) INNER JOIN between Courses_t, Courses_Taken_t with Primary key and Foreign key, INNER JOIN between Courses_Taken_t, Student_Information_t with Primary key and Foreign key, INNER JOIN between Courses_Taken_t, Professor_Information_t with Primary key and Foreign key

4) WHERE clause for Student Name, GPA, and Department restriction

Q5. The department and the “departmental average GPA” for the student John Halber where the

departmental average GPA is the lowest among all the departmental average GPAs. The

departmental average GPA for a given student is defined as the average GPA of all the courses the

student took from that department. The final query should return the student name, department

name and the departmental average GPA for the department where the student John Halber got

the lowest departmental average GPA.

1) Columns in SELECT clause (use TOP 1 key word in SELECT statement)

: Name, Department, GPA (ROUND() and AVG() function for GAP column)

2) Tables in FROM clause

: Courses_Taken_t, Student_Information_t

3) INNER JOIN between Courses_Taken_t, Student_Information_t with Primary key and Foreign key

4) WHERE clause for Student Name

5) GROUP BY clause for columns in SELECT statement except GAP column

6) ORDER BY clause to sort GPA column.