SQL

Introduction

SQL is a standard of ANSI/ISO.

RDBMS - Relational Database Management System

Things to remember:

• Data in RDBMS is stored in databases in "tables".
• Tables is a collection of related entries
• Consists of columns and rows
• Tables have "fields" - the columns
• Each row represents a "record"
• SQL statements are case insensitive
• Prefer single quotes

Setting up the test environment

In this section, I am going to set up the test environment to execute the upcoming commands. I will be using Northwind database.

1. Create a directory where all our files will be stored. This will be our working directory on the host machine.
2. Download the Northwind database .sql file from Wikiversity website to the newly created folder. I didn't find any direct download links so I just copy-pasted the content to a text editor, fixed the formatting and saved with name north.sql.
   • The database "Text is available under the Creative Commons Attribution-ShareAlike License; additional terms may apply" as mentioned at the bottom of the above Wikiversity page.
3. Install Docker on your machine. Refer to the official docker website for instructions for your machine.
4. Create a new file in the working directory and name it Dockerfile.

5. Paste the following content in the Dockerfile. I will not be going through each line in the Dockerfile. You can refer to the official docker documentation for reference.

   FROM alpine:3.16
   WORKDIR /app
   COPY ./north.sql /app/north.sql
   
   # Uncomment following line if there is error with certificates
   # RUN sed -ie "s/https/http/g" /etc/apk/repositories
   RUN apk update
   RUN apk add mysql-client mariadb-connector-c
   
   CMD ["/bin/sh"]

6. Build and tag the image. Open a terminal in the working directory and issue the following command:

   docker build -t dbapp:v1 .

7. Create a new file called compose.yml and paste the following content in it. I will not be going through what each line in this compose file is doing. You can refer to the official docker documentation for reference.

   services:
     dbapp:
       image: dbapp:v1
       stdin_open: true  # docker run -i
       tty: true         # docker run -t
     mysql:
       image: mysql
       environment:
         - MYSQL_ROOT_PASSWORD=mypass  # DO NOT DO THIS IN PROD
       expose:
         - 3306

8. Start the services. In the terminal (in the working directory), run:

   docker compose up

9. Find out the dbapp container name by checking the output of docker compose up command and connect to it. If for some reason you are unable to do so, issue the following command from another terminal (in the same directory) and note down the name of the dbapp container

   docker compose ps  # To find out the name of the dbapp container
   docker attach <whatevername>  # To attach
   # Press Ctrl-p Ctrl-q to detach

10. From within the attached container, connect to the MySQL service and create Northwind database

    # Provide the password at stdin
    mysql -h mysql -P 3306 -u root -p
    
    # Source the north.sql file
    source ./north.sql

11. We are using the root user as of now. This is not recommended but should be okay for testing. If you want another user, use the commands provided in this document to do so.

Some DB engines require ";" at the end of statements. So, it is a good idea to use it generally.

No quotes around numbers

Database = bunch of tables
Table = bunch of records
Record = bunch of fields

Common SQL tasks

Connecting to a MySQL instance

mysql -h <hostname> -P <port> -u <username> -p

Creating a new user

CREATE USER "username"@"source" IDENTIFIED WITH mysql_native_password BY "topsecretpassword"

Granting user permissions

GRANT 
    REFERENCES, 
    CREATE, 
    DROP, 
    SELECT, 
    INSERT, 
    UPDATE, 
    ALTER, 
    DELETE, 
    INDEX 
ON *.* 
TO "username"@"source";

FLUSH PRIVILEGES;

Show the create table command for a given table

SHOW CREATE TABLE <table_name>;

SQL commands

SELECT

Select data from a database.

-- Select specific columns from the table
 SELECT <col1>, <col2>, ...
 FROM <table_name>
 WHERE <condition>;

 SELECT CustomerName, City 
 FROM Customers
 WHERE Country="Spain";

-- Select everything from the table
 SELECT * FROM <table_name>;

 -- Select only distinct values for specific columns
 SELECT DISTINCT <col1>, <col2>, ...
 FROM <table_name>;

-- Count the unique values in a column in a table
SELECT COUNT(DISTINCT <col>) FROM <table>;

-- Return a limited number of results (MySQL)
-- Different DB engines have different clauses for returning limited results
-- e.g., 'SELECT TOP n [PERCENT] <col1>, <col2> FROM <table>' in MS SQL or 
-- 'SELECT * FROM <table> FETCH FIRST n ROWS ONLY' for Oracle
SELECT * FROM Customers LIMIT 10;  -- Returns only first 10 results

UPDATE

Modify existing records.
Must use WHERE clause with UPDATE statement otherwise it will update ALL the records in the table.
Whether one record is updated or more than one depends on the condition that we specify.

-- Syntax
UPDATE <table_name>
SET <col1> = <val1>, <col2> = <val2>, ...
WHERE <condition>  -- All the matching records will be updated

UPDATE Customers 
SET ContactName = 'Unicorn', Address = 'Rainbow' 
WHERE (ContactName IS NULL) AND (Address IS NULL);

UPDATE Customers  
SET ContactName = 'Unicorn', Address = 'Nowhere' 
WHERE CustomerID=99;

Must use WHERE clause with UPDATE statement otherwise it will update ALL the records in the table.

DELETE

Deletes a record from the table.
Must use WHERE clause with DELETE statement otherwise it will delete ALL the records in the table.
Whether one record is deleted or more than one depends on the condition that we specify.

-- Syntax
DELETE FROM <table_name>
WHERE <condition>;

DELETE FROM Customers
WHERE CustomerName='Jack';

DELETE FROM Customers;  -- Delete ALL the records from this table (table is still intact)

Must use WHERE clause with DELETE statement otherwise it will delete ALL the records in the table.

INSERT INTO

Insert new records in the table.
Multiple values can be specified in the same insert statement.
To insert record(s) with only specific columns populated, specify the column names and then the values.
To insert record(s) for all the columns, just specify the values but in the same order as the columns.

-- Insert multiple records, specify column names
INSERT INTO <table_name> (<col1>, <col2>, ...)
VALUES 
(<col1val1>, <col2val1>, ...), 
(<col1val2>, <col2val2>, ...), ... ;

INSERT INTO Customers (CustomerName, City, PostalCode, Country) 
VALUES 
('Jill', 'Bangkok', 10042, 'Thailand'),
('Jack', 'Madrid', 34500, 'Spain'),
('John', 'NYC', 34212, 'USA');

-- Insert multiple records without specifying any columns
INSERT INTO <table_name>
VALUES 
(<col1val1>, <col2val1>, ...), 
(<col1val2>, <col2val2>, ...);

-- Insert single record without specifying any column
-- Add more record entries to add multiple records
-- We need to specify valuese for all the columns (including auto-increment ones)
INSERT INTO Customers 
VALUES 
(99, 'Jack', 'Jill', '13B, Baker Street', 'London', '33333', 'UK');

CREATE DATABASE

ALTER DATABASE

CREATE TABLE

ALTER TABLE

DROP TABLE

CREATE INDEX

DROP INDEX

Clauses

WHERE

Acts as filter

SELECT * FROM Categories WHERE CategoryID=5;
SELECT * FROM Categories WHERE CategoryID>5;
SELECT * FROM Categories WHERE CategoryID<5;
SELECT * FROM Categories WHERE CategoryID<>5; -- not equal, some DB engines have '!='
SELECT * FROM Categories WHERE CategoryID<=5;
SELECT * FROM Categories WHERE CategoryID>=5;
SELECT * FROM Categories WHERE CategoryID BETWEEN 3 AND 5; -- inclusive
SELECT * FROM Categories WHERE CategoryID IN (3, 5); -- only 3 and 5
SELECT * FROM Categories WHERE CategoryName LIKE 'Con%'; -- '%' here is similar to '*' in regex

ORDER BY

Used to sort the output.
Sorted in ascending order by default
Alphabetic sorting for strings
Sorting by multiple columns is possible. What happens in such cases is: first the result is sorted by col1. If there are multiple records that have the same value for that column then those records will be sorted by col2 and so on.

-- Sort in descending order
-- Ascending (ASC) is default 
SELECT <col1>, <col2>
FROM <table_name>
ORDER BY <col> DESC;   -- ASC is default behaviour

-- Sort by multiple columns
SELECT * FROM <table_name>
ORDER BY <col1>, <col2> ASC;

-- Sorting both ASC and DESC
SELECT <col1>, <col2> 
FROM <table_name>
WHERE <condition> 
ORDER BY <col3> ASC, <col4> ASC, <col5> DESC;

JOIN

Combine rows between two tables, based on a related column between them.

Operators

Comparison operators

Comparison operators: <, >, <=, >=, <>, =, BETWEEN, LIKE, IN
_See the MARKDOWN_HASH5105e0481cb9b1e1d0dd3e10bab1f1c0MARKDOWNHASH clause above for examples.

LIKE

Used with WHERE clause.

-- Starts with a pattern (here letter 'a')
SELECT * FROM <table_name>
WHERE <col> LIKE 'a%';  -- Starts with 'a' followed by 0, 1 or more chars

-- Ends with a pattern (here letter 'a')
SELECT * FROM <table_name>
WHERE <col> LIKE '%a';

-- Contains a pattern (here letter 'a')
SELECT * FROM <table_name>
WHERE <col> LIKE '%a%';

-- There is '_' wildcard that matches *exactly* one character
-- MySQL also has support for UNIX style regex using 'RLIKE' operator. Check MySQL documentation for more information
-- For negative test use 'NOT LIKE'

IN

Shorthand for multiple OR conditions

-- Positive test
SELECT * FROM <table_name>
WHERE <col> IN (val1, val2, val3, ...);

-- Negative test
SELECT * FROM <table_name>
WHERE <col> NOT IN (val1, val2, ...);

-- Can also be used with sub-queries
SELECT * FROM <table_name>
WHERE <col> IN/NOT IN (<sub-query>);

BETWEEN

Used to select values within a given range (inclusive).
Values can be text, date or integers.

SELECT * FROM <table_name>
WHERE <col> BETWEEN <val1> AND <val2>

-- Negative test
SELECT * FROM <table_name>
WHERE <col> NOT BETWEEN <val1> AND <val2>

Logical operators

AND, OR, NOT

-- AND
SELECT <col1>, <col2> ...
FROM <table_name>
WHERE <condition1> AND <condition2> AND ...;

-- OR
SELECT <col1>, <col2> ...
FROM <table_name> 
WHERE <condition1> OR <condition2> OR ...;

-- NOT
SELECT <col1>, <col2>, ...
FROM <table_name>
WHERE NOT <condtion1>;  -- Only in case of BETWEEN, IN and LIKE, we can say 'NOT BETWEEN', 'NOT IN' or 'NOT LIKE'

-- AND and OR
SELECT FirstName, LastName
FROM Employees
WHERE (EmployeeID BETWEEN 1 AND 3) OR (FirstName LIKE 'A%' AND LastName LIKE 'F%')
ORDER BY LastName DESC;

Conditions can be grouped using parentheses: ()

Null values

If a field had no value provided to it when the record was created, it will have no value represented by NULL (different from 0 or a field with spaces).
Can not be checked by comparison operators. Use IS NULL and IS NOT NULL operators

-- Select all the records that have NULL values <col1>
SELECT * 
FROM <table_name> 
WHERE <col1> IS NULL;

-- Select all the records that have non-NULL values for <col1>
SELECT * 
FROM <table_name>
WHERE <col1> IS NOT NULL;

SQL Functions

MIN and MAX

Find the minimum / maximum value in a column.

-- Find minimum value
SELECT MIN(<col>) AS 'SmallestValue'
FROM <table>

-- Find maximum value
SELECT MAX(<col>) AS 'LargestValue'
FROM <table>

COUNT

Find the total number of records that fulfill a criteria.

-- Find the total number of records in a table
SELECT COUNT(*) 
FROM <table>;

-- Find the total number of records in a table that match a criteria
SELECT COUNT(*)
FROM <table>
WHERE <condition>;

-- Count the number of non-NULL entries in a column
SELECT COUNT(<col>)
FROM <table>;

-- Count the number of distinct values in a column
SELECT COUNT(DISTINCT <col>)
FROM <table>;

SUM

Find the sum of a column with numeric values.

-- Syntax
SELECT SUM(<col>) AS <alias>
FROM <table_name>
WHERE <condition>;

-- Concrete example
SELECT SUM(Quantity) AS QuantitySum
FROM OrderDetails 
WHERE ProductID=11;

-- Sum with an expression
SELECT SUM(Quantity * 10) AS TotalCost 
FROM OrderDetails 
WHERE ProductID=11;

AVG

Return the average of a numerical column.
It ignores the NULL values.

-- Syntax
SELECT AVG(<col>) AS <alias>
FROM <table_name> 
WHERE <condition>

-- Concrete example with alias
SELECT AVG(Price) AS AveragePrice
FROM Products;

-- Concrete example with sub-query
SELECT * 
FROM Products
WHERE Price > (SELECT AVG(Price) FROM Products);

Alias

Use AS keyword to create an alias for a column or table name. The keyword can be omitted as well.
Aliases are transient in nature and exist only for the duration of the query.
Use aliases when it makes the query smaller or more readable.

-- Syntax
SELECT <col> AS <alias>
FROM <table>

-- Syntax without 'AS'
SELECT <col> <alias>
FROM <table>

-- Table alias with spaces
SELECT <col>
FROM <table> AS [<name with spaces>]

-- Column alias with spaces
SELECT <col> "<name with spaces>"

-- '[]' and double quotes are both valid ways of setting alias with spaces
-- Notice that it is double quotes and not single quotes

SELECT CustomerName, CONCAT(City, ', ', Country) AS Location
FROM Customers;

Sub-queries

Date format in SQL

Troubleshooting

A solution here which installs caching_sha2_password is to add the following package mariadb-connector-c-dev