SQL Command Categories and Examples Cheat Sheet

SQL Cheatsheet

IMG_0215.jpeg

Categories

SQL commands are grouped into different categories based on their purpose:

  1. DDL (Data Definition Language) :

    • Used to define or alter database structures.
    • Commands within DDL include: CREATE, DROP, ALTER, TRUNCATE, RENAME, COMMENT.
  2. DQL (Data Query Language) :

    • Primarily focused on querying data within the database.
    • Command: SELECT.
  3. DML (Data Manipulation Language) :

    • Used to manipulate data stored in the database.
    • Commands include: INSERT, UPDATE, DELETE, LOCK, CALL, EXPLAIN PLAN.
  4. DCL (Data Control Language) :

    • Used to control access to data within the database.
    • Commands: GRANT, REVOKE.
  5. 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:

www.geeksforgeeks.org
SQL Cheat Sheet ( Basic to Advanced) - GeeksforGeeks
www.geeksforgeeks.org
Create a Database in SQL
www.geeksforgeeks.org
SQL Operator