API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Mar 20th, 2019
113
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 17.23 KB | None | 0 0
raw download clone embed print report
  1. DROP DATABASE IF EXISTS school_sport_clubs;
  2. CREATE DATABASE school_sport_clubs;
  3. USE school_sport_clubs;
  4.  
  5. CREATE TABLE school_sport_clubs.sports (
  6. id INT AUTO_INCREMENT PRIMARY KEY,
  7. name VARCHAR(255) NOT NULL
  8. );
  9.  
  10. CREATE TABLE school_sport_clubs.coaches (
  11. id INT AUTO_INCREMENT PRIMARY KEY,
  12. name VARCHAR(255) NOT NULL,
  13. egn VARCHAR(10) NOT NULL UNIQUE
  14. );
  15.  
  16. CREATE TABLE school_sport_clubs.students (
  17. id INT AUTO_INCREMENT PRIMARY KEY,
  18. name VARCHAR(255) NOT NULL,
  19. egn VARCHAR(10) NOT NULL UNIQUE,
  20. address VARCHAR(255) NOT NULL,
  21. phone VARCHAR(20) NULL DEFAULT NULL,
  22. class VARCHAR(10) NULL DEFAULT NULL
  23. );
  24.  
  25. CREATE TABLE school_sport_clubs.sportGroups (
  26. id INT AUTO_INCREMENT PRIMARY KEY,
  27. location VARCHAR(255) NOT NULL,
  28. dayOfWeek ENUM('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'),
  29. hourOfTraining TIME NOT NULL,
  30. sport_id INT NOT NULL,
  31. coach_id INT NOT NULL,
  32. UNIQUE KEY (location , dayOfWeek , hourOfTraining),
  33. CONSTRAINT FOREIGN KEY (sport_id)
  34. REFERENCES sports (id),
  35. CONSTRAINT FOREIGN KEY (coach_id)
  36. REFERENCES coaches (id)
  37. );
  38.  
  39. CREATE TABLE school_sport_clubs.student_sport (
  40. student_id INT NOT NULL,
  41. sportGroup_id INT NOT NULL,
  42. CONSTRAINT FOREIGN KEY (student_id)
  43. REFERENCES students (id),
  44. CONSTRAINT FOREIGN KEY (sportGroup_id)
  45. REFERENCES sportGroups (id),
  46. PRIMARY KEY (student_id , sportGroup_id)
  47. );
  48.  
  49. CREATE TABLE taxesPayments (
  50. id INT AUTO_INCREMENT PRIMARY KEY,
  51. student_id INT NOT NULL,
  52. group_id INT NOT NULL,
  53. paymentAmount DOUBLE NOT NULL,
  54. month TINYINT,
  55. year YEAR,
  56. dateOfPayment DATETIME NOT NULL,
  57. CONSTRAINT FOREIGN KEY (student_id)
  58. REFERENCES students (id),
  59. CONSTRAINT FOREIGN KEY (group_id)
  60. REFERENCES sportgroups (id)
  61. );
  62.  
  63. CREATE TABLE salaryPayments (
  64. id INT AUTO_INCREMENT PRIMARY KEY,
  65. coach_id INT NOT NULL,
  66. month TINYINT,
  67. year YEAR,
  68. salaryAmount DOUBLE,
  69. dateOfPayment DATETIME NOT NULL,
  70. CONSTRAINT FOREIGN KEY (coach_id)
  71. REFERENCES coaches (id),
  72. UNIQUE KEY (coach_id , `month` , `year`)
  73. );
  74.  
  75. INSERT INTO sports
  76. VALUES (NULL, 'Football') ,
  77. (NULL, 'Volleyball'),
  78. (NULL, 'Tennis');
  79.  
  80. INSERT INTO coaches
  81. VALUES (NULL, 'Ivan Todorov Petkov', '7509041245') ,
  82. (NULL, 'georgi Ivanov Todorov', '8010091245') ,
  83. (NULL, 'Ilian Todorov Georgiev', '8407106352') ,
  84. (NULL, 'Petar Slavkov Yordanov', '7010102045') ,
  85. (NULL, 'Todor Ivanov Ivanov', '8302160980') ,
  86. (NULL, 'Slavi Petkov Petkov', '7106041278');
  87.  
  88. INSERT INTO students (name, egn, address, phone, class)
  89. VALUES ('Iliyan Ivanov', '9401150045', 'Sofia-Mladost 1', '0893452120', '10') ,
  90. ('Ivan Iliev Georgiev', '9510104512', 'Sofia-Liylin', '0894123456', '11') ,
  91. ('Elena Petrova Petrova', '9505052154', 'Sofia-Mladost 3', '0897852412', '11') ,
  92. ('Ivan Iliev Iliev', '9510104542', 'Sofia-Mladost 3', '0894123457', '11') ,
  93. ('Maria Hristova Dimova', '9510104547', 'Sofia-Mladost 4', '0894123442', '11') ,
  94. ('Antoaneta Ivanova Georgieva', '9411104547', 'Sofia-Krasno selo', '0874526235', '10');
  95.  
  96. INSERT INTO sportGroups
  97. VALUES (NULL, 'Sofia-Mladost 1', 'Monday', '08:00:00', 1, 1 ) ,
  98. (NULL, 'Sofia-Mladost 1', 'Monday', '09:30:00', 1, 2 ) ,
  99. (NULL, 'Sofia-Liylin 7', 'Sunday', '08:00:00', 2, 1) ,
  100. (NULL, 'Sofia-Liylin 7', 'Sunday', '09:30:00', 2, 2) ,
  101. (NULL, 'Plovdiv', 'Monday', '12:00:00', '1', '1');
  102.  
  103. INSERT INTO student_sport
  104. VALUES (1, 1),
  105. (2, 1),
  106. (3, 1),
  107. (4, 2),
  108. (5, 2),
  109. (6, 2),
  110. (1, 3),
  111. (2, 3),
  112. (3, 3);
  113.  
  114. INSERT INTO school_sport_clubs.`taxespayments`
  115. VALUES (NULL, '1', '1', '200', '1', 2015, now()),
  116. (NULL, '1', '1', '200', '2', 2015, now()),
  117. (NULL, '1', '1', '200', '3', 2015, now()),
  118. (NULL, '1', '1', '200', '4', 2015, now()),
  119. (NULL, '1', '1', '200', '5', 2015, now()),
  120. (NULL, '1', '1', '200', '6', 2015, now()),
  121. (NULL, '1', '1', '200', '7', 2015, now()),
  122. (NULL, '1', '1', '200', '8', 2015, now()),
  123. (NULL, '1', '1', '200', '9', 2015, now()),
  124. (NULL, '1', '1', '200', '10', 2015, now()),
  125. (NULL, '1', '1', '200', '11', 2015, now()),
  126. (NULL, '1', '1', '200', '12', 2015, now()),
  127. (NULL, '2', '1', '250', '1', 2015, now()),
  128. (NULL, '2', '1', '250', '2', 2015, now()),
  129. (NULL, '2', '1', '250', '3', 2015, now()),
  130. (NULL, '2', '1', '250', '4', 2015, now()),
  131. (NULL, '2', '1', '250', '5', 2015, now()),
  132. (NULL, '2', '1', '250', '6', 2015, now()),
  133. (NULL, '2', '1', '250', '7', 2015, now()),
  134. (NULL, '2', '1', '250', '8', 2015, now()),
  135. (NULL, '2', '1', '250', '9', 2015, now()),
  136. (NULL, '2', '1', '250', '10', 2015, now()),
  137. (NULL, '2', '1', '250', '11', 2015, now()),
  138. (NULL, '2', '1', '250', '12', 2015, now()),
  139. (NULL, '3', '1', '250', '1', 2015, now()),
  140. (NULL, '3', '1', '250', '2', 2015, now()),
  141. (NULL, '3', '1', '250', '3', 2015, now()),
  142. (NULL, '3', '1', '250', '4', 2015, now()),
  143. (NULL, '3', '1', '250', '5', 2015, now()),
  144. (NULL, '3', '1', '250', '6', 2015, now()),
  145. (NULL, '3', '1', '250', '7', 2015, now()),
  146. (NULL, '3', '1', '250', '8', 2015, now()),
  147. (NULL, '3', '1', '250', '9', 2015, now()),
  148. (NULL, '3', '1', '250', '10', 2015, now()),
  149. (NULL, '3', '1', '250', '11', 2015, now()),
  150. (NULL, '3', '1', '250', '12', 2015, now()),
  151. (NULL, '1', '2', '200', '1', 2015, now()),
  152. (NULL, '1', '2', '200', '2', 2015, now()),
  153. (NULL, '1', '2', '200', '3', 2015, now()),
  154. (NULL, '1', '2', '200', '4', 2015, now()),
  155. (NULL, '1', '2', '200', '5', 2015, now()),
  156. (NULL, '1', '2', '200', '6', 2015, now()),
  157. (NULL, '1', '2', '200', '7', 2015, now()),
  158. (NULL, '1', '2', '200', '8', 2015, now()),
  159. (NULL, '1', '2', '200', '9', 2015, now()),
  160. (NULL, '1', '2', '200', '10', 2015, now()),
  161. (NULL, '1', '2', '200', '11', 2015, now()),
  162. (NULL, '1', '2', '200', '12', 2015, now()),
  163. (NULL, '4', '2', '200', '1', 2015, now()),
  164. (NULL, '4', '2', '200', '2', 2015, now()),
  165. (NULL, '4', '2', '200', '3', 2015, now()),
  166. (NULL, '4', '2', '200', '4', 2015, now()),
  167. (NULL, '4', '2', '200', '5', 2015, now()),
  168. (NULL, '4', '2', '200', '6', 2015, now()),
  169. (NULL, '4', '2', '200', '7', 2015, now()),
  170. (NULL, '4', '2', '200', '8', 2015, now()),
  171. (NULL, '4', '2', '200', '9', 2015, now()),
  172. (NULL, '4', '2', '200', '10', 2015, now()),
  173. (NULL, '4', '2', '200', '11', 2015, now()),
  174. (NULL, '4', '2', '200', '12', 2015, now()),
  175. /**2014**/
  176. (NULL, '1', '1', '200', '1', 2014, now()),
  177. (NULL, '1', '1', '200', '2', 2014, now()),
  178. (NULL, '1', '1', '200', '3', 2014, now()),
  179. (NULL, '1', '1', '200', '4', 2014, now()),
  180. (NULL, '1', '1', '200', '5', 2014, now()),
  181. (NULL, '1', '1', '200', '6', 2014, now()),
  182. (NULL, '1', '1', '200', '7', 2014, now()),
  183. (NULL, '1', '1', '200', '8', 2014, now()),
  184. (NULL, '1', '1', '200', '9', 2014, now()),
  185. (NULL, '1', '1', '200', '10', 2014, now()),
  186. (NULL, '1', '1', '200', '11', 2014, now()),
  187. (NULL, '1', '1', '200', '12', 2014, now()),
  188. (NULL, '2', '1', '250', '1', 2014, now()),
  189. (NULL, '2', '1', '250', '2', 2014, now()),
  190. (NULL, '2', '1', '250', '3', 2014, now()),
  191. (NULL, '2', '1', '250', '4', 2014, now()),
  192. (NULL, '2', '1', '250', '5', 2014, now()),
  193. (NULL, '2', '1', '250', '6', 2014, now()),
  194. (NULL, '2', '1', '250', '7', 2014, now()),
  195. (NULL, '2', '1', '250', '8', 2014, now()),
  196. (NULL, '2', '1', '250', '9', 2014, now()),
  197. (NULL, '2', '1', '250', '10', 2014, now()),
  198. (NULL, '2', '1', '250', '11', 2014, now()),
  199. (NULL, '2', '1', '250', '12', 2014, now()),
  200. (NULL, '3', '1', '250', '1', 2014, now()),
  201. (NULL, '3', '1', '250', '2', 2014, now()),
  202. (NULL, '3', '1', '250', '3', 2014, now()),
  203. (NULL, '3', '1', '250', '4', 2014, now()),
  204. (NULL, '3', '1', '250', '5', 2014, now()),
  205. (NULL, '3', '1', '250', '6', 2014, now()),
  206. (NULL, '3', '1', '250', '7', 2014, now()),
  207. (NULL, '3', '1', '250', '8', 2014, now()),
  208. (NULL, '3', '1', '250', '9', 2014, now()),
  209. (NULL, '3', '1', '250', '10', 2014, now()),
  210. (NULL, '3', '1', '250', '11', 2014, now()),
  211. (NULL, '3', '1', '250', '12', 2014, now()),
  212. (NULL, '1', '2', '200', '1', 2014, now()),
  213. (NULL, '1', '2', '200', '2', 2014, now()),
  214. (NULL, '1', '2', '200', '3', 2014, now()),
  215. (NULL, '1', '2', '200', '4', 2014, now()),
  216. (NULL, '1', '2', '200', '5', 2014, now()),
  217. (NULL, '1', '2', '200', '6', 2014, now()),
  218. (NULL, '1', '2', '200', '7', 2014, now()),
  219. (NULL, '1', '2', '200', '8', 2014, now()),
  220. (NULL, '1', '2', '200', '9', 2014, now()),
  221. (NULL, '1', '2', '200', '10', 2014, now()),
  222. (NULL, '1', '2', '200', '11', 2014, now()),
  223. (NULL, '1', '2', '200', '12', 2014, now()),
  224. (NULL, '4', '2', '200', '1', 2014, now()),
  225. (NULL, '4', '2', '200', '2', 2014, now()),
  226. (NULL, '4', '2', '200', '3', 2014, now()),
  227. (NULL, '4', '2', '200', '4', 2014, now()),
  228. (NULL, '4', '2', '200', '5', 2014, now()),
  229. (NULL, '4', '2', '200', '6', 2014, now()),
  230. (NULL, '4', '2', '200', '7', 2014, now()),
  231. (NULL, '4', '2', '200', '8', 2014, now()),
  232. (NULL, '4', '2', '200', '9', 2014, now()),
  233. (NULL, '4', '2', '200', '10', 2014, now()),
  234. (NULL, '4', '2', '200', '11', 2014, now()),
  235. (NULL, '4', '2', '200', '12', 2014, now()),
  236. /**2016**/
  237. (NULL, '1', '1', '200', '1', 2016, now()),
  238. (NULL, '1', '1', '200', '2', 2016, now()),
  239. (NULL, '1', '1', '200', '3', 2016, now()),
  240. (NULL, '2', '1', '250', '1', 2016, now()),
  241. (NULL, '3', '1', '250', '1', 2016, now()),
  242. (NULL, '3', '1', '250', '2', 2016, now()),
  243. (NULL, '1', '2', '200', '1', 2016, now()),
  244. (NULL, '1', '2', '200', '2', 2016, now()),
  245. (NULL, '1', '2', '200', '3', 2016, now()),
  246. (NULL, '4', '2', '200', '1', 2016, now()),
  247. (NULL, '4', '2', '200', '2', 2016, now());
  248.  
  249.  
  250.  
  251.  
  252. SELECT
  253. st.name, sp.name
  254. FROM
  255. students AS st
  256. JOIN
  257. sports AS sp ON st.id IN (SELECT
  258. ss.student_id
  259. FROM
  260. student_sport AS ss
  261. WHERE
  262. ss.sportGroup_id IN (SELECT
  263. sg.id
  264. FROM
  265. sportgroups AS sg
  266. WHERE
  267. sg.sport_id = sp.id));
  268.  
  269. SELECT
  270. students.name, sports.name
  271. FROM
  272. students
  273. JOIN
  274. student_sport AS ss ON students.id = ss.student_id
  275. JOIN
  276. sportgroups ON sportgroups.id = ss.sportGroup_id
  277. JOIN
  278. sports ON sports.id = sportgroups.sport_id;
  279.  
  280.  
  281. SELECT
  282. st.name, c.name, c.egn
  283. FROM
  284. students AS st
  285. JOIN
  286. coaches AS c ON st.id IN (SELECT
  287. tax.student_id
  288. FROM
  289. taxespayments AS tax
  290. WHERE
  291. tax.group_id IN (SELECT
  292. sg.id
  293. FROM
  294. sportgroups AS sg
  295. WHERE
  296. sg.coach_id = c.id));
  297.  
  298.  
  299. SELECT DISTINCT
  300. st.name, SUM(tax.paymentAmount)
  301. FROM
  302. students AS st
  303. JOIN
  304. taxespayments AS tax ON st.id = tax.student_id
  305. WHERE
  306. st.id = 1
  307. ORDER BY tax.student_id;
  308.  
  309. SELECT
  310. s.name, SUM(paymentAmount)
  311. FROM
  312. sports AS s
  313. JOIN
  314. taxespayments AS tp ON s.id IN (SELECT
  315. sg.sport_id
  316. FROM
  317. sportgroups AS sg
  318. WHERE
  319. sg.id = tp.group_id);
  320.  
  321.  
  322.  
  323.  
  324.  
  325.  
  326.  
  327.  
  328.  
  329.  
  330.  
  331.  
  332.  
  333.  
  334.  
  335.  
  336.  
  337.  
  338.  
  339. SELECT
  340. st1.name AS Student1, st2.name AS Student2, sp.name AS Sport
  341. FROM
  342. students AS st1
  343. JOIN
  344. students AS st2 ON st1.id > st2.id
  345. JOIN
  346. sports AS sp ON (st1.id IN (SELECT
  347. ss.student_id
  348. FROM
  349. student_sport AS ss
  350. WHERE
  351. ss.sportGroup_id IN (SELECT
  352. sg.id
  353. FROM
  354. sportgroups AS sg
  355. WHERE
  356. sg.sport_id = sp.id))
  357. AND (st2.id IN (SELECT
  358. ss.student_id
  359. FROM
  360. student_sport AS ss
  361. WHERE
  362. ss.sportGroup_id IN (SELECT
  363. sg.id
  364. FROM
  365. sportgroups AS sg
  366. WHERE
  367. sg.sport_id = sp.id))))
  368. WHERE
  369. st1.id IN (SELECT
  370. student_id
  371. FROM
  372. student_sport
  373. WHERE
  374. sportGroup_id IN (SELECT
  375. sportGroup_id
  376. FROM
  377. student_sport
  378. WHERE
  379. student_id = st2.id))
  380. ORDER BY Sport;
  381.  
  382.  
  383.  
  384. SELECT
  385. st1.name AS Student1, st2.name AS Student2, sp.name AS Sport
  386. FROM
  387. students AS st1
  388. JOIN
  389. students AS st2 ON st1.id > st2.id
  390. JOIN
  391. sports AS sp ON (st1.id IN (SELECT
  392. ss.student_id
  393. FROM
  394. student_sport AS ss
  395. WHERE
  396. ss.sportGroup_id IN (SELECT
  397. sg.id
  398. FROM
  399. sportgroups AS sg
  400. WHERE
  401. sg.sport_id = sp.id))
  402. AND (st2.id IN (SELECT
  403. ss.student_id
  404. FROM
  405. student_sport AS ss
  406. WHERE
  407. ss.sportGroup_id IN (SELECT
  408. sg.id
  409. FROM
  410. sportgroups AS sg
  411. WHERE
  412. sg.sport_id = sp.id))));
  413.  
  414.  
  415. SELECT
  416. st.name, sp.name
  417. FROM
  418. students AS st
  419. JOIN
  420. sports AS sp ON st.id IN (SELECT
  421. ss.student_id
  422. FROM
  423. student_sport AS ss
  424. WHERE
  425. ss.sportGroup_id IN (SELECT
  426. sg.id
  427. FROM
  428. sportgroups AS sg
  429. WHERE
  430. sg.sport_id = sp.id));
  431.  
  432. SELECT
  433. sg.location, sp.name
  434. FROM
  435. sportgroups AS sg
  436. LEFT JOIN
  437. sports AS sp ON sg.sport_id = sp.id
  438. UNION (SELECT
  439. sg.location, sp.name
  440. FROM
  441. sportgroups AS sg
  442. RIGHT JOIN
  443. sports AS sp ON sg.sport_id = sp.id);
  444.  
  445.  
  446.  
  447.  
  448. SELECT
  449. students.name, sports.name, coaches.name
  450. FROM
  451. students
  452. JOIN
  453. sports ON students.id IN (SELECT
  454. student_sport.student_id
  455. FROM
  456. student_sport
  457. WHERE
  458. student_sport.sportGroup_id IN (SELECT
  459. sportgroups.id
  460. FROM
  461. sportgroups
  462. WHERE
  463. sportgroups.sport_id = sports.id))
  464. JOIN
  465. coaches ON coaches.id = students.id
  466. WHERE
  467. coaches.name LIKE '%Ivan%'
  468. AND students.class = '11';
  469.  
  470. SELECT
  471. students.name, sports.name
  472. FROM
  473. students
  474. JOIN
  475. sports ON students.id IN (SELECT
  476. student_sport.student_id
  477. FROM
  478. student_sport
  479. WHERE
  480. student_sport.sportGroup_id IN (SELECT
  481. sportgroups.id
  482. FROM
  483. sportgroups
  484. WHERE
  485. sportgroups.sport_id = sports.id))
  486. WHERE
  487. students.class = 11
  488. AND sports.name = 'Football';
  489.  
  490. SELECT
  491. st.name, sp.name, tax.year
  492. FROM
  493. students AS st
  494. JOIN
  495. student_sport AS ss ON st.id = ss.student_id
  496. JOIN
  497. sportgroups AS sg ON sg.id = ss.sportGroup_id
  498. JOIN
  499. sports AS sp ON sp.id = sg.sport_id
  500. JOIN
  501. taxespayments AS tax ON st.id = tax.student_id
  502. WHERE
  503. st.class = '11' AND sp.name = 'Football';
  504.  
  505.  
  506.  
  507. SELECT
  508. st.name, sg.hourOfTraining
  509. FROM
  510. students AS st
  511. JOIN
  512. sportgroups AS sg ON st.id IN (SELECT
  513. tax.student_id
  514. FROM
  515. taxespayments AS tax
  516. WHERE
  517. tax.group_id = sg.id);
  518.  
  519.  
  520.  
  521. SELECT
  522. c.name, sp.name
  523. FROM
  524. coaches AS c
  525. JOIN
  526. sports AS sp ON c.id IN (SELECT
  527. sg.coach_id
  528. FROM
  529. sportgroups AS sg
  530. WHERE
  531. sg.sport_id = sp.id);
  532.  
  533.  
  534.  
  535. SELECT
  536. st.name, st.address, SUM(paymentAmount)
  537. FROM
  538. students AS st
  539. JOIN
  540. taxespayments AS tax ON st.id = tax.student_id
  541. WHERE
  542. st.id = 1 OR st.id = 2 OR st.id = 3
  543. OR st.id = 4
  544. OR st.id = 5
  545. OR st.id = 6
  546. GROUP BY paymentAmount
  547. LIMIT 6;
  548.  
  549.  
  550. SELECT
  551. group_id, SUM(paymentAmount)
  552. FROM
  553. taxespayments
  554. GROUP BY SUM(paymentAmount);
  555.  
  556. UPDATE taxespayments
  557. SET
  558. group_id = '3'
  559. WHERE
  560. id = 15;
  561.  
  562.  
  563.  
  564.  
  565. SELECT
  566. st.name, sp.name
  567. FROM
  568. students AS st
  569. JOIN
  570. sports AS sp ON st.id IN (SELECT
  571. ss.student_id
  572. FROM
  573. student_sport AS ss
  574. WHERE
  575. ss.sportGroup_id IN (SELECT
  576. sg.id
  577. FROM
  578. sportgroups AS sg
  579. WHERE
  580. sg.sport_id = sp.id));
  581.  
  582.  
  583. select st.name,sp.name
  584. from students as st
  585. join (sports as sp )
  586. on st.id in(
  587. select tax.student_id
  588. from taxespayments as tax
  589. where tax.group_id in(
  590. select sg.id
  591. from sportgroups as sg
  592. where sg.id=sp.id))
  593. where sp.name like 'football';
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!