Practically, data from more than one table is required to serve end-user applications.
For example, a web page might need to show employee name, employeed id, deptt id, deptt city and salary in tabular format.
In such case, tables having department details and employee details need to be joined to get required output. Let's prepare the data and run a join query.
Freshly create tables deptt_<<your labe username>> and emp_<<your lab username>>.
%%sql
drop table if exists emp_<<your lab username>>;
drop table if exists deptt_<<your lab username>>;
create table deptt_<<your lab username>>(
depid int not null auto_increment,
depname varchar(30) not null,
depcity varchar(20) not null,
depstreet varchar(30) not null,
depopendate date,
primary key ( depid )
);
insert into deptt_<<your lab username>> (depname,depcity, depstreet,depopendate)
values('Accounts','Bengaluru','Daker Street',now());
insert into deptt_<<your lab username>> (depname,depcity, depstreet,depopendate)
values('HR','Delhi','Baker Street',now());
insert into deptt_<<your lab username>> (depname,depcity, depstreet,depopendate)
values('Finance','Mumbai','Laker Street',now());
create table emp_<<your lab username>>(
empid int not null auto_increment,
empfname varchar(100) not null,
emplname varchar(100) not null,
depid int not null,
doj date,
empsal int not null,
mgrempid int,
primary key ( empid ),
foreign key (depid) references deptt_<<your lab username>>(depid) on delete restrict
);
insert into emp_<<your lab username>> (empfname, emplname, depid, doj, empsal,mgrempid)
values('Sam','Fox',1,'2016-06-22',2200,null);
insert into emp_<<your lab username>> (empfname, emplname, depid, doj, empsal,mgrempid)
values('John','Doe',1,'2017-05-17',1600,1);
insert into emp_<<your lab username>> (empfname, emplname, depid, doj, empsal,mgrempid)
values('Beck','Dot',2,'2014-09-10',2500,1);
insert into emp_<<your lab username>> (empfname, emplname, depid, doj, empsal,mgrempid)
values('Jack','Den',2,'2017-10-28',2400,3);
insert into emp_<<your lab username>> (empfname, emplname, depid, doj, empsal,mgrempid)
values('Emil','Bon',3,'2018-01-20',2100,1);
insert into emp_<<your lab username>> (empfname, emplname, depid, doj, empsal,mgrempid)
values('Siera','Leck',3,'2014-02-17',2000,4);
insert into emp_<<your lab username>> (empfname, emplname, depid, doj, empsal,mgrempid)
values('Koe','Hoker',3,'2016-03-16',2900,4);
So, now we have 3 departments and 7 employees. We want to display department and employee details in a single output. Here, deptt and emp tables are related to each other via depid column and generally, and typically such a column is used to join 2 tables.
We join the tables as follows:
%%sql
select e.empid, e.empfname, e.emplname, e.depid, e.doj, e.empsal, d.depname, d.depcity
from emp_<<your lab username>> e, deptt_<<your lab username>> d
where e.depid = d.depid
order by empid,empfname,emplname;
Let us create a table deptt_emp_<<your lab username>> to store the resultant of the above join. To do so, we just need to put the above join query inside the create table syntax.
%%sql
drop table if exists deptt_emp_<<your lab username>>;
create table deptt_emp_<<your lab username>>(
select e.empid, e.empfname, e.emplname, e.depid, e.doj, e.empsal, d.depname, d.depcity
from emp_<<your lab username>> e, deptt_<<your lab username>> d
where e.depid = d.depid
order by empid,empfname,emplname
);
select * from deptt_emp_<<your lab username>>;
Note - Having trouble with the assessment engine? Follow the steps listed here
Loading comments...