UDHNA CITIZEN COMMERCE COLLEGE &
SPB COLLEGE OF BUSINESS ADMINISTRATION &
SMT. DIWALIBEN HARJIBHAI GONDALIA COLLEGE OF BCA AND I.T.
(Self Financed College Affiliated To VNSGU, Surat)
(Managed By Udhna Academy Education Trust, Udhna)
214, Ranchhodnagar, Opp. Swaminarayan Temple, Surat-Navsari Road, Udhna, Surat – 394 210
(B.COM./B.B.A./B.C.A.)
:: B.C.A. PROGRAMME ::
CERTIFICATE
This is to certify that Mr. / Miss.
of Class Semester Roll No.
Exam/Seat No. has satisfactorily completed his / her software
laboratory work in the subject
paper no. during the academic year .
He / She has completed programs out of .
Date:
Faculty Name: Head Of the Dept.
Sign:
Examiner:
Date:
UDHNA CITIZEN COMMERCE COLLEGE &
SPB COLLEGE OF BUSINESS ADMINISTRATION &
SMT.DIWALIBEN HARJIBHAI GONDALIYA COLLEGE OF BCA &IT
A.Y. :2024-25
CLASS:S.Y.B.C.A.( 3rd SEMESTER)
SUBJECT: Database Handling Using Python(303)
INDEX
Sr Program Page Sign.
No. no.
1 Create the following tables and enters at least 10 records with
appropriate constraints:
Employees(employee_id, first_name, last_name, age, email,
phone_number,hire_date, job_id, salary, commission_pct,
manager_id, department_id)
Departments( Department_Id, Department_Name, Manager_Id,
Location Id)
Locations( location_id, street_address, postal_code city,
state_province,country_id)
Countries(country_id,country_name,region_id)
A) Write a query to display the names (first_name, last_name)
using alias name "First Name", "Last Name"
B) Write a query to select first 10 records from a table
C) Write a query to display the last names of employees whose
names have exactly 6 characters
D) Write a query to get the department ID and the total salary
payable ineach department
E) Write a query to find the names (first_name, last_name) of
the employees who have a manager who works for a department
based in the United States
F) Write a query to find the names (first_name, last_name), the
salary of the employees who earn more than the average salary
and who works in any of the IT departments
G) Write a query to find the employee id, name (last_name)
along with their manager id, manager name (last_name).
H) Write a Query to display the job title and average salary of
employees.
I) Write a query to find the addresses(location_id,street_address,
city,state province, country name) of all the departments.
2 Write a command to Dump entire database with proper file
name and tables structure into a file named as your rollno.
3 Write a trigger called AGECHECK on table employees that
don’t allow the insertion or update of any record that has an age
less than 18
4 Program to find mean, median, mode from set of numbers in a
list
5 Write a python program to retrieve all rows from employee table
and display the column values in tabular format
6 Program to read CSV file and upload data into table
7 Write a Python Program to retrieve all rows from employee
table and dump into ‘employee_details.csv’ CSV file.
8 Write Program to implement DML operations using sqlite3
9 Get total salary from employees table and show line plot with
the following style properties
Generated line plot must include following style properties :-
• line style dotted and line color should be red.
• show legend at the lower right location.
• Y label name=salary
• X label name=employee name
• Add a circle Marker.
• Line marker color as read
• line width should be 3.
10 Use employee_details.csv file and read salary and
commission_pct data and show it using the bar chart the bar
chart should display the number of units for each employee. add
a separate bar for each first name in the same chart.
RAKESH GOUD DIV:- E 2481021
PRATICLE-1
Q.1 Create the following tables and enter at least 10 records with appropriate
constraints:
1. Employees(employee_id, first name, last_name, age, email, phone number, hire
date, job id, salary, commission pct, manager id, department id)
2. Departments( Department_Id, Department_ Name, Manager Id, Location Id)
3. Locations( location id, street address, postal_code city, state_province, country id)
4. Countries (country id, country name, region id)
A. Write a query to display the names (first name, last name) using alias name "First
Name", "Last Name"
B. Write a query to select first 10 records from a table.
C. Write a query to display the last names of employees whose names have exactly
6 characters.
D. Write a query to get the department ID and the total salary payable in each
department.
E. Write a query to find the names (first name, last name) of the employees who
have a manager who works for a department based in the United States.
F. Write a query to find the names (first name, last nanme), the salary of the
employees who carn more than the average salary and who works in any of the
IT department.
G. Write a query to find the employee id, name (last name) along with their
manager id, manager name (last name).
H. Write a query to display the job title and average salary of employees.
I. Write a query to find the addresses (location id, street address, city, state
province, country name) of all the departments.
TABLE 1:-
CREATE TABLE Employe
( employee_id INTEGER PRIMARY KEY,
first_name TEXT,
P a g e 1 | 28
RAKESH GOUD DIV:- E 2481021
last_name TEXT,
age INTEGER,
email TEXT,
phone_number TEXT,
hire_date TEXT,
job_id TEXT,
salary REAL,
commission_pct REAL,
manager_id INTEGER,
department_id INTEGER );
TABLE 2:-
CREATE TABLE Departments
( department_id INTEGER PRIMARY KEY,
department_name TEXT,
manager_id INTEGER,
location_id INTEGER);
TABLE 3:-
CREATE TABLE Locations
( location_id INTEGER PRIMARY KEY,
street_address TEXT,
postal_code TEXT,
city TEXT,
state_province TEXT,
country_id TEXT);
P a g e 2 | 28
RAKESH GOUD DIV:- E 2481021
TABLE 4:-
CREATE TABLE Countries
( country_id TEXT PRIMARY KEY,
country_name TEXT,
region_id INTEGER);
TABLE 1 INSERT:-
TABLE 2 INSERT:-
P a g e 3 | 28
RAKESH GOUD DIV:- E 2481021
TABLE 3 INSERT:-
P a g e 4 | 28
RAKESH GOUD DIV:- E 2481021
TABLE 4 INSERT:-
P a g e 5 | 28
RAKESH GOUD DIV:- E 2481021
A. Write a query to display the names (first name, last name) using alias
name "First Name", "Last Name".
B. Write a query to select first 10 records from a table.
P a g e 6 | 28
RAKESH GOUD DIV:- E 2481021
C. Write a query to display the last names of employees whose names have
exactly 6 characters.
D. Write a query to get the department ID and the total salary payable in
each department.
P a g e 7 | 28
RAKESH GOUD DIV:- E 2481021
E. Write a query to find the names (first name, last name) of the employees
who have a manager who works for a department based in the United
States.
F. Write a query to find the names (first name, last nanme), the salary of
the employees who carn more than the average salary and who works in
any of the IT department.
P a g e 8 | 28
RAKESH GOUD DIV:- E 2481021
G. Write a query to find the employee id, name (last name) along with their
manager id, manager name (last name).
H. Write a query to display the job title and average salary of employees.
P a g e 9 | 28
RAKESH GOUD DIV:- E 2481021
I. Write a query to find the addresses (location id, street address,
city, state province, country name) of all the departments.
P a g e 10 | 28
RAKESH GOUD DIV:- E 2481021
PRATICLE-2
Q.2 Write a command to dump entire database with proper file name
and tables structure into a file named as your rollno.
sqlite>.output 2481021.sql
sqlite> .dump
sqlite> .quit
P a g e 11 | 28
RAKESH GOUD DIV:- E 2481021
P a g e 12 | 28
RAKESH GOUD DIV:- E 2481021
P a g e 13 | 28
RAKESH GOUD DIV:- E 2481021
P a g e 14 | 28
RAKESH GOUD DIV:- E 2481021
PRATICLE-
15
Q.3 Write a trigger called AGECHECK on table EMPLOYE that don’t
allow the insertion or update or any record that has an age less than
18.
P a g e 15 | 28
RAKESH GOUD DIV:- E 2481021
PRATICLE-4
Q.4 Write a python program to find mean, median, mode from set of
numbers in a list.
import statistics
def calculate_statistics(numbers):
if not numbers:
raise ValueError("The list of numbers is empty")
mean = statistics.mean(numbers)
median = statistics.median(numbers)
mode = statistics.mode(numbers)
return mean, median, mode
def main():
numbers = [1, 2, 2, 3, 4, 5, 5, 5, 6, 7, 8, 9]
try:
mean, median, mode = calculate_statistics(numbers)
print(f"Mean: {mean}")
print(f"Median: {median}")
print(f"Mode: {mode}")
except ValueError as e:
print(e)
if name == "__main ":
main()
P a g e 16 | 28
RAKESH GOUD DIV:- E 2481021
OUTPUT:-
P a g e 17 | 28
RAKESH GOUD DIV:- E 2481021
PRATICLE-5
Q.5 Write a python to retrieve all rows from Employe table and
display the column values in tabular format.
import sqlite3
import pandas as pd
def fetch_and_display_employees(db_file):
conn = sqlite3.connect(db_file)
cursor = conn.cursor()
cursor.execute("SELECT * FROM employe")
rows = cursor.fetchall()
column_names = [description[0] for description in cursor.description]
df = pd.DataFrame(rows, columns=column_names)
conn.close()
db_file = "C:\sqlite\2481071.db"
fetch_and_display_employees(db_file)
P a g e 18 | 28
RAKESH GOUD DIV:- E 2481021
P a g e 19 | 28
RAKESH GOUD DIV:- E 2481021
PRATICLE-6
Q.6 Write a python program to read CSV file and upload data into
table.
import sqlite3
import csv
# Function to create a database connection and table
def create_table(database_path):
connection = sqlite3.connect(database_path)
cursor = connection.cursor()
cursor.execute('''
CREATE TABLE IF NOT EXISTS employees2 ( id INTEGER PRIMARY KEY, name TEXT, age
INTEGER,
department TEXT ) ''')
# This command creates the table if it does not exist
connection.commit()
connection.close()
# Function to read CSV file and insert data into the table
def insert_data_from_csv(database_path, csv_file):
connection = sqlite3.connect(database_path)
cursor = connection.cursor()
with open(csv_file, 'r') as file:
reader = csv.DictReader(file) for row in reader:
cursor.execute
(''' INSERT INTO employees2 (id, name, age, department) VALUES (
:id, :name, :age, :department) ''', row)
# This command inserts each row from the CSV file into the table
connection.commit()
connection.close()
P a g e 20 | 28
RAKESH GOUD DIV:- E 2481021
# Main execution if name == " main ":
database_path = r'C:\sqlite\vishal.db'
# Database path csv_file_path = r'C:\sqlite\employees.csv'
# CSV file path
create_table(database_path)
# Create the table if it doesn't exist insert_data_from_csv(database_path,
csv_file_path)
# Insert data from CSV connection = sqlite3.connect(database_path)
cursor = connection.cursor()
cursor.execute('SELECT * FROM employees2')
rows = cursor.fetchall()
for row in rows: print(row) #
Print each row to verify insertion connection.close()
P a g e 21 | 28
RAKESH GOUD DIV:- E 2481021
PRATICLE-
22
Q.7 Write a python program to retrieve all rows from employee table
and dump into 'employee details.csv' CSV file
P a g e 22 | 28
RAKESH GOUD DIV:- E 2481021
PRATICLE-8
Q.8 Write a program to implement DML operation using sqlite3.
Create:-
Insert:-
Select:-
Update:-
P a g e 23 | 28
RAKESH GOUD DIV:- E 2481021
Delete:-
P a g e 24 | 28
RAKESH GOUD DIV:- E 2481021
PRATICLE-9
Q.9 Get total salary from employees table and show line plot with
the following style properties Generated line plot must include
following style properties :
• line style dotted and line color should be red.
• show legend at the lower right location.
• Y label name=salary
• X label name=employee name
• Add a circle Marker.
• Line marker color as read
• line width should be 3.
P a g e 25 | 28
RAKESH GOUD DIV:- E 2481021
P a g e 26 | 28
RAKESH GOUD DIV:- E 2481021
PRATICLE-10
Q.10 ,Use employe_details.csv file and read salary and
commission_pct data and show it using the bar chart should display
the number of units for each employee. Add a separate bar each first
name in the same chart.
P a g e 27 | 28
RAKESH GOUD DIV:- E 2481021
P a g e 28 | 28
