CS 4660 Program Assignment #1 A student-instructor-course database has the follo
ID: 3678022 • Letter: C
Question
CS 4660 Program Assignment #1 A student-instructor-course database has the following schema: S(SNUM,SNAME,MAJOR,CITY) I(INUM,INAME,DEPT,CITY) C(CNUM,CNAME,UNITS) ENROLL(SNUM,CNUM,SECNUM,GRADE) O(INUM,CNUM,SECNUM,DAY,RNUM) For this assignment you are to write and execute SQL commands for the following queries on the Student-Instructor-Course database. 1. Find CNUM of courses taught only by I2, i.e. courses taught by I2 and not by anyone else. 2. Find CNAME of courses offered by every instructor in the CS dept. 3. Find CNUM of courses not taught by instructors in the CS dept. 4. Find SNAME,INAME pairs such that the student is taught by the instructor and they live in the same city. (Student in section taught by the instructor) 5. Find CNAME of courses that are taught by instructors whose classes all meet in the same room. 6. Create a view TOTALUNITS with attributes SNUM,SNAME,TUNITS such that the total number of units taken by the student is given by TUNITS. Then use a select from TOTALUNITS the SNAME of students who have 12 or more units. Note: the units should not be included if the grade is 'W' Email your script file as a .txt file to me. DO NOT send me a MS Word file. Make sure you are using my table names.
Explanation / Answer
1- select ci.cnum from C ci,I ii,O oi where ii.inum = 'I2' and oi.Iinum = ii.inum and oi.cnum = ci.cnum;
2- select ci.cname from C ci,I ii,O oi where ii.dept= 'CS' and oi.Iinum = ii.inum and oi.cnum = ci.cnum;
3- select ci.cnum from C ci,I ii,O oi where ii.dept= 'CS' and oi.Iinum = ii.inum and oi.cnum = ci.cnum;
4- select si.sname,si.city from S si,enroll en,O oi,I ii where oi.cnum = en.cnum and en.snum = si.snum and si.city = i.city;
5- select ci.cname from C ci,I ii,O oi where oi.Iinum = ii.inum and oi.cnum = ci.cnum;