Instance of the College Database Department ACC Accounting Fiterman (212) 220-81
ID: 3732780 • Letter: I
Question
Instance of the College Database Department ACC Accounting Fiterman (212) 220-8190 CIS Computer Information Systems Fiterman (212) 220-8385 Main (212) 220-1335 Main (212)220-8230 MAT Mathematics NUR Nursing -Department names are unique. - Departments have distinct phone numbers. courseNo deptib ACC122 | ACC Acc ACC241 ACC Federal Taxation CIS395 credits capacity Principles 3 32 30 26 26 30 24 18 CIS Database Systems CIS UNDX MAT20 MAT |Statistics MAT315 MAT Algebra NUR411NUR Surgical Nursing Student studentiD firstName lastName dateofBirth GPA deptiD N012 Roxane Campain 12-MAR-1995 roxane@hotmail com 3.51 NUR C033 Noah Kalafatis 23-JUN-1993 nkalafatis@aol.com 2.36 CIS MO91 Malcolm Tromblay 19-SEP-1991 tromblay@cox.net 3.88 MAT A117 Teddy Pedrozo 16-NOV-1998 tpedrozo@gmail.com 3.69 ACC M148 Irina - Students with the same names do not have the same date of birth. A125 Samira Heintzman 08-IAN-1999 heintz@gmalil.com 374 ACC Garda 02-APR-1997 igarcia @juno. com 335 MAT courseNo semester grade CO33 Cisa95 | Fall 2016 CO33 CIS395 | Fall 2017 A125 |ACC241 |Fall 2016 M091 MAT209Fall 2017 C |MAT315 |Spring 201 A117 M148 MAT209 Fall 2017 M091 |MAT315 |Spring 201 A N012 NUR411 Fall 2016 A117 MAT209 |Spring 2017 D M148 MAT315 Fall 2016Explanation / Answer
due to time constraint i am able to answer first 8 parts
4)
select e.studentid, e.courseno, c.title from enrollment e , course c where e.courseno = c.courseno
5)
select c.title, c.capacity, c.deptid, d.name from courses c, department d where c.credits = 4 and c.deptid = d.deptid
6) select count(*) from department
7) select c.courseno, c.capacity from department d where d.name ='CIS' and d.deptid = c.deptid
8) select count(*) from student s, course c, department d , enrollment e where d.deptid = s.deptid and c.deptid = d.deptid and c.courseno='MAT315' and s.studentid = e.studentid and e.semester = 'Sprint 2017'
9) select building, count(*) from department group by building
10) select c.courseno, avg(c.capacity) from course c , department d where d.deptid = c.deptid and c.credits=3 group by d.deptid
11) select c.courseno, sum(c.credits) from course c, student s , enrollment e where e.studentid = s.studentid and c.courseno = e.couseno and s.studentid = 'M148'