📚 Database Module - Complete Practical
Notes
📖 Core Database Concepts
Concept Definition Practical Example
Organized collection of structured
Railway reservation system sto
Database information stored electronically in
passenger, train, and booking
tables
Software to create and manage MySQL, PostgreSQL, Oracle, SQ
DBMS
databases Server
Collection of related data organized in students table with columns: id
Table
rows and columns name, age
Row/
Single entry in a table One student's complete inform
Record
Column/
Single attribute of a table Student's name, age, or grade
Field
Keys in Databases
Key Type Definition Example
Primary Unique identifier for each record Student ID, PNR Number, Employ
Key (cannot be NULL) Code
Foreign customer_id in orders table refere
Field linking two tables together
Key customers table
Candidate Email, Phone Number (both could
Potential primary keys
Key PK)
Key Type Definition Example
Composite Multiple columns combined as a (Flight_No, Date) together unique
Key key identify a flight
Database Normalization
Normal
Rule Example
Form
Atomic values (no repeating Split "Phone Numbers" column into m
1NF
groups) rows
1NF + full functional Remove partial dependencies in comp
2NF
dependency keys
2NF + no transitive Remove dependencies between non-k
3NF
dependencies columns
sql
-- UNNORMALIZED TABLE (Bad Design)
CREATE TABLE orders (
order_id INT,
customer_name VARCHAR(100),
customer_phone VARCHAR(20),
product_names VARCHAR(200) -- Multiple products in one field -
VIOLATES 1NF
);
-- NORMALIZED TABLES (Good Design)
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(100),
phone VARCHAR(20)
);
CREATE TABLE products (
product_id INT PRIMARY KEY,
name VARCHAR(100),
price DECIMAL(10,2)
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
CREATE TABLE order_items (
order_id INT,
product_id INT,
quantity INT,
PRIMARY KEY (order_id, product_id),
FOREIGN KEY (order_id) REFERENCES orders(order_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);
🛠️SQL Commands - Practical Guide
1. Data Definition Language (DDL)
sql
-- CREATE DATABASE
CREATE DATABASE university;
USE university;
-- CREATE TABLE with constraints
CREATE TABLE students (
student_id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
age INT CHECK (age >= 16),
enrollment_date DATE DEFAULT CURRENT_DATE,
status ENUM('active', 'graduated', 'suspended') DEFAULT 'active'
);
-- CREATE TABLE with FOREIGN KEY
CREATE TABLE enrollments (
enrollment_id INT PRIMARY KEY AUTO_INCREMENT,
student_id INT,
course_id INT,
grade CHAR(2),
FOREIGN KEY (student_id) REFERENCES students(student_id),
FOREIGN KEY (course_id) REFERENCES courses(course_id)
);
-- ALTER TABLE (modify structure)
ALTER TABLE students ADD COLUMN phone VARCHAR(20);
ALTER TABLE students MODIFY COLUMN phone VARCHAR(15);
ALTER TABLE students DROP COLUMN phone;
ALTER TABLE students RENAME COLUMN email TO student_email;
-- DROP TABLE
DROP TABLE IF EXISTS temp_data;
-- TRUNCATE (remove all data but keep structure)
TRUNCATE TABLE logs;
2. Data Manipulation Language (DML)
sql
-- INSERT records
INSERT INTO students (first_name, last_name, email, age)
VALUES ('John', 'Doe', 'john@[Link]', 20);
-- INSERT multiple records
INSERT INTO students (first_name, last_name, email, age) VALUES
('Jane', 'Smith', 'jane@[Link]', 22),
('Bob', 'Johnson', 'bob@[Link]', 19),
('Alice', 'Brown', 'alice@[Link]', 21);
-- UPDATE records
UPDATE students
SET age = 21, status = 'graduated'
WHERE student_id = 1;
-- DELETE records
DELETE FROM students WHERE student_id = 3;
-- Be careful with DELETE (without WHERE deletes ALL records)
DELETE FROM students; -- Deletes everything!
3. Data Query Language (DQL) - SELECT
Basic SELECT Queries
sql
-- Select all columns
SELECT * FROM students;
-- Select specific columns
SELECT first_name, last_name, email FROM students;
-- Select with alias
SELECT
first_name AS "First Name",
last_name AS "Last Name",
email AS "Email Address"
FROM students;
-- DISTINCT values
SELECT DISTINCT status FROM students;
WHERE Clause - Filtering Data
sql
-- Comparison operators
SELECT * FROM students WHERE age >= 18;
SELECT * FROM students WHERE age BETWEEN 18 AND 25;
SELECT * FROM students WHERE last_name LIKE 'S%'; -- Starts with S
SELECT * FROM students WHERE email LIKE '%@[Link]'; -- Ends with
@[Link]
SELECT * FROM students WHERE first_name IN ('John', 'Jane', 'Bob');
-- Logical operators
SELECT * FROM students
WHERE age >= 18 AND status = 'active';
SELECT * FROM students
WHERE age < 18 OR status = 'suspended';
SELECT * FROM students
WHERE NOT status = 'graduated';
-- NULL handling
SELECT * FROM students WHERE phone IS NULL;
SELECT * FROM students WHERE phone IS NOT NULL;
ORDER BY - Sorting
sql
-- Single column sort
SELECT * FROM students ORDER BY last_name ASC;
SELECT * FROM students ORDER BY age DESC;
-- Multiple column sort
SELECT * FROM students
ORDER BY status ASC, age DESC, last_name ASC;
LIMIT and OFFSET - Pagination
sql
-- First 10 records
SELECT * FROM students LIMIT 10;
-- Next 10 records (page 2)
SELECT * FROM students LIMIT 10 OFFSET 10;
-- Top 5 oldest students
SELECT * FROM students ORDER BY age DESC LIMIT 5;
🔗 JOIN Operations - Practical Examples
Sample Tables for JOIN Examples
sql
-- Customers table
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(100),
city VARCHAR(50)
);
-- Orders table
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
amount DECIMAL(10,2)
);
-- Sample data
INSERT INTO customers VALUES
(1, 'John', 'New York'),
(2, 'Jane', 'Boston'),
(3, 'Bob', 'Chicago'),
(4, 'Alice', 'Miami');
INSERT INTO orders VALUES
(101, 1, '2024-01-15', 150.00),
(102, 2, '2024-01-16', 275.00),
(103, 1, '2024-01-17', 89.99),
(104, 4, '2024-01-18', 420.00);
-- Note: Customer 3 has no orders
INNER JOIN - Only matching records
sql
-- Get customers with their orders
SELECT
[Link],
[Link],
orders.order_id,
[Link],
orders.order_date
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id;
-- Result: Customer 3 (Bob) is excluded (no orders)
LEFT JOIN - All from left table, matching from right
sql
-- All customers, including those without orders
SELECT
[Link],
[Link],
orders.order_id,
[Link]
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;
-- Result: Bob appears with NULL for order_id, amount
RIGHT JOIN - All from right table, matching from left
sql
-- All orders with customer details (even if customer missing)
SELECT
[Link],
[Link],
orders.order_id,
[Link]
FROM customers
RIGHT JOIN orders ON customers.customer_id = orders.customer_id;
FULL OUTER JOIN - All records from both tables
sql
-- Not supported in MySQL, but can be simulated with UNION
SELECT * FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
UNION
SELECT * FROM customers
RIGHT JOIN orders ON customers.customer_id = orders.customer_id;
📊 Aggregate Functions and GROUP BY
Basic Aggregate Functions
sql
-- COUNT
SELECT COUNT(*) AS total_students FROM students;
SELECT COUNT(DISTINCT city) AS unique_cities FROM customers;
-- SUM
SELECT SUM(amount) AS total_sales FROM orders;
-- AVG
SELECT AVG(amount) AS average_order FROM orders;
-- MIN/MAX
SELECT
MIN(amount) AS smallest_order,
MAX(amount) AS largest_order
FROM orders;
GROUP BY - Categorizing Data
sql
-- Count customers per city
SELECT
city,
COUNT(*) AS customer_count
FROM customers
GROUP BY city
ORDER BY customer_count DESC;
-- Total sales per customer
SELECT
[Link],
COUNT(orders.order_id) AS order_count,
SUM([Link]) AS total_spent,
AVG([Link]) AS avg_order
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
GROUP BY customers.customer_id, [Link];
HAVING - Filtering Groups
sql
-- Customers with total spending > $200
SELECT
[Link],
SUM([Link]) AS total_spent
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
GROUP BY customers.customer_id, [Link]
HAVING SUM([Link]) > 200;
-- Cities with more than 2 customers
SELECT
city,
COUNT(*) AS customer_count
FROM customers
GROUP BY city
HAVING COUNT(*) >= 2;
📝 Subqueries and Advanced Queries
Subqueries in WHERE Clause
sql
-- Find customers who have placed orders
SELECT name FROM customers
WHERE customer_id IN (SELECT DISTINCT customer_id FROM orders);
-- Find students older than average
SELECT first_name, last_name, age FROM students
WHERE age > (SELECT AVG(age) FROM students);
Subqueries in SELECT Clause
sql
-- Show each customer with total spent and average overall
SELECT
name,
(SELECT SUM(amount) FROM orders WHERE customer_id =
customers.customer_id) AS personal_total,
(SELECT AVG(amount) FROM orders) AS overall_average
FROM customers;
Subqueries in FROM Clause
sql
-- Find top spending customers
SELECT
customer_name,
total_spent
FROM (
SELECT
[Link] AS customer_name,
SUM([Link]) AS total_spent
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
GROUP BY customers.customer_id, [Link]
) AS customer_totals
WHERE total_spent > 200;
Correlated Subqueries
sql
-- Find orders above customer's average
SELECT
o1.order_id,
o1.customer_id,
[Link]
FROM orders o1
WHERE amount > (
SELECT AVG([Link])
FROM orders o2
WHERE o2.customer_id = o1.customer_id
);
🎯 Practical Exercises with Solutions
Exercise 1: Library Database
sql
-- Create tables
CREATE TABLE books (
book_id INT PRIMARY KEY,
title VARCHAR(200),
author VARCHAR(100),
published_year INT,
isbn VARCHAR(20) UNIQUE
);
CREATE TABLE members (
member_id INT PRIMARY KEY,
name VARCHAR(100),
join_date DATE,
membership_type ENUM('regular', 'premium')
);
CREATE TABLE loans (
loan_id INT PRIMARY KEY,
book_id INT,
member_id INT,
loan_date DATE,
return_date DATE,
FOREIGN KEY (book_id) REFERENCES books(book_id),
FOREIGN KEY (member_id) REFERENCES members(member_id)
);
-- Q1: Find all books by a specific author
SELECT title, published_year
FROM books
WHERE author = 'J.K. Rowling'
ORDER BY published_year;
-- Q2: Count loans per member
SELECT
[Link],
COUNT(l.loan_id) AS books_borrowed
FROM members m
LEFT JOIN loans l ON m.member_id = l.member_id
GROUP BY m.member_id, [Link]
ORDER BY books_borrowed DESC;
-- Q3: Books currently on loan (not returned)
SELECT
[Link],
[Link],
[Link] AS borrower,
l.loan_date
FROM loans l
JOIN books b ON l.book_id = b.book_id
JOIN members m ON l.member_id = m.member_id
WHERE l.return_date IS NULL;
-- Q4: Most popular books
SELECT
[Link],
COUNT(l.loan_id) AS times_borrowed
FROM books b
LEFT JOIN loans l ON b.book_id = l.book_id
GROUP BY b.book_id, [Link]
ORDER BY times_borrowed DESC
LIMIT 5;
Exercise 2: Employee Management
sql
-- Tables
CREATE TABLE departments (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(100),
location VARCHAR(100)
);
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
name VARCHAR(100),
salary DECIMAL(10,2),
hire_date DATE,
dept_id INT,
manager_id INT,
FOREIGN KEY (dept_id) REFERENCES departments(dept_id),
FOREIGN KEY (manager_id) REFERENCES employees(emp_id)
);
-- Q1: Employees earning above department average
SELECT
[Link],
[Link],
d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE [Link] > (
SELECT AVG(salary)
FROM employees
WHERE dept_id = e.dept_id
);
-- Q2: Department salary statistics
SELECT
d.dept_name,
COUNT(e.emp_id) AS employee_count,
AVG([Link]) AS avg_salary,
MIN([Link]) AS min_salary,
MAX([Link]) AS max_salary,
SUM([Link]) AS total_payroll
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.dept_name;
-- Q3: Employees with managers
SELECT
[Link] AS employee,
[Link] AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;
🔧 Indexes and Performance
sql
-- Create index for faster searches
CREATE INDEX idx_student_email ON students(email);
CREATE INDEX idx_student_name ON students(last_name, first_name);
-- View indexes
SHOW INDEX FROM students;
-- Drop index
DROP INDEX idx_student_email ON students;
🎓 Exam Tips & Common Questions
ACID Properties
Atomicity: Transaction all-or-nothing
Consistency: Data integrity maintained
Isolation: Concurrent transactions don't interfere
Durability: Committed data persists
SQL Command Categories
DDL: CREATE, ALTER, DROP, TRUNCATE
DML: INSERT, UPDATE, DELETE
DQL: SELECT
DCL: GRANT, REVOKE
TCL: COMMIT, ROLLBACK, SAVEPOINT
Common Exam Questions
1. Difference between DELETE and TRUNCATE
o DELETE: DML, can have WHERE, slower, logs each row
o TRUNCATE: DDL, removes all rows, faster, cannot rollback
2. Primary Key vs Unique Key
o PK: One per table, cannot be NULL
o Unique: Multiple allowed, can have NULL
3. Normalization benefits
o Reduces redundancy
o Prevents anomalies
o Improves data integrity
sql
-- Quick Reference - Most Common SQL Patterns
-- ==========================================
-- SELECT with JOIN
SELECT [Link], [Link]
FROM table1 t1
JOIN table2 t2 ON [Link] = [Link];
-- Aggregation
SELECT category, COUNT(*)
FROM table
GROUP BY category;
-- Filtering groups
SELECT category, COUNT(*)
FROM table
GROUP BY category
HAVING COUNT(*) > 5;
-- Pagination
SELECT * FROM table
ORDER BY id
LIMIT 10 OFFSET 20;
-- Update with JOIN
UPDATE table1 t1
JOIN table2 t2 ON [Link] = [Link]
SET [Link] = [Link]