SQL RELATED PROGRAMS
1) Display record whose name start with particular character
import mysql.connector
mydb=mysql.connector.connect(host="localhost",user="root",database='result',
passwd="Sasi@2023",auth_plugin="mysql_native_password")
cursor=mydb.cursor()
s='SELECT * FROM mark1 where STUD_NAME like "s%"'
print("MARK1 TABLE")
cursor.execute(s)
print(cursor.fetchall())
s1='SELECT * from mark1 where STUD_NAME like "_H%"'
print("MARK1 TABLE")
cursor.execute(s1)
print(cursor.fetchall())
__________________________________________________________________________________
2) update mark of particular student
import mysql.connector
mydb=mysql.connector.connect(host="localhost",user="root",database='result',
passwd="Sasi@2023",auth_plugin="mysql_native_password")
cursor=mydb.cursor()
s='update mark1 set MARK=MARK+5 where STUD_NAME="SHALINI"'
cursor.execute(s)
mydb.commit()
print("MARK1 TABLE")
s1='select * from mark1'
cursor.execute(s1)
print(cursor.fetchall())
3) inserting records into table
import mysql.connector
mydb=mysql.connector.connect(host="localhost",user="root",database='result',
passwd="Sasi@2023",auth_plugin="mysql_native_password")
cursor=mydb.cursor()
sql='INSERT INTO mark1 values(12001,"SHYAM",98.23),
(12002,"ABISHEK",87.56),(12003,"SRI",90.67),(12004,"SHALINI",85.98),
(12005,"VISHAL",78.87)'
cursor.execute(sql)
mydb.commit()
s='SELECT * FROM mark1'
print("MARK1 TABLE")
cursor.execute(s)
print(cursor.fetchall())
__________________________________________________________________________________
4) sorting records
import mysql.connector
mydb=mysql.connector.connect(host="localhost",user="root",database='result',
passwd="Sasi@2023",auth_plugin="mysql_native_password")
cursor=mydb.cursor()
s='SELECT * FROM mark1 order by STUD_NAME'
print("MARK1 TABLE")
cursor.execute(s)
print(cursor.fetchall())
s1='SELECT * from mark1 order by MARK DESC'
print("MARK1 TABLE")
cursor.execute(s1)
print(cursor.fetchall())
__________________________________________________________________________________
5) display student record whose mark greater than 90
import mysql.connector
mydb=mysql.connector.connect(host="localhost",user="root",database='result',
passwd="Sasi@2023",auth_plugin="mysql_native_password")
cursor=mydb.cursor()
s='SELECT * FROM mark1 where MARK>90'
print("MARK1 TABLE")
cursor.execute(s)
print(cursor.fetchall())
________________________________________________________________________________
6) display selected column
import mysql.connector
mydb=mysql.connector.connect(host="localhost",user="root",database='result',
passwd="Sasi@2023",auth_plugin="mysql_native_password")
cursor=mydb.cursor()
s='SELECT REGNO,MARK FROM mark1'
print("MARK1 TABLE")
cursor.execute(s)
print(cursor.fetchall())
7) create database
import mysql.connector
mydb=mysql.connector.connect(host="localhost",user="root",passwd="Sasi@20
23",auth_plugin="mysql_native_password")
cursor=mydb.cursor()
sql='create database result1'
cursor.execute(sql)
s='show databases'
print("ALL DATABASES")
cursor.execute(s)
print(cursor.fetchall())
