Comprehensive Guide to SQL: Essential Commands and Concepts

September 5, 2024

Structured Query Language (SQL) is the standard language for interacting with relational databases. Mastering SQL is crucial for database management, data manipulation, and querying information efficiently. This guide serves as an all-encompassing reference to essential SQL commands and concepts, making it an invaluable resource for both beginners and experienced users.

Creating and Managing Databases and Tables

Create

Used to create a new database or table.

Syntax:

sql

CREATE DATABASE <DATABASE NAME>;

CREATE TABLE <TABLE NAME> ( <COLUMN NAME> <DATA TYPE>, … );

Drop

Used to delete an existing database or table.

Syntax:

sql

DROP DATABASE <DATABASE NAME>;

DROP TABLE <TABLE NAME>;

Truncate

Used to delete all information in the table but doesn’t delete the table itself.

Syntax:

sql

TRUNCATE TABLE <TABLE NAME>;

Alter

Used to delete, add or modify constraints or columns in a table.

Syntax:

sql

ALTER TABLE <TABLE NAME> ADD <COLUMN NAME> <DATA TYPE>; ALTER TABLE <TABLE NAME> DROP COLUMN <COLUMN NAME>; ALTER TABLE <TABLE NAME> ALTER COLUMN <COLUMN NAME> <DATA TYPE>;

Backup

Used to create a backup of an existing database.

Syntax:

sql

BACKUP DATABASE <DATABASE NAME> TO DISK = ‘<PATH>’;

Data Manipulation

Insert

Used to insert new tuples (rows) in a table.

Syntax:

sql

INSERT INTO <TABLE NAME> (<COLUMN1>, <COLUMN2>, …) VALUES (<VALUE1>, <VALUE2>, …);

Delete

Used to delete tuples (rows) from a table.

Syntax:

sql

DELETE FROM <TABLE NAME> WHERE <CONDITION>;

If you omit the WHERE clause, all rows will be deleted.

Update

Used to modify existing records in a table.

Syntax:

sql

UPDATE <TABLE NAME> SET <COLUMN NAME> = <NEW VALUE> WHERE <CONDITION>;

Select

Used to select data from a table.

Syntax:

sql

SELECT <ATTRIBUTE LIST> FROM <TABLE NAME> WHERE <CONDITION>;

If you omit the WHERE clause, all rows in the table are returned.

Advanced Operations

Union, Intersect, Except

Equivalent to the set operations: union, intersection, and difference.

Syntax:

sql

<FIRST SELECT STATEMENT> UNION / INTERSECT / EXCEPT <SECOND SELECT STATEMENT>;

In

Compares a value with a set of values, returns true if the value is one of the elements in the set.

Syntax:

sql

SELECT <ATTRIBUTE LIST> FROM <TABLE NAME> WHERE <VALUE> IN (<ANOTHER SELECT QUERY>);

Null

Used to check whether a value is NULL.

Syntax:

sql

<ATTRIBUTE NAME> IS (NOT) NULL;

Join

Used to join two tables based on a related column between them.

Syntax:

sql

SELECT <ATTRIBUTES LIST> FROM <TABLE 1> JOIN <TABLE 2> ON <JOIN CONDITION> WHERE <SELECTION CONDITION>;

Assertion

Used to ensure a certain condition is always met in the database.

Syntax:

sql

CREATE ASSERTION <ASSERTION NAME> CHECK (<CONDITION>);

Trigger

Triggers are activated when a defined action is executed to the table.

Syntax:

sql

CREATE TRIGGER <TRIGGER NAME> BEFORE / AFTER INSERT / UPDATE / DELETE ON <TABLE NAME> FOR EACH ROW <TRIGGER BODY>;

Data Types

Understanding data types is essential for defining the structure of the data you will store in your database.

Numeric: INT, SMALLINT, DECIMAL(i, j)
String: CHAR, CHAR(n), VARCHAR(n)
Bit String: BIT, BIT(n)
Date and Time: DATE, TIME, TIME(i), TIMESTAMP

Referential Triggered Actions

Used to set what happens on updating or deleting a tuple (row) in the database that references another row.

Syntax:

sql

ON DELETE <OPTION> ON UPDATE <OPTION>;

Options:

SET NULL
SET DEFAULT
CASCADE

Renaming (Aliasing)

Relation and attribute names can be renamed for convenience or to remove ambiguity using the keyword AS.

Syntax:

sql

<TABLE NAME> AS <NEW TABLE NAME> (<NEW ATTRIBUTE 1 NAME>, …);

Cross Product

Used to produce a result table that has the number of rows of the first table multiplied by the number of rows of the second table.

Syntax:

sql

SELECT <ATTRIBUTE LIST> FROM <TABLE 1>, <TABLE 2>;

Handling Duplicates

Distinct

Used to eliminate duplicates.

Syntax:

sql

SELECT DISTINCT <ATTRIBUTE LIST> FROM <TABLE NAME>;

All

Used to allow duplicates.

Syntax:

sql

SELECT ALL <ATTRIBUTE LIST> FROM <TABLE NAME>;

String Comparisons

Like

Used for string comparison.

Syntax:

sql

<ATTRIBUTE> LIKE <PATTERN>;

%: Replaces an arbitrary number of characters.
_: Replaces one character.

Arithmetic Operators

+: Add
–: Subtract
*****: Multiply
/: Divide

Ordering

Order By is used to order the resulting tuples.

Syntax:

sql

SELECT <ATTRIBUTE LIST> FROM <TABLE NAME> ORDER BY <ATTRIBUTE> ASC / DESC;

Set Comparisons

Any and All

Can be used with (=, >, >=, <, <=, <>) to compare a value with a set.

Syntax:

sql

SELECT <ATTRIBUTE LIST> FROM <TABLE NAME> WHERE <VALUE> > ALL (<ANOTHER SELECT QUERY>);

Contains

Compares two sets and returns true if one set contains the other.

Exists

Checks whether the result of a nested query is empty or not.

Unique

Checks if the table has duplicates.

Types of Join

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 left or right table.

Aggregate Functions

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

COUNT: Counts how many rows in a particular column.
SUM: Adds together all the values in a particular column.
MIN: Returns the minimum value in a column.
MAX: Returns the maximum value in a column.
AVG: Returns the average of a group of selected values.

Conclusion: Comprehensive Guide to SQL: Essential Commands and Concepts

SQL is a powerful and versatile language that is fundamental for interacting with relational databases. This guide covers essential SQL commands and concepts, providing a comprehensive reference for both beginners and experienced practitioners. Whether you’re creating databases, manipulating data, or querying information, understanding these SQL principles is crucial for effective database management.