Let us consider the following relational database. Professors(id, fName, IName, ssn, email) Students (id,...

 

Transcribed Image Text:

Let us consider the following relational database. ■ Professors(id, fName, IName, ssn, email) ■ Students (id, Name, IName, dob, gpa, email) ■ Teaches Courses (number, sectionNo, semester, creditHrs, title, profID) ■CompLanguages(studentID, complanguage) ■ Enrolls(studentID, number, sectionNo. semester, courseGrade) ■ UseGrading Schema(number, sectionNo, semester, cutoff, letterGrade) ■ HasGradingCategoriess (category, weight, mumber, sectionNo, semester) Grading Activites (category, title, maxPoints) Submit(studentID, category, title, point) The primary key for each relation is underlined. The foreign keys for this database are as follows: the column profID of relation Teaches Courses that references table Professors. ■ the column studentID of relation CompLanguages that references table Students, ■ the column studentID of relation Enrolls that references table Students, the columns number, section No, semester of relation Enrolls that references table Teaches Courses, ■the columns number, sectionNo, semester of relation Use Grading Schema that references table TeachesCourses, ■the columns number, sectionNo, semester of relation HasGrading Categoriess that references table Teaches Courses, ■the column category of relation Grading Activites that references table HasGrading Categoriess. ■the column studentID of relation Submits that references table Students, and ■the columns category and tile of relation Submits that references table Grading Activites Task: Write exactly one SQL statement for the following question. Also, the query answers must not contain duplicates, but you should use the SQL keyword distinct only when necessary. Use ecLab.sql to test your code, and its output should be like the one listed here. Otherwise, your SQL code has a logic error(s). Q: Retrieve the course titles offered in Spring 2020, along with their professor's ID. Hints: It can be solved by using one input table. /*************Output*************** Course Title | Professor ID | | Biology I 50783 | Biology II 50783 Chemistry I 50123 Chemistry II 50123 Database I 87654 Database I Lab | 87654 | English I 09785 English II 09785 Calculus I 72121 Calculus I 71376 Calculus II 71376 | Calculus II 72121 12 rows in set (0.002 sec) Let us consider the following relational database. ■ Professors(id, fName, IName, ssn, email) ■ Students (id, Name, IName, dob, gpa, email) ■ Teaches Courses (number, sectionNo, semester, creditHrs, title, profID) ■CompLanguages(studentID, complanguage) ■ Enrolls(studentID, number, sectionNo. semester, courseGrade) ■ UseGrading Schema(number, sectionNo, semester, cutoff, letterGrade) ■ HasGradingCategoriess (category, weight, mumber, sectionNo, semester) Grading Activites (category, title, maxPoints) Submit(studentID, category, title, point) The primary key for each relation is underlined. The foreign keys for this database are as follows: the column profID of relation Teaches Courses that references table Professors. ■ the column studentID of relation CompLanguages that references table Students, ■ the column studentID of relation Enrolls that references table Students, the columns number, section No, semester of relation Enrolls that references table Teaches Courses, ■the columns number, sectionNo, semester of relation Use Grading Schema that references table TeachesCourses, ■the columns number, sectionNo, semester of relation HasGrading Categoriess that references table Teaches Courses, ■the column category of relation Grading Activites that references table HasGrading Categoriess. ■the column studentID of relation Submits that references table Students, and ■the columns category and tile of relation Submits that references table Grading Activites Task: Write exactly one SQL statement for the following question. Also, the query answers must not contain duplicates, but you should use the SQL keyword distinct only when necessary. Use ecLab.sql to test your code, and its output should be like the one listed here. Otherwise, your SQL code has a logic error(s). Q: Retrieve the course titles offered in Spring 2020, along with their professor's ID. Hints: It can be solved by using one input table. /*************Output*************** Course Title | Professor ID | | Biology I 50783 | Biology II 50783 Chemistry I 50123 Chemistry II 50123 Database I 87654 Database I Lab | 87654 | English I 09785 English II 09785 Calculus I 72121 Calculus I 71376 Calculus II 71376 | Calculus II 72121 12 rows in set (0.002 sec)