CREATE DATABASE ctd;

create table Groups (
    group_id int,
    group_no char(6)
);

create table Students (
    student_id int,
    name varchar(30),
    group_id int
);

insert into Groups
    (group_id, group_no) values
    (1, 'M34371'),
    (2, 'M34391');

insert into Students
    (student_id, name, group_id) values
    (1, 'Maksim Alzhanov', 2),
    (2, 'Artem Koton', 1),
    (3, 'Anna Suris', 1);

SELECT group_id, group_no FROM Groups;

SELECT student_id, name, group_id FROM Students;

SELECT name, group_no
FROM Students NATURAL JOIN Groups;

SELECT Students.name, Groups.group_no
FROM Students
     INNER JOIN Groups
     ON Students.group_id = Groups.group_id;

SELECT group_id, group_no FROM Groups ORDER BY group_id;

SELECT group_id, group_no FROM Groups ORDER BY group_no;

SELECT group_id, group_no FROM Groups ORDER BY group_no DESC;

DROP TABLE Groups;

update Students
    set group_id = 2 
    where student_id = 2;

update Students
    set group_id = 2 
    where name = 'Artem Koton';

insert into Groups (group_id, group_no) values
    (1, 'M34381');

SELECT group_id, group_no FROM Groups;

delete from Groups where group_no = 'M34381';

alter table Groups
    add constraint group_id_unique unique (group_id);

INSERT INTO Groups (group_id, group_no)
VALUES
	(1, 'M32381');

psql:/tmp/babel-oESQj7/sql-in-jieQhI:4: ERROR:  duplicate key value violates unique constraint "group_id_unique"
DETAIL:  Key (group_id)=(1) already exists.

INSERT INTO Groups (group_id, group_no)
VALUES
	(3, 'M32381');

update Students set group_id = 5 where student_id = 1;

update Students set group_id = 1 where student_id = 1;

ALTER TABLE Students
      ADD FOREIGN KEY (group_id)
      REFERENCES Groups (group_id);

UPDATE Students SET group_id = 5 WHERE student_id = 1;

psql:/tmp/babel-oESQj7/sql-in-bVa0e3:2: ERROR:  insert or update on table "students" violates foreign key constraint "students_group_id_fkey"
DETAIL:  Key (group_id)=(5) is not present in table "groups".

UPDATE Students SET group_id = 5 WHERE student_id = 1;

INSERT INTO Students (student_id, name, group_id)
VALUES
	(4, 'Илья Ярошевский', 1);

SELECT name, group_no FROM Students NATURAL JOIN Groups;

INSERT INTO Students (student_id, name, group_id)
VALUES
	(4, 'Кучма Андрей Андреевич', 2),
	(5, 'Шехунов Максим Александрович', 2),
	(6, 'Дзестелов Хетаг Артурович', 2),
	(7, 'Жогова Мария Сергеевна', 2);

select count(*) from Groups;

select count(*) from Students;

select count(*)
from Students
where group_id in (select group_id from Groups where group_no = 'M34391');

select count(*)
from Students natural join Groups
where name like '%зе%' or group_no like '%7%';

select count(*)
from Students natural join Groups
where name like '%v' and group_no like '%7%';

DELETE FROM Groups;

psql:/var/folders/pq/5b7h31wx2hl_q9svrzm49gdmtwncy0/T/babel-sCT3PQ/sql-in-EP5kGq:2: ERROR:  update or delete on table "groups" violates foreign key constraint "students_group_id_fkey" on table "students"

DELETE FROM Students;

DELETE FROM Groups;

INSERT INTO Groups (group_id, group_no)
VALUES
	(1, 'M34361'),
	(2, 'M34371'),
	(3, 'M34381'),
	(4, 'M34391');

INSERT INTO Students (student_id, name, group_id)
VALUES
	(1, 'Student 1', 1),

	(2, 'Student 2', 2),
	(3, 'Student 3', 2),
	(4, 'Student 4', 2),
	(5, 'Student 5', 2),

	(6, 'Student 6', 3),
	(7, 'Student 7', 3),
	(8, 'Student 8', 3),

	(9, 'Student 9', 4),
	(10, 'Student 10', 4),

select group_no, count(*)
from Groups g inner join Students s on g.group_id = s.group_id
group by group_no
order by group_no desc;

select group_no, count(*)
from Groups g inner join Students s on g.group_id <> s.group_id
where group_no like '%1'
group by group_no
order by group_no;