SQL: Structured Query Language

Introduction to SQL

SQL (Structured Query Language) is a standard language for managing and manipulating databases. It is used to communicate with a database.

SQL statements are used to perform tasks such as updating data on a database, or retrieving data from a database.

SQL is a declarative language, meaning you tell the database what you want, and it figures out how to get it.

Basic Queries

SELECT

The SELECT statement is used to select data from a database.

The data returned is stored in a result table, called the result-set.

SELECT column1, column2 FROM table_name;
SELECT * FROM table_name;

FROM

The FROM clause is used to specify the table from which to retrieve the data.

Note: You must have at least one table in your database. You can also specify multiple tables in the FROM clause, separated by commas or using JOIN clauses.

WHERE

The WHERE clause is used to filter records. It extracts only those records that fulfill a specified condition.

SELECT * FROM Customers WHERE Country = 'Mexico';

Common operators used in the WHERE clause include:

= Equal to
> Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to
<> Not equal to
BETWEEN Between an inclusive range
LIKE Search for a pattern
IN Specify multiple possible values for a column

ORDER BY

The ORDER BY clause sorts the result-set in ascending or descending order.

ASC - Ascending order (this is the default)

DESC - Descending order

SELECT column1, column2
FROM table_name
ORDER BY column1 ASC;

SELECT column1, column2
FROM table_name
ORDER BY column1 DESC;

LIMIT

The LIMIT clause is used to restrict the number of rows returned by a query.

SELECT column1, column2
FROM table_name
LIMIT 10;

Note: The exact syntax for limiting results may vary slightly between different SQL database systems (e.g., TOP in SQL Server, ROWNUM in Oracle).

Data Manipulation Language (DML)

INSERT INTO

The INSERT INTO statement is used to add new records to a table.

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

INSERT INTO table_name
VALUES ('value1', value2, value3);

UPDATE

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

UPDATE table_name
SET column1 = 'new_value', column2 = new_value2
WHERE condition;

Caution: If you omit the WHERE clause, all records in the table will be updated!

DELETE

The DELETE statement is used to delete existing records from a table.

DELETE FROM table_name
WHERE condition;

Caution: If you omit the WHERE clause, all records in the table will be deleted! To delete all rows, you can also use the TRUNCATE TABLE statement, which is generally faster.

Data Definition Language (DDL)

CREATE TABLE

The CREATE TABLE statement is used to create a new table in a database.

CREATE TABLE table_name (
    column1 datatype PRIMARY KEY,
    column2 datatype NOT NULL,
    column3 datatype,
    column4 datatype DEFAULT 'somevalue'
);

Common data types include:

INT or INTEGER: Stores whole numbers.
VARCHAR(n): Stores variable-length strings up to 'n' characters.
TEXT: Stores long strings.
DATE: Stores dates.
DECIMAL(p,s) or NUMERIC(p,s): Stores precise numbers with 'p' total digits and 's' digits after the decimal point.
BOOLEAN: Stores TRUE or FALSE values.

ALTER TABLE

The ALTER TABLE statement is used to modify an existing table.

ADD COLUMN: Adds a new column.
DROP COLUMN: Removes a column.
MODIFY COLUMN: Modifies a column's data type or constraints.
ADD CONSTRAINT: Adds a constraint.
DROP CONSTRAINT: Removes a constraint.

ALTER TABLE table_name
ADD column_name datatype;

ALTER TABLE table_name
DROP COLUMN column_name;

DROP TABLE

The DROP TABLE statement is used to delete an existing table from a database.

DROP TABLE table_name;

Caution: This action cannot be undone. All data in the table will be lost.

JOIN Clauses

JOIN clauses 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.

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

LEFT JOIN

Returns all records from the left table, and the matched records from the right table. The result is NULL where there is no match.

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

RIGHT JOIN

Returns all records from the right table, and the matched records from the left table. The result is NULL where there is no match.

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

FULL OUTER JOIN

Returns all records when there is a match in either left or right table. Returns NULL in the place where there is no match.

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name;

Note: FULL OUTER JOIN is not supported by all database systems (e.g., MySQL).

Aggregate Functions

Aggregate functions perform a calculation on a set of values and return a single value.

COUNT

Returns the number of rows that match a specified criterion.

SELECT COUNT(column_name)
FROM table_name;

SELECT COUNT(*)
FROM table_name;

SUM

Returns the total sum of a numeric column.

SELECT SUM(column_name)
FROM table_name;

AVG

Returns the average value of a numeric column.

SELECT AVG(column_name)
FROM table_name;

MIN

Returns the minimum value of a column.

SELECT MIN(column_name)
FROM table_name;

MAX

Returns the maximum value of a column.

SELECT MAX(column_name)
FROM table_name;

Aggregate functions can be used with the GROUP BY clause to perform calculations on groups of rows.

SELECT column1, COUNT(column2)
FROM table_name
GROUP BY column1;

SQL Docs