SQL and Relational Databases

Relational databases have been a cornerstone of data management for decades, offering structured and efficient ways to store, retrieve, and manipulate data. Structured Query Language (SQL), is the ubiquitous tool used to interact with these databases, allowing for powerful data handling and analysis. In this article, we’ll dive into the fundamentals of relational databases and explore how SQL enables us to effectively manage and utilize data.

Relational Model

Relational databases rely on the relational model which primarily consists of tables. A table is a structured set of data made up of rows and columns.

• Tables represent entities. For instance, a Users table would have information about users.
• Columns are attributes of that entity, such as username or email.
• Rows represent individual records in that table.

Relationships

• One-to-one (1:1): A record in Table A relates to one, and only one, record in Table B. For example, a person to their social security number.
• One-to-Many (1:M): A record in Table A can relate to many records in table B. For example, a single author can write multiple books
• Many-to-Many (M:N): Records in Table A can relate to multiple records in table B and vice versa. Think of students and courses; students can enroll in multiple courses, and courses can have multiple students.

Basic Queries

Throughout this post, we’ll use a hypothetical Bookstore database, primarily comprising tables: Authors, Books, and Orders.

CREATE TABLE Authors (
    AuthorID int PRIMARY KEY AUTO_INCREMENT,
    AuthorName varchar(255) NOT NULL,
    BirthYear int
);

CREATE TABLE Books (
    BookID int PRIMARY KEY AUTO_INCREMENT,
    Title varchar(255) NOT NULL,
    AuthorID int,
    Price float,
    FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID)
);

CREATE TABLE Orders (
    OrderID int PRIMARY KEY AUTO_INCREMENT,
    BookID int,
    OrderDate date,
    FOREIGN KEY (BookID) REFERENCES Books(BookID)
);

SELECT & FROM

SQL’s power is in its querying capabilities. The ‘SELECT’ statement is pivotal for this.

This is how we retrieve specific columns from a table:

SELECT Title, Price
FROM Books;

WHERE

Filter records based on specific conditions.

For example, to get all authors born after 1980:

SELECT AuthorName
FROM Authors
WHERE BirthYear > 1980;

A full list of comparison operators can be found here.

BETWEEN

Filter records within a specific range. For instance, to find books priced between $10 and $50:

SELECT Title
FROM Books
WHERE Price BETWEEN 10 AND 25;

LIKE

Search for a specified pattern.

If you want to find authors with the name starting with ‘Jo’:

SELECT AuthorName
FROM Authors
WHERE AuthorName LIKE 'Jo%';

If you want to find books with titles containing ‘Data’:

SELECT Title
FROM Books
WHERE Title LIKE '%Data%';

If you want to find books with titles ending in ‘SQL’:

SELECT Title
FROM Books
WHERE Title LIKE '%SQL';

ORDER BY

Sort the result set. For instance, sorting books by title:

SELECT Title
FROM Books
ORDER BY Title ASC;

HAVING

You may notice that this looks very similar to the WHERE clause. There is a difference between them though. While WHERE filters rows before aggregating data, HAVING filters after.

To find books ordered more than one time, we filter the results of the GROUP BY clause.

SELECT B.BookID, B.Title
FROM Books B
JOIN Orders O ON B.BookID = O.BookID
GROUP BY B.BookID, B.Title
HAVING COUNT(O.OrderID) > 1;

JOIN

Used to combine records from two or more tables based on a related column. There are four main types:

• Inner Join: Returns records that have matching values in both tables.

  SELECT Books.Title, Authors.AuthorName
  FROM Books
  INNER JOIN Authors ON Books.AuthorID = Authors.AuthorID;

• Left Join: Returns all records from the left table, and matched records from the right table.

  SELECT Authors.AuthorName, Books.Title
  FROM Authors
  LEFT JOIN Books ON Authors.AuthorID = Books.AuthorID;

• Right Join: The opposite of the LEFT JOIN. It returns all records from the right table, and the matched records from the left table.
• Full (Outer) Join: Returns all records when there’s a match in either the left or right table.

  SELECT Books.Title, Authors.AuthorName
  FROM Books
  FULL JOIN Authors ON Books.AuthorID = Authors.AuthorID;

  MySQL does not support FULL JOIN directly. Here’s how to simulate it by combining joins:

  SELECT Books.Title, Authors.AuthorName
  FROM Books
  LEFT JOIN Authors ON Books.AuthorID = Authors.AuthorID
  UNION
  SELECT Books.Title, Authors.AuthorName
  FROM Books
  RIGHT JOIN Authors ON Books.AuthorID = Authors.AuthorID;

DML Statements

Data Manipulation Language (DML) deals with data manipulation, and includes:

INSERT

Insert data into a table:

INSERT INTO tableName (column1, column2)
VALUES (value1, value2);

This one inserts a new book, ‘Learning SQL with John’:

INSERT INTO Books (BookID, Title, AuthorID, Price)
VALUES (11, 'Learning SQL with John', 6, 29.99);

I do not have to specify the BookID, as we declared it to have an AUTO_INCREMENT constraint:

INSERT INTO Books (Title, AuthorID, Price)
VALUES ('Learning SQL with John', 6, 29.99);

This is the same process you follow for DEFAULT constraints as well.

UPDATE

Modify existing data:

UPDATE tableName
SET column1 = value1, column2 = value2
WHERE condition;

This one updates the price of the ‘SQL Basics’ book:

UPDATE Books
SET Price = 18.99
WHERE Title = 'SQL Basics';

DELETE

Remove existing data:

DELETE FROM tableName
WHERE condition;

This one deletes orders that were placed before 2023:

DELETE FROM Orders
WHERE OrderDate < '2023-01-01';

Primary Keys & Foreign Keys

• Primary Key: A unique identifier for a record in a table. For example, in our Authors table, AuthorID is the primary key.
• Foreign Key: A field (or collection of fields) in one table that refers to the primary key in another table. The AuthorID in our Books table is a foreign key referencing Authors.

-- Finding all books by a specific author using JOIN on Primary and Foreign Key:
SELECT Books.Title
FROM Books
INNER JOIN Authors ON Books.AuthorID = Authors.AuthorID
WHERE Authors.AuthorName = 'J.K. Rowling';

Normalization

Normalization is a method used in relational database design to reduce data redundancy and inaccuaries. The process involves decomposing tables in a way that eliminates potential data anomalies. Various levels, or forms, of normalization exist, and each has a specific set of requirements:

• 1NF:
  • All the values in each column are atomic, which means each field contains only a single value, and there are no repeating groups or arrays.
• 2NF: All non-key attributes are fully functionally dependent on the primary key. This means that there is no partial dependency of any column on the primary key.
• 3NF: All its attributes are not only fully functionally dependent on the primary key but also non-transitively dependent.
• BCNF: For every non-trivial functional dependency X -> Y, X should be a super key

DDL Statements

Data Definition Language (DDL) deals with the structure of the database:

CREATE TABLE

CREATE TABLE tableName (
    column1 datatype constraint,
    column2 datatype,
    ...
);

ALTER TABLE

Modify an existing table:

ALTER TABLE tableName
ADD COLUMN columnName;

DROP TABLE

Remove a table:

DROP TABLE tableName;

We can drop our Authors table:

DROP TABLE Authors;

Constraints

Constraints define rules and relationships. They enforce the relational integrity of the database.

• NOT NULL: Ensures a column cannot have a NULL value.
• UNIQUE: Ensures all values in a column are unique.
• CHECK: Validates the values of a column against a specified condition.
• DEFAULT: Provides a default value for the column.
• AUTO_INCREMENT: Automatically generates a new unique number for each new record. Often used for automatically handling primary keys.

-- Creating an Authors table with constraints:
CREATE TABLE Authors (
    AuthorID int PRIMARY KEY AUTO_INCREMENT,
    AuthorName varchar(255) NOT NULL UNIQUE,
    BirthYear int CHECK(BirthYear > 1900),
    Country varchar(50) DEFAULT 'Unknown'
);

Advanced SQL Concepts

Aliasing

Aliases are temporary names given to a table or column for the purpose of a particular SQL query. It improves readability:

SELECT a.AuthorName AS Writer, b.Title AS BookTitle
FROM Authors a
INNER JOIN Books b ON a.AuthorID = b.AuthorID;

UNION

Combine the result set of two or more SELECT statements (must have the same number of columns):

SELECT AuthorName FROM Authors WHERE Country = 'USA'
UNION
SELECT AuthorName FROM Authors WHERE BirthYear < 1970;

Aggregate Functions

Aggregate functions are utilized to execute a calculation on a set of values to return a singular value.

• COUNT(): Returns the number of items in a set.

  SELECT COUNT(BookID)
  FROM Books;

• SUM(): Adds up the values in a numeric column.

  SELECT SUM(Price)
  FROM Books;

• AVG(): Determines the average value of a numeric column.

  SELECT AVG(Price)
  FROM Books;

• MAX() and MIN(): Find the highest and lowest value in a numeric column, respectively.

  SELECT MAX(Price), MIN(Price)
  FROM Books;

Subqueries

Subqueries, or nested queries, are queries embedded within other SQL queries. They can be placed in various sections, including the SELECT, FROM, or WHERE clauses.

Consider this example which identifies authors who’ve written books priced above the average:

SELECT AuthorName 
FROM Authors 
WHERE AuthorID IN 
    (SELECT AuthorID 
     FROM Books 
     WHERE Price > 
         (SELECT AVG(Price) FROM Books));

Transactions

Transactions are units of work that are executed as a single passage, while ensuring the ACID properties are maintained:

• Atomicity: Ensures that all operations within the work unit are completed successfully. If not, the transaction is aborted.
• Consistency: Ensures that the database properly changes states upon a successfully committed transaction.
• Isolation: Enables transactions to operate independently of and transparent to each other.
• Durability: Ensures that the result or effect of a committed transaction persists in case of a system failure.

Suppose you want to update a book’s price and insert a new order for it. Both operations should be atomic: if one fails, the other should be rolled back.

BEGIN TRANSACTION;

UPDATE Books
SET Price = 20.99
WHERE BookID = 1;

INSERT INTO Orders (BookID, OrderDate)
VALUES (1, '2023-10-12');

COMMIT;

If there was an error in the above operations, you’d use ROLLBACK to undo them.

Indexes

Indexes enhance the speed of data retrieval operations in a database. However, they also have some trade-offs:

• While indexes significantly improve query speed, they can slow down data modification operations like INSERT, UPDATE, and DELETE due to the additional work required to maintain the index.
• Indexes consume additional disk space.

The choice of indexing should be made carefully based on the specific use case and database workload.

Creating an index on the AuthorName column of the Authors table:

CREATE INDEX idx_authorname
ON Authors (AuthorName);

Views

A view is essentially a virtual table constructed from a query. It doesn’t store data, but instead provides a way to access data in a more structured or restricted manner.

For instance, if you often need a list of all books and their authors, you can create a view:

CREATE VIEW View_BooksAuthors AS
SELECT Books.Title, Authors.AuthorName
FROM Books
INNER JOIN Authors ON Books.AuthorID = Authors.AuthorID;

Stored Procedures & Functions

Stored procedures are sets of precompiled SQL statements. They enhance performance, security, and code reusability.

A procedure to fetch all books by a specific author might look like this:

CREATE PROCEDURE GetBooksByAuthor (@AuthorName varchar(255))
AS
BEGIN
   SELECT Books.Title
   FROM Books
   INNER JOIN Authors ON Books.AuthorID = Authors.AuthorID
   WHERE Authors.AuthorName = @AuthorName;
END;

Triggers

Triggers are special kinds of stored procedures that run automatically when specific events occur in the database. They’re used to maintain the integrity of data.

For instance, if we want to automatically log every price change for a book into a hypothetical PriceLogs table:

CREATE TRIGGER UpdateBookPrice 
AFTER UPDATE ON Books 
FOR EACH ROW 
BEGIN
  IF NEW.Price <> OLD.Price THEN
    INSERT INTO PriceLogs (BookID, OldPrice, NewPrice, UpdateDate)
    VALUES (NEW.BookID, OLD.Price, NEW.Price, NOW());
  END IF;
END;

Common Table Expressions

CTEs provide a temporary result set for a query that can then be referenced within the main query. They improve readability and can simplify complex queries.

Here’s an example that finds the total number of books each author has written:

WITH AuthorBookCount AS (
    SELECT AuthorID, COUNT(BookID) as TotalBooks
    FROM Books
    GROUP BY AuthorID
)

SELECT Authors.AuthorName, AuthorBookCount.TotalBooks
FROM Authors
JOIN AuthorBookCount ON Authors.AuthorID = AuthorBookCount.AuthorID;

More Resources

• Learn SQL in 60 Minutes
• Learn Database Normalization - 1NF, 2NF, 3NF, 4NF, 5NF
• Intermediate SQL Tutorials
• Advanced SQL Tutorials
• SQL interview questions and answers | Entry level data analyst interview