-- Tristan Sabol 
-- Due Date 12/18/24
-- Attendance Tracker
--This Database put names into a Attendance for shops and years    
Create Database Attendance;
--  This swicthes use into my database to put data in 
use Attendance;
-- This  Creates a table and then i put the field into the table 
Create table tracker (
id int primary key auto_increment,
First_Name varchar (50),
Last_Name varchar (50),
Class_Year varchar (20),
Day_of_Week varchar (20),
Shop varchar (2),
Entry_Reason varchar (15)

);
-- it makes the Last_Name become A key to make the row easy to use 
create index idx_Last_Name on tracker(Last_Name);
-- it makes the SHOP become A key to make the row easy to use 
create index idx_Shop on tracker(Shop);

-- This give the info to make sure that i did the table correctly
Describe tracker;
-- this insert the values into the table and saves it there
insert into tracker
(First_Name, Last_Name, Class_Year, Day_of_Week, Shop, Entry_Reason)
-- This is the vaules that get inseted into the table 
value
('Enrique', 'Bruno', '2021', 'Tuesday','EL' ,'Absent'),
('Enrique', 'Bruno', '2019', 'Monday','EL' ,'Tardy'),
('Enrique', 'Bruno', '2018', 'Wednesday','EL' ,'Early Dismissal'),
('Enrique', 'Bruno', '2021', 'Monday','EL' ,'WBL'),
('Enrique', 'Bruno', '2020', 'Friday','EL' , 'Absent'),
('Luanna', 'Grimaldi', '2018', 'Tuesday','AU', 'Absent'),
('Luanna', 'Grimaldi', '2019', 'Thursday','AU', 'Tardy'),
('Luanna', 'Grimaldi', '2020', 'Wednesday','AU', 'Early Dismissal'),
('Luanna', 'Grimaldi', '2021', 'Monday','AU', 'WBL'),
('Luanna', 'Grimaldi', '2018', 'Friday','AU', 'Absent'),
('Katina', 'Alspaugh', '2018', 'Monday','EL', 'Tardy'),
('Katina', 'Alspaugh', '2019', 'Tuesday','EL', 'Early Dismissal'),
('Katina', 'Alspaugh', '2018', 'Monday','EL', 'WBL'),
('Katina', 'Alspaugh', '2018', 'Friday','EL', 'Absent'),
('Katina', 'Alspaugh', '2019', 'Tuesday','EL', 'Early Dismissal'),
('Anamaria', 'Laplant', '2021', 'Thursday','PH', 'WBL'),
('Anamaria', 'Laplant', '2020', 'Wednesday','PH', 'Absent'),
('Anamaria', 'Laplant', '2019', 'Monday','PH', 'Tardy'),
('Anamaria', 'Laplant', '2021', 'Friday','PH', 'WBL'),
('Anamaria', 'Laplant', '2018', 'Tuesday','PH', 'Early Dismissal'),
('Suzi', 'Colas', '2021', 'Tuesday','CR', 'WBL'),
('Suzi', 'Colas', '2019', 'Monday','CR', 'Absent'),
('Suzi', 'Colas', '2021', 'Friday','CR', 'WBL'),
('Suzi', 'Colas', '2018', 'Monday','CR', 'Absent'),
('Suzi', 'Colas', '2020', 'Thursday','CR', 'Early Dismissal'),
('Teresita', 'Halko', '2019', 'Monday','EM', 'WBL'),
('Teresita', 'Halko', '2018', 'Thursday','EM', 'Tardy'),
('Teresita', 'Halko', '2020', 'Wednesday','EM', 'Absent'),
('Teresita', 'Halko', '2021', 'Friday','EM', 'Early Dismissal'),
('Teresita', 'Halko', '2019', 'Friday','EM', 'WBL'),
('Mia', 'Carrier', '2019', 'Monday','EM', 'Absent'),
('Mia', 'Carrier', '2021', 'Thursday','IT', 'Absent'),
('Mia', 'Carrier', '2020', 'Monday','IT', 'Tardy'),
('Mia', 'Carrier', '2018', 'Friday','IT', 'Absent'),
('Mia', 'Carrier', '2021', 'Thursday','IT', 'Tardy'),
('Vesta', 'Chunn', '2020', 'Tuesday','PH', 'WBL'),
('Vesta', 'Chunn', '2018', 'Wednesday','PH', 'Tardy'),
('Vesta', 'Chunn', '2021', 'Friday','PH', 'Absent'),
('Vesta', 'Chunn', '2019', 'Thursday','PH', 'Early Dismissal'),
('Vesta', 'Chunn', '2020', 'Monday','PH', 'WBL'),
('Floria', 'Vanriper', '2019', 'Wednesday','AT', 'WBL'),
('Floria', 'Vanriper', '2021', 'Monday','AT', 'Tardy'),
('Floria', 'Vanriper', '2018', 'Friday','AT', 'Absent'),
('Floria', 'Vanriper', '2020', 'Tuesday','AT', 'Early Dismissal'),
('Floria', 'Vanriper', '2021', 'Wednesday','AT', 'WBL'),
('Maryln', 'Kuster', '2020', 'Thursday','AU', 'WBL'),
('Maryln', 'Kuster', '2019', 'Tuesday','AU', 'Absent'),
('Maryln', 'Kuster', '2018', 'Wednesday','AU', 'Tardy'),
('Maryln', 'Kuster', '2021', 'Friday','AU', 'Absent'),
('Maryln', 'Kuster', '2020', 'Friday','AU', 'Early Dismissal'),
('Kathryn', 'Banach', '2018', 'Monday','AU', 'WBL'),
('Kathryn', 'Banach', '2021', 'Tuesday','AU', 'Tardy'),
('Kathryn', 'Banach', '2020', 'Wednesday','AU', 'Absent'),
('Kathryn', 'Banach', '2019', 'Thursday','AU', 'Early Dismissal'),
('Kathryn', 'Banach', '2021', 'Friday','AU', 'WBL'),
('Lavada', 'Baver', '2019', 'Monday','ET', 'Tardy'),
('Lavada', 'Baver', '2021', 'Tuesday','ET', 'Early Dismissal'),
('Lavada', 'Baver', '2020', 'Thursday','ET', 'WBL'),
('Lavada', 'Baver', '2018', 'Friday','ET', 'Absent'),
('Lavada', 'Baver', '2020', 'Tuesday','ET', 'Absent'),
('Stacey', 'Coll', '2021', 'Wednesday','ET', 'Tardy'),
('Stacey', 'Coll', '2018', 'Monday','ET', 'Early Dismissal'),
('Stacey', 'Coll', '2020', 'Friday','ET', 'WBL'),
('Stacey', 'Coll', '2019', 'Thursday','ET', 'Tardy'),
('Stacey', 'Coll', '2021', 'Monday','ET', 'Absent'),
('Rosita', 'Ohalloran', '2019', 'Wednesday','CD', 'WBL'),
('Rosita', 'Ohalloran', '2020', 'Monday','CD', 'Tardy'),
('Rosita', 'Ohalloran', '2021', 'Friday','CD', 'Absent'),
('Rosita', 'Ohalloran', '2018', 'Thursday','CD', 'Early Dismissal'),
('Rosita', 'Ohalloran', '2020', 'Wednesday','CD', 'WBL'),
('Tamisha', 'Talcott', '2020', 'Thursday','HV', 'Tardy'),
('Tamisha', 'Talcott', '2021', 'Monday','HV', 'Early Dismissal'),
('Tamisha', 'Talcott', '2019', 'Friday','HV', 'Absent'),
('Tamisha', 'Talcott', '2020', 'Wednesday','HV', 'Tardy'),
('Tamisha', 'Talcott', '2019', 'Monday','HV', 'WBL'),
('Elba', 'Carver', '2020', 'Tuesday','CA', 'WBL'),
('Elba', 'Carver', '2021', 'Thursday','CA', 'Absent'),
('Elba', 'Carver', '2018', 'Monday','CA', 'Tardy'),
('Elba', 'Carver', '2021', 'Wednesday','CA', 'Early Dismissal'),
('Elba', 'Carver', '2019', 'Thursday','CA', 'WBL'),
('Ronda', 'Jetter', '2021', 'Monday','EM', 'Absent'),
('Ronda', 'Jetter', '2020', 'Friday','EM', 'Tardy'),
('Ronda', 'Jetter', '2018', 'Tuesday','EM', 'WBL'),
('Ronda', 'Jetter', '2019', 'Wednesday','EM', 'Early Dismissal'),
('Ronda', 'Jetter', '2020', 'Thursday','EM', 'Absent'),
('Ardelia', 'Italiano', '2021', 'Monday','ET', 'Tardy'),
('Ardelia', 'Italiano', '2019', 'Tuesday','ET', 'WBL'),
('Ardelia', 'Italiano', '2020', 'Friday','ET', 'Absent'),
('Ardelia', 'Italiano', '2021', 'Thursday','ET' ,'Early Dismissal'),
('Ardelia', 'Italiano', '2018', 'Wednesday','ET', 'WBL'),
('Kelley', 'Wortham', '2019', 'Friday','HC', 'Tardy'),
('Kelley', 'Wortham', '2021', 'Monday','HC', 'Absent'),
('Kelley', 'Wortham', '2020', 'Wednesday','HC', 'WBL'),
('Kelley', 'Wortham', '2018', 'Monday','HC', 'Early Dismissal'),
('Kelley', 'Wortham', '2021', 'Tuesday','HC', 'WBL'),
('Sueann', 'Malatesta', '2020', 'Thursday','HC', 'Absent'),
('Sueann', 'Malatesta', '2019', 'Monday','HC', 'WBL'),
('Sueann', 'Malatesta', '2021', 'Wednesday','HC', 'Tardy'),
('Sueann', 'Malatesta', '2018', 'Tuesday','HC', 'Tardy'),
('Sueann', 'Malatesta', '2020', 'Friday','HC', 'Early Dismissal'),
('Marissa', 'Mccants', '2021', 'Monday','EM', 'WBL'),
('Marissa', 'Mccants', '2019', 'Thursday','EM', 'Absent'),
('Marissa', 'Mccants', '2020', 'Tuesday','EM', 'Tardy'),
('Marissa', 'Mccants', '2021', 'Friday','EM', 'Early Dismissal'),
('Marissa', 'Mccants', '2018', 'Wednesday','EM', 'WBL'),
('Pei', 'Wrinkle', '2020', 'Wednesday','EL', 'Absent'),
('Pei', 'Wrinkle', '2021', 'Monday','EL', 'Tardy'),
('Pei', 'Wrinkle', '2019', 'Tuesday','EL', 'Early Dismissal'),
('Pei', 'Wrinkle', '2018', 'Friday','EL', 'WBL'),
('Pei', 'Wrinkle', '2021', 'Thursday','EL', 'Absent'),
('Frida', 'Furlong', '2019', 'Monday','EL', 'WBL'),
('Frida', 'Furlong', '2020', 'Thursday','EL', 'Tardy'),
('Frida', 'Furlong', '2018', 'Wednesday','EL', 'Absent'),
('Frida', 'Furlong', '2021', 'Tuesday','EL', 'Early Dismissal'),
('Frida', 'Furlong', '2020', 'Friday','EL', 'WBL'),
('Adalberto', 'Ebel', '2020', 'Monday','CU', 'Absent'),
('Adalberto', 'Ebel', '2021', 'Wednesday','CU', 'Tardy'),
('Adalberto', 'Ebel', '2019', 'Friday','CU', 'Early Dismissal'),
('Adalberto', 'Ebel', '2018', 'Tuesday','CU', 'WBL'),
('Adalberto', 'Ebel', '2020', 'Wednesday','CU', 'Absent'),
('Charmain', 'Rigney', '2021', 'Friday','AU', 'Tardy'),
('Charmain', 'Rigney', '2018', 'Monday','AU', 'WBL'),
('Charmain', 'Rigney', '2020', 'Thursday','AU', 'Absent'),
('Charmain', 'Rigney', '2019', 'Tuesday','AU', 'Early Dismissal'),
('Charmain', 'Rigney', '2021', 'Thursday','AU', 'WBL'),
('Sage', 'Romberger', '2019', 'Wednesday','CR', 'Tardy'),
('Sage', 'Romberger', '2020', 'Monday','CR', 'Absent'),
('Sage', 'Romberger', '2021', 'Monday','CR', 'Early Dismissal'),
('Sage', 'Romberger', '2020', 'Friday','CR', 'WBL'),
('Sage', 'Romberger', '2018', 'Tuesday','CR', 'Absent'),
('Laverna', 'Aguas', '2020', 'Thursday','IT', 'WBL'),
('Laverna', 'Aguas', '2019', 'Monday','IT', 'Tardy'),
('Laverna', 'Aguas', '2018', 'Friday','IT', 'Early Dismissal'),
('Laverna', 'Aguas', '2021', 'Tuesday','IT', 'WBL'),
('Laverna', 'Aguas', '2020', 'Wednesday','IT', 'Absent'),
('Casie', 'Blauvelt', '2021', 'Thursday','CR', 'Absent'),
('Casie', 'Blauvelt', '2019', 'Wednesday','CR', 'WBL'),
('Casie', 'Blauvelt', '2020', 'Monday','CR', 'Tardy'),
('Casie', 'Blauvelt', '2018', 'Friday','CR', 'Early Dismissal'),
('Casie', 'Blauvelt', '2021', 'Tuesday','CR', 'WBL'),
('Hannah', 'Villagomez', '2021', 'Monday','AT', 'Tardy'),
('Hannah', 'Villagomez', '2019', 'Tuesday','AT', 'WBL'),
('Hannah', 'Villagomez', '2018', 'Thursday','AT', 'Absent'),
('Hannah', 'Villagomez', '2020', 'Friday','AT', 'Early Dismissal'),
('Hannah', 'Villagomez', '2020', 'Wednesday','AT', 'Tardy'),
('Daniel', 'Pinon', '2020', 'Thursday','EL', 'Tardy'),
('Daniel', 'Pinon', '2021', 'Friday','EL', 'WBL'),
('Daniel', 'Pinon', '2018', 'Wednesday','EL', 'Absent'),
('Daniel', 'Pinon', '2020', 'Tuesday','EL', 'WBL'),
('Daniel', 'Pinon', '2019', 'Monday','EL', 'Early Dismissal')
;
-- This displays the text in a sql table by pull the data togather in a graph 
SELECT * FROM tracker;

SELECT CONCAT(First_Name, ' ', Last_Name, ' - ', Shop) AS "Attendance List"
FROM tracker
ORDER BY Shop ASC;

SELECT Day_of_Week, COUNT(*) AS "Attendance Count"
FROM tracker
WHERE Entry_Reason = 'Absent'
GROUP BY Day_of_Week
ORDER BY FIELD(Day_of_Week, 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday');

SELECT CONCAT(First_Name, ' ', Last_Name, ' - ', Shop, ' - ', Class_Year) AS "WBL List"
FROM tracker
WHERE Entry_Reason = 'WBL';

SELECT CONCAT(Last_Name, ', ', First_Name, ' - ', Shop, ' - ', Class_Year) AS "For each shop"
FROM tracker
ORDER BY Last_Name;

SELECT CONCAT(First_Name, ' ', Last_Name, ' - ', Class_Year) AS "Student Data"
FROM tracker
WHERE Day_of_Week = 'Tuesday' AND Entry_Reason = 'Early Dismissal';

-- This is to Drop or delete the database from the Server 
drop database Attendance;