SQL Statements - Language Reference

This section provides a comprehensive overview of the SQL statements available for interacting with and managing your database. Understanding these statements is fundamental to effectively querying, manipulating, and defining data.

Data Definition Language (DDL) Statements

DDL statements are used to define and manage the database schema.

CREATE Statement

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

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
);

See also: CREATE TABLE, CREATE INDEX, CREATE VIEW

ALTER Statement

Used to modify existing database objects.

ALTER TABLE table_name
ADD column_name datatype;

See also: ALTER TABLE

DROP Statement

Used to delete database objects.

DROP TABLE table_name;

See also: DROP TABLE

TRUNCATE Statement

Used to remove all records from a table quickly.

TRUNCATE TABLE table_name;

See also: TRUNCATE TABLE

Data Manipulation Language (DML) Statements

DML statements are used to manage data within schema objects.

SELECT Statement

Used to retrieve data from a database.

SELECT column1, column2, ...
FROM table_name
WHERE condition;

See also: SELECT, SQL Functions

INSERT Statement

Used to insert new records into a table.

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

See also: INSERT INTO

UPDATE Statement

Used to update existing records in a table.

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

See also: UPDATE

DELETE Statement

Used to delete existing records from a table.

DELETE FROM table_name
WHERE condition;

See also: DELETE FROM

Data Control Language (DCL) Statements

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

GRANT Statement

Used to give users permission to do certain things in the database.

GRANT SELECT ON table_name TO user_name;

See also: GRANT

REVOKE Statement

Used to take back permissions from users.

REVOKE DELETE ON table_name FROM user_name;

See also: REVOKE

Transaction Control Language (TCL) Statements

TCL statements manage transactions within the database.

COMMIT Statement

Used to save all the transactions to the database.

COMMIT;

ROLLBACK Statement

Used to undo transactions that have not been saved.

ROLLBACK;

SAVEPOINT Statement

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

SAVEPOINT savepoint_name;

Detailed Statement References

Click on the links below for detailed syntax, examples, and explanations of specific SQL statements:

CREATE TABLE

Creates a new table in the database. You specify the table name and the columns, along with their data types and any constraints.

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    RegistrationDate DATE DEFAULT GETDATE()
);

Constraints can include PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, and CHECK.

ALTER TABLE

Modifies an existing table by adding, deleting, or modifying columns, or by adding or dropping constraints.

Adding a column:

ALTER TABLE Customers
ADD Phone VARCHAR(20);

Dropping a column:

ALTER TABLE Customers
DROP COLUMN Email;

Modifying a column's data type:

ALTER TABLE Customers
ALTER COLUMN FirstName VARCHAR(75);

DROP TABLE

Deletes a table and all of its data from the database.

DROP TABLE Customers;

Use with caution, as this action is irreversible.

TRUNCATE TABLE

Removes all rows from a table, but the table structure remains. It is generally faster than DELETE for removing all rows.

TRUNCATE TABLE Orders;

Note: TRUNCATE cannot be used on tables referenced by foreign key constraints, unless specified otherwise by the database system.

SELECT

Retrieves data from one or more tables. You can specify which columns to retrieve and apply conditions to filter the results.

Selecting all columns:

SELECT *
FROM Products;

Selecting specific columns with a filter:

SELECT ProductName, Price
FROM Products
WHERE Category = 'Electronics' AND Price > 500;

Using JOIN clauses to combine rows from two or more tables based on a related column.

INSERT INTO

Adds new rows (records) to a table.

Inserting values into all columns:

INSERT INTO Customers (CustomerID, FirstName, LastName, Email)
VALUES (101, 'Jane', 'Doe', 'jane.doe@example.com');

Inserting values into specific columns:

INSERT INTO Products (ProductID, ProductName, Price)
VALUES (55, 'Wireless Mouse', 25.99);

UPDATE

Modifies existing records in a table. The WHERE clause is crucial to specify which rows to update.

UPDATE Products
SET Price = 29.99, StockQuantity = 150
WHERE ProductID = 55;

If the WHERE clause is omitted, all rows in the table will be updated.

DELETE FROM

Removes one or more rows from a table. The WHERE clause is used to specify which rows to delete.

DELETE FROM Customers
WHERE CustomerID = 101;

If the WHERE clause is omitted, all rows in the table will be deleted.

GRANT

Allows specific users or roles to perform certain actions (privileges) on database objects.

GRANT SELECT, INSERT ON Employees TO UserApp;

Common privileges include SELECT, INSERT, UPDATE, DELETE, ALL PRIVILEGES.

REVOKE

Removes previously granted privileges from users or roles.

REVOKE UPDATE ON Employees FROM UserApp;