SQL Projects

A SQL project is a practical application where a database is designed and SQL is used to store, retrieve, update, and analyze data.

CREATE DATABASE school_db;

Projects help you understand how SQL concepts work together instead of using each command separately.

A typical SQL project can be developed using these steps:

1. Understand the requirements.
2. Identify entities and tables.
3. Define columns and data types.
4. Create primary and foreign keys.
5. Insert sample data.
6. Write required queries.
7. Create reports and views.
8. Optimize important queries.
9. Test the database.

A student management system stores information about students, courses, admissions, and fees.

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    email VARCHAR(150),
    city VARCHAR(100),
    course_id INT
);

This can be expanded with courses, attendance, payments, and other tables.

Create a courses table for the student management system.

CREATE TABLE courses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    course_name VARCHAR(100),
    fee DECIMAL(10,2)
);

The course_id in the students table can reference this table.

Insert sample course and student records.

INSERT INTO courses
(course_name, fee)
VALUES
('Python', 15000),
('Java', 18000),
('Web Development', 12000);

INSERT INTO students
(name, email, city, course_id)
VALUES
('Rahul', 'rahul@example.com', 'Patna', 1),
('Amit', 'amit@example.com', 'Delhi', 2),
('Priya', 'priya@example.com', 'Patna', 3);

Use JOIN to display students with their courses.

SELECT
    s.name,
    s.city,
    c.course_name,
    c.fee
FROM students s
INNER JOIN courses c
ON s.course_id = c.id;

This is a common real-world reporting query.

Use GROUP BY and COUNT to find the number of students in each course.

SELECT
    c.course_name,
    COUNT(s.id) AS total_students
FROM courses c
LEFT JOIN students s
ON c.id = s.course_id
GROUP BY c.id, c.course_name;

Suppose the students table also contains marks.

SELECT
    AVG(marks) AS average_marks
FROM students;

You can use AVG() to calculate the average marks of students.

A library management system can contain books, members, and issue records.

CREATE TABLE books (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200),
    author VARCHAR(150),
    quantity INT
);

Additional tables can store members and book transactions.

CREATE TABLE members (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    mobile VARCHAR(20),
    city VARCHAR(100)
);

This table stores basic library member information.

An issue table can connect books with members.

CREATE TABLE book_issues (
    id INT PRIMARY KEY AUTO_INCREMENT,
    book_id INT,
    member_id INT,
    issue_date DATE,
    return_date DATE
);

Foreign keys can be added to maintain relationships between the tables.

Use JOIN to display issued book information.

SELECT
    b.title,
    m.name AS member_name,
    i.issue_date,
    i.return_date
FROM book_issues i
INNER JOIN books b
ON i.book_id = b.id
INNER JOIN members m
ON i.member_id = m.id;

Use WHERE to find books with available quantity.

SELECT title, author, quantity
FROM books
WHERE quantity > 0
ORDER BY title;

Use LIKE to search books by title or author.

SELECT *
FROM books
WHERE title LIKE '%SQL%'
OR author LIKE '%Kumar%';

This allows flexible text searching.

An employee management system stores employee, department, salary, and joining information.

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    department_id INT,
    salary DECIMAL(10,2),
    joining_date DATE
);

CREATE TABLE departments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    department_name VARCHAR(100)
);

Employees can be connected to departments using department_id.

Use aggregate functions to calculate salary statistics.

SELECT
    COUNT(*) AS total_employees,
    AVG(salary) AS average_salary,
    MAX(salary) AS highest_salary,
    MIN(salary) AS lowest_salary
FROM employees;

Use GROUP BY to calculate the average salary by department.

SELECT
    department_id,
    COUNT(*) AS total_employees,
    AVG(salary) AS average_salary
FROM employees
GROUP BY department_id;

A sales system can contain customers, products, orders, and payments.

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(150),
    price DECIMAL(10,2),
    stock INT
);

Additional tables can store customers and sales transactions.

Use SUM() to calculate total sales.

SELECT
    SUM(amount) AS total_sales
FROM payments;

This can be extended to calculate daily, monthly, or yearly sales.

Date functions and GROUP BY can be used for monthly reports.

SELECT
    YEAR(payment_date) AS payment_year,
    MONTH(payment_date) AS payment_month,
    SUM(amount) AS total_amount
FROM payments
GROUP BY
    YEAR(payment_date),
    MONTH(payment_date)
ORDER BY
    payment_year,
    payment_month;

A course fee management system can track total fees, paid fees, and outstanding fees.

SELECT
    name,
    total_fee,
    paid_fee,
    total_fee - paid_fee AS due_fee
FROM students;

This query creates a basic fee report.

Use a WHERE condition to find students with outstanding fees.

SELECT
    name,
    total_fee,
    paid_fee,
    total_fee - paid_fee AS due_fee
FROM students
WHERE total_fee - paid_fee > 0
ORDER BY due_fee DESC;

A view can simplify frequently used fee reports.

CREATE VIEW fee_report AS
SELECT
    id,
    name,
    total_fee,
    paid_fee,
    total_fee - paid_fee AS due_fee
FROM students;

SELECT *
FROM fee_report
WHERE due_fee > 0;

An attendance system can contain students and daily attendance records.

CREATE TABLE attendance (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT,
    attendance_date DATE,
    status VARCHAR(20)
);

Status values might include Present or Absent according to the application's rules.

Use GROUP BY and HAVING to identify students with multiple absences.

SELECT
    student_id,
    COUNT(*) AS absent_days
FROM attendance
WHERE status = 'Absent'
GROUP BY student_id
HAVING COUNT(*) >= 3;

This identifies students having at least three absence records.

An e-commerce database can contain:

• Customers
• Products
• Categories
• Orders
• Order items
• Payments
• Shipping records

These tables can be connected using primary and foreign keys.

Indexes can improve frequently executed queries in large projects.

CREATE INDEX idx_student_email
ON students(email);

CREATE INDEX idx_payment_date
ON payments(payment_date);

Indexes should be created based on actual query requirements and workload.

Advanced projects can combine transactions and stored procedures.

START TRANSACTION;

INSERT INTO payments
(student_id, amount)
VALUES
(101, 2000);

UPDATE students
SET paid_fee = paid_fee + 2000
WHERE id = 101;

COMMIT;

A stored procedure can package similar operations into a reusable database operation.

A complete SQL project can combine the concepts learned throughout this tutorial.

CREATE DATABASE training_db;

USE training_db;

CREATE TABLE courses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    course_name VARCHAR(100),
    fee DECIMAL(10,2)
);

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    email VARCHAR(150) UNIQUE,
    course_id INT,
    total_fee DECIMAL(10,2),
    paid_fee DECIMAL(10,2) DEFAULT 0,
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

Insert data:

INSERT INTO courses
(course_name, fee)
VALUES
('Python Full Stack', 15000),
('Java Full Stack', 18000);

INSERT INTO students
(name, email, course_id, total_fee, paid_fee)
VALUES
('Rahul', 'rahul@example.com', 1, 15000, 5000),
('Priya', 'priya@example.com', 2, 18000, 10000);

Create a useful report:

SELECT
    s.name,
    s.email,
    c.course_name,
    s.total_fee,
    s.paid_fee,
    s.total_fee - s.paid_fee AS due_fee
FROM students s
INNER JOIN courses c
ON s.course_id = c.id
ORDER BY due_fee DESC;

This project demonstrates database creation, tables, constraints, INSERT, SELECT, JOIN, calculations, DEFAULT, UNIQUE, FOREIGN KEY, and ORDER BY.