SELECT count(DISTINCT incident_type_code) FROM Behavior_Incident
How many distinct incident type codes are there?
CREATE TABLE `Ref_Address_Types` (
`address_type_code` VARCHAR(15) PRIMARY KEY,
`address_type_description` VARCHAR(80)
);
CREATE TABLE `Ref_Detention_Type` (
`detention_type_code` VARCHAR(10) PRIMARY KEY,
`detention_type_description` VARCHAR(80)
);
CREATE TABLE `Ref_Incident_Type` (
`incident_type_code` VARCHAR(10) PRIMARY KEY,
`incident_type_description` VARCHAR(80)
);
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`line_1` VARCHAR(120),
`line_2` VARCHAR(120),
`line_3` VARCHAR(120),
`city` VARCHAR(80),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50),
`other_address_details` VARCHAR(255)
);
CREATE TABLE `Students` (
`student_id` INTEGER PRIMARY KEY,
`address_id` INTEGER NOT NULL,
`first_name` VARCHAR(80),
`middle_name` VARCHAR(40),
`last_name` VARCHAR(40),
`cell_mobile_number` VARCHAR(40),
`email_address` VARCHAR(40),
`date_first_rental` DATETIME,
`date_left_university` DATETIME,
`other_student_details` VARCHAR(255),
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` )
);
CREATE TABLE `Teachers` (
`teacher_id` INTEGER PRIMARY KEY,
`address_id` INTEGER NOT NULL,
`first_name` VARCHAR(80),
`middle_name` VARCHAR(80),
`last_name` VARCHAR(80),
`gender` VARCHAR(1),
`cell_mobile_number` VARCHAR(40),
`email_address` VARCHAR(40),
`other_details` VARCHAR(255),
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` )
);
CREATE TABLE `Assessment_Notes` (
`notes_id` INTEGER NOT NULL ,
`student_id` INTEGER,
`teacher_id` INTEGER NOT NULL,
`date_of_notes` DATETIME,
`text_of_notes` VARCHAR(255),
`other_details` VARCHAR(255),
FOREIGN KEY (`student_id` ) REFERENCES `Students`(`student_id` ),
FOREIGN KEY (`teacher_id` ) REFERENCES `Teachers`(`teacher_id` )
);
CREATE TABLE `Behavior_Incident` (
`incident_id` INTEGER PRIMARY KEY,
`incident_type_code` VARCHAR(10) NOT NULL,
`student_id` INTEGER NOT NULL,
`date_incident_start` DATETIME,
`date_incident_end` DATETIME,
`incident_summary` VARCHAR(255),
`recommendations` VARCHAR(255),
`other_details` VARCHAR(255),
FOREIGN KEY (`incident_type_code` ) REFERENCES `Ref_Incident_Type`(`incident_type_code` ),
FOREIGN KEY (`student_id` ) REFERENCES `Students`(`student_id` )
);
CREATE TABLE `Detention` (
`detention_id` INTEGER PRIMARY KEY,
`detention_type_code` VARCHAR(10) NOT NULL,
`teacher_id` INTEGER,
`datetime_detention_start` DATETIME,
`datetime_detention_end` DATETIME,
`detention_summary` VARCHAR(255),
`other_details` VARCHAR(255),
FOREIGN KEY (`detention_type_code` ) REFERENCES `Ref_Detention_Type`(`detention_type_code` ),
FOREIGN KEY (`teacher_id` ) REFERENCES `Teachers`(`teacher_id` )
);
CREATE TABLE `Student_Addresses` (
`student_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`date_address_to` DATETIME,
`monthly_rental` DECIMAL(19,4),
`other_details` VARCHAR(255),
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` ),
FOREIGN KEY (`student_id` ) REFERENCES `Students`(`student_id` )
);
CREATE TABLE `Students_in_Detention` (
`student_id` INTEGER NOT NULL,
`detention_id` INTEGER NOT NULL,
`incident_id` INTEGER NOT NULL,
FOREIGN KEY (`incident_id` ) REFERENCES `Behavior_Incident`(`incident_id` ),
FOREIGN KEY (`detention_id` ) REFERENCES `Detention`(`detention_id` ),
FOREIGN KEY (`student_id` ) REFERENCES `Students`(`student_id` )
);
INSERT INTO Ref_Address_Types (`address_type_code`, `address_type_description`) VALUES ('BILL', 'Billing');
INSERT INTO Ref_Detention_Type (`detention_type_code`, `detention_type_description`) VALUES ('BREAK ', 'During Break time');
INSERT INTO Ref_Incident_Type (`incident_type_code`, `incident_type_description`) VALUES ('NOISE', 'Noise');
INSERT INTO Addresses (`address_id`, `line_1`, `line_2`, `line_3`, `city`, `zip_postcode`, `state_province_county`, `country`, `other_address_details`) VALUES (1, '020 Orie Canyon', NULL, NULL, 'North Loyceville', '197', 'Hawaii', 'USA', NULL);
INSERT INTO Students (`student_id`, `address_id`, `first_name`, `middle_name`, `last_name`, `cell_mobile_number`, `email_address`, `date_first_rental`, `date_left_university`, `other_student_details`) VALUES (1, 19, 'Emma', 'Frederic', 'Rohan', '235.899.9744', 'derrick.jenkins@example.com', '2017-12-05 15:20:04', '2018-03-03 03:33:05', NULL);
INSERT INTO Teachers (`teacher_id`, `address_id`, `first_name`, `middle_name`, `last_name`, `gender`, `cell_mobile_number`, `email_address`, `other_details`) VALUES (1, 15, 'Lyla', 'Wilson', 'Medhurst', '1', '792.333.7714', 'ohammes@example.com', NULL);
INSERT INTO Assessment_Notes (`notes_id`, `student_id`, `teacher_id`, `date_of_notes`, `text_of_notes`, `other_details`) VALUES (1, 7, 3, '1978-04-15 04:49:18', NULL, NULL);
INSERT INTO Behavior_Incident (`incident_id`, `incident_type_code`, `student_id`, `date_incident_start`, `date_incident_end`, `incident_summary`, `recommendations`, `other_details`) VALUES (1, 'NOISE', 6, '2017-07-09 10:04:13', '2018-03-08 14:08:54', NULL, NULL, NULL);
INSERT INTO Detention (`detention_id`, `detention_type_code`, `teacher_id`, `datetime_detention_start`, `datetime_detention_end`, `detention_summary`, `other_details`) VALUES (1, 'AFTER', 7, '2017-09-05 00:38:25', '2018-03-08 02:08:32', NULL, NULL);
INSERT INTO Student_Addresses (`student_id`, `address_id`, `date_address_from`, `date_address_to`, `monthly_rental`, `other_details`) VALUES (6, 12, '2017-10-16 13:56:34', '2018-03-15 10:37:19', '826.4319', 'house');
INSERT INTO Students_in_Detention (`student_id`, `detention_id`, `incident_id`) VALUES (3, 15, 1);