DROP TABLE IF EXISTS books;
CREATE TABLE books (
book_code serial PRIMARY KEY,
author character varying(80),
name character varying(160),
publisher character varying(80),
price money,
remains smallint
);
INSERT INTO books (author, name, publisher, price, remains)
VALUES
('Михаил Булгаков', 'Мастер и Маргарита', 'Эксмо — АСТ', 10, '1'),
('Лев Толстой', 'Война и Мир', 'Эксмо — АСТ', 11, '10'),
('Федор Достоевский', 'Преступление и наказание', 'Эксмо — АСТ', 12, '2'),
('Николай Гоголь', 'Вечера на хуторе близ Диканьки', 'Эксмо — АСТ', 13, '9'),
('Александр Солженицын', 'Архипелаг ГУЛАГ', 'ИП Просвещение', 14, '3'),
('Лев Толстой', 'Анна Каренина', 'ИП Просвещение', 15, '8'),
('Михаил Шолохов', 'Тихий Дон', 'ИП Просвещение', 9, '4'),
('Борис Пастернак', 'Доктор Живаго', 'Азбука – Аттикус', 8, '7'),
('Михаил Лермонтов', 'Герой нашего времени', 'Азбука – Аттикус', 7, '5'),
('Александр Пушкин', 'Евгений Онегин', 'Фламинго', 6, '6'),
('F. Scott Fitzgerald', 'The Great Gatsby', 'Hachette Book Group', 10, '11'),
('Harper Lee', 'To Kill a Mockingbird', 'HarperCollins', 10, '20'),
('Mark Twain', 'The Adventures of Tom Sawyer', 'Macmillan Publishers', 10, '12'),
('Ernest Hemingway', 'The Old Man and the Sea', 'Penguin Random House', 10, '19'),
('George Orwell', '1984', 'Hachette Book Group', 10, '13'),
('J. D. Salinger', 'The Catcher in the Rye', 'HarperCollins', 10, '18'),
('John Steinbeck', 'Of Mice and Men', 'Macmillan Publishers', 10, '14'),
('John Steinbeck, Robert DeMott', 'The Grapes of Wrath', 'Hachette Book Group', 10, '17'),
('Kurt Vonnegut and Kevin Power', 'Slaughterhouse-Five', 'HarperCollins', 10, '15'),
('Jack London', 'White fang', 'Hachette Book Group', 10, '16');
-- SELECT * FROM books;
DROP TABLE IF EXISTS suppliers;
CREATE TABLE suppliers (
supplier_code serial PRIMARY KEY,
name character varying(40) UNIQUE NOT NULL,
city character varying(40),
address character varying(80),
telephone character(13) DEFAULT '000 111-11-11',
info character varying(40)
);
INSERT INTO suppliers (name, city, address, telephone, info)
VALUES
('ОптНск', 'Новосибирск', 'Сибирская 42, оф. 25', '950 590-34-12', 'NULL'),
('ОптБел', 'Белово', 'Подсобная 33, оф. 3', '999 648-78-52', 'info'),
('Книжная поставка', 'Новосибирск', 'Ленина 5, оф. 3', '999 666-11-00', 'info'),
('Пост', 'Новосибирск', 'Ядринцевская 18, оф. 202', '913 913-13-13', 'info'),
('Бук', 'Омск', 'Ленина 3, оф. 42', '953 911-34-56', 'info'),
('Топ', 'Новосибирск', 'Шевченко 12, оф. 34', '987 654-32-10', 'info'),
('Северо-Восток', 'Томск', 'Красный пр. 44, оф. 4', '961 714-40-04', 'info'),
('Централ', 'Новосибирск', 'Красный пр. 26, оф. 11', '912 384-10-01', 'info'),
('Юг', 'Барнаул', 'Эмилии Алексеевой 6, оф. 322', '961 642-24-54', 'NULL'),
('Нск', 'Новосибирск', 'Дуси Ковальчук 250, оф. 200', '912 123-58-99', 'info'),
('ПрессФ', 'Новосибирск', 'Серебрениковская 42, оф. 1', '911 745-01-10', 'info'),
('ТуПэй', 'Новосибирск', 'Спортивная 32, оф. 88', '951 363-54-18', 'info'),
('Респект', 'Новосибирск', 'Тюменская 6, оф. 11', '951 034-01-23', 'info'),
('Сибирь', 'Новосибирск', 'Богдана Хмельницкого 23, оф. 1', '998 012-22-33', 'info'),
('Авангард', 'Омск', 'Центральная 5, оф. 11', '912 333-12-21', 'info'),
('Металлург', 'Новокузнецк', 'Дзержинская 1, оф. 101', '911 020-30-40', 'info'),
('Новая Сибирь', 'Новосибирск', 'Немировича-Данченко 160, оф. 18', '974 111-93-33', 'info'),
('Томь', 'Томск', 'Новый переулок 1, оф. 10', '984 001-11-10', 'info'),
('Фантазия', 'Новосибирск', NULL, NULL, NULL),
('Интернационал', 'Москва', NULL, '999 782-82-82', 'info');
-- SELECT * FROM suppliers;
DROP TABLE IF EXISTS customers;
CREATE TABLE customers (
customer_code serial PRIMARY KEY,
name character varying(40),
city character varying(40),
address character varying(80),
telephone character(13) DEFAULT '000 111-11-11'
);
INSERT INTO customers (name, city, address, telephone)
VALUES
('Черепанов Георгий Андреевич', 'Новосибирск', 'Красный проспект 1, кв. 1', '999 111-00-01'),
('Митрофанов Илья Ярославович', 'Новосибирск', 'Красный проспект 2, кв. 2', '999 111-00-02'),
('Кузьмин Даниил Максимович', 'Новосибирск', 'Красный проспект 3, кв. 3', '999 111-00-03'),
('Кузьмина Варвара Сергеевна', 'Новосибирск', 'Красный проспект 4, кв. 4', '999 111-00-04'),
('Дементьева Евгения Константиновна', 'Новосибирск', 'Красный проспект 5, кв. 5', '999 111-00-05'),
('Бычкова Валерия Матвеевна', 'Новосибирск', 'Красный проспект 6, кв. 6', '999 111-00-06'),
('Иванова Арина Андреевна', 'Новосибирск', 'Красный проспект 7, кв. 7', '999 111-00-07'),
('Кузьмин Андрей Павлович', 'Новосибирск', 'Красный проспект 8, кв. 8', '999 111-00-08'),
('Романов Фёдор Даниилович', 'Новосибирск', 'Красный проспект 9, кв. 9', '999 111-00-09'),
('Баранова Александра Максимовна', 'Новосибирск', 'Красный проспект 10, кв. 10', '999 111-00-10'),
('Федотов Даниил Фёдорович', 'Новосибирск', 'Красный проспект 11, кв. 11', '999 111-00-11'),
('Григорьев Егор Михайлович', 'Новосибирск', 'Красный проспект 12, кв. 12', '999 111-00-12'),
('Дмитриев Тихон Дмитриевич', 'Новосибирск', 'Красный проспект 13, кв. 13', '999 111-00-13'),
('Королева Маргарита Тиграновна', 'Новосибирск', 'Красный проспект 14, кв. 14', '999 111-00-14'),
('Воробьева Алиса Алексеевна', 'Бердск', 'Красный проспект 15, кв. 15', '999 111-00-15'),
('Кочетков Александр Денисович', 'Бердск', 'Красный проспект 16, кв. 16', '999 111-00-16'),
('Панфилов Денис Тимофеевич', 'Бердск', 'Красный проспект 17, кв. 17', '999 111-00-17'),
('Чеботарев Александр Лукич', 'Бердск', 'Красный проспект 18, кв. 18', '999 111-00-18'),
('Васильева Полина Демидовна', 'Бердск', 'Красный проспект 19, кв. 19', '999 111-00-19'),
('Воронов Павел Тимурович', 'Бердск', 'Красный проспект 20, кв. 20', '999 111-00-20');
-- SELECT * FROM customers;
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
order_code serial PRIMARY KEY,
book_code integer REFERENCES books(book_code),
customer_code integer REFERENCES customers(customer_code),
summ money,
prepayment_amount money,
paid character varying(3) CHECK(paid = 'Yes' OR paid = 'No') NOT NULL,
date1 date DEFAULT (NOW() at time zone 'ALMST') NOT NULL
);
INSERT INTO orders (book_code, customer_code, summ, prepayment_amount, paid, date1)
VALUES
(10, 10, 10, 9, 'Yes', '01/10/2022'),
(20, 10, 10, 2, 'Yes', '02/11/2022'),
('2', '11', '11', 3, 'Yes', '03/10/2022'),
('19', '8', '10', 4, 'Yes', '04/11/2022'),
('3', '12', '12', 5, 'Yes', '01/10/2022'),
('18', '7', '10', 6, 'Yes', '02/11/2022'),
('4', '13', '13', 7, 'No', '03/10/2022'),
('17', '6', '10', 8, 'Yes', '01/11/2022'),
('5', '14', '14', 9, 'Yes', '02/10/2022'),
('16', '5', '10', 10, 'Yes', '01/11/2022'),
('6', '15', '15', 10, 'No', '03/10/2022'),
('15', '4', '10', 9, 'Yes', '01/11/2022'),
('7', '16', '9', 8, 'No', '04/10/2022'),
('14', '4', '10', 7, 'Yes', '01/11/2022'),
('8', '17', '8', 6, 'Yes', '01/10/2022'),
('13', '2', '10', 5, 'Yes', '03/11/2022'),
('9', '18', '7', 4, 'Yes', '01/10/2022'),
('12', '20', '10', 3, 'No', '01/11/2022'),
('11', '19', '10', 2, 'No', '01/10/2022'),
('10', '1', '6', 1, 'Yes', '02/11/2022');
-- SELECT * FROM orders;
DROP TABLE IF EXISTS supplies;
CREATE TABLE supplies (
number serial PRIMARY KEY,
book_code integer REFERENCES books(book_code),
supplier_code integer REFERENCES suppliers(supplier_code),
quantity integer,
date2 date
);
INSERT INTO supplies (book_code, supplier_code, quantity, date2)
VALUES
('6', '4', '8', '01/09/2022'),
('7', '3', '4', '01/09/2022'),
('8', '2', '7', '01/09/2022'),
('9', '1', '5', '01/09/2022'),
('10', '10', '6', '02/09/2022'),
('1', '9', '1', '01/09/2022'),
('2', '8', '10', '02/09/2022'),
('3', '7', '2', '01/09/2022'),
('4', '6', '9', '01/09/2022'),
('5', '5', '3', '01/09/2022'),
('16', '20', '18', '03/09/2022'),
('17', '20', '14', '03/09/2022'),
('18', '20', '17', '03/09/2022'),
('19', '20', '15', '03/09/2022'),
('11', '20', '11', '03/09/2022'),
('12', '20', '20', '03/09/2022'),
('13', '20', '12', '03/09/2022'),
('14', '20', '19', '03/09/2022'),
('15', '20', '13', '03/09/2022'),
('20', '20', '16', '03/09/2022');
-- SELECT * FROM supplies;
SELECT name, author, price FROM books;
SELECT DISTINCT author FROM books;
SELECT * FROM orders WHERE (prepayment_amount >= '5');
SELECT * FROM orders WHERE prepayment_amount >= summ*0.3;
SELECT * FROM orders WHERE paid = 'Yes' AND customer_code = 10 ;
SELECT * FROM customers WHERE city = 'Бердск';
SELECT books.book_code, price, supplier_code FROM supplies, books
WHERE books.book_code = supplies.book_code;
SELECT * FROM books WHERE (price < '10' OR NOT (author IS NULL AND remains > 50));
SELECT * FROM orders where (date1 > '02/10/22' or date1 < '03/10/22');
SELECT * FROM orders;
SELECT * FROM customers WHERE name LIKE 'В%';
SELECT * FROM customers WHERE LEFT(name, 1) BETWEEN 'А' AND 'М';
SELECT * FROM books WHERE name LIKE '% и %' OR name LIKE '% and %';
SELECT * FROM suppliers WHERE info = 'NULL';
SELECT * FROM orders WHERE orders.date1 = '2022-03-10';
SELECT SUM(books.price) FROM books, orders
WHERE books.book_code=orders.book_code
AND orders.date1='2022-03-10';
SELECT COUNT(address) as has_address FROM suppliers WHERE address IS NOT NULL;
SELECT COUNT(telephone) as has_telephone FROM suppliers WHERE telephone IS NOT NULL;
SELECT name, MIN(prepayment_amount) FROM orders, customers
WHERE orders.customer_code = customers.customer_code
GROUP BY name;
SELECT name
FROM Customers
WHERE name like 'К%'
ORDER BY name;
SELECT summ, date1 FROM orders WHERE orders.date1
BETWEEN '2022-02-10' AND '2022-04-10';
SELECT SUM(books.price) FROM books, orders
WHERE books.book_code=orders.book_code
AND orders.date1 BETWEEN '2022-02-10' AND '2022-04-10';
ALTER TABLE orders ALTER COLUMN prepayment_amount TYPE numeric(5,2);
SELECT prepayment_amount, date1 FROM orders WHERE orders.date1
BETWEEN '01/01/2022' AND '01/31/2022';
\echo '\nJanuary average: '
SELECT Round(AVG(prepayment_amount), 2) FROM orders
WHERE orders.date1 BETWEEN '01/01/2022' AND '01/31/2022';
SELECT prepayment_amount, date1 FROM orders WHERE orders.date1
BETWEEN '02/01/2022' AND '02/28/2022';
\echo '\nFebruary average: '
SELECT Round(AVG(prepayment_amount), 2) FROM orders
WHERE orders.date1 BETWEEN '02/01/2022' AND '02/28/2022';
SELECT prepayment_amount, date1 FROM orders WHERE orders.date1
BETWEEN '03/01/2022' AND '03/31/2022';
\echo '\nMarch average: '
SELECT Round(AVG(prepayment_amount), 2) FROM orders
WHERE orders.date1 BETWEEN '03/01/2022' AND '03/31/2022';
SELECT prepayment_amount, date1 FROM orders WHERE orders.date1
BETWEEN '04/01/2022' AND '04/30/2022';
\echo '\nApril average: '
SELECT Round(AVG(prepayment_amount), 2) FROM orders
WHERE orders.date1 BETWEEN '04/01/2022' AND '04/30/2022';
ALTER TABLE orders ALTER COLUMN prepayment_amount TYPE money;
SELECT SUM(summ), date1 FROM orders
GROUP BY date1 ORDER BY SUM DESC;
SELECT name, SUM(quantity) FROM suppliers, supplies
WHERE supplies.supplier_code = suppliers.supplier_code
GROUP BY name ORDER BY SUM DESC;
ALTER TABLE orders ALTER COLUMN prepayment_amount TYPE numeric(5,2);
SELECT prepayment_amount, date1 FROM orders;
\echo '\nAverage prepayment_amount: '
SELECT Round(AVG(prepayment_amount), 2) FROM orders;
SELECT order_code, prepayment_amount FROM orders WHERE
prepayment_amount > (SELECT AVG(prepayment_amount) FROM orders);
ALTER TABLE orders ALTER COLUMN prepayment_amount TYPE money;
SELECT supplier_code, COUNT(name) AS kinds_of_books,
SUM(remains) AS remain_of_books
FROM books, supplies
WHERE supplies.book_code = books.book_code
GROUP BY supplier_code
ORDER BY remain_of_books DESC;
SELECT order_code, paid, date1, customers.city FROM orders, customers
WHERE date1 BETWEEN '01/01/2022' AND '03/31/2022'
AND customers.city = 'Бердск'
AND orders.customer_code = customers.customer_code;
SELECT paid, COUNT(paid) FROM orders, customers
WHERE date1 BETWEEN '01/01/2022' AND '03/31/2022'
AND customers.city = 'Бердск'
AND orders.customer_code = customers.customer_code
GROUP BY paid;
SELECT name, city FROM customers WHERE customers.city = (
SELECT city FROM orders, customers WHERE paid='Yes'
AND prepayment_amount =
(SELECT MAX(prepayment_amount)
FROM orders, customers WHERE paid='Yes'
AND orders.customer_code = customers.customer_code)
AND orders.customer_code = customers.customer_code);
SELECT name, price, price*1.12 as price_plus_12_percent FROM books;
SELECT name, price*1.12 as price_plus_12_percent FROM books
WHERE price*1.12 > '10';
ALTER TABLE orders ALTER COLUMN summ TYPE numeric(5,2);
SELECT name, SUM(summ), COUNT(summ) FROM orders, customers
WHERE orders.customer_code = customers.customer_code
GROUP BY name;
SELECT ROUND(AVG(summ), 2) FROM orders, customers
WHERE orders.customer_code = customers.customer_code;
SELECT name, sum, count FROM
(SELECT name, SUM(summ), COUNT(summ) FROM orders, customers
WHERE orders.customer_code = customers.customer_code
GROUP BY name)
as aaa WHERE sum >
(SELECT ROUND(AVG(summ), 2) FROM orders, customers
WHERE orders.customer_code = customers.customer_code);
ALTER TABLE orders ALTER COLUMN summ TYPE money;
SELECT name, orders.customer_code, SUM(summ), COUNT(summ), city
FROM orders, customers
WHERE orders.customer_code = customers.customer_code
GROUP BY name, city, orders.customer_code;
SELECT MAX(sum), city FROM
(SELECT name, orders.customer_code, SUM(summ), COUNT(summ), city
FROM orders, customers
WHERE orders.customer_code = customers.customer_code
GROUP BY name, city, orders.customer_code)
as ab GROUP BY city;
SELECT DISTINCT name, customers.customer_code, customers.city
FROM customers, orders
WHERE (city = ANY (SELECT city FROM suppliers))
AND (book_code = ANY (SELECT book_code FROM supplies));
SELECT * FROM books
WHERE book_code = ANY(SELECT book_code FROM orders);
SELECT * FROM books
WHERE NOT (book_code = ANY(SELECT book_code FROM orders));
SELECT customer_code, name FROM customers
WHERE customer_code = ANY (SELECT customer_code FROM orders WHERE paid='No');
ALTER TABLE orders ALTER COLUMN prepayment_amount TYPE numeric(5,2);
SELECT ROUND(AVG(prepayment_amount), 2) as average FROM orders;
SELECT DISTINCT customers.customer_code, name, prepayment_amount, 'High rate' as comment
FROM customers, orders
GROUP BY customers.customer_code, prepayment_amount, orders.customer_code
HAVING (SUM(prepayment_amount) > (SELECT AVG(prepayment_amount) FROM orders))
AND (customers.customer_code = orders.customer_code)
union
SELECT DISTINCT customers.customer_code, name, prepayment_amount, 'Low rate' as comment
FROM customers, orders
GROUP BY customers.customer_code, prepayment_amount, orders.customer_code
HAVING (SUM(prepayment_amount) <= (SELECT AVG(prepayment_amount) FROM orders))
AND (customers.customer_code = orders.customer_code);
ALTER TABLE orders ALTER COLUMN prepayment_amount TYPE money;
Write, Run & Share PostgreSQL queries online using OneCompiler's PostgreSQL online editor and compiler for free. It's one of the robust, feature-rich online editor and compiler for PostgreSQL. Getting started with the OneCompiler's PostgreSQL editor is really simple and pretty fast. The editor shows sample boilerplate code when you choose database as 'PostgreSQL' and start writing queries to learn and test online without worrying about tedious process of installation.
PostgreSQL is a open source relational database system and is also knows as Postgres.
CREATE command is used to create a table, schema or an index.
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
....);
ALTER command is used to add, modify or delete columns or constraints from the database table.
ALTER TABLE Table_name ADD column_name datatype;
TRUNCATE command is used to delete the data present in the table but this will not delete the table.
TRUNCATE table table_name;
DROP command is used to delete the table along with its data.
DROP TABLE table_name;
RENAME command is used to rename the table name.
ALTER TABLE table_name1 RENAME to new_table_name1;
INSERT Statement is used to insert new records into the database table.
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
Select statement is used to select data from database tables.
SELECT column1, column2, ...
FROM table_name;
UPDATE statement is used to modify the existing values of records present in the database table.
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
DELETE statement is used to delete the existing records present in the database table.
DELETE FROM table_name where condition;