drop database upr;
create database upr;
use upr;

create table students (
	id integer not null auto_increment primary key,
    name varchar(150) not null,
    class varchar(3)


);

insert into students (id,name,class)
values (NULL,'pesho Ivan','11a');
insert into students (id,name,class)
values (NULL,'maria  ivanova','11b');

insert into students (id,name,class)
values (NULL,'Ivan Gosho','11a');

alter table students add grade integer not null;

update students set grade = 6 where name like '%ev';
update students set grade = 5 where name like '%ov';

create table subjects (
	id integer not null auto_increment primary key,
    name varchar(150) not null

);

insert into subjects (id,name)
values (NULL,'SUBD');

insert into subjects (id,name)
values (NULL,'AE');

insert into subjects (id,name)
values (NULL,'BEL');

insert into subjects (id,name)
values (NULL,'RE');

create table StudentSubjects(
	StudentID integer ,
    SubjectID integer ,
    primary key(StudentID,SubjectID)
);

insert into StudentSubjects(StudentID,SubjectID)
values(1,1);
insert into StudentSubjects(StudentID,SubjectID)
values(3,1);
insert into StudentSubjects(StudentID,SubjectID)
values(1,2);
insert into StudentSubjects(StudentID,SubjectID)
values(2,3);




SELECT st.name
FROM StudentSubjects ss
INNER JOIN students st ON StudentID = st.id
INNER JOIN subjects sb ON sb.id = ss.SubjectID
Where sb.name = 'SUBD';

SELECT sb.name
FROM StudentSubjects ss
INNER JOIN subjects sb ON sb.id = ss.SubjectID
INNER JOIN students st ON StudentID = st.id
Where st.name like 'Ivan%';

SELECT st.name, sb.name
FROM StudentSubjects ss
INNER JOIN subjects sb ON sb.id = ss.SubjectID
INNER JOIN students st ON StudentID = st.id;


SELECT sb.name
FROM subjects sb
left JOIN StudentSubjects ss ON sb.id =ss.SubjectID
WHERE ss.StudentID is null;