PRATICAL NUMBER-7
➤ Perform Different types of joins on any two tables.
Types of joins-
A. Equi Join
B. Theta Join or Non-Equi Join
C. Self Join
D. Natural Join
E. Inner join
F. Outer Join
1. Left Outer Join
2. Right Outer Join
3. Full Outer Join
G. Cross Join
We can perform these joins by given below steps-
1. Create Student and Course table to perform join operations by given below commands-
SQL>CREATE TABLE Student
(
Enroll_No int,
Student_Name varchar(20),
Course_ID int
);
Table created.
SQL>CREATE TABLE Course
(
Course_ID int,
Course_Name varchar(20),
Course_Duration varchar(20)
);
Table created.
2. Insert records in above table by given below commands-
SQL> INSERT INTO Student VALUES(101, 'Shreya', 102);
1 row created.
SQL> INSERT INTO Student VALUES(102, 'Tanu', 103);
1 row created.
SQL> INSERT INTO Student VALUES(103, 'Roshni', 100);
1 row created.
SQL> INSERT INTO Student VALUES(104, 'Manisha', 101);
1 row created.
SQL> INSERT INTO Course VALUES(101, 'BCA', 'Three Years');
1 row created.
SQL> INSERT INTO Course VALUES(103, 'BEd', 'Two Years');
1 row created.
SQL> INSERT INTO Course VALUES(108, 'BALLB', 'Five Years');
1 row created.
3. Now check the record by below select Command-
SQL>SELECT * FROM Student;
Enroll_No | Student_Name | Course_ID |
101 | Shreya | 102 |
102 | Tanu | 103 |
103 | Roshni | 100 |
104 | Manisha | 101 |
SQL> SELECT * FROM Course;
Course_ID | Course_Name | Course_Duration |
101 | BCA | Three Years |
103 | BEd | Two Years |
108 | BALLB | Five Years |
4. Now we can start to perform the join operations by below commands-
A. Equi Join-It is used to join two tables by equal to operator(=).
SQL> SELECT Enroll_No, Student_Name, Course_Name, Course_Duration FROM Student S, Course C WHERE S.Course_ID = C.Course_ID;
Enroll_No | Student_Name | Course_Name | Course_Duration |
102 | Tanu | BEd | Two Years |
104 | Manisha | BCA | Three Years |
B. Non-Equi Join-It is used to join two tables by Relational Operator except equal to operator.
SQL> SELECT Enroll_No, Student_Name, Course_Name, Course_Duration FROM Student S, Course C WHERE S.Course_ID > C.Course_ID;
Enroll_No | Student_Name | Course_Name | Course_Duration |
101 | Shreya | BCA | Two Years |
102 | Tanu | BCA | Two Years |
C. Self Join- It is used to join table itself by its aliases.
SQL> SELECT S1. Enroll_No, S2. Course_ID, S1.Student_Name FROM Student S1, Student S2 WHERE S1.Enroll_No=S2.Course_ID;
Enroll_No | Course_ID | Student_Name |
102 | 102 | Tanu |
103 | 103 | Roshni |
101 | 101 | Shreya |
D. Natural Join- It is used to join tables by common Attribute.
SQL> SELECT * FROM Student NATURAL JOIN Course;
Course_ID | Enroll_No | Student_Name | Course_Name | Course_Duration |
103 | 102 | Tanu | BEd | Two Years |
101 | 104 | Manisha | BCA | Three Years |
E. Inner Join- It is used to join tables by common values of conditional result.
SQL> SELECT * FROM Student INNER JOIN Course ON Student.Course_ID=Course.Course_ID;
Enroll_No | Student_Name | Course_ID | Course_ID | Course_Name | Course_Duration |
102 | Tanu | 103 | 103 | BEd | Two Years |
104 | Manisha | 101 | 101 | BCA | Three Years |
F. Outer Join- It is used to join tables by common Attribute as well as table remaining attributes based on different outer joins. Let us Discuss all three Outer Joins by an example of each.
1. LEFT OUTER JOIN- It is used to fetch the common result of both tables as well as complete record of Left Table and null values from Right table.
SQL> SELECT * FROM Student LEFT OUTER JOIN Course ON Student.Course_ID = Course.Course_ID;
Enroll_No | Student_Name | Course_ID | Course_ID | Course_Name | Course_Duration |
104 | Manisha | 101 | 101 | BCA | Three Years |
102 | Tanu | 103 | 103 | BEd | Two Years |
103 | Roshni | 100 | |||
101 | Shreya | 102 |
2. RIGHT OUTER JOIN- It is used to fetch the common result of both tables as well as complete record of Right Table and null values from left table.
SQL> SELECT * FROM Student RIGHT OUTER JOIN Course ON Student.Course_ID = Course.Course_ID;
Enroll_No | Student_Name | Course_ID | Course_ID | Course_Name | Course_Duration |
102 | Tanu | 103 | 103 | BEd | Two Years |
104 | Manisha | 101 | 101 | BCA | Three Years |
108 | BALLB | Five Years |
3. FULL OUTER JOIN- It is used to fetch the common result of both tables as well as complete record of Left Table and right table with null values from both tables.
SQL> SELECT * FROM Student FULL OUTER JOIN Course ON Student.Course_ID = Course.Course_ID;
Enroll_No | Student_Name | Course_ID | Course_ID | Course_Name | Course_Duration |
104 | Manisha | 101 | 101 | BCA | Three Years |
102 | Tanu | 103 | 103 | BEd | Two Years |
103 | Roshni | 100 | |||
101 | Shreya | 102 | |||
108 | BALLB | Five Years |
G. Cross Join- It is used to join tables by mapping of each row of first table to each row of second table.
SQL> SELECT * FROM Student CROSS JOIN Course;
Enroll_No | Student_Name | Course_ID | Course_ID | Course_Name | Course_Duration |
101 | Shreya | 102 | 101 | BCA | Three Years |
102 | Tanu | 103 | 101 | BCA | Three Years |
103 | Roshni | 100 | 101 | BCA | Three Years |
104 | Manisha | 101 | 101 | BCA | Three Years |
101 | Shreya | 102 | 103 | BEd | Two Years |
102 | Tanu | 103 | 103 | BEd | Two Years |
103 | Roshni | 100 | 103 | BEd | Two Years |
104 | Manisha | 101 | 103 | BEd | Two Years |
101 | Shreya | 102 | 108 | BALLB | Five Years |
102 | Tanu | 103 | 108 | BALLB | Five Years |
103 | Roshni | 100 | 108 | BALLB | Five Years |
104 | Manisha | 101 | 108 | BALLB | Five Years |
OUTPUT WINDOWS
TABLE CREATION FOR JOIN OPERATIONS Equi, Non-Equi, Self & Natural Join Operations Inner Join, Outer Join (Left, Right & Full Outer Join) Operations
Cross Join Operation |
No comments:
Post a Comment