Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
Joins and Case
--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;
run
|
edit
|
history
|
help
1
Ankit
SQL Moving Rating Practice by Han Wang 20200720
SQL - Social Network Analysis by Patrick Lin
Demande complète
Islands demonstration
podd version
OLA
Christmas Tree Generator
PostreSQL: CASE
array comparison