New SQL Question: Consider the following entities.
Student:
studentID, studentName, studentAddress, studentPhone
100 Mark 111 College st 111-222-3333
200 Shelby 222 5th ave 111-222-4444
300 Chuck 123 California 123-234-3456
400 Michelle 333 Iowa St 222-111-3333
Course:
courseID, coursName, creditHour
100 Math 4
110 English 3
120 Physics 3
130 Chemistry 3
Semester:
semesterID, semesterName
1 Spring 1
2 Spring 2
3 Summer 1
4 Summer 2
5 Fall 1
6 Fall 2
Professor:
professorID, professorName, professorAddress, professorPhone
10 Charles 300 main st 999-111-2222
20 George 400 main st 999-111-3333
30 Jane 500 main st 999-111-4444
40 Brenda 600 main st 999-111-5555
Grade:
gradeID, gradeCharacter, gradeNumeric
1 A 4.0
2 A- 3.7
3 B+ 3.4
4 B 3.0
5 B- 2.7
6 C+ 2.4
7 C 2.0
8 C- 1.7
9 D+ 1.4
10 D 1.0
11 D- 0.7
12 F 0.0
CoursesOffered:
courseOfferID, courseID, semesterID, professorID, sectionNumber
1000 100 1 10 1
1001 100 1 30 2
1100 120 1 40 1
1200 130 1 40 1
CourseEnrollment:
courseOfferID, studentID, gradeID
1000 100 3
1000 200 2
1100 100 2
Using ANSI SQL, write select statements for the following questions.
1. List studentName, courseNeme, professorName, semesterName, charaterGrade
2. List studentName, semesterName, number of courses taken by the student
3. List professorName, semesterName, number of courses taught by the professor
4. List studentName, overall GPA for the student
5. semesterName, number of students enrolled in the semester