Skip to main content

PLSQL Programming part 4 - SQL joins

/**
Do the following exercises for learning SQL joins
Pre-requisites: You must have CREATE privilege and some space allocated for you
**/

create table temp1
(
cust_id number,
cust_name varchar2(100),
addr1 varchar2(40),
addr2 varchar2(40),
addr3 varchar2(40),
addr4 varchar2(40),
pincode varchar2(40),
constraint cust_pk primary key ( cust_id )
);
/

create table service_tbl
(
cust_id number,
service_no number,
start_date date,
end_date date,
plan_type varchar2(10),
foreign key (cust_id) references temp1(cust_id)
);
/

alter table temp1 rename to cust_table;

Select * from cust_table;

insert into cust_table
values(100,'CUSTOMER 1','ADDRESS CODE 1','ADDRESS CODE 2','ADDRESS CODE 3','ADDRESS CODE 4',11111);
insert into cust_table
values(200,'CUSTOMER 2','ADDRESS CODE 1','ADDRESS CODE 2','ADDRESS CODE 3','ADDRESS CODE 4',11111);
insert into cust_table
values(300,'CUSTOMER 3','ADDRESS CODE 1','ADDRESS CODE 2','ADDRESS CODE 3','ADDRESS CODE 4',11111);
insert Into cust_table
values(400,'CUSTOMER 4','ADDRESS CODE 1','ADDRESS CODE 2','ADDRESS CODE 3','ADDRESS CODE 4',11111);
insert into cust_table
values(500,'CUSTOMER 5','ADDRESS CODE 1','ADDRESS CODE 2','ADDRESS CODE 3','ADDRESS CODE 4',11111);
insert into cust_table
values(600,'CUSTOMER 6','ADDRESS CODE 1','ADDRESS CODE 2','ADDRESS CODE 3','ADDRESS CODE 4',11111);
insert into cust_table
values(700,'CUSTOMER 6','ADDRESS CODE 1','ADDRESS CODE 2','ADDRESS CODE 3','ADDRESS CODE 4',11111);

commit;

Select *
from service_tbl;

insert into service_tbl
values(100,1032427,sysdate,null,'Post Paid');
insert into service_tbl
values(100,1032428,sysdate,null,'Post Paid');
insert into service_tbl
values(100,1032429,sysdate,null,'Post Paid');
insert into service_tbl
values(100,1032430,sysdate,null,'Post Paid');
insert into service_tbl
values(100,1032431,sysdate,null,'PrePaid');
insert into service_tbl
values(100,1032432,sysdate,null,'PrePaid');
insert into service_tbl
values(100,1032433,sysdate,null,'Post Paid');
insert into service_tbl
values(200,2032427,sysdate,null,'Post Paid');
insert into service_tbl
values(200,2032427,sysdate,null,'Post Paid');
insert into service_tbl
values(200,2032427,sysdate,null,'Post Paid');
insert into service_tbl
values(200,2032427,sysdate,null,'Post Paid');
insert into service_tbl
values(300,3032427,sysdate,null,'PrePaid');
insert into service_tbl
values(300,3032427,sysdate,null,'PrePaid');
insert into service_tbl
values(300,3032427,sysdate,null,'PrePaid');
insert into service_tbl
values(300,3032427,sysdate,null,'PrePaid');
insert into service_tbl
values(400,4032427,sysdate,null,'Post Paid');
insert into service_tbl
values(500,5032427,sysdate,null,'Post Paid');
insert into service_tbl
values(600,6032427,sysdate,null,'Post Paid');
insert into service_tbl
values(600,6032427,sysdate,null,'Post Paid');
insert into service_tbl
values(600,6032427,sysdate,null,'Post Paid');

commit;

/***
CARTESIAN PRODUCT
***/

Select *
from cust_table,service_tbl;

--It returns 140 rows

select count(*) from cust_table; -- Returns 7
select count(*) from service_tbl; --Returns 20 rows


/***
SELF JOIN
***/

Select *
from cust_table A,CUST_TABLE B;

--It returns 49 rows -- 7x7 ROWS

/***
EQUI JOINS
***/

Select *
from cust_table,SERVICE_TBL
WHERE cust_table.CUST_ID = SERVICE_TBL.CUST_ID;

--It returns 20 rows
--Returns only the records those satisy the condition
--Driving table or the leading table is duplicated for the no of records fetched in the joining table


/***
OUTER JOIN - LEFT

***/
--Modern syntax
Select *
from cust_table,SERVICE_TBL
WHERE cust_table.CUST_ID = SERVICE_TBL.CUST_ID(+);

--Old syntax
Select *
from cust_table left outer join SERVICE_TBL
on cust_table.CUST_ID = SERVICE_TBL.CUST_ID;
--It returns 21 rows
--Returns the records those satisfy the condition in serivce_tbl and those dont in cust_table
--For the cust_id 700 in cust_table, there are no child records in service_tbl
--So , in left outer join, it finds no matching records and adds a null set

/***
OUTER JOIN - RIGHT
***/

--Modern syntax
Select *
from cust_table,SERVICE_TBL
WHERE cust_table.CUST_ID(+) = SERVICE_TBL.CUST_ID;

--Old syntax
Select *
from cust_table right outer join SERVICE_TBL
on cust_table.CUST_ID = SERVICE_TBL.CUST_ID;
--It returns 20 rows
--Returns the records those satisy the condition in cust_table and those dont in service_tbl

Comments

Rosewell said…
nice post!
keep writing
July 3, 2013 at 7:35 AM
Post a Comment

Popular posts from this blog

Editor's Column - Traditional DBMS

Editor's column There were days when Rooms-sized processors used for processing data. IT boomed up at the start of 21st Century and so many innumerable inventions made Computer world and few among them are still up in the markets for thier better implemented programming Alogrithms which couples with computer hardware and software ( OS ). Textpads or notepads were the repository of data and programmers had to be Experts to do data manipulation. In other words, Programmers had to use thier own logic to do insert , delete, update in data source. For instance, Consider the following: file1.txt: 1 I am line number 1 2 I am line number 2 3 I am line number 3 4 I am line number 4 5 I am line number 5 6 I am line number 6 7 I am line number 7 8 I am line number 8 9 I am line number 9 10 I am line number 10 To programmatically delete 8 th record , developer had to use the following logic with OLD file handling methods in 3rd generation language. 1. Open file1.txt, an...
Post a Comment
Read more

PLSQL programming ( Learn by examples - Part 3 )

/** In this section , lets learn the following. How to use a for loop to process multiple records... **/ set serveroutput on size 100000 begin --Select objects of table type that are valid under the schema Susil for var in (Select * from all_objects where object_name not like '%$%' and object_name in ('EMPLOYEE','EMP') and status='VALID' and owner='SUSIL') loop if var.object_type='TABLE' then /* Dbms_metadata package provides us a function get_ddl to get the data definition on object. For your understanding , i have just chosen two tables in this example If you remove Object_name condition in the above SQL , it will print DDL definitions for all tables under the specified schema **/ dbms_output.put_line('====================================================================='); dbms_output.put_line('Table name =>'||var.objec...
Post a Comment
Read more

Identify current and prev details of a customer

  create table customer_rating( id char, name char, irrating char, jcrrating char, procDate date, flag char ); insert into customer_rating values('A','D','X','Y','28-Jul-21','M'); insert into customer_rating values('A','D','M','L','27-Jul-21','M'); Select cr.id, cr.name, cr_prev.irrating pcob_rating, cr.irrating cob_rating, cr_prev.jcrrating pcob_jcr_rating, cr.jcrrating cob_jcr_rating From Customer_Rating cr join Customer_Rating cr_Prev  ON cr.procdate = '28-Jul-21' AND cr_prev.id = cr.id AND cr_prev.procdate = (  Select max(procdate) from Customer_Rating  Where id=cr.id and procdate < cr.procdate ) ;
Post a Comment
Read more