SQL Statements Reference

Data Definition Language (DDL) Statements

DDL statements are used to define and modify the structure of database objects.

CREATE Statement

Used to create new objects in the database, such as tables, views, indexes, stored procedures, and functions.

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100) UNIQUE
);

ALTER Statement

Used to modify the structure of existing database objects.

ALTER TABLE Customers
ADD PhoneNumber VARCHAR(20);

DROP Statement

Used to delete existing database objects.

DROP TABLE Customers;

TRUNCATE Statement

Used to remove all records from a table quickly, but preserves the table structure.

TRUNCATE TABLE Orders;

Data Manipulation Language (DML) Statements

DML statements are used to manage data within schema objects.

INSERT Statement

Used to insert new rows of data into a table.

INSERT INTO Customers (CustomerID, FirstName, LastName, Email)
VALUES (1, 'John', 'Doe', 'john.doe@example.com');

UPDATE Statement

Used to update existing records in a table.

UPDATE Customers
SET Email = 'john.doe.new@example.com'
WHERE CustomerID = 1;

DELETE Statement

Used to delete rows from a table.

DELETE FROM Customers
WHERE CustomerID = 1;

SELECT Statement

Used to retrieve data from one or more tables.

SELECT FirstName, LastName
FROM Customers
WHERE CustomerID > 10;

Data Control Language (DCL) Statements

DCL statements deal with permissions, authorizations, and other controls on the database system.

GRANT Statement

Used to give users permissions to perform certain actions on database objects.

GRANT SELECT ON Customers TO WebUser;

REVOKE Statement

Used to take back permissions from users.

REVOKE INSERT ON Customers FROM WebUser;

Transaction Control Language (TCL) Statements

TCL statements manage transactions within the database.

COMMIT Statement

Saves all the transactions to the database.

COMMIT TRANSACTION;

ROLLBACK Statement

Undoes all the transactions since the last COMMIT or ROLLBACK.

ROLLBACK TRANSACTION;

SAVEPOINT Statement

Sets a point in a transaction to which you can later roll back.

SAVE TRANSACTION MySavePoint;