VzzqxALY

1DROP database IF EXISTS Sportclubs;
2create database sportclubs;
3Use sportclubs;
4
5create table sports (
6ID int auto_increment primary key,
7SportName varchar(45) not null
8);
9
10create table coaches(
11id int auto_increment primary key,
12coachtname varchar(20) not null unique,
13email varchar (255) null
14);
15create table groups (
16id int auto_increment primary key,
17dayofweek Enum ('monday','tuesday','wendsday','thursday','friday','saturday', 'sunday'),
18hoursofday time not null,
19location varchar(255) not null,
20sport_id int not null,
21constraint foreign key (sport_id) references sports(id),
22coach_id int not null,
23constraint foreign key (coach_id) references coaches(id),
24Unique key (dayofweek,hoursofday,location)
25);
26create table students(
27id int auto_increment primary key,
28Studentname varchar(20) not null unique,
29email varchar (255) null
30);
31create table student_group (
32student_id int not null,
33group_id int not null,
34constraint foreign key(student_id) references students(id),
35constraint  foreign key(group_id) references groups(id),
36primary key(student_id , group_id)
37);
38INSERT into sports (SportName)
39values ('Footbal'),
40	    ('Basketball'),
41        ('Tennis');
42
43INSERT into coaches (coachtname, email)
44values ('Darko Darkov', 'darko123@gmail.com'),
45	('Angela Kostadinovska' , 'angela123@gmail.com'),
46     ('Emil Moris' , 'moris489@gamil.com');
47
48INSERT into students (Studentname,email)
49values ('Nina Pavlovska', 'ninazpavl@gmail.com') ,
50	('Zivko Zivkov', 'zivezivkov12@gmail.com'),
51      ('Matei Mate', 'mate98@yahoo.com'),
52    ('Angela Kostadinovska', 'angie56@yahoo.com');
53
54update coaches 
55set coachtname = 'Asen Angelov'
56where id=1;
57
58delete from coaches 
59where email= 'darko123@gmail.com';
60
61select *from students where id between 2 and 4;