Instance of the College Database Department ACC Accounting Fiterman (212) 220-81
ID: 3732798 • 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
Answer)
3. List full details of students with GPA greater than 3.5.
select * from Student where GPA > 3.5;
//Simple query to list out all attributes when GPA greater than 3.5
4. List the course number and title of all 4 credit courses offered by the CIS department.
select courseNo, title from Course where deptID='CIS';
// Simple query to list courseNo, title from Course table where deptID is CIS department
5. List the IDs of the students and the grades they obtained in MAT315 course in Spring2017.
select studentID, grade from Enrollment where courseNo='MAT315' and semester='Spring 2017';
// Simple query to list the studentID, grade from Enrollment table where courseNo is MAT315 and semester is the Spring 2017
6. List the titles and credits of the courses offered by departments from the Main building.
select title, credits from Course where deptID in(
select deptID from Department where building='Main'
);
// Sub query to list out all department ids where the building is main building and from that department id to select the title, credits from Course table.