Skip to main content

SQL Overview

This section will cover a basic overview of Structured Query Language (SQL).

Table of Contents​
  • Overview
  • Types of Databases
    • Relational Database
    • Non-relational Database
  • SQL Statements
    • INSERT Statement
    • SELECT Statement
    • DROP Statement
    • ALTER Statement
    • UPDATE Statement
  • Sorting Results
    • ORDER BY Statement
    • LIMIT Statement
    • WHERE Clause
    • LIKE Clause
  • SQL Operators
    • AND Operator
    • OR Operator
    • NOT Operator
    • Symbol Operator
    • Multiple Operators Precedence

Overview​

SQL is a language that can be used to access and manipulate databases. There are many different types of SQL from different organisations such as Microsoft SQL (MSSQL), Oracle, MySQL, etc.

Types of Databases​

There are two main types of databases in SQL - Relational and Non-relational (NoSQL).

Relational Database​

A relational database uses a schema, a template, to dictate the data structure stored in the database. Tables in a relation database are associated with keys that provide a quick database summary or access to the specific row or column where specific data needs to be reviewed. These tables, also called entities, are all related to each other.

A Relational Database Management System (RDBMS) can help ease the use and management of databases and allow for fine-grained control for security.

Non-relational Database​

A non-relational database (also called as a NoSQL database) does not use tables, rows, and columns or prime keys, relationships, or schemas. Instead, a NoSQL database stores data using various storage models, depending on the type of data stored. This allows NoSQL databases to be very scalable and flexible due to the lack of a defined structure.

There are four common storage models for NoSQL databases:

  • Key-Value
  • Document-Based
  • Wide-Column
  • Graph

Each model above have different ways of storing data. An example will be the Key-Value model which usually stores data in JSON or XML, and have a key for each pair, and stores all its data as its value.

SQL Statements​

This section will cover some basic SQL statements that can be used.

INSERT Statement​

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

INSERT INTO <table_name> VALUES (column1_value, column2_value, column3_value);

An example:

INSERT INTO users VALUES(1, 'admin', 'mypass','2024-07-29');

It is also possible to skip filling columns with default values. An example will be the id or date_of_joining columns if present in a database that contains login details.

INSERT INTO <table_name>(column1,column2) VALUES (column1_value, column2_value);

An example:

INSERT INTO logins(username, password) VALUES('myuser', 'password123');

Multiple values can also be added by separating them with a comma.

INSERT INTO logins(username, password) VALUES ('myuser1', 'myuser2'), ('password', 'securepw');

SELECT Statement​

The SELECT statement can be used to retrieve data in a database. It can also be used to perform other actions.

To use it, we can use the following query.

SELECT * FROM <table_name>;

Breakdown:

  • SELECT - Specify to use the SELECT statement.
  • * - Specify all data, acts as a wildcard.
  • FROM - Specify the location to obtain the data.
  • <table_name> - Specify the table name to obtain the data from.

An example will be displaying all data entries for the table called login:

SELECT * FROM logins;

We can also specify specific columns instead of using the astrisk.

SELECT <column1>,<column2> FROM <table_name>;

DROP Statement​

The DROP statement can be used to remove tables and databases from the server.

DROP TABLE <table_name>;

Note: This statement will permanently delete the table with no confirmation. It should be used with caution and be avoided if not necessary.

ALTER Statement​

The ALTER statement can be used to change the name of any table and any of its fields, delete or add a new column to an existing table.

ALTER TABLE <table_name> ADD <new column_name> INT;

The above query will add the specified column name to the specified table and set the column's datatype to INT.

To rename a column, the RENAME COLUMN statement can be used.

ALTER TABLE <table_name> RENAME COLUMN <old_column_name> TO <new_column_name>;

To change the datatype of a column, we can use the MODIFY statement.

ALTER TABLE <table_name> MODIFY <column_name> <datatype>;

To delete a column, the DROP statement can be used.

ALTER TABLE <table_name> DROP <column_name>;

Examples:

ALTER TABLE logins ADD newcolumn DATE;
ALTER TABLE logins RENAME COLUMN newcolumn mycolumn;
ALTER TABLE logins MODIFY mycolumn INT;
ALTER TABLE logins DROP mycolumn;

UPDATE Statement​

The UPDATE can be used to update specific records within a table, based on its conditions.

UPDATE <table_name> SET <column_name>=<new_value>, <column_name2>=<new_value2> WHERE <condition>;

An example:

UPDATE logins SET password = 'change_password' WHERE id > 1;

The above query will update all passwords in all records where the id is higher than 1.

Sorting Results​

This section will cover some ways to sort the results of a query.

ORDER BY Statement​

To sort results, we can use the ORDER BY statement and specify the column to sort by.

SELECT * FROM <table_name> ORDER BY <column_name>;

By default, the sort is done in ascending order. To specify a order, we can specify it by using methods such as ASC or DESC for ascending and descending respectively.

SELECT * FROM <table_name> ORDER BY <column_name> DESC;

To sort multiple columns, we can specify a secondary sort. An example will be using a secondary sort for duplicate values in the primary column.

SELECT * FROM <table_name> ORDER BY <column_name_1> DESC, <column_name_2> ASC;

An example will be sorting the password column in a logins database and using the ID column to sort if there are duplicate values in the password column.

SELECT * FROM logins ORDER BY passwords DESC, id ASC;

LIMIT Statement​

We can specify the amount of records to return using the LIMIT statement.

SELECT * FROM <table_name> LIMIT <value>;

An example will be selecting only 5 entries from the specified table.

SELECT * FROM users LIMIT 5;

WHERE Clause​

To search for specific data, we can use the WHERE clause to fine-tune results.

SELECT * FROM <table_name> WHERE <column_name> = '<value>';
SELECT * FROM <table_name> WHERE <column_name> > '<value>';

An example will be selecting a specific user from the users table.

SELECT * FROM users WHERE username = 'administrator';

Another example will be selecting all entries from a ID column above a specified value.

SELECT * FROM products WHERE id > 5;

Note: String and data datatypes should be surrounded by single quotes (') or double quotes (") while numbers can be used directly without them.

LIKE Clause​

To select entries with a matching pattern, we can use the LIKE clause.

SELECT * FROM <table_name> WHERE <column_name> LIKE '<value>%';

The percent (%) symbol acts as a wildcard and matches all characters after the specified value while the underscore (_) symbol is used to match exactly one character.

An example will be matching all usernames with the word admin in it.

SELECT * FROM logins WHERE username LIKE 'admin%';

The following example will be an example where we want to match all usernames with exactly five characters.

SELECT * FROM users WHERE username LIKE '_____';

SQL Operators​

This section will cover SQL operators.

AND Operator​

The AND operator takes in two conditions and returns a true or false based on their evaluation.

condition1 AND condition2

If both conditions return "true", the result will be true. However, if one of the conditions is "false", the result will be false. In MySQL, any non-zero value is considered true.

OR Operator​

The OR operator takes in two expressions and returns true when at least one of the conditions evaluates to true.

SELECT 1 = 1 OR 'test' = '123';

The above query will return true as 1=1 will return the value true even when 'test' = '123' returns false.

NOT Operator​

The NOT operator toggles a Boolean value. This means that it will convert a true into false and vice versa.

SELECT NOT 1 = 1;

The above query will return false. This is because 1 = 1 will return the value true which will then be inversed by the NOT operator to false.

Symbol Operators​

Instead of using AND, OR, or NOT, we can use symbols. The &&, ||, and ! represents AND, OR, and NOT respectively.

SELECT 1 = 1 && 'test' = '123';
SELECT 1 = 1 || 'test' = '123';
SELECT ! 1 = 1;

Multiple Operators Precedence​

SQL also supports various other operations such as addition, division, and bitwise. We can craft a query that allows the use of multiple expressions with multiple operations at once. The order of the operation is decided through operator precedence.

The following is a list of common operators and their precedence:

  1. Division (/), Multiplication (*), and Modulus (%)
  2. Addition (+) and subtraction (-)
  3. Comparison (=, >, <, <=, >=, !=, LIKE)
  4. NOT operator (!)
  5. AND operator (&&)
  6. OR operator (||)

The above information can be found in the MariaDB documentation.

An example:

SELECT * FROM users != 'admin' AND id > 3 - 2;

The above query will first evaluate the 3 - 2 which will return a value of 1. After that, it will look for all entries where the username is not admin where the id is greater than 1. Once done, it will apply both conditions with the AND and return true if both are true and false if one or both conditions returns false.