JrR2aiPN

1DROP TABLE IF EXISTS direction CASCADE;
2DROP TABLE IF EXISTS one CASCADE;
3DROP TABLE IF EXISTS tour CASCADE;
4DROP TABLE IF EXISTS agency CASCADE;
5DROP TABLE IF EXISTS customer CASCADE;
6DROP TABLE IF EXISTS own CASCADE;
7
8DROP SEQUENCE IF EXISTS s_direction CASCADE;
9DROP SEQUENCE IF EXISTS s_one CASCADE;
10DROP SEQUENCE IF EXISTS s_tour CASCADE;
11DROP SEQUENCE IF EXISTS s_agency CASCADE;
12DROP SEQUENCE IF EXISTS s_customer CASCADE;
13DROP SEQUENCE IF EXISTS s_own CASCADE;
14
15CREATE SEQUENCE s_direction;
16CREATE SEQUENCE s_one;
17CREATE SEQUENCE s_tour;
18CREATE SEQUENCE s_agency;
19CREATE SEQUENCE s_customer;
20CREATE SEQUENCE s_own;
21
22CREATE TABLE IF NOT EXISTS customer(
23id		INT PRIMARY KEY DEFAULT nextval('s_customer'),
24nam		CHAR(256)	NOT NULL,
25surname		CHAR(256)	NOT NULL,
26id_passport	INT		NOT NULL,
27age		INT		NOT NULL,
28floor		CHAR(256)	NOT NULL
29);
30
31CREATE TABLE IF NOT EXISTS own(
32id		INT PRIMARY KEY DEFAULT nextval('s_own'),
33name_surname	CHAR(256)	NOT NULL,
34patronymic	CHAR(256)	NOT NULL,
35age		INT		NOT NULL,
36floor		CHAR(256)	NOT NULL,
37capital		DECIMAL(50,2)	NOT NULL
38);
39
40CREATE TABLE IF NOT EXISTS direction(
41id			INT PRIMARY KEY DEFAULT nextval('s_direction'),
42country			CHAR(256)	NOT NULL,
43departure_country	CHAR(256)	NOT NULL,
44name_of_travel_agency	CHAR(256)	NOT NULL,
45city			CHAR(256)	NOT NULL,
46flight_time		INT		NOT NULL
47);
48
49CREATE TABLE IF NOT EXISTS agency(
50id		INT PRIMARY KEY DEFAULT nextval('s_agency'),
51agency		CHAR(256)	NOT NULL,
52company_age	INT		NOT NULL,
53number_of_tours	INT		NOT NULL,
54owne		CHAR(256)	NOT NULL
55
56);
57
58CREATE TABLE IF NOT EXISTS tour(
59id		INT PRIMARY KEY DEFAULT nextval('s_tour'),
60agency_name	CHAR(256)	NOT NULL,
61tour_name	CHAR(256)	NOT NULL,
62val		DECIMAL(50,2)	NOT NULL,
63customers	CHAR(256)	NOT NULL,
64amount_of_days	INT		NOT NULL
65
66);
67
68CREATE TABLE IF NOT EXISTS one(
69id_direction	INT PRIMARY KEY DEFAULT nextval('s_one'),
70id_tour		INT	NOT NULL
71);
72
73
74INSERT INTO direction
75VALUES (NEXTVAL('s_direction'), 'Russia', 'Ukraine', 'Kokoko', 'Moskva', 6);
76INSERT INTO direction
77VALUES (NEXTVAL('s_direction'), 'China', 'Japan', 'Lalala', 'Bankok', 6);
78
79INSERT INTO one
80VALUES (NEXTVAL('s_one'), 1);
81INSERT INTO one
82VALUES (NEXTVAL('s_one'), 2);
83
84INSERT INTO tour
85VALUES (NEXTVAL('s_tour'), 'Kokoko', 'Papapa', 1000, 'Petrov', 7);
86INSERT INTO tour
87VALUES (NEXTVAL('s_tour'), 'Lalala', 'Ahahah', 2000, 'Ivanov', 9);
88
89INSERT INTO agency
90VALUES (NEXTVAL('s_agency'), 'Kokoko', 10, 3, 'Pupkin');
91INSERT INTO agency
92VALUES (NEXTVAL('s_agency'), 'Lalala', 7, 4, 'Kovalev');
93
94INSERT INTO customer
95VALUES (NEXTVAL('s_customer'), 'Vasya', 'Petrov', 1234, 10, 'M');
96INSERT INTO customer
97VALUES (NEXTVAL('s_customer'), 'Vitalik', 'Ivanov', 4321, 25, 'M');
98
99INSERT INTO own
100VALUES (NEXTVAL('s_own'), 'Pupkin', 'Noname', '80', 'M', 0);
101INSERT INTO own
102VALUES (NEXTVAL('s_own'), 'Kovalev', 'Nonamovich', '25', 'M', 1000000000);
103SELECT *FROM own;
104
105/*Подзвпрос выводит все туры у которых кол-во дней больше или равно 8
106SELECT * FROM tour t WHERE 8 >= ALL(SELECT t.amount_of_days );*/
107
108/*Выводит все туры у которых названия Агенства совпадает с названиям перелета
109SELECT*FROM tour t WHERE agency_name = ANY(SELECT name_of_travel_agency FROM direction);*/
110
111/*Вывести агенство Kokoko если у него есть вылеты с China
112SELECT * FROM agency WHERE agency='Kokoko' AND EXISTS(SELECT country='China' FROM direction);*/
113
114/*Вывести все имена клиентов которые пользовались услугами компании которой является Kovalev
115SELECT customers FROM tour WHERE agency_name = (SELECT agency FROM agency WHERE owne = 'Kovalev');*/
116
117/*Выводит названия агенств по их владельцам
118SELECT agency FROM agency WHERE owne IN (SELECT name_surname FROM own); */
119
120/*Вывод direction где названия агенста совпадает с Kokoko и время путешествия меньше или равно 8 дней
121SELECT * FROM direction WHERE name_of_travel_agency = (SELECT agency_name FROM tour WHERE agency_name =
122 'Kokoko' AND amount_of_days <=8); */