Answer to Question #246782 in Python for saksham

Question

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).

Answer 1

import sqlite3

# connect to emp database
connection = sqlite3.connect('EmpDB')
# point to connection cursor
cursor = connection.cursor()

# create database table
cursor.execute('DROP TABLE IF EXISTS Employee')
cursor.execute('CREATE TABLE Employee (EmpID, DeptName, GrossSalary)')

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

print('Employee Details:\n')
cursor.execute('SELECT * FROM Employee')
# fetch data
print(cursor.fetchall())

print('Gross salary: \n')
# execute statement
cursor.execute('SELECT GrossSalary FROM Employee WHERE DeptName = \'Quality Control\'')
print(cursor.fetchall())

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