Connect to a MySQL database run below commands to prepare the data for subsequent questions.
use retail_db;
drop table emp;
drop table deptt;
create table deptt (
depid int not null auto_increment,
depname varchar(100) not null,
depcity varchar(100) not null,
depstreet varchar(100) not null,
depopendate date,
primary key ( depid )
);
insert into deptt (depname,depcity, depstreet,depopendate)
values('Accounts','Bengaluru','Daker Street',NOW());
insert into deptt (depname,depcity, depstreet,depopendate)
values('HR','Delhi','Baker Street',NOW());
insert into deptt (depname,depcity, depstreet,depopendate)
values('Finance','Mumbai','Laker Street',NOW());
insert into deptt (depname,depcity, depstreet,depopendate)
values('Travel','Pune','Haker Street',NOW());
create table emp(
empid int not null auto_increment,
empfname varchar(100) not null,
emplname varchar(100) not null,
depid int not null,
doj datetime,
empsal int not null,
mgrempid int,
primary key ( empid )
,foreign key (depid) references deptt(depid) on delete restrict
);
insert into emp (empfname, emplname, depid, doj, empsal,mgrempid)
values('Beck','Dot',2,'2014-09-10',2500,1);
insert into emp (empfname, emplname, depid, doj, empsal,mgrempid)
values('Jack','Den',2,'2017-10-28',2400,3);
insert into emp (empfname, emplname, depid, doj, empsal,mgrempid)
values('Emil','Bon',3,'2018-01-20',2100,1);
insert into emp (empfname, emplname, depid, doj, empsal,mgrempid)
values('Siera','Leck',3,'2014-02-17',2000,4);
insert into emp (empfname, emplname, depid, doj, empsal,mgrempid)
values('Koe','Hoker',3,'2016-03-16',2900,4);
insert into emp (empfname, emplname, depid, doj, empsal,mgrempid)
values('Kate','Huds',4,'2017-11-22',2500,3);
insert into emp (empfname, emplname, depid, doj, empsal,mgrempid)
values('Rene','Swaz',4,'2017-09-19',2450,3);
insert into emp (empfname, emplname, depid, doj, empsal,mgrempid)
values('Zack','Night',4,'2013-05-15',2800,3);
insert into emp (empfname, emplname, depid, doj, empsal,mgrempid)
values('Beck','Decker',4,'2015-06-23',2350,3);
insert into emp (empfname, emplname, depid, doj, empsal,mgrempid)
values('Bruno','Mars',2,'1985-10-08',2350,1);
insert into emp (empfname, emplname, depid, doj, empsal,mgrempid)
values('Kale','Hacker',2,'1984-12-17',2800,1);
insert into emp (empfname, emplname, depid, doj, empsal,mgrempid)
values('Colt','Speed',4,'1987-11-18',2800,2);
insert into emp (empfname, emplname, depid, doj, empsal,mgrempid)
values('Etal','Femme',1,'1985-01-01',2425,2);
insert into emp (empfname, emplname, depid, doj, empsal,mgrempid)
values('Mont','Blanc',1,'1987-05-01',2425,2);
insert into emp (empfname, emplname, depid, doj, empsal,mgrempid)
values('Iron','Man',4,'1984-12-17',2800,3);
insert into emp (empfname, emplname, depid, doj, empsal,mgrempid)
values('Spider','Man',2,'2015-06-23',2350,3);
insert into emp (empfname, emplname, depid, doj, empsal,mgrempid)
values('Captain','America',2,'2014-02-17',2000,1);
insert into emp (empfname, emplname, depid, doj, empsal,mgrempid)
values('Super','Hulk',1,'2014-02-17',2000,2);
insert into emp (empfname, emplname, depid, doj, empsal,mgrempid)
values('Cat','Woman',2,'2016-05-22',2375,3);
Loading comments...