Skip to content

Latest commit

 

History

History
359 lines (301 loc) · 7.75 KB

README.org

File metadata and controls

359 lines (301 loc) · 7.75 KB

HW 1

В интерактивном: psql Батчево: psql -d <database> -f <file.sql>

Создание базы данных

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;

ORDER BY

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;