clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2335834 fiddles created (27455 in the last week).

-- Table where relevant data for your associates CREATE TABLE associates ( associate_id INTEGER NOT NULL PRIMARY KEY, associate_name TEXT NOT NULL, other_data TEXT ) ;
 hidden batch(es)


INSERT INTO associates (associate_id, associate_name, other_data) VALUES (1, 'Alice Cooper', 'nice buy'), (2, 'Bob Geldorf', 'difficult to define') ;
2 rows affected
 hidden batch(es)


-- Table where every job is described CREATE TABLE jobs ( job_id INTEGER NOT NULL PRIMARY KEY, job_description TEXT ) ;
 hidden batch(es)


INSERT INTO jobs (job_id, job_description) VALUES (101, 'Cleaning windows'), (102, 'Dish whashing') ;
2 rows affected
 hidden batch(es)


-- Table where you assign which are the job/s that every -- associate must perform, and the average_daily_goal CREATE TABLE jobs_x_associates ( job_id INTEGER NOT NULL REFERENCES jobs(job_id), associate_id INTEGER NOT NULL REFERENCES associates(associate_id), average_daily_goal DECIMAL(5,2) NOT NULL, -- (job_id, associate_id) is the natural primary key PRIMARY KEY(job_id, associate_id) ) ;
 hidden batch(es)


INSERT INTO jobs_x_associates (job_id, associate_id, average_daily_goal) VALUES (101, 1, 5.0), -- Alice should clean windows for avg 5 h/day (101, 2, 3.0), -- Bob should clean windows for avg. 3 h/day (102, 1, 1.0), -- Alice should wash dishes for avg. 1 h/day (102, 2, 0.5); -- Bob should wash dishes for avg. 0.5 h/day
4 rows affected
 hidden batch(es)


-- Table where you store the worked hours that every associate -- performed of every kind of job s/he is assigned to perform CREATE TABLE worked_hours ( job_id INTEGER NOT NULL REFERENCES jobs(job_id), associate_id INTEGER NOT NULL REFERENCES associates(associate_id), work_day date NOT NULL, worked_hours DECIMAL(5,2) NOT NULL, -- The pair (job_id, associate_id) must exist in jobs_x_associates FOREIGN KEY (job_id, associate_id) REFERENCES jobs_x_associates(job_id, associate_id), -- Only one entry per associate,job and day PRIMARY KEY(associate_id, job_id, work_day) ) ;
 hidden batch(es)


-- We record what they did INSERT INTO worked_hours (job_id, associate_id, work_day, worked_hours) VALUES (101, 1, '2017-07-01', 5.0), (101, 2, '2017-07-01', 3.0), (102, 1, '2017-07-01', 0.7), (102, 2, '2017-07-01', 1.0), (101, 1, '2017-07-02', 3.0), (101, 2, '2017-07-02', 2.0), (102, 1, '2017-07-02', 0.8), (102, 2, '2017-07-02', 1.3) ;
8 rows affected
 hidden batch(es)


SELECT work_day, associate_name, job_description, average_daily_goal, worked_hours FROM worked_hours JOIN jobs USING(job_id) JOIN associates USING (associate_id) JOIN jobs_x_associates USING (job_id, associate_id) ORDER BY work_day, associate_name, job_description ;
work_day associate_name job_description average_daily_goal worked_hours
2017-07-01 Alice Cooper Cleaning windows 5.00 5.00
2017-07-01 Alice Cooper Dish whashing 1.00 0.70
2017-07-01 Bob Geldorf Cleaning windows 3.00 3.00
2017-07-01 Bob Geldorf Dish whashing 0.50 1.00
2017-07-02 Alice Cooper Cleaning windows 5.00 3.00
2017-07-02 Alice Cooper Dish whashing 1.00 0.80
2017-07-02 Bob Geldorf Cleaning windows 3.00 2.00
2017-07-02 Bob Geldorf Dish whashing 0.50 1.30
 hidden batch(es)


SELECT associate_name, job_description, average_daily_goal, CAST(avg(worked_hours) AS DECIMAL(10,2)) AS average_worked_hours FROM worked_hours JOIN jobs USING(job_id) JOIN associates USING (associate_id) JOIN jobs_x_associates USING (job_id, associate_id) GROUP BY associate_name, job_description, average_daily_goal ORDER BY associate_name, job_description ;
associate_name job_description average_daily_goal average_worked_hours
Alice Cooper Cleaning windows 5.00 4.00
Alice Cooper Dish whashing 1.00 0.75
Bob Geldorf Cleaning windows 3.00 2.50
Bob Geldorf Dish whashing 0.50 1.15
 hidden batch(es)