DB (2) - SQL

This post is part of the Database Series.
Click here to read from the previous post.

Now let’s dive into the Database Language, SQL (Structured Query Language). We will learn MySQL.

1. About SQL

SQL is non-procedural, transform-oriented, and standard language for relational databases and it stands for Structured Query Language. It is actually a combination of a four different (but similar in some sense) languages.
DDL (Data Definition Lang.) for creating DB,
DML (Data Manipulating Lang.) for manipulating DB,
DQL (Data Query Lang.) for retrieving information, and
DCL (Data Control Lang.) for controlling access or giving permissions.

2. Understanding the Relational Database Model

Image source
SQL is about Table Referencing. In Relational Database Model, every data is stored in structured tables that are strongly inter-connected with each others. Because everything is in table, it is okay to say data can be referenced by column and row. Also, one of the key concept of DB is that there are no duplicates of a row. Every row are unique, although the entities are not unique, set of entities, which is row, is unique.

Here, we should know what a ‘Primary key’ is. A primary key is a field in a table which uniquely identifies each row in a database table. Primary keys must have unique values, which is not NULL. For example, let’s say we are dealing with DB of Staff in one company. The primary key for the table name ‘Staff’ can be something like ‘Staff_No.’, which is a unique number given to every staff.

Remember this model is ‘Relational’. There are many tables that are ‘related to’ each other. How can we relate one table to other(s)? It’s done by a ‘Foreign Key’. It is a field in one table that refers to the Primary key in another table. This relationship can be termed by the really familiar relationship we always encounter; Parent-Child relationship. The table containing the foreign key is the child table, and the table containing the candidate key is the parent table.

Additionally, there’s one more thing called ‘Composite key’. It is literally a composite of several attributes, playing a role as Primary key. It is when joined attributes uniquely identify each row in a DB.

3. MySQL

3-1. Data types

INT             -- whole numbers
DECIMAL(5, 2)   -- decimal numbers of total 5 digits and 2 decimal digits
VARCHAR(10)     -- Variable Character of size 10
BLOB            -- Binary Large Object
DATE            -- 'YYYY-MM-DD'
TIMESTAMP       -- 'YYY-MM-DD HH:MM:SS'

3-2. Creating DB and Making tables

Example of creating DB and making Tables: (Example of EstateAgent is borrowed from Reference No. 1)

CREATE DATABASE EstateAgent;

-- making it the current DB in use
USE EstateAgent;

-- Creating a table to hold data
CREATE TABLE Staff (staffNo VARCHAR(4), fName VARCHAR(20), sex VARCHAR(1), DOB DATE, salary DECIMAL(5, 2));

-- Inserting a row into a table
INSERT INTO Staff(staffNo, fName, sex, DOB, salary) 
VALUES ('AB01', 'Danny', 'M', '1984-01-01', 99000)

-- Let's say now we have many rows inserted in our DB, EstateAgent
-- Updating data in tables (Give all staff a 5% pay increase)
UPDATE Staff
SET salary = salary * 1.05

3-3. Querying data

Primary Clauses for retrieving data

SELECT    -- columns to appear
FROM      -- table to be used
WHERE     -- filters rows
GROUP BY  -- form groups of rows with the same column value
HAVING    -- filters groups subject to some conditions
ORDER BY  -- order of the results

SELECT, FROM

-- column named staffNo and fName will appear from Staff Table
SELECT staffNo, fName
FROM Staff;

-- Can select all attributes by asterisk.
SELECT *
FROM Staff;

-- Eliminate possible duplicates by DISTINCT and give title to the attribute by AS
SELECT DISTINCT fName AS firstName
FROM Staff;

-- Calculation (montly salaries)
SELECT staffNo, salary / 12
FROM Staff;

WHERE, OR, AND, BETWEEN, NOT, IN, IS NULL, ORDER BY, Pattern Matching by LIKE

-- Male staff or staff who make salary between 40k and 60k
-- Can use NOT BETWEEN
SELECT *
FROM Staff
WHERE sex='M' OR salary BETWEEN  40000 AND 60000;

-- Set membership search by IN
-- Can do WHERE fName='Claire' OR fName='Soo' OR fName='Yoonho' OR fName='Cassey', but a bit messy.
SELECT staffNO, fName, DOB, salary
FROM Staff
WHERE fName IN ('Claire', 'Soo', 'Yoonho', 'Cassey');

-- NULL Value Finding -> used a lot in Data Science
-- IS NULL, IS NOT NULL
SELECT staffNO, fName, DOB, salary
FROM Staff
WHERE DOB IS NOT NULL 

-- Can do pattern matching with special symbol, % and _
-- % means zero or more chars
-- _ means any single chars
SELECT fName, salary
FROM Staff
WHERE fName LIKE '%air%'
-- this will give you 'Claire' on table
-- If you want list of fName starts with C, you can do 'C%'

-- Let's see who earns money the best by ORDER BY
SELECT *
FROM Staff
ORDER BY fName, salary DESC;

Reference

1. My note taken from Dr. John Dowell’s lectures at UCL - COMP0009: Logic and Database Theory (19/20)
2. MySQL Logo: By Source, Fair use, https://en.wikipedia.org/w/index.php?curid=17119753