Schema Department {dept _id, dept_name, budget) Course {course-id, title, credit

Question

Schema Department {dept _id, dept_name, budget) Course {course-id, title, credits, dept_id, prerequisite Faculty {faculty_id, name, room_id, dept_id, salary) Student {student id, last_name , first_name, street, city, state, zip, birth_date, major_id, phone, student_type) Term (term id, term desc , start_date , end_date) Section {section id , course_id , section_number , term id , faculty_id , day , max_count , start time, end_time, room _id) Registration {student_id, section_id, midterm_grade, final_grade) Major fmajor_id , major _desc) Location {room id, building, room_no, capacity, room_type , room_description)

Explanation / Answer

If you have any doubts, please give me comment...

-- 1)

SELECT course_id, section_id, term_id

FROM Section S, Term T

WHERE S.term_id = T.term_id AND T.term_desc = 'Winter 2017';

--2)

UPDATE COURSE SET title='Introduction to Relational Databases' WHERE course_id='CIS253';

--3)

SELECT S.section_id, course_id, COUNT(*) AS no_of_enrollments

FROM Section S, Registration R

WHERE S.section_id = R.section_id AND S.section_id=1103 AND S.course_id='MA150';

--4)

SELECT course_id, section_id, CONCAT(last_name, ', ', firstname)

FROM Section S, Registration R, Student ST, Faculty F

WHERE S.section_id = R.section_id AND R.student_id = ST.student_id AND S.faculty_id = F.faculty_id AND F.name='Sen';

--5)

SELECT dept_name, COUNT(*) AS no_of_instructors

FROM Department D, Faculty F

WHERE D.dept_id = F.dept_id

GROUP BY dept_name;

-- 6)

SELECT faculty_id, name, dept_id

FROM Department D, Faculty F

WHERE D.dept_id = F.dept_id AND D.dept_id ='4' or D.dept_id='5';

--7)

SELECT course_id, section_id, name

FROM Section S, Facutly F, Term T

WHERE S.term_id = T.term_id AND T.faculty_id = F.faculty_id AND T.term_desc='Winter 2017';

--8)

SELECT last_name, first_name

FROM Student ST, Registration R, Section S, Term T

WHERE ST.student_id = R.student_id AND R.section_id=S.section_id AND S.term_id = T.term_id AND final_grade = 'A' AND T.term_desc='winter 2017';

-- 9)

SELECT COUNT(*) AS TOTAL_ENROLLED

FROM Section S, Registration R

WHERE S.section_id = R.section_id AND C.course_id ='CIS253';

--10)

SELECT title, capacity, COUNT(*) AS no_of_sections

FROM Course C, Section S, Location L

WHERE C.course_id = S.course_id AND S.room_id = L.room_id

GROUP BY C.course_id, S.section_id

HAVING COUNT(*)>1;

-- 11)

CREATE VIEW Accounting_dept AS

SELECT *

FROM Course C, Section S, Department D, Term T

WHERE C.course_id = S.course_id AND C.dept_id = D.dept_id AND S.term_id = T.term_id AND D.dept_name = 'Accounting' AND T.term_des = 'Winter 2017';

--12)

SELECT course_id, section_id, COUNT(*) AS no_of_enrollments

FROM Section S, Registrations R

WHERE S.section_id = R.section_id

GROUP BY course_id, section_id

HAVING COUNT(*)>2;

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

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