SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE public.klients ( "id_klient" serial PRIMARY KEY, "name" character varying(255), post character varying(150), phone character varying(11), dates date ); CREATE TABLE public.pet ( "id_pet" serial PRIMARY KEY, "id_klient" integer REFERENCES public.klients("id_klient"), "name" character varying(255), "vid" character varying(255), "poroda" character varying(255), year integer, "osobenocti" character varying(255) ); CREATE TABLE public.petsitter ( "id_petsitter" serial PRIMARY KEY, "name" character varying(255), post character varying(150), adres character varying(255), phone character varying(11), pol character varying(10), dolgnost character varying(15), opit integer ); CREATE TABLE public.zaiavka ( "id_zaiavka" serial PRIMARY KEY, "id_petsitter" integer REFERENCES public.petsitter("id_petsitter"), "id_pet" integer REFERENCES public.pet("id_pet"), dates_cozd date, "ysl" character varying(255), dates_ysl date, status character varying(11) ); INSERT INTO public.klients("id_klient","name", post, phone, dates) VALUES ( 1, 'Иванов', 'Ivanov@mail.ru', '89663454455', '2024/08/20') RETURNING *; CREATE TABLE public.otziv ( "id_otziv" serial PRIMARY KEY, "id_klient" integer REFERENCES public.klients("id_klient"), dates date, otsenka integer, comment character varying(300), opisanie_ysl character varying(15) ); INSERT INTO public.pet("id_pet", "id_klient", "name", "vid", "poroda", year, "osobenocti") VALUES (1, 1, 'Ласка', 'Собака', 'Дворняга', 7, 'добрая') RETURNING *; INSERT INTO public.petsitter("id_petsitter","name", post, adres, phone, pol,dolgnost,opit) VALUES ( 1, 'Семенов Семен Семенович', 'Semenov@mail.ru','Киров, Воровского 75Б', '89663454554','муж', 'грумер',10) RETURNING *; INSERT INTO public.zaiavka("id_zaiavka","id_petsitter","id_pet", dates_cozd,"ysl", dates_ysl, status) VALUES (1,1,1,'01.10.2024', 'стрижка','01.10.2024', 'Выполнено') RETURNING *; INSERT INTO public.otziv("id_otziv","id_klient", dates,otsenka, comment, opisanie_ysl) VALUES (1,1,'01.10.2024', 5, 'Лучший мастер!!!', 'Прогулка') RETURNING *; CREATE OR REPLACE VIEW pet_statistics AS SELECT 'Количество питомцев' AS описание, COUNT(*) AS значение FROM public.pet UNION ALL SELECT 'Максимальная оценка', MAX(o.otsenka) FROM public.otziv o UNION ALL SELECT 'Минимальная оценка', MIN(o.otsenka) FROM public.otziv o UNION ALL SELECT 'Средняя оценка', AVG(o.otsenka) FROM public.otziv o WHERE o.otsenka IS NOT NULL UNION ALL SELECT 'Общее количество клиентов', COUNT(DISTINCT k.id_klient) FROM public.klients k; SELECT * FROM pet_statistics; CREATE OR REPLACE VIEW klients_pet AS SELECT k.id_klient AS id_клиента, k.name AS имя_клиента, p.id_pet AS id_животного, p.name AS имя_животного FROM public.klients k JOIN public.pet p ON k.id_klient = p.id_klient; CREATE OR REPLACE VIEW client_reviews AS SELECT k.id_klient AS id_клиента, k.name AS "Имя клиента", o.id_otziv AS "ID отзыва", o.otsenka AS "Оценка", o.comment AS "Комментарий" FROM public.klients k JOIN public.otziv o ON k.id_klient = o.id_klient; CREATE OR REPLACE VIEW zaiavka_petsitter AS SELECT z.id_zaiavka AS "id_zaiavka", p.id_pet AS "id_pet", ps.id_petsitter AS "id_petsitter", ps.name AS "Имя сиделки", z.status AS "status" FROM public.zaiavka z JOIN public.pet p ON z.id_pet = p.id_pet JOIN public.petsitter ps ON z.id_petsitter = ps.id_petsitter; SELECT * FROM klients_pet; SELECT * FROM client_reviews; SELECT * FROM zaiavka_petsitter;