Notes

CREATE DATABASE HW2;
USE HW2;
CREATE TABLE Employee(employee_id int PRIMARY KEY AUTO_INCREMENT,
employee_name varchar(30) NOT NULL,
office_no int NOT NULL,
floor_no int NOT NULL CHECK (floor_no BETWEEN 1 AND 10),
phone bigint NOT NULL CHECK (phone BETWEEN 1000000000 AND 9999999999) UNIQUE,
email varchar(35) NOT NULL UNIQUE,
age int);



CREATE TABLE Meeting(meeting_id int PRIMARY KEY AUTO_INCREMENT,
room_no int NOT NULL,
floor_no int NOT NULL CHECK (floor_no BETWEEN 1 AND 10),
meeting_start int NOT NULL CHECK (meeting_start BETWEEN 8 AND 18));

CREATE TABLE EmployeeMeetingBridge( meeting_id int NOT NULL,
employee_id int NOT NULL,
PRIMARY KEY(employee_id, meeting_id),
FOREIGN KEY (employee_id) REFERENCES Employee(employee_id),
FOREIGN KEY (meeting_id) REFERENCES Meeting(meeting_id));


CREATE TABLE Symptom(row_id int PRIMARY KEY AUTO_INCREMENT,
employee_id int NOT NULL,
reported_date DATE NOT NULL,
symptom_id int NOT NULL CHECK (symptom_id BETWEEN 1 AND 5),
FOREIGN KEY (employee_id) REFERENCES Employee(employee_id));



CREATE TABLE Notification(notification_id int PRIMARY KEY AUTO_INCREMENT,
employee_id int NOT NULL,
notification_date DATE NOT NULL,
notification_type varchar(9) NOT NULL CHECK (notification_type IN ('optional', 'mandatory')),
FOREIGN KEY (employee_id) REFERENCES Employee(employee_id));

CREATE TABLE Scan(scan_id int PRIMARY KEY AUTO_INCREMENT,
scan_date DATE NOT NULL,
scan_time int NOT NULL CHECK(scan_time BETWEEN 0 AND 23),
temperature float NOT NULL,
employee_id int NOT NULL,
FOREIGN KEY (employee_id) REFERENCES Employee(employee_id));

CREATE TABLE Test(test_id int PRIMARY KEY AUTO_INCREMENT,
test_location varchar(200) NOT NULL,
test_date DATE NOT NULL,
test_time int NOT NULL CHECK(test_time BETWEEN 0 AND 23),
test_result varchar(8) NOT NULL CHECK (test_result IN ('positive', 'negative')),
employee_id int NOT NULL,
FOREIGN KEY (employee_id) REFERENCES Employee(employee_id));

CREATE TABLE PositiveCase(case_id int PRIMARY KEY AUTO_INCREMENT,
case_date DATE NOT NULL,
resolution varchar(16) NOT NULL CHECK (resolution IN ('back to work', 'left the company', 'deceased')),
employee_id int NOT NULL,
FOREIGN KEY (employee_id) REFERENCES Employee(employee_id));

CREATE TABLE HealthStatus(row_id int PRIMARY KEY AUTO_INCREMENT,
status_date DATE NOT NULL,
status_type varchar(12) NOT NULL CHECK (status_type IN ('sick', 'well', 'hospitalized')),
employee_id int NOT NULL,
FOREIGN KEY (employee_id) REFERENCES Employee(employee_id));

INSERT INTO Employee (employee_name, office_no, floor_no, phone, email, age) VALUES("Chintan", 1, 1,1345678915,"[email protected]",23);
INSERT INTO Employee (employee_name, office_no, floor_no, phone, email, age) VALUES("Ram", 1, 1,6231147891,"[email protected]",23);
INSERT INTO Employee (employee_name, office_no, floor_no, phone, email, age) VALUES("Raj", 11, 1,1244237891,"[email protected]",21);
INSERT INTO Employee (employee_name, office_no, floor_no, phone, email, age) VALUES("Ramesh", 12, 1,1564567891,"[email protected]",22);
INSERT INTO Employee (employee_name, office_no, floor_no, phone, email, age) VALUES("Rutul", 1, 1,1237867891,"[email protected]",43);
INSERT INTO Employee (employee_name, office_no, floor_no, phone, email, age) VALUES("Rakesh", 13, 1,1346567891,"[email protected]",23);
INSERT INTO Employee (employee_name, office_no, floor_no, phone, email, age) VALUES("Ayush", 13, 1,5276566591,"[email protected]",23);
INSERT INTO Employee (employee_name, office_no, floor_no, phone, email, age) VALUES("Arham", 13, 1,1234994591,"[email protected]",63);
INSERT INTO Employee (employee_name, office_no, floor_no, phone, email, age) VALUES("Amogh", 13, 1,1234887591,"[email protected]",12);
INSERT INTO Employee (employee_name, office_no, floor_no, phone, email, age) VALUES("Chinamy", 13, 1,2255567891,"[email protected]",23);
INSERT INTO Employee (employee_name, office_no, floor_no, phone, email, age) VALUES("Ruchi", 12, 1,5234217891,"[email protected]",23);
INSERT INTO Employee (employee_name, office_no, floor_no, phone, email, age) VALUES("Ayushi", 22, 2,6234667891,"[email protected]",23);
INSERT INTO Employee (employee_name, office_no, floor_no, phone, email, age) VALUES("Janice", 22, 2,7234367891,"[email protected]",23);
INSERT INTO Employee (employee_name, office_no, floor_no, phone, email, age) VALUES("Rutvik", 21, 2,9233467891,"[email protected]",22);
INSERT INTO Employee (employee_name, office_no, floor_no, phone, email, age) VALUES("Rutu", 21, 2,1864523891,"[email protected]",22);
INSERT INTO Employee (employee_name, office_no, floor_no, phone, email, age) VALUES("Yen", 21, 2,1234564399,"[email protected]",23);
INSERT INTO Employee (employee_name, office_no, floor_no, phone, email, age) VALUES("Tao", 21, 2,1234566500,"[email protected]",22);
INSERT INTO Employee (employee_name, office_no, floor_no, phone, email, age) VALUES("Raju", 21, 2,1234512801,"[email protected]",24);
INSERT INTO Employee (employee_name, office_no, floor_no, phone, email, age) VALUES("Kamlesh", 33, 3,1223507891,"[email protected]",18);
INSERT INTO Employee (employee_name, office_no, floor_no, phone, email, age) VALUES("Nitin", 31, 3,1234329891,"[email protected]",13);
INSERT INTO Employee (employee_name, office_no, floor_no, phone, email, age) VALUES("Priti", 31, 3,1234235691,"[email protected]",63);
INSERT INTO Employee (employee_name, office_no, floor_no, phone, email, age) VALUES("Ajay", 31, 3,1234523351,"[email protected]",33);
INSERT INTO Employee (employee_name, office_no, floor_no, phone, email, age) VALUES("Smarty", 32, 3,1231143891,"[email protected]",53);
INSERT INTO Employee (employee_name, office_no, floor_no, phone, email, age) VALUES("Shikha", 32, 3,1231167891,"[email protected]",43);
INSERT INTO Employee (employee_name, office_no, floor_no, phone, email, age) VALUES("HarshalL", 31, 3,1211697800,"[email protected]",23);
INSERT INTO Employee (employee_name, office_no, floor_no, phone, email, age) VALUES("Param",44, 4,1231163391,"[email protected]",23);
INSERT INTO Employee (employee_name, office_no, floor_no, phone, email, age) VALUES("Peena", 44, 4,1200565591,"[email protected]",29);
INSERT INTO Employee (employee_name, office_no, floor_no, phone, email, age) VALUES("Richa", 44, 4,1299567791,"[email protected]",27);
INSERT INTO Employee (employee_name, office_no, floor_no, phone, email, age) VALUES("Manali", 41, 4,1774544891,"[email protected]",23);
INSERT INTO Employee (employee_name, office_no, floor_no, phone, email, age) VALUES("Manish", 41, 4,1324231891,"[email protected]",33);
INSERT INTO Employee (employee_name, office_no, floor_no, phone, email, age) VALUES("Pjjw", 41, 4,1231261111,"[email protected]",54);

INSERT INTO Meeting(room_no, floor_no, meeting_start) VALUES(1, 1,10);
INSERT INTO Meeting(room_no, floor_no, meeting_start) VALUES(1, 1,12);
INSERT INTO Meeting(room_no, floor_no, meeting_start) VALUES(2, 1,10);
INSERT INTO Meeting(room_no, floor_no, meeting_start) VALUES(3, 1,11);
INSERT INTO Meeting(room_no, floor_no, meeting_start) VALUES(4, 1,15);
INSERT INTO Meeting(room_no, floor_no, meeting_start) VALUES(5, 1,17);
INSERT INTO Meeting(room_no, floor_no, meeting_start) VALUES(2, 1,9);
INSERT INTO Meeting(room_no, floor_no, meeting_start) VALUES(2, 2,10);
INSERT INTO Meeting(room_no, floor_no, meeting_start) VALUES(2, 2,11);
INSERT INTO Meeting(room_no, floor_no, meeting_start) VALUES(5, 2,15);
INSERT INTO Meeting(room_no, floor_no, meeting_start) VALUES(7, 2,17);
INSERT INTO Meeting(room_no, floor_no, meeting_start) VALUES(8, 2,12);
INSERT INTO Meeting(room_no, floor_no, meeting_start) VALUES(9, 2,11);
INSERT INTO Meeting(room_no, floor_no, meeting_start) VALUES(5, 2,9);
INSERT INTO Meeting(room_no, floor_no, meeting_start) VALUES(9, 3,10);
INSERT INTO Meeting(room_no, floor_no, meeting_start) VALUES(9, 1,13);
INSERT INTO Meeting(room_no, floor_no, meeting_start) VALUES(9, 3,14);
INSERT INTO Meeting(room_no, floor_no, meeting_start) VALUES(5, 3,15);
INSERT INTO Meeting(room_no, floor_no, meeting_start) VALUES(4, 3,16);
INSERT INTO Meeting(room_no, floor_no, meeting_start) VALUES(3, 3,17);
INSERT INTO Meeting(room_no, floor_no, meeting_start) VALUES(2, 3,8);
INSERT INTO Meeting(room_no, floor_no, meeting_start) VALUES(1, 3,8);
INSERT INTO Meeting(room_no, floor_no, meeting_start) VALUES(1, 2,8);

INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (1,1);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (1,2);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (1,3);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (1,4);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (1,5);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (2,3);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (2,4);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (2,1);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (2,5);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (2,6);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (2,7);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (3,5);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (3,6);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (3,7);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (4,5);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (4,6);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (4,7);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (4,8);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (5,7);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (5,8);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (5,11);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (6,1);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (6,2);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (7,1);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (7,2);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (8,1);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (8,2);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (9,1);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (9,2);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (10,1);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (10,2);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (11,1);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (11,2);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (12,1);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (12,2);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (13,1);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (13,2);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (14,1);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (14,2);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (15,1);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (15,2);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (16,1);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (16,2);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (17,1);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (17,2);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (18,8);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (18,7);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (19,6);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (19,5);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (20,1);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (20,2);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (21,1);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (21,2);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (22,1);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (22,2);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (23,2);
INSERT INTO EmployeeMeetingBridge(meeting_id, employee_id) VALUES (23,1);

INSERT INTO Symptom(employee_id, reported_date, symptom_id) VALUES(5, '2021-8-20',5);
INSERT INTO Symptom(employee_id, reported_date, symptom_id) VALUES(1, '2021-8-12',1);
INSERT INTO Symptom(employee_id, reported_date, symptom_id) VALUES(1, '2021-8-13',3);
INSERT INTO Symptom(employee_id, reported_date, symptom_id) VALUES(2, '2021-8-12',1);
INSERT INTO Symptom(employee_id, reported_date, symptom_id) VALUES(2, '2021-8-14',2);
INSERT INTO Symptom(employee_id, reported_date, symptom_id) VALUES(2, '2021-8-15',3);
INSERT INTO Symptom(employee_id, reported_date, symptom_id) VALUES(1, '2021-8-17',2);
INSERT INTO Symptom(employee_id, reported_date, symptom_id) VALUES(1, '2021-8-18',5);
INSERT INTO Symptom(employee_id, reported_date, symptom_id) VALUES(5, '2021-8-21',2);
INSERT INTO Symptom(employee_id, reported_date, symptom_id) VALUES(5, '2021-8-22',1);
INSERT INTO Symptom(employee_id, reported_date, symptom_id) VALUES(5, '2021-8-20',4);
INSERT INTO Symptom(employee_id, reported_date, symptom_id) VALUES(6, '2021-8-20',5);
INSERT INTO Symptom(employee_id, reported_date, symptom_id) VALUES(6, '2021-8-7',2);
INSERT INTO Symptom(employee_id, reported_date, symptom_id) VALUES(6, '2021-8-8',1);
INSERT INTO Symptom(employee_id, reported_date, symptom_id) VALUES(7, '2021-8-9',4);
INSERT INTO Symptom(employee_id, reported_date, symptom_id) VALUES(7, '2021-8-10',5);
INSERT INTO Symptom(employee_id, reported_date, symptom_id) VALUES(7, '2021-8-7',2);
INSERT INTO Symptom(employee_id, reported_date, symptom_id) VALUES(8, '2021-8-22',1);
INSERT INTO Symptom(employee_id, reported_date, symptom_id) VALUES(8, '2021-8-12',4);
INSERT INTO Symptom(employee_id, reported_date, symptom_id) VALUES(8, '2021-8-17',5);
INSERT INTO Symptom(employee_id, reported_date, symptom_id) VALUES(9, '2021-8-7',2);
INSERT INTO Symptom(employee_id, reported_date, symptom_id) VALUES(9, '2021-8-8',1);
INSERT INTO Symptom(employee_id, reported_date, symptom_id) VALUES(11, '2021-8-9',4);
INSERT INTO Symptom(employee_id, reported_date, symptom_id) VALUES(12, '2021-8-10',5);
INSERT INTO Symptom(employee_id, reported_date, symptom_id) VALUES(11, '2021-8-17',2);
INSERT INTO Symptom(employee_id, reported_date, symptom_id) VALUES(12, '2021-8-8',1);
INSERT INTO Symptom(employee_id, reported_date, symptom_id) VALUES(13, '2021-8-9',4);
INSERT INTO Symptom(employee_id, reported_date, symptom_id) VALUES(14, '2021-8-10',5);


INSERT INTO Scan(scan_date,scan_time, temperature,employee_id) VALUES('2021-8-17', 11, 100.5,1);
INSERT INTO Scan(scan_date,scan_time, temperature,employee_id) VALUES('2021-8-19', 12, 91.5,2);
INSERT INTO Scan(scan_date,scan_time, temperature,employee_id) VALUES('2021-8-15', 13, 91.5,3);
INSERT INTO Scan(scan_date,scan_time, temperature,employee_id) VALUES('2021-8-13', 14, 92.5,4);
INSERT INTO Scan(scan_date,scan_time, temperature,employee_id) VALUES('2021-8-12', 15, 93.5,5);
INSERT INTO Scan(scan_date,scan_time, temperature,employee_id) VALUES('2021-8-12', 16, 95.5,6);
INSERT INTO Scan(scan_date,scan_time, temperature,employee_id) VALUES('2021-8-16', 11, 96.5,7);
INSERT INTO Scan(scan_date,scan_time, temperature,employee_id) VALUES('2021-8-17', 10, 97.5,8);
INSERT INTO Scan(scan_date,scan_time, temperature,employee_id) VALUES('2021-8-11', 10, 99.5,9);

INSERT INTO Test(test_location, test_date, test_time, test_result,employee_id) VALUES ('UPC', '2021-8-17', 12,'positive',1);
INSERT INTO Test(test_location, test_date, test_time, test_result,employee_id) VALUES ('Onsite', '2021-8-20', 10,'negative',2);
INSERT INTO Test(test_location, test_date, test_time, test_result,employee_id) VALUES ('UPC', '2021-8-18', 11,'positive',3);

INSERT INTO PositiveCase(case_date, resolution, employee_id) VALUES ('2021-8-22', 'back to work',1);
INSERT INTO PositiveCase(case_date, resolution, employee_id) VALUES ('2021-8-24', 'back to work',3);



INSERT INTO HealthStatus(status_date, status_type, employee_id) VALUES('2021-8-17','sick',1);
INSERT INTO HealthStatus(status_date, status_type, employee_id) VALUES('2021-8-18','sick',1);
INSERT INTO HealthStatus(status_date, status_type, employee_id) VALUES('2021-8-19','sick',1);
INSERT INTO HealthStatus(status_date, status_type, employee_id) VALUES('2021-8-20','sick',1);
INSERT INTO HealthStatus(status_date, status_type, employee_id) VALUES('2021-8-21','sick',1);
INSERT INTO HealthStatus(status_date, status_type, employee_id) VALUES('2021-8-22','well',1);
INSERT INTO HealthStatus(status_date, status_type, employee_id) VALUES('2021-8-19','sick',3);
INSERT INTO HealthStatus(status_date, status_type, employee_id) VALUES('2021-8-20','sick',3);
INSERT INTO HealthStatus(status_date, status_type, employee_id) VALUES('2021-8-21','sick',3);
INSERT INTO HealthStatus(status_date, status_type, employee_id) VALUES('2021-8-22','sick',3);
INSERT INTO HealthStatus(status_date, status_type, employee_id) VALUES('2021-8-23','well',3);


INSERT INTO Notification(employee_id,notification_date,notification_type) VALUES (2,'2021-8-17','mandatory');
INSERT INTO Notification(employee_id,notification_date,notification_type) VALUES (3,'2021-8-17','mandatory');
INSERT INTO Notification(employee_id,notification_date,notification_type) VALUES (4,'2021-8-17','mandatory');
INSERT INTO Notification(employee_id,notification_date,notification_type) VALUES (5,'2021-8-17','mandatory');
INSERT INTO Notification(employee_id,notification_date,notification_type) VALUES (6,'2021-8-17','optional');
INSERT INTO Notification(employee_id,notification_date,notification_type) VALUES (7,'2021-8-17','optional');
INSERT INTO Notification(employee_id,notification_date,notification_type) VALUES (8,'2021-8-17','optional');
INSERT INTO Notification(employee_id,notification_date,notification_type) VALUES (9,'2021-8-17','optional');
INSERT INTO Notification(employee_id,notification_date,notification_type) VALUES (10,'2021-8-17','optional');
INSERT INTO Notification(employee_id,notification_date,notification_type) VALUES (11,'2021-8-17','optional');
INSERT INTO Notification(employee_id,notification_date,notification_type) VALUES (1,'2021-8-18','mandatory');
INSERT INTO Notification(employee_id,notification_date,notification_type) VALUES (2,'2021-8-17','mandatory');
INSERT INTO Notification(employee_id,notification_date,notification_type) VALUES (3,'2021-8-18','optional');
INSERT INTO Notification(employee_id,notification_date,notification_type) VALUES (4,'2021-8-18','optional');
INSERT INTO Notification(employee_id,notification_date,notification_type) VALUES (5,'2021-8-18','optional');
INSERT INTO Notification(employee_id,notification_date,notification_type) VALUES (6,'2021-8-18','optional');
INSERT INTO Notification(employee_id,notification_date,notification_type) VALUES (7,'2021-8-18','optional');
INSERT INTO Notification(employee_id,notification_date,notification_type) VALUES (8,'2021-8-18','optional');
INSERT INTO Notification(employee_id,notification_date,notification_type) VALUES (9,'2021-8-18','optional');
INSERT INTO Notification(employee_id,notification_date,notification_type) VALUES (10,'2021-8-18','optional');
INSERT INTO Notification(employee_id,notification_date,notification_type) VALUES (11,'2021-8-18','optional');



SELECT symptom_id from Symptom
GROUP BY(symptom_id) having count(symptom_id)=
(select max(ms) from (select count(symptom_id) as ms
from symptom
group by(symptom_id)) temp);


SELECT Employee.floor_no from Employee INNER JOIN HealthStatus ON HealthStatus.employee_id = Employee.employee_id
where HealthStatus.status_type = "sick" ;



SELECT (
-- subquery to check for total number of scans
SELECT COUNT(*)
FROM Scan
WHERE scan_date > "2021-08-10" AND scan_date < "2021-08-22"
) AS total_scan_count, (
-- subquery to check for total number of self_reports
SELECT COUNT( DISTINCT employee_id)
FROM Symptom
WHERE reported_date > "2021-08-10" AND reported_date < "2021-08-22"
) AS total_self_report, (
-- subquery to check for total number of tests
SELECT COUNT(*)
FROM Test
WHERE test_date > "2021-08-10" AND test_date < "2021-08-22"
) AS total_test, (
-- subquery to check for total number of covid_cASes
SELECT COUNT(*)
FROM Test
WHERE case_date > "2021-08-10" AND case_date < "2021-08-22"
) AS total_covid_cases;