SQL & Pandas Tasks

A. Use the tables in (in the attached files, code and picture) to answer the following questions in SQL.

1. Print the names of employees with no dependents.

2. Print the names of employees who are managers.

3. Print the names of employees who have more than one spouse.

4. Print the names of employees who worked more than 20 hours in Computerization or Reorganization projects.

5. Print the names of employees who work in all the projects.

6. Print the name of each employee and the name of his manager, for an employee who worked for a project located in Houston but never worked for a project located in Stafford.

B. Download any dataset from Kaggle and then perform the following in Pandas:

1. Read the file into a Pandas dataframe.

2. Apply the following Pandas functions on the dataframe:

i. fillna

ii. dropna

iii. replace

iv. groupby

v. concat

vi. merge (use it with inner joint, outer joint, left joint, right joint)

vii. pivot and pivot_table

viii. melt

ix. stack and unstack

x. crosstab

Deliverables:

– the code

– output

– Sample of the input file

create table Employee(
Fname varchar(15) NOT NULL,
Minit char(1) NOT NULL,
Lname varchar(15) NOT NULL,
ssn char(9) NOT NULL,
Bdate date,
Address varchar(30),
Sex char(1),
Salary decimal(10,2),
super_ssn char(9),
primary key(ssn),
foreign key(super_ssn) references Employee(ssn));
create table Department(
Dname varchar(15) NOT NULL,
Dnumber int NOT NULL,
Mgr_ssn char(9) NOT NULL,
Mgr_start_date date,
primary key(Dnumber),
foreign key(Mgr_ssn) references Employee(ssn));
create table Dept_Locations(
Dnumber int NOT NULL,
Dlocation varchar(15) NOT NULL,
primary key(Dnumber, Dlocation),
foreign key(Dnumber) references Department(Dnumber));
alter table Employee add(
Dno int NOT NULL,
foreign key(dno) references Department(dnumber));
create table Project(
Pname varchar(15) NOT NULL,
Pnumber int NOT NULL,
Plocation varchar(30) NOT NULL,
Dnum int NOT NULL,
primary key(Pnumber, Plocation),
foreign key(Dnum) references Department(Dnumber));
create table Works_On(
Essn char(9) NOT NULL,
Pno int NOT NULL,
hours time,
primary key(Essn, Pno),
foreign key(Essn) references Employee(ssn),
foreign key(Pno) references Project(Pnumber));
create table Dependent(
Essn char(9) NOT NULL,
Dependent_name varchar(15) NOT NULL,
sex char(1) NOT NULL,
Bdate date,
Relationship varchar(10) NOT NULL,
primary key(Essn, Dependent_name),
foreign key(Essn) references Employee(ssn));
SET FOREIGN_KEY_CHECKS=0;
Insert into Employee(Fname, Minit, Lname, ssn, BDate,
Address, Sex, Salary, Super_ssn, Dno) Values
(‘John’, ‘B’, ‘Smith’, ‘123456789’,’1965-09-01′, ‘731
Fondren, Houston, TX’, ‘M’, 30000, ‘333445555’, 5),
(‘Franklin’, ‘T’, ‘Wong’, ‘333445555’,’1955-12-08′, ‘638
Voss, Houston, TX’, ‘M’, 40000, ‘888665555’, 5),
(‘Alicia’, ‘J’, ‘Zelaya’, ‘999887777’,’1968-01-19′, ‘3321
Castle, Spring, TX’, ‘F’, 25000, ‘987654321’, 4),
(‘Jennifer’, ‘S’, ‘Wallace’, ‘987654321’,’1941-06-20′,
‘291 Berry, Bellaire, TX’, ‘F’, 43000, ‘888665555’, 4),
(‘Ramesh’, ‘K’, ‘Narayan’, ‘666884444’,’1962-09-15′, ‘975
Fire Oak, humble, TX’, ‘M’, 38000, ‘333445555’, 5),
(‘Joyce’, ‘A’, ‘English’, ‘453453453’,’1972-07-31′, ‘5631
Rice, Houston, TX’, ‘F’, 25000, ‘333445555’, 5),
(‘Ahmad’, ‘V’, ‘Jabbar’, ‘987987987’,’1969-03-29′, ‘980
Dallas, Houston, TX’, ‘M’, 25000, ‘987654321’, 4),
(‘James’, ‘E’, ‘Borg’, ‘888665555’,’1937-11-10′, ‘450
Stone, Houston, TX’, ‘M’, 55000, NULL, 1);
Insert into Department(Dname, Dnumber, Mgr_ssn,
Mgr_start_date) values
(‘Research’, 5, ‘333445555’, ‘1988-05-22’),
(‘Adminstration’, 4, ‘987654321’, ‘1995-01-01’),
(‘Headquarters’, 1, ‘888665555’, ‘1981-06-19’);
insert into Dept_Locations(Dnumber, Dlocation) values
(1, ‘Houston’),
(4, ‘Stafford’),
(5, ‘Bellaire’),
(5, ‘Sugarland’),
(5, ‘Houston’);
insert into Project(Pname, Pnumber, Plocation, Dnum)
values
(‘ProductX’, 1, ‘Bellaire’, 5),
(‘ProductY’, 2, ‘Sugarland’, 5),
(‘ProductZ’, 3, ‘Houston’, 5),
(‘Computerization’, 10, ‘Stafford’, 4),
(‘Reorganization’, 20, ‘Houston’, 1),
(‘Newbenefits’, 30, ‘Stafford’, 4);
insert into Works_On(Essn, Pno, Hours) values
(‘123456789’, 1, ‘32.5’),
(‘123456789’, 2, ‘7.5’),
(‘666884444’, 3, ‘40.0’),
(‘453453453’, 1, ‘20.0’),
(‘453453453’, 2, ‘20.0’),
(‘333445555’, 2, ‘10.0’),
(‘333445555’, 3, ‘10.0’),
(‘333445555’, 10, ‘10.0’),
(‘333445555’, 20, ‘10.0’),
(‘999887777’, 30, ‘30.0’),
(‘999887777’, 10, ‘10.0’),
(‘987987987’, 10, ‘35.0’),
(‘987987987’, 30, ‘5.0’),
(‘987654321’, 30, ‘20.0’),
(‘987654321’, 20, ‘15.0’),
(‘888665555’, 20, NULL);
insert into Dependent(Essn, Dependent_name, sex, BDate,
Relationship) values
(‘333445555’, ‘Alice’, ‘F’, ‘1986-04-05’, ‘Daughter’),
(‘333445555’, ‘Theodore’, ‘M’, ‘1983-10-25’, ‘Son’),
(‘333445555’, ‘Joy’, ‘F’, ‘1958-05-03’, ‘Spouse’),
(‘987654321’, ‘Abner’, ‘M’, ‘1942-02-28’, ‘Spouse’),
(‘123456789’, ‘Michael’, ‘M’, ‘1988-01-04’, ‘Son’),
(‘123456789’, ‘Alice’, ‘F’, ‘1988-12-30’, ‘Daughter’),
(‘123456789’, ‘Elizabeth’, ‘F’, ‘1967-05-05’, ‘Spouse’);
SET FOREIGN_KEY_CHECKS=1;