Demonstrate Natural Join and SQL Quries

Demonstrate Natural Join

#Natural Join
- it is used when we need data from multiple tables
- if is similar to cartesian product except following two things
- if removes common attributes automatically
- if performs equality test of common attributes automatically.
- let us consider following tables :
Emp
edit Ename salary Dno
e1 Ram 28000 d1
e2 Sita 36000 d1
e3 Hari 30000 d2

Dept
Dno Dname
d1 IT
d2 Admin

Emp [X] Dept
Eid Ename Salary Dno Dname
e1 Ram 28000 d1 IT
e2 Sita 36000 d1 IT
e3 Hari 30000 d2 Admin

- Natural Join can be used only when
- PK and FK attribute hace same naem and
- No attributes other than PK and FK have same Name.
Questions :
1. Find natural join of teacher and teaches
select * from instructor natural join teaches
2. Find natural join of instructor teaches and course
select * from instructor natural join teaches natural join coruse2
3. frind id,name, and level of students who enrolled in course ICT501
select sid,sname,level from student natural join enroll where cno='ICT501'
4. Find cno and name of courses taught by instructor 102
select cno,cname from coruse2 natural join teaches natural join instructor where Iid='102'
5. find id, name and qualification of instructors who teaches course ict501
select Iid,Iname,qualification from instructor natural join teaches where cno='ict501'

create table teahes1(Iid integer,Cno varchar,foreign key(Iid) references instructor(Iid),foreign key(Cno) references coruse2(Cno))
6. Find id, and name of studens who are enrolled in DBMS
select sid,sname from student natural join enroll natural join coruse2 where cname='ADBMS'
7. find cno and name of courses taught by Ram
select cno,cname from coruse2 natural join teaches natural join instructor where Iname='Ram'
8. find id name and level of students taught by instructor RAM
select sid,sname,level from student natural join enroll natural join teaches natural join instructor where Iname='Ram'   -->
select student.sid,student.sname,level from student,enroll,teaches,instructor where student.sid=enroll.sid and enroll.cno=teaches.cno and teaches.Iid=instructor.Iid and Iname='Ram'