SQL exercise

CREATE TABLE `instructor` (
`id` INTEGER,
`first_name` VARCHAR(50),
`last_name` VARCHAR(50),
`email` VARCHAR(50),
`zip_code` VARCHAR(20),
CONSTRAINT `pk_instructor_id`
        PRIMARY KEY(`id`)
);


CREATE TABLE `research` (
`id` INTEGER,
`name` VARCHAR(50),
`description` VARCHAR(500),
`instructor_id` INTEGER,

CONSTRAINT `pk_research_id`
        PRIMARY KEY(`id`),
CONSTRAINT `fk_research_instructor_id`
       FOREIGN KEY(`instructor_id`)
              REFERENCES `instructor`(`id`)
);


CREATE TABLE `teaching_team` (
`id` INTEGER,
`name` VARCHAR(50),

CONSTRAINT `pk_teaching_team_id`
       PRIMARY KEY(`id`)
);



CREATE TABLE `teaching_team_instructor` (
`teaching_team_id` INTEGER,
`instructor_id` INTEGER,

CONSTRAINT `pk_teaching_team_id_instructor_id`
        PRIMARY KEY (`teaching_team_id`, `instructor_id`),
CONSTRAINT `fk_teaching_team_instructor_teaching_team_id`
        FOREIGN KEY (`teaching_team_id`)
               REFERENCES `teaching_team`(`id`),
CONSTRAINT `fk_teaching_team_insructor_instructor_id`
        FOREIGN KEY (`instructor_id`)
               REFERENCES `instructor`(`id`)
);


CREATE TABLE `advanced_technology_course` (
`id` INTEGER,
`name` VARCHAR(50),
`description` VARCHAR(500),
`teaching_team` INTEGER,

CONSTRAINT `pk_advanced_technology_course`
       PRIMARY KEY(`id`),
CONSTRAINT `fk_advanced_technology_course_teaching_team`
       FOREIGN KEY(`teaching_team`)
              REFERENCES `teaching_team`(`id`)
);



CREATE TABLE `training_session` (
`id` INTEGER,
`course_id` INTEGER,
`start_time` TIME,
`date` DATE,

CONSTRAINT `pk_training_session_id`
       PRIMARY KEY(`id`),
CONSTRAINT `fk_training_session_course_id`
       FOREIGN KEY(`course_id`)
              REFERENCES `advanced_technology_course`(`id`)
);



REATE TABLE `trainee`(
`id` INTEGER,
`first_name` VARCHAR(50),
`last_name` VARCHAR(50),
`email` VARCHAR(50),
`zip_code` VARCHAR(20),
`enrolled` DATETIME,
CONSTRAINT `pk_trainee_id`
       PRIMARY KEY(`id`)
);



CREATE TABLE `training_session_trainee` (
`trainee_id` INTEGER,
`training_session_id` INTEGER,

CONSTRAINT `pk_training_session_trainee_trainee_id_training_session_id`
       PRIMARY KEY(`trainee_id`,`training_session_id`),
CONSTRAINT `fk_training_session_trainee_trainee_id`
       FOREIGN KEY(`trainee_id`)
              REFERENCES `trainee`(`id`),
CONSTRAINT `fk_training_session_trainee_training_session_id`
       FOREIGN KEY(`training_session_id`)
              REFERENCES `training_session`(`id`)
);



ALTER TABLE `instructor`
MODIFY COLUMN `email` VARCHAR(50) NOT NULL;

ALTER TABLE `trainee`
MODIFY COLUMN `email` VARCHAR(50) NOT NULL;



ALTER TABLE `research`
MODIFY COLUMN `description` VARCHAR(500) DEFAULT 'N/A';

ALTER TABLE `advanced_technology_course`
MODIFY COLUMN `description` VARCHAR(500) DEFAULT 'N/A';



ALTER TABLE `training_session`
MODIFY COLUMN `start_time` TIME NOT NULL;
ADD CONSTRAINT `chk_training_session_start_time`
       CHECK (`start_time` BETWEEN 9:00:00 AND 17:00:00);



ALTER TABLE `instructor`
ADD CONSTRAINT `chk_instructor_zip_code`
       CHECK (`zip_code` LIKE '??? ???');

ALTER TABLE `trainee`
ADD CONSTRAINT `chk_trainee_zip_code`
       CHECK (`zip_code` LIKE '?#? #?#');





ALTER TABLE `trainee`
MODIFY COLUMN `enrolled` DATETIME DEFAULT NOW();





SELECT COUNT(*) FROM `research`
GROUP BY `instructor_id`;

SELECT R.`instructor_id` FROM `research` R INNER JOIN `teaching_team_instructor` T
ON R.`instructor_id` = T.`instructor_id`;