SQL Query Language

This document provides a comprehensive overview of the Standard Query Language (SQL) used for managing and querying relational databases. SQL is a declarative language, meaning you specify what data you want to retrieve or manipulate, and the database system determines the most efficient way to execute the request.

Core Concepts

The SQL query language is built around several fundamental concepts:

• Tables: Data is organized into tables, which consist of rows (records) and columns (fields).
• Schema: The structure of a database, including table definitions, relationships, and constraints.
• Queries: Statements used to retrieve, insert, update, or delete data.
• Statements: Specific SQL commands like SELECT, INSERT, UPDATE, and DELETE.

Data Manipulation Language (DML)

DML statements are used to manage data within database tables.

SELECT Statement

The SELECT statement is used to retrieve data from one or more tables. It is the most frequently used SQL statement.

Syntax:

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

Example: Retrieve the names and emails of all users from the Users table where the user is active:

SELECT name, email
FROM Users
WHERE status = 'Active';

INSERT Statement

The INSERT statement is used to add new rows of data to a table.

Syntax:

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

Example: Add a new user to the Users table:

INSERT INTO Users (username, email, registration_date)
VALUES ('john_doe', 'john.doe@example.com', '2023-10-27');

UPDATE Statement

The UPDATE statement is used to modify existing records in a table.

Syntax:

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

Example: Update the email address for a specific user:

UPDATE Users
SET email = 'new.email@example.com'
WHERE username = 'john_doe';

DELETE Statement

The DELETE statement is used to remove records from a table.

Syntax:

DELETE FROM table_name
WHERE condition;

Example: Delete a user based on their username:

DELETE FROM Users
WHERE username = 'old_user';

Data Definition Language (DDL)

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

CREATE TABLE Statement

Used to create new tables in the database.

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

ALTER TABLE Statement

Used to modify an existing table's structure.

ALTER TABLE table_name
ADD column_name datatype;

DROP TABLE Statement

Used to delete an entire table and its data.

DROP TABLE table_name;

Advanced Querying

SQL supports complex queries involving joins, subqueries, aggregation, and more.

Joins

Joins are used to combine rows from two or more tables based on a related column between them.

• INNER JOIN: Returns records that have matching values in both tables.
• LEFT JOIN: Returns all records from the left table, and the matched records from the right table.
• RIGHT JOIN: Returns all records from the right table, and the matched records from the left table.
• FULL OUTER JOIN: Returns all records when there is a match in either the left or right table.

Example: Get orders with customer names:

SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

Aggregation Functions

Functions like COUNT(), SUM(), AVG(), MIN(), and MAX() are used to perform calculations on data.

Example: Count the number of orders:

SELECT COUNT(OrderID)
FROM Orders;

For detailed syntax and examples of specific SQL dialects (e.g., T-SQL, PL/SQL), please refer to the specific database vendor documentation.