SQL Command Categories and Examples Cheat Sheet
SQL Cheatsheet
Categories
SQL commands are grouped into different categories based on their purpose:
-
DDL (Data Definition Language):
- Used to define or alter database structures.
- Commands within DDL include:
CREATE
,DROP
,ALTER
,TRUNCATE
,RENAME
,COMMENT
.
-
DQL (Data Query Language):
- Primarily focused on querying data within the database.
- Command:
SELECT
.
-
DML (Data Manipulation Language):
- Used to manipulate data stored in the database.
- Commands include:
INSERT
,UPDATE
,DELETE
,LOCK
,CALL
,EXPLAIN PLAN
.
-
DCL (Data Control Language):
- Used to control access to data within the database.
- Commands:
GRANT
,REVOKE
.
-
TCL (Transaction Control Language):
- Used to manage transactions within the database.
- Commands:
COMMIT
,ROLLBACK
,SAVEPOINT
,SET TRANSACTION
.
Commands
A quick overview of some of the commands and their uses:
-
DDL:
CREATE
: Create a new table or object in the database.DROP
: Delete objects from the database.ALTER
: Modify an existing database object.TRUNCATE
: Remove all records from a table.RENAME
: Rename an existing database object.COMMENT
: Add comments to the data dictionary.
-
DQL:
SELECT
: Retrieve data from the database.
-
DML:
INSERT
: Add new records to the table.UPDATE
: Modify existing records.DELETE
: Remove records.LOCK
: Lock the table or object.CALL
: Execute a PL/SQL or JAVA routine.EXPLAIN PLAN
: Read the access path for a query.
-
DCL:
GRANT
: Provide user access.REVOKE
: Remove user access.
-
TCL:
COMMIT
: Save transaction changes.ROLLBACK
: Revert transaction changes.SAVEPOINT
: Set a point within a transaction to which you can rollback.SET TRANSACTION
: Change transaction settings like isolation level.
Operators
Different operators in SQL help perform various operations:
- Arithmetic:
+
,-
,*
,/
,%
. - Bitwise:
&
,|
,^
. - Comparison:
=
,!=
,<>
,>
,<
,>=
,<=
. - Compound:
+
,-
,*
,/
,%
. - Logical:
AND
,OR
,NOT
,ANY
,SOME
,ALL
,BETWEEN
,IN
,EXISTS
,LIKE
,IS NULL
,UNIQUE
.
Database Objects
Common objects in a database include:
- TABLE: Basic unit of data storage.
- VIEW: A virtual table based on a query.
- SYNONYM: An alias for a database object.
- SEQUENCE: Generates numeric values.
- INDEX: Provides quick access to rows.
- TRIGGER: Executes a predefined action.
Constraints
Constraints ensure the accuracy and reliability of the data within the database:
- NOT NULL
- UNIQUE
- PRIMARY KEY
- FOREIGN KEY
- CHECK
- DEFAULT
Aggregation Functions
These functions calculate statistics and summarize data:
AVG
: Average value.COUNT
: Number of values.MAX
: Maximum value.MIN
: Minimum value.SUM
: Total sum of values.
Aggregation Keywords
GROUP BY
: Groups rows sharing a property.HAVING
: Filters grouped rows.
Joins
Joins combine rows from two or more tables:
- INNER JOIN: Returns records with matching values.
- LEFT (OUTER) JOIN: Returns all records from the left table and matched records from the right table.
- RIGHT (OUTER) JOIN: Returns all records from the right table and matched records from the left table.
- FULL (OUTER) JOIN: Returns all records from both tables.
Set Operations
Combine results from multiple queries:
- UNION: Combines results and eliminates duplicates.
- UNION ALL: Combines results without removing duplicates.
- INTERSECT: Returns records common to both queries.
- EXCEPT/MINUS: Returns records from the first query not found in the second.
Important Keywords
These keywords help refine SQL queries:
WHERE
,DISTINCT
,LIMIT
,ORDER BY
,DESC
,ASC
,AS
,FROM
,SET
,VALUES
,CASE
,DEFAULT
.
Examples of SQL Commands
1. DDL Examples
-
Create 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 an existing column
ALTER TABLE Students ALTER COLUMN lname varchar(512);
-
Removing an existing column from the Table
ALTER TABLE Students DROP COLUMN lname;
-
Truncate (remove all data) a Table
TRUNCATE TABLE Students;
-
Drop a Table
DROP Table Students;
2. DQL Examples
-
Fetch all data from a Table
SELECT * FROM Students;
-
Filter data from a Table
SELECT * FROM Students WHERE rollno = 1234;
-
Fetch data containing specific records
SELECT * FROM Students WHERE gender = 'Female';
-
Fetch particular columns from Table and sort
SELECT fname, lname FROM Students WHERE rollno = 1234 AND gender = 'Female' ORDER BY fname;
-
Fetch number of records
SELECT count(*) FROM Students;
-
Fetch (order) fetched records
SELECT frame, lname FROM Students WHERE rollno > 2345 ORDER BY gender;
-
Fetch Maximum Age
SELECT MAX(age) FROM Students;
-
Fetch Minimum Age
SELECT MIN(age) FROM Students;
-
Fetch Sum of record's Age
SELECT SUM(age) FROM Students;
-
Fetch Average Age
SELECT AVG(age) FROM Students;
-
Fetch Average Age grouped by section
SELECT section_id, AVG(age) FROM Students GROUP BY section_id;
3. DML Examples
-
Insert data (rows) into a Table
INSERT INTO Students (rollno, fname, lname) VALUES (1234, 'Christiano', 'Ronaldo');
-
Update data (value of column) of a Table
UPDATE Students SET lname = 'Messi' WHERE rollno = 1234;
-
Delete data (rows) from a Table
DELETE FROM Students WHERE rollno = 1234;
-
Aggregate, and Filter
SELECT section_id, COUNT(*) FROM Students GROUP BY section_id HAVING COUNT(*) > 20;
-
Filter Inner Join
SELECT frame, classteacher FROM Students, Section INNER JOIN Section ON Students.section_id=Section.id;
Reference: