SQL API Reference

Introduction to the SQL API

The SQL API (Application Programming Interface) allows developers to interact with databases using the Structured Query Language. This reference details the syntax, parameters, and usage of various SQL commands and constructs.

Understanding the nuances of each command is crucial for efficient and secure database management.

SELECT Statement

Retrieves data from one or more tables.

Syntax


                    SELECT [DISTINCT] column1, column2, ...

                    FROM table_name

                    [WHERE condition]

                    [GROUP BY column1, column2, ...]

                    [HAVING condition]

                    [ORDER BY column1 [ASC | DESC], ...];

Description

The SELECT statement is the fundamental command for querying data. You can specify which columns to retrieve, filter rows using a WHERE clause, group results with GROUP BY, filter groups with HAVING, and sort the output with ORDER BY.

Parameters

DISTINCT: Optional. Removes duplicate rows from the result set.
column1, column2, ...: The columns you want to retrieve. Use * to select all columns.
FROM table_name: The table(s) from which to retrieve data.
WHERE condition: Optional. Filters rows based on a specified condition.
GROUP BY column1, column2, ...: Optional. Groups rows that have the same values in specified columns into summary rows.
HAVING condition: Optional. Filters groups based on a specified condition.
ORDER BY column1 [ASC | DESC], ...: Optional. Sorts the result set in ascending (ASC) or descending (DESC) order.

Example

SELECT customer_name, email
FROM customers
WHERE country = 'USA'
ORDER BY customer_name ASC;

INSERT INTO Statement

Adds new rows of data to a table.

Syntax (Specify Columns)


                    INSERT INTO table_name (column1, column2, column3, ...)

                    VALUES (value1, value2, value3, ...);

Syntax (All Columns)


                    INSERT INTO table_name

                    VALUES (value1, value2, value3, ...);

Description

Use INSERT INTO to add records to a table. You can specify the columns and their corresponding values, or if you are providing values for all columns in the correct order, you can omit the column list.

Parameters

table_name: The name of the table to insert data into.
(column1, column2, ...): Optional. The names of the columns you are providing values for.
VALUES (value1, value2, ...): The values to be inserted. The number and order of values must match the specified columns (or the table's column order if columns are omitted).

Example

INSERT INTO products (product_name, price, stock)
VALUES ('Laptop', 1200.50, 50);

UPDATE Statement

Modifies existing records in a table.

Syntax


                    UPDATE table_name

                    SET column1 = value1, column2 = value2, ...

                    [WHERE condition];

Description

The UPDATE statement is used to change values in existing rows. The SET clause specifies which columns to update and their new values. The WHERE clause is crucial for specifying which rows to modify; without it, all rows in the table will be updated.

Parameters

table_name: The name of the table to update.
SET column1 = value1, ...: Specifies the columns to update and their new values.
WHERE condition: Optional. Filters rows to update. If omitted, all rows are affected.

Example

UPDATE employees
SET salary = salary * 1.10
WHERE department = 'Sales';

DELETE Statement

Removes rows from a table.

Syntax


                    DELETE FROM table_name

                    [WHERE condition];

Description

The DELETE statement removes one or more records from a table. The WHERE clause is essential for specifying which rows to delete. If the WHERE clause is omitted, all records in the table will be deleted.

Parameters

table_name: The name of the table from which to delete rows.
WHERE condition: Optional. Filters rows to delete. If omitted, all rows are deleted.

Example

DELETE FROM orders
WHERE order_date < '2023-01-01';

CREATE TABLE Statement

Creates a new table in the database.

Syntax


                    CREATE TABLE table_name (

                        column1 datatype constraints,

                        column2 datatype constraints,

                        ...

                        [table constraints]

                    );

Description

Use CREATE TABLE to define the structure of a new table, including its name, columns, data types for each column, and any constraints (like PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE).

Parameters

table_name: The name for the new table.
column1 datatype constraints: Defines a column with its name, data type (e.g., INT, VARCHAR(255), DATE), and optional constraints.
table constraints: Optional. Constraints that apply to the table as a whole, such as composite primary keys or unique constraints on multiple columns.

Example

CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    registration_date DATE
);

ALTER TABLE Statement

Modifies the structure of an existing table.

Syntax (Add Column)


                    ALTER TABLE table_name

                    ADD COLUMN column_name datatype constraints;

Syntax (Drop Column)


                    ALTER TABLE table_name

                    DROP COLUMN column_name;

Syntax (Modify Column)


                    ALTER TABLE table_name

                    MODIFY COLUMN column_name new_datatype new_constraints;

Description

ALTER TABLE is used to add, delete, or modify columns in an existing table. It's a powerful tool for evolving your database schema without discarding existing data.

Parameters

table_name: The name of the table to alter.
ADD COLUMN column_name datatype constraints: Adds a new column with its definition.
DROP COLUMN column_name: Removes a column and its data.
MODIFY COLUMN column_name new_datatype new_constraints: Changes the data type or constraints of an existing column.

Example

ALTER TABLE products
ADD COLUMN description TEXT;

DROP TABLE Statement

Deletes an existing table and all its data.

Syntax


                    DROP TABLE table_name;

Description

The DROP TABLE statement permanently removes a table and all the data it contains. This operation is irreversible.

Parameters

table_name: The name of the table to delete.

Example

DROP TABLE temporary_data;

SQL Data Types - Numeric

Data types for representing numerical values.

Common Numeric Types

INT or INTEGER: Whole numbers.
DECIMAL(precision, scale) or NUMERIC(precision, scale): Exact fixed-point numbers.
FLOAT or REAL: Approximate floating-point numbers.
DOUBLE PRECISION: Double-precision floating-point numbers.
BIGINT: Large whole numbers.

Description

Choosing the correct numeric type is important for accuracy and storage efficiency. For exact values like currency, use DECIMAL. For scientific calculations, FLOAT or DOUBLE PRECISION might be more appropriate.

SQL Data Types - String

Data types for representing character strings.

Common String Types

VARCHAR(n): Variable-length character string up to n characters.
CHAR(n): Fixed-length character string of exactly n characters (padded with spaces if shorter).
TEXT: For long text strings (size often depends on the database system).
NVARCHAR(n): Variable-length string supporting Unicode characters.

Description

VARCHAR is generally preferred over CHAR unless you need fixed-length strings, as it saves space by only storing the characters actually used. TEXT is suitable for longer descriptions or articles.

SQL Data Types - Date & Time

Data types for representing date and time values.

Common Date & Time Types

DATE: Stores a date (year, month, day).
TIME: Stores a time (hour, minute, second).
DATETIME: Stores a combination of date and time.
TIMESTAMP: Stores a point in time, often with automatic updates upon record modification.
INTERVAL: Represents a duration of time.

Description

These types are essential for tracking events, scheduling, and performing time-based queries. The exact format and capabilities may vary slightly between different SQL database systems.

Stored Procedures

Precompiled SQL code stored in the database.

Description

Stored procedures are sets of SQL statements and control logic compiled and saved in the database. They can accept parameters, return values, and perform complex operations. Using stored procedures can improve performance, enhance security, and promote code reusability.

Benefits

Performance: Precompiled code runs faster.
Reusability: Call the same procedure from multiple applications.
Security: Grant permissions to execute procedures without granting direct table access.
Reduced Network Traffic: Send a single call instead of multiple SQL statements.

Example (Conceptual)

-- Example for creating a stored procedure (syntax varies by RDBMS)
CREATE PROCEDURE GetCustomerOrders (@CustomerID INT)
AS
BEGIN
    SELECT * FROM orders
    WHERE customer_id = @CustomerID;
END;

Triggers

Special stored procedures that execute automatically in response to data modification events.

Description

Triggers are database objects that are associated with a table and are automatically invoked when a specific event occurs on that table, such as an INSERT, UPDATE, or DELETE operation. They are often used for maintaining data integrity, enforcing business rules, or auditing changes.

Event Types

BEFORE INSERT
AFTER INSERT
BEFORE UPDATE
AFTER UPDATE
BEFORE DELETE
AFTER DELETE

Example (Conceptual)

-- Example for creating a trigger (syntax varies by RDBMS)
CREATE TRIGGER TRG_UpdateProductTimestamp
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
    UPDATE products
    SET last_modified = CURRENT_TIMESTAMP
    WHERE product_id = NEW.product_id;
END;

Transactions

A sequence of operations performed as a single logical unit of work.

Description

Transactions are fundamental for ensuring data consistency and integrity in databases. They follow the ACID properties: Atomicity, Consistency, Isolation, and Durability.

Key Commands

START TRANSACTION; (or BEGIN TRANSACTION;): Initiates a new transaction.
COMMIT;: Saves all changes made within the transaction.
ROLLBACK;: Undoes all changes made within the transaction.
SAVEPOINT savepoint_name;: Creates a point within a transaction to which you can later roll back.

ACID Properties

Atomicity: Ensures that all operations within a transaction are completed successfully, or none of them are. The transaction is treated as a single, indivisible unit.
Consistency: Ensures that a transaction brings the database from one valid state to another, maintaining all database rules and constraints.
Isolation: Ensures that concurrent transactions do not interfere with each other. Each transaction appears to execute in isolation.
Durability: Ensures that once a transaction has been committed, its changes are permanent and will survive system failures (e.g., power outages).

Example

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

MSDN Documentation