Day 6 · 1 Hour
Database & SQL
MySQL, CREATE TABLE, JOINs — give your app a memory that survives restarts and scales to millions.
🔥 Real-World Hook (0–5 min)
Zomato stores 50 million orders. When you press "Reorder" it finds your last order in under 100ms. Paytm processes 10 million payments daily — every one is a SQL query.
Without a database, your Python app loses all data the moment you close the terminal. Today you give your app a permanent memory — one that survives restarts, scales to millions of rows, and can answer complex questions in milliseconds.
📊 Database = Excel with Superpowers (5–25 min)
💡 Analogy
A Table is like an Excel sheet. Each row is one record. A PRIMARY KEY is like a unique Aadhaar number — no two rows can share it. But unlike Excel, you query millions of rows in milliseconds.
💻 Live Coding — Student Database (25–45 min)
-- Create the table CREATE TABLE students ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL, email VARCHAR(150) UNIQUE, dept VARCHAR(50), gpa DECIMAL(3,2) DEFAULT 0.0 ); -- Insert INSERT INTO students (name, email, dept, gpa) VALUES ("Priya", "priya@mail.com", "CS", 3.9), ("Ravi", "ravi@mail.com", "IT", 3.2), ("Anita", "anita@mail.com", "CS", 3.7); -- Read (most common) SELECT * FROM students WHERE dept = "CS" AND gpa > 3.5; SELECT name, gpa FROM students ORDER BY gpa DESC LIMIT 3; -- Aggregate SELECT dept, COUNT(*) AS total, AVG(gpa) AS avg_gpa FROM students GROUP BY dept ORDER BY avg_gpa DESC;
🐍 Python + MySQL with mysql-connector
import mysql.connector conn = mysql.connector.connect( host="localhost", user="root", password="yourpassword", database="internship" ) cursor = conn.cursor(dictionary=True) # returns dicts not tuples # Insert a student cursor.execute( "INSERT INTO students (name, email, dept, gpa) VALUES (%s, %s, %s, %s)", ("Meena", "meena@mail.com", "CS", 3.8) ) conn.commit() # Fetch all CS students cursor.execute("SELECT * FROM students WHERE dept = %s", ("CS",)) students = cursor.fetchall() for s in students: print(f"{s['name']} — GPA: {s['gpa']}") cursor.close() conn.close()
🎯 You Code It (45–55 min)
- Create the
studentstable in MySQL Workbench - Insert at least 8 students from different departments (CS, IT, EC, ME)
- Query 1: All students with GPA > 3.5, ordered by GPA descending
- Query 2: Count students per department
- Query 3: Department with highest average GPA
- Query 4: Students whose name contains the letter "a" (use LIKE '%a%')
- Bonus: Write the same queries using
mysql-connectorin Python
📄 Day 6 Cheat Sheet — SQL + Python
DDL
| CREATE TABLE t (...) | New table |
| PRIMARY KEY AUTO_INCREMENT | Auto ID |
| NOT NULL | Required |
| UNIQUE | No duplicates |
| DEFAULT value | If not provided |
DML
| INSERT INTO t VALUES (v) | Add row |
| SELECT * FROM t WHERE c | Read |
| UPDATE t SET col=v WHERE c | Edit |
| DELETE FROM t WHERE c | Remove |
| LIKE '%text%' | Pattern match |
SELECT Clauses
| ORDER BY col DESC | Sort |
| LIMIT 10 | First 10 rows |
| GROUP BY col | Group rows |
| HAVING COUNT(*) > 2 | Filter groups |
| COUNT(*) AVG() MAX() | Aggregates |
Python mysql-connector
| mysql.connector.connect(...) | Connect |
| conn.cursor(dictionary=True) | Dict cursor |
| cursor.execute(sql, (val,)) | Run query |
| cursor.fetchall() | All rows |
| conn.commit() | Save changes |
| cursor.close() | Close cursor |