-- ITEC 3335 Fall 2018 HW #2. -- Q1. Show the first name, last time, minor and number of credits -- of all students majoringin ITEC. select distinct fname, lname, minor, credits from student where major = 'ITEC'; -- Q2. Show the names, school codes and numbers of faculty members -- of all departments. select distinct deptName, schoolCode, numFaculty from department; -- Q3. Show the names, department names and school codes of the faculty members -- working for a department in the school 'CSE'. select distinct f.fname, f.lname, d.deptName, d.schoolCode from faculty f, department d where f.deptCode = d.deptCode and d.schoolCode = 'CSE'; -- Q4. Show the names, ranks and department names of the faculty members -- working for a department in the school 'CSE' or 'HSH' in the following -- manner Note that the result is shown in ascending order of last name -- and then first name. select distinct f.fname as `first name`, f.lname as `last name`, d.deptName as department, f.rank from faculty f, department d where f.deptCode = d.deptCode and (d.schoolCode = 'CSE' or d.schoolCode = 'HSH') order by `last name`,`first name`; -- Q5. Show the ids and names of the students and the semesters -- that they have enrolled in the course with id 2000. select distinct s.stuId, s.fname, s.lname from student s, enroll e, class c where s.stuId = e.stuId and e.classId = c.classId and c.courseId = 2000; -- Q6. Show the ids and names of the students -- who have enrolled in a 'CINF' course. select distinct s.stuId, s.fname, s.lname from student s, enroll e, class c, course co where s.stuId = e.stuId and e.classId = c.classId and c.courseId = co.courseId and co.rubric = 'CINF'; -- Q7. Show the student ids, names, minor department names, and -- faculty advsior names of all students majoring in CSCI. select distinct s.stuId, s.fname, s.lname, d.deptName, f.fname as "adv. fname", f.lname as "adv. lname" from student s, department d, faculty f where s.advisor = f.facId and s.minor = d.deptCode and s.major = 'CSCI';