SQLize | PHPize | SQLtest

create table courses ( id int primary key auto_increment, name varchar(64) ); create table modules ( id int primary key auto_increment, course_id int references courses(id), name varchar(64), order_seq int ); -- Insert 2 courses INSERT INTO courses (name) VALUES ('Introduction to PHP'), ('Advanced SQL Techniques'); -- Assuming the above INSERTs will result in course IDs 1 and 2 due to auto_increment -- If your course IDs are different, adjust the course_id in the modules INSERTs accordingly. -- Insert 5 modules, linking them to the courses -- 3 modules for 'Introduction to PHP' (course_id = 1) INSERT INTO modules (course_id, name, order_seq) VALUES (1, 'PHP Fundamentals', 1), (1, 'Variables and Data Types', 2), (1, 'Control Structures (If, Else, Loops)', 3); -- 2 modules for 'Advanced SQL Techniques' (course_id = 2) INSERT INTO modules (course_id, name, order_seq) VALUES (2, 'SQL Joins Explained', 1), (2, 'Database Normalization', 2); SELECT courses.name AS course, modules.name AS module, ROW_NUMBER() OVER (PARTITION BY courses.id ORDER BY order_seq) AS module_number FROM courses JOIN modules ON courses.id = course_id order by courses.id, module_number; DELETE FROM modules WHERE id = 2; SELECT courses.name AS course, modules.name AS module, ROW_NUMBER() OVER (PARTITION BY courses.id ORDER BY order_seq) AS module_number FROM courses JOIN modules ON courses.id = course_id order by courses.id, module_number;