Given the following database schema: Student (snum: integer, sname: char(30), major: char(25), level: char(2), age: integer) Faculty (fid: integer, fname: char(30), deptid: integer) Class (cname: char(40), meets_at: char(20), room: char(10), fid: integer | fid REFS Faculty.fid) Enrolled (snum: integer, cname: char(40) | snum REFS student.snum, cname REFS class.name) Find the names of faculty members and their departments, classes and room number which they teaching. Also include faculty members even they are not teaching. Lab Task Write SQL expressions for each of the following queries using outer join and execute them: 1. Find the names of all juniors (Level = JR) who are either enrolled or not in a class taught by ‘Ivana Teach’. 2. Find the names of faculty members that either teach to class ‘database systems’ or not. 3. Find the name of the courses that is either taught by a faculty member or not. 4. Find the names of classes that is either taught by ‘Richard Jackson’ or not and their times when a class meet there. 5. Retrieve the snum and sname of students who have taken classes either from ‘Ivana Teach’ or ‘Linda Davis’ or both. 6. Find room number where a faculty member either takes a class or not. 7. Find the department IDs where students have been enrolled or not. 8. Find the room number where either Robert Brown teaches or not.
Given the following
Student (snum: integer, sname: char(30), major: char(25), level: char(2), age: integer)
Faculty (fid: integer, fname: char(30), deptid: integer)
Class (cname: char(40), meets_at: char(20), room: char(10), fid: integer | fid REFS Faculty.fid)
Enrolled (snum: integer, cname: char(40) | snum REFS student.snum, cname REFS class.name)
Find the names of faculty members and their departments, classes and room number which they
teaching. Also include faculty members even they are not teaching.
Lab Task
Write SQL expressions for each of the following queries using outer join and execute them:
1. Find the names of all juniors (Level = JR) who are either enrolled or not in a class taught
by ‘Ivana Teach’.
2. Find the names of faculty members that either teach to class ‘database systems’ or not.
3. Find the name of the courses that is either taught by a faculty member or not.
4. Find the names of classes that is either taught by ‘Richard Jackson’ or not and their times
when a class meet there.
5. Retrieve the snum and sname of students who have taken classes either from ‘Ivana
Teach’ or ‘Linda Davis’ or both.
6. Find room number where a faculty member either takes a class or not.
7. Find the department IDs where students have been enrolled or not.
8. Find the room number where either Robert Brown teaches or not.
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
