-- Create simple table
-- Status values: 0=deleted, 1=inactive, 2=active, >=2 activate and different use cases
CREATE TABLE `students` (
    `id` int(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `status` tinyint(1) NOT NULL DEFAULT 2,
    `registered` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated` timestamp NULL DEFAULT NULL,
    `first_name` varchar(255) NOT NULL,
    `last_name` varchar(255) NOT NULL,
    `description` text
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;

-- Create another simple table
-- Status values: 0=deleted, 1=inactive, 2=active, >=2 activate and different use cases
CREATE TABLE `courses` (
    `id` int(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `status` tinyint(1) NOT NULL DEFAULT 2,
    `code` VARCHAR(32) NOT NULL DEFAULT '',
    `title` VARCHAR(255) NOT NULL,
    `description` text
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;

-- Create ManyToMany connection between the tables
CREATE TABLE `course_membership` (
`id_student` INT UNSIGNED NOT NULL,
`id_course` INT UNSIGNED NOT NULL,
PRIMARY KEY (`id_student`, `id_course`),
CONSTRAINT `constr_course_membership_student_fk`
    FOREIGN KEY `student_fk` (`id_student`) REFERENCES `students` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `constr_course_membership_course_fk`
    FOREIGN KEY `course_fk` (`id_course`) REFERENCES `courses` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;

-- Insert row into table
INSERT INTO checklists (todo_id, task_id, is_completed, todo)
VALUES (1, 1, 0, 'My first task');

-- Insert multiple rows into table
INSERT INTO checklists (todo_id, task_id, is_completed, todo)
VALUES (2, 1, 0, 'My second task'),
(2, 2, 0, 'My third task');

-- Insert using update syntax
INSERT INTO checklists SET
todo_id = 3,
task_id = 1,
is_completed = 0,
todo = 'My forth task';

-- Get active students from course 1
SELECT cm.*, c.title, CONCAT(s.first_name, ' ', s.last_name) AS student, s.status
FROM course_membership AS cm
    INNER JOIN courses AS c ON (cm.id_course = c.id)
    INNER JOIN students AS s ON (cm.id_student = s.id AND s.status = 2)
WHERE cm.id_course = 1