XuXiUWkX

1--CREATE DATABASE Sem_1 WITH ENCODING 'UTF-8';
2/*
3CREATE TYPE c_country AS ENUM ('Россия', 'Украина');
4
5CREATE TABLE city
6(
7	city_id SERIAL NOT NULL PRIMARY KEY,
8	name VARCHAR(50) NOT NULL,
9	country c_country
10);
11
12CREATE TABLE users (
13	user_id SERIAL NOT NULL PRIMARY KEY,
14	firstname VARCHAR(50) NOT NULL,
15	surname VARCHAR(50) NOT NULL,
16	city INT,
17	CONSTRAINT users_city_id_fk FOREIGN KEY (city)
18	REFERENCES city (city_id)
19);
20
21DROP TABLE category;
22
23CREATE TABLE IF NOT EXISTS category (
24	category_id SERIAL,
25	name VARCHAR(50) NOT NULL
26);
27
28ALTER TABLE category 
29	ADD PRIMARY KEY (category_id);
30
31CREATE TABLE IF NOT EXISTS post (
32	post_id SERIAL NOT NULL PRIMARY KEY,
33	title VARCHAR(255) NOT NULL,
34	category_id INT,
35	"text" TEXT,
36	CONSTRAINT post_category_id_fk FOREIGN KEY (category_id)
37	REFERENCES category (category_id)
38		ON DELETE CASCADE
39		ON UPDATE NO ACTION
40);
41*/
42
43
44--COPY city("name", country) FROM '/home/thefacetakt/Documents/projects/technotrack/db/data' 
45--	WITH (FORMAT CSV, DELIMITER ';')
46
47ALTER TABLE city RENAME COLUMN "coutry" to "country";
48
49SELECT * FROM CITY;
50
51ALTER TABLE post ADD COLUMN user_id INT NOT NULL;
52
53ALTER TABLE
54	ADD CONSTRAINT post_user_id_fk FOREIGN KEY (user_id)
55		REFERENCES users (user_id);
56
57SELECT u.firstname || ' '  || u.surname author_name
58	FROM users AS u
59	WHERE EXISTS (SELECT 1
60		FROM post sub_p
61			JOIN category sub_c ON sub_c.category_id = sub_p.category_id
62				AND sub_c.name = 'Математика'
63				AND suc_p.user_id = u.user_id)