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.
Used to create a new database or table.
Syntax:
sqlCREATE DATABASE <DATABASE NAME>;
CREATE TABLE <TABLE NAME> ( <COLUMN NAME> <DATA TYPE>, … );
Used to delete an existing database or table.
Syntax:
sqlDROP DATABASE <DATABASE NAME>;
DROP TABLE <TABLE NAME>;
Used to delete all information in the table but doesn’t delete the table itself.
Syntax:
sqlTRUNCATE TABLE <TABLE NAME>;
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>;
Used to create a backup of an existing database.
Syntax:
sqlBACKUP DATABASE <DATABASE NAME> TO DISK = ‘<PATH>’;
Used to insert new tuples (rows) in a table.
Syntax:
sqlINSERT INTO <TABLE NAME> (<COLUMN1>, <COLUMN2>, …) VALUES (<VALUE1>, <VALUE2>, …);
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.
Used to modify existing records in a table.
Syntax:
sqlUPDATE <TABLE NAME> SET <COLUMN NAME> = <NEW VALUE> WHERE <CONDITION>;
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.
Equivalent to the set operations: union, intersection, and difference.
Syntax:
sql<FIRST SELECT STATEMENT> UNION / INTERSECT / EXCEPT <SECOND SELECT STATEMENT>;
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>);
Used to check whether a value is NULL.
Syntax:
sql<ATTRIBUTE NAME> IS (NOT) NULL;
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>;
Used to ensure a certain condition is always met in the database.
Syntax:
sqlCREATE ASSERTION <ASSERTION NAME> CHECK (<CONDITION>);
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>;
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
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:
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>, …);
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>;
Used to eliminate duplicates.
Syntax:
sqlSELECT DISTINCT <ATTRIBUTE LIST> FROM <TABLE NAME>;
Used to allow duplicates.
Syntax:
sqlSELECT ALL <ATTRIBUTE LIST> FROM <TABLE NAME>;
Used for string comparison.
Syntax:
sql<ATTRIBUTE> LIKE <PATTERN>;
%: Replaces an arbitrary number of characters.
_: Replaces one character.
+: Add
–: Subtract
*****: Multiply
/: Divide
Order By is used to order the resulting tuples.
Syntax:
sqlSELECT <ATTRIBUTE LIST> FROM <TABLE NAME> ORDER BY <ATTRIBUTE> ASC / DESC;
Can be used with (=, >, >=, <, <=, <>) to compare a value with a set.
Syntax:
sqlSELECT <ATTRIBUTE LIST> FROM <TABLE NAME> WHERE <VALUE> > ALL (<ANOTHER SELECT QUERY>);
Compares two sets and returns true if one set contains the other.
Checks whether the result of a nested query is empty or not.
Checks if the table has duplicates.
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 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.
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.
What is ETL? A Comprehensive Guide to Extract, Transform, Load In today's data-driven world, businesses…
Artificial intelligence (AI) has become a powerful tool for accelerating learning. Whether you’re mastering a…
When preparing for the PMP® (Project Management Professional) exam, finding the right study materials and…
NVIDIA Launches Free AI Courses: Top 6 Courses to Explore in 2024 NVIDIA has just…
Running a business is both rewarding and challenging. As an entrepreneur or business leader, you…
Understanding API Pagination Methods APIs often return a large set of data that can be…