SQL Basics
SQL (Structured Query Language) is the standard language for relational databases.
Categories
| Category | Commands |
|---|---|
| DDL (definition) | CREATE, ALTER, DROP, TRUNCATE |
| DML (manipulation) | INSERT, UPDATE, DELETE |
| DQL (query) | SELECT |
| DCL (control) | GRANT, REVOKE |
| TCL (transaction) | COMMIT, ROLLBACK, SAVEPOINT |
Creating and changing data
ddl_dml.sql
CREATE TABLE student (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
marks INT CHECK (marks BETWEEN 0 AND 100),
city VARCHAR(30)
);
INSERT INTO student (id, name, marks, city)
VALUES (1, 'Asha', 91, 'Pune'), (2, 'Ravi', 74, 'Delhi');
UPDATE student SET marks = marks + 5 WHERE id = 2;
DELETE FROM student WHERE marks < 40;
Querying
select.sql
SELECT name, marks
FROM student
WHERE city = 'Pune' AND marks >= 60
ORDER BY marks DESC
LIMIT 10;
Logical order of evaluation: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.
Useful filters
filters.sql
SELECT * FROM student WHERE name LIKE 'A%';
SELECT * FROM student WHERE city IN ('Pune', 'Delhi');
SELECT * FROM student WHERE marks BETWEEN 60 AND 90;
SELECT * FROM student WHERE city IS NULL; -- not "= NULL"
CAUTION
NULL means "unknown". Comparisons with NULL (= NULL, <> NULL) are never true; use IS NULL / IS NOT NULL.
DELETE vs TRUNCATE vs DROP
| Removes | Can use WHERE | Rollback (typical) | |
|---|---|---|---|
DELETE | Rows | Yes | Yes |
TRUNCATE | All rows | No | Depends on the DBMS |
DROP | The whole table | No | No |
Loading Comments...