Database Management Systems

-- Create Database
CREATE DATABASE university;

-- Create Table
CREATE TABLE Student (
    student_id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    dob DATE,
    dept_id INT,
    gpa DECIMAL(3,2) CHECK (gpa >= 0.0 AND gpa <= 4.0),
    FOREIGN KEY (dept_id) REFERENCES Department(dept_id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

-- Create Index
CREATE INDEX idx_name ON Student(name);
CREATE UNIQUE INDEX idx_email ON Student(email);

-- Add Column
ALTER TABLE Student ADD phone VARCHAR(15);

-- Modify Column
ALTER TABLE Student MODIFY phone VARCHAR(20);

-- Drop Column
ALTER TABLE Student DROP COLUMN phone;

-- Add Constraint
ALTER TABLE Student ADD CONSTRAINT chk_gpa CHECK (gpa <= 4.0);

-- Rename Table
ALTER TABLE Student RENAME TO Students;

-- DROP: Remove entire object (structure + data)
DROP TABLE Student;           -- Delete table completely
DROP TABLE IF EXISTS Student; -- Safe drop
DROP INDEX idx_name;          -- Remove index

-- TRUNCATE: Remove all data, keep structure
TRUNCATE TABLE Student;
-- Faster than DELETE (no logging)
-- Cannot use WHERE clause
-- Resets auto-increment

-- Single Row Insert
INSERT INTO Student (student_id, name, email, dept_id) 
VALUES (1, 'Alice', 'alice@mail.com', 101);

-- Multiple Rows
INSERT INTO Student (student_id, name, email, dept_id) VALUES
    (2, 'Bob', 'bob@mail.com', 102),
    (3, 'Charlie', 'charlie@mail.com', 101),
    (4, 'Diana', 'diana@mail.com', 103);

-- Insert from SELECT
INSERT INTO Archive_Student 
SELECT * FROM Student WHERE grad_year < 2020;

-- Basic SELECT
SELECT * FROM Student;                    -- All columns
SELECT name, email FROM Student;          -- Specific columns
SELECT DISTINCT dept_id FROM Student;     -- Unique values

-- WHERE Clause (Filtering)
SELECT * FROM Student WHERE gpa > 3.5;
SELECT * FROM Student WHERE dept_id IN (101, 102);
SELECT * FROM Student WHERE name LIKE 'A%';      -- Starts with A
SELECT * FROM Student WHERE name LIKE '%son';    -- Ends with son
SELECT * FROM Student WHERE name LIKE '_a%';     -- 2nd char is 'a'
SELECT * FROM Student WHERE email IS NOT NULL;

-- Logical Operators
SELECT * FROM Student 
WHERE gpa > 3.0 AND dept_id = 101;

SELECT * FROM Student 
WHERE gpa > 3.5 OR dept_id = 102;

SELECT * FROM Student 
WHERE NOT dept_id = 103;

-- ORDER BY (Sorting)
SELECT * FROM Student ORDER BY name ASC;     -- Ascending
SELECT * FROM Student ORDER BY gpa DESC;     -- Descending
SELECT * FROM Student ORDER BY dept_id, gpa DESC;

-- LIMIT / TOP
SELECT * FROM Student LIMIT 5;              -- MySQL
SELECT TOP 5 * FROM Student;                -- SQL Server

-- UPDATE
UPDATE Student SET gpa = 3.8 WHERE student_id = 1;
UPDATE Student SET dept_id = 102, gpa = gpa + 0.1 
WHERE name = 'Alice';

-- DELETE
DELETE FROM Student WHERE student_id = 4;
DELETE FROM Student WHERE gpa < 2.0;
DELETE FROM Student;  -- Delete all rows (use TRUNCATE instead)

-- Aggregate Functions
SELECT COUNT(*) FROM Student;                -- Total rows
SELECT COUNT(DISTINCT dept_id) FROM Student; -- Unique departments
SELECT SUM(gpa) FROM Student;
SELECT AVG(gpa) FROM Student;
SELECT MAX(gpa), MIN(gpa) FROM Student;

-- GROUP BY - Aggregate per group
SELECT dept_id, COUNT(*) as student_count, AVG(gpa) as avg_gpa
FROM Student
GROUP BY dept_id;

-- Output:
-- dept_id | student_count | avg_gpa
-- 101     | 2             | 3.65
-- 102     | 1             | 3.80
-- 103     | 1             | 3.20

-- HAVING - Filter groups (WHERE for groups)
SELECT dept_id, AVG(gpa) as avg_gpa
FROM Student
GROUP BY dept_id
HAVING AVG(gpa) > 3.5;

-- Complete Query Order
SELECT dept_id, COUNT(*) as cnt
FROM Student
WHERE gpa > 2.0              -- 1. Filter rows
GROUP BY dept_id             -- 2. Group remaining
HAVING COUNT(*) > 1          -- 3. Filter groups
ORDER BY cnt DESC            -- 4. Sort
LIMIT 5;                     -- 5. Limit output

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

-- Sample Tables
-- Student: student_id, name, dept_id
-- Department: dept_id, dept_name

-- INNER JOIN (Matching rows only)
SELECT s.name, d.dept_name
FROM Student s
INNER JOIN Department d ON s.dept_id = d.dept_id;

-- LEFT JOIN (All left + matching right)
SELECT s.name, d.dept_name
FROM Student s
LEFT JOIN Department d ON s.dept_id = d.dept_id;
-- Students without department get NULL for dept_name

-- RIGHT JOIN (All right + matching left)
SELECT s.name, d.dept_name
FROM Student s
RIGHT JOIN Department d ON s.dept_id = d.dept_id;
-- Departments without students get NULL for name

-- FULL OUTER JOIN (All rows from both)
SELECT s.name, d.dept_name
FROM Student s
FULL OUTER JOIN Department d ON s.dept_id = d.dept_id;

-- CROSS JOIN (Cartesian Product)
SELECT s.name, d.dept_name
FROM Student s
CROSS JOIN Department d;
-- Returns m × n rows

-- SELF JOIN (Table with itself)
SELECT e1.name as Employee, e2.name as Manager
FROM Employee e1
JOIN Employee e2 ON e1.manager_id = e2.emp_id;

INNER JOIN:      A ∩ B       (intersection only)
LEFT JOIN:       A + (A ∩ B) (all of A + matching B)
RIGHT JOIN:      B + (A ∩ B) (all of B + matching A)
FULL OUTER:      A ∪ B       (everything)
CROSS JOIN:      A × B       (cartesian product)

-- Subquery in WHERE
SELECT name FROM Student
WHERE dept_id = (SELECT dept_id FROM Department WHERE dept_name = 'CS');

-- Subquery with IN
SELECT name FROM Student
WHERE dept_id IN (SELECT dept_id FROM Department WHERE location = 'Building A');

-- Subquery with EXISTS
SELECT name FROM Student s
WHERE EXISTS (
    SELECT 1 FROM Enrollment e WHERE e.student_id = s.student_id
);

-- Subquery with ANY/ALL
SELECT name FROM Student WHERE gpa > ALL (
    SELECT gpa FROM Student WHERE dept_id = 102
);

-- Subquery in FROM (Derived Table)
SELECT dept_name, avg_gpa
FROM (
    SELECT dept_id, AVG(gpa) as avg_gpa
    FROM Student
    GROUP BY dept_id
) AS dept_avg
JOIN Department d ON dept_avg.dept_id = d.dept_id
WHERE avg_gpa > 3.0;

-- Correlated Subquery (references outer query)
SELECT s.name, s.gpa
FROM Student s
WHERE s.gpa > (
    SELECT AVG(s2.gpa) 
    FROM Student s2 
    WHERE s2.dept_id = s.dept_id  -- References outer s
);

-- GRANT: Give permissions
GRANT SELECT, INSERT ON Student TO user1;
GRANT ALL PRIVILEGES ON university.* TO admin;

-- REVOKE: Remove permissions
REVOKE INSERT ON Student FROM user1;
REVOKE ALL PRIVILEGES ON university.* FROM user1;

-- Start Transaction
START TRANSACTION;  -- or BEGIN

-- Make changes
UPDATE Account SET balance = balance - 500 WHERE id = 1;
UPDATE Account SET balance = balance + 500 WHERE id = 2;

-- Commit if successful
COMMIT;

-- Or Rollback on error
ROLLBACK;

-- Savepoint for partial rollback
SAVEPOINT sp1;
UPDATE ...
ROLLBACK TO sp1;  -- Undo to savepoint

σ_gpa>3.5(Student)
→ SELECT * FROM Student WHERE gpa > 3.5

π_name,email(Student)
→ SELECT name, email FROM Student

σ_gpa>3.5(π_name,gpa(Student))
→ SELECT name, gpa FROM Student WHERE gpa > 3.5

Student × Course
→ SELECT * FROM Student CROSS JOIN Course

Student ⋈_dept_id=dept_id Department
→ SELECT * FROM Student JOIN Department ON Student.dept_id = Department.dept_id

π_name(Student) ∪ π_name(Faculty)
→ SELECT name FROM Student UNION SELECT name FROM Faculty

Student − Graduate_Student
→ SELECT * FROM Student EXCEPT SELECT * FROM Graduate_Student

Natural Join (⋈):
R ⋈ S = Automatically joins on common attributes

Theta Join (⋈_θ):
R ⋈_θ S = Join with arbitrary condition θ

Equi-Join:
Theta join with only equality conditions

Semi-Join (⋉):
R ⋉ S = Tuples in R that have matching tuples in S