Database Handling Using Python

Assigmente :- 1

C:\Users\Admin>cd..

C:\Users>cd..

C:\>cd sqlite

C:\sqlite>sqlite3

SQLite version 3.36.0 2021-06-18 18:36:39

Enter ".help" for usage hints.

Connected to a transient in-memory database.

Use ".open FILENAME" to reopen on a persistent database.

sqlite> attach database '[Link]' as 'dbstud';

1- Create table temp with one column name dt, insert 5 different type of records and check the type
of it in sorted order.

sqlite> create table [Link](dt);

sqlite> insert into temp values(50),('GHV'),(2.5),(NULL),(x'abcd');

sqlite> .mode column

sqlite> select * from temp order by dt;

2-Create example for commit and roll back.

sqlite> begin;

sqlite> delete from temp where dt = 50;

sqlite> select * from temp;

106-DHRUVIN GHORI Page 1

Database Handling Using Python

sqlite> rollback;

sqlite> select * from temp;

sqlite> begin;

sqlite> delete from temp where dt = 50;

sqlite> commit;

sqlite> select * from temp;

3-Create table tblemp with fields Eid, Fname, Lname, Jdate, Salary, M_id, post.

create table [Link] (Eid, Fname, Lname, Jdate, Salary, M_id, post);

insert into tblemp values

select * from tblemp;

4-Write a query to display record of id 101.

select * from tblemp where eid = 101;

5-Write a query to display only the unique values.

select distinct lname from tblemp;

6-Write a query to display query salary between 10000 and 20000.

106-DHRUVIN GHORI Page 2
Database Handling Using Python

select * from tblemp where salary between 10000 and 20000;

7-Write a query to display lname from 'Patel', 'Mehta','Vyas'.

select * from tblemp where lname in ('Patel','Mehta','Vyas');

8-Write a query to display fname starting with A.

select fname from tblemp where fname like 'A%';

9-Write a query to display fname with length 4.

select fname from tblemp where fname like '____';

10-Write a query to display top 5 records.

select * from tblemp order by salary limit 5;

11-Write a query to get all employee details from the employee table order by first name in
descending order.

106-DHRUVIN GHORI Page 3

Database Handling Using Python

select * from tblemp order by fname desc;

12-Write a query to get the employee ID, fname, lname, salary in ascending order of salary.

select eid, fname, lname, salary from tblemp order by salary;

Eid Fname Lname Salary

13-Write a query to get the maximum and minimum salary from employees table.

select max(salary) as Max_salary, min(salary) as Min_salary from tblemp;

14-Write a query to get the average salary and number of employees in the employees table.

select avg(salary) as Avg_salary, count(eid) as Total_employees from tblemp;

15-Write a query get all first name from employees table in upper case.

select upper(fname) as Upper_case_Fname from tblemp;

106-DHRUVIN GHORI Page 4

Database Handling Using Python

16-Write a query to display the fname, lname and salary for all employees whose salary is not in the
range 10,000 through 15,000 and are in M_id 30 or 100.

select fname, lname, salary from tblemp where salary not between 10000 and 15000 and m_id=30
or m_id=100;

17-Write a query to display the last names of employees whose names have exactly 6 characters.

select lname from tblemp where fname like '______';

18-Write a query to display the last names of employees having 'e' as the third character.

select lname from tblemp where lname like '__e%';

19-Write a query to display the post of Manager and CEO in the employees table.

select post from tblemp where post in ('Manager','CEO');

20-Write a query to display the fname of all employees who have both an "a" and "i" in their first
name.

select fname from tblemp where fname like '%a%i%' or fname like '%i%a%';

106-DHRUVIN GHORI Page 5

Database Handling Using Python

Assignment :- 2

import sqlite3 as sq

print("1")

cn=[Link]("c:\sqlite\[Link]")

print("2")

[Link]("Drop table salesman1")

[Link]("create table salesman1(sid primary key, name, city, commission)")

+print("Table Created Successfully.")

[Link]("insert into salesman1 values(5001,'Mohan Patel','Anand',0.15)")

[Link]("insert into salesman1 values(5002,'Nail Shah','Surat',0.13)")

[Link]("insert into salesman1 values(5005,'Preet Vyas','Ahamdabad',0.11)")

[Link]("insert into salesman1 values(5006,'Jeevan Mehta','Navsari',0.14)")

[Link]("insert into salesman1 values(5003,'Paul Adam','Nadiad',0.12)")

[Link]("insert into salesman1 values(5007,'Ramesh Patel','Surat',0.13)")

print("Records Successfully Inserted.")

[Link]()

print("4")

s2=[Link]("select * from salesman1")

for i in s2:

print(i)

print("5")

s2=[Link]("select * from salesman1")

for i in s2:

print(i)
106-DHRUVIN GHORI Page 6
Database Handling Using Python

print("6")

s3=[Link]("select * from salesman1 where sid=5006 or sid=5003")

a=[Link]()

for i in a:

print(i)

print("7")

s4=[Link]("select * from salesman1")

for i in s4:

print(i)

print("8")

a=[Link]("select * from salesman1 where city = 'Surat'")

for i in a:

print(i)

print("9")

a=[Link]("select * from salesman1 where name like '%m%'")

for i in a:

print(i)

print("10")

a=[Link]("select * from salesman1 where commission between 0.11 and 0.13")

for i in a:

print(i)

print("11")

a=[Link]("select * from salesman1 where city in ('Anand','Navsari')")

106-DHRUVIN GHORI Page 7
Database Handling Using Python

for i in a:

print(i)

print("12")

a=int(input("Chose Sid from 5001,5002,5003,5005,5006,5007 : "))

b=[Link](f"select * from salesman1 where sid = {a}")

for i in b:

print(i)

print("13")

[Link]("update salesman1 set commission = 0.15 where sid = 5006")

s5=[Link]("select * from salesman1")

for i in s5:

print(i)

print("14")

[Link]("update salesman1 set city = 'Bharuch' where commission = 0.13")

s6=[Link]("select * from salesman1")

for i in s6:

print(i)

print("15")

[Link]("delete from salesman1 where sid = 5007")

s7=[Link]("select * from salesman1")

for i in s7: print(i)

print("16")

[Link]("delete from salesman1 where city = 'Bharuch'")

s8=[Link]("select * from salesman1")

106-DHRUVIN GHORI Page 8
Database Handling Using Python

for i in s8:

print(i)

print("17")

for i in range(3):

sid=int(input("Enter the Sid : "))

name=input("Enter the Name : ")

city=input("Enter the City : ")

commission=float(input("Enter the Commission : "))

[Link](f"insert into salesman1 values({sid},'{name}','{city}',{commission})")

s9=[Link]("select * from salesman1")

for i in s9:

print(i)

[Link]()

Assigment – 3

import sqlite3

cn=[Link]("c:\sqlite\[Link]")

[Link]("drop table tblorder")

print("1")

[Link]("create table tblorder(oid primary key,pur_amt,ord_date,sid references salesman(sid))" )

print("table successfully created.")

print("2")

[Link]("create trigger trg_tblorder before insert on tblorder begin select case when
new.pur_amt<1000 then raise(abort,'not enough purchase amount.')end; end;")

print("trigger is created.")

106-DHRUVIN GHORI Page 9

Database Handling Using Python

print("3")

[Link]("insert into tblorder values(1,150000,'2020-12-05',5001)")

[Link]("insert into tblorder values(2,42000,'2020-12-20',5002)")

[Link]("insert into tblorder values(3,2000,'2021-2-02',5003)")

[Link]("insert into tblorder values(4,14000,'2021-03-23',5004)")

[Link]("insert into tblorder values(5,23000,'2021-04-15',5005)")

[Link]("insert into tblorder values(6,33000,'2021-05-20',5006)")

[Link]("insert into tblorder values(7,32000,'2021-06-23',5007)")

[Link]("insert into tblorder values(8,30000,'2021-07-01',5008)")

[Link]("insert into tblorder values(9,43000,'2021-07-05',5009)")

[Link]("insert into tblorder values(10,12000,'2021-07-15',5010)")

print("record successfully inserted.")

[Link]()

print("5")

a=[Link]("select * from tblorder order by pur_amt")

for i in a:

print(i)

print("6")

a=[Link]("select * from tblorder limit 5")

for i in a:

print(i)

print("7")

a=[Link]("select * from tblorder where ord_date between '2020-12-01' and '2020-12-31';")

for i in a:

print(i)

106-DHRUVIN GHORI Page 10

Database Handling Using Python

print("8")

a=[Link]("select * from tblorder where pur_amt between 10000 and 20000")

for i in a:

print(i)

print("9")

a=[Link]("select ord.*, [Link],[Link] from tblorder ord, salesman1 s1m where

[Link]=[Link]")

for i in a:

print(i)

print("10")

a=[Link]("select sid, sum(pur_amt) from tblorder group by sid ")

for i in a:

print(i)

print("11")

a=[Link]("select *,(case when sid in(5001, 5002) then 'A' when sid in (5003,5005) then 'B' else
'C' end)as class from tblorder")

for i in a:

print(i)

print("12")

a=[Link]("select ord.*, [Link] from tblorder ord, salesman1 s1m where

[Link]=[Link]")

for i in a:

print(i)

print("13")

a=[Link]("select * from salesman1 where sid not in (select sid from tblorder)")
106-DHRUVIN GHORI Page 11
Database Handling Using Python

for i in a:

print(i)

print("14")

a=input("Enter the date : ")

b=[Link](f"select * from tblorder where ord_date='(a)'")

for i in b:

print(i)

print("15")

a=input("enter the first date : ")

b=input("enter the secound date : ")

c=[Link](f"select * from tblorder where ord_date between '(a)' and '(b)'")

for i in c:

print()

Assignment :- 4

import sqlite3

cn=[Link]("c:\sqlite\[Link]")

[Link]("drop table customer")

print("1")

[Link]("create table customer (cid primary key,cust_name,grade)")

print("table successfully create.")

print("2")

[Link]("create trigger trg_customer before insert on customer begin select case when
[Link] not between 100 and 500 then raise (abort,'invalid grade.')end; end;")

print("triger is create.")

106-DHRUVIN GHORI Page 12

Database Handling Using Python

print("3")

[Link]("insert into customer values('c1','Mohit Patel',100)")

[Link]("insert into customer values('c2','Geeta Vyas',200)")

[Link]("insert into customer values('c3','Jaya Patil',100)")

[Link]("insert into customer values('c4','Vishal Gohel',300)")

[Link]("insert into customer values('c5','Kartik Goyenka',200)")

[Link]("insert into customer values('c6','Meera Prajapati',100)")

[Link]("insert into customer values('c7','Veer Vyas',300)")

[Link]("insert into customer values('c8','Maya Mehta',200)")

print("records successfully inserted")

print("5")

a=[Link]("select distinct grade from customer")

for i in a:

print(i)

print("6")

a=[Link]("select cid, cust_name, grade,(case when grade=100 then 5000 when grade=200 then
10000 else 15000 end)as bouns from customer")

for i in a:

print(i)

print("7")

b=int(input("enter the grade :"))

a=[Link](f"select * from customer where grade = {b}")

for i in a:

print(i)

print("8")

106-DHRUVIN GHORI Page 13

Database Handling Using Python

b=input("enter any character : ")

a=[Link](f"select * from customer where cust_name like '%{b}%'")

for i in a:

print(i)

print("9")

b=input("enter the cid : ")

a=[Link](f"delete from customer where cid ='{b}'")

s1=[Link]("select * from customer")

for i in s1:

print(i)

print("10")

b=input("enter the cid : ")

a=[Link](f"update customer set grade = grade+50 where cid= '{b}'")

a=[Link]("select * from customer")

for i in s1:

print(i)

print("11")

for i in range(3):

a=input("enter the cid :")

b=input("enter the name :")

c=int(input("enter the grade : "))

[Link](f"insert into customer values('{a}','{b}',{c})")

s1=[Link]("select * from customer")

for i in s1:

print(i)
106-DHRUVIN GHORI Page 14
Database Handling Using Python

print("12")

b=input("enter the city : ")

a=[Link](f"select * from salesman1 where city= '{b}'")

for i in a:

print(i)

print("13")

b=int(input("enter the purchase amount : "))

a=[Link](f"select * from tblorder where pur_amt > {b}")

for i in a:

print(i)

print("14")

b=int(input("enter the salesman1 id : "))

a=[Link](f"select * from salesman1 where sid= {b}")

for i in a:

print(i)

Assignment :- 5

from [Link] import Armstrongnumber

from [Link] import Palidromenumber

from [Link] import Reversenumber

print(Armstrongnumber(155),"\n\n")

print(Palidromenumber(155),"\n\n")

print(Reversenumber(155),"\n\n")
106-DHRUVIN GHORI Page 15
Database Handling Using Python

from Pack1 import insert

from Pack2.Pack3 import Delete

from Pack3 import Update

from Pack3 import Display

from Pack4 import Search

while(True):

print("\n\n1) Insert 2) Delete 3) Update 4) Display 5) Search 6)EXIT")

choice = int(input("Enter Operation Number: "))

if (choice==1):

insert.insert_data()

continue

elif (choice==2):

Delete.Delete_Data()

continue

elif (choice==3):

Update.Update_Data()

continue

elif (choice==4):

Display.Display_Data()

continue

elif (choice==5):

Search.Search_Data()

continue

elif (choice==6):

break

else:

print("Invalid input")
106-DHRUVIN GHORI Page 16
Database Handling Using Python

continue

def insert_data():

import sqlite3

con = [Link]('C:\sqlite\[Link]')

print("connected")

d1 = int(input("Enter eid: "))

d2 = input("Enter Name: ")

d3 = int(input("Enter Salary: "))

d4 = input("Enter Post: ")

d5 = input("Enter Gender: ")

d6 = input("Enter Jdate: ")

[Link](f"insert into emp values{ d1, d2 , d3 , d4 , d5 , d6 }")

[Link]()

print("Record inserted")

def Delete_Data():

import sqlite3

con = [Link]('C:\sqlite\[Link]')

print("connected")

d1 = int(input("Enter eid to delete: "))

[Link](f"delete from emp where eid = {d1}")

print("Record Deleted")

[Link]().

def Display_Data():

import sqlite3

con = [Link]('C:\sqlite\[Link]')

print("connected")
106-DHRUVIN GHORI Page 17
Database Handling Using Python

a = [Link]("select * from emp")

for i in a:

print(i)

def Update_Data():

import sqlite3

con = [Link]('C:\sqlite\[Link]')

print("connected")

a1 = int(input("Enter Salary: "))

a2 = int(input("Enter Eid to Update: "))

[Link](f"update emp set salary = {a1} where eid = {a2}")

[Link]()

def Search_Data():

import sqlite3

con = [Link]('C:\sqlite\[Link]')

print("connected")

d1 = int(input("Enter EID to Search Record: "))

a = [Link](f"select * from emp where eid = {d1}")

for i in a:

print(i)

def Armstrongnumber(num):

# num = int(input("Enter number to check: "))

sum1 = 0

num1 = num

while(num>0):

rem = num%10

sum1 = sum1 + (rem * rem * rem)

106-DHRUVIN GHORI Page 18
Database Handling Using Python

num = num//10

if(num1==sum1):

print("Total sum: ",sum1)

print("Number is Armstrong")

else:

print("Total sum: ",sum1)

print("Number is not Armstrong")

def Palidromenumber(num):

# num = int(input("Enter number to check: "))

rev = 0

num1 = num

while(num>0):

rem = num%10

rev = rev * 10 + rem

num = num//10

if(num1==rev):

print("Reversed number: ",rev)

print("Number is palidrome")

else:

print("Reversed number: ",rev)

print("Number is not palidrome")

def Reversenumber(num):

# num = int(input("Enter number to check: "))

rev = 0

num1 = num

while(num>0):

rem = num%10
106-DHRUVIN GHORI Page 19
Database Handling Using Python

rev = rev * 10 + rem

num = num//10

print("Original Number: " , num1)

print("Reverse Number: " , rev)

Assignment :- 6

import pandas as pd

df=pd.read_excel('[Link]','Sheet1')

print(df)

mn=df[['Sub1','Sub2','Sub3']].mean(axis=0)

print(mn)

mn=df[['Sub1','Sub2','Sub3']].mean(axis=1)

print(mn)

md=df[['Sub1','Sub2','Sub3']].median(axis=0)

print(md)

md=df[['Sub1','Sub2','Sub3']].median(axis=1)

print(md)

mo=df[['Sub1','Sub2','Sub3']].mode(axis=0)

print(mo)

mo=df[['Sub1','Sub2','Sub3']].mode(axis=1)

print(mo)

106-DHRUVIN GHORI Page 20

Database Handling Using Python

sd=df[['Sub1','Sub2','Sub3']].std(axis=0)

print(sd)

sd=df[['Sub1','Sub2','Sub3']].std(axis=1)

print(sd)

vr=df[['Sub1','Sub2','Sub3']].var(axis=0)

print(vr)

vr=df[['Sub1','Sub2','Sub3']].var(axis=1)

print(vr)

import [Link] as plt

r=[Link]

n=[Link]

s1=df.Sub1

s2=df.Sub2

s3=df.Sub3

##[Link](r,s1,label='Sub1',color='b')

##[Link](r,s2,label='Sub2',color='g')

##[Link](r,s3,label='Sub3',color='r')

##[Link]('Subjectwise Student Result in Line Chart')

##[Link]('Rollno.')

##[Link]('Marks')

##[Link]()

##[Link]()

##[Link](r,s1,marker='o',c='Red',edgecolor='Black')
106-DHRUVIN GHORI Page 21
Database Handling Using Python

##[Link](r,s2,marker='o',c='Yellow',edgecolor='Black')

##[Link](r,s3,marker='o',c='Blue',edgecolor='Black')

##[Link]('Subjectwise Student Result in Scatter Chart')

##[Link]('Rollno.')

##[Link]('Marks')

##[Link]()

##[Link](s1,edgecolor='Black',bins=r,color='Red')

##[Link](s2,edgecolor='Black',bins=r,color='Red')

##[Link](s3,edgecolor='Black',bins=r,color='Red')

##[Link]('Subjectwise Student Result in Histogram Chart')

##[Link]('Rollno.')

##[Link]('Marks')

##[Link]()

[Link](r,s1,label='Sub1')

[Link](r,s2,label='Sub2')

[Link](r,s3,label='Sub3')

[Link]('Subjectwise Student Result in Bar chart')

[Link]('Rollno.')

[Link]('Marks')

[Link]()

106-DHRUVIN GHORI Page 22