Categories
- DDL: Data Definition Language
- DQL: Data Query Language
- DML: Data Manipulation Language
- DCL: Data Control Language
- TCL: Transaction Control Language
Commands
DDL
CREATE | DROP | ALTER | TRUNCATE | RENAME | COMMENT
DDL Examples
Create a Table
CREATE TABLE Students(
rollno int PRIMARY KEY,
fname varchar(255) NOT NULL,
lname varchar(255)
);
Adding a new column to the Table
ALTER TABLE Students
ADD email varchar(255);
Modifying the data type of existing column
ALTER TABLE Students
ALTER COLUMN lname varchar(512);
Removing an existing column from the Table
ALTER TABLE Students
DROP COLUMN email;
Truncate (remove all data) a Table
TRUNCATE TABLE Students;
Drop a Table
DROP TABLE Students;
DQL
SELECT
DQL Examples
Fetch all data from a Table
SELECT * FROM Students;
Filter data from a Table
SELECT * FROM Students
WHERE rollno = 1234;
SELECT * FROM Students
WHERE rollno > 1234
AND age < 15;
Fetch selected columns
SELECT fname, lname FROM Students
WHERE rollno > 1234
AND age < 15;
Fetch maximum 10 rows
SELECT fname, lname FROM Students
WHERE rollno > 1234
AND age < 15
LIMIT 10;
Fetch count of records
SELECT count(*) FROM Students;
Fetch Maximum Age
SELECT max(age) FROM Students;
Fetch Minimum Age
SELECT min(age) FROM Students;
Fetch Sum of Age
SELECT sum(age) FROM Students;
Fetch Average Age
SELECT avg(age) FROM Students;
Fetch Average Age for each gender
SELECT avg(age)
FROM Students
GROUP BY gender;
Sort (order) fetched records
SELECT fname, lname FROM Students
WHERE rollno > 1234
AND age < 15
GROUP BY gender;
Sort in descending order
SELECT fname, lname FROM Students
WHERE rollno > 1234
AND age < 15
GROUP BY gender DESC;
Fetch from 2 Tables
SELECT fname, clsteacher
FROM Students
INNER JOIN Section
ON Students.section = Section.id;
Aggregate and, Filter
SELECT section, count(*) AS studentcount
FROM Students
GROUP BY section
HAVING count(*) > 20;
Full Outer Join
SELECT fname, clsteacher
FROM Students
FULL JOIN Section
ON Students.section = Section.id;
DML
INSERT | UPDATE | DELETE | LOCK | CALL | EXPLAIN PLAN
DML Examples
Insert data (rows) into a Table
INSERT INTO Students(rollno, fname, lname)
VALUES (1234, 'firstname', 'lastname');
Update data (value of column) of a Table
UPDATE Students
SET lname = 'newname'
WHERE rollno = 1234;
Delete data (rows) from Table
DELETE FROM Students
WHERE rollno = 1234;
DCL
GRANT | REVOKE
TCL
COMMIT | ROLLBACK | SAVEPOINT | SET TRANSACTION
Operators
Arithmetic
- + (Add)
- - (Subtract)
- * (Multiply)
- % (Modulo)
Bitwise
- & (Bitwise AND)
- | (Bitwise OR)
- ^ (Bitwise Exclusive OR)
Comparison
- = (Equals)
- > (Greater Than)
- < (Less Than)
- >= (Greater Than or Equal To)
- <= (Less Than or Equal To)
- <> (Not Equal To)
- !< (Not Less Than)
- != (Not Equal To)
- !> (Not Greater Than)
Compound
- += (Add Assignment)
- -= (Subtract Assignment)
- *= (Multiply Assignment)
- /= (Divide Assignment)
- %= (Modulus Assignment)
- &= (Bitwise AND Assignment)
- ^= (Bitwise Exclusive OR Assignment)
- |= (Bitwise OR Assignment)
Logical
AND | OR | NOT | ANY | SOME | ALL | BETWEEN | IN | EXISTS | LIKE | IS NULL | UNIQUE
Important Keywords
WHERE | DISTINCT | LIMIT | ORDER BY | DESC | ASC | AS | FROM | SET | VALUES | CASE | DEFAULT
Database Object
TABLE | VIEW | SYNONYM | SEQUENCE | INDEX | TRIGGER
Constraints
NOT NULL | UNIQUE | PRIMARY KEY | FOREIGN KEY | CHECK | DEFAULT
Aggregation Function
AVG | COUNT | MAX | MIN | SUM
Aggregation Keywords
GROUP BY | HAVING
Joins
INNER JOIN
LEFT [OUTER] JOIN
FULL [OUTER] JOIN
RIGHT [OUTER] JOIN
Set Operations
UNION
UNION ALL
INTERSECT
EXCEPT
MINUS