🖥️ MySQL SQL Revision Tour

In Class 11 you learnt the basics of databases and SQL. This chapter is a revision tour of that material — the relational data model, creating tables with constraints, and inserting, querying, updating and deleting data, then altering and dropping tables — before the advanced topics that follow, such as Joins and Python connectivity.

5.1 🌟 Introduction

Let's refresh the big picture before diving into commands:

graph TD
    DB["🗄️ Database\nOrganised collection\nof related data"]
    DBMS["⚙️ DBMS\nSoftware to manage\ndatabases"]
    RDBMS["🔗 RDBMS\nDBMS that stores data\nin TABLES"]
    SQL["🗣️ SQL\nLanguage to talk\nto RDBMS"]
    MYSQL["🐬 MySQL\nA popular RDBMS\nsoftware"]

    DB --> DBMS --> RDBMS --> SQL
    RDBMS --> MYSQL

    style DBMS fill:#2196F3,color:#fff
    style RDBMS fill:#9C27B0,color:#fff
    style MYSQL fill:#4CAF50,color:#fff

5.2 📐 Relational Data Model

The Relational Model organises all data into simple 2D tables — rows and columns, just like an Excel sheet!

     RollNo    Name      Marks
     ------    ----      -----
       1       Amit       85      ← This entire row = ONE Tuple
       2       Neha       90
       3       Raj        78

     ↑
  This column = ONE Attribute

Key Terminology — Memorise These! 🧠

graph LR
    T["Table = Relation"]
    R["Row = Tuple"]
    C["Column = Attribute"]

    style T fill:#FF9800,color:#fff
    style R fill:#2196F3,color:#fff
    style C fill:#4CAF50,color:#fff

The Key Family — Primary, Candidate, Alternate, Foreign 🔑

Analogy: Imagine choosing a team captain (Primary Key) from a group of eligible players (Candidate Keys). The ones NOT chosen are still "eligible" — they're your Alternate Keys!

5.3 🐬 MySQL — A Popular RDBMS

MySQL is free, open-source RDBMS software used worldwide — from small school projects to giant companies!

5.3.1 MySQL and SQL 🗣️

Important distinction: MySQL is the software (the engine). SQL is the language you use to talk to it (the steering wheel).

graph LR
    YOU["👤 You type\nSQL commands"]
    MYSQL["🐬 MySQL Server\n(understands SQL,\nstores your data)"]
    RESULT["📊 Result\n(your data,\nback to you)"]

    YOU -->|"SQL"| MYSQL --> RESULT

    style MYSQL fill:#4CAF50,color:#fff

5.3.2 Common MySQL Data Types 📦

Every column needs a data type — this decides what kind of value it can hold.

5.4 🔓 Accessing Database in MySQL

Before creating any table, you need to CREATE and SELECT a database:

-- accessing_database.sql
CREATE DATABASE SchoolDB;    -- Create a brand-new database

USE SchoolDB;                -- Select it for use (MUST do this before creating tables!)

SHOW DATABASES;              -- See all databases on the server

5.5 🏗️ Creating Tables in MySQL

The CREATE TABLE command builds a new table with named columns and data types.

-- create_table.sql
CREATE TABLE Student (
    RollNo   INT,
    Name     VARCHAR(30),
    Stream   VARCHAR(20),
    Fee      DECIMAL(10,2)
);

General Syntax:

CREATE TABLE table_name (
    column1_name  datatype,
    column2_name  datatype,
    ...
);

CREATE TABLE Student (
    RollNo INT,
    Name   VARCHAR(30),
    Marks  DECIMAL(5,2)
);

5.6 📥 Inserting Data into Table

The INSERT INTO command adds new rows (records) to a table.

Method 1 — All Columns (must match table order EXACTLY):

-- insert_all_columns.sql
INSERT INTO Student VALUES (1, 'Rohan', 'Science', 2500.00);

Method 2 — Specific Columns (Recommended! ⭐):

-- insert_specific_columns.sql
INSERT INTO Student (RollNo, Name) VALUES (2, 'Priya');
-- Other columns (Stream, Fee) become NULL automatically

Inserting Multiple Rows at Once:

-- insert_multiple.sql
INSERT INTO Student VALUES
    (3, 'Karan', 'Commerce', 2200.00),
    (4, 'Sita',  'Science',  2800.00),
    (5, 'Meera', 'Arts',     1900.00);

5.7 🔍 Making Simple Queries through SELECT Command

SELECT is the most-used SQL command — it retrieves (fetches) data from tables.

Our reference Student table for ALL examples below:

5.7.1 Selecting All Data ⭐

-- select_all.sql
SELECT * FROM Student;

The * (asterisk) means "give me ALL columns". This shows the entire table.

5.7.2 Selecting Particular Rows 🎯

Use the WHERE clause to filter and show only specific rows that match a condition.

-- select_particular_rows.sql
SELECT * FROM Student WHERE Stream = 'Science';

Output:

RollNo  Name    Stream    Fee
1       Rohan   Science   2500.00
2       Priya   Science   NULL
4       Sita    Science   2800.00

-- Another example:
SELECT * FROM Student WHERE Fee > 2000;

Only rows that SATISFY the condition are shown — this is called filtering!

5.7.3 Selecting Particular Columns 📋

Instead of * (all columns), name only the columns you want:

-- select_particular_columns.sql
SELECT Name, Fee FROM Student;

Output:

Name    Fee
Rohan   2500.00
Priya   NULL
Karan   2200.00
Sita    2800.00
Meera   1900.00

Combine columns AND rows together:

SELECT Name, Fee FROM Student WHERE Stream = 'Science';

5.7.4 Eliminating Redundant Data — DISTINCT Keyword 🧹

DISTINCT removes duplicate values, showing each unique value only once.

-- distinct_demo.sql
SELECT DISTINCT Stream FROM Student;

Output:

Stream
Science
Commerce
Arts

Even though "Science" appears 3 times in the table, DISTINCT shows it only ONCE!

5.7.5 Selecting from all the Rows — ALL Keyword 📊

ALL is the OPPOSITE of DISTINCT — and it's actually the DEFAULT behaviour (you rarely type it explicitly).

-- all_keyword_demo.sql
SELECT ALL Stream FROM Student;
-- Same as: SELECT Stream FROM Student;
-- Shows Science, Science, Commerce, Science, Arts (ALL rows, WITH duplicates)

5.7.6 Viewing Structure of a Table 🏗️

DESCRIBE (or DESC) shows the table's STRUCTURE (column names, data types) — NOT the actual data!

-- describe_demo.sql
DESCRIBE Student;
-- OR:
DESC Student;

Output:

Field    Type           Null   Key   Default   Extra
RollNo   int            YES
Name     varchar(30)    YES
Stream   varchar(20)    YES
Fee      decimal(10,2)  YES

5.7.7 Performing Simple Calculations ➕

SQL can do math directly! You can calculate values right inside a SELECT statement.

-- simple_calculations.sql
SELECT 25 + 10;          -- Output: 35
SELECT 100 / 4;          -- Output: 25.0000
SELECT 6 * 7;            -- Output: 42

Calculations using actual table columns:

-- calculation_on_column.sql
SELECT Name, Fee * 12 FROM Student;   -- Annual Fee = Monthly Fee × 12

Output:

Name    Fee*12
Rohan   30000.00
Priya   NULL          ← NULL × 12 = NULL (any math with NULL = NULL!)
Karan   26400.00
Sita    33600.00
Meera   22800.00

5.7.8 Using Column Aliases 🏷️

An Alias gives a column a temporary, nicer name in the output — using the AS keyword.

-- column_alias_demo.sql
SELECT Name, Fee * 12 AS AnnualFee FROM Student;

Output:

Name    AnnualFee
Rohan   30000.00
Karan   26400.00

The alias AnnualFee is much more readable than the messy default heading "Fee*12"! Remember: the alias is TEMPORARY — it doesn't rename anything in the actual table.

5.7.9 Condition Based on a Range — BETWEEN 📏

BETWEEN checks if a value falls within a range — INCLUSIVE of both ends!

-- between_demo.sql
SELECT * FROM Student WHERE Fee BETWEEN 2000 AND 2800;

This is exactly the same as writing: WHERE Fee >= 2000 AND Fee <= 2800

Output:

RollNo  Name    Stream    Fee
1       Rohan   Science   2500.00
3       Karan   Commerce  2200.00
4       Sita    Science   2800.00     ← 2800 IS included!

5.7.10 Condition Based on a List — IN 📋

IN checks if a value matches ANY value from a given list — a shortcut for multiple OR conditions.

-- in_demo.sql
SELECT * FROM Student WHERE Stream IN ('Science', 'Commerce');
-- Same as: WHERE Stream = 'Science' OR Stream = 'Commerce'

Output:

RollNo  Name    Stream     Fee
1       Rohan   Science    2500.00
2       Priya   Science    NULL
3       Karan   Commerce   2200.00
4       Sita    Science    2800.00

Meera (Arts) is excluded — 'Arts' is not in our list!

5.7.11 Condition Based on Pattern Matches — LIKE 🔎

LIKE searches for a text PATTERN using wildcard symbols.

-- like_demo.sql

-- Names starting with 'R':
SELECT * FROM Student WHERE Name LIKE 'R%';

-- Names where 2nd letter is 'a':
SELECT * FROM Student WHERE Name LIKE '_a%';

-- Names that are EXACTLY 5 characters long:
SELECT * FROM Student WHERE Name LIKE '_____';   -- 5 underscores!

-- Names ending in 'a':
SELECT * FROM Student WHERE Name LIKE '%a';

5.7.12 Searching for NULL 🕳️

Since NULL means "unknown", you CANNOT use = to find it! You must use special keywords.

-- null_search_demo.sql

-- WRONG way (returns NOTHING, even if NULLs exist!):
SELECT * FROM Student WHERE Fee = NULL;    ❌

-- CORRECT way:
SELECT * FROM Student WHERE Fee IS NULL;       ✅
SELECT * FROM Student WHERE Fee IS NOT NULL;   ✅

Output of WHERE Fee IS NULL:

RollNo  Name    Stream    Fee
2       Priya   Science   NULL

5.8 🔒 Creating Tables with SQL Constraints

5.8.1 SQL Constraints — What Are They? 🛡️

Constraints are RULES applied to columns that prevent bad or invalid data from being stored. Think of them as "quality control" for your table!

graph TD
    C["🔒 SQL CONSTRAINTS"]
    C1["NOT NULL\nMust have a value"]
    C2["UNIQUE\nNo duplicate values"]
    C3["PRIMARY KEY\nNOT NULL + UNIQUE\n(one per table)"]
    C4["FOREIGN KEY\nMust match another\ntable's Primary Key"]
    C5["CHECK\nMust satisfy a\ncondition"]
    C6["DEFAULT\nAuto-fills a value\nif none given"]

    C --> C1
    C --> C2
    C --> C3
    C --> C4
    C --> C5
    C --> C6

    style C fill:#9C27B0,color:#fff

All Six Constraints Explained Simply:

Constraints Quick Reference:

5.8.2 Applying Table Constraints 🛠️

-- table_with_constraints.sql
CREATE TABLE Student (
    RollNo   INT PRIMARY KEY,                       -- Unique ID, can't be NULL
    Name     VARCHAR(30) NOT NULL,                  -- Cannot be blank
    Email    VARCHAR(50) UNIQUE,                     -- No duplicates
    Stream   VARCHAR(20) DEFAULT 'Science',          -- Auto-fills if skipped
    Fee      DECIMAL(10,2) CHECK (Fee > 0),          -- Must be positive!
    DeptId   INT,
    FOREIGN KEY (DeptId) REFERENCES Department(DeptId)   -- Links to another table
);

Testing the constraints:

-- Valid insert (follows all rules):
INSERT INTO Student (RollNo, Name, Fee) VALUES (1, 'Rohan', 2500);
-- Stream automatically becomes 'Science' (DEFAULT)

-- INVALID insert (violates CHECK — negative fee!):
INSERT INTO Student (RollNo, Name, Fee) VALUES (2, 'Priya', -500);
-- ❌ ERROR! CHECK constraint failed.

-- INVALID insert (violates NOT NULL):
INSERT INTO Student (RollNo, Fee) VALUES (3, 2000);
-- ❌ ERROR! Name cannot be NULL.

CREATE TABLE Student (
    RollNo  INT PRIMARY KEY,
    Name    VARCHAR(30) NOT NULL,
    Fee     DECIMAL(10,2) CHECK (Fee > 0)
);

5.9 🔍 Viewing a Table Structure

We touched on this in 5.7.6, but let's cement it — DESCRIBE shows structure, NOT data:

-- viewing_structure.sql
DESCRIBE Student;

Output:

Field    Type            Null   Key    Default    Extra
RollNo   int             NO     PRI
Name     varchar(30)     NO
Email    varchar(50)     YES    UNI
Stream   varchar(20)     YES               Science
Fee      decimal(10,2)   YES

What each column of the output means:

5.10 🔄 Inserting Data into another Table

You can copy matching data from one table into another table, using INSERT INTO ... SELECT:

-- insert_into_another_table.sql

-- First, create a table for toppers:
CREATE TABLE Toppers (
    Name  VARCHAR(30),
    Fee   DECIMAL(10,2)
);

-- Copy data from Student table where Fee > 2500:
INSERT INTO Toppers (Name, Fee)
SELECT Name, Fee FROM Student WHERE Fee > 2500;

What happens:

graph LR
    STU["📋 Student Table\n(All Students)"]
    FILTER["WHERE Fee > 2500"]
    TOP["📋 Toppers Table\n(Only matching rows\ncopied here!)"]

    STU --> FILTER --> TOP

    style FILTER fill:#FF9800,color:#fff
    style TOP fill:#4CAF50,color:#fff

Very useful for creating backups, archiving old data, or splitting data into categories — like moving "Toppers" into their own table!

5.11 ✏️ Modifying Data in Tables

The UPDATE command changes existing data in a table.

-- update_demo.sql
UPDATE Student
SET Fee = Fee + 500
WHERE Stream = 'Commerce';

More Examples:

-- Update a single row:
UPDATE Student SET Fee = 3000 WHERE RollNo = 1;

-- Update multiple columns at once:
UPDATE Student SET Fee = 2600, Stream = 'Arts' WHERE RollNo = 2;

-- Give everyone in Science a 10% raise:
UPDATE Student SET Fee = Fee * 1.10 WHERE Stream = 'Science';

5.12 🗑️ Deleting Data from Tables

The DELETE command removes rows from a table.

-- delete_demo.sql

-- Delete ONE specific row:
DELETE FROM Student WHERE RollNo = 5;

-- Delete MULTIPLE matching rows:
DELETE FROM Student WHERE Stream = 'Arts';

-- Delete ALL rows (table becomes empty, but structure remains!):
DELETE FROM Student;

5.13 🔧 Altering Tables

ALTER TABLE changes the STRUCTURE of a table (adding/removing/modifying columns) — NOT the data inside it!

graph TD
    ALTER["🔧 ALTER TABLE"]
    ADD["ADD\nAdd a new column"]
    MODIFY["MODIFY\nChange a column's\ndata type/size"]
    DROP["DROP COLUMN\nRemove a column"]

    ALTER --> ADD
    ALTER --> MODIFY
    ALTER --> DROP

    style ALTER fill:#FF9800,color:#fff

Adding a new column:

-- alter_add.sql
ALTER TABLE Student ADD Email VARCHAR(50);

Changing a column's data type/size:

-- alter_modify.sql
ALTER TABLE Student MODIFY Name VARCHAR(50);
-- Expands Name from VARCHAR(30) to VARCHAR(50)

Removing a column:

-- alter_drop_column.sql
ALTER TABLE Student DROP COLUMN Email;
-- Email column AND its data are permanently gone!

Renaming a column (bonus!):

-- alter_change.sql
ALTER TABLE Student CHANGE Fee Tuition DECIMAL(10,2);
-- Renames 'Fee' to 'Tuition'

5.14 💣 Dropping Tables

DROP TABLE permanently and completely destroys a table — structure AND data — with no way to undo it!

-- drop_table_demo.sql
DROP TABLE Student;
-- Student table is COMPLETELY GONE. Structure gone. Data gone. Forever.

graph LR
    BEFORE["📋 Student Table\nStructure + All Data"]
    DROP["💣 DROP TABLE Student;"]
    AFTER["❌ COMPLETELY GONE\nNo Recycle Bin\nNo Undo!"]

    BEFORE --> DROP --> AFTER

    style DROP fill:#F44336,color:#fff
    style AFTER fill:#9E9E9E,color:#fff

DELETE vs DROP — The Ultimate Comparison Table!

⚠️ Common Errors and Misconceptions

CREATE DATABASE name;    USE name;    DROP DATABASE name;

CREATE TABLE name (col type constraint, ...);
DESCRIBE name;    -- or DESC name; (view structure)
ALTER TABLE name ADD/MODIFY/DROP COLUMN ...;
DROP TABLE name;  -- permanently destroys table

INSERT INTO name VALUES (...);
UPDATE name SET col=value WHERE condition;
DELETE FROM name WHERE condition;

🎯 Sample Board Exam Questions

Q1: Very Short Answer [1 mark each]

a) What is the full form of DBMS? → Database Management System

b) Which command is used to view a table's structure? → DESCRIBE (or DESC)

c) Which keyword removes duplicate values in a SELECT query? → DISTINCT

d) What is the correct format for a DATE value in MySQL? → 'YYYY-MM-DD'

e) Which constraint ensures a column can never contain duplicate values? → UNIQUE

Q2: Short Answer [2 marks]

Q: Differentiate between DELETE and DROP.

DELETE is a DML command that removes rows (data) from a table using a WHERE clause; the table itself continues to exist (empty if all rows deleted). DROP is a DDL command that removes the ENTIRE table — both structure and data — permanently, with no WHERE clause option and no way to undo it.

Q3: Practical SQL [4 marks]

Q: Write SQL statements to: a) Create a table Employee with EmpId (Primary Key), Name (cannot be blank), and Salary (must be positive). b) Insert a record. c) Display employees earning more than 30000, sorted... (display all fields). d) Increase salary of all employees by 10%.

-- a) Create table
CREATE TABLE Employee (
    EmpId   INT PRIMARY KEY,
    Name    VARCHAR(30) NOT NULL,
    Salary  DECIMAL(10,2) CHECK (Salary > 0)
);

-- b) Insert record
INSERT INTO Employee VALUES (1, 'Aman', 35000);

-- c) Display employees earning > 30000
SELECT * FROM Employee WHERE Salary > 30000;

-- d) Increase salary by 10%
UPDATE Employee SET Salary = Salary * 1.10;

Q4: Output Based [2 marks]

Q: What is the output?

SELECT Name FROM Student WHERE Fee IS NULL;

(Given: Priya has NULL fee)

Output:

Name
Priya

✏️ Practice Problems

1. Write SQL to create a Book table with BookId (Primary Key), Title (cannot be blank), Price (must be greater than 0), and Genre (default value 'Fiction').

2. Insert 3 records into the Book table, one with a missing Price (NULL).

3. Write a query to display all books priced between ₹200 and ₹500.

4. Write a query to display book titles that start with the letter 'H'.

5. Write a query to find books where Genre is either 'Fiction' or 'Mystery' using the IN operator.

6. Write SQL to add a new column PublishYear to the Book table.

7. Write a query to display book titles where Price is NULL.

8. Explain the difference between CHAR(10) and VARCHAR(10) with a real-world example.

9. Write SQL to copy all books priced above ₹500 into a new table called PremiumBooks.

10. What will happen if you run DELETE FROM Book; followed by SELECT * FROM Book;? What if you instead ran DROP TABLE Book; followed by SELECT * FROM Book;? Explain both outcomes.