Master DBMS
From Zero to Viva

-- Create a table
CREATE TABLE Students (
    Student_ID  INT          PRIMARY KEY,
    Name        VARCHAR(100) NOT NULL,
    Branch      VARCHAR(50)  DEFAULT 'CSE',
    Marks       DECIMAL(5,2) CHECK (Marks >= 0 AND Marks <= 100),
    Email       VARCHAR(100) UNIQUE
);

-- Add a new column
ALTER TABLE Students ADD Phone VARCHAR(15);

-- Modify column type
ALTER TABLE Students MODIFY Marks FLOAT;

-- Drop a column
ALTER TABLE Students DROP COLUMN Phone;

-- Delete all data but keep structure
TRUNCATE TABLE Students;

-- Delete table completely
DROP TABLE Students;

-- INSERT
INSERT INTO Students VALUES (101, 'Rahul', 'CSE', 89.5, 'rahul@email.com');
INSERT INTO Students (Student_ID, Name) VALUES (102, 'Priya');

-- UPDATE
UPDATE Students SET Marks = 92 WHERE Student_ID = 101;
UPDATE Students SET Branch = 'IT' WHERE Branch = 'CSE' AND Marks < 60;

-- DELETE
DELETE FROM Students WHERE Student_ID = 102;
DELETE FROM Students WHERE Marks < 40;

-- SELECT with conditions
SELECT Name, Marks FROM Students WHERE Branch = 'CSE';
SELECT DISTINCT Branch FROM Students;
SELECT * FROM Students WHERE Marks BETWEEN 60 AND 90;
SELECT * FROM Students WHERE Name LIKE 'R%';
SELECT * FROM Students WHERE Branch IN ('CSE', 'IT', 'ECE');

-- GROUP BY: groups rows that have same values
SELECT Branch, COUNT(*) AS Student_Count, AVG(Marks) AS Avg_Marks
FROM Students
GROUP BY Branch;

-- Output:
-- Branch | Student_Count | Avg_Marks
-- CSE    | 3             | 87.00
-- IT     | 1             | 85.00

-- HAVING: filter AFTER grouping (like WHERE but for groups)
SELECT Branch, AVG(Marks) AS Avg_Marks
FROM Students
GROUP BY Branch
HAVING AVG(Marks) > 80;  -- only branches with avg > 80

-- ORDER BY: sort results
SELECT Name, Marks FROM Students ORDER BY Marks DESC;
SELECT Name, Marks FROM Students ORDER BY Marks ASC LIMIT 3;

SELECT COUNT(*) FROM Students;          -- 4 (total rows)
SELECT COUNT(Email) FROM Students;      -- counts non-null emails
SELECT SUM(Marks) FROM Students;        -- sum of all marks
SELECT AVG(Marks) FROM Students;        -- average
SELECT MAX(Marks) FROM Students;        -- highest
SELECT MIN(Marks) FROM Students;        -- lowest

CREATE TABLE Department (
    Dept_ID   INT          PRIMARY KEY,
    Dept_Name VARCHAR(100) NOT NULL
);

CREATE TABLE Employee (
    Emp_ID   INT           PRIMARY KEY,
    Emp_Name VARCHAR(100)  NOT NULL,
    Dept_ID  INT,
    Salary   DECIMAL(10,2) CHECK (Salary > 0),
    FOREIGN KEY (Dept_ID) REFERENCES Department(Dept_ID)
        ON DELETE CASCADE    -- if dept deleted, employees also deleted
        ON UPDATE CASCADE    -- if dept_id updated, update here too
);

-- Tables: Students(SID, Name, CID) | Courses(CID, Course_Name)
-- Data: Rahul→101(DBMS), Priya→102(OS), Amit→NULL | 103(CN) has no student

-- INNER JOIN: only matching rows from BOTH tables
SELECT s.Name, c.Course_Name
FROM Students s INNER JOIN Courses c ON s.CID = c.CID;
-- Result: Rahul-DBMS, Priya-OS  (Amit and CN excluded)

-- LEFT JOIN: all from left table, matching from right (NULL if no match)
SELECT s.Name, c.Course_Name
FROM Students s LEFT JOIN Courses c ON s.CID = c.CID;
-- Result: Rahul-DBMS, Priya-OS, Amit-NULL

-- RIGHT JOIN: all from right table, matching from left
SELECT s.Name, c.Course_Name
FROM Students s RIGHT JOIN Courses c ON s.CID = c.CID;
-- Result: Rahul-DBMS, Priya-OS, NULL-CN

-- FULL OUTER JOIN: all rows from both tables
-- (MySQL doesn't support directly, use UNION of LEFT + RIGHT)
SELECT s.Name, c.Course_Name FROM Students s
LEFT JOIN Courses c ON s.CID = c.CID
UNION
SELECT s.Name, c.Course_Name FROM Students s
RIGHT JOIN Courses c ON s.CID = c.CID;

-- CROSS JOIN: every combination (Cartesian product)
SELECT s.Name, c.Course_Name FROM Students s CROSS JOIN Courses c;
-- 3 students × 3 courses = 9 rows

-- SELF JOIN: table joined with itself
SELECT A.Name AS Student, B.Name AS Mentor
FROM Students A JOIN Students B ON A.Mentor_ID = B.SID;

-- Find students with marks above average
SELECT Name, Marks FROM Students
WHERE Marks > (SELECT AVG(Marks) FROM Students);

-- Find students enrolled in 'DBMS' course
SELECT Name FROM Students
WHERE CID IN (SELECT CID FROM Courses WHERE Course_Name = 'DBMS');

-- Correlated Subquery: inner query references outer query
SELECT Name, Marks FROM Students s1
WHERE Marks > (
    SELECT AVG(Marks) FROM Students s2
    WHERE s2.Branch = s1.Branch  -- references outer query's Branch
);
-- This finds students above the avg of their OWN branch

-- Create a view showing only CSE students
CREATE VIEW CSE_Students AS
SELECT Student_ID, Name, Marks
FROM Students WHERE Branch = 'CSE';

-- Query the view like a table
SELECT * FROM CSE_Students;

-- Drop a view
DROP VIEW CSE_Students;

-- Create index on Name column
CREATE INDEX idx_name ON Students(Name);

-- Unique index (also enforces uniqueness)
CREATE UNIQUE INDEX idx_email ON Students(Email);

-- Drop index
DROP INDEX idx_name ON Students;

-- Trigger: log every INSERT into Students
CREATE TRIGGER after_student_insert
AFTER INSERT ON Students
FOR EACH ROW
BEGIN
    INSERT INTO Audit_Log(Action, Student_ID, Log_Time)
    VALUES ('INSERT', NEW.Student_ID, NOW());
END;

DELIMITER //
CREATE PROCEDURE GetStudentsByBranch(IN branch_name VARCHAR(50))
BEGIN
    SELECT * FROM Students WHERE Branch = branch_name;
END //
DELIMITER ;

-- Call the procedure
CALL GetStudentsByBranch('CSE');

-- Grant permissions to a user
GRANT SELECT, INSERT ON Students TO 'teacher_user'@'localhost';

-- Grant ALL privileges
GRANT ALL PRIVILEGES ON university.* TO 'admin'@'localhost';

-- Revoke permissions
REVOKE INSERT ON Students FROM 'teacher_user'@'localhost';

-- Grant with option to re-grant
GRANT SELECT ON Students TO 'dept_head' WITH GRANT OPTION;