Short guide to what SQL is and how to use it.

What is SQL?

SQL is a query langauge used to extract data from databases.

Simple Queries

Fetch all the columns from the country table.

SELECT * FROM country;

Fetch id and name columns from the city table.

SELECT id, name FROM city;

Fetch city names sorted by the rating column in the default ascending order.

SELECT name FROM city ORDER BY rating [ASC];

Fetch city names sorted by the rating column in the descending order.

SELECT name FROM city ORDER BY rating DESC;

Filtering

Comparison Operators

Fetch names of cities that have a rating above 3.

SELECT name FORM city WHERE rating > 3;

Fetch names of cities that are neither Berlin nor Madrid.

SELECT name FROM city WHERE name != 'Berlin' AND name != 'Madrid';

Text Operators

Fetch names of cities that start with a ‘P’ or end with an ‘s’

SELECT name FROM city WHERE name LIKE 'P%' OR name LIKE '%s';

Fetch names of ciites that start with any letter followed by ‘ublin’

SELECT name FROM city WHERE name LIKE '_ublin';

Other Operators

Fetch names of cities that have a population between 500K and 5M

SELECT name FROM city WHERE population BETWEEN 500000 AND 5000000;

Fetch names of cities that don’t missing a ration value

SELECT name FROM city WHRE rating IS NOT NULL;

Fetch names of cities that are in countries with IDs 1, 4, 7 or 8

SELECT name FROM city WHERE country_id IN(1, 4, 7, 8);

Querying Multiple Tables

Inner Join

Returns rows that have matching values in both tables.

SELECT city.name, country.name FROM city
[INNER] JOIN country ON city.county_id = country.id;

Left Join

Returns all rows from the left table with corresponding rows with the right table. If there are no matching rows NULL will be returned from the second table.

SELECT city.name, country.name FROM city
LEFT JOIN country ON city.country_id = country.id;

Right Join

Returns all rows from the right table with corresponding rows from the left table. If there are no matching rows NULL is returned from the left table.

SELECT city.name, country.name FROM city
RIGHT JOIN country ON city.country_id = country.id;

Full Join

Returns all the rows from both tables. If there are no matching rows in the second table NULL is returned.

SELECT city.name, country.name FROM city
FULL [OUTER] JOIN country ON city.country_id = country.id;

Cross Join

Returns all possible combinations of rows from both tables.

SELECT city.name, country.name FROM city CROSS JOIN country;

SELECT city.name, country.name FROM city, country;

Natural Join

Join tables by all columns with the same name.

SELECT city.name, country.name FROM city NATURAL JOIN country;

Subqueries

A subquery is a query nested inside another query or inside another subquery.

Single Value

Returns exactly one row.

SELECT name FROM city
WHERE rating = (
  SELECT rating
  FROM city
  WHERE name = 'Madrid'
);

Multiple Values

Return multiple columns or rows.

SELECT name FROM city
WHERE country_id IN (
  SELECT country_iid
  FROM country
  WHERE population > 2000000
);

Correlated

Refers to the tables introduced in the outer query. A correlated subquery depends on the outer query. It cannot be run independently from the outer query.

Finidng cities with a population greater than the average population in the country.

SELECT * FROM city main_city
WHERE population > (
  SELECT AVG(population)
  FROM city average_city
  WHERE average_city.country_id = main_city.country_id
);

Finding countries that have at least one city.

SELECT name FROM country
WHERE EXISTS (
  SELECT *
  FROM city
  WHERE country_id = country.id
)

Set Operations

Union

Combines the results of two results sets and removes duplicates.

SELECT name FROM cycling WHERE country = 'DE'
UNION / UNION ALL
SELECT name FROM skating WHERE country = 'DE';

Intersect

Returns only rows that appear in both result sets.

SELECT name FROM cycling WHERE country = 'DE'
INTERSECT
SELECT name FROM skating WHRE country = 'DE';

Except

Returns only the rows that appear in the first result set but do not appear in the second result set.

Displaying German cyclists unless they are also German skates at the same time.

SELECT name FROM cycling WHERE country = 'DE'
EXCEPT / MINUS
SELECT name FROM skating WHERE country = 'DE';