SQL (Structured Query Language) is the standard language for managing relational databases. It has four sub-languages: DDL (Data Definition Language) for creating/modifying schema, DML (Data Manipulation Language) for querying/modifying data, DCL (Data Control Language) for permissions, and TCL (Transaction Control Language) for transaction management. SQL is declarative — you describe WHAT result you want, not HOW to compute it. Used by every database professional, data analyst, and backend developer daily.
Real-life analogy: The file cabinet system
DDL is the office manager who designs the filing system (CREATE/ALTER/DROP TABLE). DML is the receptionist who files, retrieves, and updates documents (INSERT, SELECT, UPDATE, DELETE). DCL is the security officer who decides who can access which cabinet (GRANT, REVOKE). TCL is the auditor who approves or rejects batches of changes (COMMIT, ROLLBACK).
DDL — Data Definition Language
Complete DDL — creating a university database
-- CREATE TABLE with all constraint types
CREATE TABLE Department (
DeptID INT PRIMARY KEY,
DeptName VARCHAR(100) NOT NULL UNIQUE,
Budget DECIMAL(12,2) CHECK (Budget > 0)
);
CREATE TABLE Student (
StudentID INT PRIMARY KEY,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
Email VARCHAR(100) UNIQUE NOT NULL,
DOB DATE,
GPA DECIMAL(3,2) CHECK (GPA BETWEEN 0.00 AND 4.00),
DeptID INT REFERENCES Department(DeptID)
ON DELETE SET NULL
ON UPDATE CASCADE,
EnrollDate DATE DEFAULT CURRENT_DATE,
Status VARCHAR(20) DEFAULT 'Active'
CHECK (Status IN ('Active','Graduated','Suspended'))
);
-- ALTER TABLE: modify existing structure
ALTER TABLE Student ADD COLUMN PhoneNumber VARCHAR(15);
ALTER TABLE Student DROP COLUMN PhoneNumber;
ALTER TABLE Student ADD CONSTRAINT chk_dob CHECK (DOB < '2010-01-01');
-- DROP vs TRUNCATE vs DELETE
DROP TABLE Student; -- Remove table (structure + data)
TRUNCATE TABLE Student; -- Remove all rows, keep structure, fast, less logged
DELETE FROM Student; -- Remove all rows, fully logged, rollback-able
-- CREATE INDEX
CREATE INDEX idx_student_dept ON Student(DeptID);
CREATE UNIQUE INDEX idx_email ON Student(Email);
CREATE INDEX idx_name ON Student(LastName, FirstName); -- CompositeDML — INSERT, UPDATE, DELETE
DML operations with practical examples
-- INSERT: single row
INSERT INTO Department VALUES (1, 'Computer Science', 500000);
INSERT INTO Department (DeptID, DeptName) VALUES (2, 'Mathematics');
-- INSERT multiple rows
INSERT INTO Student (StudentID, FirstName, LastName, Email, DeptID) VALUES
(101, 'Ravi', 'Kumar', 'ravi@univ.edu', 1),
(102, 'Priya', 'Sharma', 'priya@univ.edu', 2),
(103, 'Ahmed', 'Khan', 'ahmed@univ.edu', 1);
-- INSERT from SELECT (copy data)
INSERT INTO ArchivedStudents SELECT * FROM Student WHERE Status = 'Graduated';
-- UPDATE rows
UPDATE Student SET GPA = 3.9 WHERE StudentID = 101;
UPDATE Student SET GPA = GPA + 0.1 WHERE DeptID = 1; -- Computed
-- DELETE rows
DELETE FROM Student WHERE StudentID = 103;
DELETE FROM Student WHERE DeptID NOT IN (SELECT DeptID FROM Department);
-- UPSERT (PostgreSQL ON CONFLICT)
INSERT INTO Student (StudentID, FirstName, LastName, Email, GPA)
VALUES (101, 'Ravi', 'Kumar', 'ravi@univ.edu', 3.95)
ON CONFLICT (StudentID) DO UPDATE SET GPA = EXCLUDED.GPA;SQL Data Types
| Category | Type | Storage | Example |
|---|---|---|---|
| Integer | TINYINT, SMALLINT, INT, BIGINT | 1/2/4/8 bytes | Age INT, Quantity SMALLINT |
| Decimal | DECIMAL(p,s), FLOAT, DOUBLE | Variable/4/8 bytes | Price DECIMAL(10,2) = 99999999.99 |
| String | CHAR(n), VARCHAR(n), TEXT | Fixed/Variable/Unlimited | Name VARCHAR(100), Bio TEXT |
| Date/Time | DATE, TIME, TIMESTAMP | 4/8/8 bytes | DOB DATE, CreatedAt TIMESTAMP |
| Boolean | BOOLEAN | 1 byte | IsActive BOOLEAN DEFAULT TRUE |
| JSON | JSON, JSONB | Variable | Metadata JSONB |
CHAR vs VARCHAR
CHAR(n) always stores exactly n characters (pads with spaces). VARCHAR(n) stores only what you put in. CHAR(50) storing "Ravi" uses 50 bytes; VARCHAR(50) uses 4 bytes. Always use VARCHAR unless you need fixed-width (e.g., country codes). DECIMAL(p,s): p = total digits, s = digits after decimal point.
Practice questions
- Difference between DROP, DELETE, and TRUNCATE? (Answer: DROP removes entire table structure and data. DELETE removes specific rows — logged, rollback-able, can use WHERE. TRUNCATE removes all rows without per-row logging — faster, cannot use WHERE, difficult to rollback.)
- DECIMAL(7,3) — what is the maximum value it can store? (Answer: 9999.999 — 4 digits before decimal, 3 after.)
- Difference between NULL and empty string in SQL? (Answer: NULL = unknown/missing data, not a value. Empty string = valid zero-length string. NULL comparisons use IS NULL / IS NOT NULL, not = NULL.)
- ON DELETE CASCADE vs ON DELETE SET NULL: when to use each? (Answer: CASCADE: child rows have no meaning without parent (OrderItems when Order deleted). SET NULL: child can exist independently (Employee.DeptID can be NULL if department closed).)
- Can a table have no primary key? (Answer: Technically yes (heap table) but bad practice — duplicate rows possible, UPDATE/DELETE may affect unintended rows, FK references impossible. Every table should have a PK.)
On LumiChats
LumiChats can write, debug, and optimise any SQL query for PostgreSQL, MySQL, SQLite, or SQL Server. Describe your table structure and the data you need in plain English and LumiChats generates correct SQL with full explanation.
Try it free