SGAiBW15

1drop table if exists Blacklist cascade;
2drop table if exists Client cascade;
3drop table if exists Item_description cascade;
4drop table if exists Item cascade;
5drop table if exists Moderator cascade;
6drop table if exists Organisation cascade;
7drop table if exists Transaction cascade;
8drop table if exists Verification cascade;
9
10
11create table Client (
12  ID INTEGER PRIMARY KEY,
13  LOGIN VARCHAR(25) NOT NULL,
14  NAME VARCHAR(25) NOT NULL,
15  REGISTRATION DATE NOT NULL,
16  INFO VARCHAR(255)
17);
18
19create table Moderator (
20  ID INTEGER PRIMARY KEY,
21  LOGIN VARCHAR(25) NOT NULL,
22  NAME VARCHAR(25) NOT NULL,
23  HIRE_DATE DATE,
24  INFO VARCHAR(255)
25);
26
27create table Item (
28  ITEM_ID INTEGER PRIMARY KEY ,
29  SELLER_ID INTEGER NOT NULL,
30  DATE_PLACED DATE,
31  CONSTRAINT FK_SELLER_ID FOREIGN KEY (SELLER_ID) REFERENCES CLIENT (ID)
32);
33
34create table Transaction (
35  TRANSACTION_ID INTEGER PRIMARY KEY ,
36  SELLER_ID INTEGER NOT NULL,
37  CUSTOMER_ID INTEGER NOT NULL,
38  ITEM_ID INTEGER NOT NULL,
39  DATE DATE,
40  INFO VARCHAR(255),
41  CONSTRAINT FK_SELLER_ID FOREIGN KEY (SELLER_ID) REFERENCES CLIENT (ID),
42  CONSTRAINT FK_CUSTOMER_ID FOREIGN KEY (CUSTOMER_ID) REFERENCES CLIENT (ID),
43  CONSTRAINT FK_ITEM_ID FOREIGN KEY (ITEM_ID) REFERENCES ITEM (ITEM_ID)
44);
45
46create table Verification (
47  TRANSACTION_ID INTEGER PRIMARY KEY,
48  MODERATOR_ID INTEGER NOT NULL,
49  STATUS VARCHAR(10),
50  COMMENT VARCHAR(255),
51  VERIFIED_DATE DATE NOT NULL,
52  CONSTRAINT FK_MODERATOR_ID FOREIGN KEY (MODERATOR_ID) REFERENCES Moderator (ID),
53  CONSTRAINT CHK_STATUS CHECK ( STATUS = 'ACCEPTED' OR STATUS = 'DENIED' OR STATUS IS NULL),
54  CONSTRAINT FK_ACTION_ID FOREIGN KEY (TRANSACTION_ID) REFERENCES Transaction (TRANSACTION_ID)
55);
56
57
58create table Blacklist (
59  ACTION_ID INTEGER PRIMARY KEY,
60  MODERATOR_ID INTEGER NOT NULL,
61  CLIENT_ID INTEGER NOT NULL,
62  STATUS VARCHAR(10),
63  COMMENT VARCHAR(255),
64  CONSTRAINT FK_MODERATOR_ID FOREIGN KEY (MODERATOR_ID) REFERENCES Moderator (ID),
65  CONSTRAINT FK_CLIENT_ID FOREIGN KEY (CLIENT_ID) REFERENCES Client (ID),
66  CONSTRAINT CHK_STATUS CHECK ( STATUS IS NULL OR STATUS = 'BANNED' OR STATUS = 'SAVED' )
67);
68
69create table Item_description (
70  ITEM_ID INTEGER NOT NULL,
71  PRICE INTEGER NOT NULL,
72  NAME VARCHAR(50) NOT NULL,
73  DESCRIPTION VARCHAR(255),
74
75  VALID_FROM DATE,
76  VALID_TO DATE DEFAULT '9999-01-01',
77
78  CONSTRAINT PK_VERSION PRIMARY KEY (ITEM_ID, VALID_FROM)
79);
80
81INSERT INTO Client (ID, LOGIN, NAME, REGISTRATION, INFO) VALUES (1001, 'Dean', 'Dean Johnson', '2018-04-11', '');
82INSERT INTO Client (ID, LOGIN, NAME, REGISTRATION, INFO) VALUES (1002, 'Fancy', 'Martin Iden', '2017-03-21', '');
83INSERT INTO Client (ID, LOGIN, NAME, REGISTRATION, INFO) VALUES (1003, 'TraZZ', 'Alex Westhood', '2010-11-02', '');
84INSERT INTO Client (ID, LOGIN, NAME, REGISTRATION, INFO) VALUES (1004, 'JJekk76', 'Jack Anderson', '2019-03-21', '');
85INSERT INTO Client (ID, LOGIN, NAME, REGISTRATION, INFO) VALUES (1005, 'VaSSil', 'Mishele Nicolas', '2015-02-02', '');
86INSERT INTO Client (ID, LOGIN, NAME, REGISTRATION, INFO) VALUES (1006, 'flyer.fd', 'Fred Flyer', '2016-07-09', '');
87
88
89INSERT INTO Item (ITEM_ID, SELLER_ID, DATE_PLACED) VALUES (2001, 1002, '2019-04-10');
90INSERT INTO Item (ITEM_ID, SELLER_ID, DATE_PLACED) VALUES (2002, 1002, '2019-04-10');
91INSERT INTO Item (ITEM_ID, SELLER_ID, DATE_PLACED) VALUES (2003, 1002, '2019-04-12');
92INSERT INTO Item (ITEM_ID, SELLER_ID, DATE_PLACED) VALUES (2004, 1004, '2019-03-21');
93INSERT INTO Item (ITEM_ID, SELLER_ID, DATE_PLACED) VALUES (2005, 1005, '2018-11-03');
94
95
96INSERT INTO Item_description (ITEM_ID, PRICE, NAME, DESCRIPTION, VALID_FROM) VALUES (2001, 1999, 'Headphones', 'Simple bluetooth headphones', '2019-04-10');
97INSERT INTO Item_description (ITEM_ID, PRICE, NAME, DESCRIPTION, VALID_FROM) VALUES (2002, 399, 'Headphones adapter', 'Connecter between mobile 4-tics jack 3.5mm and x2 computer 3-tics jacks', '2019-04-10');
98INSERT INTO Item_description (ITEM_ID, PRICE, NAME, DESCRIPTION, VALID_FROM) VALUES (2003, 990, 'Micro SD card', '8 gb micro sd card', '2019-04-12');
99INSERT INTO Item_description (ITEM_ID, PRICE, NAME, DESCRIPTION, VALID_FROM) VALUES (2004, 690, 'Nazi poster', '75x75sm poster with nazi symbols', '2019-03-21');
100INSERT INTO Item_description (ITEM_ID, PRICE, NAME, DESCRIPTION, VALID_FROM) VALUES (2005, 550, '1C merch', 'Stickers, 2 pens and notebook', '2018-11-03');
101
102/* Продавец снизил цену на товар
103
104 */
105UPDATE Item_description SET VALID_TO = '2019-01-06' WHERE ITEM_ID = 2005;
106INSERT INTO Item_description (ITEM_ID, PRICE, NAME, DESCRIPTION, VALID_FROM) VALUES (2005, 350, '1C merch', 'Stickers, 2 pens and notebook', '2019-01-06');
107
108
109INSERT INTO Moderator (ID, LOGIN, NAME, HIRE_DATE, INFO) VALUES (9000, 'Curator', 'Dredd', '2015-11-21', 'Main moderator');
110
111
112INSERT INTO Transaction (TRANSACTION_ID, SELLER_ID, CUSTOMER_ID, ITEM_ID, DATE, INFO) VALUES (3001, 1002, 1001, 2001, '2019-04-15', 'Sell headphones and adapter');
113INSERT INTO Transaction (TRANSACTION_ID, SELLER_ID, CUSTOMER_ID, ITEM_ID, DATE, INFO) VALUES (3002, 1002, 1001, 2002, '2019-04-15', 'Sell headphones and adapter');
114INSERT INTO Transaction (TRANSACTION_ID, SELLER_ID, CUSTOMER_ID, ITEM_ID, DATE, INFO) VALUES (3003, 1005, 1003, 2005, '2019-05-03', 'Sell 1C merch');
115INSERT INTO Transaction (TRANSACTION_ID, SELLER_ID, CUSTOMER_ID, ITEM_ID, DATE, INFO) VALUES (3004, 1004, 1006, 2004, '2019-03-23', 'Sell nazi poster');
116
117
118INSERT INTO Verification (TRANSACTION_ID, MODERATOR_ID, STATUS, COMMENT, VERIFIED_DATE) VALUES (3001, 9000, 'ACCEPTED', 'All clear', '2019-04-15');
119INSERT INTO Verification (TRANSACTION_ID, MODERATOR_ID, STATUS, COMMENT, VERIFIED_DATE) VALUES (3002, 9000, 'ACCEPTED', 'All clear', '2019-04-15');
120INSERT INTO Verification (TRANSACTION_ID, MODERATOR_ID, STATUS, COMMENT, VERIFIED_DATE) VALUES (3003, 9000, 'ACCEPTED', 'All clear', '2019-05-03');
121INSERT INTO Verification (TRANSACTION_ID, MODERATOR_ID, STATUS, COMMENT, VERIFIED_DATE) VALUES (3004, 9000, 'DENIED', 'Violation of market rules', '2019-03-24');
122
123
124INSERT INTO Blacklist (ACTION_ID, MODERATOR_ID, CLIENT_ID, STATUS, COMMENT) VALUES (3004, 9000, 1004, 'BANNED', 'Violation of market rules: propoganda of nazi symbols');
125
126/*
127select * from Client;
128select * from Item;
129select * from Item_description;
130select * from Blacklist;
131select * from Verification;
132select * from Transaction;
133select * from Moderator;
134*/
135
136
137/*  Покупатели, которые совершили покупки с 1 февраля 2019 года
138
139 */
140select Client.LOGIN, Client.NAME
141from Client inner join Transaction on Client.ID = Transaction.SELLER_ID
142where Transaction.DATE >= '2019-02-01' and TRANSACTION_ID not in (select TRANSACTION_ID from Verification where STATUS = 'ACCEPTED')
143
144
145
146/* Для каждого действующего пользователя определяем самый дорогой проданный товар
147
148 */
149select t1.SELLER_ID, t1.NAME, t1.PRICE
150from (Transaction inner join Item_description on Transaction.ITEM_ID = Item_description.ITEM_ID) as t1
151left join (Transaction inner join Item_description on Transaction.ITEM_ID = Item_description.ITEM_ID) as t2
152on t1.SELLER_ID = t2.SELLER_ID and t1.PRICE < t2.PRICE
153where t1.SELLER_ID not in (select CLIENT_ID from Blacklist) and t2.PRICE IS NULL
154group by t1.SELLER_ID, t1.NAME, t1.PRICE;
155
156
157
158/* Найти товары, проданные после снижения их цены
159
160 */
161select t1.NAME, t1.PRICE, t2.PRICE
162from Item_description as t1 left join Item_description as t2
163on t1.ITEM_ID = t2.ITEM_ID and t1.PRICE < t2.PRICE
164where t2.PRICE IS NOT NULL AND t1.ITEM_ID IN
165    (select ITEM_ID from Transaction inner join Verification V on Transaction.TRANSACTION_ID = V.TRANSACTION_ID where V.STATUS = 'ACCEPTED')