SQL with Jupyter Notebook Tutorial

36 / 61

Prepare data for Questions

Connect to a MySQL database run below commands to prepare the data for subsequent questions.

INSTRUCTIONS

Run the following:

%%sql
use retail_db;

drop table  if exists emp_<<your lab uername>>;

drop table  if exists deptt_<<your lab uername>>;

create table deptt_<<your lab uername>> (
   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_<<your lab uername>> (depname,depcity, depstreet,depopendate)
values('Accounts','Bengaluru','Daker Street','1900-05-19');

insert into deptt_<<your lab uername>> (depname,depcity, depstreet,depopendate)
values('HR','Delhi','Baker Street','1911-11-29');

insert into deptt_<<your lab uername>> (depname,depcity, depstreet,depopendate)
values('Finance','Mumbai','Laker Street', '1903-03-03');

insert into deptt_<<your lab uername>> (depname,depcity, depstreet,depopendate)
values('Travel','Pune','Haker Street','1907-07-07');

create table emp_<<your lab uername>>(
   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_<<your lab uername>>(depid) on delete restrict
);

insert into emp_<<your lab uername>> (empfname, emplname, depid, doj, empsal,mgrempid)
values('Beck','Dot',2,'2014-09-10',2500,1);

insert into emp_<<your lab uername>> (empfname, emplname, depid, doj, empsal,mgrempid)
values('Jack','Den',2,'2017-10-28',2400,3);

insert into emp_<<your lab uername>> (empfname, emplname, depid, doj, empsal,mgrempid)
values('Emil','Bon',3,'2018-01-20',2100,1);

insert into emp_<<your lab uername>> (empfname, emplname, depid, doj, empsal,mgrempid)
values('Siera','Leck',3,'2014-02-17',2000,4);

insert into emp_<<your lab uername>> (empfname, emplname, depid, doj, empsal,mgrempid)
values('Koe','Hoker',3,'2016-03-16',2900,4);

insert into emp_<<your lab uername>> (empfname, emplname, depid, doj, empsal,mgrempid)
values('Kate','Huds',4,'2017-11-22',2500,3);

insert into emp_<<your lab uername>> (empfname, emplname, depid, doj, empsal,mgrempid)
values('Rene','Swaz',4,'2017-09-19',2450,3);

insert into emp_<<your lab uername>> (empfname, emplname, depid, doj, empsal,mgrempid)
values('Zack','Night',4,'2013-05-15',2800,3);

insert into emp_<<your lab uername>> (empfname, emplname, depid, doj, empsal,mgrempid)
values('Beck','Decker',4,'2015-06-23',2350,3);

insert into emp_<<your lab uername>> (empfname, emplname, depid, doj, empsal,mgrempid)
values('Bruno','Mars',2,'1985-10-08',2350,1);

insert into emp_<<your lab uername>> (empfname, emplname, depid, doj, empsal,mgrempid)
values('Kale','Hacker',2,'1984-12-17',2800,1);

insert into emp_<<your lab uername>> (empfname, emplname, depid, doj, empsal,mgrempid)
values('Colt','Speed',4,'1987-11-18',2800,2);

insert into emp_<<your lab uername>> (empfname, emplname, depid, doj, empsal,mgrempid)
values('Etal','Femme',1,'1985-01-01',2425,2);

insert into emp_<<your lab uername>>(empfname, emplname, depid, doj, empsal,mgrempid)
values('Mont','Blanc',1,'1987-05-01',2425,2);

insert into emp_<<your lab uername>> (empfname, emplname, depid, doj, empsal,mgrempid)
values('Iron','Man',4,'1984-12-17',2800,3);

insert into emp_<<your lab uername>> (empfname, emplname, depid, doj, empsal,mgrempid)
values('Spider','Man',2,'2015-06-23',2350,3);

insert into emp_<<your lab uername>> (empfname, emplname, depid, doj, empsal,mgrempid)
values('Captain','America',2,'2014-02-17',2000,1);

insert into emp_<<your lab uername>> (empfname, emplname, depid, doj, empsal,mgrempid)
values('Super','Hulk',1,'2014-02-17',2000,2);

insert into emp_<<your lab uername>> (empfname, emplname, depid, doj, empsal,mgrempid)
values('Cat','Woman',2,'2016-05-22',2375,3);

No hints are availble for this assesment

Answer is not availble for this assesment


Note - Having trouble with the assessment engine? Follow the steps listed here

Loading comments...