CREATE DATABASE _93aa_ksig;
USE _93aa_ksig;
CREATE TABLE students(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
address VARCHAR(50) NOT NULL,
egn VARCHAR (10) NOT NULL UNIQUE,
phone VARCHAR(10) NULL DEFAULT NULL,
class VARCHAR(5) NULL DEFAULT NULL
);
INSERT INTO students(name, egn, address, phone, class)
VALUES
('Ilyan Ivanov', '9401150045', 'Sofia-Mladost', '0895345123', '10'),
('Ivan Iliev', '9510104512', 'Sofia-Lulin', '0896786453', '11'),
('Elena Petrovs', '9505052154', 'Sofia-Mladost2', '0899123456', '11'),
('Ivan Iliev', '9510104542', 'Sofia-Mladost3', '0897123456', '11'),
('Maria Dimova', '9510104547', 'Sofia-Mladost4', '088898654', '11'),
('Antoaneta Ivanova', '9411104547', 'Sofia-Krasno selo', '0877123456', '10');

CREATE TABLE sports(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL
);
INSERT INTO sports
VALUES
(NULL, 'football'),
(NULL, 'voleyball');

CREATE  TABLE coaches(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
egn VARCHAR (10) NOT NULL UNIQUE
);
INSERT INTO coaches(name, egn)
VALUES
('Ivan Petrov', '75090412'),
('Petyr Dimitrov', '2345678901'),
('Kaloyan Kolev', '3456789012');

CREATE TABLE sportGroups(
id INT AUTO_INCREMENT PRIMARY KEY,
location VARCHAR(50) NOT NULL,
dayOfWeek ENUM('Mon', 'Tue', 'Wed', 'Thur', 'Fri', 'Sat', 'Sun'),
hourOfTraining TIME NOT NULL,
sport_id INT NOT NULL,
CONSTRAINT FOREIGN KEY (sport_id) REFERENCES sports(id),
coach_id INT NOT NULL,
CONSTRAINT FOREIGN KEY(coach_id) REFERENCES coaches(id),
UNIQUE KEY (location, dayOfWeek,  hourOfTraining)
);
INSERT INTO sportGroups(location, dayOfWeek, hourOfTraining, sport_id, coach_id)
VALUES
('Sofia-Mladost1', 'Mon', '8:00:00', 1, 1),
('Sofia-Mladost1', 'Mon', '9:00:00', 1, 2),
('Sofia-Lulin', 'Sun', '8:00:00', 2, 3),
('Sofia-Lulin', 'Sun', '9:30:00', 2, 2),
('Plovdiv', 'Mon', '12:00:00', 1, 1);

CREATE TABLE sportgr_student(
student_id INT NOT NULL,
CONSTRAINT FOREIGN KEY (student_id) REFERENCES students(id),
sportgr_id INT NOT NULL,
CONSTRAINT FOREIGN KEY (sportgr_id) REFERENCES sportgroups(id),
PRIMARY KEY (student_id, sportgr_id)
);
INSERT INTO sportgr_student
VALUES
(1,1),
(2,1),
(3,2),
(4,2),
(5,1),
(6,2);

CREATE TABLE programmers(
id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    address VARCHAR(255) NOT NULL,
    startWorkingDate DATE,/**YYYY-MM-DD**/
    teamLead_id INT NULL DEFAULT NULL,
    CONSTRAINT FOREIGN KEY (teamLead_id) REFERENCES programmers(id)
);

INSERT INTO programmers (name, address, startWorkingDate, teamLead_id)
VALUES
('Ivan Ivanov', 'Sofia', '1999-05-25', NULL),
('Georgi Petkov Todorov', 'Bulgaria - Sofia Nadezhda, bl. 35', '2002-12-01', 1),
('Todor Petkov', 'Sofia - Liylin 7', '2009-11-01', 1),
('Sofiq Dimitrova Petrova', 'Sofia - Mladost 4, bl. 7', '2010-01-01', 1),
('Teodor Ivanov Stoyanov', 'Sofia - Obelya, bl. 48', '2011-10-01', NULL),
('Iliya Stoynov Todorov', 'Sofia Nadezhda, bl. 28', '2000-02-01', 5),
('Mariela Dimitrova Yordanova', 'Sofia - Knyajevo, bl. 17', '2005-05-01', 5),
('Elena Miroslavova Georgieva', 'Sofia - Krasno Selo, bl. 27', '2008-04-01', 5),
('Teodor Milanov Milanov', 'Sofia - Lozenetz', '2012-04-01', 5);

SELECT progr.name as ProgramerName, progr.address as ProgramerAddress, teamLeads.name as TeamLeadName
FROM programmers as progr JOIN programmers as teamLeads
WHERE progr.teamLead_id = teamLeads.id;

SELECT sportGroups.location,
sportGroups.dayOfWeek,
sportGroups.hourOfTraining,
sports.name
from sportGroups inner join sports
on sportGroups.sport_id = sports.id;

SELECT coaches.name, sports.name
FROM coaches JOIN sports
on coaches.id IN(
SELECT coach_id
from sportGroups
WHERE sportGroups.sport_id = sports.id
);

SELECT distinct coaches.name, sports.name
from coaches JOIN sportGroups
on coach_id = sportGroups.coach_id
JOIN sports
on sportGroups.sport_id = sports.id;


 delimiter |
 create procedure getAllSportGroupsWithSports()
 begin
 select sg.location as locationOfGroup,
 sg.DayOfWeek as trainigDay,
 sg.hourOfTraining as trainingHour,
 sp.name as sportName
 from sportgroups as sg join sports as sp
 on sg.sport_id = sp.id;
 end
 |
 delimiter ;

 call getAllSportGroupsWithSports();
delimiter |
create procedure proc_in (in var varchar(50))
begin
set @coach_name = var;
end;
|
delimiter ;
call proc_in ('Ivan Petrov');
select * from coaches
where name = @coach_name;