· 8 years ago · Apr 14, 2018, 08:16 AM
1CREATE DATABASE SCHOOL_SPORT_CLUBS; USE
2 SCHOOL_SPORT_CLUBS;
3CREATE TABLE students(
4 id INT AUTO_INCREMENT PRIMARY KEY,
5 NAME VARCHAR(255) NOT NULL,
6 egn VARCHAR(10) NOT NULL UNIQUE,
7 address VARCHAR(255) NOT NULL,
8 phone VARCHAR(20) NULL DEFAULT NULL,
9 class VARCHAR(10) NULL DEFAULT NULL
10); CREATE TABLE sportGroups(
11 id INT AUTO_INCREMENT PRIMARY KEY,
12 location VARCHAR(255) NOT NULL,
13 DAYOFWEEK ENUM(
14 'Monday',
15 'Tuesday',
16 'Wednesday',
17 'Thursday',
18 'Friday',
19 'Saturday',
20 'Sunday'
21 ),
22 hourOfTraining TIME NOT NULL,
23 UNIQUE KEY(
24 location,
25 DAYOFWEEK,
26 hourOfTraining
27 )
28); CREATE TABLE student_sport(
29 student_id INT NOT NULL,
30 CONSTRAINT FOREIGN KEY(student_id) REFERENCES students(id),
31 sportGroup_id INT NOT NULL,
32 CONSTRAINT FOREIGN KEY(sportGroup_id) REFERENCES sportGroups(id),
33 PRIMARY KEY(student_id, sportGroup_id)
34); CREATE TABLE sports(
35 id INT AUTO_INCREMENT PRIMARY KEY,
36 NAME VARCHAR(255) NOT NULL
37); ALTER TABLE
38 sportGroups ADD sport_id INT NOT NULL;
39ALTER TABLE
40 sportGroups ADD CONSTRAINT FOREIGN KEY(sport_id) REFERENCES sports(id);
41CREATE TABLE coaches(
42 id INT AUTO_INCREMENT PRIMARY KEY,
43 NAME VARCHAR(255) NOT NULL,
44 egn VARCHAR(10) NOT NULL UNIQUE
45); ALTER TABLE
46 sportGroups ADD coach_id INT;
47ALTER TABLE
48 sportGroups ADD CONSTRAINT FOREIGN KEY(coach_id) REFERENCES coaches(id);
49USE
50 school_sport_clubs;
51DROP TABLE IF EXISTS
52 taxesPayments;
53CREATE TABLE taxesPayments(
54 id INT AUTO_INCREMENT PRIMARY KEY,
55 student_id INT NOT NULL,
56 group_id INT NOT NULL,
57 paymentAmount DOUBLE NOT NULL,
58 MONTH TINYINT,
59 YEAR YEAR,
60 dateOfPayment DATETIME NOT NULL,
61 CONSTRAINT FOREIGN KEY(student_id) REFERENCES students(id),
62 CONSTRAINT FOREIGN KEY(group_id) REFERENCES sportgroups(id)
63); DROP TABLE IF EXISTS
64 salaryPayments;
65CREATE TABLE salaryPayments(
66 id INT AUTO_INCREMENT PRIMARY KEY,
67 coach_id INT NOT NULL,
68 MONTH TINYINT,
69 YEAR YEAR,
70 salaryAmount DOUBLE,
71 dateOfPayment DATETIME NOT NULL,
72 CONSTRAINT FOREIGN KEY(coach_id) REFERENCES coaches(id),
73 UNIQUE KEY(`coach_id`, `month`, `year`)
74); INSERT INTO sports(`name`)
75VALUES('Football');
76INSERT INTO sports(`name`)
77VALUES('Volleyball');
78INSERT INTO sports(NAME)
79VALUES(BASKETBALL);
80INSERT INTO `coaches`(`name`, `egn`)
81VALUES('Иван Петков', '7509041245'),(
82 'Георги Иванов Тодоров',
83 '8010091245'
84),(
85 'ИлиÑн Тодоров Георгиев',
86 '8407106352'
87),(
88 'ИВÐРТОДОРОВ ПЕТРОВ',
89 '7869854750'
90);
91INSERT INTO students(
92 NAME,
93 egn,
94 address,
95 phone,
96 class
97)
98VALUES(
99 'Iliyan Ivanov',
100 '9401150045',
101 'Sofia-Mladost 1',
102 '0893452120',
103 '10'
104),(
105 'Ivan Iliev Georgiev',
106 '9510104512',
107 'Sofia-Liylin',
108 '0894123456',
109 '11'
110),(
111 'Elena Petrova Petrova',
112 '9505052154',
113 'Sofia-Mladost 3',
114 '0897852412',
115 '11'
116),(
117 'Ivan Iliev Iliev',
118 '9510104542',
119 'Sofia-Mladost 3',
120 '0894123457',
121 '11'
122),(
123 'Maria Hristova Dimova',
124 '9510104547',
125 'Sofia-Mladost 4',
126 '0894123442',
127 '11'
128),(
129 'Antoaneta Ivanova Georgieva',
130 '9411104547',
131 'Sofia-Krasno selo',
132 '0874526235',
133 '10'
134);
135INSERT INTO school_sport_clubs.`student_sport`(student_id, sportGroup_id)
136VALUES(1, 1),(2, 1),(3, 1),(4, 2),(5, 2),(6, 2);
137TRUNCATE TABLE
138 `school_sport_clubs`.`taxespayments`;
139 /**2015**/
140INSERT INTO `school_sport_clubs`.`taxespayments`(
141 `student_id`,
142 `group_id`,
143 `paymentAmount`,
144 `month`,
145 `year`,
146 `dateOfPayment`
147)
148VALUES('1', '1', '200', '1', 2015, NOW()),('1', '1', '200', '2', 2015, NOW()),('1', '1', '200', '3', 2015, NOW()),('1', '1', '200', '4', 2015, NOW()),('1', '1', '200', '5', 2015, NOW()),('1', '1', '200', '6', 2015, NOW()),('1', '1', '200', '7', 2015, NOW()),('1', '1', '200', '8', 2015, NOW()),('1', '1', '200', '9', 2015, NOW()),('1', '1', '200', '10', 2015, NOW()),('1', '1', '200', '11', 2015, NOW()),('1', '1', '200', '12', 2015, NOW()),('2', '1', '250', '1', 2015, NOW()),('2', '1', '250', '2', 2015, NOW()),('2', '1', '250', '3', 2015, NOW()),('2', '1', '250', '4', 2015, NOW()),('2', '1', '250', '5', 2015, NOW()),('2', '1', '250', '6', 2015, NOW()),('2', '1', '250', '7', 2015, NOW()),('2', '1', '250', '8', 2015, NOW()),('2', '1', '250', '9', 2015, NOW()),('2', '1', '250', '10', 2015, NOW()),('2', '1', '250', '11', 2015, NOW()),('2', '1', '250', '12', 2015, NOW()),('3', '1', '250', '1', 2015, NOW()),('3', '1', '250', '2', 2015, NOW()),('3', '1', '250', '3', 2015, NOW()),('3', '1', '250', '4', 2015, NOW()),('3', '1', '250', '5', 2015, NOW()),('3', '1', '250', '6', 2015, NOW()),('3', '1', '250', '7', 2015, NOW()),('3', '1', '250', '8', 2015, NOW()),('3', '1', '250', '9', 2015, NOW()),('3', '1', '250', '10', 2015, NOW()),('3', '1', '250', '11', 2015, NOW()),('3', '1', '250', '12', 2015, NOW()),('1', '2', '200', '1', 2015, NOW()),('1', '2', '200', '2', 2015, NOW()),('1', '2', '200', '3', 2015, NOW()),('1', '2', '200', '4', 2015, NOW()),('1', '2', '200', '5', 2015, NOW()),('1', '2', '200', '6', 2015, NOW()),('1', '2', '200', '7', 2015, NOW()),('1', '2', '200', '8', 2015, NOW()),('1', '2', '200', '9', 2015, NOW()),('1', '2', '200', '10', 2015, NOW()),('1', '2', '200', '11', 2015, NOW()),('1', '2', '200', '12', 2015, NOW()),('4', '2', '200', '1', 2015, NOW()),('4', '2', '200', '2', 2015, NOW()),('4', '2', '200', '3', 2015, NOW()),('4', '2', '200', '4', 2015, NOW()),('4', '2', '200', '5', 2015, NOW()),('4', '2', '200', '6', 2015, NOW()),('4', '2', '200', '7', 2015, NOW()),('4', '2', '200', '8', 2015, NOW()),('4', '2', '200', '9', 2015, NOW()),('4', '2', '200', '10', 2015, NOW()),('4', '2', '200', '11', 2015, NOW()),('4', '2', '200', '12', 2015, NOW()),
149/**2014**/
150('1', '1', '200', '1', 2014, NOW()),('1', '1', '200', '2', 2014, NOW()),('1', '1', '200', '3', 2014, NOW()),('1', '1', '200', '4', 2014, NOW()),('1', '1', '200', '5', 2014, NOW()),('1', '1', '200', '6', 2014, NOW()),('1', '1', '200', '7', 2014, NOW()),('1', '1', '200', '8', 2014, NOW()),('1', '1', '200', '9', 2014, NOW()),('1', '1', '200', '10', 2014, NOW()),('1', '1', '200', '11', 2014, NOW()),('1', '1', '200', '12', 2014, NOW()),('2', '1', '250', '1', 2014, NOW()),('2', '1', '250', '2', 2014, NOW()),('2', '1', '250', '3', 2014, NOW()),('2', '1', '250', '4', 2014, NOW()),('2', '1', '250', '5', 2014, NOW()),('2', '1', '250', '6', 2014, NOW()),('2', '1', '250', '7', 2014, NOW()),('2', '1', '250', '8', 2014, NOW()),('2', '1', '250', '9', 2014, NOW()),('2', '1', '250', '10', 2014, NOW()),('2', '1', '250', '11', 2014, NOW()),('2', '1', '250', '12', 2014, NOW()),('3', '1', '250', '1', 2014, NOW()),('3', '1', '250', '2', 2014, NOW()),('3', '1', '250', '3', 2014, NOW()),('3', '1', '250', '4', 2014, NOW()),('3', '1', '250', '5', 2014, NOW()),('3', '1', '250', '6', 2014, NOW()),('3', '1', '250', '7', 2014, NOW()),('3', '1', '250', '8', 2014, NOW()),('3', '1', '250', '9', 2014, NOW()),('3', '1', '250', '10', 2014, NOW()),('3', '1', '250', '11', 2014, NOW()),('3', '1', '250', '12', 2014, NOW()),('1', '2', '200', '1', 2014, NOW()),('1', '2', '200', '2', 2014, NOW()),('1', '2', '200', '3', 2014, NOW()),('1', '2', '200', '4', 2014, NOW()),('1', '2', '200', '5', 2014, NOW()),('1', '2', '200', '6', 2014, NOW()),('1', '2', '200', '7', 2014, NOW()),('1', '2', '200', '8', 2014, NOW()),('1', '2', '200', '9', 2014, NOW()),('1', '2', '200', '10', 2014, NOW()),('1', '2', '200', '11', 2014, NOW()),('1', '2', '200', '12', 2014, NOW()),('4', '2', '200', '1', 2014, NOW()),('4', '2', '200', '2', 2014, NOW()),('4', '2', '200', '3', 2014, NOW()),('4', '2', '200', '4', 2014, NOW()),('4', '2', '200', '5', 2014, NOW()),('4', '2', '200', '6', 2014, NOW()),('4', '2', '200', '7', 2014, NOW()),('4', '2', '200', '8', 2014, NOW()),('4', '2', '200', '9', 2014, NOW()),('4', '2', '200', '10', 2014, NOW()),('4', '2', '200', '11', 2014, NOW()),('4', '2', '200', '12', 2014, NOW()),
151/**2016**/
152('1', '1', '200', '1', 2016, NOW()),('1', '1', '200', '2', 2016, NOW()),('1', '1', '200', '3', 2016, NOW()),('2', '1', '250', '1', 2016, NOW()),('3', '1', '250', '1', 2016, NOW()),('3', '1', '250', '2', 2016, NOW()),('1', '2', '200', '1', 2016, NOW()),('1', '2', '200', '2', 2016, NOW()),('1', '2', '200', '3', 2016, NOW()),('4', '2', '200', '1', 2016, NOW()),('4', '2', '200', '2', 2016, NOW());