SQL Syntax

SQLBolt - Learn SQL - Introduction to SQL

Welcome to SQLBolt, a series of interactive lessons and exercises designed to help you quickly learn SQL right in your browser. SQL, or Structured Query Language, is a language designed to allow both technical and non-technical users query, manipulate, and transform data from a relational database.

https://sqlbolt.com/

About SQL

is a language designed to allow users query, manipulate, and transform data from a relational database .

There are many popular SQL databases including SQLite, MySQL, Postgres, Oracle and Microsoft SQL Server. All of them support the common SQL language standard,

Relational Databases

A relational database represents a collection of related (two-dimensional) tables.

Select queries

select - statements / queries

show entire table

SELECT *
FROM myTable;

select query for a specific column

SELECT column0, column1, ...
FROM myTable;

where - constraints and string matching

Select query with constraints

SELECT column0, column1, …
FROM myTable
WHERE condition
AND/OR another_condition
AND/OR …;

String Matching

avoiding NULL values

SELECT column0, column1, …
FROM mytable
WHERE column0 IS/IS NOT NULL
AND/OR another_condition
AND/OR …;

distinct - remove duplicates

Only select unique results

SELECT DISTINCT column0, column1, …
FROM mytable
WHERE condition(s);

order by - sort results, set limit and offset

SQL provides a way to sort your results by a given column in ascending or descending order

SELECT column0, column1, …
FROM mytable
WHERE condition(s)
ORDER BY column0 ASC/DESC;

Using LIMIT and OFFSET clauses to indicate to the database the subset of the results you care about

LIMIT: number of rows to return

OFFSET: where to begin counting the rows from

If you think about websites like Reddit or Pinterest, the front page is a list of links sorted by popularity and time, and each subsequent page can be represented by sets of links at different offsets in the database.

SELECT column0, column1, …
FROM mytable
WHERE condition(s)
ORDER BY column0 ASC/DESC
LIMIT num_limit OFFSET num_offset;

join - multi table queries

Entity data in the real world is often broken down into pieces and stored across multiple orthogonal tables using a process known as normalization .

Tables that share information about a single entity need to have a primary key that identifies that entity uniquely across the database.

INNER JOIN (or JOIN) to create a result row with the combined columns from both tables.

SELECT column0, column1, …
FROM mytable
INNER JOIN another_table
ON mytable.id = another_table.id
WHERE condition(s)
ORDER BY column0, … ASC/DESC
LIMIT num_limit OFFSET num_offset;

If the two tables have asymmetric data, which can easily happen when data is entered in different stages, then we would have to use a LEFT JOIN, RIGHT JOIN or FULL JOIN instead to ensure that the data you need is not left out of the results.

the OUTER keyword is really kept for SQL-92 compatibility

SELECT column0, column1, …
FROM mytable
INNER/LEFT/RIGHT/FULL JOIN another_table
ON mytable.id = another_table.matching_id
WHERE condition(s)
ORDER BY column, … ASC/DESC
LIMIT num_limit OFFSET num_offset;

as - queries with expressions

We can create a new column based on a self defined expression:

SELECT particle_speed / 2.0 AS half_particle_speed
FROM physics_data
WHERE ABS(particle_position) * 10.0 > 500;

SELECT column AS better_column_name, …
FROM a_long_widgets_table_name AS mywidgets
INNER JOIN widget_sales
ON mywidgets.id = widget_sales.widget_id;

functions - aggregate expressions

Functions used on columns

SELECT AGG_FUNC(column_or_expression) AS aggregate_description, …
FROM mytable
WHERE constraint_expression;

group by - grouping

Using aggregate functions on groups

The GROUP BY clause works by grouping rows that have the same value in the column specified.

SELECT AGG_FUNC(column_or_expression) AS aggregate_description, …
FROM mytable
WHERE constraint_expression
GROUP BY column0;

all groups with same val in column0

HAVING is like the WHERE clause but for groups (its executed to filter before the groups are formed).

SELECT group_by_column, AGG_FUNC(column_expression) AS aggregate_result_alias, …
FROM mytable
WHERE condition
GROUP BY column0
HAVING group_condition;

Summary: A complete query

A complete query:

SELECT DISTINCT column, AGG_FUNC(column_or_expression), …
FROM mytable
JOIN another_table
ON mytable.column = another_table.column
WHERE constraint_expression
GROUP BY column
HAVING constraint_expression
ORDER BY column ASC/DESC
LIMIT count OFFSET COUNT;

Because each part of the query is executed sequentially, it's important to understand the order of execution so that you know what results are accessible at different time frames.

1. FROM and JOIN s

The FROM clause, and subsequent JOIN s are first executed to determine the total working set of data that is being queried.

2. WHERE

Once we have the total working set of data, the first-pass WHERE constraints are applied to the individual rows, and rows that do not satisfy the constraint are discarded.

Aliases in the SELECT part of the query are not accessible in most databases since they may include expressions dependent on parts of the query that have not yet executed.

3. GROUP BY

The remaining rows after the WHERE constraints are applied are then grouped based on common values in the column specified in the GROUP BY clause.

4. HAVING

If the query has a GROUP BY clause, then the constraints in the HAVING clause are then applied to the grouped rows, discard the grouped rows that don't satisfy the constraint.

5. SELECT

Any expressions in the SELECT part of the query are finally computed.

6. DISTINCT

Of the remaining rows, rows with duplicate values in the column marked as DISTINCT will be discarded.

7. ORDER BY

If an order is specified by the ORDER BY clause, the rows are then sorted by the specified data in either ascending or descending order.

Since all the expressions in the SELECT part of the query have been computed, you can reference aliases in this clause.

8. LIMIT / OFFSET

Finally, the rows that fall outside the range specified by the LIMIT and OFFSET are discarded, leaving the final set of rows to be returned from the query.

Bonus: Matching character strings in the WHERE clause

The LIKE keyword indicates that the following character string is a matching pattern.

SELECT Surname
FROM Contacts
WHERE Surname LIKE 'Mc%';

Mc% : Search for every name that begins with the letters Mc → McEvoy

%en% : Search for every name containing the letters en → Pettengill, Lencki, Cohen

To make queries case insensitive:

Lets say only the entry Pedro is available → we have to format every query to lower and then search:

SELECT * FROM HORSE WHERE lower(name)='pedro'

Insertion of data

SQL schema

We previously described a table in a database as a two-dimensional set of rows and columns, with the columns being the properties and the rows being instances of the entity in the table. In SQL, the database schema is what describes the structure of each table, and the datatypes that each column of the table can contain.

For example, in our Movies table, the values in the Year column must be an Integer, and the values in the Title column must be a String.

insert - inserting new data

declares which table to write into, the columns of data that we are filling, and one or more rows of data to insert. In general, each row of data you insert should contain values for every corresponding column in the table. You can insert multiple rows at a time by just listing them sequentially.

INSERT INTO mytable
VALUES (value_or_expr, another_value_or_expr, …),
(value_or_expr_2, another_value_or_expr_2, …),
…;

In some cases, if you have incomplete data and the table contains columns that support default values, you can insert rows with only the columns of data you have by specifying them explicitly.

INSERT INTO mytable
(column0, column1, …)
VALUES (value_or_expr, another_value_or_expr, …),
(value_or_expr_2, another_value_or_expr_2, …),
…;

update - updating rows

Update statement

UPDATE mytable
SET column0 = value_or_expr,
column1 = another_value_or_expr,
…
WHERE condition;

delete - deleting rows

To remove rows

DELETE FROM mytable
WHERE condition;

create table - defining table schemas

you can create a new database table using the CREATE TABLE statement. (default value is optional)

CREATE TABLE IF NOT EXISTS mytable (
column DataType TableConstraint DEFAULT default_value,
another_column DataType TableConstraint DEFAULT default_value,
…
);

Example:

CREATE TABLE movies (
id INTEGER PRIMARY KEY,
title TEXT,
director TEXT,
year INTEGER,
length_minutes INTEGER
);

Datatypes:

Table constraints:

alter table - changing database schemas

Adding a column:

ALTER TABLE mytable
ADD column0 DataType OptionalTableConstraint DEFAULT default_value;

Removing columns:

ALTER TABLE mytable
DROP column_to_be_deleted;

Renaming table:

ALTER TABLE mytable
RENAME TO new_table_name;

drop table - removing database schema

To delete entire table

DROP TABLE IF EXISTS mytable;