Introduction to Databases and SQL: Concepts, RDBMS & SQL Commands

Study Deep: The ACID Properties

In any database transaction, four properties (ACID) must be maintained to ensure data reliability.

1. A - Atomicity: "All or nothing." Either the entire transaction succeeds, or it is completely rolled back.
2. C - Consistency: A transaction must transform the database from one valid state to another valid state.
3. I - Isolation: Concurrent transactions should not interfere with each other.
4. D - Durability: Once a transaction is committed, it remains committed even in the case of a system failure.

1. Core Database Terminology

Understanding databases begins with a clear vocabulary:

2. What is MySQL?

MySQL is the most popular open-source Relational Database Management System (RDBMS). It organizes data into tables and uses SQL (Structured Query Language) to interact with the data.

Why MySQL with PHP?

• MySQL is free and open-source.
• Excellent PHP support via MySQLi and PDO extensions.
• Used by millions of websites (WordPress, Facebook originally).
• Fast, reliable, and easy to set up with XAMPP/WAMP.

3. RDBMS vs NoSQL Comparison

4. SQL Command Categories

SQL (Structured Query Language) is the standard language for interacting with relational databases. All SQL commands fall into one of four categories:

a) DDL — Data Definition Language Commands that define or modify the structure of the database (tables, databases, indexes).

b) DML — Data Manipulation Language Commands that manipulate the data stored in tables.

c) DCL — Data Control Language Commands that control user access and permissions.

d) TCL — Transaction Control Language Commands that manage database transactions (groups of operations that must all succeed or all fail).

5. Important SQL Concepts

The WHERE Clause: Filters records to operate on specific rows only. Always use with UPDATE and DELETE to prevent modifying or deleting ALL records accidentally.

-- Update ONLY student with id=1
UPDATE students SET marks=90 WHERE id=1;

-- Delete ONLY students with course='BCA'
DELETE FROM students WHERE course='BCA';

-- Without WHERE: ALL records would be affected!

ORDER BY, LIMIT:

SELECT * FROM students ORDER BY name ASC;   -- Sort A-Z
SELECT * FROM students ORDER BY marks DESC; -- Sort highest first
SELECT * FROM students LIMIT 10;            -- Only first 10 records

6. Aggregate Functions

Definition: Aggregate functions perform a calculation on a set of rows and return a single summarized value. They are used with the SELECT statement, typically alongside GROUP BY.

-- Get total number of students
SELECT COUNT(*) AS total_students FROM students;

-- Get average, max, and min marks
SELECT AVG(marks) AS avg_marks,
       MAX(marks) AS highest,
       MIN(marks) AS lowest
FROM students;

-- Get total marks scored by all students in BCA
SELECT SUM(marks) AS bca_total FROM students WHERE course = 'BCA';

7. GROUP BY and HAVING

GROUP BY: Groups rows that share the same value in a specified column, so aggregate functions can be applied per group (e.g., per course, per city).

HAVING: Filters groups after aggregation (similar to WHERE, but WHERE filters individual rows before grouping).

Rule: Use WHERE to filter rows. Use HAVING to filter groups.

-- Count students in each course
SELECT course, COUNT(*) AS total
FROM students
GROUP BY course;

-- Average marks per course, sorted highest first
SELECT course,
       AVG(marks)  AS avg_marks,
       COUNT(*)    AS student_count
FROM students
GROUP BY course
ORDER BY avg_marks DESC;

-- Only show courses where average marks > 70 (HAVING filters groups)
SELECT course, AVG(marks) AS avg_marks
FROM students
GROUP BY course
HAVING AVG(marks) > 70;

-- WHERE + GROUP BY + HAVING together
SELECT course, COUNT(*) AS count
FROM students
WHERE is_active = 1          -- Filter rows first
GROUP BY course              -- Then group
HAVING COUNT(*) >= 5         -- Then filter groups
ORDER BY count DESC;

Execution Order of a SELECT statement:

1. FROM      → Identify source table
2. WHERE     → Filter individual rows
3. GROUP BY  → Group filtered rows
4. HAVING    → Filter groups
5. SELECT    → Choose columns / apply aggregates
6. ORDER BY  → Sort results
7. LIMIT     → Restrict number of output rows

8. SQL JOINs — Combining Multiple Tables

Definition: A JOIN combines rows from two or more tables based on a related column (usually a Primary Key – Foreign Key relationship). JOINs are fundamental to relational databases.

Setup for examples:

-- Table: departments
CREATE TABLE departments (
  dept_id   INT PRIMARY KEY,
  dept_name VARCHAR(50)
);
INSERT INTO departments VALUES (1,'BCA'), (2,'BBA'), (3,'MBA');

-- Table: students (has a foreign key to departments)
CREATE TABLE students (
  id      INT PRIMARY KEY,
  name    VARCHAR(50),
  dept_id INT  -- Foreign Key
);
INSERT INTO students VALUES
  (1,'Rohit',1), (2,'Priya',1), (3,'Amit',2), (4,'Neha',NULL);

a) INNER JOIN — Returns rows where there is a match in BOTH tables.

SELECT s.name, d.dept_name
FROM students s
INNER JOIN departments d ON s.dept_id = d.dept_id;
-- Result: Rohit|BCA, Priya|BCA, Amit|BBA
-- Neha is excluded (NULL dept_id — no match)

b) LEFT JOIN (LEFT OUTER JOIN) — Returns ALL rows from the left table + matched rows from the right. Unmatched right-side values are NULL.

SELECT s.name, d.dept_name
FROM students s
LEFT JOIN departments d ON s.dept_id = d.dept_id;
-- Result: Rohit|BCA, Priya|BCA, Amit|BBA, Neha|NULL
-- Neha IS included (left table = students, all rows returned)

c) RIGHT JOIN (RIGHT OUTER JOIN) — Returns ALL rows from the right table + matched rows from the left. Unmatched left-side values are NULL.

SELECT s.name, d.dept_name
FROM students s
RIGHT JOIN departments d ON s.dept_id = d.dept_id;
-- Result: Rohit|BCA, Priya|BCA, Amit|BBA, NULL|MBA
-- MBA department IS included even though no student belongs to it

Visual Summary:

-- Real example: Show each student with their course name and average marks
SELECT d.dept_name,
       COUNT(s.id)  AS student_count,
       AVG(s.marks) AS avg_marks
FROM departments d
LEFT JOIN students s ON d.dept_id = s.dept_id
GROUP BY d.dept_name
ORDER BY avg_marks DESC;