SQL for Class 12: Build Tables, Ask Questions, Join Tables

DDL and DML, and data types

• DDL (Data Definition Language) defines structure: CREATE, ALTER, DROP.
• DML (Data Manipulation Language) works on rows: INSERT, UPDATE, DELETE, and SELECT (querying is often grouped here).

Common MySQL data types

• CHAR(n): fixed length text; always uses n characters (good for PIN codes).
• VARCHAR(n): variable length text up to n; uses only what it needs (good for names).
• INT: whole numbers.
• FLOAT / DECIMAL(p,s): numbers with decimals.
• DATE: 'YYYY-MM-DD'.

SQL keywords are not case-sensitive; each statement ends with a semicolon. Text and dates go in quotes.

Constraints

A constraint is a rule on a column that stops wrong data.

• NOT NULL: the column must have a value.
• UNIQUE: no two rows can have the same value.
• PRIMARY KEY: unique and not null; identifies each row.
• DEFAULT: a value used when none is given.
• FOREIGN KEY: value must exist in the referenced table's primary key.

Working with databases and tables

CREATE DATABASE school;
SHOW DATABASES;
USE school;
CREATE TABLE student (
  RollNo INT PRIMARY KEY,
  Name VARCHAR(20) NOT NULL,
  Class CHAR(3),
  City VARCHAR(15) DEFAULT 'Delhi',
  Marks INT
);
SHOW TABLES;
DESCRIBE student;      -- or DESC student;

ALTER TABLE

ALTER TABLE student ADD Phone CHAR(10);          -- add a column
ALTER TABLE student MODIFY Name VARCHAR(30);     -- change type/size
ALTER TABLE student DROP Phone;                  -- remove a column
ALTER TABLE student ADD PRIMARY KEY (RollNo);    -- add a key (if none)
ALTER TABLE student DROP PRIMARY KEY;            -- remove the key

DROP

DROP TABLE student; removes the table and all its data. DROP DATABASE school; removes the whole database.

Foreign key example: CREATE TABLE marks (ExamID INT PRIMARY KEY, RollNo INT, Score INT, FOREIGN KEY (RollNo) REFERENCES student(RollNo));

INSERT, UPDATE, DELETE

INSERT INTO student VALUES (1, 'Asha', '12A', 'Delhi', 92);
INSERT INTO student (RollNo, Name, Marks) VALUES (2, 'Ravi', 75);  -- City gets DEFAULT, Class NULL
UPDATE student SET Marks = Marks + 5 WHERE Marks < 60;
DELETE FROM student WHERE RollNo = 2;

Without WHERE, UPDATE and DELETE act on every row. DELETE removes rows but keeps the table; DROP removes the table itself.

SELECT: operators, aliases, DISTINCT, WHERE, IN, BETWEEN, LIKE, IS NULL

SELECT * FROM student;
SELECT Name, Marks FROM student;
SELECT Name, Marks + 5 AS NewMarks FROM student;   -- arithmetic + alias
SELECT DISTINCT City FROM student;                  -- no repeats

• Operators: arithmetic + - * / %; relational = < > <= >= <> (or !=); logical AND, OR, NOT.
• Alias: a temporary name for a column or table with AS: SELECT Name AS Student FROM student S;
• WHERE Marks BETWEEN 60 AND 80 includes both ends.
• WHERE City IN ('Delhi', 'Pune') means City is any value in the list; NOT IN is the opposite.
• WHERE Name LIKE 'A%': starts with A. '%a': ends with a. '%sh%': contains sh. '_a%': second letter is a. '____': exactly 4 letters.
• WHERE Class IS NULL / IS NOT NULL (never = NULL).

ORDER BY

SELECT * FROM student ORDER BY Marks DESC;
SELECT * FROM student ORDER BY City, Name;   -- by City, then Name inside each city

ASC (ascending) is the default; DESC sorts high to low.

Aggregate functions

Aggregate (group) functions work on many rows and return one value.

• COUNT(*): number of rows. COUNT(col): number of non-NULL values in col. COUNT(DISTINCT col): number of different values.
• SUM(col), AVG(col): total and average of numbers.
• MAX(col), MIN(col): highest and lowest (work on text and dates too).

All except COUNT(*) ignore NULL values. Example with Marks 92, 75, 58, NULL: COUNT(*) = 4, COUNT(Marks) = 3, SUM = 225, AVG = 75.

GROUP BY and HAVING

GROUP BY puts rows with the same value in a column into one group, and aggregates run once per group.

SELECT City, COUNT(*), AVG(Marks)
FROM student
GROUP BY City;

HAVING filters groups using an aggregate condition:

SELECT City, COUNT(*) FROM student
GROUP BY City
HAVING COUNT(*) >= 2;

WHERE vs HAVING: WHERE filters rows before grouping and cannot use aggregates; HAVING filters groups after grouping. Order of clauses: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY. Columns in SELECT that are not aggregated should be in GROUP BY.

Joins: Cartesian product, equi-join, natural join

Tables: STUDENT(RollNo, Name) with 3 rows and MARKS(RollNo, Score) with 2 rows.

Cartesian product

SELECT * FROM student, marks;

Every row of the first table is paired with every row of the second: 3 × 2 = 6 rows. Degree = 2 + 2 = 4 columns. Most pairs are meaningless.

Equi-join

SELECT * FROM student S, marks M
WHERE S.RollNo = M.RollNo;

Keeps only pairs where the common column is equal. RollNo appears twice (S.RollNo and M.RollNo). Use table aliases (S, M) and write table.column when a column name is in both tables. JOIN … ON gives the same result: SELECT * FROM student JOIN marks ON student.RollNo = marks.RollNo;

Natural join

SELECT * FROM student NATURAL JOIN marks;

Joins on all columns with the same name and shows the common column only once.

Try it: your class in SQL

Install MySQL or use any online SQL playground (for example one that runs SQLite in the browser). Create STUDENT with 8 friends, their city and marks. Answer with one query each: toppers above 80; names starting with 'S'; average marks per city; cities with more than 2 students; all students sorted by marks high to low. Before running each, predict the answer. Then compare with the 3D free-play step.

Key formulas and definitions

• SELECT cols FROM table WHERE row-condition GROUP BY col HAVING group-condition ORDER BY col [DESC];
• LIKE: % = any number of characters, _ = exactly one
• COUNT(*) counts rows; COUNT(col), SUM, AVG, MAX, MIN ignore NULL
• Cartesian product: rows multiply, columns add · Equi-join: WHERE A.k = B.k · Natural join: common column once

Worked examples

Common mistakes

• Writing WHERE City = NULL instead of WHERE City IS NULL.
• Using an aggregate in WHERE (WHERE COUNT(*) > 2); use HAVING after GROUP BY.
• Forgetting WHERE in UPDATE or DELETE and changing every row.
• Writing an ambiguous column like RollNo in a join without the table name or alias.