Subject code:BTEA19621 Subject Name: Python and Sql for Data Science
Enrollment No: ET21BTCO082 Name: Heta Vimalesh Patel Date:15 /02/24
Lab Assignment 5
● Students:
Columns: student_id (Primary Key), name, age, gender
● Courses:
Columns: course_id (Primary Key), course_name, instructor
● Enrollments:
Columns: enrollment_id (Primary Key), student_id (Foreign Key references Students table),
course_id (Foreign Key references Courses table), grade
SCET/CO/2023-24/EVEN/BTECH Div-II/Sem-VI Page No:
Subject code:BTEA19621 Subject Name: Python and Sql for Data Science
Enrollment No: ET21BTCO082 Name: Heta Vimalesh Patel Date:15 /02/24
● STUDENTS TABLE
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="student",
database="CO_82"
)
mycursor = mydb.cursor()
create_table_query = """CREATE TABLE IF NOT EXISTS STUDENTS(
student_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30),
age INT,
gender VARCHAR(5)
)"""
insert_data_query = """INSERT INTO Students(name, age, gender) VALUES (%s,
%s, %s)"""
data = [('Alice',20,'F'),
('Bob',22,'M'),
('Charlie',21,'M'),
('Diana',23,'F')]
display_data_query = """SELECT * FROM STUDENTS"""
try:
mycursor.execute(create_table_query)
mydb.commit()
print("Table created successfully.")
mycursor.executemany(insert_data_query, data)
mydb.commit()
print("\n",mycursor.rowcount, "record(s) inserted.")
mycursor.execute(display_data_query)
result = mycursor.fetchall()
print("\nData in table 'Students' :\n")
for row in result:
print(row)
SCET/CO/2023-24/EVEN/BTECH Div-II/Sem-VI Page No:
Subject code:BTEA19621 Subject Name: Python and Sql for Data Science
Enrollment No: ET21BTCO082 Name: Heta Vimalesh Patel Date:15 /02/24
except mysql.connector.Error as err:
print("Error:", err)
finally:
if mydb.is_connected():
mycursor.close()
mydb.close()
print("\nMySQL connection closed.")
Output:-
● COURSES TABLE
import mysql.connector
mydb = mysql.connector.connect(
host="127.0.0.1",
user="root",
password="student",
database="CO_82"
)
create_table_query = """
CREATE TABLE IF NOT EXISTS courses (
course_id INT PRIMARY KEY,
couse_name VARCHAR(255),
instructor VARCHAR(255)
)
"""
insert_data_query = """
INSERT INTO courses (course_id, course_name, instructor ) VALUES (%s, %s, %s)
"""
SCET/CO/2023-24/EVEN/BTECH Div-II/Sem-VI Page No:
Subject code:BTEA19621 Subject Name: Python and Sql for Data Science
Enrollment No: ET21BTCO082 Name: Heta Vimalesh Patel Date:15 /02/24
data_to_insert = [
(101, 'Math', 'Prof.Smith'),
(102, 'Physics','Prof.Johnson'),
(103, 'Chemistry', 'Prof.White'),
]
display_data_query = """
SELECT * FROM courses
"""
try:
cursor = mydb.cursor()
cursor.execute(create_table_query)
mydb.commit()
print("Table created successfully.")
cursor.executemany(insert_data_query, data_to_insert)
mydb.commit()
print(cursor.rowcount, "record(s) inserted.")
cursor.execute(display_data_query)
result = cursor.fetchall()
print("Data in example_table:")
for row in result:
print(row)
except mysql.connector.Error as err:
print("Error:", err)
finally:
if mydb.is_connected():
cursor.close()
mydb.close()
print("MySQL connection closed.")
Output:-
SCET/CO/2023-24/EVEN/BTECH Div-II/Sem-VI Page No:
Subject code:BTEA19621 Subject Name: Python and Sql for Data Science
Enrollment No: ET21BTCO082 Name: Heta Vimalesh Patel Date:15 /02/24
● ENROLLMENTS TABLE
import mysql.connector
mydb = mysql.connector.connect(
host="127.0.0.1",
user="root",
password="student",
database="CO_82"
)
create_table_query = """
CREATE TABLE IF NOT EXISTS enrollments (
enrollment_id INT PRIMARY KEY,
id Int,
course_id Int,
FOREIGN KEY(id) REFERENCES students(id),
FOREIGN KEY(course_id) REFERENCES courses(course_id),
grade VARCHAR(255)
)
"""
insert_data_query = """
INSERT INTO enrollments (enrollment_id, id, course_id, grade ) VALUES (%s, %s, %s,
%s)
"""
data_to_insert = [
( 1, 1,101, 'A'),
( 2, 2,101, 'B'),
( 3, 3,102, 'A'),
( 4, 4,103, 'B'),
( 5, 1,103, 'C')
SCET/CO/2023-24/EVEN/BTECH Div-II/Sem-VI Page No:
Subject code:BTEA19621 Subject Name: Python and Sql for Data Science
Enrollment No: ET21BTCO082 Name: Heta Vimalesh Patel Date:15 /02/24
]
display_data_query = """
SELECT * FROM enrollments
"""
try:
cursor = mydb.cursor()
cursor.execute(create_table_query)
mydb.commit()
print("Table created successfully.")
cursor.executemany(insert_data_query, data_to_insert)
mydb.commit()
print(cursor.rowcount, "record(s) inserted.")
cursor.execute(display_data_query)
result = cursor.fetchall()
print("Data in example_table:")
for row in result:
print(row)
except mysql.connector.Error as err:
print("Error:", err)
finally:
if mydb.is_connected():
cursor.close()
mydb.close()
print("MySQL connection closed.")
Output:-
SCET/CO/2023-24/EVEN/BTECH Div-II/Sem-VI Page No:
Subject code:BTEA19621 Subject Name: Python and Sql for Data Science
Enrollment No: ET21BTCO082 Name: Heta Vimalesh Patel Date:15 /02/24
1. Retrieve the names of all students who have enrolled in the Math course.
Code:-
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="student",
database="CO_82"
)
mycursor = mydb.cursor()
query = """SELECT S.id, name FROM STUDENTS AS S JOIN enrollments AS
E ON S.id=E.id JOIN COURSES AS C ON E.course_id=C.course_id
WHERE C.course_name='Math';"""
try:
mycursor.execute(query)
result = mycursor.fetchall()
print("\nNames of all students who have enrolled in the Math course :\n")
for row in result:
print(row)
except mysql.connector.Error as err:
print("Error:", err)
finally:
if mydb.is_connected():
mycursor.close()
mydb.close()
print("\nMySQL connection closed.")
Output:-
SCET/CO/2023-24/EVEN/BTECH Div-II/Sem-VI Page No:
Subject code:BTEA19621 Subject Name: Python and Sql for Data Science
Enrollment No: ET21BTCO082 Name: Heta Vimalesh Patel Date:15 /02/24
2. Find the average age of students.
Code:-
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="student",
database="CO_82"
)
mycursor = mydb.cursor()
query = """SELECT AVG(age) FROM STUDENTS"""
try:
mycursor.execute(query)
result = mycursor.fetchall()
print("\nAverage age of students :\n")
for row in result:
print(row)
except mysql.connector.Error as err:
print("Error:", err)
finally:
if mydb.is_connected():
mycursor.close()
mydb.close()
print("\nMySQL connection closed.")
Output:-
SCET/CO/2023-24/EVEN/BTECH Div-II/Sem-VI Page No:
Subject code:BTEA19621 Subject Name: Python and Sql for Data Science
Enrollment No: ET21BTCO082 Name: Heta Vimalesh Patel Date:15 /02/24
3. List all courses along with the number of students enrolled in each course.
Code:-
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="student",
database="CO_82"
)
mycursor = mydb.cursor()
query = """SELECT C.course_id, course_name, COUNT(E.id) AS count FROM Courses
AS C JOIN Enrollments AS E ON C.course_id=E.course_id GROUP BY E.course_id"""
try:
mycursor.execute(query)
result = mycursor.fetchall()
print("\nList all courses along with the number of students enrolled in each course:\n")
for row in result:
print(row)
except mysql.connector.Error as err:
print("Error:", err)
finally:
if mydb.is_connected():
mycursor.close()
mydb.close()
print("\nMySQL connection closed.")
Output:-
SCET/CO/2023-24/EVEN/BTECH Div-II/Sem-VI Page No:
Subject code:BTEA19621 Subject Name: Python and Sql for Data Science
Enrollment No: ET21BTCO082 Name: Heta Vimalesh Patel Date:15 /02/24
4. Retrieve the names of students who have scored an A grade in any course.
Code:-
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="student",
database="CO_82"
)
mycursor = mydb.cursor()
query = """SELECT S.id,name FROM STUDENTS AS S JOIN Enrollments AS
E ON S.id=E.id WHERE E.grade='A'"""
try:
mycursor.execute(query)
result = mycursor.fetchall()
print("\nNames of students who have scored an A grade in any course:\n")
for row in result:
print(row)
except mysql.connector.Error as err:
print("Error:", err)
finally:
if mydb.is_connected():
mycursor.close()
mydb.close()
print("\nMySQL connection closed.")
Output:-
SCET/CO/2023-24/EVEN/BTECH Div-II/Sem-VI Page No:
Subject code:BTEA19621 Subject Name: Python and Sql for Data Science
Enrollment No: ET21BTCO082 Name: Heta Vimalesh Patel Date:15 /02/24
5. Update Diana's age to 24.
Code:-
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="student",
database="CO_82"
)
mycursor = mydb.cursor()
update_table_query = """UPDATE STUDENTS SET age='24' WHERE name='Diana'"""
display_table_query = """SELECT * FROM STUDENTS"""
try:
mycursor.execute(update_table_query)
result = mycursor.fetchall()
print("\nTable Updates Succesfully..!!!\n")
mycursor.execute(update_table_query)
mydb.commit()
print("Table updated successfully.")
mycursor.execute(display_table_query)
result = mycursor.fetchall()
print("\nData in table 'Students' :\n")
for row in result:
print(row)
except mysql.connector.Error as err:
print("Error:", err)
finally:
if mydb.is_connected():
mycursor.close()
mydb.close()
print("\nMySQL connection closed.")
Output:-
SCET/CO/2023-24/EVEN/BTECH Div-II/Sem-VI Page No:
Subject code:BTEA19621 Subject Name: Python and Sql for Data Science
Enrollment No: ET21BTCO082 Name: Heta Vimalesh Patel Date:15 /02/24
6. Delete the enrollment of the student with ID 2 from the Physics course.
Code:-
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="student",
database="CO_82"
)
mycursor = mydb.cursor()
delete_table_query = """DELETE FROM Enrollments WHERE id=2 AND
course_id=(SELECT course_id FROM Courses WHERE course_name='Physics')"""
display_table_query = """SELECT * FROM Enrollments"""
try:
mycursor.execute(delete_table_query)
mydb.commit()
print("Data deleted successfully.")
mycursor.execute(display_table_query)
result = mycursor.fetchall()
print("\nData in table 'Enrollments' :\n")
for row in result:
print(row)
SCET/CO/2023-24/EVEN/BTECH Div-II/Sem-VI Page No:
Subject code:BTEA19621 Subject Name: Python and Sql for Data Science
Enrollment No: ET21BTCO082 Name: Heta Vimalesh Patel Date:15 /02/24
except mysql.connector.Error as err:
print("Error:", err)
finally:
if mydb.is_connected():
mycursor.close()
mydb.close()
print("\nMySQL connection closed.")
Output:-
7. Find the course with the highest enrollment.
Code:-
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="student",
database="CO_82"
)
mycursor = mydb.cursor()
query = """SELECT course_id, course_name, enrollment_count FROM (SELECT
C.course_id, C.course_name, COUNT(E.id) AS enrollment_count, RANK()
OVER (ORDER BY COUNT(E.id) DESC) AS rnk FROM Courses C JOIN
Enrollments E ON C.course_id = E.course_id GROUP BY C.course_id,
C.course_name) ranked_courses WHERE rnk = 1;"""
SCET/CO/2023-24/EVEN/BTECH Div-II/Sem-VI Page No:
Subject code:BTEA19621 Subject Name: Python and Sql for Data Science
Enrollment No: ET21BTCO082 Name: Heta Vimalesh Patel Date:15 /02/24
try:
mycursor.execute(query)
result = mycursor.fetchall()
print("\nThe course with the highest enrollment :\n")
for row in result:
print(row)
except mysql.connector.Error as err:
print("Error:", err)
finally:
if mydb.is_connected():
mycursor.close()
mydb.close()
print("\nMySQL connection closed.")
Output:-
8. Retrieve the names of instructors who have courses with no enrollments.
Code:-
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="student",
database="CO_82"
)
mycursor = mydb.cursor()
query = """SELECT C.course_id, instructor, COUNT(E.id) AS count FROM
SCET/CO/2023-24/EVEN/BTECH Div-II/Sem-VI Page No:
Subject code:BTEA19621 Subject Name: Python and Sql for Data Science
Enrollment No: ET21BTCO082 Name: Heta Vimalesh Patel Date:15 /02/24
Courses AS C JOIN Enrollments AS E ON C.course_id=E.course_id GROUP BY
E.course_id HAVING COUNT(E.id)=0"""
try:
mycursor.execute(query)
result = mycursor.fetchall()
print("\nNames of instructors who have courses with no enrollments :\n")
for row in result:
print(row)
except mysql.connector.Error as err:
print("Error:", err)
finally:
if mydb.is_connected():
mycursor.close()
mydb.close()
print("\nMySQL connection closed.")
Output:-
9. Calculate the average grade for each course.
Code:-
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="student",
database="CO_82"
)
mycursor = mydb.cursor()
SCET/CO/2023-24/EVEN/BTECH Div-II/Sem-VI Page No:
Subject code:BTEA19621 Subject Name: Python and Sql for Data Science
Enrollment No: ET21BTCO082 Name: Heta Vimalesh Patel Date:15 /02/24
query = """ SELECT C.course_id,instructor, avg(E.grade) AS avgGrade FROM
Courses AS C JOIN Enrollments AS E ON C.course_id=E.course_id GROUP BY
E.course_id;"""
try:
mycursor.execute(query)
result = mycursor.fetchall()
print("\nAverage grade for each course:\n")
for row in result:
print(row)
except mysql.connector.Error as err:
print("Error:", err)
finally:
if mydb.is_connected():
mycursor.close()
mydb.close()
print("\nMySQL connection closed.")
Output:-
10. Retrieve the students who have enrolled in more than one course.
Code:-
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="student",
SCET/CO/2023-24/EVEN/BTECH Div-II/Sem-VI Page No:
Subject code:BTEA19621 Subject Name: Python and Sql for Data Science
Enrollment No: ET21BTCO082 Name: Heta Vimalesh Patel Date:15 /02/24
database="CO_82"
)
mycursor = mydb.cursor()
query = """SELECT S.id, name, COUNT(E.id) AS count FROM
STUDENTS AS S JOIN Enrollments AS E ON S.id=E.id GROUP BY
E.id HAVING COUNT(E.id)>1"""
try:
mycursor.execute(query)
result = mycursor.fetchall()
print("\nNames of students who have enrolled in more than one course :\n")
for row in result:
print(row)
except mysql.connector.Error as err:
print("Error:", err)
finally:
if mydb.is_connected():
mycursor.close()
mydb.close()
print("\nMySQL connection closed.")
Output:-
SCET/CO/2023-24/EVEN/BTECH Div-II/Sem-VI Page No:
