Question 1
Write a function fetchdata() to display all records in ascending
order of their salary from table employee using mysql connector
Host=localhost
User=root
Password=tiger
Database=school
Answer
Table Employee:
def fetchdata():
import mysql.connector
mydb = mysql.connector.connect(host = "localhost",
user = "root",
passwd = "tiger",
database = "School")
mycursor = mydb.cursor()
mycursor.execute("select * FROM EMPLOYEE ORDER BY SALARY")
myrecords = mycursor.fetchall()
for row in myrecords:
print(row)
mycursor.close()
mydb.close()
fetchdata()
Output
(4, 'ABHAY', 'FASHION STUDIES', 45000.0)
(1, 'RAJESH', 'IT', 60000.0)
(2, 'MANOJ KUMAR', 'HISTORY', 65000.0)
(3, 'ANUSHA', 'MARKETING', 70000.0)
Question 2
Write a function display() using interface to increase salary of the
employee, whose name is "MANOJ KUMAR", by 3000.
Host=localhost
User=root
Password=tiger
Database=school
Answer
def display():
import mysql.connector
mydb = mysql.connector.connect(host = "localhost",
user = "root",
passwd = "tiger",
database = "School")
mycursor = mydb.cursor()
mycursor.execute("UPDATE employee SET salary =
salary + 3000 WHERE Ename = 'MANOJ KUMAR'")
mydb.commit()
mydb.close()
Question 3
Write a program to delete the employee record whose name is
read from keyboard at execution time.
Host=localhost
User=root
Password=tiger
Database=school
Answer
import mysql.connector
mydb = mysql.connector.connect(host = "localhost",
user = "root",
passwd = "tiger",
database = "School")
mycursor = mydb.cursor()
employee_name = input("Enter the name of the employee to delete: ")
mycursor.execute("DELETE FROM employee
WHERE ENAME = { }”.format(employee_name))
print(mycursor.rowcount, "Record Deleted")
Output
Enter the name of the employee to delete: RAJESH
1 Record Deleted
Question 4
Create a database TESTDB in interface python.
Host=localhost
User=root
Password=tiger
Answer
import mysql.connector
conn = mysql.connector.connect(host = 'localhost',
user = 'root',
passwd = 'tiger')
cursor = conn.cursor()
cursor.execute("CREATE DATABASE TESTDB")
conn.commit()
conn.close()
Question 5
Write a program to create a table EMPLOYEE with Fields
FIRST_NAME, LAST_NAME, AGE, GENDER and INCOME by
using interface.
Host=localhost
User=root
Password=tiger
Database=school
Answer
import mysql.connector
mydb = mysql.connector.connect(host = 'localhost',
user = 'root',
passwd = 'tiger',
database = "school")
mycursor = mydb.cursor()
mycursor.execute("CREATE table EMPLOYEE (FIRST_NAME VARCHAR(45),
LAST_NAME VARCHAR(45),
AGE INTEGER,
GENDER VARCHAR(10),
INCOME FLOAT)")
mydb.commit()
mycursor.close()
mydb.close()
Write a function display() using interface to increase salary of the
employee, whose name and salary values are inputted by the
user during execution time.
Host=localhost
User=root
Password=tiger
Database=school
Answer
def display():
import mysql.connector
mydb = mysql.connector.connect(host = "localhost",
user = "root",
passwd = "tiger",
database = "School")
mycursor = mydb.cursor()
name=input(“Enter the name to update”)
salary=int(input(“Enter a new salary”))
mycursor.execute("UPDATE employee SET salary =
{} + 3000 WHERE Ename = {}".format(salary,name)
mydb.commit()
mydb.close()
