SQL joining through referential integrity

Referential Integrity

A REFERENTIAL INTEGRITY is a database concept that is used to build and maintain logical relationships between tables to avoid logical corruption of data. It is a very useful and important part in RDBMS.

Usually, referential integrity is made up of the combination of a primary key and a foreign key.

The main concept of REFERENTIAL INTEGRITY is that it does not allow to add any record in a table that contains the foreign key unless the reference table containing a corresponding primary key.

If any record in referenced table (i.e. the table who contain primary key) is deleted, all the corresponding records in the referencing table will be deleted for the referential integrity.

Example:

Sample table: agents

Sample table: orders

To get 'agent_code' and 'agent_name' from 'agents' table and 'ord_num' and 'advance_amount' form 'orders' after a joining with following conditions -

1. 'agent_code' is primary key in 'agents' table

2. 'agent_code' is a foreign key in orders table which is referring to the primary key of 'agents' table.

3. 'agent_code' of 'agents' and 'orders' must be same.

the following SQL statement can be used:

SQL Code:

SELECT agents.agent_code,agents.agent_name,
orders.ord_num,orders.advance_amount
FROM agents,orders
WHERE agents.agent_code=orders.agent_code;

Output:

AGENT_CODE AGENT_NAME                                  ORD_NUM ADVANCE_AMOUNT
---------- ---------------------------------------- ---------- --------------
A008       Alford                                       200114           2000
A004       Ivan                                         200122            400
A006       McDen                                        200118            100
A010       Santakumar                                   200119            700
A004       Ivan                                         200121            600
A011       Ravi Kumar                                   200130            400
A005       Anderson                                     200134           1800
A004       Ivan                                         200108            600
A005       Anderson                                     200103            700
A011       Ravi Kumar                                   200105            500
A010       Santakumar                                   200109            800
A008       Alford                                       200101           1000
A008       Alford                                       200111            300
A004       Ivan                                         200104            500
A002       Mukesh                                       200106            700
A005       Anderson                                     200125            600
A001       Subbarao                                     200117            200
A002       Mukesh                                       200123            100
A002       Mukesh                                       200120            100
.................
.................

See our Model Database

Here is a new document which is a collection of questions with short and simple answers, useful for learning SQL as well as for interviews.

Practice SQL Exercises

• SQL Exercises, Practice, Solution
• SQL Retrieve data from tables [33 Exercises]
• SQL Boolean and Relational operators [12 Exercises]
• SQL Wildcard and Special operators [22 Exercises]
• SQL Aggregate Functions [25 Exercises]
• SQL Formatting query output [10 Exercises]
• SQL Quering on Multiple Tables [8 Exercises]
• FILTERING and SORTING on HR Database [38 Exercises]
• SQL JOINS
• SQL JOINS [29 Exercises]
• SQL JOINS on HR Database [27 Exercises]
• SQL SUBQUERIES
• SQL SUBQUERIES [39 Exercises]
• SQL SUBQUERIES on HR Database [55 Exercises]
• SQL Union[9 Exercises]
• SQL View[16 Exercises]
• SQL User Account Management [16 Exercise]
• Movie Database
• BASIC queries on movie Database [10 Exercises]
• SUBQUERIES on movie Database [16 Exercises]
• JOINS on movie Database [24 Exercises]
• Soccer Database
• Introduction
• BASIC queries on soccer Database [29 Exercises]
• SUBQUERIES on soccer Database [33 Exercises]
• JOINS queries on soccer Database [61 Exercises]
• Hospital Database
• Introduction
• BASIC, SUBQUERIES, and JOINS [39 Exercises]
• Employee Database
• BASIC queries on employee Database [115 Exercises]
• SUBQUERIES on employee Database [77 Exercises]
• More to come!

Want to improve the above article? Contribute your Notes/Comments/Examples through Disqus.

Previous: SQL SELF JOIN
Next: Joining tables with group by and order by