DROP SCHEMA IF EXISTS p2 CASCADE;
CREATE SCHEMA p2
AUTHORIZATION lsimbakalia;
DROP TABLE IF EXISTS p2.products CASCADE; -- Contains avoid all bundles as pacakaged and sold
DROP TABLE IF EXISTS p2.purchases CASCADE; -- Contains pallet orders from vendors
DROP TABLE IF EXISTS p2.beverages CASCADE; -- Contains avoid all bundles as pacakaged and sold
DROP TABLE IF EXISTS p2.prod_items CASCADE; -- Contains avoid all individual products in bundles
DROP TABLE IF EXISTS p2.catalog CASCADE; -- Contains menu info available to customer in online catalog
DROP TABLE IF EXISTS p2.orders CASCADE; -- Contains Payment method and Funnel status
DROP TABLE IF EXISTS p2.order_items CASCADE; -- Contains cart contents
DROP TABLE IF EXISTS p2.customers CASCADE; -- Customer information
DROP TABLE IF EXISTS p2.delivery CASCADE; -- Shipping information
-- Tables for Inventory Management
CREATE TABLE p2.products (
prod_id smallint GENERATED ALWAYS AS IDENTITY,
category varchar(20) NOT NULL,
vendor varchar(20) NOT NULL,
CONSTRAINT pk_prod_id PRIMARY KEY (prod_id)
);
CREATE TABLE p2.purchases (
purch_id smallint GENERATED ALWAYS AS IDENTITY,
bev_id smallint NOT NULL, --instead of bev_id because bev_id does not include products that are sold as bundles
quantity smallint NOT NULL DEFAULT 1000,
unit_price char(15) NOT NULL,
purch_date timestamptz NOT NULL DEFAULT ('1/'|| EXTRACT('month' FROM CURRENT_TIMESTAMP) || '/2024')::date,
CONSTRAINT pk_purchases_purchid PRIMARY KEY (purch_id)
);
CREATE TABLE p2.beverages (
bev_id smallint GENERATED ALWAYS AS IDENTITY,
bev_name varchar(30) NOT NULL,
CONSTRAINT pk_bev_id PRIMARY KEY (bev_id)
);
CREATE TABLE p2.prod_items (
prod_id smallint,
bev_id smallint NOT NULL, --FK
quantity smallint NOT NULL DEFAULT 1,
CONSTRAINT pk_pack_dual PRIMARY KEY (prod_id, bev_id)
);
-- Tables for Store UX
CREATE TABLE p2.catalog (
prod_name varchar(50) UNIQUE,
prod_id smallint NOT NULL UNIQUE, --PK, FK
prod_price numeric(5,2) NOT NULL,
stock boolean DEFAULT TRUE, -- Delete, can be calculated with JOINS
CONSTRAINT pk_cat_prodn PRIMARY KEY (prod_name)
);
-- Tables for Conversion and Transaction Information
CREATE TABLE p2.orders (
order_id smallint GENERATED ALWAYS AS IDENTITY,
pay_card char(15) NOT NULL,
customer_id smallint NOT NULL, --FK
order_date timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT pk_orders_orderid PRIMARY KEY (order_id)
);
CREATE TABLE p2.order_items (
item_id smallint GENERATED ALWAYS AS IDENTITY,
order_id smallint NOT NULL,
prod_id smallint NOT NULL,
quantity smallint NOT NULL DEFAULT 1,
CONSTRAINT pk_items_itemid PRIMARY KEY (item_id)
);
-- Tables for order fullfillment
CREATE TABLE p2.customers (
customer_id smallint GENERATED ALWAYS AS IDENTITY,
last_name varchar(25) NOT NULL,
first_name varchar(25) NOT NULL,
address varchar(25) NOT NULL,
zip char(5) NOT NULL,
email varchar(35) NOT NULL UNIQUE,
phone char(10) NOT NULL UNIQUE,
CONSTRAINT pk_cust_id PRIMARY KEY (customer_id)
);
CREATE TABLE p2.delivery (
shipping_id char(20),
order_id smallint NOT NULL, --FK
ship_date timestamptz DEFAULT CURRENT_TIMESTAMP + INTERVAL '3 days',
CONSTRAINT pk_ship_id PRIMARY KEY (shipping_id)
);
-- Establishing Foreign Keys
ALTER TABLE p2.purchases
ADD CONSTRAINT fk_purch_bev_id FOREIGN KEY (bev_id)
REFERENCES p2.beverages (bev_id)
ON UPDATE CASCADE
ON DELETE RESTRICT;
ALTER TABLE p2.catalog
ADD CONSTRAINT fk_cat_prod_id FOREIGN KEY (prod_id)
REFERENCES p2.products (prod_id)
ON UPDATE CASCADE
ON DELETE RESTRICT;
ALTER TABLE p2.prod_items
ADD CONSTRAINT fk_items_bev_id FOREIGN KEY (bev_id)
REFERENCES p2.beverages (bev_id)
ON UPDATE CASCADE
ON DELETE RESTRICT;
ALTER TABLE p2.orders
ADD CONSTRAINT fk_ord_cust_id FOREIGN KEY (customer_id)
REFERENCES p2.customers (customer_id)
ON UPDATE CASCADE
ON DELETE RESTRICT;
ALTER TABLE p2.order_items
ADD CONSTRAINT fk_items_order_id FOREIGN KEY (order_id)
REFERENCES p2.orders (order_id)
ON UPDATE CASCADE
ON DELETE RESTRICT;
ALTER TABLE p2.delivery
ADD CONSTRAINT fk_del_order_id FOREIGN KEY (order_id)
REFERENCES p2.orders (order_id)
ON UPDATE CASCADE
ON DELETE SET NULL;
-- Value Insertion
INSERT INTO p2.products (category, vendor) (
VALUES
(bundle, 'Seedlip'),
(bundle, 'Seedlip'),
(bundle, 'Seedlip'),
(bundle, 'Seedlip'),
(bundle, 'Ritual Zero Proof'),
(bundle, 'Ritual Zero Proof'),
(bundle, 'Ritual Zero Proof'),
(bundle, 'Ritual Zero Proof'),
(bundle, 'Ritual Zero Proof'),
(bundle, 'Kin Euphorics'),
(bundle, 'Kin Euphorics'),
(bundle, 'Kin Euphorics'),
(bundle, 'Kin Euphorics'),
(bundle, 'Kin Euphorics'),
(bundle, 'Kin Euphorics'),
(bundle, 'Kin Euphorics'),
(bundle, 'Kin Euphorics'),
(bundle, 'Kin Euphorics'),
(bundle, 'Kin Euphorics'),
(bundle, 'Kin Euphorics'),
(bundle, 'Kin Euphorics'),
(bundle, 'Kin Euphorics'),
(bundle, 'Kin Euphorics'),
(bundle, 'Kin Euphorics'),
(bundle, 'Three Spirit'),
(bundle, 'Three Spirit'),
(bundle, 'Athletic Brewing Co.'),
(bundle, 'Athletic Brewing Co.'),
(bundle, 'Athletic Brewing Co.'),
(bundle, 'Athletic Brewing Co.'),
(bundle, 'Athletic Brewing Co.'),
(bundle, 'Athletic Brewing Co.'),
(bundle, 'Athletic Brewing Co.'),
(bundle, 'Athletic Brewing Co.'),
(single, 'Seedlip'),
(single, 'Seedlip'),
(single, 'Seedlip'),
(single, 'Seedlip'),
(single, 'Seedlip'),
(single, 'Seedlip'),
(single, 'Seedlip'),
(single, 'Seedlip'),
(single, 'Seedlip'),
(single, 'Seedlip'),
(single, 'Seedlip'),
(single, 'Ritual Zero Proof'),
(single, 'Ritual Zero Proof'),
(single, 'Ritual Zero Proof'),
(single, 'Ritual Zero Proof'),
(single, 'Ritual Zero Proof'),
(single, 'Ritual Zero Proof'),
(single, 'Ritual Zero Proof'),
(single, 'Ritual Zero Proof'),
(single, 'Ritual Zero Proof'),
(single, 'Ritual Zero Proof'),
(single, 'Ritual Zero Proof'),
(single, 'Lyre''s'),
(single, 'Lyre''s'),
(single, 'Lyre''s'),
(single, 'Lyre''s'),
(single, 'Lyre''s'),
(single, 'Lyre''s'),
(single, 'Lyre''s'),
(single, 'Lyre''s'),
(single, 'Lyre''s'),
(single, 'Lyre''s'),
(single, 'Kin Euphorics'),
(single, 'Kin Euphorics'),
(single, 'Kin Euphorics'),
(single, 'Kin Euphorics'),
(single, 'Kin Euphorics'),
(single, 'Monday Distillery'),
(single, 'Monday Distillery'),
(single, 'Monday Distillery'),
(single, 'Monday Distillery'),
(single, 'Monday Distillery'),
(single, 'Monday Distillery'),
(single, 'Monday Distillery'),
(single, 'Monday Distillery'),
(single, 'Monday Distillery'),
(single, 'Monday Distillery'),
(single, 'Three Spirit'),
(single, 'Three Spirit'),
(single, 'Three Spirit'),
(single, 'Three Spirit'),
(single, 'Three Spirit'),
(single, 'Three Spirit'),
(single, 'Three Spirit'),
(single, 'Three Spirit'),
(single, 'Three Spirit'),
(single, 'Three Spirit'),
(single, 'Ceder''s'),
(single, 'Ceder''s'),
(single, 'Ceder''s'),
(single, 'Ceder''s'),
(single, 'Ceder''s'),
(single, 'Ceder''s'),
(single, 'Ceder''s'),
(single, 'Ceder''s'),
(single, 'Ceder''s'),
(single, 'Ceder''s'),
(single, 'Pentire Drinks'),
(single, 'Pentire Drinks'),
(single, 'Pentire Drinks'),
(single, 'Pentire Drinks'),
(single, 'Pentire Drinks'),
(single, 'Pentire Drinks'),
(single, 'Pentire Drinks'),
(single, 'Pentire Drinks'),
(single, 'Pentire Drinks'),
(single, 'Pentire Drinks'),
(single, 'Töst'),
(single, 'Töst'),
(single, 'Töst'),
(single, 'Töst'),
(single, 'Töst'),
(single, 'Töst'),
(single, 'Töst'),
(single, 'Töst'),
(single, 'Töst'),
(single, 'Töst'),
(single, 'Athletic Brewing Co.'),
(single, 'Athletic Brewing Co.'),
(single, 'Athletic Brewing Co.'),
(single, 'Athletic Brewing Co.'),
(single, 'Athletic Brewing Co.'),
(single, 'Athletic Brewing Co.'),
(single, 'Athletic Brewing Co.'),
(single, 'Athletic Brewing Co.')
);
INSERT INTO p2.purchases (prod_id, unit_price) (
VALUES
(1, 100),
(2, 100),
(3, 100),
(4, 66.75),
(5, 87.99),
(6, 48.7125),
(7, 48.7125),
(8, 48.7125),
(9, 48.7125),
(10, 29.25),
(11, 3.75),
(12, 3.75),
(13, 3.75),
(14, 3.75),
(15, 42.75),
(16, 42.75),
(17, 42.75),
(18, 42.75),
(19, 42.75),
(20, 67.5),
(21, 67.5),
(22, 67.5),
(23, 67.5),
(24, 67.5),
(25, 69.99),
(26, 56.25),
(27, 11.24),
(28, 11.24),
(29, 11.24),
(30, 11.24),
(31, 11.24),
(32, 11.24),
(33, 11.24),
(34, 11.24),
(35, 23.99),
(36, 23.99),
(37, 23.99),
(38, 23.99),
(39, 23.99),
(40, 23.99),
(41, 23.99),
(42, 23.99),
(43, 23.99),
(44, 23.99),
(45, 23.99),
(46, 22.49),
(47, 22.49),
(48, 22.49),
(49, 22.49),
(50, 22.49),
(51, 22.49),
(52, 22.49),
(53, 22.49),
(54, 22.49),
(55, 22.49),
(56, 22.49),
(57, 28.49),
(58, 28.49),
(59, 28.49),
(60, 28.49),
(61, 28.49),
(62, 28.49),
(63, 28.49),
(64, 28.49),
(65, 28.49),
(66, 28.49),
(67, 3.75),
(68, 3.75),
(69, 3.75),
(70, 3.75),
(71, 3.75),
(72, 15),
(73, 15),
(74, 15),
(75, 15),
(76, 15),
(77, 15),
(78, 15),
(79, 15),
(80, 15),
(81, 15),
(82, 29.25),
(83, 29.25),
(84, 29.25),
(85, 29.25),
(86, 29.25),
(87, 29.25),
(88, 29.25),
(89, 29.25),
(90, 29.25),
(91, 29.25),
(92, 18.74),
(93, 18.74),
(94, 18.74),
(95, 18.74),
(96, 18.74),
(97, 18.74),
(98, 18.74),
(99, 18.74),
(100, 18.74),
(101, 18.74),
(102, 14.25),
(103, 14.25),
(104, 14.25),
(105, 14.25),
(106, 14.25),
(107, 14.25),
(108, 14.25),
(109, 14.25),
(110, 14.25),
(111, 14.25),
(112, 8.24),
(113, 8.24),
(114, 8.24),
(115, 8.24),
(116, 8.24),
(117, 8.24),
(118, 8.24),
(119, 8.24),
(120, 8.24),
(121, 8.24),
(122, 2.25),
(123, 2.25),
(124, 2.25),
(125, 2.25),
(126, 2.25),
(127, 2.25),
(128, 2.25),
(129, 2.25)
);
INSERT INTO p2.beverages (bev_name) (
VALUES
('Spice 94'),
('Garden 108'),
('Grove 42'),
('Citrus Blend 3'),
('Herbal Blend 0'),
('Spice & Citrus'),
('Spice & Soda'),
('Garden & Tonic'),
('Grove & Tonic'),
('Citrus & Tonic'),
('Notas de Agave'),
('Gin Alternative'),
('Whiskey Alternative'),
('Tequila Alternative'),
('Rum Alternative'),
('Vodka Alternative'),
('Apertif Alternative'),
('Dark & Stormy'),
('Gin & Tonic'),
('Margarita'),
('Old Fashioned'),
('Whiskey Sour'),
('American Malt'),
('Italian Spritz'),
('Dark Cane Spirit'),
('White Cane Spirit'),
('Dry London Spirit'),
('Orange Sec'),
('Spiced Cane Spirit'),
('Aperitif Rosso'),
('Aperitif Dry'),
('Amaretti'),
('Spritz'),
('High Rhode'),
('Dream Light'),
('Dream Light Lavender'),
('Dream Light Citrus'),
('Non-Alcoholic Gin'),
('Non-Alcoholic Rum'),
('Non-Alcoholic Whiskey'),
('Non-Alcoholic Vodka'),
('Non-Alcoholic Tequila'),
('Non-Alcoholic Absinthe'),
('Non-Alcoholic Brandy'),
('Non-Alcoholic Aperitivo'),
('Non-Alcoholic Cachaça'),
('Non-Alcoholic Liqueur'),
('Social Elixir'),
('Livener'),
('Nightcap'),
('Awake'),
('Rest'),
('Mind'),
('Body'),
('Soul'),
('Mellow'),
('Bold'),
('Classic'),
('Wild'),
('Pink Rose'),
('Crisp'),
('Old Fashioned'),
('Sour'),
('Collins'),
('Martini'),
('Cosmopolitan'),
('Mojito'),
('Adrift'),
('Seaward'),
('Pacific'),
('Sea Spray'),
('Seaweed'),
('Rock Samphire'),
('Cornish'),
('Coastal'),
('Botanical'),
('Saltwater'),
('Original'),
('Coconut & Pineapple'),
('Blood Orange & Ginger'),
('Grapefruit & Pomelo'),
('Lemon & Elderflower'),
('Cranberry & Juniper'),
('Lime & Hibiscus'),
('Apple & Cinnamon'),
('Pomegranate & Rose'),
('Pear & Cardamom'),
('Upside Dawn'),
('Run Wild'),
('Free Wave'),
('Cerveza Atletica'),
('All Out'),
('Freewheel'),
('Upside Dawn Coffee'),
('Upside Dawn Lemon')
);
INSERT INTO p2.prod_items (prod_id, bev_id, quantity) (
VALUES
(1, 3, 6),
(2, 11, 6),
(3, 2, 6),
(4, 1, 1),
(4, 2, 1),
(4, 3, 1),
(5, 12, 1),
(5, 13, 1),
(5, 14, 1),
(5, 15, 1),
(5, 17, 1),
(6, 12, 1),
(6, 17, 1),
(7, 14, 1),
(7, 17, 1),
(8, 13, 1),
(8, 17, 1),
(9, 15, 1),
(9, 17, 1),
(10, 33, 8),
(12, 35, 8),
(15, 33, 12),
(17, 35, 12),
(20, 33, 24),
(22, 35, 24),
(25, 48, 1),
(25, 49, 1),
(25, 50, 1),
(26, 49, 1),
(26, 50, 1),
(27, 88, 6),
(28, 89, 6),
(29, 90, 6),
(30, 91, 6),
(31, 92, 6),
(32, 93, 6),
(33, 94, 6),
(34, 95, 6),
(35, 1, 1),
(36, 2, 1),
(37, 3, 1),
(38, 4, 1),
(39, 5, 1),
(40, 6, 1),
(41, 7, 1),
(42, 8, 1),
(43, 9, 1),
(44, 10, 1),
(45, 11, 1),
(46, 12, 1),
(47, 13, 1),
(48, 14, 1),
(49, 15, 1),
(50, 16, 1),
(51, 17, 1),
(52, 18, 1),
(53, 19, 1),
(54, 20, 1),
(55, 21, 1),
(56, 22, 1),
(57, 23, 1),
(58, 24, 1),
(59, 25, 1),
(60, 26, 1),
(61, 27, 1),
(62, 28, 1),
(63, 29, 1),
(64, 30, 1),
(65, 31, 1),
(66, 32, 1),
(67, 33, 1),
(68, 34, 1),
(69, 35, 1),
(70, 36, 1),
(71, 37, 1),
(72, 38, 1),
(73, 39, 1),
(74, 40, 1),
(75, 41, 1),
(76, 42, 1),
(77, 43, 1),
(78, 44, 1),
(79, 45, 1),
(80, 46, 1),
(81, 47, 1),
(82, 48, 1),
(83, 49, 1),
(84, 50, 1),
(85, 51, 1),
(86, 52, 1),
(87, 53, 1),
(88, 54, 1),
(89, 55, 1),
(90, 56, 1),
(91, 57, 1),
(92, 58, 1),
(93, 59, 1),
(94, 60, 1),
(95, 61, 1),
(96, 62, 1),
(97, 63, 1),
(98, 64, 1),
(99, 65, 1),
(100, 66, 1),
(101, 67, 1),
(102, 68, 1),
(103, 69, 1),
(104, 70, 1),
(105, 71, 1),
(106, 72, 1),
(107, 73, 1),
(108, 74, 1),
(109, 75, 1),
(110, 76, 1),
(111, 77, 1),
(112, 78, 1),
(113, 79, 1),
(114, 80, 1),
(115, 81, 1),
(116, 82, 1),
(117, 83, 1),
(118, 84, 1),
(119, 85, 1),
(120, 86, 1),
(121, 87, 1),
(122, 88, 1),
(123, 89, 1),
(124, 90, 1),
(125, 91, 1),
(126, 92, 1),
(127, 93, 1),
(128, 94, 1),
(129, 95, 1)
);
INSERT INTO p2.catalog (prod_name, prod_id, prod_price) (
VALUES
('Seedlip''s Grove 42 6-Pack', 1, 150),
('Seedlip''s Notas de Agave 6-Pack', 2, 150),
('Seedlip''s Garden 108 6-Pack', 3, 150),
('Seedlip''s Special Offer Trio', 4, 89),
('Ritual Zero Proof''s Bar Cart Bundle', 5, 131.99),
('Ritual Zero Proof''s Negroni Cocktail Bundle', 6, 64.95),
('Ritual Zero Proof''s Siesta Cocktail Bundle', 7, 64.95),
('Ritual Zero Proof''s Boulevardier Cocktail Bundle', 8, 64.95),
('Ritual Zero Proof''s Jungle Bird Cocktail Bundle', 9, 64.95),
('Kin Euphorics''s Spritz 8-Pack', 10, 39),
('Kin Euphorics''s High Rhode 8-Pack', 11, 5),
('Kin Euphorics''s Dream Light 8-Pack', 12, 5),
('Kin Euphorics''s Dream Light Lavender 8-Pack', 13, 5),
('Kin Euphorics''s Dream Light Citrus 8-Pack', 14, 5),
('Kin Euphorics''s Spritz 12-Pack', 15, 57),
('Kin Euphorics''s High Rhode 12-Pack', 16, 57),
('Kin Euphorics''s Dream Light 12-Pack', 17, 57),
('Kin Euphorics''s Dream Light Lavender 12-Pack', 18, 57),
('Kin Euphorics''s Dream Light Citrus 12-Pack', 19, 57),
('Kin Euphorics''s Spritz 24-Pack', 20, 90),
('Kin Euphorics''s High Rhode 24-Pack', 21, 90),
('Kin Euphorics''s Dream Light 24-Pack', 22, 90),
('Kin Euphorics''s Dream Light Lavender 24-Pack', 23, 90),
('Kin Euphorics''s Dream Light Citrus 24-Pack', 24, 90),
('Three Spirit''s The Collection', 25, 104.99),
('Three Spirit''s The Get Up/Get Down', 26, 75),
('Athletic Brewing Co.''s Upside Dawn 6-Pack', 27, 14.99),
('Athletic Brewing Co.''s Run Wild 6-Pack', 28, 14.99),
('Athletic Brewing Co.''s Free Wave 6-Pack', 29, 14.99),
('Athletic Brewing Co.''s Cerveza Atletica 6-Pack', 30, 14.99),
('Athletic Brewing Co.''s All Out 6-Pack', 31, 14.99),
('Athletic Brewing Co.''s Freewheel 6-Pack', 32, 14.99),
('Athletic Brewing Co.''s Day Pack 6-Pack', 33, 14.99),
('Athletic Brewing Co.''s Upside Dawn Coffee 6-Pack', 34, 14.99),
('Seedlip''s Spice 94', 35, 31.99),
('Seedlip''s Garden 108', 36, 31.99),
('Seedlip''s Grove 42', 37, 31.99),
('Seedlip''s Citrus Blend 3', 38, 31.99),
('Seedlip''s Herbal Blend 0', 39, 31.99),
('Seedlip''s Spice & Citrus', 40, 31.99),
('Seedlip''s Spice & Soda', 41, 31.99),
('Seedlip''s Garden & Tonic', 42, 31.99),
('Seedlip''s Grove & Tonic', 43, 31.99),
('Seedlip''s Citrus & Tonic', 44, 31.99),
('Seedlip''s Notas de Agave', 45, 31.99),
('Ritual Zero Proof''s Gin Alternative', 46, 29.99),
('Ritual Zero Proof''s Whiskey Alternative', 47, 29.99),
('Ritual Zero Proof''s Tequila Alternative', 48, 29.99),
('Ritual Zero Proof''s Rum Alternative', 49, 29.99),
('Ritual Zero Proof''s Vodka Alternative', 50, 29.99),
('Ritual Zero Proof''s Apertif Alternative', 51, 29.99),
('Ritual Zero Proof''s Dark & Stormy', 52, 29.99),
('Ritual Zero Proof''s Gin & Tonic', 53, 29.99),
('Ritual Zero Proof''s Margarita', 54, 29.99),
('Ritual Zero Proof''s Old Fashioned', 55, 29.99),
('Ritual Zero Proof''s Whiskey Sour', 56, 29.99),
('Lyre''s American Malt', 57, 37.99),
('Lyre''s Italian Spritz', 58, 37.99),
('Lyre''s Dark Cane Spirit', 59, 37.99),
('Lyre''s White Cane Spirit', 60, 37.99),
('Lyre''s Dry London Spirit', 61, 37.99),
('Lyre''s Orange Sec', 62, 37.99),
('Lyre''s Spiced Cane Spirit', 63, 37.99),
('Lyre''s Aperitif Rosso', 64, 37.99),
('Lyre''s Aperitif Dry', 65, 37.99),
('Lyre''s Amaretti', 66, 37.99),
('Kin Euphorics''s Spritz', 67, 5),
('Kin Euphorics''s Bloom', 68, 5),
('Kin Euphorics''s Dream Light', 69, 5),
('Kin Euphorics''s Lightwave', 70, 5),
('Kin Euphorics''s Actual Sunshine', 71, 5),
('Monday Distillery''s Non-Alcoholic Gin', 72, 20),
('Monday Distillery''s Non-Alcoholic Rum', 73, 20),
('Monday Distillery''s Non-Alcoholic Whiskey', 74, 20),
('Monday Distillery''s Non-Alcoholic Vodka', 75, 20),
('Monday Distillery''s Non-Alcoholic Tequila', 76, 20),
('Monday Distillery''s Non-Alcoholic Absinthe', 77, 20),
('Monday Distillery''s Non-Alcoholic Brandy', 78, 20),
('Monday Distillery''s Non-Alcoholic Aperitivo', 79, 20),
('Monday Distillery''s Non-Alcoholic Cachaça', 80, 20),
('Monday Distillery''s Non-Alcoholic Liqueur', 81, 20),
('Three Spirit''s Social Elixir', 82, 39),
('Three Spirit''s Livener', 83, 39),
('Three Spirit''s Nightcap', 84, 39),
('Three Spirit''s Awake', 85, 39),
('Three Spirit''s Rest', 86, 39),
('Three Spirit''s Mind', 87, 39),
('Three Spirit''s Body', 88, 39),
('Three Spirit''s Soul', 89, 39),
('Three Spirit''s Mellow', 90, 39),
('Three Spirit''s Bold', 91, 39),
('Ceder''s Classic', 92, 24.99),
('Ceder''s Wild', 93, 24.99),
('Ceder''s Pink Rose', 94, 24.99),
('Ceder''s Crisp', 95, 24.99),
('Ceder''s Old Fashioned', 96, 24.99),
('Ceder''s Sour', 97, 24.99),
('Ceder''s Collins', 98, 24.99),
('Ceder''s Martini', 99, 24.99),
('Ceder''s Cosmopolitan', 100, 24.99),
('Ceder''s Mojito', 101, 24.99),
('Pentire Drinks''s Adrift', 102, 19),
('Pentire Drinks''s Seaward', 103, 19),
('Pentire Drinks''s Pacific', 104, 19),
('Pentire Drinks''s Sea Spray', 105, 19),
('Pentire Drinks''s Seaweed', 106, 19),
('Pentire Drinks''s Rock Samphire', 107, 19),
('Pentire Drinks''s Cornish', 108, 19),
('Pentire Drinks''s Coastal', 109, 19),
('Pentire Drinks''s Botanical', 110, 19),
('Pentire Drinks''s Saltwater', 111, 19),
('Töst''s Original', 112, 10.99),
('Töst''s Coconut & Pineapple', 113, 10.99),
('Töst''s Blood Orange & Ginger', 114, 10.99),
('Töst''s Grapefruit & Pomelo', 115, 10.99),
('Töst''s Lemon & Elderflower', 116, 10.99),
('Töst''s Cranberry & Juniper', 117, 10.99),
('Töst''s Lime & Hibiscus', 118, 10.99),
('Töst''s Apple & Cinnamon', 119, 10.99),
('Töst''s Pomegranate & Rose', 120, 10.99),
('Töst''s Pear & Cardamom', 121, 10.99),
('Athletic Brewing Co.''s Upside Dawn', 122, 3),
('Athletic Brewing Co.''s Run Wild', 123, 3),
('Athletic Brewing Co.''s Free Wave', 124, 3),
('Athletic Brewing Co.''s Cerveza Atletica', 125, 3),
('Athletic Brewing Co.''s All Out', 126, 3),
('Athletic Brewing Co.''s Freewheel', 127, 3),
('Athletic Brewing Co.''s Upside Dawn Coffee', 128, 3),
('Athletic Brewing Co.''s Upside Dawn Lemon', 129, 3)
);
INSERT INTO p2.customers (last_name, first_name, address, zip, email, phone) (
VALUES
('Jones', 'Emily', '123 Main St', 54321, '[email protected]', 5551234567),
('Smith', 'John', '456 Elm St', 98765, '[email protected]', 5559876542),
('Brown', 'Alexander', '789 Oak St', 13579, '[email protected]', 5552461357),
('Davis', 'Olivia', '321 Pine St', 24680, '[email protected]', 5553692584),
('Wilson', 'Michael', '654 Maple St', 80246, '[email protected]', 5551237892),
('Taylor', 'Sophia', '987 Birch St', 50670, '[email protected]', 5554561236),
('Martinez', 'Daniel', '741 Cedar St', 70315, '[email protected]', 5557894561),
('Anderson', 'Isabella', '852 Pine St', 90817, '[email protected]', 5553216548),
('Thomas', 'William', '963 Elm St', 90210, '[email protected]', 5556549872),
('Jackson', 'Ava', '159 Oak St', 30303, '[email protected]', 5559876544),
('White', 'Noah', '357 Maple St', 40404, '[email protected]', 5551237890),
('Harris', 'Sophia', '753 Birch St', 60606, '[email protected]', 5554561235),
('Clark', 'Ethan', '852 Cedar St', 70707, '[email protected]', 5557894569),
('Lewis', 'Emma', '963 Cedar St', 80808, '[email protected]', 5553216547),
('Walker', 'Aiden', '147 Pine St', 90909, '[email protected]', 5556549871),
('Hall', 'Olivia', '258 Elm St', 10101, '[email protected]', 5559876543),
('Young', 'Liam', '369 Oak St', 20202, '[email protected]', 5551237891),
('King', 'Mia', '741 Cedar St', 30303, '[email protected]', 5554561234),
('Scott', 'Amelia', '852 Pine St', 40404, '[email protected]', 5557894562),
('Adams', 'Lucas', '963 Elm St', 50505, '[email protected]', 5553216549)
);
INSERT INTO p2.orders (pay_card, customer_id) (
VALUES
(453985274891843, 13),
(371674281946721, 8),
(541267983452761, 13),
(601145762839517, 15),
(491628374619246, 16),
(378264197352187, 13),
(526374910284631, 7),
(401756298375619, 16),
(557198346125784, 3),
(353826947162094, 20),
(455627193481526, 10),
(341972836518274, 11),
(601983475261983, 17),
(371659284751093, 4),
(542618739465127, 12),
(492876153829476, 10),
(372948163759102, 13),
(453810294861730, 10),
(601873459612837, 20),
(491827356198372, 18)
);
INSERT INTO p2.order_items (order_id, prod_id, quantity) (
VALUES
(21, 6, 4),
(21, 46, 9),
(21, 69, 3),
(21, 82, 3),
(21, 88, 12),
(21, 98, 1),
(22, 9, 8),
(22, 16, 11),
(22, 19, 5),
(22, 27, 7),
(22, 52, 5),
(22, 59, 2),
(22, 62, 10),
(22, 86, 7),
(22, 112, 7),
(22, 118, 1),
(23, 71, 9),
(24, 3, 7),
(24, 43, 8),
(24, 106, 7),
(24, 126, 8),
(25, 51, 1),
(25, 73, 4),
(25, 123, 8),
(25, 128, 7),
(26, 22, 10),
(26, 26, 6),
(26, 64, 2),
(26, 67, 12),
(26, 83, 6),
(26, 97, 10),
(27, 3, 3),
(27, 57, 8),
(27, 112, 4),
(27, 113, 11),
(28, 2, 4),
(28, 21, 11),
(28, 24, 6),
(28, 69, 7),
(28, 82, 3),
(28, 111, 11),
(29, 2, 7),
(29, 9, 9),
(29, 10, 7),
(29, 21, 11),
(29, 26, 2),
(29, 72, 8),
(30, 20, 6),
(30, 31, 5),
(30, 59, 2),
(30, 68, 10),
(30, 105, 11),
(31, 1, 7),
(31, 43, 8),
(31, 52, 6),
(31, 74, 12),
(31, 88, 5),
(32, 27, 12),
(32, 102, 5),
(33, 72, 1),
(33, 94, 3),
(34, 49, 8),
(34, 127, 1),
(35, 42, 6),
(35, 83, 6),
(35, 87, 9),
(35, 97, 11),
(36, 40, 4),
(36, 47, 10),
(36, 60, 10),
(36, 73, 7),
(36, 93, 2),
(36, 118, 5),
(36, 129, 2),
(37, 4, 11),
(37, 9, 7),
(37, 22, 10),
(37, 61, 10),
(37, 80, 9),
(37, 108, 7),
(37, 121, 6),
(37, 124, 5),
(38, 115, 1),
(39, 10, 4),
(39, 55, 12),
(39, 70, 1),
(39, 78, 1),
(39, 82, 3),
(39, 101, 3),
(39, 102, 5),
(39, 116, 6),
(40, 27, 10),
(40, 64, 5),
(40, 74, 1),
(40, 83, 12),
(40, 103, 5),
(40, 110, 3),
(40, 118, 9)
);
INSERT INTO p2.delivery (shipping_id, order_id) (
VALUES
(55452201069625800000, 35),
(25767789358209500000, 21),
(51500607179519300000, 31),
(46853523439881700000, 38),
(42790258650721300000, 25),
(55653525046092500000, 37),
(42353905257967200000, 24),
(54558528579916000000, 39),
(47536907921276600000, 26),
(92714827207750500000, 32),
(49991411233087900000, 23),
(90666648491812600000, 27),
(76098813488023600000, 29),
(64251650636691400000, 40),
(86340474338212700000, 22),
(96507148866660800000, 30),
(81698915891559900000, 34),
(92116278444722500000, 33),
(96780049361857400000, 28),
(87965582573947500000, 36)
);
-- Capitalizing first letter of first and last name
UPDATE p2.customers SET first_name = INITCAP(first_name);
UPDATE p2.customers SET last_name = INITCAP(last_name);
-- Capitalizing product and beverage names
UPDATE p2.catalog SET prod_name = INITCAP(prod_name);
UPDATE p2.beverages SET bev_name = INITCAP(bev_name); 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;