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:
sqlCREATE DATABASE <DATABASE NAME>;
CREATE TABLE <TABLE NAME> ( <COLUMN NAME> <DATA TYPE>, … );
Drop
Used to delete an existing database or table.
Syntax:
sqlDROP DATABASE <DATABASE NAME>;
DROP TABLE <TABLE NAME>;
Truncate
Used to delete all information in the table but doesn’t delete the table itself.
Syntax:
sqlTRUNCATE TABLE <TABLE NAME>;
Alter
Used to delete, add or modify constraints or columns in a table.
Syntax:
sqlALTER 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:
sqlBACKUP DATABASE <DATABASE NAME> TO DISK = ‘<PATH>’;
Data Manipulation
Insert
Used to insert new tuples (rows) in a table.
Syntax:
sqlINSERT INTO <TABLE NAME> (<COLUMN1>, <COLUMN2>, …) VALUES (<VALUE1>, <VALUE2>, …);
Delete
Used to delete tuples (rows) from a table.
Syntax:
sqlDELETE 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:
sqlUPDATE <TABLE NAME> SET <COLUMN NAME> = <NEW VALUE> WHERE <CONDITION>;
Select
Used to select data from a table.
Syntax:
sqlSELECT <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:
sqlSELECT <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:
sqlSELECT <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:
sqlCREATE ASSERTION <ASSERTION NAME> CHECK (<CONDITION>);
Trigger
Triggers are activated when a defined action is executed to the table.
Syntax:
sqlCREATE 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:
sqlON 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:
sqlSELECT <ATTRIBUTE LIST> FROM <TABLE 1>, <TABLE 2>;
Handling Duplicates
Distinct
Used to eliminate duplicates.
Syntax:
sqlSELECT DISTINCT <ATTRIBUTE LIST> FROM <TABLE NAME>;
All
Used to allow duplicates.
Syntax:
sqlSELECT 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:
sqlSELECT <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:
sqlSELECT <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.
