(Published: 2024-11-15)

SQL Self Training Notes

This is the self learning phase notes of the SQL database management system language.

You can consider this is a compilation of basic, intro-level notes to SQL. It is not comprehensive nor is it “in-depth”.

If you feel there is anything missing, wrong, or worthy of addition, please feel free to reach out and open a discussion.

The link to the training is found below:

SQL and Databases - A Full Course for Beginners

Material Covered

Broadly speaking, it will cover: Schema design, Create-Read-Update-Delete operations (CRUD), and database fundamentals.

What a database is
Tables and Keys
SQL Basics
MySQL Windows/Mac Installation
Creating Tables
Inserting Data
Constraints
Update & Delete
Basic Queries
Company Database Intro
Creating Company Database
More Basic Queries
Functions
Wildcards
Unions
Joins
Nested Queries
On Delete
Triggers
ER Diagrams

What is a database

A database is any collection of related information like a phone book, shopping list, todo list, user base, list of transactions.

We typically store these in computers using a relational database. We use a structured query language (SQL) to interact with that database.

Database Management Systems (DBMS)

Special software program that helps users maintain a database.

Handles managing large amounts of information, security, backups, importing/exporting, concurrency, etc.

C.R.U.D. Operations

Performs the CRUD operations:

Create
Read
Update
Delete

Types of databases

Relational Dabatabases (SQL)

Organizes data into tables with rows and columns

Rows are identified using unique keys.

Examples:

mySQL
Oracle
PostgreSQL
MariaDB

Non-Relational Dabatabases (Non-SQL)

“Anything but a traditional table”

Organizes data in less structured form.

Typically uses things like key-value stores (JSON), Documents, Graphs, etc.

Examples:

MongoDB
DynamoDB
Apache Cassandra
Firebase

Implementation specific: Each implementation uses its own DBMS to handle its own storage structure

Database Queries

Queries are simply requests made from a user of application to the database management system asking for speicific information matching a criteria.

Tables and Keys

Surrogate Key: A unique key which has no real world meaning or value (e.g. randomly generated, unique, number to identify employees).
Natural Key: A unique key which has real world mapping (e.g. employee SSN, or citizen national ID number).
Foreign Key: A attribute key which is used to link one table to another table. A foreign key is considered a primary key for the table that it links to.
Composite Key: A unique key that is made up of 2 or more attributes (ie. ID + name).

SQL Basics

SQL is a language used for intercting with Relational Database Management Systems (RDBMS).

SQL is a hybrid of 4 types of languages:

Data Query Language (DQL)
- Query database for information
Data Definition Language (DDL)
- Defining database schemas
Data Control Language (DCL)
- Controlling access to the data in the database
Data Manipulation Language (DML)
- CRUD manipulation for updating database entries

Queries

A query is one or more criteria given to the RDBMS as an instruction to retrieve specific information from the database

Data Types

INT
DECIMAL
VARCHAR(l) : String of length l
BLOB : Binary large object, stores large data
DATE : YYYY-MM-DD
TIMESTAMP : YYYY-MM-DD HH:MM:SS

Keywords

DEFAULT for default values
AUTO_INCREMENT for setting PK without explicitly typing
NOT NULL does not allow empty values
UNIQUE prevents duplicates

Primary key is essentially just an INT with both UNIQUE and NOT NULL properties

UPDATE

To update values of rows in the table:

UPDATE student
      SET major = 'Biochemistry'
      WHERE major = 'Bio' OR major = 'Chemistry'

DELETE

To delete a certain row/entry from a table:

-- Deletes all entries where name is 'Tom' with undecided major
      DELETE FROM student
      WHERE name = 'Tom' AND major = 'undecided'
      
      -- To delete all entries from the table
      DELETE FROM student

SELECT

Retrieve all rows in the table:

-- Retrieves all columns from the `students` table (all entries)
      SELECT * FROM student;
      
      -- Select only the id and name in alphabetical order
      SELECT student_id, name
      FROM student
      ORDER BY name DESC;

Company Database Example

Questions

Difference between:

ON DELETE SET NULL
      ON DELETE CASCADE

In this tutorial chapter we set up the company database for the office with the tables for employee, branch, client, works_with, and branch_supplier.

-- Employee table
      CREATE TABLE employee (
          emp_id INT PRIMARY KEY,
          first_name VARCHAR(40),
          last_name VARCHAR(40),
          birth_day DATE,
          sex VARCHAR(1),
          salary INT,
          super_id INT,
          branch_id INT
      );
      DESCRIBE employee;
      SELECT * FROM employee;
      
      -- Branch table
      CREATE TABLE branch (
          branch_id INT PRIMARY KEY,
          branch_name VARCHAR(40),
          mgr_id INT,
          mgr_start_date DATE,
          FOREIGN KEY(mgr_id) REFERENCES employee(emp_id) ON DELETE SET NULL 
      );
      DESCRIBE branch;
      SELECT * FROM branch
      
      -- Add branch_id foreign key to employee
      ALTER TABLE employee
      ADD FOREIGN KEY(branch_id)
      REFERENCES branch(branch_id)
      ON DELETE SET NULL;
      
      ALTER TABLE employee
      ADD FOREIGN KEY(super_id)
      REFERENCES employee(emp_id)
      ON DELETE SET NULL;
      
      -- Client table
      CREATE TABLE client (
          client_id INT PRIMARY KEY,
          client_name VARCHAR(40),
          branch_id INT,
          FOREIGN KEY(branch_id) REFERENCES branch(branch_id) ON DELETE SET NULL 
      );
      DESCRIBE client;
      SELECT * FROM client;
      
      -- Works_with table
      CREATE TABLE works_with (
          emp_id INT,
          client_id INT,
          total_sales INT,
          PRIMARY KEY(emp_id, client_id),
          FOREIGN KEY(emp_id) REFERENCES employee(emp_id) ON DELETE CASCADE,
          FOREIGN KEY(client_id) REFERENCES client(client_id) ON DELETE CASCADE
      );
      DESCRIBE works_with;
      SELECT * FROM works_with;
      
      -- Branch supplier table
      CREATE TABLE branch_supplier (
          branch_id INT,
          supplier_name VARCHAR(40),
          supply_type VARCHAR(40),
          PRIMARY KEY(branch_id, supplier_name),
          FOREIGN KEY(branch_id) REFERENCES branch(branch_id) ON DELETE CASCADE
      );
      DESCRIBE branch_supplier;
      SELECT * FROM branch_supplier;
      
      -- Insert Corporate office employees
      INSERT INTO employee VALUES(100, 'David', 'Wallace', '1967-11-17', 'M', 25000, NULL, NULL);
      INSERT INTO branch VALUES(1, 'Corporate', 100, '2006-02-09');
      
      UPDATE employee
      SET branch_id = 1
      WHERE emp_id = 100;
      
      INSERT INTO employee VALUES(101, 'Jan', 'Levinson', '1961-05-11', 'F', 110000, 100, 1);

More Basic Queries

SELECT <attributes> vs. *
SELECT <attribute> AS <alias>
SELECT DISTINCT <attribute>
ORDER BY
LIMIT

Functions

SELECT COUNT(<attribute>) to find number of entries there are
SELECT AVG(<attribute>) to find the average of entries
SELECT SUM(<attribute>) to find the sum of entries
GROUP BY <attribute> to aggregate function results by attribute

Wildcards

% = any
_ = one character
LIKE

-- Find any clients who are an LLC
      SELECT *
      FROM client
      WHERE client_name LIKE '%LLC';

-- Find any employee born in October
      SELECT *
      FROM employee
      WHERE birth_date LIKE '____-10%';

Union

Union: A special operator used to combine the result of two or more search criteria.

-- Find a list of employee first names and branch names
      SELECT first_name
      FROM employee
      UNION
      SELECT branch_name
      FROM branch;

> Note: Unions assert that each combined criteria is using the same number of > columns in the SELECT part

Joins

Join: Operator used to combine rows from two or more tables based on a related attribute between them

-- Find all branches and the names of their managers
      SELECT employee.emp_id, employee.first_name, branch.branch_name
      FROM employee
      JOIN branch
      ON employee.emp_id = branch.mgr_id;

LEFT JOIN: Includes all entries in the LEFT table (FROM <table>) in the output of the JOIN
RIGHT JOIN: Includes all entries in the RIGHT table (JOIN <table>) in the output of the JOIN

Nested Queries

Using multiple SELECT statements to get specific information.

Nest another SELECT statement after WHERE statement

-- Find names of all employees who have sold over 30,000 to a single client
      SELECT employee.first_name, employee.last_name
      FROM employee
      WHERE employee.emp_id IN (
          SELECT works_with.emp_id
          FROM works_with
          WHERE works_with.total_sales > 30000
      );

On Delete

Determines how deleted entries update onto other tables with foreign keys.

SET NULL

When one entry is deleted, it sets the foreign key in other tables pointing to that entry to NULL.

CASCADE

When one entry is deleted, it also deletes the entries in other tables where the deleted entry is the foreign key.

Typically, we use CASCADE when the foreign key is part of the primary key for the relevant table (because a primary key cannot have a NULL value).

Triggers

A piece of code that defines a certain action that should occur when another event/operation takes place.

DELIMITER $$
      CREATE
          TRIGGER my_trigger BEFORE INSERT
          ON employee
          FOR EACH ROW BEGIN
              INSERT INTO trigger_test VALUES('added new employee');
          END$$
      DELIMITER ;

Entity Relationship (ER)

ER diagrams are a way to visually map out the schema, the relations between its tables and attributes.

Entity: Object we want to model and store information about
Attributes: Specific pieces of information about an entity. These are going to be the attributes or columns in tables
Primary Key: Attribute taht uniquely identify an entry in the database table
Composite attribute: Attribute that can be broken up into two or more sub-attributes
Multi-valued attribute: An attribute that can have more than one value
Derived attribute: Attribute that can be derived from other attributes. Does not go into the actual table but is used for visual convenience
Relationships: Defines a relationship between two entities to describe how they relate. Denoted by “Takes” or “Taken by”
Cardinality: 1:1, 1:N, N:M referring to how the relationships act (ie. one side is connected to many of another side, etc.)
Weak Entity: Entity that cannot be uniquely identified by its attributes alone. It needs to be expressed using attributes of another entity as well

Mapping ER to Schema

Mapping regular entities entity types -> Create tables
Mapping of weak entity types -> Create a relation table
Mapping of binary 1:1 relationship types -> Foreign keys
Mapping of binary 1:N relationship types -> 1 sides PK as FK of N side
Mapping of binary N:M relationship types -> Create a new table whose PK is combination of each side’s PK