Question

Create tables according to the mapping. Add 2 records to each. Create 5 queries for database of 3 table joins to use most of the tables or group of tables in database. You should not have tables that are of no use.

Student(ssn, name, major)

Class(classID, name, f_ssn)

Faculty(ssn, name, office_num, dept_id)

Department(Dept_id, office_num, f_ssn)

Enroll(s_ssn, classID, grade)

Professor(f_ssn, alma-mater, tenured)

Instructor(f_ssn, term_degree, type)

Lecture(classID, method)

Lab(classID, location)

Person(ssn, dob, gender)

Answer 1

Solution:

Here I am using SQL Editor for creating Tables and queries

Step1:Table Creation and value insertion

Queries:

Create table Person(ssn int primary key,dob datetime,gender char(1));
Insert into Person values(1,'05/20/1990','M')
Insert into Person values(2,'08/20/1994','F')
Insert into Person values(3,'07/20/1990','M')
Insert into Person values(4,'08/28/2004','F')
Insert into Person values(5,'05/20/2005','M')
Insert into Person values(6,'08/25/1994','F')
Insert into Person values(7,'05/20/1993','M')
Insert into Person values(8,'08/20/1992','F')
Insert into Person values(9,'07/20/1991','M')
select * from Person;
Create table Professor(f_ssn int Primary key,alma_mater varchar(50), tenured varchar(15),foreign key(f_ssn) references Person(ssn))
Insert into Professor values(1,'Oxford University','Permanet')
Insert into Professor values(2,'IIT Chennai','Temporay')
Insert into Professor values(3,'IIM Mumbai','Permanent')
Insert into Professor values(7,'IIT Kanpur','Permanet')
Insert into Professor values(8,'Oxford University','Temporay')
Insert into Professor values(9,'IIM Mumbai','Permanent')
select * from Professor;
Create table Instructor(f_ssn int Primary key, term_degree int, type varchar(50),foreign key(f_ssn) references Professor(f_ssn))
Insert into Instructor values(1,4,'Computer Science Engineering')
Insert into Instructor values(2,4,'Electronics Engineering')
Insert into Instructor values(3,2,'Management')
Insert into Instructor values(7,4,'Computer Science Engineering')
Insert into Instructor values(8,4,'Electronics Engineering')
Insert into Instructor values(9,2,'Management')
select * from Instructor;
Create table Department(Dept_id varchar(25) , office_num int ,f_ssn int,Primary key(Dept_id), foreign key(f_ssn) references Professor(f_ssn))
Insert into Department values('Computer Science',105,1)
Insert into Department values('Electronics Engineering',108,2)
Insert into Department values('Management',110,3)
select * from Department;
Create table Faculty(ssn int primary key,name varchar(30), office_num int , dept_id varchar(25),foreign key(dept_id) references Department(dept_id),
foreign key(ssn) references Person(ssn))
Insert into Faculty values(1,'John',105,'Computer Science')
Insert into Faculty values(2,'Tom',108,'Electronics Engineering')
Insert into Faculty values(3,'Anna',110,'Management')
Insert into Faculty values(7,'Syam',106,'Computer Science')
Insert into Faculty values(8,'Neema',109,'Electronics Engineering')
Insert into Faculty values(9,'Sani',1110,'Management')
select * from Faculty;
Create table Student(ssn int primary key,name varchar(30),majour varchar(25),foreign key(ssn) references Person(ssn),foreign key(majour) references Department(dept_id))
Insert into Student values(4,'Mary','Computer Science')
Insert into Student values(5,'Easther','Electronics Engineering')
Insert into Student values(6,'Daniel','Management')
select * from Student;
Create table Class(classID int Primary key, name varchar(25), f_ssn int,foreign key(f_ssn) references Professor(f_ssn),foreign key(name) references Department(dept_id))
Insert into Class values(1,'Computer Science',7)
Insert into Class values(2,'Electronics Engineering',8)
Insert into Class values(3,'Management',9)
select * from Class;
Create table Enroll(s_ssn int primary key , classID int , grade char(2),foreign key(s_ssn) references Person(ssn),foreign key(classID) references Class(classID))
Insert into Enroll values(4,1,'A+')
Insert into Enroll values(5,2,'A')
Insert into Enroll values(6,3,'A')
select * from Enroll;
Create table Lecture(classID int primary key, method varchar(50),foreign key(classID) references Class(classID))
Insert into Lecture values(1,'Personalized Learning ')
Insert into Lecture values(2,'Personalized Learning ')
Insert into Lecture values(3,'Flipped Classrooms ')
select * from Lecture;
Create table Lab(classID int primary key , location varchar(50),foreign key(classID) references Class(classID))
insert into Lab values(1,'Building 1')
insert into Lab values(2,'Building 2')
insert into Lab values(3,'Building 3')
select * from Lab;

QUERIES in SQL EDITOR

RESULTING Tables

5 Queries using atleast 3 joins

1.Select all student's name,Date Of Birth ,Grade and Gender

Query:

select s.name as Student,P.dob,E.grade,P.gender from Student S join Person P on S.ssn=P.ssn join Enroll E on E.s_ssn=P.ssn

Query in Sql Editor:

RESULT

2.Select All Faculty name ,Dob,Gender, Office number , Department,term_degree and Type

Query:

select F.name,P.dob,P.gender,F.office_num,F.dept_id,I.term_degree,I.type from Faculty F join Instructor I on F.ssn=I.f_ssn join Person P on F.ssn=P.ssn

Query in Sql Editor:

RESULT

3.Select All Department, Office Number Of Department,No Of classes

Query:

select d.Dept_id,d.office_num ,Count(d.f_ssn) as NoOfFaculties,count(c.classID) AS NOofclasses from Department d join Faculty F on d.Dept_id=F.Dept_id Join Class C on d.Dept_id=c.name
group by d.Dept_id,d.office_num

Query in Sql Editor:

RESULT:

4.Select all class,Location,method of teaching

Query:

select distinct c.name As Class, L.method as MethodOfTeaching,location as Location from Class c join Lecture L on c.classID=L.classID
join Lab Lb on c.classID=Lb.classID join Faculty F on c.name=F.dept_id join Person P on c.f_ssn=P.ssn

Query in Sql Editor:

RESULT:

5.Select student name, class,Department ,Faculty

Query:

select s.name as Student,E.classID as Class,s.majour as Department , F.name AS ClassFaculty from Student s join Person p on s.ssn=p.ssn
join Enroll E on s.ssn=E.s_ssn join Class c on E.classID=c.classID join Faculty F on c.f_ssn=F.ssn

Query in Sql Editor:

RESULT

If you have any further queries.please comment....