Key Characteristics

• Non-procedural — specify WHAT, not HOW
• Set-oriented — operates on entire sets of rows at once
• Standardized — portable across MySQL, Oracle, PostgreSQL, SQL Server
• Comprehensive — DDL + DML + DCL + TCL in one language
• Human-readable — English-like keywords

SQL Command Categories

CREATE TABLE with Constraints

CREATE TABLE STUDENT (
    Student_ID  INT          PRIMARY KEY,
    Name        VARCHAR(50) NOT NULL,
    Age         INT          CHECK (Age BETWEEN 15 AND 35),
    Gender      CHAR(1)      CHECK (Gender IN ('M','F','O')),
    Email       VARCHAR(100) UNIQUE,
    Dept_ID     INT,
    FOREIGN KEY (Dept_ID) REFERENCES DEPARTMENT(Dept_ID)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

Constraints Summary

Foreign Key Actions

ALTER TABLE

ALTER TABLE STUDENT ADD COLUMN Phone VARCHAR(15);
ALTER TABLE STUDENT MODIFY COLUMN Name VARCHAR(100);
ALTER TABLE STUDENT DROP COLUMN Phone;
ALTER TABLE STUDENT ADD CONSTRAINT chk CHECK (Age >= 15);
ALTER TABLE STUDENT DROP CONSTRAINT chk;

DROP vs TRUNCATE vs DELETE

CREATE INDEX

CREATE INDEX idx_name ON STUDENT (Name);
CREATE UNIQUE INDEX idx_email ON STUDENT (Email);
DROP INDEX idx_name ON STUDENT;

SELECT Statement Structure

SELECT [DISTINCT] columns
FROM   table(s)
[WHERE  condition]
[GROUP BY column]
[HAVING group_condition]
[ORDER BY column [ASC|DESC]];

WHERE Operators

JOIN Types

JOIN Syntax Examples

-- INNER JOIN
SELECT s.Name, d.Dept_Name
FROM STUDENT s INNER JOIN DEPARTMENT d ON s.Dept_ID = d.Dept_ID;

-- LEFT JOIN (all students, even without dept)
SELECT s.Name, d.Dept_Name
FROM STUDENT s LEFT JOIN DEPARTMENT d ON s.Dept_ID = d.Dept_ID;

-- SELF JOIN (employee and their manager)
SELECT e1.Name AS Employee, e2.Name AS Manager
FROM EMPLOYEE e1 JOIN EMPLOYEE e2 ON e1.Manager_ID = e2.Employee_ID;

-- CROSS JOIN
SELECT s.Name, c.Course_Name FROM STUDENT s CROSS JOIN COURSE c;

INSERT / UPDATE / DELETE

-- INSERT
INSERT INTO STUDENT VALUES (104, 'Meena', 20, 'F', '[email protected]', 'CS');
INSERT INTO STUDENT (Student_ID, Name) VALUES (105, 'Arjun');
INSERT INTO STUDENT SELECT * FROM NEW_ADMISSIONS WHERE Year = 2024;

-- UPDATE
UPDATE STUDENT SET Age = 21 WHERE Student_ID = 101;

-- DELETE
DELETE FROM STUDENT WHERE Student_ID = 105;

Aggregate Functions

GROUP BY + HAVING

-- Count students per dept (only depts with > 50 students)
SELECT Dept_ID, COUNT(*) AS Total
FROM STUDENT
WHERE Age > 17              -- filter rows first
GROUP BY Dept_ID
HAVING COUNT(*) > 50       -- filter groups after
ORDER BY Total DESC;

Nested Queries (Subqueries)

-- Non-correlated: find CS students
SELECT Name FROM STUDENT
WHERE Dept_ID = (SELECT Dept_ID FROM DEPARTMENT WHERE Dept_Name = 'CS');

-- Correlated: students above their dept avg
SELECT s.Name, m.Marks FROM STUDENT s JOIN MARKS m ON s.Student_ID = m.Student_ID
WHERE m.Marks > (
    SELECT AVG(m2.Marks) FROM MARKS m2 JOIN STUDENT s2 ON m2.Student_ID = s2.Student_ID
    WHERE s2.Dept_ID = s.Dept_ID  -- references outer query
);

Subquery Operators

-- EXISTS: students enrolled in at least one course
SELECT Name FROM STUDENT s
WHERE EXISTS (SELECT 1 FROM ENROLLMENT e WHERE e.Student_ID = s.Student_ID);

-- ALL: students scoring more than every CS student
SELECT Name FROM MARKS
WHERE Marks > ALL (SELECT Marks FROM MARKS WHERE Dept_ID = 'CS');

Views

-- Create view
CREATE VIEW CS_Students AS
SELECT Student_ID, Name, Age FROM STUDENT WHERE Dept_ID = 'CS';

-- Use like a table
SELECT * FROM CS_Students WHERE Age > 20;

-- Drop view
DROP VIEW CS_Students;

Assertions

-- Max 60 students per course
CREATE ASSERTION max_enrollment CHECK (
    NOT EXISTS (
        SELECT Course_ID FROM ENROLLMENT
        GROUP BY Course_ID HAVING COUNT(*) > 60
    )
);

Triggers

-- Trigger: auto-deduct stock on order
CREATE TRIGGER update_stock
AFTER INSERT ON ORDER_ITEM FOR EACH ROW
BEGIN
    UPDATE PRODUCT
    SET Stock = Stock - NEW.Quantity
    WHERE Product_ID = NEW.Product_ID;
END;

-- Trigger: audit salary changes
CREATE TRIGGER log_salary
AFTER UPDATE OF Salary ON EMPLOYEE FOR EACH ROW
BEGIN
    INSERT INTO SALARY_LOG
    VALUES (OLD.Employee_ID, OLD.Salary, NEW.Salary, NOW());
END;

Why NoSQL?

• Data is unstructured / semi-structured (JSON, XML, images)
• Schema changes frequently — rigid tables don't work
• High velocity — millions of records per second
• Need to scale horizontally (add servers, not bigger servers)
• RDBMS can't handle this cost-effectively at web scale

NoSQL Database Types

ACID vs BASE

CAP Theorem (Brewer's Theorem)

• In-memory — all data in RAM → sub-millisecond latency
• Persistence — RDB snapshots + AOF (append-only file) log
• Atomic operations — all commands atomic
• TTL — keys expire automatically (great for caching/sessions)
• Pub/Sub — publish/subscribe messaging

-- String: store and retrieve
SET username:101 'Arun Kumar'
GET username:101          -- Returns: 'Arun Kumar'
EXPIRE username:101 3600  -- Expires in 1 hour

-- Hash: store object
HSET user:101 name 'Arun' age 20 dept 'CS'
HGETALL user:101

-- Sorted Set: leaderboard
ZADD leaderboard 1500 'Arun'
ZRANGE leaderboard 0 9 WITHSCORES  -- Top 10

• Schema-less — documents in same collection can have different fields
• Nested documents — embed related data (no joins needed)
• Horizontal scalability — built-in sharding
• Replica sets — automatic failover for high availability
• Rich query language — filter, sort, project, aggregate

// Document example
{
    _id: ObjectId('64a1b2c3...'),
    student_id: 101,
    name: 'Arun Kumar',
    courses: ['DBMS', 'OS', 'CN'],    // array field
    address: { city: 'Bangalore', pin: '560001' }  // nested doc
}

// INSERT
db.students.insertOne({ student_id: 101, name: 'Arun', dept: 'CS' });

// FIND (SELECT)
db.students.find({ dept: 'CS' });
db.students.find({ age: { $gt: 20 } }, { name: 1, dept: 1, _id: 0 });

// UPDATE
db.students.updateOne({ student_id: 101 }, { $set: { age: 21 } });

// DELETE
db.students.deleteOne({ student_id: 101 });

Redis vs MongoDB vs SQL

SQL Command Quick Reference

All Facts in One Table