Session Overview

WHERE, ORDER BY and LIMIT

These clauses help us filter, sort and restrict records returned by a query.

WHERE Clause

SELECT *
FROM Student
WHERE City = 'Mumbai';

ORDER BY

SELECT *
FROM Student
ORDER BY Name;

ORDER BY DESC

SELECT *
FROM Student
ORDER BY Name DESC;

LIMIT

SELECT *
FROM Student
LIMIT 2;

Relationships and Foreign Keys

Relationships allow tables to be connected with each other.

Example

One Category can contain many Blog Posts.

Create Category Table

CREATE TABLE Category
(
    Id INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(100)
);

Create Post Table

CREATE TABLE Post
(
    Id INT PRIMARY KEY AUTO_INCREMENT,
    Title VARCHAR(200),
    CategoryId INT,
    FOREIGN KEY(CategoryId)
    REFERENCES Category(Id)
);

Joins (Introduction)

Joins are used to retrieve data from multiple related tables.

Category Table

Post Table

INNER JOIN Example

SELECT
    Post.Title,
    Category.Name
FROM Post
INNER JOIN Category
ON Post.CategoryId = Category.Id;

+---------------+------------+
| Title         | Name       |
+---------------+------------+
| Django Basics | Technology |
| Goa Trip      | Travel     |
+---------------+------------+

Transactions

A Transaction is a group of SQL statements executed as a single unit of work.

Transactions help maintain data consistency and prevent partial updates.

COMMIT

COMMIT permanently saves changes made during a transaction.

START TRANSACTION;

UPDATE Student
SET City = 'Pune'
WHERE Id = 1;

COMMIT;

ROLLBACK

ROLLBACK cancels all changes made during the current transaction.

START TRANSACTION;

UPDATE Student
SET City = 'Mumbai'
WHERE Id = 1;

ROLLBACK;

Bank Transfer Example

START TRANSACTION;

UPDATE Accounts
SET Balance = Balance - 1000
WHERE Id = 1;

UPDATE Accounts
SET Balance = Balance + 1000
WHERE Id = 2;

COMMIT;

Stored Procedures (Basics)

A Stored Procedure is a group of SQL statements stored inside the database and executed whenever required.

Advantages

• Reduces code duplication.
• Improves maintainability.
• Improves performance for frequently executed queries.
• Centralizes business logic inside the database.

Example

DELIMITER //

CREATE PROCEDURE GetStudents()
BEGIN
    SELECT * FROM Student;
END //

DELIMITER ;

Execute Stored Procedure

CALL GetStudents();

All records from Student table will be displayed.

Blog Database Design

Before starting our Django Blog Project, let's understand the database design.

Tables Required

Category Table

Category
--------------------------------
Id
Name

Post Table

Post
--------------------------------
Id
Title
Description
Image
CreatedDate
CategoryId

Relationship

One Category
      |
      |
      |
Many Posts

Table Creation Example

CREATE TABLE Category
(
    Id INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(100) NOT NULL
);

CREATE TABLE Post
(
    Id INT PRIMARY KEY AUTO_INCREMENT,
    Title VARCHAR(200) NOT NULL,
    Description TEXT,
    Image VARCHAR(255),
    CreatedDate DATETIME DEFAULT CURRENT_TIMESTAMP,
    CategoryId INT,

    FOREIGN KEY(CategoryId)
    REFERENCES Category(Id)
);