UPDATE products SET price = 10 WHERE price = 5;

UPDATE products SET price = price * 1.10;

UPDATE products SET price = price + 3000;

UPDATE products SET manufacturer = 'samsung Inc.' WHERE manufacturer = 'samsung';

UPDATE mytable SET a = 5, b = 3, c = 1 WHERE a > 0;

CREATE TABLE bill (bill_id serial PRIMARY KEY, bill text NOT NULL, billdate date NOT NULL DEFAULT CURRENT_DATE);

CREATE TABLE bill_product (bill_id int REFERENCES bill (bill_id) ON UPDATE CASCADE ON DELETE CASCADE, product_id int REFERENCES product (product_id) ON UPDATE CASCADE, amount numeric NOT NULL DEFAULT 1, CONSTRAINT bill_product_pkey PRIMARY KEY (bill_id, product_id) -- explicit pk);

CREATE TABLE product (product_id serial PRIMARY KEY, product text NOT NULL, price numeric NOT NULL DEFAULT 0);

ALTER TABLE products ADD COLUMN description text;

ALTER TABLE products ADD COLUMN description text CHECK (description <> '');

ALTER TABLE products DROP COLUMN description;

ALTER TABLE products DROP COLUMN description CASCADE;

ALTER TABLE products ADD CHECK (name <> '');

ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no);

ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;

ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;

ALTER TABLE products DROP CONSTRAINT some_name;

ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;

ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;

ALTER TABLE products ALTER COLUMN price DROP DEFAULT;

ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);

ALTER TABLE products RENAME COLUMN product_no TO product_number;

ALTER TABLE products RENAME TO items;

DELETE FROM products WHERE manufacturer='apple';

DELETE FROM products;

DELETE FROM products WHERE manufacturer='htc' AND price < 15000;

INSERT INTO products (productname, manufacturer, productcount, price) VALUES ('iphone x', 'apple', 3, 36000), ('iphone 8', 'apple', 2, 41000), ('galaxy s9', 'samsung', 2, 46000), ('galaxy s8 plus', 'samsung', 1, 56000), ('desire 12', 'htc', 5, 28000);

SELECT DISTINCT manufacturer FROM products;

DROP DATABASE usersdb;

CREATE DATABASE usersdb;

CREATE TABLE customers (Id SERIAL PRIMARY KEY, firstname CHARACTER VARYING(20), age int DEFAULT 18 CHECK(age >0 AND age < 100), email CHARACTER VARYING(30) UNIQUE CHECK(email !=''), phone CHARACTER VARYING(20) UNIQUE CHECK(phone !=''));

CREATE TABLE customers

(Id SERIAL PRIMARY KEY, age int DEFAULT 18, firstname CHARACTER VARYING(20), LastName CHARACTER VARYING(20), email CHARACTER VARYING(30) UNIQUE, phone CHARACTER VARYING(20) UNIQUE, CHECK((age >0 AND age<100) AND (email !='') AND (phone !='')));

CASCADE RESTRICT NO ACTION SET NULL SET DEFAULT

ALTER TABLE customers ADD phone CHARACTER VARYING(20) NULL;

ALTER TABLE customers ADD address CHARACTER VARYING(30) NOT NULL DEFAULT 'Неизвестно';

ALTER TABLE customers DROP COLUMN address;

ALTER TABLE customers ALTER COLUMN firstname TYPE VARCHAR(50);

ALTER TABLE customers ADD CHECK (age > 0);

ALTER TABLE customers ADD UNIQUE (email);

INSERT INTO products VALUES (1, 'galaxy s9', 'samsung', 4, 63000)

SELECT * FROM products LIMIT 5;

SELECT productcount AS title, manufacturer, price * productcount AS TotalSum FROM products;

CREATE TABLE products ( product_no int, name text, price numeric CHECK (price > 0) );

CREATE TABLE products (product_no int, name text, price numeric CONSTRAINT positive_price CHECK (price > 0));

CREATE TABLE products (product_no int, name text,price numeric CHECK (price > 0), discounted_price numeric CHECK (discounted_price > 0), CONSTRAINT valid_discount CHECK (price > discounted_price));

CREATE TABLE products (product_no int NOT NULL, name text NOT NULL, price numeric NOT NULL CHECK (price > 0));

CREATE TABLE example (a int, b int, c int, UNIQUE (a, c));

LEFT OUTER JOIN RIGHT OUTER JOIN INNER JOIN CROSS JOIN FULL OUTER JOIN

CREATE TABLE t1 (a int PRIMARY KEY, b int, c int, FOREIGN KEY (b, c) REFERENCES other_table (c1, c2));

SELECT STRING_AGG(productname, ', ') FROM products;

AVG COUNT(*) COUNT(expression) SUM MIN MAx STRING_AGG(expression, delimiter)

DICTINCT LIMIT OFFSET WHERE HAVING UNION AND OR IN NOT BETWEEN UNION ROUND CONCAT

TRUNCATE TABLE products;

ORDER BY manufacturer ASC, productname DESC OFFSET 2;

SELECT Company, COUNT(*) AS Models, productcount FROM products GROUP BY GROUPING SETS(Company, productcount);

SELECT Company, COUNT(*) AS Models, SUM(productcount) AS Units FROM products WHERE price * productcount > 80000 GROUP BY Company HAVING SUM(productcount) > 2 ORDER BY Units DESC;

SELECT firstname, COUNT(Orders.Id) FROM customers JOIN Orders ON Orders.CustomerId = customers.Id GROUP BY customers.Id, customers.firstname;

SELECT products.productname, products.Company, SUM(Orders.productcount * Orders.price) AS TotalSum FROM products LEFT JOIN Orders ON Orders.ProductId = products.Id GROUP BY products.Id, products.productname, products.Company;

SELECT firstname, LastName FROM customers UNION SELECT firstname, LastName FROM Employees;

TIMESTAMP '2004-10-19 10:23:54+02'

SELECT * FROM table_name WHERE lname='abrakov' AND (fname='ivan' OR fname='alex');

TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'

serial smallserial bigserial smallint INTEGER int int4 bigint int8 numeric decimal real float4 double precision float8

timestamp timestamp with time zone date time time with time zone interval

boolean bytea

json jsonb uuid xml

SELECT * FROM table_name WHERE fname='ivan' AND lname='abrakov';

LEFT JOIN tableB ON A.key = B.key

LEFT JOIN tableB ON A.key = B.key WHERE B.key is NULL

RIGHT JOIN tableB ON A.key = B.key

RIGHT JOIN tableB ON A.key = B.key WHERE A.key is NULL

FULL OUTER JOIN tableB ON A.key = B.key

FULL OUTER JOIN tableB ON A.key = B.key WHERE A.key IS NULL OR B.key IS NULL

INNER JOIN tableB ON A.key = B.key