For each of the information requests below, write an SQL query that provides the

Question

For each of the information requests below, write an SQL query that provides the requested information.

a. The professor ID, professor name, course ID, and section ID for all course sections taught in the Fall term, sorted by professor name and course ID.

b. The student ID, name, top-ranked area of study, and average grade in the Winter term for all students with an average grade of at least 3.0 in the Winter term.

c. The student ID and name of any student who is not registered in any course section in the Fall term. Use a sub-query in your solution.

studentArea ostudentld rank area depth student studentid stuName birthdate program feesOwed course courseld title credits hours register 00 offering student!d courseld sectid approval grade 00 ?course!d sectid term limit location | y, counsel teach instruction sectid ? type course!d- sectid 0 type description of roomld time profid profName phone officeRoomld deptid room O0 ? roomid- department purpose capacity deptld deptName dcpProfld

Explanation / Answer

a) Select p.profid,p.profname,t.courseid,t.sectid from prof p ,teach t , offering o where p.profid = t.profid and t.sectid = o.sectid and o.term = "Fall" order by p.profname , t.courseid

explanation :- In above query we are selecting profid . profname , courseid ,sectid from prof table and teach table which we are joining with the primary keys werequire the details of only fall term so we add the term codition from the offering table.Finally we use the order by clause to sort the results you can use the desc next to the column mentioned here to sort in descending order.

b) select s.studentid,s.studname,min(a.rank),avg(r.grade) from student s , studentarea a , register r, offering o where s.studentid = a.studentid and s.studentid = r.studentid and r.sectd = o.sectid and o.term = "Winter". and avg(r.grade) > 3.0

explanation:- Here we use the min and avg functions in the mysql to get the least rank (to get top rank) and average score of studentswe perform join between student,studentarea and register using studentid and join between register and offering using the sectid and finally in where clause we mention that the term mustbe in winter and the average score must be greater then 3.0.

c) select s.studentid , s.studname from student s where s.studentid not in (select s.studentid from student s,, register r, offering o where s.studentid = r.studentid .and r.sectid = o.sectid and o.term = "Fall")

explanation :- Here we use the not in to check if the student id present in the result from the subquery and our subquery will give the list of students registeredin the fall term since we have joined the tables student , register and offering and gave term as fall .

Note : if this answer was helpful please give a thumbsup and comment if you have any queries.

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

---

---

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