PROGRAM 12
Q12. Write a Python program to display all students
who scored the highest marks from the Student
table.
Code –
import [Link]
con = [Link](host="localhost",
user="root", password="1234", database="School")
cur = [Link]()
[Link]("SELECT MAX(Marks) FROM Student")
highest = [Link]()[0]
[Link]("SELECT Name, Marks FROM
Student WHERE Marks = %s", (highest,))
data = [Link]()
print("Toppers:")
for row in data:
print("Name:", row[0], " Marks:", row[1])
[Link]()
PROGRAM 12
Output –
Toppers:
Name: Rahul Sharma Marks: 98
Name: Neha Verma Marks: 98
DOCUMENTATION 12
Objective
Write a Python program to display all
students who scored the highest marks
from the Student table.
Type of file
.py
Size of file
1 kb
Software required
Python
Version
3.11
DOCUMENTATION 13
Objective
Write a Python program to fetch and
display only Name and Marks of students
who have Marks greater than 90.
Type of file
.py
Size of file
1 kb
Software required
Python
Version
3.11
PROGRAM 13
Q13. Write a Python program to fetch and display
only Name and Marks of students who have Marks
greater than 90.
Code –
import [Link]
con = [Link](host="localhost",
user="root", password="1234", database="School")
cur = [Link]()
[Link]("SELECT Name, Marks FROM Student
WHERE Marks > 90")
data = [Link]()
[Link]("SELECT Name, Marks FROM
Student WHERE Marks = %s", (highest,))
data = [Link]()
for row in data:
print("Name:", row[0], " Marks:", row[1])
[Link]()
PROGRAM 13
Output –
Name: Rahul Sharma Marks: 98
Name: Neha Verma Marks: 98
Name: Aarav Mehta Marks: 95
Name: Riya Kapoor Marks: 95
Name: Ankit Sharma Marks: 92
Name: Sneha Patel Marks: 97
DOCUMENTATION 14
Objective
Write a Python program to count the
number of students who scored more
than 80 marks
Type of file
.py
Size of file
1 kb
Software required
Python
Version
3.11
PROGRAM 14
Q14. Write a Python program to count the number
of students who scored more than 80 marks.
Code –
import [Link]
con =[Link](host="localhost",
user="root", password="1234", database="School")
cur = [Link]()
[Link]("SELECT COUNT(*) FROM Student
WHERE Marks > 80")
count = [Link]()[0]
print("Number of students scoringabove 80:",
count)
[Link]()
PROGRAM 14
Output –
Number of students scoring above 80: 6
DOCUMENTATION 15
Objective
Write a Python program to delete a
record with RollNo = 1 from the Student
table
Type of file
.py
Size of file
1 kb
Software required
Python
Version
3.11
PROGRAM 15
Q15. Write a Python program to delete a record
with RollNo = 1 from the Student table.
Code –
import [Link]
con =[Link](host="localhost",
user="root", password="1234", database="School")
cur = [Link]()
[Link]("DELETE FROM Student WHERE
RollNo = 1")
[Link]
print("Record deleted successfully!”)
[Link]()
PROGRAM 15
Output –
Record deleted successfully!
DOCUMENTATION 16
Objective
Write a Python program to create a table
Student in a MySQL database School with
fields: RollNo (INT, Primary Key) , Name
(VARCHAR(30)) , Marks (INT). Also, insert two
records into the table
Type of file
.py
Size of file
2 kb
Software required
Python
Version
3.11
PROGRAM 16
Q16. Write a Python program to create a table
Student in a MySQL database School with fields:
RollNo (INT, Primary Key) , Name (VARCHAR(30)) ,
Marks (INT). Also, insert two records into the table.
Code –
import [Link]
con = [Link](host="localhost",
user="root", password="1234", database="School")
cur = [Link]()
[Link]("CREATE TABLE IF NOT EXISTS
Student(RollNo INT PRIMARY KEY, Name
VARCHAR(30), Marks INT)")
[Link]("INSERT INTO Student VALUES (1, 'Amit',
85)")
[Link]("INSERT INTO Student VALUES (2, 'Priya',
92)")
[Link]()
print("Table created and records inserted
successfully!")
[Link]()
PROGRAM 16
Output –
Table created and records inserted
successfully!
PROGRAM 17
Q17. Consider the following DEPT and WORKER
tables. Write SQL queries for (i) to and (iv) find
outputs for SQL queries (v) to (viii):
Table: debt
Table: worker
i) To display Wno, Name, Gender from the table
WORKER in descending order of Wno.
ii) To display the Name of all the FEMALE workers
from the table WORKER.
iii) To display the Wno and Name of those workers
from the table WORKER who are born between
“1987-01-01” and “1991-12-01”.
iv) To count and display MALE workers who have
joined after “1986-01-01”
v) SELECT COUNT(*), DCODE FROM WORKER
GROUP BY DCODE HAVING COUNT(*) > 1;
vi) SELECT DISTINCT DEPARTMENT FROM DEPT;
vii) SELECT NAME , DEPARTMENT , CITY FROM
WORKER W, DEPT D WHERE [Link] =
[Link] AND WNO < 1003;
viii) SELECT MAX(DOJ), MIN(DOB) FROM
WORKER;
SQL Queries:
i) SELECT WNO, NAME, GENDER FROM
WORKER ORDER BY WNO Desc;
ii) SELECT NAME FROM WORKER WHERE
GENDER = ‘FEMALE’;
iii) SELECT WNO, NAME FROM WORKER
WHERE DOB BETWEEN ‘1987-01-01’ AND ‘1991-
12-01’;
iv) SELECT count(*) FROM WORKER WHERE
GENDER = ‘MALE’ AND DOJ > ‘1986-01-01’
Output:
v) (2,D01) , (2,D05)
vi) (MEDIA , MARKETING , INFRASTRUCTURE
FINANCE , HUMAN RESOURCE)
vii) (George K , MEDIA , DELHI) , (Ryma Sen ,
INFRASTRUCTURE , MUMBAI)
viii)Max(DOJ)(2014-06-09) , Min(DOB)(1984-10-19)