Unit 1: Database Concepts (RDBMS)
Chapter 3: SQL Fundamentals - DDL and DML
1. What is SQL?
Structured Query Language (SQL) is the standard, declarative language used to create, query, update, and manage relational databases.
- Originally developed by IBM in the 1970s (as SEQUEL).
- Standardized by ANSI (American National Standards Institute) and ISO.
- SQL is case-insensitive (e.g., SELECT, Select, and select are identical), though string literal data inside single quotes is case-sensitive in comparisons.
2. Classification of SQL Commands
SQL commands are categorized based on their functional purpose:
+-----------------------------------------------------------------+
| SQL Command Categories |
+-----------------------------------------------------------------+
| | | |
DDL DML DCL TCL
(Data Def.) (Data Manip.) (Data Control) (Transaction)
- CREATE - INSERT - GRANT - COMMIT
- ALTER - SELECT - REVOKE - ROLLBACK
- DROP - UPDATE - SAVEPOINT
- TRUNCATE - DELETE
1. DDL (Data Definition Language)
- Commands that define, alter, or remove database structures (schemas, tables, indexes, views).
- Key Commands:
CREATE,ALTER,DROP,TRUNCATE. - Changes made by DDL are auto-committed (permanent immediately).
2. DML (Data Manipulation Language)
- Commands that insert, retrieve, modify, and delete data rows inside tables.
- Key Commands:
SELECT,INSERT,UPDATE,DELETE. - DML operations are transaction-controlled (can be rolled back unless committed).
3. DCL (Data Control Language)
- Commands that administer security permissions and user access rights.
- Key Commands:
GRANT(give permission),REVOKE(take back permission).
4. TCL (Transaction Control Language)
- Commands that manage transactions and preserve ACID properties.
- Key Commands:
COMMIT(save permanently),ROLLBACK(undo changes),SAVEPOINT(create rollback milestone).
3. Standard MySQL Data Types
| Data Type | Description | Example Syntax |
|---|---|---|
CHAR(M) |
Fixed-length string ($0$ to $255$ chars). Padded with spaces if shorter. | Gender CHAR(1) |
VARCHAR(M) |
Variable-length string ($0$ to $65,535$ chars). Uses only needed space. | Cust_Name VARCHAR(50) |
INT or INTEGER |
4-byte standard whole number ($-2^{31}$ to $2^{31}-1$). | RollNo INT |
DECIMAL(P, D) |
Exact decimal number. $P$ = total precision digits, $D$ = decimal places. | Price DECIMAL(8, 2) |
DATE |
Calendar date in YYYY-MM-DD format. |
DOB DATE |
TIME |
Time value in HH:MM:SS format. |
Login_Time TIME |
Board Exam Note: CHAR vs. VARCHAR
| Feature | CHAR(M) | VARCHAR(M) |
|---|---|---|
| Length | Fixed length | Variable length |
| Storage Usage | Always reserves $M$ bytes regardless of input | Uses actual character length $+ 1$ or $2$ bytes |
| Speed | Faster processing | Slightly slower due to length computation |
| Ideal For | Predictable strings (State codes: DL, UP; Gender: M, F) |
Varied strings (Name, Address, Email) |
4. SQL Literals and Syntax Rules
- Text & Date Literals: Must be enclosed in single quotes (e.g.,
'Rohit Sharma','2024-03-15'). - Numeric Literals: Written directly without quotes (e.g.,
8500,94.5). - Statements Terminator: Every SQL statement terminates with a semicolon (
;). - NULL Concept:
NULLrepresents missing, unknown, or not applicable values. It is NOT equal to zero (0) or an empty string (""). Comparisons withNULLrequireIS NULLorIS NOT NULL.
5. Board Exam Focus
Important
- Grouping error:
SELECTis technically considered DQL (Data Query Language) or part of DML. For CBSE IT Class XII,SELECTis categorized under DML. - Remember that
DROP TABLEremoves table structure + data, whileDELETE FROMremoves records but keeps table structure intact.