Python Program to find the Gross Salary of Employee

 

Python Program to find the Gross Salary of Employee in a Department

Problem Statement

Write a Python program to establish a database connection to EmpDB and display the total gross salary paid to the employee working in the Quality Control Department. Assume the employee table has already been created and exists in the EmpDB. The fields of the Employee table are (EmpID, DeptName, GrossSalary).

Video Tutorial

Steps in Python program to find the find the Gross Salary of Employee in a Quality Control Department

Here SQLite database system is used for demonstration. hence first we need to import sqlite3. Once the sqlite3 library is imported we need to connect to the database using connect() function of sqlite3.

Pass database name as parameter to connect() function. In this case, the database name is EmpDB. If the database is already present connect() function will open the database otherwise it will create a new database with EmpDB as the name of the database.

Next, the execute() function of sqlite3 is used to execute insert and select queries. Sample data is inserted into the database using the insert query.

Then the select query is used to get all the data from the database and displayed to the user. Again select query is used to get the Gross Salary of all employees working in the Quality Control Department. Finally, the aggregate function SUM is used to get the total Gross Salary of all employees working in the Quality Control department. Finally, the aggregate function SUM is used to get the total Gross Salary of all employees working in the Quality Control department.

Source Code Python Program to find the Gross Salary of Employee in a Department

import sqlite3
conn = sqlite3.connect('EmpDB')
cur = conn.cursor()

cur.execute('DROP TABLE IF EXISTS Employee')
cur.execute('CREATE TABLE Employee (EmpID, DeptName, GrossSalary)')

cur.execute('INSERT INTO Employee (EmpID, DeptName, GrossSalary) VALUES (?, ?, ?)', \
            (100, 'Quality Control', 25000))
cur.execute('INSERT INTO Employee (EmpID, DeptName, GrossSalary) VALUES (?, ?, ?)', \
            (101, 'Quality Control', 20000))
cur.execute('INSERT INTO Employee (EmpID, DeptName, GrossSalary) VALUES (?, ?, ?)', \
            (102, 'Testing', 25000))
cur.execute('INSERT INTO Employee (EmpID, DeptName, GrossSalary) VALUES (?, ?, ?)', \
            (103, 'Testing', 30000))
cur.execute('INSERT INTO Employee (EmpID, DeptName, GrossSalary) VALUES (?, ?, ?)', \
            (104, 'Quality Control', 35000))

print('Employee Details:')
cur.execute('SELECT * FROM Employee')
print (cur.fetchall())

print('Gross salary of employees working in the Quality Control Department:')
cur.execute('SELECT GrossSalary FROM Employee WHERE DeptName = \'Quality Control\'')
print (cur.fetchall())

cur.execute('SELECT SUM(GrossSalary) FROM Employee WHERE DeptName = \'Quality Control\'')
print ('Total Gross Salary of Employees Working in Quality Control Dept. is', cur.fetchall()[0][0])

Output:

Employee Details:

[(100, ‘Quality Control’, 25000), (101, ‘Quality Control’, 20000), (102, ‘Testing’, 25000), (103, ‘Testing’, 30000), (104, ‘Quality Control’, 35000)]

Gross salary of employees working in the Quality Control Department: [(25000,), (20000,), (35000,)]

Total Gross Salary of Employees Working in Quality Control Dept. is 80000

Summary:

This tutorial discusses how to write a Python program to find the Gross Salary of employees in a quality control department. If you like the tutorial then share it with your friends. Subscribe to our YouTube channel for more videos and like the Facebook page for regular updates.

Leave a Comment

Your email address will not be published. Required fields are marked *