· 5 years ago · Feb 02, 2020, 02:44 PM
1use examjulyninth;
2
3#1
4create table cards
5(
6 id int(11) primary key auto_increment,
7 card_number varchar(19) not null,
8 card_status varchar(7) not null,
9 bank_account_id int(11) not null
10);
11
12create table bank_accounts
13(
14 id int(11) primary key auto_increment,
15 account_number varchar(10) not null,
16 balance decimal(10, 2) not null,
17 client_id int(11) not null unique
18);
19
20alter table cards
21 add constraint fk_cards_bank_accounts foreign key cards (bank_account_id)
22 references bank_accounts (id);
23
24create table clients
25(
26 id int(11) primary key auto_increment,
27 full_name varchar(50) not null,
28 age int(11) not null
29);
30
31alter table bank_accounts
32 add constraint fk_bank_accounts_clients
33 foreign key bank_accounts (client_id) references clients (id);
34
35create table employees_clients
36(
37 employee_id int(11) null,
38 client_id int(11) null
39);
40
41alter table employees_clients
42 add constraint fk_employees_clients_clients
43 foreign key employees_clients (client_id) references clients (id);
44
45create table employees
46(
47 id int(11) primary key auto_increment,
48 first_name varchar(20) not null,
49 last_name varchar(20) not null,
50 salary decimal(10, 2) not null,
51 started_on date not null,
52 branch_id int(11) not null
53);
54
55alter table employees_clients
56 add constraint
57 fk_employees_clients_employees foreign key employees_clients (employee_id)
58 references employees (id);
59
60create table branches
61(
62 id int(11) primary key auto_increment,
63 name varchar(30) not null unique
64);
65
66alter table employees
67 add constraint fk_employees_branches
68 foreign key employees (branch_id) references branches (id);
69
70
71#2
72
73insert into cards(card_number, card_status, bank_account_id)
74select reverse(c.full_name), 'Active', c.id
75from cards ca
76 join bank_accounts ba on ca.bank_account_id = ba.id
77 join clients c on ba.client_id = c.id
78where c.id between 191 and 200
79group by ba.client_id;
80
81select c.id, c.full_name cl_name, e2.id, e2.first_name emp_name
82from employees_clients e
83 join clients c on e.client_id = c.id
84 join employees e2 on e.employee_id = e2.id
85where c.id = e2.id;
86
87update employees_clients join
88 (select e.employee_id empid
89 from employees_clients e
90 group by e.employee_id
91 order by count(e.client_id), e.employee_id
92 limit 1) es
93set employee_id = es.empid
94where employee_id = client_id;
95
96
97select e.employee_id
98from employees_clients e
99group by e.employee_id
100order by count(e.client_id), e.employee_id
101limit 1;
102
103
104select e.employee_id, count(e.client_id) as ct
105from employees_clients e
106group by e.employee_id
107order by ct, e.employee_id;
108
109select e.employee_id, count(e.client_id) as ct
110from employees_clients e
111group by e.employee_id
112order by ct desc
113limit 1;
114
115use `21october2018sql`;
116##Database Basics (MySQL) Exam
117# Colonial Journey Management System
118drop table planets, journeys, spaceships,spaceports,travel_cards,colonists;
119create table planets
120(
121 id int primary key auto_increment,
122 name varchar(30) not null
123);
124
125create table spaceports
126(
127 id int primary key auto_increment,
128 name varchar(50) not null,
129 planet_id int
130);
131
132create table spaceships
133(
134 id int primary key auto_increment,
135 name varchar(50) not null,
136 manufacturer varchar(30) not null,
137 light_speed_rate int default 0
138);
139
140create table colonists
141(
142 id int primary key auto_increment,
143 first_name varchar(20) not null,
144 last_name varchar(20) not null,
145 ucn char(10) not null unique,
146 birth_date date not null
147);
148
149create table journeys
150(
151 id int primary key auto_increment,
152 journey_start datetime not null,
153 journey_end datetime not null,
154 purpose enum ('Medical', 'Technical', 'Educational', 'Military'),
155 destination_spaceport_id int,
156 spaceship_id int
157);
158
159create table travel_cards
160(
161 id int primary key auto_increment,
162 card_number char(10) not null unique,
163 job_during_journey enum ('Pilot','Engineer', 'Trooper', 'Cleaner', 'Cook'),
164 colonist_id int,
165 journey_id int
166);
167
168alter table spaceports
169 add constraint fk_spaceports_planets foreign key spaceports (planet_id)
170 references planets (id);
171
172alter table travel_cards
173 add constraint fk_travel_cards_colonists foreign key travel_cards (colonist_id)
174 references colonists (id);
175
176alter table travel_cards
177 add constraint fk_travel_cards_journeys foreign key travel_cards (journey_id)
178 references journeys (id);
179
180alter table journeys
181 add constraint fk_journeys_spaceships foreign key journeys (spaceship_id)
182 references spaceships (id);
183
184
185alter table journeys
186 add constraint fk_journeys_spaceports foreign key journeys (destination_spaceport_id)
187 references spaceports (id);
188
189select id, concat(first_name, ' ', last_name), ucn
190from colonists
191order by first_name, last_name, id;
192
193
194
195select ss.name,
196 ss.manufacturer
197from spaceships ss
198 join journeys j on ss.id = j.spaceship_id
199 join travel_cards tc on j.id = tc.journey_id
200 join colonists c on tc.colonist_id = c.id
201where tc.job_during_journey = 'Pilot'
202 and c.birth_date > ('1989-01-01')
203order by ss.name
204limit 30;
205
206
207select p.name, count(j.id) c
208from planets p
209 join spaceports sp on p.id = sp.planet_id
210 join journeys j on j.destination_spaceport_id = sp.id
211group by p.name
212order by c desc, p.name;
213
214select j.journey_start, j.journey_end
215from journeys j
216group by j.id
217order by max(j.journey_start + j.journey_end);
218
219select j.id,
220 p.name,
221 sp.name,
222 j.purpose,
223 max(datediff(date(j.journey_start), date(j.journey_end))) max
224from planets p
225 join spaceports sp on p.id = sp.planet_id
226 join journeys j on j.destination_spaceport_id = sp.id
227group by j.id
228order by max desc
229limit 5;
230
231
232
233drop procedure udp_modify_spaceship_light_speed_rate;
234create procedure
235 udp_modify_spaceship_light_speed_rate(spaceship_name VARCHAR(50), light_speed_rate_increse INT(11))
236begin
237 start transaction;
238
239 if not exists(select name
240 from spaceships
241 where name = spaceship_name
242 ) then
243 ROLLBACK;
244 SIGNAL SQLSTATE '45000'
245 SET MESSAGE_TEXT = 'Spaceship you are trying to modify does not exists.';
246 end if;
247
248 update spaceships
249 set light_speed_rate = light_speed_rate + light_speed_rate_increse;
250 commit;
251end;
252
253call udp_modify_spaceship_light_speed_rate('Anarchy', 2);
254select locate('Anarchy', name), light_speed_rate
255from spaceships;
256
257delete c
258from colonists c
259 left join travel_cards tc
260 on c.id = tc.colonist_id
261where tc.colonist_id is null;
262
263select *
264from colonists c
265 left join travel_cards tc on c.id = tc.colonist_id
266where tc.colonist_id is null;
267select id
268from colonists
269where id between 95 and 100;
270
271select *
272from travel_cards
273where id > 90;
274
275insert into travel_cards (card_number, job_during_journey, colonist_id, journey_id)
276select if(date('1980-01-01') < c.birth_date,
277 concat(year(c.birth_date), day(c.birth_date), left(c.ucn, 4)),
278 concat(year(c.birth_date), month(c.birth_date), right(c.ucn, 4)))
279 ,
280 (case
281 when c.id % 2 = 0 then 'Pilot'
282 when c.id % 3 = 0 then 'Cook'
283 else 'Engineer'
284 end),
285 c.id,
286 left
287 (c.ucn, 1)
288from colonists c
289where c.id between 96 and 100;
290
291
292update journeys
293set purpose = (case
294 when id % 2 = 0 then 'Medical'
295 when id % 3 = 0 then 'Technical'
296 when id % 5 = 0 then 'Educational'
297 when id % 7 = 0 then 'Military'
298 end)
299where id % 2 = 0
300 or id % 3 = 0
301 or id % 5 = 0
302 or id % 7 = 0;
303
304select id, purpose
305from journeys;
306
307use examjulyninth;
308
309insert into cards(card_number, card_status, bank_account_id)
310select reverse(c.full_name), 'Active', c.id
311from clients c
312where c.id between 191 and 200;
313
314use soft_uni;
315drop function ufn_is_word_comprised;
316
317create function
318 ufn_is_word_comprised(set_of_letters varchar(50), word varchar(50))
319 returns bit
320begin
321 declare length int;
322 declare i int;
323 declare res int;
324 set res = 1;
325 set i = 0;
326 set length = char_length(word);
327
328 while (i <= length)
329 do
330 if lower(set_of_letters) not like lower(concat('%', substring(word, i, 1), '%')) <> 0
331 then
332 set res = 0;
333 end if;
334 set i = i + 1;
335 end while;
336 return res;
337end;
338
339select ufn_is_word_comprised('dog', 'dogdgodgoz');
340
341use bank;
342drop function ufn_calculate_future_value;
343drop procedure usp_calculate_future_value_for_account;
344
345create function ufn_calculate_future_value(sum decimal(16, 4), interest_rate decimal(16, 4), years int)
346 returns decimal(16, 4)
347begin
348 return sum * (pow(1 + interest_rate, years));
349end;
350
351create procedure usp_calculate_future_value_for_account(acc_id int, int_rate decimal(16, 4))
352begin
353 select a.id,
354 ah.first_name,
355 ah.last_name,
356 a.balance,
357 round(ufn_calculate_future_value(a.balance, int_rate, 5), 4)
358 from accounts a
359 join account_holders ah on a.account_holder_id = ah.id
360 where acc_id = a.id;
361end;
362
363select a.balance
364from accounts a
365where id = 1;
366call usp_calculate_future_value_for_account(1, 0.01);
367CALL usp_calculate_future_value_for_account(1, 1);
368
369
370create procedure usp_time_difference_from_beginning_to_now()
371begin
372 select year(from_days(datediff('2020-10-29', '2018-10-29'))) years,
373 month(from_days(datediff('2020-10-29', '2018-10-29'))) months,
374 day(from_days(datediff('2020-10-29', '2018-10-29'))) days;
375end;
376
377call usp_time_difference_from_beginning_to_now();
378
379show triggers from bank;
380
381insert into accounts(id, account_holder_id, balance)
382values (91, 10, 100);
383
384select *
385from accounts
386where id = 90;
387
388
389select *
390from logs;
391create table logs
392(
393 log_id int primary key auto_increment,
394 account_id int,
395 old_sum decimal(16, 4) not null,
396 new_sum decimal(16, 4) not null,
397 constraint fk_logs_accounts foreign key (account_id)
398 references accounts (id)
399);
400
401create trigger tr_logs_accounts_change
402 after update
403 on accounts
404 for each row
405begin
406 if (old.balance != new.balance)
407 then
408 insert into logs(account_id, old_sum, new_sum)
409 values (new.id, old.balance, new.balance);
410 end if;
411end;
412# drop table notification_emails;
413# drop table notification_emails;
414create table notification_emails
415(
416 id int primary key auto_increment,
417 recipient int,
418 subject varchar(255),
419 body varchar(255)
420);
421
422create trigger tr_logs_create_new_email
423 after insert
424 on logs
425 for each row
426begin
427 if (new.old_sum != new.new_sum) then
428 insert into notification_emails(recipient, subject, body)
429 VALUES (new.account_id,
430 concat('Balance change for account: ', NEW.account_id),
431 concat('On ',
432 date_format(
433 now(), '%b %d %Y %l:%i:%s %p'
434 ),
435 ' your balance was changed from ',
436 NEW.old_sum, ' to ',
437 NEW.new_sum, '.'));
438 end if;
439end;
440
441drop trigger tr_logs_create_new_email;
442select *
443from logs;
444select *
445from notification_emails;
446update accounts
447set accounts.balance = accounts.balance + 15
448where accounts.id = 91;
449select *
450from accounts
451where id = 91;
452truncate logs;
453select *
454from logs;
455
456
457use geography;
458
459
460
461select c.continent_code, c.currency_code, count(c.currency_code) cz
462from countries c
463group by c.continent_code, c.currency_code
464having cz > 1
465 and cz >= (
466 select count(c1.currency_code) mx
467 from countries c1
468 where c.continent_code = c1.continent_code
469 group by c1.continent_code, c1.currency_code
470 order by mx desc
471 limit 1
472)
473order by c.continent_code, c.currency_code;
474
475
476create table students
477(
478
479 student_id int primary key auto_increment,
480 name varchar(50) not null
481);
482create table exams
483(
484 exam_id int primary key auto_increment,
485 name varchar(50) not null
486);
487
488create table students_exams
489(
490 student_id int,
491 exam_id int,
492 constraint fk_students_exams_students
493 foreign key (student_id) references students (student_id),
494 constraint fk_students_exams_exams
495 foreign key (exam_id) references exams (exam_id)
496);
497
498insert into students
499values (1, 'Mila'),
500 (2, 'Toni'),
501 (3, 'Ron');
502
503insert into exams
504values (101, 'Spring MVC'),
505 (102, 'Neo4j'),
506 (103, 'Oracle 11g');
507
508insert into students_exams
509values (1, 101),
510 (1, 102),
511 (2, 101),
512 (3, 103),
513 (2, 102),
514 (2, 103);
515
516
517
518create table teachers
519(
520 teacher_id int primary key auto_increment,
521 name varchar(50),
522 manager_id int
523);
524
525# drop table teachers;
526
527insert into teachers
528values (101, 'John', NULL),
529 (102, 'Maya', 106),
530 (103, 'Silvia', 106),
531 (104, 'Ted', 105),
532 (105, 'Mark', 101),
533 (106, 'Greta', 101);
534alter table teachers
535 add constraint fk_teachers_teachers
536 foreign key teachers (manager_id) references teachers (teacher_id);
537
538create table students
539(
540
541 student_id int primary key auto_increment,
542 name varchar(50) not null
543);
544create table exams
545(
546 exam_id int primary key auto_increment,
547 name varchar(50) not null
548);
549
550create table students_exams
551(
552 student_id int,
553 exam_id int,
554 primary key pk_students_exams (student_id, exam_id),
555 constraint fk_students_exams_students
556 foreign key (student_id) references students (student_id),
557 constraint fk_students_exams_exams
558 foreign key (exam_id) references exams (exam_id)
559);
560
561insert into students
562values (1, 'Mila'),
563 (2, 'Toni'),
564 (3, 'Ron');
565
566insert into exams
567values (101, 'Spring MVC'),
568 (102, 'Neo4j'),
569 (103, 'Oracle 11g');
570
571insert into students_exams
572values (1, 101),
573 (1, 102),
574 (2, 101),
575 (3, 103),
576 (2, 102),
577 (2, 103);
578
579create table items_types
580(
581 item_type_id int(11) primary key auto_increment,
582 name varchar(50)
583);
584
585create table items
586(
587 item_id int(11) primary key auto_increment,
588 name varchar(50),
589 item_type_id int(11),
590 constraint fk_items_items_types foreign key (item_type_id)
591 references items_types (item_type_id)
592);
593
594create table order_items
595(
596 order_id int(11),
597 item_id int(11),
598 primary key pk_order_items (order_id, item_id)
599);
600
601create table orders
602(
603 order_id int(11) primary key auto_increment,
604 customer_id int(11) null
605);
606create table customers
607(
608 customer_id int(11) primary key auto_increment,
609 name varchar(50),
610 birthday date not null,
611 city_id int(11) unique
612);
613
614create table cities
615(
616 city_id int(11) primary key auto_increment,
617 name varchar(50)
618);
619
620alter table customers
621 add constraint fk_customers_cities
622 foreign key customers (city_id) references cities (city_id);
623
624alter table orders
625 add constraint fk_orders_customers
626 foreign key (customer_id) references customers (customer_id);
627
628alter table order_items
629 add constraint fk_order_items_orders
630 foreign key order_items (order_id) references orders (order_id);
631
632alter table order_items
633 add constraint fk_order_items_items
634 foreign key order_items (item_id) references items (item_id);
635
636alter table items
637 add constraint fk_items_item_types
638 foreign key items (item_type_id) references items_types (item_type_id);
639
640create table subjects
641(
642 subject_id int(11) primary key auto_increment,
643 subject_name varchar(50)
644);
645
646create table agenda
647(
648 student_id int(11),
649 subject_id int(11),
650 primary key pk_agenda (student_id, subject_id),
651 constraint fk_agenda_subjects foreign key (subject_id)
652 references subjects (subject_id)
653);
654
655create table students
656(
657 student_id int(11) primary key auto_increment,
658 student_number varchar(12),
659 student_name varchar(50),
660 major_id int(11)
661);
662
663alter table agenda
664 add constraint fk_agenda_students
665 foreign key agenda (student_id) references students (student_id);
666
667create table majors
668(
669 major_id int(11) primary key auto_increment,
670 name varchar(50)
671);
672alter table students
673 add constraint fk_students_majors
674 foreign key students (major_id) references majors (major_id);
675create table payments
676(
677 payment_id int(11) primary key auto_increment,
678 payment_date date,
679 payment_amount decimal(8, 2),
680 student_id int(11),
681 constraint fk_payments_students
682 foreign key (student_id) references students (student_id)
683);
684
685
686drop table item_types;
687
688create table item_types
689(
690 item_type_id int(11) primary key auto_increment,
691 name varchar(50)
692);
693
694create table items
695(
696 item_id int(11) primary key auto_increment,
697 name varchar(50),
698 item_type_id int(11),
699 constraint fk_items_item_types foreign key (item_type_id)
700 references item_types (item_type_id)
701);
702
703create table order_items
704(
705 order_id int(11),
706 item_id int(11),
707 primary key pk_order_items (order_id, item_id),
708 constraint fk_order_items_items foreign key (item_id)
709 references items (item_id)
710);
711
712drop table cities, customers, orders, order_items;
713
714use `mysql exam - 25 february 2018`;
715drop table commits, files, issues, repositories_contributors, repositories, users;
716
717
718create table users
719(
720 id int primary key auto_increment,
721 username varchar(30) not null,
722 password varchar(30) not null,
723 email varchar(50) not null
724);
725
726create table issues
727(
728 id int primary key auto_increment,
729 title varchar(255) not null,
730 issue_status varchar(6) not null,
731 repository_id int not null,
732 assignee_id int not null
733);
734
735create table repositories
736(
737
738 id int primary key auto_increment,
739 name varchar(50) not null
740);
741create table commits
742(
743 id int primary key auto_increment,
744 message varchar(255) not null,
745 issue_id int,
746 repository_id int not null,
747 contributor_id int not null
748);
749
750create table files
751(
752 id int primary key auto_increment,
753 name varchar(100),
754 size decimal(10, 2),
755 parent_id int,
756 commit_id int not null
757);
758
759create table repositories_contributors
760(
761 repository_id int,
762 contributor_id int,
763# constraint pk_repositories_contributors
764# primary key (repository_id, contributor_id),
765 constraint fk_repositories_contributors_users
766 foreign key (contributor_id)
767 references users (id),
768 constraint
769 fk_repositories_contributors_repositories
770 foreign key (repository_id)
771 references repositories (id)
772);
773
774alter table commits
775 add constraint fk_commits_repositories
776 foreign key commits (repository_id)
777 references repositories (id),
778 add constraint fk_commits_users
779 foreign key commits (contributor_id) references
780 users (id),
781 add constraint fk_commits_issues
782 foreign key commits (issue_id) references
783 issues (id);
784
785alter table issues
786 add constraint fk_issues_repositories
787 foreign key issues (repository_id)
788 references repositories (id),
789 add constraint fk_issues_users
790 foreign key issues (assignee_id)
791 references users (id);
792
793alter table files
794 add constraint fk_files_commits
795 foreign key files (commit_id) references commits (id),
796 add constraint fk_files_files
797 foreign key files (parent_id) references files (id);
798
799
800select repository_id, contributor_id
801from repositories_contributors
802where repository_id = contributor_id
803order by repository_id;
804
805
806select id, name, size
807from files
808where name like '%html'
809 and size > 1000
810order by size desc;
811
812select i.id, concat(u.username, ' : ', i.title)
813from issues i
814 join users u on i.assignee_id = u.id
815order by i.id desc;
816
817select f1.id, f1.name, concat(f1.size, 'KB')
818from files f1
819where f1.id not in (
820 select f2.parent_id
821 from files f2
822 where f2.parent_id is not null
823)
824order by f1.id;
825
826select r.id, r.name, count(i.id) c
827from repositories r
828 join issues i on r.id = i.repository_id
829group by r.id
830order by c desc, r.id
831limit 5;
832
833select r.id, r.name, count(c.id) coms, count(c.contributor_id) conts, c.contributor_id
834from repositories r
835 join commits c on r.id = c.repository_id
836group by r.id
837order by coms asc;
838
839
840select rc.repository_id id,
841 r.name,
842 (
843 select count(c.id)
844 from commits c
845 where rc.repository_id = c.repository_id
846 ),
847 count(rc.contributor_id) contributors
848from repositories_contributors rc
849 join repositories r on rc.repository_id = r.id
850group by rc.repository_id
851order by contributors desc
852 , rc.repository_id
853limit 1;
854
855
856select u.id, u.username, count(i.id) issues
857from users u
858 join issues i on i.assignee_id = u.id
859group by u.id
860order by u.id;
861
862
863select u.id, u.username, count(i.id) cm
864from users u
865 left join commits c on u.id = c.contributor_id
866 left join issues i on c.issue_id = i.id
867 and i.assignee_id = u.id
868group by u.id
869order by cm desc, u.id;
870
871
872select count(
873 message),
874 message
875from commits
876where id = 27;
877
878
879select r.id, r.name, count(distinct c.contributor_id) users
880from repositories_contributors rc
881 right join repositories r on rc.repository_id = r.id
882 left join commits c on r.id = c.repository_id
883group by r.id
884order by users desc, r.id;
885
886create procedure udp_commit(username varchar(50),
887 password varchar(50),
888 message varchar(255),
889 issue_id int)
890begin
891
892 if not exists(select u.username
893 from users u
894 where u.username = username)
895 then
896 signal SQLSTATE '45000' SET message_text = 'No such user!';
897 elseif
898 not exists(select u.password
899 from users u
900 where u.password = password)
901 then
902 signal SQLSTATE '45000' SET message_text = 'Password is incorrect!';
903 elseif
904 not exists(select id from issues where id = issue_id)
905 then
906 signal SQLSTATE '45000' SET message_text = 'The issue does not exist!';
907 end if;
908
909 insert into commits(message, issue_id, repository_id, contributor_id)
910 select message,
911 issue_id,
912 (select i.repository_id
913 from issues i
914 where i.id = issue_id),
915 u.id
916 from users u
917 join issues i on i.assignee_id = u.id
918 join commits c on i.id = c.issue_id
919 where username = u.username
920 limit 1;
921
922 update issues
923 set issue_status = 'closed'
924 where id = issue_id = id;
925end;
926
927drop procedure udp_commit;
928call udp_commit(
929 'WhoDenoteBel',
930 'ajmiSQi*',
931 'fk dis 4it', 2
932 );
933select *
934from commits;
935
936select message,
937 issue_id,
938 i.repository_id,
939 u.id
940from users u
941 join issues i on u.id = i.assignee_id
942 join commits c on i.id = c.issue_id
943where 'WhoDenoteBel' = u.username
944limit 1;
945
946
947SELECT SUBSTRING_INDEX(f.name, '.', 1) AS 'file',
948# COUNT(nullif(
949 LOCATE(f.name, c.message)
950 ,
951 f.name,
952 c.message
953# , 0)) AS 'recursive_count'
954FROM `files` AS f
955 JOIN
956 `files` AS p ON f.parent_id = p.id
957 JOIN
958 `commits` AS c
959GROUP BY f.name
960ORDER BY f.name;
961
962select left(a.name, locate('.', a.name) - 1)
963 ,
964 COUNT(
965 nullif(
966 LOCATE(
967 a.name, c.message
968 )
969 , 0)
970 ),
971 c.message,
972 a.name
973from files a
974 join files b on
975 a.id = b.parent_id and b.id = a.parent_id and a.id != b.id
976 join commits c
977group by a.name
978order by a.name;
979
980
981insert into issues (title, issue_status, repository_id, assignee_id)
982select concat('Critical Problem With ', f.name, '!'),
983 'open',
984 ceil(f.id * 2 / 3),
985 c.contributor_id
986from files f
987 join commits c on f.commit_id = c.id
988where f.id between 46 and 50;
989
990update repositories_contributors rc
991 join (
992 select r.id, count(contributor_id) count
993 from repositories r
994 left join repositories_contributors rc
995 on r.id = rc.repository_id
996 group by repository_id
997 order by min(contributor_id), r.id
998 limit 1
999 ) as z
1000set rc.repository_id = z.id
1001where contributor_id = repository_id;
1002select *
1003from repositories_contributors;
1004
1005update repositories_contributors rc
1006 join repositories r2 on rc.repository_id = r2.id
1007
1008set contributor_id =
1009where contributor_id = repository_id;
1010
1011select contributor_id, repository_id
1012from repositories_contributors
1013where contributor_id = repository_id
1014;
1015
1016
1017
1018delete r
1019from repositories as r
1020 left join issues c on r.id = c.repository_id
1021where c.id is null;
1022
1023
1024DELETE r
1025FROM `repositories` AS r
1026 LEFT JOIN
1027 `issues` AS i ON r.id = i.repository_id
1028WHERE i.id IS NULL;
1029
1030create schema `exam 03 Sept 2017`;
1031use `exam 03 Sept 2017`;
1032
1033drop schema `exam 03 Sept 2017`;
1034
1035create table pictures
1036(
1037 id int primary key auto_increment,
1038 path varchar(255) not null,
1039 size decimal(10, 2) not null
1040);
1041
1042create table users
1043(
1044 id int primary key auto_increment,
1045 username varchar(30) not null unique,
1046 password varchar(30) not null,
1047 profile_picture_id int,
1048 constraint fk_users_pictures foreign key (profile_picture_id)
1049 references pictures (id)
1050);
1051
1052create table users_followers
1053(
1054 user_id int,
1055 follower_id int,
1056 constraint fk_users_followers_users
1057 foreign key (user_id) references users (id),
1058 constraint fk_users_followers_follower
1059 foreign key (follower_id)
1060 references users (id)
1061);
1062
1063create table comments
1064(
1065 id int primary key auto_increment,
1066 content varchar(255) not null,
1067 user_id int not null,
1068 post_id int not null,
1069 constraint fk_comments_users foreign key (user_id)
1070 references users (id)
1071);
1072
1073create table posts
1074(
1075 id int primary key auto_increment,
1076 caption varchar(255) not null,
1077 user_id int not null,
1078 picture_id int not null,
1079 constraint fk_posts_users foreign key (user_id)
1080 references users (id),
1081 constraint fk_posts_pictures foreign key (picture_id)
1082 references pictures (id)
1083);
1084
1085alter table comments
1086 add constraint fk_comments_posts foreign key (post_id)
1087 references posts (id);
1088
1089select id, path, size
1090from pictures
1091where size > 50000
1092 and path regexp 'png|jpeg'
1093order by size desc;
1094
1095select c.id, concat(u.username, ' : ', c.content)
1096from comments c
1097 join users u on c.user_id = u.id
1098order by c.id desc;
1099select distinct u.id,
1100 u.username,
1101 concat(p.size, 'KB') p
1102from users u
1103 join users u2 on u.profile_picture_id = u2.profile_picture_id
1104 and u.id != u2.id
1105 join pictures p on u.profile_picture_id = p.id
1106order by u.id;
1107
1108select p.id, p.caption, count(c.id) cz
1109from posts p
1110 left join comments c on p.id = c.post_id
1111group by p.id
1112order by cz desc, p.id
1113limit 5;
1114
1115select u.id,
1116 u.username,
1117 (select count(p.id)
1118 from posts p
1119 where p.user_id = uf.user_id) zz
1120 ,
1121 count(uf.follower_id) fc
1122from users u
1123 join users_followers uf on u.id = uf.user_id
1124group by u.id
1125order by fc desc
1126limit 1;
1127
1128
1129# нестед джойн
1130
1131select p.id, p.caption, count(c.user_id) cz
1132from posts p
1133 join comments c on p.id = c.post_id
1134group by p.id
1135order by cz desc, p.id;
1136
1137select p.id, p.caption, count(u.id) cz
1138from posts p
1139 join users u on p.user_id = u.id
1140 join comments c on u.id = c.user_id
1141group by p.id
1142order by cz desc, p.id;
1143
1144
1145SELECT p.id,
1146 concat((SELECT pp.caption
1147 FROM posts as pp
1148 )) as caption,
1149 COUNT(u.id) as "count"
1150FROM posts p
1151
1152 JOIN comments as c
1153 ON c.post_id = p.id
1154 JOIN users as u
1155 ON u.id = c.user_id
1156GROUP BY post_id
1157order by count desc, p.id
1158
1159select p.id, p.caption, count(distinct c.user_id) cz
1160from posts p
1161 left join comments c on p.id = c.post_id
1162group by p.id
1163order by cz desc, p.id;
1164
1165
1166select u.id,
1167 u.username,
1168 (
1169 select p2.caption
1170 from posts p2
1171 left join comments c2 on p2.id = c2.post_id
1172 where u.id = p2.user_id
1173 group by p2.id
1174 order by count(c2.id) desc, p2.id
1175 limit 1)
1176from posts p
1177 left join comments c on p.id = c.post_id
1178 left join users u on p.user_id = u.id
1179group by u.id#, p.id
1180order by u.id#, p.id;
1181
1182
1183# select * from A join B join C JOIN D
1184
1185 from posts p
1186 left join comments c
1187on p.id = c.post_id
1188 left join users u on p.user_id = u.id
1189 table a - posts table b - comments
1190#how does join work??
1191#left join show all from posts ?
1192#what happens after the second join with users ??
1193#which table is left and right ????
1194
1195
1196insert into comments(content, user_id, post_id)
1197select concat('Omg!', u.username, '!This is so cool!'),
1198 ceil(p.id * 3 / 2),
1199 p.id
1200from posts p
1201 join users u on p.user_id = u.id
1202where p.id between 1 and 10;
1203
1204
1205select u.id, count(uf.follower_id)
1206from users u
1207 left join users_followers uf on u.id = uf.user_id
1208group by u.id;
1209
1210delete u
1211from users u
1212where u.id not in (
1213 select uf.follower_id
1214 from users_followers uf
1215);
1216
1217select u.id
1218from users u
1219where u.id = 21;
1220
1221create procedure udp_commit(username varchar(255), password varchar(255),
1222 cap varchar(255), path varchar(255))
1223begin
1224 if !exists(select u.username
1225 from users u
1226 where u.password = password) then
1227 signal sqlstate '45000' SET MESSAGE_TEXT = 'Password is incorrect!';
1228 elseif
1229 !exists(select p.path
1230 from users u
1231 join pictures p on
1232 u.profile_picture_id = p.id
1233 where p.path = path
1234 )
1235 then
1236 signal sqlstate '45000' SET MESSAGE_TEXT = 'The picture does not exist!';
1237 end if;
1238
1239 insert into posts (caption, user_id, picture_id)
1240 values (cap, (select u.id, p.id
1241 from users u
1242 join
1243 pictures p
1244 on u.profile_picture_id = p.id
1245 where u.username = username));
1246end;
1247
1248drop procedure udp_commit;
1249
1250call udp_commit('UnderSinduxrein', '4l8nYGTKMW',
1251 '#new #procedure', 'src/folders/resources/images/story/reformatted/img/hRI3TW31rC.img');
1252
1253CREATE PROCEDURE udp_post(username VARCHAR(30),
1254 password VARCHAR(30),
1255 caption VARCHAR(255),
1256 path VARCHAR(255))
1257BEGIN
1258
1259 DECLARE user_id INT(11);
1260 DECLARE picture_id INT(11);
1261
1262 IF ((
1263 SELECT u.password
1264 FROM users AS u
1265 WHERE u.username = username
1266 ) <> password)
1267 THEN
1268 SIGNAL SQLSTATE '45000'
1269 SET MESSAGE_TEXT = 'Password is incorrect!';
1270 END IF;
1271
1272 IF ((
1273 SELECT COUNT(p.path)
1274 FROM pictures AS p
1275 WHERE p.path = path) = 0)
1276 THEN
1277 SIGNAL SQLSTATE '45000'
1278 SET MESSAGE_TEXT = 'The picture does not exist!';
1279 END IF;
1280
1281 SET user_id := (
1282 SELECT id
1283 FROM users AS u
1284 WHERE u.username = username
1285 );
1286
1287 SET picture_id := (
1288 SELECT p.id
1289 FROM pictures AS p
1290 WHERE p.path LIKE path
1291 );
1292
1293 INSERT INTO posts (caption, user_id, picture_id)
1294 VALUES (caption, user_id, picture_id);
1295
1296end;
1297
1298
1299create procedure udp_filter(hashtag varchar(255))
1300begin
1301 select p.id, p.caption, u.username
1302 from posts p
1303 join users u on u.id = p.user_id
1304 where caption like concat('%', hashtag, '%')
1305 order by p.id;
1306end;
1307
1308call udp_filter('cool');
1309
1310create schema `MySQL Exam - 24 Jun 2017`;
1311use `MySQL Exam - 24 Jun 2017`;
1312drop schema `MySQL Exam - 24 Jun 2017`;
1313create table users
1314(
1315 id int primary key auto_increment,
1316 username varchar(30) not null,
1317 password varchar(30) not null,
1318 email varchar(50)
1319);
1320
1321create table submissions
1322(
1323 id int primary key auto_increment,
1324 passed_tests int,
1325 problem_id int,
1326 user_id int,
1327 constraint fk_submissions_users foreign key (user_id)
1328 references users (id)
1329);
1330
1331create table problems
1332(
1333 id int primary key auto_increment,
1334 name varchar(100) not null,
1335 points int not null,
1336 tests int default 0,
1337 contest_id int
1338);
1339alter table submissions
1340 add constraint fk_submissions
1341 foreign key (problem_id)
1342 references problems (id);
1343
1344create table contests
1345(
1346 id int primary key auto_increment,
1347 name varchar(50),
1348 category_id int
1349);
1350
1351create table users_contests
1352(
1353 user_id int,
1354 contest_id int,
1355 constraint primary key pk_users_contests (user_id, contest_id),
1356 constraint fk_users_contests_users
1357 foreign key (user_id) references users (id),
1358 constraint fk_users_contests_contests
1359 foreign key (contest_id) references contests (id)
1360);
1361
1362alter table problems
1363 add constraint
1364 fk_problems_contests foreign key (contest_id)
1365 references contests (id);
1366
1367create table categories
1368(
1369 id int primary key auto_increment,
1370 name varchar(50) not null,
1371 parent_id int,
1372 constraint fk_categories_categories foreign key
1373 (parent_id) references categories (id)
1374);
1375
1376alter table contests
1377 add constraint fk_contests_categories foreign key (category_id)
1378 references categories (id);
1379
1380select id, name
1381from categories
1382where parent_id is null
1383order by id;
1384
1385select id, name, tests
1386from problems
1387where tests > points
1388 and locate(' ', name) <> 0
1389order by id desc;
1390
1391select p.id,
1392 concat(c2.name, ' - ', c.name, ' - ', p.name) zx
1393from problems p
1394 join contests c on p.contest_id = c.id
1395 join categories c2 on c.category_id = c2.id
1396order by p.id;
1397
1398select c.id, c.name
1399from categories c
1400 left join categories c2
1401 on c.id = c2.parent_id
1402where c2.parent_id is null
1403order by c.name, c.id;
1404
1405select distinct u.id, u.username, u.password
1406from users u
1407 join users u2 on u.password = u2.password
1408 and u.id != u2.id
1409order by u.username,
1410 u.id;
1411
1412select uc.contest_id, c.name, count(distinct uc.user_id) zx
1413from users_contests uc
1414 join contests c on uc.contest_id = c.id
1415group by uc.contest_id
1416order by zx, uc.contest_id;
1417#limit 5
1418
1419select *
1420from (
1421 select c.id,
1422 c.name,
1423 count(distinct uc.user_id) zx
1424 from contests c
1425 left join users_contests uc
1426 on uc.contest_id = c.id
1427 group by uc.contest_id
1428 order by zx desc, uc.contest_id desc
1429 limit 5
1430 ) g
1431order by g.zx, g.id;
1432
1433select s.id
1434 , u.username
1435 , p.name
1436 , concat(s.passed_tests, ' / ', s.problem_id)
1437 , count(uc.contest_id)
1438from submissions s
1439 left join users_contests uc on s.user_id = uc.user_id
1440 join users u on s.user_id = u.id
1441 join problems p on s.problem_id = p.id
1442group by s.id
1443order by s.id desc;
1444
1445
1446
1447select s.id,
1448 u.username,
1449 p.name,
1450 concat(s.passed_tests, ' / ', p.tests)
1451from submissions s
1452 join (
1453 select uc.user_id, u.username, count(uc.contest_id) z
1454 from users_contests uc
1455 join users u on uc.user_id = u.id
1456 group by uc.user_id
1457 order by z desc
1458 limit 1
1459) u
1460 on u.user_id = s.user_id
1461 join problems p on s.problem_id = p.id
1462group by s.id
1463order by s.id desc;
1464
1465select c.id, c.name, max(x.y) z
1466from contests c
1467 join (
1468 select sum(p.points) y, p.contest_id
1469 from problems p
1470 join contests c2 on p.contest_id = c2.id
1471# where c2.id = p.id
1472 group by p.contest_id
1473) x on x.contest_id = c.id
1474group by c.id
1475order by z desc, c.id;
1476
1477
1478
1479create table evaluated_submissions
1480(
1481 id int primary key auto_increment,
1482 problem varchar(100) not null,
1483 user varchar(30) not null,
1484 result int not null
1485);
1486
1487
1488create table logged_in_users
1489select *
1490FROM users
1491WHERE 1 = 0;
1492
1493select *
1494from logged_in_users;
1495
1496create procedure udp_login(username varchar(30), password varchar(30))
1497begin
1498 declare id int;
1499 declare email varchar(50);
1500 IF ((SELECT u.username
1501 FROM users u
1502 WHERE u.username = username) IS NULL) THEN
1503 SIGNAL SQLSTATE '45000'
1504 SET MESSAGE_TEXT = 'Username does not exist!';
1505 elseif
1506 ((SELECT u.password
1507 FROM users u
1508 WHERE u.username = username) != password) THEN
1509 SIGNAL SQLSTATE '45000'
1510 SET MESSAGE_TEXT = 'Password is incorrect!';
1511 elseif ((
1512 select lu.username
1513 from logged_in_users lu
1514 where lu.username = username
1515 ) is not null)
1516 then
1517 SIGNAL SQLSTATE '45000'
1518 SET MESSAGE_TEXT = 'User is already logged in!';
1519 end if;
1520
1521 set id := (
1522 select u.id
1523 from users u
1524 where u.username = username
1525 and u.password = password
1526 );
1527 set email := (
1528 select u.email
1529 from users u
1530 where u.username = username
1531 and u.password = password
1532 );
1533# INSERT INTO `logged_in_users` SELECT * FROM `users` AS u WHERE u.username = username;
1534 insert into logged_in_users
1535 values (id, username, password, email);
1536end;
1537drop procedure udp_login;
1538call udp_login('doge', 'doge');
1539select *
1540from logged_in_users;
1541
1542create procedure udp_evaluate(the_id int)
1543begin
1544 declare result double;
1545 declare s_name varchar(200);
1546 declare u_user varchar(200);
1547
1548 if (select s.id from submissions s where s.id = the_id) is null
1549 then
1550 signal SQLSTATE '45000' set MESSAGE_TEXT = 'Submission does not exist!';
1551 end if;
1552 set result := (
1553 select ceil(p.points / p.tests * s.passed_tests)
1554 from submissions s
1555 join problems p on s.problem_id = p.id
1556 where s.id = the_id
1557 );
1558 set s_name := (
1559 select p.name
1560 from problems p
1561 join submissions s2 on p.id = s2.problem_id
1562 where s2.id = the_id
1563 );
1564 set u_user := (
1565 select u.username
1566 from submissions p
1567 join users u on p.user_id = u.id
1568 where p.id = the_id
1569 );
1570
1571
1572 insert into evaluated_submissions
1573 values (the_id, s_name, u_user, result);
1574end;
1575
1576drop PROCEDURE udp_evaluate;
1577call udp_evaluate(1);
1578select *
1579from evaluated_submissions;
1580
1581create trigger tr
1582 before insert
1583 on problems
1584 for each row
1585begin
1586 if locate(' ', new.name) = 0
1587 then
1588 signal sqlstate '45000' set message_text = 'The given name is invalid!';
1589
1590 elseif
1591 new.points <= 0
1592 then
1593 signal sqlstate '45000' set message_text = 'The problem’s points cannot be less or equal to 0!';
1594
1595 elseif
1596 new.tests <= 0 then
1597 signal sqlstate '45000' set message_text = 'The problem’s tests cannot be less or equal to 0!';
1598 end if;
1599
1600 insert into problems(name, points, tests, contest_id)
1601 VALUES (new.name, new.points, new.tests, new.contest_id);
1602end;
1603
1604select *
1605from submissions;
1606insert into submissions(passed_tests, problem_id, user_id)
1607 (
1608 select ceil
1609 (sqrt
1610 (
1611 pow
1612 (char_length(p.name), 3)
1613 ) - char_length(p.name)),
1614 p.id,
1615 ceil(p.id * 3 / 2)
1616 from problems p
1617 where p.id between 1 and 10);
1618
1619delete u
1620from users u
1621 left join users_contests c
1622 on u.id = c.user_id
1623where c.user_id is null;
1624
1625create schema `MySQL Exam Preparation I`;
1626use `MySQL Exam Preparation I`;
1627
1628
1629create table towns
1630(
1631 town_id int primary key auto_increment,
1632 town_name varchar(30) not null
1633);
1634
1635create table airports
1636(
1637 airport_id int primary key auto_increment,
1638 airport_name varchar(50) not null,
1639 town_id int not null,
1640 constraint fk_airports_towns
1641 foreign key (town_id) references towns (town_id)
1642);
1643create table airlines
1644(
1645 airline_id int primary key auto_increment,
1646 airline_name varchar(30) not null,
1647 nationality varchar(30) not null,
1648 rating int default 0
1649);
1650
1651create table customers
1652(
1653 customer_id int primary key auto_increment,
1654 first_name varchar(20) not null,
1655 last_name varchar(20) not null,
1656 date_of_birth date not null,
1657 gender VARCHAR(1) not null,
1658 home_town_id int not null,
1659 constraint fk_customers_towns foreign key (home_town_id)
1660 references towns (town_id)
1661);
1662
1663create table flights
1664(
1665 flight_id int primary key auto_increment,
1666 departure_time datetime not null,
1667 arrival_time datetime not null,
1668 status varchar(9) not null,
1669 origin_airport_id int,
1670 destination_airport_id int,
1671 airline_id int not null,
1672 constraint fk_flights_airports foreign key (origin_airport_id)
1673 references airports (airport_id),
1674 constraint fk_flights_airports2 foreign key (destination_airport_id)
1675 references airports (airport_id),
1676 constraint fk_flights_airlines foreign key (airline_id)
1677 references airlines (airline_id)
1678);
1679
1680create table tickets
1681(
1682
1683 ticket_id int primary key auto_increment,
1684 price decimal(8, 2) not null,
1685 class varchar(6) not null,
1686 seat varchar(5) not null,
1687 customer_id int not null,
1688 flight_id int not null,
1689 constraint fk_tickets_customers foreign key (customer_id)
1690 references customers (customer_id),
1691 constraint fk_tickets_flights foreign key (flight_id)
1692 references flights (flight_id)
1693);
1694
1695
1696
1697select distinct c.customer_id,
1698 (select concat_full_name(c.first_name, c.last_name)),
1699 t.town_name
1700from towns t
1701 join customers c on t.town_id = c.home_town_id
1702 join (
1703 select f.origin_airport_id, a.town_id, t.customer_id
1704 from flights f
1705 join tickets t on t.flight_id = f.flight_id
1706 join
1707 airports a on
1708 f.origin_airport_id = a.airport_id
1709 and f.status = 'Departing'
1710) z on z.town_id = c.home_town_id
1711 and z.customer_id = c.customer_id
1712order by c.customer_id;
1713
1714
1715select distinct c.customer_id,
1716 concat_full_name(c.first_name, c.last_name),
1717 2016 - year(c.date_of_birth) z
1718from customers c
1719 join tickets t on c.customer_id = t.customer_id
1720 join flights f on t.flight_id = f.flight_id
1721 and f.status = 'Departing'
1722order by z, c.customer_id;
1723
1724
1725
1726select c.customer_id,
1727 concat_full_name(c.first_name, c.last_name),
1728 max(t.price) et,
1729 a.airport_name
1730from customers c
1731 join tickets t on c.customer_id = t.customer_id
1732 join flights f on t.flight_id = f.flight_id
1733 join airports a on f.destination_airport_id = a.airport_id
1734 and f.status = 'Delayed'
1735group by c.customer_id
1736order by et desc,
1737 c.customer_id
1738limit 3;
1739
1740select f.flight_id,
1741 f.departure_time,
1742 f.arrival_time,
1743 o.airport_name org,
1744 d.airport_name des,
1745 o.airport_id,
1746 d.airport_id
1747from flights f
1748 join (
1749 select a.airport_id, a.airport_name
1750 from airports a
1751) o on f.origin_airport_id = o.airport_id
1752 join (
1753 select a.airport_id, a.airport_name
1754 from airports a
1755) d on f.destination_airport_id = o.airport_id
1756# where f.status = 'Departing'
1757group by f.flight_id;
1758
1759#LAST
1760select f.flight_id
1761 , f.departure_time
1762 , f.arrival_time
1763 , f5.org
1764 , f5.des
1765from flights f
1766 join (
1767 select f.flight_id
1768 , a.airport_name org
1769 , a2.airport_name des
1770 from flights f
1771 join airports a on a.airport_id = f.origin_airport_id
1772 join airports a2 on a2.airport_id = f.destination_airport_id
1773 where f.status = 'Departing'
1774 order by f.departure_time desc, f.flight_id desc
1775 limit 5
1776) f5 on f.flight_id = f5.flight_id
1777order by f.departure_time, f.flight_id;
1778
1779
1780create function concat_full_name(first varchar(20), last varchar(20))
1781 returns varchar(40)
1782begin
1783 return concat(first, ' ', last);
1784end;
1785
1786select distinct c.customer_id,
1787 (concat_full_name(c.first_name, c.last_name)),
1788 2016 - year(c.date_of_birth) z
1789from customers c
1790 join tickets t on c.customer_id = t.customer_id
1791 join flights f on t.flight_id = f.flight_id
1792 and f.status = 'Arrived'
1793where 2016 - year(c.date_of_birth) < 21
1794order by z desc, c.customer_id;
1795
1796select a.airport_id, a.airport_name, z.cz
1797from airports a
1798 join flights f on (a.airport_id = f.origin_airport_id or
1799 a.airport_id = f.destination_airport_id)
1800 and f.status = 'Departing'
1801 join (
1802 select count(c.customer_id) cz, t.flight_id
1803 from customers c
1804 left join tickets t on c.customer_id = t.customer_id
1805 group by t.ticket_id
1806) z on z.flight_id = f.flight_id
1807order by a.airport_id;
1808
1809select a.airport_id, a.airport_name, count(t.customer_id)
1810from airports a
1811 join flights f on f.origin_airport_id = a.airport_id
1812 and f.status = 'Departing'
1813 join tickets t on f.flight_id = t.flight_id
1814group by a.airport_id, a.airport_name
1815order by a.airport_id;
1816
1817create table customer_reviews
1818(
1819 review_id int primary key auto_increment,
1820 review_content varchar(255) not null,
1821 review_grade int check (review_grade between 2 and 10),
1822 airline_id int,
1823 customer_id int,
1824 constraint fk_customer_reviews_airlines foreign key
1825 (airline_id)
1826 references airlines (airline_id),
1827 constraint fk_customer_reviews_customers foreign key
1828 (customer_id)
1829 references customers (customer_id)
1830);
1831
1832create table customer_bank_accounts
1833(
1834 account_id int primary key auto_increment,
1835 account_number varchar(10) not null unique,
1836 balance decimal(10, 2) not null,
1837 customer_id int,
1838 constraint fk_customer_bank_accounts_customers
1839 foreign key (customer_id) references customers (customer_id)
1840);
1841
1842create procedure udp_submit_review(cu int,
1843 rc varchar(255),
1844 rg int,
1845 an varchar(255))
1846begin
1847
1848 if (select a.airline_name
1849 from airlines a
1850 where a.airline_name = an) is null then
1851 signal sqlstate '45000' set message_text
1852 = 'Airline does not exist.';
1853 end if;
1854
1855 insert into customer_reviews (review_content,
1856 review_grade,
1857 airline_id,
1858 customer_id)
1859 values (rc, rg, (
1860 select a.airline_id
1861 from airlines a
1862 where a.airline_name = an)
1863 ,
1864 cu);
1865end;
1866
1867create procedure udp_purchase_ticket(_customer_id int
1868 , _flight_id int
1869 , _ticket_price decimal(8, 2)
1870 , _class varchar(6)
1871 , _seat varchar(5))
1872begin
1873
1874 if (select customer_id
1875 from customer_bank_accounts
1876 where customer_id = _customer_id
1877 and _ticket_price < balance
1878 ) is null then
1879 signal sqlstate '45000'
1880 set message_text =
1881 'Insufficient bank account balance for ticket purchase.';
1882 end if;
1883
1884 insert into tickets (price, class, seat, customer_id, flight_id)
1885 values (_ticket_price, _class, _seat, _customer_id, _flight_id);
1886
1887 update customer_bank_accounts
1888 set balance = balance - _ticket_price
1889 where customer_id = _customer_id;
1890end;
1891
1892create table arrived_flights
1893(
1894 flight_id int primary key auto_increment,
1895 arrival_time datetime not null,
1896 origin varchar(50) not null,
1897 destination varchar(50) not null,
1898 passengers int not null
1899);
1900
1901insert into flights (departure_time, arrival_time, status, origin_airport_id, destination_airport_id, airline_id)
1902select '2017-06-19 14:00:00',
1903 '2017-06-21 11:00:00',
1904 (case (a.airline_id % 4)
1905 when 0 then 'Departing'
1906 when 1 then 'Delayed'
1907 when 2 then 'Arrived'
1908 when 3 then 'Canceled'
1909 end),
1910 ceil(sqrt(char_length(a.airline_name))),
1911 ceil(sqrt(char_length(a.nationality))),
1912 a.airline_id
1913from airlines a
1914where a.airline_id between 1 and 10;
1915
1916
1917update tickets t
1918 join flights f on t.flight_id = f.flight_id
1919 join airlines a on f.airline_id = a.airline_id
1920set t.price = t.price * 1.50
1921where a.rating = (
1922 select a2.rating
1923 from airlines a2
1924 order by a2.rating desc
1925 limit 1
1926);
1927
1928
1929create schema the_nerd_herd;
1930use the_nerd_herd;
1931
1932create table locations
1933(
1934 id int primary key auto_increment,
1935 latitude float,
1936 longitude float
1937);
1938
1939create table credentials
1940(
1941 id int primary key auto_increment,
1942 email varchar(30),
1943 password varchar(20)
1944);
1945
1946create table users
1947(
1948 id int primary key auto_increment,
1949 nickname varchar(25),
1950 gender char(1),
1951 age int,
1952 location_id int,
1953 credential_id int unique,
1954 constraint fk_users_locations foreign key (location_id)
1955 references locations (id),
1956 constraint fk_users_credential foreign key (credential_id)
1957 references credentials (id)
1958);
1959
1960create table chats
1961(
1962 id int primary key auto_increment,
1963 title varchar(32),
1964 start_date date,
1965 is_active bit
1966);
1967
1968create table messages
1969(
1970 id int primary key auto_increment,
1971 content varchar(200),
1972 sent_on date,
1973 chat_id int,
1974 user_id int,
1975 constraint fk_messages_chats foreign key (chat_id)
1976 references chats (id),
1977 constraint fk_messages_users foreign key (user_id)
1978 references users (id)
1979);
1980
1981create table users_chats
1982(
1983 user_id int,
1984 chat_id int,
1985 constraint pk_users_chat primary key (user_id, chat_id),
1986 constraint fk_users_chats_users foreign key (user_id)
1987 references users (id),
1988 constraint fk_users_chats_chats foreign key (chat_id)
1989 references chats (id)
1990);
1991
1992select c.id, count(m.id) z
1993from chats c
1994 join messages m on c.id = m.chat_id
1995where m.id < 90
1996group by c.id
1997order by z desc, c.id
1998limit 5;
1999
2000select u.nickname, c.email, c.password
2001from users u
2002 join credentials c on u.credential_id = c.id
2003where c.email like '%co.uk'
2004order by c.email;
2005
2006select distinct u.id, u.nickname, u.age
2007from users u
2008where u.location_id is null
2009order by u.id;
2010
2011
2012
2013select distinct m.id, c.id, u.id
2014from messages m
2015 join users u on m.user_id = u.id
2016 join chats c on m.chat_id = c.id
2017 left join users_chats uc on m.user_id = uc.user_id
2018 and m.chat_id = uc.chat_id
2019where m.chat_id = 17
2020 and uc.chat_id is null
2021order by m.id desc;
2022
2023select u.nickname, c.title, l.latitude, l.longitude
2024from users u
2025 join locations l on l.id = u.location_id
2026 join users_chats uc on uc.user_id = u.id
2027 join chats c on c.id = uc.chat_id
2028where l.latitude between
2029 41.139999 and 44.129999
2030 and l.longitude between 22.209999 and 28.359999
2031order by c.title;
2032
2033select c.title, m.content
2034from chats c
2035 left join (
2036 select m.id, m.content, m.chat_id
2037 from messages m
2038 order by m.sent_on desc
2039) m on c.id = m.chat_id
2040where c.start_date = (
2041 select c2.start_date
2042 from chats c2
2043 order by c2.start_date asc
2044 limit 1
2045)
2046order by m.id;
2047
2048
2049select c.title, m.content#, m.sent_on
2050from chats c
2051 left join (
2052 select m.id ,m.content, m.chat_id,m.sent_on
2053 from messages m
2054 order by m.sent_on desc
2055) m on c.id = m.chat_id
2056where c.start_date = (
2057 select c2.start_date
2058 from chats c2
2059 order by c2.start_date desc
2060 limit 1
2061)
2062order by m.sent_on, m.id;