Record Program 4:

PROGRAM USING NESTED DICTIONARY

PROBLEM DEFINITION:

To Write an interactive menu driven program in python to accept the

details of employees such as employee number, name, Basic ,HRA , DA from
user and calculate annual salary of every employee with deductions like PF and
to calculate the net salary(Basic+HRA +DA –PF)using dictionaries.

Program:

def calculate(d,n):

for i in range(1,n+1):

pf=(d[i]['basic']+d[i]['hra']+d[i]['da'])*0.1

net=d[i]['basic']+d[i]['hra']+d[i]['da'] -pf

d[i]['pf']=pf

d[i]['net']=net

d[i][‘annual’]=net*12

d={}

print("\tProgram using Nested Dictionary")

n=int(input("Enter the No. of Employees"))

for i in range(1, n+1):

d1={}

d1['name']=input("Enter the name")

d1['basic']=int(input("Enter the basic pay"))

d1['hra']=int(input("Enter the hra"))

d1['da']=int(input("Enter the da"))

d[i]=d1

calculate(d,n)

print("The Payment details are")

print("Name \t\t Basic \t\t HRA \t\t DA \t\t PF \t\t Netsalary\t\t”Annual Salary")

for i in d:

for j in d[i]:

print(d[i][j],"\t\t", end='')

print()
Output:

Program using Nested Dictionary

Enter the No. of Employees3

Enter the nameARJUN

Enter the basic pay26000

Enter the hra4500

Enter the da2300

Enter the nameBHAVIK

Enter the basic pay32000

Enter the hra3400

Enter the da2300

Enter the nameCHINTU

Enter the basic pay12000

Enter the hra1200

Enter the da700

The Payment details are

Name Basic HRA DA PF Netsalary Annual Salary

ARJUN 26000 4500 2300 3280.0 29520.0 354240.0

BHAVIK 32000 3400 2300 3770.0 33930.0 407160.0

CHINTU 12000 1200 700 1390.0 12510.0 150120.0

Record Program 5:
 SQL EXERCISE -1

PROBLEM DEFINITION:

Create STATIONARY and CONSUMER table with the following specifications and insert the
given data.
i) Specification for tables:
Table name : STATIONARY
Attributes: S_ID of type String (Primary Key)

Company of type String

Price of type int
Table name : CONSUMER
Attributes: C_ID of type String(Primary key)
Consumer Name of type String
Address of type String
S_ID of type String (Foreign Key)
ii)Insert the data from the table given.
Write SQL commands for the following statements:
iii) To display distinct Company from STATIONARY.
iv) To display the details of Stationary whose Price is in the range of 8 to 15 (Both values
included).
v) To display the ConsumerName, Address from table Consumer and Company and Price from
table Stationary with their corresponding matching S_ID.

Table: STATIONARY

S_ID StationaryName Company Price

DP01 Dot Pen ABC 10
PL02 Pencil XYZ 6
ER05 Eraser XYZ 7
PL01 Pencil CAM 5
GP02 Gel Pen ABC 15
Table: CONSUMER

C_ID ConsumerName Address S_ID

01 Good Learner Delhi PL01
06 Write Well Mumbai GP02
12 Topper Delhi DP01
15 Write & Draw Delhi PL02
16 Motivation Bangalore PL01

SQL COMMANDS:

1. To create the table STATIONARY and CONSUMER.

Ans: create table STATIONARY(S_ID char(4) primary key, StationaryName

varchar(30), Company varchar(20), Price int);
Output: 0 rows affected ( table created)

create table CONSUMER(C_ID int primary key, ConsumerName varchar(30),

Address varchar(20), S_ID char(4), constraint cons foreign key(S_ID)
references STATIONARY(S_ID));

Output: 0 rows affected ( table created)

2. To insert the data into the tables.

Ans: insert into STATIONARY values(‘DP01’,’Dot Pen’, ‘ABC’,10),(‘PL02’,’Pencil’,

‘XYZ’,6), (‘ER05’, ‘Eraser’,’XYZ’, 7),(‘PL01’, ‘Pencil’, ‘CAM’, 5), (‘GP02’,’Gel
Pen’, ‘ABC’, 15);

Output: 5 rows affected

Ans: insert into CONSUMER values( 01, ‘Good Learner’,’Delhi’,’PL01’),( 06,’Write

Well’, ‘Mumbai’,’GP02’),( 12,’Topper’,’Delhi’,’DP01’),( 15,’Write &
Draw’,’Delhi’,’PL02’), (16,’Motivation’,’Bangalore’,’PL01’);

Output: 5 rows affected

3. To display distinct Company from STATIONARY

Ans: select distinct Company from STATIONARY;

Output:
distinct Company
ABC
XYZ
CAM

4. To display the details of Stationary whose Price is in the range of 8 to 15 (Both

values included)
Ans: select * from STATIONARY where Price between 8 and 15;

Output:
S_ID StationaryName Company Price
DP01 Dot Pen ABC 10
GP02 Gel Pen ABC 15

5. To display the ConsumerName, Address from table Consumer and Company and
Price from table Stationary with their corresponding matching S_ID

Ans: select ConsumerName , Address, Company, Price from STATIONARY natural

join CONSUMER;

Output: ConsumerName Adrress Company Price

Topper Delhi ABC 10
Write & Draw Delhi XYZ 6
Good Learner Delhi CAM 5
Motivation Bangalore CAM 5
Write Well Mumbai ABC 15

Record Program No. 6:

SQL EXERCISE -2

PROBLEM DEFINITION:

Consider the tables WORKER and PAYLEVEL and answer the following
questions:
(i) Write a SQL Command to display the details of all the WORKERS in
descending order of their DOJ.
(ii) Write a SQL Command to display the NAME and DESIG of those
WORKERS whose PLEVEL is either P001 or P002.
(iii) Write a SQL Command to display the number of workers whose
PAY+ALLOWANCE is more than 30000 for every PLEVEL.
(iv) Write a SQL Command to increase the ALLOWANCE by 1000 where
the pay is greater than 20000.
(v) Write a SQL Command to display the number of Workers designation
wise.

TABLE : WORKER

ECODE NAME DESIG PLEVEL DOJ DOB

11 RadheShyam Supervisor P001 2004-09-12 1981-08-23
12 Chandernath Operator P003 2010-02-22 1987-07-12
13 Fizza Operator P003 2009-06-14 1983-10-14
14 Ameen Ahmed Mechanic P002 2006-08-21 1984-03-13
15 Sanya Clerk P002 2005-12-19 1983-06-09
18 Sarsa Supervisor P001 2010-01-20 1982-02-01

TABLE : PAYLEVEL

PLEVEL PAY ALLOWANCE

P001 26000 12000
P002 22000 10000
P003 12000 6000

SQL COMMANDS:

1. To display the details of all the WORKERS in descending order of their DOJ.

Ans: SELECT * FROM WORKER ORDER BY DOJ DESC;

Output:

ECODE NAME DESIG PLEVEL DOJ DOB

12 Chandernath Operator P003 2010-02-22 1987-07-12
18 Sarsa Supervisor P001 2010-01-20 1982-02-01
13 Fizza Operator P003 2009-06-14 1983-10-14
14 Ameen Mechanic P002 2006-08-21 1984-03-13
Ahmed
15 Sanya Clerk P002 2005-12-19 1983-06-09
11 RadheShyam Supervisor P001 2004-09-12 1981-08-23

2. To display the NAME and DESIG of those WORKERS whose PLEVEL is either P001 or
P002.

Ans: SELECT NAME, DESIG FROM WORKER WHERE PLEVEL IN('P001','P002');

Output:

NAME DESIG
RadheShyam Supervisor
Ameen Ahmed Mechanic
Sanya Clerk
Sarsa Supervisor

3. To display the number of workers whose PAY+ALLOWANCE is more than 30000 for
every PLEVEL.

Ans: SELECT COUNT(*) FROM WORKER, PAYLEVEL WHERE WORKER.PLEVEL =

PAYLEVEL.PLEVEL AND PAY+ALLOWANCE >30000 GROUP BY WORKER. PLEVEL;

Output:

COUNT(*)
2
2

4. To increase the ALLOWANCE by 1000 where the pay is greater than 20000.

Ans: UPDATE PAYLEVEL SET ALLOWANCE =ALLOWANCE+1000

WHERE PAY>20000;

Output:
2 rows affected

5. To display the number of Workers designation wise.

Ans: SELECT COUNT(*),DESIG FROM WORKER GROUP BY DESIG;

Output:

COUNT(*) DESIG
2 Supervisor
2 Operator
1 Mechanic
1 Clerk

Record Program No. 7:

SQL EXERCISE -3

PROBLEM DEFINITION:

Consider the given tables COMPUTER and SALES and answer the questions that
follows:
Write SQL commands for the following statements:
i) Display the minimum price and maximum price from the table COMPUTER.
ii) Display the total number of records company wise, when the count is greater
than 1.
iii)Add a Constraint PRIMARY KEY to the column PROD_ID in the table
COMPUTER
iv)Display the product details and quantity sold for the first quarter using
natural join.
v)Increase the price of the products by 100 for input type products.

Table: COMPUTER
PROD_ID PROD_NAME PRICE COMPANY TYPEE
P001 Mouse 200 Logitech Input
P002 Laser Printer 4000 Canon Output
P003 Keyboard 500 Logitech Input
P004 Joystick 1000 Iball Input
P005 Speaker 1200 Creative Output
P006 Deskjet Printer 4300 Canon Output

Table : SALES
PROD_ID QTY_SOLD QUATER
P002 4 1
P003 2 2
P001 3 2
P004 2 1

SQL COMMANDS:

1. Display the minimum price and maximum price from the table
COMPUTER.

Ans: select max(PRICE), min(PRICE) from COMPUTER;

Output:

Max(PRICE) Min(PRICE)
4300 200

2. Display the total number of records company wise, when the count is
greater than 1.
Ans: select COMPANY, count(*) from COMPUTER group by COMPANY
having count(*)>1;

Output:

COMPANY Count(*)
Canon 2
Logitech 2

3. Add a Constraint PRIMARY KEY to the column PROD_ID in the table

COMPUTER

Ans: alter table COMPUTER add primary key(PROD_ID);

Output:
6 rows affected

4.Display the product details and quantity sold for the first quarter using
natural join.

Ans: select COMPANY.*, QTY_SOLD from COMPUTER natural join SALES

where QUATER=1

Output:

PROD_ID PROD_NAME PRICE COMPANY TYPEE QTY_SOLD

P002 Laser Printer 4000 Canon Output 4
P004 Joystick 1000 Iball Input 2
5. Increase the price of the products by 100 for input type products.

Ans: update COMPUTER set PRICE=PRICE+100 where TYPEE=’Input’;

Output:
3 rows affected.

Record Program No. 8

SQL EXERCISE - 4

PROBLEM DEFINITION:

Consider the given tables TOYSHOP and SUPPLIERS and W rite SQL commands for the
following statements:
i. To display the highest price of each type of toy.
ii. To display the toys which are supplied by Ritwij Rawat
iii. To display the total quantity of toys supplied by each supplier.
iv. To display the name of the supplier who supplies Tennis Raquets.
v. To add a new column, Date of Order of the type date to the table TOYSHOP

Table - TOYSHOP

SNO NAME SCODE QUANTITY PRICE TYPE

T005 Ball H22 20 450 HF
T003 Ludo P101 60 200 GF
T004 Stuffed Panda G204 40 650 TF
T006 Monopoly S222 70 780 GF
T001 Tennis Raquet R402 40 1150 GF
T007 Train Tracks A101 85 990 HF
T002 Singing Doll R402 25 470 GF

Table - SUPPLIERS

SCODE SNAME
H222 Rohan Kumar
P101 Vijay Pal
G204 Garv Punaya
R402 Ritwij Rawat

SQL COMMANDS:

1. To display the highest price of each type of toy.

Ans: select max(PRICE),TYPE from TOYSHOP group by TYPE;

Output:
Max(PRICE) TYPE
1150 GF
990 HF
650 TF

2. To display the toys which are supplied by Ritwij Rawat

Ans: select TOYSHOP.* from TOYSHOP natural join SUPPLIERS

where SNAME=”Ritwij Rawat”;

Output:

SNO NAME SCODE QUANTITY PRICE TYPE

T001 Tennis Raquet R402 40 1150 GF
T002 Singing Doll R402 25 470 GF

3. To display the total quantity of toys supplied by each supplier.

Ans: select SNAME, sum(QUANTITY) from TOYSHOP natural join SUPPLIERS group by
SNAME;

Output:
SNAME Sum(QUANTITY)
Vijay Pal 60
Garv Punaya 40
Ritwij Rawat 65
4. To display the name of the supplier who supplies Tennis Raquet.

Ans: select SNAME from TOYSHOP T, SUPPLIERS S where T.SCODE=S.SCODE

and NAME = “Tennis Raquet”

SNAME
Ritwij Rawat

5. To add a new column, Date of Order of the type date to the table TOYSHOP

Ans: alter table TOYSHOP add DATEOFORD date;

Output: 0 row(s) affected

Record Program No. 9

SQL EXERCISE – 5

PROBLEM DEFINITION:

Consider the given tables SPORTS and COACH and Write SQL commands for the following
statements:
(i) To display scode, the number of coaches for each scode from the table coach and display
scode in descending order.
(ii) To display details of those sports and coachname which are having Prizemoney more than
9000 and coachname ends with ‘n’.
(iii) To display the contents of the sports table with their coachname whose schedule date is in
the year 2012.
(iv) To display number of different participants from the table sports.
(v) Delete the column Prizemoney from the table SPORTS.

TABLE : SPORTS

Scode Sportsname Participants Prizemoney Scheduledate

101 Carrom 2 5000 2012-01-23
102 Badminton 2 12000 2011-12-12
103 Table Tennis 4 8000 2012-02-14
105 Chess 2 9000 2012-01-01
108 Lawn Tennis 4 25000 2012-03-19

TABLE : COACH
 Code Name Scode
1 Ravi 101
2 Mohan 108
3 Sameer 101
4 Shikhar 103

SQL COMMANDS:

1. To display scode, the number of coaches for each scode from the table coach and
display scode in descending order.

Ans. SELECT SCODE, COUNT(*) FROM COACH GROUP BY SCODE DESC;

Output:

Scode Count(*)
108 1
103 1
101 2

2. To display details of those sports and coachname which are having Prizemoney
more than 9000 and coachname ends with ‘n’.

Ans.: SELECT SPORTSNAME, NAME “COACHNAME” FROM SPORTS, COACH WHERE

SPORTS.SCODE=COACH.SCODE AND PRIZEMONEY > 9000 AND
NAME LIKE “%N”;

Output:

Sportsname COACHNAME
Lawn Tennis Mohan
3. To display the contents of the sports table with their coachname whose schedule
date is in the year 2012.

Ans: SELECT SPORTS.*, NAME FROM SPORTS JOIN COACH ON

SPORTS.SCODE=COACH.SCODE AND SCHEDULEDATE>=’2012-01-01’
AND SCHEDULEDATE<=’2012-12-31’;

Output:

Scode Sportsname Participants Prizemoney Scheduledate Name

101 Carrom 2 5000 2012-01-23 Ravi
101 Carrom 2 5000 2012-01-23 Sameer
103 Table Tennis 4 8000 2012-02-14 Shikhar
108 Lawn Tennis 4 25000 2012-03-19 Mohan

4. To display number of different participants from the table sports.

Ans: SELECT DISTINCT PARTICIPANTS FROM SPORTS;

Output:
distinct Participants
2
4

5. Delete the column Prizemoney from the table SPORTS.

Ans: Alter table SPORTS drop Prizemoney;

Output :

0 rows updated
Record Program No. 10

PROGRAM USING MYSQL CONNECTIVITY -1

PROBLEM DEFINITION:

Write a Program to connect Python with MySQL using database connectivity and perform
the following operations on data in database: Insert, Fetch and Update the data.
i)Create a table Teacher with Teacher ID, Name, Age, Department, Doj, Salary, Gender,
Place.

TABLE - TEACHER
T_id Name Age Department Date_of_join Salary Gender
1 Jugal 34 Computer Science 10/01/2017 12000 M
2 Sharmila 31 History 24/03/2008 20000 F
3 Sandeep 32 Mathematics 12/12/2016 30000 M
4 Sangeeta 35 History 01/07/2015 40000 F
5 Rakesh 42 Mathematics 05/09/2007 25000 M

ii) Insert the above records into the TEACHER Table.

iii) Display the records of the Teachers who belong to “Delhi”.
iv) Update the salary for the Teachers by 10% who have joined in the year
2017 and 2018.
v) Display the highest salary being paid in each department

Program :

#Program using mysql connectivity for Teacher Table

def Display():
 cursor.execute("select * from Teacher")
data=cursor.fetchall()
count=cursor.rowcount
print("Rows: ",count)
print("\n\t%-10s"%"Tid.","%-15s"%"Name","%-9s"%"Age","%25s"%"Department","%- 0s"%"DateofJoin",\

"%-15s"%"Salary","%-10s"%"Gender","%-15s"%"Place")

for row in data:

print("\t%-10s"%row[0],"%-15s"%row[1],"%-9s"%row[2],"%-25s"%row[3],"%-20s"%row[4],\
"%-15s"%row[5],"%-10s"%row[6],"%-15s"%row[7])
def Select1():
cursor.execute("select * from Teacher where place='Delhi'")
data=cursor.fetchall()
print(" Teacher details who belong to Delhi")

print("\n\t%-10s"%"Tid.","%-15s"%"Name","%-9s"%"Age","%-25s"%"Department",\
"%-20s"%"DateofJoin","%-15s"%"Salary","%-10s"%"Gender","%-15s"%"Place")
for row in data:
print("\t%-10s"%row[0],"%-15s"%row[1],"%-9s"%row[2],"%-25s"%row[3],\
"%-20s"%row[4],"%-15s"%row[5],"%-10s"%row[6],"%-15s"%row[7])

def Update():
try:
print(" Updation of the Salary")
cursor.execute("update Teacher set salary=salary+salary*0.05 \
where doj between '2017-01-01' and '2018-12-31' ")
cursor.execute("select * from Teacher")
data=cursor.fetchall()
print("\n\t%-10s"%"Tid.","%-15s"%"Name","%-9s"%"Age","%-25s"%"Department",\
"%-20s"%"DateofJoin","%-15s"%"Salary","%-10s"%"Gender","%-15s"%"Place")
for row in data:
print("\t%-10s"%row[0],"%-15s"%row[1],"%-9s"%row[2],"%-25s"%row[3],\
"%-20s"%row[4],"%-15s"%row[5],"%-10s"%row[6],"%-15s"%row[7])
except:
print("Updation not possible.")

def Select2():
cursor.execute("select max(salary), dept from Teacher group by dept")
data=cursor.fetchall()
print(" Highest Salary details in each Department")
print("\n\t%-15s"%"Max(Salary).","%-20s"%"Department")
for row in data:
 print("\t%-15s"%row[0],"%-20s"%row[1])

#main program
import mysql.connector as sqltor
mycon=sqltor.connect(host="localhost",user="root",passwd="admin",database="s
ys")
if mycon.is_connected():
print("Successful")
cursor=mycon.cursor()

try:
cursor.execute("create table if not exists Teacher (T_id int primary key,Name varchar(20),\
Age int,dept varchar(20),doj date,salary int, gender char(1),place varchar(20))")
print(" 1.Table Created")
except:
print("Table already exists")
exit()
cursor.execute("insert into Teacher values(1,'Jugal',34,'Computer Science','2017-
01-10',12000,'M','Delhi')" )
cursor.execute("insert into Teacher values(2,'Sharmila',31,'History','2008-03-
24',20000,'F','Raipur')")
cursor.execute("insert into Teacher values(3,'Sandeep',32,'Mathematics','2016-12-
12',30000,'M', 'Delhi')")
cursor.execute("insert into Teacher values(4,'Sangeeta',35,'History','2015-07-
01',40000,'F','Chennai')")
cursor.execute("insert into Teacher values(5,'Rakesh',42,'Mathematics', '2007-09-
05',25000,'M','Delhi')")
print("2. 5 Records Inserted")
Display()
Select1()
Update()
Select2()

Output: