Write an SQL query to join three tables-Oracle PLSQL

-


 The concept of SQL join and parent child relationship are the two methods to join three tables.It is an essential skill for database management and reporting purpose.

SQL JOINS:


We can use the join keyword to join the three tables which is the most efficient and common method. Same logic is applied to join the two tables but it can be extended to more than two tables. We can calculate maximum number of join statements is to join n tables is n-1.

The below table student is created with student_id as primary key and having two columns student_id and student_name.I have shared the code and screenshot for practicing and understanding the concept in very detailed and clear way.

create table student(student_id int NOT NULL,student_name varchar2(20),PRIMARY KEY(student_id));


Student table will display the details of the column name student_id and student_name datatype i.e. number and varchar2 respectively.
desc student;

10 rows are inserted into the table student with the below codes.
INSERT ALL
INTO student(student_id,student_name) VALUES (1,'Rahul')
INTO student(student_id,student_name) VALUES (2,'Rohan')
INTO student(student_id,student_name) VALUES (3,'Shyam')
INTO student(student_id,student_name) VALUES (4,'Mohan')
INTO student(student_id,student_name) VALUES (5,'Ramesh')
INTO student(student_id,student_name) VALUES (6,'Sohan')
INTO student(student_id,student_name) VALUES (7,'Laxman')
INTO student(student_id,student_name) VALUES (8,'Mahesh')
INTO student(student_id,student_name) VALUES (9,'Sunil')
INTO student(student_id,student_name) VALUES (10,'Sunny')
SELECT * FROM dual;


The below table marks is created with school_id as the primary key and student_id as the foreign key.Columns of the table are school_id,student_id,mark_Score and status.

create table marks(school_id int not null,student_id int,mark_score varchar2(5),status varchar2(10),PRIMARY KEY(school_id),
CONSTRAINT FK_marks_student FOREIGN KEY(student_id)
REFERENCES student(student_id));


desc marks;


Marks table will display the school_id,student_id_marks_score,status with their respective datatypes as integer,integer,varchar2 and varchar2.Here,integer is converted into number datatype.



INSERT ALL
INTO marks(school_id,student_id,mark_score,status) VALUES (1000,1,25,'Fail')
INTO marks(school_id,student_id,mark_score,status) VALUES (1004,2,96,'Pass')
INTO marks(school_id,student_id,mark_score,status) VALUES (1008,3,97,'Pass')
INTO marks(school_id,student_id,mark_score,status) VALUES (1012,4,70,'Pass')
INTO marks(school_id,student_id,mark_score,status) VALUES (1016,5,100,'Pass')
INTO marks(school_id,student_id,mark_score,status) VALUES (1020,6,75,'Pass')
INTO marks(school_id,student_id,mark_score,status) VALUES (1024,7,89,'Pass')
INTO marks(school_id,student_id,mark_score,status) VALUES (1028,8,19,'Fail')
INTO marks(school_id,student_id,mark_score,status) VALUES (1032,9,15,'Fail')
INTO marks(school_id,student_id,mark_score,status) VALUES (1036,10,50,'Pass')
SELECT * FROM dual;

10 rows are inserted into the table marks.


Create table details(city varchar2(15),email_id varchar2(50),school_id int,Board varchar2(10),
CONSTRAINT FK_details_marks FOREIGN KEY(school_id) REFERENCES marks(school_id));

Details table is created with columns as city,email_id,school_id as foreign key and board.


INSERT ALL
INTO details(city,email_id,school_id,Board) VALUES ('Mumbai','vigyan@gmail.com',1000,'CBSE')
INTO details(city,email_id,school_id,Board) VALUES ('Pune','gurukul@gmail.com',1004,'CBSE')
INTO details(city,email_id,school_id,Board) VALUES ('Noida','pathsala@gmail.com',1008,'ICSE')
INTO details(city,email_id,school_id,Board) VALUES ('Lucknow','vidhya@gmail.com',1012,'ICSE')
INTO details(city,email_id,school_id,Board) VALUES ('Patna','padhaimandir@gmail.com',1016,'BHB')
INTO details(city,email_id,school_id,Board) VALUES ('Bangalore','Gokul@gmail.com',1020,'KB')
INTO details(city,email_id,school_id,Board) VALUES ('Chennai','tamilschool@gmail.com',1024,'TB')
INTO details(city,email_id,school_id,Board) VALUES ('Hyderabad','telguschool@gmail.com',1028,'TB')
INTO details(city,email_id,school_id,Board) VALUES ('INDORE','gyan@gmail.com',1032,'MPB')
INTO details(city,email_id,school_id,Board) VALUES ('BHOPAL','sarasthimandir@gmail.com',1036,'MPB')
SELECT * FROM dual;

10 rows are inserted into the details table.


desc details;
Here,city,email_id,school_id and board columns are displayed with the data types like varchar2,varchar2,integer and varchar2 respectively.


SELECT s.student_name,m.mark_score,m.status,d.email_id,d.board
from student s
INNER JOIN marks m ON s.student_id=m.student_id
INNER JOIN details d ON m.school_id=d.school_id;


The first inner join will combine the student and marks table on the common column i.e. student_id.The second inner join will combine the result of student and marks table with details tables on the common column name i.e. school_id.Result or output is obtained by using with the help of combining the above mentioned tables i.e. student,marks and details with the matching column id as student_id and school_id.


PARENT CHILD RELATIONSHIP:

In this,each table is connected through the foreign keys where one primary key of the one table with same column attributes and its same data type is referenced as the foreign key in the another table.In the above mentioned tables student,marks and details where the student is the parent table and marks is the child table.Student_id in the student table references the student_id in the marks table.Marks is the parent table and details is the child table,school_id in the marks table references the school_id in the details table.


select student_name,mark_score,status,city,email_id,board
from student s,marks m,details d where s.student_id=m.student_id and m.school_id=d.school_id;


Here,where clause is used to retrieve the matching records across the three tables .Join keyword is not used explicitly for combining the three tables but follows the relational logic.

LEFT JOIN AND FULL OUTER JOIN ARE USED AS ADVANCED TECHNIQUES:

SELECT s.student_name,m.mark_score,m.status,d.email_id,d.board
from student s
LEFT JOIN marks m ON s.student_id=m.student_id
LEFT JOIN details d ON m.school_id=d.school_id;

It will display all the records of the first table and also display the unmatched rows of the second table. If there is not matched,it will display as NULL.

SELECT s.student_name,m.mark_score,m.status,d.email_id,d.board
from student s
FULL OUTER JOIN marks m ON s.student_id=m.student_id
FULL OUTER JOIN details d ON m.school_id=d.school_id;

It will display all the possible records from the three tables whether it is matched or not matched among the three tables.It will mark as NULL for unmatched records.


Comments

Post a Comment

Popular posts from this blog

Difference between union and union all in oracle sql plsql

-
Image
"This is one of the most important questions that interviewer might be asking in an interview.Infyosis,Linked group,Kyndryl,Vodafone Idea, IBM and Mphasis etc." INTRODUCTION: It is said to be set operators which is used to combine, add or concatenate two or more result sets. It is used in the multiple select statements where to combine to produce the final desired results. In other words, multiple select statements of similar column name in same order and having same data type to produce the unified result. It merges the content of the two structurally compatible organized tables into single table.  In MIS datalake,when you are adding the modules of GST reconciliation which is to be used in micros in Excel or VB script, union and union all set operator is preferred for eliminating duplicate records or keeping duplicate records as per business requirement. There are several alternatives like bulk collect,join,set operators like intersect & minus for combining the data from...
Read more

Explain oracle pl sql datatypes interview question and answer for fresher and experienced.

-
Image
 Oracle PLSQL Data Types : PLSQL constant,value,variable,parameter,function which return the value are having the data type. It will determine the storage space information occupied and occurred in the memory or storage format and range constraints for valid values and operations as per specified size limit. It will determine how the data will be going to stored, handled and treated by the oracle engine during the period of processing and storage. It is used in the PLSQL blocks. It allows you to define your sub-types. PLSQL codes are embedded into the java program. Subtypes are used to make the data types compatible with data types used in the PLSQL program while embedding the PLSQL code into the java program. Subtype will be compatible with ANSI/ISO & IBM. It provides the pre-defined data types. Pre-defined data types are categorized into the four types: Composite LOB Reference Scalar Scalar Datatypes: It stores the values without the internal components. It holds the single ...
Read more

Difference between full join and cross join-oracle sql plsql

-
Image
 Difference between full join and cross join in sql:- They behave differently to combine the rows from the two tables. FULL OUTER JOIN: It is also called as the full join .It will return all the rows when there is either match in the left table or right table. If there is no match, it will return NULL for the rows of the columns which is lacking the matching rows. Syntax: select c.*,d.*  from tableC c FULL OUTER JOIN tableD d on c.id=d.id; select e.ename,d.dname from emp e FULL OUTER JOIN dept d ON e.deptno=d.deptno; When you want to retain the unmatched rows i.e operations which is not present in the emp table and having deptno as 40.So,ename is appeared as NULL. CROSS JOIN: It returns the cartesian product of both the tables i.e. Every row of the table A is combined with every row of the table B.ON condition is not used due to not match rows. Syntax: select c.*.d.* from tableC c CROSS JOIN tableD d; select e.ename,d.dname from emp e CROSS JOIN dept d; ENAME ...
Read more