Relational Databases

Originally based upon relational algebra and tuple relational calculus, Sequential Query Language (SQL) consists of many types of statements used for database CRUD (Create, Read, Update, Delete) operations.

Structure

Relational Databases are divided into a structure of components:

  • Databases: sets of Tables

    • Tables: sets of Records

      • Records: sets of Columns

        • Columns: contain data such as numbers, text, objects

Commands

Important commands include:

  • SELECT - extracts data from a database

  • UPDATE - updates data in a database

  • DELETE - deletes data from a database

  • INSERT INTO - inserts new data into a database

  • CREATE DATABASE - creates a new database

  • ALTER DATABASE - modifies a database

  • CREATE TABLE - creates a new table

  • ALTER TABLE - modifies a table

  • DROP TABLE - deletes a table

  • CREATE INDEX - creates an index (search key)

  • DROP INDEX - deletes an index

Clauses

Important clauses include:

  • FROM - specifies the table from which to operate on data

  • WHERE - specifies conditions for the operations to take place

  • ORDER BY - specifies the order for sorting records

  • GROUP BY - specifies the criteria for grouping records; the first record in the group is selected

  • INTO - as part of a SELECT statement, data is inserted into a specified table

  • JOIN - specified how to join records from multiple tables to perform an operation (see below)

Operators

Important operators include:

  • Arithmetic +, -, *, /, %

  • Comparison =, >, <, >=, <=, <>

  • UNION combines the result set of two or more SELECT statements

Subqueries

Subqueries are a way to nest queries to make the results of the inner subquery available to an outer query.

  • Subqueries are the rough equivalent of creating a Python (and other programming languates) function that returns a result used by other code.

  • Subqueries must be enclosed in parentheses.

  • Subqueries are followed by, if needed, a name given to the result.

  • If no name is used for the subquery result, the result is returned to the code preceding the subquery.

  • The subquery result is returned as a table containing the data retrieved by the subquery.

  • Indentation of the subquery is not necessary but is used to identify the code as a subquery.

The example below uses a subquery to sort (ORDER BY) rows and the outer code groups the rows and keeps just the row with the highest population for a given Country:

SELECT  * 
   FROM (SELECT Country, Population
         FROM Customers
         ORDER BY Country, Population DESC)
GROUP BY Country

Table Creation

A table is created using the CREATE TABLE statement usually containing Column names and types. An example is:

CREATE TABLE Customers (
    CustomerID int NOT NULL,
    CustomerFirstName varchar(255) NOT NULL,
    CustomerLastName varchar(255) NOT NULL,
    PRIMARY KEY (CustomerID),
    );

Temporary Tables

Temporary Tables:

  • are retained only as long as the database connection which created and populated them exists

  • are only accessible from their creation context, such as the connection

  • names are prefixed with the # character

  • provide a way to avoid excessive indenting of subqueries

  • must be created using CREATE TABLE code before they are used

Table Joins

Join operations are used for combining the data from multiple tables.

For examples of SQL Joins, go here. 

Commonly used SQL Join operations are:

  • LEFT JOIN - all records from the left table (the one mentioned first) are included, records matching keys are included from the right table

  • RIGHT JOIN - all records from the right table (the one mentioned second) are included, records matching keys are included from the left table

  • INNER JOIN - requires each record in the two joined tables to have matching records

  • OUTER JOIN - does not require each record in the two joined tables to have a matching record

Keywords and Reserved Words

SQL keywords and reserved words are used to indicate aspects such as actions, conditions and identifications.

A list of MySQL keywords and reserved words can be found here.

Primary, Field and Foreign Keys

Keys are indexes used for retrieving database records:

  • Keys can be made up of one or more database table fields.

  • A Primary Key uniquely identifies each record in a table.

  • A Foreign Key is used to link two tables together.

Statements

SQL statements combine keywords, reserved words and values to perform processes.

An explanation of MySQL statements can be found here.

Examples are:

SELECT * 
  FROM persons 
 WHERE profession = "engineer"

SELECT first_name, last_name 
  FROM persons 
 WHERE city = "Chicago"

References