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