Black Wall, PostgreSQL

Language: Layout:

--PostgreSQL 9.6
--'\\' is a delimiter

SELECT NOW()::date;

SELECT TO_CHAR(NOW() :: DATE, 'Mon dd, yyyy');

--create product table

CREATE TABLE product( 
    product_id INT PRIMARY KEY NOT NULL, 
    sku VARCHAR (15) NOT NULL, 
    category VARCHAR (15) NOT NULL, 
    date DATE, 
    price NUMERIC (5, 2)
);

--create warehouse table

CREATE TABLE warehouse( 
    id INT  PRIMARY KEY NOT NULL, 
    sku VARCHAR (15),
    upc VARCHAR (20) NOT NULL, 
    quantity INT  NOT NULL 
);

--insert records into product table

INSERT INTO product
(product_id, sku, category,date, price)VALUES 
(101	, 'A113M1',	'Mascara', '2020-04-23', 23),(102,	 'A212M2	', 'Mascara', '2020-02-12', 23),(103,	 'A221NP2',	 'Nail Polish', '2020-03-07', 15), (104,	 'A332NP2',	 'Nail Polish', '2020-05-21', 15),(105	, 'A36LIPG',	 'Lipgloss', '2020-07-15', 19);

--insert records into warehouse table

INSERT INTO warehouse
(id, sku, upc, quantity) VALUES
(111,'A113M1', '3445-113-545', 540),
(222,'A212M2', '3445-212-454', 230),
(333,'A221NP2',	'3445-221-435', 670),
(444,'A332NP2',	'3445-332-003', 168),
(555,'A36LIPG', '3445-336-347', 470);

--query statements

SELECT * FROM product;

SELECT * FROM warehouse;

SELECT * FROM product WHERE price = 23;

SELECT * FROM product WHERE sku = 'A113M1';

SELECT category, price FROM product WHERE category = 'Lipgloss';

SELECT COUNT (*) AS "total records" FROM product;

SELECT * FROM product WHERE price >= 19;

SELECT * FROM product WHERE price IS NOT NULL;

--list all the records that starts with 'Lip'

SELECT * FROM product WHERE category LIKE 'Lip%';

SELECT * FROM product WHERE price NOT IN (15,18);

/*list all records where price value is between 19 and 23*/

SELECT * FROM product WHERE price BETWEEN 19 AND 27;

SELECT AVG (quantity) FROM warehouse;

SELECT SUM (quantity) FROM warehouse;

SELECT MAX (quantity) FROM warehouse;

SELECT MIN (quantity) FROM warehouse;

/*CASE: If quantity is less than 300; reorder inventory
CASE: If quantity is greater than 300; do not reorder*/

SELECT id, upc,
       quantity,
       CASE
           WHEN quantity < 300 THEN 'reorder'
           WHEN quantity > 300 THEN 'stocked'
       END status
FROM warehouse
ORDER BY sku ;

/*full outer join: Returns all records when there is a match in either left or right table*/

SELECT warehouse.sku, warehouse.upc, product.sku, product.date
FROM warehouse 
FULL OUTER JOIN product ON product.sku= warehouse.sku
ORDER BY warehouse.upc;

/*left join: Returns all records from the left table, and the matched records from the right table*/

SELECT product.product_id, warehouse.sku, warehouse.upc
FROM warehouse
LEFT JOIN product ON warehouse.sku=product.sku
ORDER BY warehouse.upc;

/*inner join: returns records that have matching values in both tables*/

SELECT product.sku, warehouse.upc, warehouse.quantity
FROM product
INNER JOIN warehouse ON product.sku= warehouse.sku;

/*right join: Returns all records from the right table, and the matched records from the left table*/

SELECT product.product_id, product.sku, warehouse.upc, warehouse.quantity
FROM product
RIGHT OUTER JOIN warehouse ON product.sku= warehouse.sku
ORDER BY warehouse.upc;


absolute service time: 0,46 sec