My notes for SQL.

CRUD Operations & Data Manipulation

SQL is not case sensitive.

USE – Select a database for use.

USE database_name;

SELECT – Retrieve data from a table.

SELECT column1, column2, ... 
FROM table_name;

SELECT * – Retrieve all columns from a table.

SELECT * FROM table_name;

DISTINCT – Retrieve distinct “projections” from the given select statement.

SELECT DISTINCT column1, column2, ...
FROM table_name;

WHERE – Filter the projections with the given condition. This clause is also used with UPDATE, DELETE, etc.

SELECT column1, column2, ...
FROM table_name
WHERE condition;

AND, OR and NOT clause can be combined with WHERE.

SELECT column1, column2, ...
FROM table_name
WHERE NOT condition1 AND condition2 OR condition3 ...;

ORDER BY – Sort the result-set in ascending or descending order specified by ASC or DESC.

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

INSERT INTO – Insert new records in a table.

No insert is possible if it violates any constraint.

2 ways:-

/* specify both the column names and values to be inserted */ 
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

/* also possible to only insert data in specified columns */
/* this is only possible when other fields can accept null values */
INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');

/* specify the values in order */ 
INSERT INTO table_name
VALUES (value1, value2, value3, ...);

NULL – A field with no value.

Checking for NULL value.

IS NULL – to test for empty values. IS NOT NULL – to test for non-empty values.

/* Conditions for null values */
SELECT column_name
FROM table_name
WHERE column_name IS NULL;

SELECT column_name
FROM table_name
WHERE column_name IS NOT NULL;

UPDATE – statement is used to modify the existing records in a table.

/* this updates all the row which satisfy the condition */
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Dangerous UPDATE statement.

/* this will update all rows in the table */
UPDATE Customers
SET ContactName='+91-99XXXXXXXX'

DELETE – to delete rows in a table which satisfy a condidion.

DELETE FROM table_name WHERE condition;

/* dangerous statement - all records will be deleted */
DELETE FROM table_name;

SELECT TOP – used to specify number of records to return. This is useful on large tables with millions of records, because retrieving very large number of rows can cause performance issues. This clause is available with LIMIT name in many SQLs. Oracle used FETCH FIRST n ROWS ONLY and ROWNUM.

Here we will discuss MariaDB syntax.

/* 2 variations of LIMIT */
SELECT * FROM table_name LIMIT offset, row_count
SELECT * FROM table_name LIMIT row_count OFFSET offset

Functions

  • MIN()
  • MAX()
/* return minimum of all values in column_name which satisfy condition */
SELECT MIN(column_name)
FROM table_name
WHERE condition;
  • COUNT() - returns number of rows that matches a specified condition
  • AVG() - returns the average value of all value that matches a specified condition
  • SUM() - returns the sum of all value that matches a specifies condition

Conditional Operators

LIKE() search a specified pattern in a column

  • % matches any number of characters, including zero.

  • _ matches any single character.

  • ESCAPE characters are used in the pattern string to indicate that any wildcard character that occurs after the escape character in the pattern string should be treated as a regular character. Default escape character is backslash \.

<select expr> [NOT] LIKE pattern [ESCAPE `escape_char`]

/* examples */
SELECT * FROM table_name WHERE column_name LIKE "___day";

IN or NOT IN operator compares a value with a list of values:

<expression> [NOT] IN (v1, v2, v3, ...)

/* nested select statement */
<expression> [NOT] IN (<select-statement>)

example:

select name, region_id
from countries
where region_id not in (1,2,3);

select name, region_id
from countries
where region_id in (
    select region_id
    from regions
    where name like '%Asia%'
)
order by name;

BETWEEN compares values within a given range (inclusive).

/* value can be numeric, date, characters */
<expression> [NOT] BETWEEN value1 AND value2;

/* numeric example */
select name, area 
from countries
where area not between 1566500 and 2780400
order by area;

/* date example */
select name, national_day 
from countries
where national_day between '1945-08-17' and '1953-11-09'
order by national_day;

Joins

Join is used to select data from multiple tables on some specified condition.

  • INNER JOIN
/*
Compares each row from `t1` table with every row from `t2` table.
If a pair of rows causes `join_condition` to evaluate to `true`, the `inner join` clause creates a new row whose columns are derived from rows in both tables and includes this row in result set.
*/
SELECT select_list
FROM t1
INNER JOIN t2 ON join_condition;

SELECT select_list
FROM t1
INNER JOIN t2 ON t1.column1 = t2.column1;

/* if columns for equality have same names */
SELECT select_list
FROM t1
INNER JOIN t2 USING (column1);
  • LEFT JOIN
/*
Behaves similar to `INNER JOIN` with a difference that if rows not matched, `left join` still creates a new row whose columns are the combination of columns from both tables. However, it uses `NULL` values for the columns in the right table.
*/

SELECT select_list
FROM t1
LEFT JOIN t2 ON join_condition;

/* example */
SELECT g.guest_id, g.name, v.vip_id, v.name
FROM guests g
LEFT JOIN vips v USING(name);
  • RIGHT JOIN is similar to LEFT JOIN.
  • CROSS JOIN doesn’t have a join predicate, it takes cartesian product of two tables.
/* Cartesian Product of tables */
SELECT g.guest_id, g.name, v.vip_id, v.name
FROM guests g
CROSS JOIN vips v;
  • MariaDB doesn’t support FULL OUTER JOIN or FULL JOIN.

UNION operator combines the result sets of two or more select statements into single result set. (JOIN appends result sets horizontally while a union appends result sets vertically.)

UNION DISTINCT remove duplicate rows from the final result set while the ALL option retains the duplicates.

<select-statement1>
UNION [ALL | DISTINCT]
<select-statement2>
UNION [ALL | DISTINCT]

/* example */
SELECT vip_id id, name, 'vip' as type FROM vips
UNION       /* by default UNION is ALL */
SELECT guest_id, name, 'guest' FROM guests;

/* example for distinct */
SELECT name FROM guests
UNION DISTINCT
SELECT name FROM vips 
ORDER BY name;

GROUP BY clause groups rows of a result into groups. It is often used with aggregate functions including COUNT(), MIN(), MAX(), SUM() and AVG().

SELECT select_list FROM table_name
GROUP BY column1, column2, ...;

/* using with aggregate_function */
SELECT column1, aggregate_function(column2) FROM table_name
GROUP BY column1;

SELECT region_id, COUNT(country_id) 
FROM countries
GROUP BY region_id
ORDER BY region_id;

SELECT regions.name, COUNT(country_id) country_count
FROM countries
INNER JOIN regions USING (region_id)
GROUP BY regions.name
ORDER BY regions.name;

SELECT regions.name region, SUM(area) region_area
FROM countries
INNER JOIN regions USING(region_id)
GROUP BY regions.name
ORDER BY region_area;

WHERE clause allows us to specify condition for filtering rows returned by the SELECT statement. But, it can’t be used for filtering summarized rows created by the GROUP BY clause. HAVING is used in those cases.

SELECT select_list
FROM table_name
GROUP BY column1, column2, ...
HAVING search_condition;

/* example */
SELECT regions.name region, COUNT(country_id) country_count
FROM countries
INNER JOIN regions USING (region_id)
GROUP BY (regions.name)
HAVING COUNT(region_id) > 10;

Subqueries

A subquery is a query nested in another query.

SELECT name, area FROM countries
WHERE country_id IN (
    SELECT country_id FROM countries
    WHERE area > 100    /* nested subquery */
) ORDER BY area, name;

SELECT * FROM countries 
WHERE area = (
    SELECT MAX(area) FROM countries    /* nested subquery */
);

SELECT name 
FROM country_stats INNER JOIN countries USING (country_id)
WHERE year = 2018 and (population, gdp) > (
    SELECT AVG(population), AVG(gdp)
    FROM country_stats
    WHERE year = 2018
) ORDER BY name;


/* subquery in the form clause */
SELECT AVG(region_area)
FROM (
    SELECT SUM(area) region_area
    FROM countries
    GROUP BY region_id
) t;