dbms lab 6

/B MAHIDHAR REDDY/
/CS20B1091/

create database dbmslab6;
use lab6;
create table IIITDM(Faculty_name varchar(25) , Student_Id varchar(10) , Building_name varchar(10) );
insert into IIITDM values('mahi','CS1001','Ashoka');
insert into IIITDM values('rushik','CS1002','Ashwatha');
insert into IIITDM values('geethan','CS1003','jasmine');
insert into IIITDM values('ramesh','CS1004','Banyan');
insert into IIITDM values('puru','CS1005','Lotus');
select * from IIITDM;
create table Faculty(Faculty_id varchar(10),Faculty_name varchar(20), Department varchar(25), Subject varchar(25),primary key(Faculty_id));
insert into faculty values('CSF01','mahi','CSE','DSA');
insert into faculty values('CSF02','rushik','CSE','DAA');
insert into faculty values('CSF03','geethan','CSE','TOC');
insert into faculty values('CSF04','ramesh','CSE','DSD');
insert into faculty values('CSF05','puru','CSE','OOPs');
select * from faculty;
create table Student(Student_id varchar(10),Student_name varchar(25),Department varchar(25), Course varchar(25), Building_name varchar(10),primary key(Student_id));
insert into Student values('CS1001','Tushar','CSE','OOPs','srinivas');
insert into Student values('CS1002','Sam','CSE','DSA','vyomesh');
insert into Student values('CS1003','Simran','CSE','DSD','siddu');
insert into Student values('CS1004','Rohit','CSE','TOC','vardhan');
insert into Student values('CS1005','Jaspreet','CSE','DSA','emarald');
select * from Student;
create table Building(Building_name varchar(10),Room_no int , Floor_no int );
insert into Building values('srinivas','30','2');
insert into Building values('vyomesh','56','4');
insert into Building values('siddu','140','13');
insert into Building values('vardhan','4','1');
insert into Building values('emarald','42','3');
select * from Building;
create table Course(Department varchar(25),Subject varchar(25),Course_id varchar(25));
insert into Course values('CSE','OOPs','CSB01');
insert into Course values('CSE','DAA','CSF03');
insert into Course values('CSE','TOC','CSB02');
insert into Course values('CSE','DSD','CSF01');
insert into Course values('CSE','DSA','CSF02');
select * from Course;

select student.* from student inner join course on course.subject = student.course inner join faculty on faculty.subject=course.subject inner join building on building.building_name=student.building_name where building.room_no=30;

select Course.* , student.student_name from course inner join student on student.course = course.subject inner join building on building.building_name = student.building_name where building.room_no =140;

select faculty.*,student.student_name from faculty inner join student on not student.course=faculty.subject ;

select course.*,student.student_name ,faculty.faculty_name from course inner join faculty on faculty.subject=course.subject inner join student on student.course = course.subject inner join building on building.building_name=student.building_name where building.floor_no=3;