CREATE, INSERT, UPDATE, DELETE

CREATE TABLE IF NOT EXISTS assignments (
    assignment_id  INTEGER PRIMARY KEY,
    course_id      INTEGER NOT NULL,
    title          TEXT    NOT NULL,
    max_points     INTEGER NOT NULL DEFAULT 100,
    due_date       DATE,
    created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (course_id) REFERENCES courses(course_id) ON DELETE CASCADE
);

CREATE TABLE student_summary AS
SELECT
    s.student_id,
    s.first_name || ' ' || s.last_name AS full_name,
    COUNT(e.enrollment_id)             AS courses,
    ROUND(AVG(e.grade), 1)             AS avg_grade
FROM students s
LEFT JOIN enrollments e ON s.student_id = e.student_id
GROUP BY s.student_id;

CREATE TABLE students_v2 (
    student_id  INTEGER PRIMARY KEY,
    email       TEXT    UNIQUE NOT NULL,
    first_name  TEXT    NOT NULL,
    gender      TEXT    CHECK (gender IN ('M','F')),
    birth_date  DATE    CHECK (birth_date < DATE('now')),
    status      TEXT    NOT NULL DEFAULT 'active',
    CHECK (LENGTH(first_name) >= 2)          -- table-level check
);

FOREIGN KEY (course_id) REFERENCES courses(course_id) ON DELETE RESTRICT   -- default: refuse
FOREIGN KEY (course_id) REFERENCES courses(course_id) ON DELETE CASCADE    -- delete children too
FOREIGN KEY (course_id) REFERENCES courses(course_id) ON DELETE SET NULL   -- orphan them

-- Named columns — the only form you should use in saved code
INSERT INTO students (student_id, first_name, last_name, gender, city, birth_date, enrolled_date)
VALUES (11, 'Nodira', 'Yusupova', 'F', 'Tashkent', '2005-04-01', '2026-09-01');

-- Positional — shorter, but breaks the day a column is added
INSERT INTO students VALUES (11, 'Nodira', 'Yusupova', 'F', 'Tashkent', '2005-04-01', '2026-09-01');

-- Many rows in one statement — much faster than many statements
INSERT INTO courses (course_id, title, category, credits, price) VALUES
    (7, 'Power BI',      'Data', 3, 1600000),
    (8, 'Excel for Analysts', 'Data', 2,  800000);

-- Columns with a DEFAULT or allowing NULL may be omitted
INSERT INTO courses (course_id, title) VALUES (9, 'Statistics II');
-- credits gets its DEFAULT of 3; category and price become NULL

CREATE TABLE top_students (student_id INTEGER, full_name TEXT, avg_grade REAL);

INSERT INTO top_students (student_id, full_name, avg_grade)
SELECT s.student_id,
       s.first_name || ' ' || s.last_name,
       AVG(e.grade)
FROM students s JOIN enrollments e USING (student_id)
WHERE e.grade IS NOT NULL
GROUP BY s.student_id
HAVING AVG(e.grade) >= 85;
-- inserts Aziza 90.0, Dilnoza 88.0, Malika 89.0, Otabek 95.5

-- SQLite / PostgreSQL
INSERT INTO courses (course_id, title, price)
VALUES (1, 'Python Basics', 1300000)
ON CONFLICT (course_id) DO UPDATE SET price = excluded.price;

-- Ignore the row instead of failing
INSERT OR IGNORE INTO courses (course_id, title) VALUES (1, 'Python Basics');

-- MySQL
INSERT INTO courses (course_id, title, price) VALUES (1, 'Python Basics', 1300000)
ON DUPLICATE KEY UPDATE price = VALUES(price);

-- Change one row
UPDATE students SET city = 'Tashkent' WHERE student_id = 7;

-- Several columns at once
UPDATE enrollments
SET grade = 85, status = 'completed'
WHERE enrollment_id = 3;

-- Compute the new value from the old one
UPDATE courses SET price = price * 1.10 WHERE category = 'Data';

-- Conditional update with CASE
UPDATE enrollments
SET status = CASE WHEN grade >= 60 THEN 'completed' ELSE 'failed' END
WHERE grade IS NOT NULL AND status = 'active';

-- SQLite / PostgreSQL: correlated subquery
UPDATE students
SET city = 'Tashkent'
WHERE student_id IN (
    SELECT student_id FROM enrollments WHERE course_id = 5
);

-- PostgreSQL also has UPDATE ... FROM
UPDATE students s
SET city = t.city
FROM temp_city_fixes t
WHERE s.student_id = t.student_id;

DELETE FROM enrollments WHERE status = 'dropped';       -- removes 1 row
DELETE FROM enrollments;                                 -- removes ALL 20 rows
DROP TABLE IF EXISTS top_students;                       -- table gone entirely

-- add a column (existing rows get NULL, or the DEFAULT)
ALTER TABLE students ADD COLUMN phone TEXT;
ALTER TABLE students ADD COLUMN is_active INTEGER NOT NULL DEFAULT 1;

-- rename a column (SQLite 3.25+)
ALTER TABLE students RENAME COLUMN phone TO phone_number;

-- rename the table
ALTER TABLE students RENAME TO learners;

-- drop a column (SQLite 3.35+)
ALTER TABLE students DROP COLUMN phone_number;

BEGIN TRANSACTION;

UPDATE students SET city = 'Samarkand' WHERE student_id = 1;
DELETE FROM enrollments WHERE enrollment_id = 20;

-- Check the result before making it permanent
SELECT * FROM students WHERE student_id = 1;

COMMIT;      -- make it permanent
-- ROLLBACK;   — or undo everything since BEGIN

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- The bad table: everything in one place
enrollments_bad
+----+-------------+---------------------+------------------+-------+---------------+
| id | student     | courses             | teacher          | grade | teacher_phone |
+----+-------------+---------------------+------------------+-------+---------------+
| 1  | Aziza K.    | Python, SQL, ML     | Sardor Yoqubov   | 92    | +998901112233 |
| 2  | Bekzod T.   | Python, Statistics  | Sardor Yoqubov   | 75    | +998901112233 |
+----+-------------+---------------------+------------------+-------+---------------+

| id | student   | course     | teacher        | grade | teacher_phone |
| 1  | Aziza K.  | Python     | Sardor Yoqubov | 92    | +998901112233 |
| 2  | Aziza K.  | SQL        | Rustam Halilov | 88    | +998903334455 |
| 3  | Aziza K.  | ML         | Nodira Alimova | NULL  | +998905556677 |

CREATE VIEW enrollment_details AS
SELECT
    e.enrollment_id,
    s.first_name || ' ' || s.last_name AS student,
    c.title                            AS course,
    COALESCE(t.full_name, '(none)')    AS teacher,
    e.grade,
    e.status,
    e.enrolled_at
FROM enrollments e
JOIN      students s ON e.student_id = s.student_id
JOIN      courses  c ON e.course_id  = c.course_id
LEFT JOIN teachers t ON e.teacher_id = t.teacher_id;

-- now query it like any table
SELECT student, course, grade FROM enrollment_details WHERE grade >= 90;

DROP VIEW IF EXISTS enrollment_details;

-- 1. Create an assignments table with sensible constraints
CREATE TABLE IF NOT EXISTS assignments (
    assignment_id INTEGER PRIMARY KEY,
    course_id     INTEGER NOT NULL REFERENCES courses(course_id),
    title         TEXT NOT NULL,
    max_points    INTEGER NOT NULL DEFAULT 100 CHECK (max_points > 0),
    due_date      DATE
);

-- 2. Insert three assignments in one statement
INSERT INTO assignments (assignment_id, course_id, title, due_date) VALUES
    (1, 2, 'JOIN exercises',      '2026-03-01'),
    (2, 2, 'Window functions',    '2026-03-15'),
    (3, 1, 'Python loops',        '2026-03-10');

-- 3. Snapshot a summary table from a query
CREATE TABLE course_stats AS
SELECT c.course_id, c.title, COUNT(e.enrollment_id) AS enrollments,
       ROUND(AVG(e.grade),1) AS avg_grade
FROM courses c LEFT JOIN enrollments e USING (course_id)
GROUP BY c.course_id, c.title;

-- 4. Safe update: look first, then change
SELECT * FROM enrollments WHERE enrollment_id = 3;
BEGIN TRANSACTION;
UPDATE enrollments SET grade = 85, status = 'completed' WHERE enrollment_id = 3;
SELECT * FROM enrollments WHERE enrollment_id = 3;
ROLLBACK;   -- or COMMIT if it looks right

-- 5. Raise Data-category prices by 10%
BEGIN TRANSACTION;
UPDATE courses SET price = ROUND(price * 1.10) WHERE category = 'Data';
SELECT title, price FROM courses WHERE category = 'Data';
ROLLBACK;

-- 6. Set status from grade using CASE
UPDATE enrollments
SET status = CASE WHEN grade >= 60 THEN 'completed' ELSE 'failed' END
WHERE grade IS NOT NULL;

-- 7. Create the reporting view
CREATE VIEW IF NOT EXISTS enrollment_details AS
SELECT e.enrollment_id, s.first_name || ' ' || s.last_name AS student,
       c.title AS course, e.grade, e.status
FROM enrollments e
JOIN students s USING (student_id)
JOIN courses  c USING (course_id);

-- 8. Clean up everything this exercise created
DROP VIEW  IF EXISTS enrollment_details;
DROP TABLE IF EXISTS course_stats;
DROP TABLE IF EXISTS assignments;