Mysql and data base programs ( 4 Practicals)

Practical 1
Aim
* To connect to MySQL Server
* To create a Database Student
* To create a Table Student
* To add 6 rows
* To fetch and display the records

import mysql.connector
#Connection to MySQL Server
con = mysql.connector.connect(host="localhost", user="root", passwd="")
mycursor = con.cursor()

#Creating Student Database
mycursor.execute("DROP DATABASE IF EXISTS student")
mycursor.execute("CREATE DATABASE student")
mycursor.execute("USE student")

#Creating Studentinfo Table
mycursor.execute("DROP TABLE IF EXISTS studentinfo")
mycursor.execute("CREATE TABLE studentinfo (name VARCHAR(30), age INT(3), gender
CHAR(1))")

sql = """INSERT INTO studentinfo(name, age, gender)
VALUES(%s, %s, %s)"""
rows = [('Amit', 18,'M'),('Sudha',17,'F'),('Suma',19,'F'),\
('Paresh',19,'M'),('Ali',17,'M'),('Gargi',17,'F')]
mycursor.executemany(sql, rows)
con.commit()

#To fetch the records and display
sql = "SELECT * FROM studentinfo"
mycursor.execute(sql)
result = mycursor.fetchall()
for row in result:
name = row[0]
age = row[1]
gender = row[2]
print("Name=%s, Age=%d, Gender=%c" % (name,age,gender))
con.close()

output
Name=Amit, Age=18, Gender=M
Name=Sudha, Age=17, Gender=F
Name=Suma, Age=19, Gender=F
Name=Paresh, Age=19, Gender=M
Name=Ali, Age=17, Gender=M
Name=Gargi, Age=17, Gender=F

Practical 2
Aim
* To Connect to database student
* To create a table result
* To add six rows
* To increase marks in Math by 5 for Sudha
* To fetch and display the records

import mysql.connector
#Connection to MySQL Server and database student
con = mysql.connector.connect(host="localhost", user="root", passwd="",\
                                                    database="student")
mycursor = con.cursor()

#Creating result Table
mycursor.execute("DROP TABLE IF EXISTS result")
mycursor.execute("CREATE TABLE result (name VARCHAR(30),\
phys INT(3), chem INT(3), math INT(3))")

#Inserting Rows in to the table
sql = """INSERT INTO result(name, phys, chem, math)
VALUES(%s, %s, %s, %s)"""
rows = [('Amit', 70,76,80),('Sudha',80,85,90),('Suma',50,70,90),\
               ('Paresh',55,60,70),('Ali',80,70,75),('Gargi',80,60,80)]
mycursor.executemany(sql, rows)
con.commit()

# Increasing marks of math by 5 for Sudha
sql = "UPDATE result SET math=math+5 WHERE name='%s'" % ('Sudha')
mycursor.execute(sql)

#To fetch the records and display
sql = "SELECT * FROM result"
mycursor.execute(sql)
result = mycursor.fetchall()
for row in result:
      name = row[0]
      p = row[1]
      c = row[2]
      m = row[3]
      print("Name=%s, Phys=%d, Chem=%d, Math=%d" % (name,p,c,m))
con.close()

output
Name=Amit, Phys=70, Chem=76, Math=80
Name=Sudha, Phys=80, Chem=85, Math=95
Name=Suma, Phys=50, Chem=70, Math=90
Name=Paresh, Phys=55, Chem=60, Math=70
Name=Ali, Phys=80, Chem=70, Math=75
Name=Gargi, Phys=80, Chem=60, Math=80

Practical 3
Aim
 * To Connect to database student
 * To create a table result
 * To add six rows
 * To delete the rows with math mark
 * To fetch and display the records

import mysql.connector
#Connection to MySQL Server and database student
con = mysql.connector.connect(host="localhost", user="root", passwd="",\
                                                   database='student')
mycursor = con.cursor()

#Inserting Rows in to the table
sql = """INSERT INTO result(name, phys, chem, math)
VALUES(%s, %s, %s, %s)"""
rows = [('Amit', 70,76,80),('Sudha',80,85,90),('Suma',50,70,90),\
                ('Paresh',55,60,70),('Ali',80,70,75),('Gargi',80,60,80)]
mycursor.executemany(sql, rows)
con.commit()

# Deleting the rows with math marks greater or equal to 90
sql = "DELETE FROM result WHERE math>='%d'" % (90)
mycursor.execute(sql)

#To fetch the records and display
sql = "SELECT * FROM result"
mycursor.execute(sql)
result = mycursor.fetchall()
for row in result:
      name = row[0]
      p = row[1]
      c = row[2]
      m = row[3]
      print("Name=%s, Phys=%d, Chem=%d, Math=%d" % (name,p,c,m))
con.close()

Output
Name=Amit, Phys=70, Chem=76, Math=80
Name=Paresh, Phys=55, Chem=60, Math=70
Name=Ali, Phys=80, Chem=70, Math=75
Name=Gargi, Phys=80, Chem=60, Math=80


Practical 4
Aim
 * To connect to database student
 * To create a Table staff
 * To add 6 rows
 * To fetch and display the records of all

import mysql.connector
#Connection to MySQL Server and database student
con = mysql.connector.connect(host="localhost", user="root", passwd="",\
                                                                        database="student")
mycursor = con.cursor()

#Creating result Table
mycursor.execute("DROP TABLE IF EXISTS staff")
mycursor.execute("CREATE TABLE staff (name VARCHAR(30),\
                desg VARCHAR(10), subject VARCHAR(10), salary INT(5))")

# Inserting six rows in staff
 sql = """INSERT INTO staff(name, desg, subject, salary)
VALUES(%s, %s, %s, %s)"""
 rows = [('Amit', 'PGT','CHEM',8000),('Sudha','HDM','BIOL',8500),\ 
               ('Suma','TGT','MATH',9000),('Paresh','PGT','HIND',7000),\
               ('Ali','PRT','COMM',7500),('Gargi','PGT','COMP',9000)]
mycursor.executemany(sql, rows)
con.commit()
#To fetch the records and display
sql = "SELECT * FROM staff WHERE salary>'%d'" % (8000)
mycursor.execute(sql)
result = mycursor.fetchall()
for row in result:
    name = row[0]
    des = row[1]
    sub = row[2]
    sal = row[3]
    print("Name=%s, Desg=%s, Subject=%s, Salary=%d" % (name,des,sub,sal))
con.close()

output
Name=Sudha, Desg=HDM, Subject=BIOL, Salary=8500
Name=Suma, Desg=TGT, Subject=MATH, Salary=9000
Name=Gargi, Desg=PGT, Subject=COMP, Salary=9000