Master database design principles and advanced SQL queries for real-world applications
What Will You Learn?
Database Foundations
Database Design Principles
SQL Basics – Querying Data
Advanced SQL Techniques
Real-World Applications & Optimization
Course Content
Database Foundations
What is a Database?
A database is an organized collection of data that can be easily accessed, managed, and updated.
Examples: Banking records, E-commerce product catalogs, Social media user data.
1.2 Types of Databases
Relational Databases (RDBMS): Data stored in tables with rows and columns (MySQL, PostgreSQL, Oracle, SQL Server).
NoSQL Databases: Flexible schema, used for big data and unstructured data (MongoDB, Cassandra, Redis).
1.3 Relational Database Concepts
Tables: Store data in rows and columns.
Records (Rows): Each row represents a single entry.
Attributes (Columns): Define properties of the data.
Primary Key: Unique identifier for each record.
Foreign Key: Connects two tables.
1.4 Transactions
Properties of transactions (ACID):
Atomicity: All or nothing.
Consistency: Database state remains valid.
Isolation: No interference between transactions.
Durability: Changes persist even after failure.
Database Design Principles
Data Modeling
Entity-Relationship (ER) Diagrams to visualize data.
Example: Customer → places → Order → contains → Product.
2.2 Normalization
Breaking down large tables into smaller ones to eliminate redundancy.
Forms of Normalization:
1NF: Eliminate repeating groups.
2NF: Remove partial dependency.
3NF: Remove transitive dependency.
2.3 Keys in Database Design
Primary Key → Uniquely identifies a row.
Foreign Key → Ensures referential integrity.
Composite Key → Combination of two or more columns.
2.4 Relationships
One-to-One (1:1) → Each employee has one ID card.
One-to-Many (1:M) → One customer places many orders.
Many-to-Many (M:M) → Students enrolled in multiple courses.
2.5 Indexing
Improves query performance.
Types: Clustered, Non-clustered, Composite, Unique Indexes.
SQL Basics – Querying Data
Introduction to SQL
SQL (Structured Query Language) is used to interact with relational databases.
3.2 Basic Commands
DDL (Data Definition Language): Create, alter, drop tables.
DML (Data Manipulation Language): Insert, update, delete.
DQL (Data Query Language): Select queries.
DCL (Data Control Language): Grant, revoke permissions.
3.3 Creating Tables
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
Name VARCHAR(100),
Email VARCHAR(100),
City VARCHAR(50)
);
3.4 Inserting Data
INSERT INTO Customers (CustomerID, Name, Email, City)
VALUES (1, 'Alice', 'alice@example.com', 'New York');
3.5 Retrieving Data
SELECT Name, City
FROM Customers
WHERE City = 'New York';
3.6 Updating and Deleting
UPDATE Customers SET City = 'Boston' WHERE CustomerID = 1;
DELETE FROM Customers WHERE CustomerID = 1;
Advanced SQL Techniques
Joins
INNER JOIN → Common records in both tables.
LEFT JOIN → All from left, matched from right.
RIGHT JOIN → All from right, matched from left.
FULL OUTER JOIN → All records from both.
SELECT Orders.OrderID, Customers.Name
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
4.2 Subqueries
SELECT Name FROM Customers
WHERE CustomerID IN (SELECT CustomerID FROM Orders WHERE Amount > 500);
4.3 Aggregations
Functions: COUNT, SUM, AVG, MAX, MIN.
Grouping data with GROUP BY.
SELECT City, COUNT(*) AS Total_Customers
FROM Customers
GROUP BY City;
4.4 Window Functions
ROW_NUMBER(), RANK(), DENSE_RANK(), LEAD(), LAG().
SELECT Name, Salary,
RANK() OVER (ORDER BY Salary DESC) AS SalaryRank
FROM Employees;
4.5 Stored Procedures & Functions
Procedure: Predefined SQL block.
Function: Returns a single value.
CREATE PROCEDURE GetCustomerOrders @CustomerID INT
AS
SELECT * FROM Orders WHERE CustomerID = @CustomerID;
4.6 Triggers
Executes automatically on insert, update, delete.
CREATE TRIGGER trg_after_insert
ON Customers
AFTER INSERT
AS
PRINT 'New customer added!';
Real-World Applications & Optimization
Real-World Applications
E-commerce: Products, Customers, Orders.
Banking: Accounts, Transactions, Loans.
Social Media: Users, Posts, Comments, Likes.
Healthcare: Patients, Doctors, Appointments.
5.2 Query Optimization
Use indexes wisely.
Avoid SELECT *, only fetch required columns.
Analyze queries with EXPLAIN.
Partition large tables for scalability.
5.3 Database Security
User roles and permissions.
SQL Injection prevention using prepared statements.
Data encryption.
5.4 Backup and Recovery
Regular database backups.
Replication & failover strategies.
Cloud databases (AWS RDS, Azure SQL, Google Cloud SQL).
