import mysql.connector

connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='theaakash'
)

if connection.is_connected():
    print('Connected to MySQL')
    cursor = connection.cursor()




========================================================


cursor.execute('SHOW DATABASES')
databases = cursor.fetchall()
print('Databases:')
for db in databases:
    print(db[0])


================================================================


cursor.execute('CREATE DATABASE IF NOT EXISTS dbStudent')
connection.database = 'dbStudent'
print('Connected to dbStudent')



=======================================================================


cursor.execute('''
    CREATE TABLE IF NOT EXISTS tblStudent (
        rollno INT PRIMARY KEY,
        name VARCHAR(255),
        birthdate DATE,
        gender VARCHAR(10),
        cmarks INT,
        cppmarks INT,
        pythonmarks INT,
        totalmarks INT,
        percentage DECIMAL(5, 2),
        grade CHAR(1)
    )
''')
print('tblStudent table created')




================================================================


data = [
    (1, 'Alice', '2000-01-01', 'F', 90, 85, 95),
]
cursor.executemany('''
    INSERT INTO tblStudent (rollno, name, birthdate, gender, cmarks, cppmarks, pythonmarks)
    VALUES (%s, %s, %s, %s, %s, %s, %s)
''', data)
connection.commit()
print('Rows inserted into tblStudent')



=====================================================================



cursor.execute('''
    UPDATE tblStudent
    SET totalmarks = cmarks + cppmarks + pythonmarks
''')
connection.commit()
print('Total marks computed and updated for all rows')




==========================================================================



cursor.execute('''
    UPDATE tblStudent
    SET percentage = (totalmarks / 300) * 100
''')
connection.commit()
print('Percentage computed and updated for all rows')



============================================================================



cursor.execute('''
    UPDATE tblStudent
    SET grade = CASE
        WHEN percentage >= 90 THEN 'A'
        WHEN percentage >= 80 THEN 'B'
        WHEN percentage >= 70 THEN 'C'
        WHEN percentage >= 60 THEN 'D'
        ELSE 'F'
    END
''')
connection.commit()
print('Grade computed and updated for all rows')





===============================================================================


cursor.execute('SELECT rollno, name, percentage, grade FROM tblStudent')
rows = cursor.fetchall()
for row in rows:
    print(row)



=================================================================================


cursor.execute('''
    SELECT rollno, name, percentage, grade
    FROM tblStudent
    ORDER BY percentage DESC
''')
merit_list = cursor.fetchall()
for i in range(len(merit_list)):
    student = merit_list[i]
    print(f'{i+1}. {student[1]} - {student[2]}% - Grade: {student[3]}')



====================================================================================


cursor.execute('''
    SELECT rollno, name, percentage, grade
    FROM tblStudent
    ORDER BY percentage DESC
    LIMIT 2
''')
toppers = cursor.fetchall()

for i in range(len(toppers)):
    student = toppers[i]
    print(f'{i+1}. {student[1]} - {student[2]}% - Grade: {student[3]}')



===================================================================================


cursor.execute('''
    SELECT rollno, name, gender, grade
    FROM tblStudent
    WHERE gender = 'F'
    AND (cmarks < 40 OR cppmarks < 40 OR pythonmarks < 40)
    HAVING SUM(cmarks < 40) + SUM(cppmarks < 40) + SUM(pythonmarks < 40) >= 2
''')
failed_girls = cursor.fetchall()
for student in failed_girls:
    print(f'{student[1]} has failed in at least two subjects')

====================================================================================


cursor.execute('''
    SELECT rollno, name, birthdate, percentage, grade
    FROM tblStudent
    WHERE gender = 'F'
    ORDER BY birthdate DESC
    LIMIT 1
''')
youngest_female_topper = cursor.fetchone()
print(youngest_female_topper)


=======================================================================================


cursor.execute('''
    SELECT rollno, name, birthdate, percentage, grade
    FROM tblStudent
    WHERE gender = 'M'
    ORDER BY birthdate ASC
    LIMIT 1
''')
oldest_male_topper = cursor.fetchone()
print(oldest_male_topper)



==========================================================================================


cursor.execute('''
    UPDATE tblStudent
    SET birthdate = DATE_ADD(birthdate, INTERVAL 1 MONTH)
    WHERE MONTH(birthdate) = 12
''')
connection.commit()
print('Done')



============================================================================================

cursor.execute('''
    UPDATE tblStudent
    SET birthdate = '2000-01-01'
    WHERE rollno = <topper_rollno>
''')
connection.commit()
print('Done')


===============================================================================================


cursor.execute('''
    SELECT rollno
    FROM tblStudent
    ORDER BY percentage DESC
    LIMIT 1
''')
topper_rollno = cursor.fetchone()[0]

# Update the birthdate of the topper student
cursor.execute('''
    UPDATE tblStudent
    SET birthdate = '2000-01-01'
    WHERE rollno = %s
''', (topper_rollno,))
connection.commit()
print('Done')



===================================================================================================


cursor.execute('''
    SELECT rollno, name, birthdate
    FROM tblStudent
    WHERE YEAR(birthdate) = 2024
    AND birthdate > CURDATE()
''')
upcoming_birthdays = cursor.fetchall()
for student in upcoming_birthdays:
    print(student)



=====================================================================================================



cursor.execute('''
    CREATE PROCEDURE InsertStudent(
        IN p_rollno INT,
        IN p_name VARCHAR(255),
        IN p_birthdate DATE,
        IN p_gender VARCHAR(10),
        IN p_cmarks INT,
        IN p_cppmarks INT,
        IN p_pythonmarks INT
    )
    BEGIN
        INSERT INTO tblStudent (rollno, name, birthdate, gender, cmarks, cppmarks, pythonmarks)
        VALUES (p_rollno, p_name, p_birthdate, p_gender, p_cmarks, p_cppmarks, p_pythonmarks);
    END
''')
print('Stored procedure InsertStudent created')




=========================================================================================================

cursor.execute('''
    CREATE PROCEDURE GetFemaleStudents()
    BEGIN
        SELECT *
        FROM tblStudent
        WHERE gender = 'F';
    END
''')
print('Stored procedure GetFemaleStudents created')



=========================================================================================================

cursor.execute('''
    CREATE PROCEDURE GetStudentsByGrade(
        IN p_grade CHAR(1)
    )
    BEGIN
        SELECT *
        FROM tblStudent
        WHERE grade = p_grade;
    END
''')
print('Stored procedure GetStudentsByGrade created')



cursor.callproc('GetStudentsByGrade', ('A',))
result = cursor.stored_results()
for row in result:
    print(row)