Answer each request about the university database below with an SQL query. Produ

Question

Answer each request about the university database below with an SQL query.

Produce a list of students and their IDs who enrolled in some section of algebra in 2017.

Produce a list of courses (not sections) offer?ed by the mathematics department in 2017.

Determine the number of courses offered by each department in 2016.

Determine the total enrollment in each section of courses offered in 2017.

Determine the number of sections taught by Professor Einstein in 2016.

Determine the year Professor Brando started teaching.

Determine the total number of sections Professor Turing has taught of the compilers course.

Determine the total number of A’s Amy (Id #2) has earned.

Determine the number of professors who have taught the database systems course.

Determine the number of years Professor Brando has been teaching.

Determine the number of students who graduated in 2017.

Determine the number of students who enrolled in at least one section of a course in 2017.

Some sample data STUDENT DEPT Sid SName GradYear Majorld DId DName 1 Joe 2018 2 amy 2018 max 2017 sue 2016 5 bob 2015 6 kim 2015 2017 pat 2016 9 lee 2017 2018 11 tom 2018 10 20 10 20 30 20 30 20 10 20 10 computer science 20 mathematics 30 drama 7 art 10 ali COURSE SECTION Deptld Sectld Courseld Prof YearOffered 2017 2018 2017 CId Number Title 12 466 databases 10 22 395 32 300 calculus 42 200 algebra 52 350 acting 62 400 elocution 10 20 20 30 30 13 12 23 12 32 43 32 53 62 63 42 turing turing newton einstein brando newton compliers 2017 2017 ENROLL Eld StudentId Sectionld Grade 24 34 13 43 43 53 53 43 63 23 54 74 84 94 null

Explanation / Answer

Produce a list of students and their IDs who enrolled in some section of algebra in 2017.

Select * from STUDENT inner join ENROLL on STUDENT.Sid = ENROLL.StudentID inner join SECTION on ENROLL.SectionID = SECTION.SectID inner join COURSE on SECTION.CourseID = COURSE.CID where Title = 'algebra' and YearOffered = 2017;

Produce a list of courses (not sections) offer?ed by the mathematics department in 2017.

Select * from COURSE inner join DEPT on COURSE.DeptId = DEPT. DId inner join SECTION on SECTION.CourseID = COURSE.CID? where DName = 'mathematics' and YearOffered = 2017;

Determine the number of courses offered by each department in 2016.

Select count(Cid) from COURSE inner join SECTION on SECTION.CourseID = COURSE.CID? ?group by DeptID having YearOffered = 2016;

Determine the total enrollment in each section of courses offered in 2017.

Select count(EId) from ENROLLMENT inner join SECTION on ENROLLEMENT.SectionID = SECTION.SectID group by ENROLLMENT.SectionID having YearOffered = 2017;

Determine the number of sections taught by Professor Einstein in 2016.

Select count(SectID) from SECTION where Prof = 'einstein' and YearOffered = 2016;

Determine the year Professor Brando started teaching.

Select YearOffered from Section where Prof = 'brando';

Determine the total number of sections Professor Turing has taught of the compilers course.

Select count(SectID) from SECTION inner join COURSE on SECTION.CourseID = COURSE.CID where Prof = 'turing' and Title = 'compilers';

Determine the total number of A’s Amy (Id #2) has earned.

Select count(Grade) from ENROLLMENT where StudentId = 2 and Grade LIKE 'A%';

Determine the number of professors who have taught the database systems course.

Select count(prof) from SECTION inner join COURSE on SECTION.CourseID = COURSE.CID? where Title = 'databases';

Determine the number of years Professor Brando has been teaching.

Select count(YearOffered) from SECTION where Prof = 'brando';

Determine the number of students who graduated in 2017.

Select count(SId) from STUDENT where GradYear = 2017;

Determine the number of students who enrolled in at least one section of a course in 2017.

Select count(SId) from STUDENT inner join ENROLL on STUDENT.Sid = ENROLL.StudentID inner join SECTION on ENROLL.SectionID = SECTION.SectID inner join COURSE on SECTION.CourseID = COURSE.CID where yearOffered = 2017;

Do ask if any doubt. Please upvote.

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.