SELECT * FROM students;

ALTER TABLE students ADD phone VARCHAR(15);          -- column যোগ
ALTER TABLE students MODIFY name VARCHAR(150) NOT NULL; -- type বদল
ALTER TABLE students RENAME COLUMN phone TO mobile;   -- নাম বদল
ALTER TABLE students DROP COLUMN mobile;              -- column মুছে ফেলা

TRUNCATE TABLE enrollments;          -- সব data মুছবে, table থাকবে
DROP TABLE IF EXISTS enrollments;    -- পুরো table উধাও

-- একটা row, column নাম সহ (best practice)
INSERT INTO students (id, name, email, dept_id, cgpa, city)
VALUES (11, 'Rafiq Islam', 'rafiq@uni.edu', 1, 3.40, 'Cumilla');

-- একসাথে অনেক row (দ্রুত)
INSERT INTO courses (id, code, title, credit, dept_id) VALUES
  (7, 'CSE310', 'Web Engineering', 3, 1),
  (8, 'BBA210', 'Marketing', 3, 3);

SELECT * FROM courses;

SELECT name AS student_name,
       cgpa,
       cgpa * 25 AS percentage,
       'BD'      AS country
FROM students;

SELECT id, name, price FROM products
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;      -- page 3 = (3 - 1) × 20

SELECT s.name AS student, d.name AS dept, c.title AS course, e.semester, e.marks
FROM enrollments e
JOIN students s       ON s.id = e.student_id
JOIN courses  c       ON c.id = e.course_id
LEFT JOIN departments d ON d.id = s.dept_id
ORDER BY s.name, e.semester;

SELECT
  SUM(CASE WHEN cgpa >= 3.5 THEN 1 ELSE 0 END)                AS excellent,
  SUM(CASE WHEN cgpa >= 3.0 AND cgpa < 3.5 THEN 1 ELSE 0 END) AS good,
  SUM(CASE WHEN cgpa < 3.0 THEN 1 ELSE 0 END)                 AS needs_help,
  SUM(CASE WHEN cgpa IS NULL THEN 1 ELSE 0 END)               AS new_student
FROM students;

WITH student_avg AS (                       -- ধাপ ১: প্রতিজনের গড়
  SELECT student_id, ROUND(AVG(marks),1) AS avg_marks
  FROM enrollments GROUP BY student_id
),
toppers AS (                                -- ধাপ ২: ৭৫+ বাছাই
  SELECT * FROM student_avg WHERE avg_marks >= 75
)
SELECT s.name, t.avg_marks                  -- ধাপ ৩: নাম জোড়া
FROM toppers t JOIN students s ON s.id = t.student_id
ORDER BY t.avg_marks DESC;

WITH ranked AS (
  SELECT name, dept_id, cgpa,
    DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY cgpa DESC) AS rnk
  FROM students WHERE dept_id IS NOT NULL
)
SELECT * FROM ranked WHERE rnk = 1;

CREATE TABLE enrollments (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  student_id INT NOT NULL,
  course_id  INT NOT NULL,
  semester   VARCHAR(20),
  marks      INT,
  UNIQUE (student_id, course_id, semester),     -- একই semester-এ duplicate না
  FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  FOREIGN KEY (course_id)  REFERENCES courses(id)
);

SELECT name, email FROM students
WHERE email = 'nobody' OR '1'='1';   -- সব student বের হয়ে গেল!

SELECT name, cgpa FROM students
WHERE city = 'Dhaka';

SELECT name, cgpa FROM students
WHERE cgpa >= 3.5
ORDER BY cgpa DESC;

SELECT city, COUNT(*) AS total
FROM students
GROUP BY city
ORDER BY total DESC;

SELECT s.name, d.name AS dept
FROM students s
LEFT JOIN departments d ON d.id = s.dept_id;

SELECT s.name FROM students s
LEFT JOIN enrollments e ON e.student_id = s.id
WHERE e.id IS NULL;

SELECT c.title, ROUND(AVG(e.marks),1) AS avg_marks
FROM courses c
JOIN enrollments e ON e.course_id = c.id
GROUP BY c.id, c.title
HAVING AVG(e.marks) >= 75;

SELECT t.name AS teacher,
       COALESCE(h.name, '— (নিজেই Head)') AS head
FROM teachers t
LEFT JOIN teachers h ON h.id = t.head_id;

SELECT MAX(cgpa) AS second_highest
FROM students
WHERE cgpa < (SELECT MAX(cgpa) FROM students);

SELECT d.name, COUNT(t.id) AS teachers,
       COALESCE(ROUND(AVG(t.salary)), 0) AS avg_salary
FROM departments d
LEFT JOIN teachers t ON t.dept_id = d.id
GROUP BY d.id, d.name
ORDER BY avg_salary DESC;

WITH r AS (
  SELECT c.title, s.name, e.marks,
    RANK() OVER (PARTITION BY e.course_id ORDER BY e.marks DESC) AS rnk
  FROM enrollments e
  JOIN students s ON s.id = e.student_id
  JOIN courses  c ON c.id = e.course_id
)
SELECT title, name, marks FROM r WHERE rnk = 1;