SQL questions

Sample data for each table is given below.

Course

CourseID (PK)	CourseCode	CourseDescription	CreditHours	DepartmentID (FK)
1	MATH125	Statistics	4	10
2	MATH298	Finite Math	3	10
3	GM592	Organizational Behavior	4	12

CourseStudent

StudentID (PK) (FK)	CourseID (PK) (FK)	StudentGrade
100	1	2.5
110	2	3.5
111	3	4.0
112	1	3.5
113	2	3.5
114	3	1.0

Student

StudentID (PK)	LastName	FirstName	NumCredits	ProgramID (FK)
100	Smith	Joe	20	200
110	Ramirez	Jose	25	210
111	Sergen	Cameron	5	230
112	Jones	Sandro	15	200
113	Barrios	Delilah	26	210
114	Menk	Mary	20	200

Program

ProgramID (PK)	Description	Required Credits
200	Health Services	45
210	Finance	42
220	Information Systems	39
230	Project Management	51

Department

DepartmentID (PK)	Description	NumProfessors	NumCourses
10	Math	15	9
11	English	10	25
12	Psychology	9	26

For each of the following questions, show the result set that would be extracted from the database, as well as the SQL that achieves this result.

The problems below are for the first discussion thread in Week 2.

1. Create a SQL query that extracts all departments and the courses that are within that department. Order the data by program in ascending order.

2. Create a query that lists all students’ last names and first names, the description of the program they are pursuing, the number of credits they have, and the total required credits for the program. Order the list by Student Lastname in descending order.

3. Create a list that shows each course code, course description, and studentID, along with the student's mark in that course. You do not have to list the student's name. Order the data by course description in ascending order.

4. Show all course IDs for a student named Jose Ramirez. Include Jose’s last name, first name, and grade in each course.

5. Create a list of students and the programs they are pursuing. List only those students whose number of credits is less than the required credits in their program.

6. Show a list of all courses, their credit hours, and descriptions of their departments.

7. Show a list of all courses, their credit hours, and descriptions of their departments. Include only those courses whose course code starts with an M.

8. Extract the average GPA of all students.

9. Extract the name of each course and the average GPA of all students in that course. Order the data by course in descending order.

10. Extract the name of each program and the average number of credits earned by students in that program.

11. Extract The last name and first name of each student, the course codes(s) associated with that student, and his or her grade.

12. Extract each course code, its description, and its credit hours. Also extract all student IDs associated with that course code and the department to which the course belongs.

13. Extract the name of each program, the description of that program, the last name and first name of all students in the program, and the course IDs of all courses the students have taken.

14. Extract the last name and first name of each student, the course codes the students have taken, and the credit hours of each course they have taken. Include only those students whose last names start with an S.

15. Extract the last name and first name of each student. Include only those students whose GPA is lower than the average GPA of all students.

16. Extract the last name and first name of each student, as well as his or her number of earned credits. Include only those students whose number of credits is less than the average number of credits of all students.

17. Extract the last name and first name of each student, as well as his or her number of credits. Include only those students whose numcredits value is higher than the student with the lowest number of credits.

18. Extract the last name and first name of each student and their number of credits. Include only those students whose numcredits value is lower than the student who has the highest number of credits.

19. Extract the name of each course that has credit hours higher than the course with the minimum number of credit hours of all courses.

20. Extract the name of each course that has credit hours that is lower than the average number of credit hours of all courses.

Course

PKCourseID

CourseCode

CourseDescription

CreditHours

FK1DepartmentId

Department

PKDepartmentId

Description

NumProfessors

NumCourses

Program

PKProgramID

Description

RequiredCredits

Student

PKStudentID

LastName

Firstname

Numcredits

FK1ProgramID

CourseStudent

PK,FK1StudentID

PK,FK2CourseID

StudentGrade

Table