SQL: EVERYONE SHOULD KNOW

8 min readApr 10, 2023

SQL, or Structured Query Language, is a language which is designed to allow both technical and non-technical users query, manipulate, and transform data from a relational database. SQL databases provide safe and scalable storage for millions of websites and mobile applications due to its simplicity.

There are many popular SQL databases like SQLite, MySQL, Postgres, Oracle etc. All these support common SQL language standards but each implementation can differ in the additional features and storage types they support.

1. SELECT query

SELECT statements are used to retrieve data from a SQL database, which are often called as queries. The query in itself is a statement that declares what data we are looking for, where to find it in the database and how to transform that data before it is returned.

The most basic query that we could write for a table of data would be that selects for a couple of columns (properties) with all the rows (instances).

Syntax

SELECT column, another_column, ...
FROM table_name;

This will result in a two-dimensional set of rows and columns with only the columns that are requested. If we want all the columns of data from a table, then we can use asterisk (*) in place of the column names.

SELECT *
FROM table_name;

2. WHERE (Queries with constraints)

If the table contains millions of rows and columns, it would be inefficient to read through all the rows. In order to filter certain results, WHERE clause is used. The clause is applied to each row of data by checking the specific column values to determine whether it should be included in the results or not.

SELECT column, another_column,...
FROM table_name
WHERE <condition>
     AND/OR <another condition>
     AND/OR ...;

We also some useful operators that are mentioned below for numerical data (ie. integer or floating point), we can also construct clauses by joining numerous AND or OR logical keywords (ie. total≥45 AND count≤ 8).

SQL doesn’t demand to write the keywords all capitalized, but for people to distinguish SQL keywords from the column and table names, we use this convention of writing the SQL keywords in capital letters.

Also there are some useful operators for text data, they also support things like case-insensitive string comparison and wildcard pattern matching etc.

The strings must be quoted so that the query parser can distinguish words in the string from SQL keywords

3. Filtering and Sorting query results

DISTINCT keyword is used to discard rows that have a duplicate column value. Since DISTINCT keyword will blindly remove duplicate rows, there are some ways to discard duplicates based on specific columns using the grouping and the GROUP BY clause.

SELECT DISCTINCT column_name, another_column,...
FROM table_name
WHERE condition(s);

Ordering results

The result set can be sorted in either ascending or descending order using the ORDER BY keyword.

Records are typically sorted using the ORDER BY keyword in ascending order by default.

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

#ORDER BY Several columns
SELECT * FROM Customers
ORDER BY Country ASC, CustomerName DESC;

4. INSERT INTO Statement

To add new records to a table, use the INSERT INTO statement.

INSERT INTO statement can be used in two ways:

a) Specify both the values and the column names that are to be inserted

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

b) If the values are added for all the columns of the table, then there is no need to specify the column names in the query

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

It is also possible to only insert data in specific columns by mentioning the column names in which the data to be inserted.

5. UPDATE Statement

A table’s current records can be changed using the UPDATE statement.

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

The WHERE clause that determines how many records will be updated.

6. DELETE Statement

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

DELETE FROM table_name WHERE condition;

Without erasing the table itself, it is possible to remove every row from a table. This indicates that the properties, table structure, and indexes will all be preserved.

DELETE FROM table_name;

7. LIMIT Clause

The number of rows to return is specified using the LIMIT clause.

With huge tables with tens of thousands of records, the LIMIT clause is helpful. Performance may be affected if many records are returned.

SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;

8. COUNT() Function

The number of rows that match a certain criterion are returned by the COUNT() function.

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

AVG(), SUM() Function

AVG() function and SUM() function returns the average value and total sum of a numeric column respectively.

/* AVG() Syntax */
SELECT AVG(column_name)
FROM table_name
WHERE condition;

/* SUM() Syntax */
SELECT SUM(column_name)
FROM table_name
WHERE condition;

9. MIN() and MAX() Functions

The MIN() function provides the selected column’s lowest value.

The greatest value in the chosen column is returned by the MAX() method.

/*MIN() Syntax*/
SELECT MIN(column_name)
FROM table_name
WHERE condition;

/*MAX() Syntax*/
SELECT MAX(column_name)
FROM table_name
WHERE condition;

10. LIKE Operator

To look for a specific pattern in a column, use the LIKE operator in a WHERE clause.

Two wildcards are frequently combined with the LIKE operator:

The percent sign (%) denotes a character value of zero, one, or more.
The underscore character (_) stands for a single character.

SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;

Wildcard Character

11. IN Operator

With a WHERE clause, you can provide multiple values by using the IN operator.

For multiple OR conditions, the IN operator serves as a shorthand.

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

--OR

SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);

12. BETWEEN Operator

The BETWEEN operator chooses values from a predetermined range. The values could be text, integers, or dates.

The BETWEEN operator includes both the begin and finish variables.

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

NOT BETWEEN

The NOT BETWEEN operator chooses values that are not within the predetermined range.

13. JOIN

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

Types of JOINS

(INNER) JOIN: Returns records that have matching values in both tables
LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
RIGHT (OUTER) 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

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

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

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

14. GROUP BY Statement

The GROUP BY statement groups rows that have the same values into summary rows, like “find the number of customers in each country”.

The COUNT(), MAX(), MIN(), SUM(), and AVG() aggregate functions are frequently used with the GROUP BY statement to group the result set by one or more columns.

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

15. HAVING Clause

The HAVING clause was added because the WHERE keyword cannot be used with aggregate functions.

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

16. CREATE TABLE

To create new table in a database, CREATE TABLE is used.

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);

The column parameters specify the names of the columns in the table.
The datatype parameter indicates the type of data that each column in the table can carry, such as varchar, integer, date, etc.

Create Table using another table

If you use CREATE TABLE to create a new table from an existing table, the new table will be populated with the existing data from the old table. The new table gets the same column definitions, and all columns or specified columns can be selected.

CREATE TABLE new_table_name AS
    SELECT column1, column2,...
    FROM existing_table_name
    WHERE ....;

CONSTRAINTS

With the CREATE TABLE or ALTER TABLE statements, constraints can be set up either during the table’s creation or after it has already been formed.

CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    column3 datatype constraint,
    ....
);

Commonly used in SQL:

NOT NULL - Ensures that a column cannot have a NULL value
UNIQUE - Ensures that all values in a column are different
PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
FOREIGN KEY - Prevents actions that would destroy links between tables
CHECK - Ensures that the values in a column satisfies a specific condition
DEFAULT - Sets a default value for a column if no value is specified
CREATE INDEX - Used to create and retrieve data from the database very quickly

17. DROP TABLE

DROP TABLE statement is used to drop an existing table in a database.

DROP TABLE table_name;

18. TRUNCATE TABLE

TRUNCATE TABLE is used to delete the data inside a table, but not the table itself.

TRUNCATE TABLE table_name;

19. ALTER TABLE

Columns in an existing table can be added, removed, or modified using the ALTER TABLE statement.

A table’s existing constraints can be added or removed using the ALTER TABLE statement.

ALTER TABLE table_name
ADD column_name datatype;

ALTER TABLE table_name
DROP COLUMN column_name;

ALTER TABLE table_name
RENAME COLUMN old_name to new_name;

20. CHECK Constraint

The value range that can be entered into a column is restricted by the CHECK constraint.

Only specific values will be permitted for a column if you define a CHECK constraint on it.

A table’s CHECK constraint can be used to restrict the values in specific columns based on the values of other columns in the same row.

--EXAMPLE
CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CHECK (Age>=18)
);

21. VIEW Statement

A view in SQL is a fictitious table built from the results of a SQL statement.

Like a true table, a view also has rows and columns. A view contains fields from one or more actual database tables.

A view can be extended with SQL statements and functions to present data as though it were drawn from a single table.

The CREATE VIEW statement produces a view. A view can be updated with the CREATE OR REPLACE VIEW statement.

--CREATE VIEW
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

--UPDATE VIEW
CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

22. UNIQUE

The UNIQUE constraint guarantees that each value in a column is unique.

A column or collection of columns’ uniqueness is ensured by the UNIQUE and PRIMARY KEY requirements.

A UNIQUE constraint comes with a PRIMARY KEY constraint by default.

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    UNIQUE (ID)
);

The blog covered the basic SQL syntax for SELECT, INSERT, UPDATE, DELETE statements, and provided examples of how to use them effectively. It also explained how to join tables, filter data, and aggregate data using SQL.