Day 6 — Database & SQL | Java Internship

0–5 min
Real Hook

5–20 min
Core Concept

20–40 min
Live Coding

40–55 min
You Code It

55–60 min
Cheat Sheet

🔥 Real-World Hook (0–5 min)

Zomato stores 50 million orders. When you press "Reorder" it finds your last order from millions in under 100ms. That's SQL with the right indexes.

HDFC Bank processes 10 million transactions daily. Every credit, debit, and balance check is a SQL query. Without databases, apps have no memory. Today you give your app a brain.

📊 Database = Excel with Superpowers (5–20 min)

💡 Analogy

A Table is like an Excel sheet. Each row is one record. Each column is a field. A PRIMARY KEY is like a unique student ID — no duplicates ever. But unlike Excel, you query millions of rows in milliseconds.

💻 Live Coding — Student Database (20–40 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
SELECT * FROM students WHERE dept = 'CS' AND gpa > 3.5;
SELECT name, gpa FROM students ORDER BY gpa DESC LIMIT 3;

-- Update
UPDATE students SET gpa = 3.95 WHERE name = 'Priya';

-- Aggregate — stats dashboard
SELECT dept, COUNT(*) AS total, AVG(gpa) AS avg_gpa
FROM students
GROUP BY dept
ORDER BY avg_gpa DESC;

🔗 JOIN — Connecting Two Tables

SELECT s.name, c.title
FROM students s
INNER JOIN enrollments e ON s.id = e.student_id
INNER JOIN courses c     ON e.course_id = c.id
WHERE s.dept = 'CS';

🎯 You Code It (40–55 min)

Create the students table 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 the highest average GPA
Query 4: Students whose name contains the letter "a" (use LIKE)
Show: All 4 query results to instructor

📄 Day 6 Cheat Sheet — SQL

DDL

CREATE TABLE t (...)	New table
ALTER TABLE t ADD col type	Add column
DROP TABLE t	Delete table
PRIMARY KEY	Unique ID
NOT NULL	Required field
UNIQUE	No duplicates

DML

INSERT INTO t (cols) VALUES (vals)	Add row
SELECT * FROM t WHERE cond	Read rows
UPDATE t SET col=val WHERE cond	Edit row
DELETE FROM t WHERE cond	Remove
LIKE '%text%'	Pattern match

SELECT Clauses

WHERE col = val	Filter rows
ORDER BY col DESC	Sort
LIMIT 10	First 10
GROUP BY col	Group rows
HAVING COUNT(*) > 2	Filter groups
COUNT(*) AVG() MAX()	Aggregates

JOINs

INNER JOIN	Only matching rows
LEFT JOIN	All left + matching right
ON a.id = b.fk_id	Join condition
AS alias	Rename column/table
FOREIGN KEY	Reference another table

← PreviousDay 5: Advanced Java Next →Day 7: Spring Boot