BCA RDBMS PRACTICAL NUMBER_7

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_IDCourse_Name Course_Duration
101 BCAThree Years
103 BEdTwo Years
108 BALLBFive 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_NoStudent_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_NoStudent_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_IDEnroll_NoStudent_NameCourse_NameCourse_Duration
103102TanuBEdTwo Years
101104ManishaBCAThree 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_NoStudent_NameCourse_IDCourse_IDCourse_NameCourse_Duration
102Tanu103103BEdTwo Years
104Manisha101101BCAThree 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_NoStudent_NameCourse_IDCourse_IDCourse_NameCourse_Duration
104Manisha101101BCAThree Years
102Tanu103103BEdTwo Years
103Roshni100


101Shreya102



           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_NoStudent_NameCourse_IDCourse_IDCourse_NameCourse_Duration
102Tanu103103BEdTwo Years
104Manisha101101BCAThree Years

108BALLBFive 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_NoStudent_NameCourse_IDCourse_IDCourse_NameCourse_Duration
104Manisha101101BCAThree Years
102Tanu103103BEdTwo Years
103Roshni100


101Shreya102





108BALLBFive 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_NoStudent_NameCourse_IDCourse_IDCourse_NameCourse_Duration
101Shreya102101BCAThree Years
102Tanu103101BCAThree Years
103Roshni100101BCAThree Years
104Manisha101101BCAThree Years
101Shreya102103BEdTwo Years
102Tanu103103BEdTwo Years
103Roshni100103BEdTwo Years
104Manisha101103BEdTwo Years
101Shreya102108BALLBFive Years
102Tanu103108BALLBFive Years
103Roshni100108BALLBFive Years
104Manisha101108BALLBFive Years

 OUTPUT WINDOWS

TABLE CREATION FOR JOIN OPERATIONS
TABLE CREATION FOR JOIN OPERATIONS

Equi, Non-Equi, Self & Natural Join Operations
Equi, Non-Equi, Self & Natural Join Operations



Inner Join, Outer Join (Left, Right & Full Outer Join) Operations
Inner Join, Outer Join (Left, Right & Full Outer Join) Operations

Cross Join Operation
Cross Join Operation

No comments:

Post a Comment