6. GROUP BY and find the min, max, sum, count and average.

 SQL

-- Find the minimum, maximum, sum, count and average age of students

SELECT MIN(age), MAX(age), SUM(age), COUNT(age), AVG(age) FROM Student;

 

-- Group students by age and find the count of students in each age group

SELECT age, COUNT(*) FROM Student GROUP BY age;

Integrating SQL with Python using the sqlite3 module:

Python

import sqlite3

 

# Connect to the database (create if it doesn't exist)

conn = sqlite3.connect('mydatabase.db')

cursor = conn.cursor()

 

# Create the table (if it doesn't already exist)

cursor.execute('''CREATE TABLE IF NOT EXISTS Student (

student_id INTEGER PRIMARY KEY,

name TEXT,

age INTEGER

                )''')

 

# Insert data

cursor.execute("INSERT INTO Student (student_id, name, age) VALUES (?, ?, ?)", (1, 'Alice', 20))

cursor.execute("INSERT INTO Student (student_id, name, age) VALUES (?, ?, ?)", (2, 'Bob', 21))

 

# Commit the changes

conn.commit()

 

# Fetch all rows

cursor.execute("SELECT * FROM Student")

rows = cursor.fetchall()

for row in rows:

print(row)

 

# Close the connection

conn.close()