The relational database schemas are as follows: department(dept-name, building, budget) instructor(ID, name, dept-name, salary) student(ID, name, dept-name, tot-cred) advisor(s-ID, i-ID) course(course-id, title, dept-name, credits) teaches(ID, course-id, sec-id, semester, year) takes(ID, course-id, sec-id, semester, year, grade) classroom(building, room-number, capacity) time-slot(time-slot-id, day, start-time, end-time) section(course-id, sec-id, semester, year, building, room-number, time-slot-id) prereq(course-id, prereq-id) ________________________________________________________________ Laboratory Work No. 3 Querying – I Using the relational database given in the previous lab work, write the following queries in SQL: 1) List all departments in the CL building. 2) Find the course title of COMP124. 3) Find the prerequisite code(s), if there exists, of COMP217. 4) Which course titles offered in the Fall 2020. 5) List all students (id and names) whose advisor is Dr Cem Kalyoncu. 6) List all courses (codes and titles) in which COMP124 is a prerequisite. 7) List all students (id and names) in Software Engineering whose advisor is Dr Soydan Redif. 8) List all courses (codes) and the corresponding instructors (names) that gave their lectures at CL building in Spring 2020. You should also present the output of each query in the lab report.
The relational
department(dept-name, building, budget)
instructor(ID, name, dept-name, salary)
student(ID, name, dept-name, tot-cred)
advisor(s-ID, i-ID)
course(course-id, title, dept-name, credits)
teaches(ID, course-id, sec-id, semester, year)
takes(ID, course-id, sec-id, semester, year, grade)
classroom(building, room-number, capacity)
time-slot(time-slot-id, day, start-time, end-time)
section(course-id, sec-id, semester, year, building, room-number, time-slot-id)
prereq(course-id, prereq-id)
________________________________________________________________
Laboratory Work No. 3
Querying – I
Using the relational database given in the previous lab work, write the following queries in SQL:
1) List all departments in the CL building.
2) Find the course title of COMP124.
3) Find the prerequisite code(s), if there exists, of COMP217.
4) Which course titles offered in the Fall 2020.
5) List all students (id and names) whose advisor is Dr Cem Kalyoncu.
6) List all courses (codes and titles) in which COMP124 is a prerequisite.
7) List all students (id and names) in Software Engineering whose advisor is Dr Soydan Redif.
8) List all courses (codes) and the corresponding instructors (names) that gave their lectures at CL building in Spring 2020.
You should also present the output of each query in the lab report.
Step by step
Solved in 4 steps
