🗄️ Unit 3 – Database & SQL · Lesson 21
SQL: DDL, DML & SELECT
What is SQL?
SQL (Structured Query Language) is the standard language to work with relational databases like MySQL. Keywords are not case-sensitive and each statement ends with a semicolon.
🏗️
DDLData Definition: CREATE, ALTER, DROP – defines structure.✏️
DMLData Manipulation: INSERT, UPDATE, DELETE, SELECT – works on rows.🛡️
DCL / TCLGRANT, REVOKE, COMMIT, ROLLBACK – permissions and transactions.SQL
CREATE DATABASE SCHOOL; USE SCHOOL; SHOW TABLES;
Data types & constraints
| Data type | Stores | Example |
|---|---|---|
| INT | Whole numbers | RollNo INT |
| DECIMAL(p,s) / FLOAT | Numbers with decimals | Marks DECIMAL(5,1) |
| CHAR(n) | Fixed-length text | Gender CHAR(1) |
| VARCHAR(n) | Variable-length text | Name VARCHAR(25) |
| DATE | YYYY-MM-DD | DOB DATE |
| Constraint | Meaning |
|---|---|
| PRIMARY KEY | Unique + NOT NULL identifier |
| NOT NULL | Value must be given |
| UNIQUE | No two rows may have the same value |
| DEFAULT | Value used when none is given |
| FOREIGN KEY | Must match a key in another table |
CREATE TABLE & DESCRIBE
SQL
CREATE TABLE STUDENT ( RollNo INT PRIMARY KEY, Name VARCHAR(25) NOT NULL, Stream VARCHAR(12), Marks DECIMAL(5,1), City VARCHAR(15) DEFAULT 'Delhi' );
SQL
DESCRIBE STUDENT;
Output
+--------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------+------+-----+---------+-------+ | RollNo | int | NO | PRI | NULL | | | Name | varchar(25) | NO | | NULL | | | Stream | varchar(12) | YES | | NULL | | | Marks | decimal(5,1) | YES | | NULL | | | City | varchar(15) | YES | | Delhi | | +--------+--------------+------+-----+---------+-------+
INSERT
Values must follow the column order; text and dates go in quotes. With a column list, missing columns get their DEFAULT (City = 'Delhi') or NULL.
SQL
INSERT INTO STUDENT VALUES (1, 'Anika', 'Science', 91.5, 'Jaipur'), (2, 'Dev', 'Commerce', 78.0, 'Delhi'), (3, 'Farhan', 'Science', 64.5, 'Lucknow'), (4, 'Gauri', 'Humanities', 88.0, 'Delhi'), (5, 'Harsh', 'Commerce', 55.0, 'Pune'), (6, 'Isha', 'Science', NULL, 'Jaipur');
SQL
INSERT INTO STUDENT (RollNo, Name, Stream, Marks) VALUES (7, 'Jatin', 'Commerce', 69.5);
SQL
SELECT * FROM STUDENT;
Output
+--------+--------+------------+-------+---------+ | RollNo | Name | Stream | Marks | City | +--------+--------+------------+-------+---------+ | 1 | Anika | Science | 91.5 | Jaipur | | 2 | Dev | Commerce | 78.0 | Delhi | | 3 | Farhan | Science | 64.5 | Lucknow | | 4 | Gauri | Humanities | 88.0 | Delhi | | 5 | Harsh | Commerce | 55.0 | Pune | | 6 | Isha | Science | NULL | Jaipur | | 7 | Jatin | Commerce | 69.5 | Delhi | +--------+--------+------------+-------+---------+
SELECT & WHERE
- 1FROM – pick the table
- 2WHERE – keep only matching rows
- 3SELECT – choose the columns
- 4ORDER BY – sort the result
SQL
SELECT Name, Marks FROM STUDENT WHERE Stream = 'Science' AND Marks > 70;
Output
+-------+-------+ | Name | Marks | +-------+-------+ | Anika | 91.5 | +-------+-------+
BETWEEN, IN, LIKE, NULL
BETWEEN a AND b includes both ends. IN matches any value in a list. In LIKE, % means any number of characters and _ exactly one.
SQL
SELECT Name, Marks FROM STUDENT WHERE Marks BETWEEN 60 AND 80;
Output
+--------+-------+ | Name | Marks | +--------+-------+ | Dev | 78.0 | | Farhan | 64.5 | | Jatin | 69.5 | +--------+-------+
SQL
SELECT Name, City FROM STUDENT WHERE City IN ('Jaipur', 'Pune');Output
+-------+--------+ | Name | City | +-------+--------+ | Anika | Jaipur | | Harsh | Pune | | Isha | Jaipur | +-------+--------+
SQL
SELECT Name FROM STUDENT WHERE Name LIKE '%a' OR Name LIKE '_a%';
Output
+--------+ | Name | +--------+ | Anika | | Farhan | | Gauri | | Harsh | | Isha | | Jatin | +--------+
SQL
SELECT Name, Marks FROM STUDENT WHERE Marks IS NULL;
Output
+------+-------+ | Name | Marks | +------+-------+ | Isha | NULL | +------+-------+
DISTINCT, ORDER BY, aliases
SQL
SELECT DISTINCT Stream FROM STUDENT;
Output
+------------+ | Stream | +------------+ | Science | | Commerce | | Humanities | +------------+
SQL
SELECT Name, Marks FROM STUDENT WHERE Marks IS NOT NULL ORDER BY Marks DESC;
Output
+--------+-------+ | Name | Marks | +--------+-------+ | Anika | 91.5 | | Gauri | 88.0 | | Dev | 78.0 | | Jatin | 69.5 | | Farhan | 64.5 | | Harsh | 55.0 | +--------+-------+
SQL
SELECT Name, Marks, Marks * 5 AS 'Out of 500' FROM STUDENT WHERE Stream = 'Commerce';
Output
+-------+-------+------------+ | Name | Marks | Out of 500 | +-------+-------+------------+ | Dev | 78.0 | 390.0 | | Harsh | 55.0 | 275.0 | | Jatin | 69.5 | 347.5 | +-------+-------+------------+
UPDATE & DELETE
⚠️ Always use WHERE
UPDATE or DELETE without WHERE changes or removes every row.
SQL
UPDATE STUDENT SET Marks = 72.0 WHERE RollNo = 6;
SQL
UPDATE STUDENT SET Marks = Marks + 2 WHERE Stream = 'Commerce';
SQL
SELECT RollNo, Name, Marks FROM STUDENT WHERE Stream IN ('Commerce') OR RollNo = 6;Output
+--------+-------+-------+ | RollNo | Name | Marks | +--------+-------+-------+ | 2 | Dev | 80.0 | | 5 | Harsh | 57.0 | | 6 | Isha | 72.0 | | 7 | Jatin | 71.5 | +--------+-------+-------+
SQL
DELETE FROM STUDENT WHERE Marks < 60;
ALTER & DROP
SQL
ALTER TABLE STUDENT ADD Phone CHAR(10);
SQL
ALTER TABLE STUDENT MODIFY Name VARCHAR(40) NOT NULL;
SQL
ALTER TABLE STUDENT DROP Phone;
SQL
SELECT * FROM STUDENT;
Output
+--------+--------+------------+-------+---------+ | RollNo | Name | Stream | Marks | City | +--------+--------+------------+-------+---------+ | 1 | Anika | Science | 91.5 | Jaipur | | 2 | Dev | Commerce | 80.0 | Delhi | | 3 | Farhan | Science | 64.5 | Lucknow | | 4 | Gauri | Humanities | 88.0 | Delhi | | 6 | Isha | Science | 72.0 | Jaipur | | 7 | Jatin | Commerce | 71.5 | Delhi | +--------+--------+------------+-------+---------+
SQL
CREATE TABLE TEMP (ID INT);
SQL
DROP TABLE TEMP;
DDL vs DML
DDL
- Works on structure (schema)
- CREATE, ALTER, DROP
- ALTER adds/changes/removes columns
- DROP TABLE removes table + data
DML
- Works on data (rows)
- INSERT, UPDATE, DELETE, SELECT
- UPDATE changes values in rows
- DELETE removes rows, keeps table
Frequently Asked Questions
What is the difference between DELETE and DROP?
DELETE (DML) removes rows but the table structure remains. DROP TABLE (DDL) removes the table itself along with all its data.
What is the difference between CHAR and VARCHAR?
CHAR(n) always uses n characters (padded with spaces). VARCHAR(n) uses only as many characters as stored, up to n.
Why do we write IS NULL instead of = NULL?
NULL means unknown, so any comparison with = gives unknown, never true. IS NULL is the special operator to test for it.
