UrNvSDm3

1use school_sport_clubs;
2
3drop table if exists programmers;
4CREATE TABLE programmers(
5	id INT AUTO_INCREMENT PRIMARY KEY,
6    name VARCHAR(255) NOT NULL,
7    address VARCHAR(255) NOT NULL,
8    startWorkingDate DATE, /** YYYY-MM-DD**/
9    teamLead_id INT NULL DEFAULT NULL,
10    CONSTRAINT FOREIGN KEY (teamLead_id) REFERENCES programmers(id)
11);
12
13INSERT INTO `programmers` (`name`, `address`, `startWorkingDate`, `teamLead_id`) 
14VALUES ('Ivan Ivanov', 'Sofia', '1999-05-25', NULL),
15('Georgi Petkov Todorov', 'Bulgaria- Sofia Nadezhda, bl. 35', '2002-12-01', '1'),
16('Todor Petkov', 'Sofia - Liylin 7', '2009-11-01', 1),
17('Sofiq Dimitrova Petrova', 'Sofia - Mladost 4, bl. 7', '2010-01-01', 1),
18('Teodor Ivanov Stoyanov', 'Sofia - Obelya, bl. 48', '2011-10-01', NULL),
19('Iliya Stoynov Todorov', 'Sofia - Nadezhda, bl. 28', '2000-02-01', 5),
20('Mariela Dimitrova Yordanova', 'Sofia - Knyajevo, bl. 17', '2005-05-01', 5),
21('Elena Miroslavova Georgieva', 'Sofia - Krasno Selo, bl. 27', '2008-04-01', 5),
22('Teodor Milanov Milanov', 'Sofia - Lozenetz', '2012-04-01', 5);
23
24create table coach_work(
25	id int auto_increment primary key,
26    coach_id int not null,
27    group_id int not null,
28    number_of_hours int not null default 1,
29	date Datetime not null,
30    foreign key (coach_id) references coaches(id),
31    foreign key (group_id) references sportgroups(id)
32);
33
34
35INSERT INTO `school_sport_clubs`.`coach_work` (`coach_id`, `group_id`, `number_of_hours`, `date`) VALUES ('1', '1', '2', '2016-03-07 08:45:55');
36INSERT INTO `school_sport_clubs`.`coach_work` (`coach_id`, `group_id`, `number_of_hours`, `date`) VALUES ('1', '1', '2', '2016-03-14 08:45:55');
37INSERT INTO `school_sport_clubs`.`coach_work` (`coach_id`, `group_id`, `number_of_hours`, `date`) VALUES ('1', '1', '2', '2016-03-21 08:45:55');
38INSERT INTO `school_sport_clubs`.`coach_work` (`coach_id`, `group_id`, `number_of_hours`, `date`) VALUES ('1', '1', '2', '2016-03-28 08:45:55');
39INSERT INTO `school_sport_clubs`.`coach_work` (`coach_id`, `group_id`, `number_of_hours`, `date`) VALUES ('1', '1', '2', '2016-04-04 08:45:55');
40INSERT INTO `school_sport_clubs`.`coach_work` (`coach_id`, `group_id`, `number_of_hours`, `date`) VALUES ('1', '1', '2', '2016-04-11 08:45:55');
41INSERT INTO `school_sport_clubs`.`coach_work` (`coach_id`, `group_id`, `number_of_hours`, `date`) VALUES ('2', '2', '2', '2016-03-07 08:45:55');
42INSERT INTO `school_sport_clubs`.`coach_work` (`coach_id`, `group_id`, `number_of_hours`, `date`) VALUES ('2', '2', '2', '2016-03-14 08:45:55');
43INSERT INTO `school_sport_clubs`.`coach_work` (`coach_id`, `group_id`, `number_of_hours`, `date`) VALUES ('2', '2', '2', '2016-03-21 08:45:55');
44INSERT INTO `school_sport_clubs`.`coach_work` (`coach_id`, `group_id`, `number_of_hours`, `date`) VALUES ('2', '2', '2', '2016-03-28 08:45:55');
45INSERT INTO `school_sport_clubs`.`coach_work` (`coach_id`, `group_id`, `number_of_hours`, `date`) VALUES ('2', '2', '2', '2016-04-04 08:45:55');
46INSERT INTO `school_sport_clubs`.`coach_work` (`coach_id`, `group_id`, `number_of_hours`, `date`) VALUES ('2', '2', '2', '2016-04-11 08:45:55');
47INSERT INTO `school_sport_clubs`.`coach_work` (`coach_id`, `group_id`, `number_of_hours`, `date`) VALUES ('2', '3', '2', '2016-04-02 08:45:55');
48INSERT INTO `school_sport_clubs`.`coach_work` (`coach_id`, `group_id`, `number_of_hours`, `date`) VALUES ('2', '3', '2', '2016-04-09 08:45:55');
49
50
51
52ALTER TABLE coach_work
53
54ADD isPayed BOOLEAN NOT NULL DEFAULT 0;