rYGk8XTK

1CREATE TABLE IF NOT EXISTS store
2	(id VARCHAR(10),
3    location VARCHAR(32),
4    PRIMARY KEY (id));
5
6CREATE TABLE IF NOT EXISTS employee
7	(id VARCHAR(10),
8    name VARCHAR(32),
9    address VARCHAR(255),
10    position VARCHAR(32),
11    PRIMARY KEY (id));
12    
13CREATE TABLE IF NOT EXISTS employee_store
14	(id VARCHAR(10),
15    employee_id VARCHAR(10),
16    store_id VARCHAR(10),
17    start_time timestamp,
18    end_time timestamp,
19    PRIMARY KEY (id),
20    FOREIGN KEY (employee_id) REFERENCES employee(id),
21    FOREIGN KEY (store_id) REFERENCES store(id));
22       
23       
24       
25        
26SELECT e.name, es.total_hours FROM employee e
27    INNER JOIN (SELECT employee_id, SUM(TIMESTAMPDIFF(HOUR ,employee_store.start_time, employee_store.end_time)) as total_hours FROM employee_store
28					WHERE employee_store.start_time BETWEEN SUBDATE(CURDATE(), INTERVAL 1 MONTH) AND NOW()
29                    GROUP BY(employee_id))
30                    es ON es.employee_id = e.id;
31                    
32#this return the store location, next working day of the week for a manager at that store and the manager's name
33SELECT st.location as store_location, es.day_of_week as next_working_day_of_week, e.name as manager_name FROM store st
34    left join (SELECT employee_id, store_id, DAYOFWEEK(employee_store.start_time) as day_of_week FROM employee_store) es ON es.store_id = st.id
35	inner join (SELECT id, name FROM employee WHERE employee.position = 'MANAGER') e on e.id = es.employee_id;