Databases
SQL Basics
Tarapong Sreenuch
8 February 2024
克明峻德，格物致知
Quick Recap: Relational Databases
Overview of Relational Model:
Relational Databases
§ Data organized in structured tables consisting of rows (tuples) and columns (attributes).
§ Emphasis on data uniqueness (no duplicate rows) and maintaining data integrity through typed and static
An Introduction
attributes.
SQL Query Example:
SELECT Name, Email
FROM Members
WHERE JoinYear = 2020;
§ Purpose: Retrieve names and emails of 2020 members
§ Output Table:
| Name | Email |
|---------------|------------------------|
| Bethany Cane | bethany.c@[Link] |
| Evan Lee | [Link]@[Link] |
Tarapong Sreenuch PhD Databases: SQL Basics 8 February 2024 2
A city's transportation
department relies on Q: What are the limitations
manual methods to
of manual traffic data
analyze traffic analysis in a growing city?
patterns.
With the city's A: The manual approach is
growth, the data unable to process large-scale,
becomes too complex real-time data, leading to
and voluminous to delayed responses and less
handle effectively. effective traffic management.
Tarapong Sreenuch PhD Databases: SQL Basics 8 February 2024 3
Structured Query Language (SQL)
Nature of Databases
Relational SQL:
§ Declarative, focusing on specifying what the result should be, unlike procedural languages that describe how to
perform tasks.
An Introduction
Declarative vs Procedural:
§ SQL Retrieval Query: § Python Query Example:
SELECT Name, Email for member in Members if member['JoinYear'] !== 2020 :
FROM Members print(member['Name'], member['Email'])
WHERE JoinYear = 2020;
Key Functions of SQL:
§ Data Definition: `CREATE`, `ALTER`, `DROP` for structuring database schemas.
§ Data Manipulation: `INSERT`, `UPDATE`, `DELETE`, `SELECT` for handling data within tables.
§ Data Control: `GRANT`, `REVOKE` for managing data access and permissions.
Tarapong Sreenuch PhD Databases: SQL Basics 8 February 2024 4
Basic SQL Query
Understanding `SELECT` Statements:
Relational Databases
§ `SELECT` statements are fundamental in SQL for retrieving specific data from a database.
§ They allow users to specify exactly which columns to display and set conditions for selecting rows.
An Introduction
Example: Retrieve All Records
§ SQL Query:
SELECT *
FROM Books;
§ Purpose: Retrieves all columns for every row in the 'Books' table.
§ Output Table:
| BookID | Title | Author | Genre | PublishedYear |
|--------|-------------------------|--------------|-------------|---------------|
| 101 | Journey Through SQL | A. Coder | Technology | 2015 |
| 102 | The History of Databases| D. Base | Education | 2018 |
| !!... | !!... | !!... | !!... | !!... |
Tarapong Sreenuch PhD Databases: SQL Basics 8 February 2024 5
Retrieving Specific Data with SQL
Selecting Specific Columns and Applying Conditions:
§Relational Databases
SQL's `SELECT` command can be tailored to extract only certain columns from a database table. Adding a
`WHERE` clause allows for condition-based filtering of data.
An Introduction
Example: Filter by Genre
§ Source Table: ‘Books’
| BookID | Title | Author | Genre | PublishedYear |
|--------|---------------------------|----------------|-------------|---------------|
| 101 | Journey Through SQL | A. Coder | Technology | 2015 |
| 102 | The History of Databases | D. Base | Education | 2018 |
| 103 | Adventures in Coding | P. Programmer | Fiction | 2020 |
| !!... | !!... | !!... | !!... | !!... |
§ SQL Query:
SELECT Title, Author
FROM Books
WHERE Genre = ’Technology’;
§ Purpose: To display the titles and authors of books that fall under the 'Technology' genre.
Tarapong Sreenuch PhD Databases: SQL Basics 8 February 2024 6
Retrieving Specific Data with SQL
Selecting Specific Columns and Applying Conditions:
§Relational Databases
SQL's `SELECT` command can be tailored to extract only certain columns from a database table. Adding a
`WHERE` clause allows for condition-based filtering of data.
An Introduction
Example: Filter by Genre
§ Source Table: ‘Books’
| BookID | Title | Author | Genre | PublishedYear |
|--------|---------------------------|----------------|-------------|---------------|
| 101 | Journey Through SQL | A. Coder | Technology | 2015 |
| 102 | The History of Databases | D. Base | Education | 2018 |
| 103 | Adventures in Coding | P. Programmer | Fiction | 2020 |
| !!... | !!... | !!... | !!... | !!... |
§ SQL Query:
SELECT Title, Author
FROM Books
WHERE Genre = ’Technology’;
§ Purpose: To display the titles and authors of books that fall under the 'Technology' genre.
Tarapong Sreenuch PhD Databases: SQL Basics 8 February 2024 7
Retrieving Specific Data with SQL
Selecting Specific Columns and Applying Conditions:
§Relational Databases
SQL's `SELECT` command can be tailored to extract only certain columns from a database table. Adding a
`WHERE` clause allows for condition-based filtering of data.
An Introduction
Example: Filter by Genre
§ Source Table: ‘Books’
| BookID | Title | Author | Genre | PublishedYear |
|--------|---------------------------|----------------|-------------|---------------|
| 101 | Journey Through SQL | A. Coder | Technology | 2015 |
| 102 | The History of Databases | D. Base | Education | 2018 |
| 103 | Adventures in Coding | P. Programmer | Fiction | 2020 |
| !!... | !!... | !!... | !!... | !!... |
§ SQL Query: § Output Table:
SELECT Title, Author | Title | Author |
FROM Books | ------------------- | -------- |
WHERE Genre = ’Technology’; | Journey Through SQL | A. Coder |
§ Purpose: To display the titles and authors of books that fall under the 'Technology' genre.
Tarapong Sreenuch PhD Databases: SQL Basics 8 February 2024 8
Database Internals
SQL Query Processing Overview
Relational Databases
§ Parsing: Analyze SQL syntax and structure.
§ Conversion: Translate SQL to relational algebra, a set-based query language.
§An Introduction
Execution: Database engine executes relational algebra expressions.
§ Optimization: Database engine optimizes operations for performance.
Relational Algebra in SQL [Members Table]
|
§ Key Operations: | (σ JoinYear=2020)
V
§ Selection (σ): Filters rows based on criteria. [Filtered Rows - 2020]
§ Projection (π): Retrieves specific columns. |
§ Join (⋈): Combines rows from different tables. | (π Name)
V
[Result - Names of Members Joined in 2020]
Example Translation
§ SQL: SELECT Name FROM Members WHERE JoinYear = 2020;
π Name (σ JoinYear=2020 (Members))
§ Relational Algebra:
Tarapong Sreenuch PhD Databases: SQL Basics 8 February 2024 9
For-Each Semantics
Understanding Through Examples:
Relational Databases
§ Source Table: 'Books':
| BookID | Title | Author | Genre | PublishedYear |
|--------|---------------------------|----------------|-------------|---------------|
An Introduction
| 101 | Journey Through SQL | A. Coder | Technology | 2015 |
| 102 | The History of Databases | D. Base | Education | 2018 |
| 103 | Adventures in Coding | P. Programmer | Fiction | 2020 |
| !!... | !!... | !!... | !!... | !!... |
§ SQL Query: § Pseudocode Equivalent:
SELECT Title, Author for each row in Books:
FROM Books § if ([Link] !== 'Technology'):
WHERE Genre = ’Technology’; § output [Link], [Link]
§ Retrieves titles from the 'Books' table where the genre is 'Technology'.
Tarapong Sreenuch PhD Databases: SQL Basics 8 February 2024 10
For-Each Semantics
Understanding Through Examples:
Relational Databases
§ Source Table: 'Books'
| BookID | Title | Author | Genre | PublishedYear |
|--------|---------------------------|----------------|-------------|---------------|
An Introduction
| 101 | Journey Through SQL | A. Coder | Technology | 2015 |
| 102 | The History of Databases | D. Base | Education | 2018 |
| 103 | Adventures in Coding | P. Programmer | Fiction | 2020 |
| !!... | !!... | !!... | !!... | !!... |
§ SQL Query: § Pseudocode Equivalent:
SELECT Title, Author for each row in Books:
FROM Books § if ([Link] !== 'Education'):
WHERE Genre = ’Education’; § output [Link], [Link]
§ Retrieves titles from the 'Books' table where the genre is 'Technology'.
§ Output:
| Title | Author |
| ------------------------ | ------- |
Tarapong Sreenuch PhD Databases: SQL Basics 8 February 2024 11
For-Each Semantics
Understanding Through Examples:
Relational Databases
§ Source Table: 'Books':
| BookID | Title | Author | Genre | PublishedYear |
|--------|---------------------------|----------------|-------------|---------------|
An Introduction
| 101 | Journey Through SQL | A. Coder | Technology | 2015 |
| 102 | The History of Databases | D. Base | Education | 2018 |
| 103 | Adventures in Coding | P. Programmer | Fiction | 2020 |
| !!... | !!... | !!... | !!... | !!... |
§ SQL Query: § Pseudocode Equivalent:
SELECT Title, Author for each row in Books:
FROM Books § if ([Link] !== 'Education'):
WHERE Genre = ’Education’; § output [Link], [Link]
§ Retrieves titles from the 'Books' table where the genre is 'Technology'.
§ Output:
| Title | Author |
| ------------------------ | ------- |
| The History of Databases | D. Base |
Tarapong Sreenuch PhD Databases: SQL Basics 8 February 2024 12
For-Each Semantics
Understanding Through Examples:
Relational Databases
§ Source Table: 'Books':
| BookID | Title | Author | Genre | PublishedYear |
|--------|---------------------------|----------------|-------------|---------------|
An Introduction
| 101 | Journey Through SQL | A. Coder | Technology | 2015 |
| 102 | The History of Databases | D. Base | Education | 2018 |
| 103 | Adventures in Coding | P. Programmer | Fiction | 2020 |
| !!... | !!... | !!... | !!... | !!... |
§ SQL Query: § Pseudocode Equivalent:
SELECT Title, Author for each row in Books:
FROM Books § if ([Link] !== 'Eduction'):
WHERE Genre = ’Education’; § output [Link], [Link]
§ Retrieves titles from the 'Books' table where the genre is 'Technology'.
§ Output:
| Title | Author |
| ------------------------ | ------- |
| The History of Databases | D. Base |
Tarapong Sreenuch PhD Databases: SQL Basics 8 February 2024 13
For-Each Semantics
Understanding Through Examples:
Relational Databases
§ Source Table: 'Books':
| BookID | Title | Author | Genre | PublishedYear |
|--------|---------------------------|----------------|-------------|---------------|
An Introduction
| 101 | Journey Through SQL | A. Coder | Technology | 2015 |
| 102 | The History of Databases | D. Base | Education | 2018 |
| 103 | Adventures in Coding | P. Programmer | Fiction | 2020 |
| !!... | !!... | !!... | !!... | !!... |
§ SQL Query: § Pseudocode Equivalent:
SELECT Title, Author for each row in Books:
FROM Books § if ([Link] !== 'Eduction'):
WHERE Genre = ’Education’; § output [Link], [Link]
§ Retrieves titles from the 'Books' table where the genre is 'Technology'.
§ Output:
| Title | Author |
| ------------------------ | ------- |
| The History of Databases | D. Base |
| !!... | !!... |
Tarapong Sreenuch PhD Databases: SQL Basics 8 February 2024 14
SQL Functions: ORDER BY
§Relational
Purpose: Databases
Organizes query results by sorting data in either ascending or descending order.
§ Key Usage: Essential for presenting data in a readable and meaningful order, especially in reports and analytics.
An Introduction
Example and Explanation
§ SQL Query:
SELECT Title, PublishedYear
FROM Books
ORDER BY PublishedYear DESC;
§ Purpose: To sort books by their publication year, starting with the most recent.
§ Output Table:
| Title | PublishedYear |
| --------------------------- | ------------- |
| Adventures in Coding | 2020 |
| Mysteries of the Universe | 2019 |
| The History of Databases | 2018 |
| !!... | !!... |
Tarapong Sreenuch PhD Databases: SQL Basics 8 February 2024 15
SQL Functions: DISTINCT
§ Purpose: Eliminates duplicate rows from query results, providing a set of unique records.
Relational Databases
§ Key Usage: Useful in queries where identifying unique values is crucial, such as summarizing data or ensuring
data quality.
An Introduction
Example and Explanation
§ SQL Query:
SELECT DISTINCT Genre
FROM Books;
§ Purpose: To list all unique genres available in the 'Books' table.
§ Output Table:
| Genre |
| ---------- |
| Technology |
| Education |
| Fiction |
| !!... |
Tarapong Sreenuch PhD Databases: SQL Basics 8 February 2024 16
SQL Functions: COUNT
§Relational
Purpose: The `COUNT` function in SQL is used to count the number of rows in a table or rows that match a
Databases
certain condition.
§An Introduction
Common Uses: `COUNT` is essential for data analysis, particularly in scenarios requiring the quantification of
data, such as calculating totals, averages, or identifying data density.
Example and Explanation
§ SQL Query:
SELECT COUNT(*) AS TotalBooks
FROM Books;
§ Purpose: Determine the total number of books in the 'Books' table and label the output as ‘TotalBooks’.
§ Output Table:
| TotalBooks |
| ---------- |
| 5 |
Tarapong Sreenuch PhD Databases: SQL Basics 8 February 2024 17
Creating and Managing Tables
Table Creation
Relational in SQL:
Databases
§ Table Structures in Relational Databases: Tables are the primary structure for storing data in SQL databases,
composed of rows and columns.
An Introduction
§ Using `CREATE TABLE` Statement: The statement is crucial for defining new tables within a database,
specifying column names, data types, and constraints.
SQL Query:
§ Example: Creating the 'Books' Table:
CREATE TABLE Books (
BookID INT PRIMARY KEY,
Title VARCHAR(100),
Author VARCHAR(100),
Genre VARCHAR(100),
PublishedYear INT
);
§ `BookID INT PRIMARY KEY`: Unique identifier for each record.
Tarapong Sreenuch PhD Databases: SQL Basics 8 February 2024 18
Data Types
Understanding SQL Data Types
Relational Databases
§ Purpose of Data Types:
§ Data types define the kind of data a column can hold in an SQL table. Choosing the correct data type is crucial for data
An Introduction
integrity and query performance.
Common SQL Data Types
§ Integer (INT): A whole number without a decimal.
§ Character (CHAR): A fixed-length string.
§ Variable Character (VARCHAR): A string of text of variable length.
§ Date and Time Types (DATE, TIME, DATETIME): Represent dates and times.
§ Floating Point (FLOAT, DOUBLE): Numbers with fractional parts.
Tarapong Sreenuch PhD Databases: SQL Basics 8 February 2024 19
CHAR vs VARCHAR
Text DataDatabases
Relational Types
§ CHAR(size): A fixed-length string. Space-efficient for strings that are always the same length.
§ VARCHAR(size): A variable-length string. Ideal for strings that vary in length.
An Introduction
Usage Examples and Considerations
§ Choosing CHAR:
§ Use CHAR for data that is consistently the same size, such as country codes (CHAR(2) for 'US', 'UK').
§ Example: StateCode CHAR(2)
§ Choosing VARCHAR:
§ Use VARCHAR for data that varies in size, such as names or addresses.
§ Example: Title VARCHAR(100)
Performance Implications
§ CHAR:
§ Faster access due to fixed length. Can waste storage space if used for variable-length data.
§ VARCHAR:
§ More flexible and storage-efficient for variable-length data. Slightly slower access due to variable length management.
Tarapong Sreenuch PhD Databases: SQL Basics 8 February 2024 20
Entity Integrity
Understanding Primary Key
Relational Databases
§ Definition: A Primary Key is a unique identifier for each row in a database table.
§ Purpose: Ensures entity integity, i.e. uniqueness and integrity, of the data, preventing duplicate records.
An Introduction
Example:
§ Books Table:
| BookID (PK) | Title | Author | Genre | PublishedYear |
|-------------|---------------------------|----------------|-------------|---------------|
| 101 | Journey Through SQL | A. Coder | Technology | 2015 |
| 102 | The History of Databases | D. Base | Education | 2018 |
| 103 | Adventures in Coding | P. Programmer | Fiction | 2020 |
| !!... | !!... | !!... | !!... | !!... |
§ Failed Insert Query:
INSERT INTO Books (BookID, Title, Author, Genre, PublisedYear)
VALUES (101, ‘Data Mastery’, ‘L. Data’, 2021);
§ This query attempts to insert a new book record with an existing `BookID` (101).
§ Result:
§ The database system rejects the query and throws an error (e.g., `Duplicate entry '101' for key 'PRIMARY'`).
Tarapong Sreenuch PhD Databases: SQL Basics 8 February 2024 21
Composite Primary Keys
Advanced Key Concepts
§Relational
Definition:Databases
A composite primary key is formed by using two or more columns in combination to create a
unique identifier for each row in a table. It is used when no single column can serve as a unique identifier.
Example:
An Introduction
Reservation Table with Composite Key
§ Creating the BookReservations Table:
CREATE TABLE Borrow ( When to Use
BookID INT,
MemberID INT, § Applicable in many-to-many relationships or when
BorrowDate DATE, the uniqueness is only guaranteed by the
ReturnDate DATE,
PRIMARY KEY (BookID, MemberID, BorrowDate), combination of multiple attributes.
FOREIGN KEY (BookID) REFERENCES Books(BookID),
FOREIGN KEY (MemberID) REFERENCES Members(MemberID)
);
§ BookReservations Table:
| BookID (PK,FK) | MemberID (PK, FK) | BorrowDate (PK) | ReturnDate |
|----------------|-------------------|-----------------|-------------|
| 101 | 501 | 2022-01-15 | 2022-01-30 |
| 102 | 502 | 2022-01-16 | 2022-01-31 |
| 101 | 501 | 2022-02-01 | 2022-02-15 |
Tarapong Sreenuch PhD Databases: SQL Basics 8 February 2024 22
Foreign Keys
Understanding Foreign Keys
Relational Databases
§ Definition: A Foreign Key in one table is a field that references a Primary Key in another table, creating a link
between the two.
§An Introduction
Purpose: Ensures referential integrity and enables relationships across tables in a relational database.
SQL Query: Adding a Foreign Key
§ Example: Linking 'Borrow' and 'Books' Tables
CREATE TABLE Borrow (
BookID INT,
MemberID INT,
BorrowDate DATE,
ReturnDate DATE,
PRIMARY KEY (BookID, MemberID, BorrowDate),
FOREIGN KEY (BookID) REFERENCES Books(BookID),
FOREIGN KEY (MemberID) REFERENCES Members(MemberID)
);
§ Key Component:
§ `FOREIGN KEY (BookID) REFERENCES Books(BookID)`: `BookID` in the 'Borrow' table is the foreign key that references
`BookID` in the 'Books' table.
Tarapong Sreenuch PhD Databases: SQL Basics 8 February 2024 23
Referencial Integrity
Maintaining Data Consistency
§Relational Databases
Foreign Keys maintain consistency by ensuring that the relationship between the data in two tables remains
valid and that any changes to the data do not violate the integrity of the database.
An Introduction
Example: Enforcing Referential Integrity
§ Books Table: § Borrow Table:
| BookID (PK) | Title | Author | | BookID (FK) | MemberID | BorrowDate | ReturnDate |
|-------------|----------------|--------------| |-------------|----------|------------|-------------|
| 101 | SQL Essentials | A. Coder | | 101 | 501 | 2022-01-15 | 2022-01-30 |
| 102 | Database Deep | D. Analyst | | 102 | 502 | 2022-01-16 | 2022-01-31 |
| 101 | 501 | 2022-02-01 | 2022-02-15 |
§ Attempted Deletion from 'Books' Table:
§ SQL Query:
DELETE FROM Books WHERE BookID = 101;
§ Result: The database system rejects the deletion because 'Borrow' table has a foreign key that depends on the
book record being deleted.
ERROR: Cannot delete or update a parent row: a foreign key constraint fails (`database`.`borrow`, CONSTRAINT `borrow_bookid_fk`
FOREIGN KEY (`BookID`) REFERENCES `books` (`BookID`))
Tarapong Sreenuch PhD Databases: SQL Basics 8 February 2024 24
Hand-On SQL Demonstration
§Relational
Focus: Applying SQL in a realistic library database context.
Databases
§ Goal: Illustrate effective data extraction and manipulation using SQL.
An Introduction
Example Query
§ Identify Fiction Titles:
§ SQL Query:
SELECT Title, Author
FROM Books
WHERE Genre = ’Technology’;
§ Purpose: Demonstrate SQL's ability to filter data based on specific criteria.
Try It Yourself
1. Retrieve Recent Books:
§ Task: Write a query to list all books published after 2015.
2. Order By Genre:
§ Task: Modify the query to sort the results by genre in alphabetical order.
Tarapong Sreenuch PhD Databases: SQL Basics 8 February 2024 25
Recap and Key Takeaways
§ SQL is a declarative language basing on set operations. We specify ‘What’ from the data, but not ‘How’ to get
Relational Databases
it. SQL enables data retreival and manipulation to be carried out at scale.
§ Entity- and Referential-Integrities make data consistency possible across the business. Primary Key (PK)
enforces uniqueness within the table, and Foriegn Key (FK) ensures accurate information referring from the
An Introduction
other tables.
Example SQL Recap:
SELECT Title, PublishedYear
FROM Books
WHERE PublishedYear > 2005
ORDER BY PublishedYear DESC;
§ Purpose: Demonstrated a SQL application in targeted data retrieval and organising the query result.
Preparing for Joining Tables:
§ Next Lecture Preview:
§ Dive deeper into SQL JOINs.
§ Explore Relational Algebra equivalencies
Tarapong Sreenuch PhD Databases: SQL Basics 8 February 2024 26
Why is maintaining data integrity crucial in a database, and how
do primary and foreign keys contribute to this process?