SQL Language Reference

Introduction to SQL Language

Structured Query Language (SQL) is a standard language for managing and manipulating relational databases. This reference provides a comprehensive overview of SQL syntax, data types, operators, statements, and functions.

Whether you are a beginner or an experienced database professional, this guide aims to be your go-to resource for all things SQL.

Table of Contents

Data Types

SQL defines various data types to store different kinds of information. Choosing the appropriate data type is crucial for data integrity, performance, and storage efficiency.

Numeric Types

Used for storing numerical values.

  • INT, INTEGER: Whole numbers.
  • DECIMAL, NUMERIC: Exact precision numbers.
  • FLOAT, REAL: Approximate precision floating-point numbers.
  • BIGINT: Larger integer values.

String Types

Used for storing character data.

  • VARCHAR(n): Variable-length character string.
  • CHAR(n): Fixed-length character string.
  • TEXT: Long text strings.
  • NVARCHAR(n): Variable-length Unicode character string.

Date and Time Types

Used for storing date and time values.

  • DATE: Stores date values (year, month, day).
  • TIME: Stores time values (hour, minute, second).
  • DATETIME: Stores date and time values.
  • TIMESTAMP: Stores date and time, often with automatic updates.

Other Types

  • BOOLEAN: Stores TRUE or FALSE values.
  • BLOB: Binary Large Object, for storing large binary data.
  • JSON: Stores JSON formatted data.

Operators

Operators perform operations on one or more operands (values or columns).

Arithmetic Operators

  • +: Addition
  • -: Subtraction
  • *: Multiplication
  • /: Division
  • %: Modulo (remainder)

Comparison Operators

  • =: Equal to
  • !=, <>: Not equal to
  • <: Less than
  • >: Greater than
  • <=: Less than or equal to
  • >=: Greater than or equal to
  • BETWEEN: Between an inclusive range
  • LIKE: Pattern matching
  • IN: Value is in a list

Logical Operators

  • AND: Combines conditions, true if all are true.
  • OR: Combines conditions, true if any is true.
  • NOT: Negates a condition.

SQL Statements

SQL statements are commands used to interact with a database.

Data Definition Language (DDL)

DDL statements are used to define and manage database structures.

CREATE TABLE

Creates a new table in the database.

Syntax Example:

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

ALTER TABLE

Modifies an existing table structure.

Syntax Example:

ALTER TABLE Customers
ADD COLUMN PhoneNumber VARCHAR(20);

DROP TABLE

Deletes a table from the database.

Syntax Example:

DROP TABLE Customers;

Data Manipulation Language (DML)

DML statements are used to manage data within database objects.

SELECT

Retrieves data from one or more tables.

Syntax Example:

SELECT FirstName, LastName
FROM Customers
WHERE Country = 'USA';

INSERT

Adds new records into a table.

Syntax Example:

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

UPDATE

Modifies existing records in a table.

Syntax Example:

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

DELETE

Removes records from a table.

Syntax Example:

DELETE FROM Customers
WHERE CustomerID = 1;

Data Control Language (DCL)

DCL statements manage permissions and access to database objects.

GRANT

Gives users privileges to perform specific actions on database objects.

Syntax Example:

GRANT SELECT ON Customers TO Public;

REVOKE

Removes user privileges.

Syntax Example:

REVOKE INSERT ON Orders FROM SalesTeam;

Built-in Functions

SQL provides a rich set of built-in functions for performing calculations, manipulating strings, working with dates, and more.

Aggregate Functions

These functions operate on a set of rows and return a single value.

  • COUNT(): Counts the number of rows.
  • SUM(): Calculates the sum of values in a column.
  • AVG(): Computes the average of values.
  • MIN(): Finds the minimum value.
  • MAX(): Finds the maximum value.

Syntax Example:

SELECT COUNT(CustomerID) AS TotalCustomers
FROM Customers;

Scalar Functions

These functions operate on a single value and return a single value.

  • UPPER(string): Converts a string to uppercase.
  • LOWER(string): Converts a string to lowercase.
  • SUBSTRING(string, start, length): Extracts a part of a string.
  • GETDATE(): Returns the current date and time.
  • ROUND(number, decimals): Rounds a number.

Syntax Example:

SELECT UPPER(FirstName) AS UppercaseName
FROM Customers
WHERE CustomerID = 2;

Advanced Topics

Explore more complex SQL concepts and techniques.

  • Joins: Combining rows from two or more tables (INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN).
  • Subqueries: Queries nested inside other SQL queries.
  • Indexes: Data structures that improve the speed of data retrieval operations.
  • Views: Virtual tables based on the result-set of an SQL statement.
  • Transactions: A sequence of database operations performed as a single logical unit of work.
  • Stored Procedures and Triggers: Precompiled SQL code stored in the database for reuse and automated execution.

Important Note:

Specific syntax and available functions may vary slightly between different SQL database systems (e.g., SQL Server, MySQL, PostgreSQL, Oracle). Always refer to the specific documentation for your database management system for exact details.