add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
--------------------------------------------------------------------------------
-- Taken from: https://github.com/d-roman-halliday/sql-and-data-notes-and-samples/blob/main/oracle/sample_and_test_data/simple_shop_model.sql
--------------------------------------------------------------------------------
-- A simple Shop Model
--
-- Tables:
-- - people
-- - products
-- - shopping_cart_items
-- - shopping_carts

select * from V$VERSION;

BANNER BANNER_FULL BANNER_LEGACY CON_ID
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0		 Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release 0
--------------------------------------------------------------------------------
-- people
--------------------------------------------------------------------------------
--DROP TABLE IF EXISTS people;

CREATE TABLE people
(
person_id NUMBER(38) NOT NULL PRIMARY KEY,
first_name VARCHAR2(26) NOT NULL,
last_name VARCHAR2(26),
email VARCHAR2(26),
phone_number VARCHAR2(26)
);
INSERT ALL
INTO people(person_id, first_name, last_name, email, phone_number) VALUES (1, 'John', 'Doe', 'johndoe@example.com', '555-123-4567')
INTO people(person_id, first_name, last_name, email, phone_number) VALUES (2, 'Jane', 'Smith', 'janesmith@example.com', '555-987-6543')
INTO people(person_id, first_name, last_name, email, phone_number) VALUES (3, 'Robert', 'Johnson', 'robertjohnson@example.com', '555-234-5678')
INTO people(person_id, first_name, last_name, email, phone_number) VALUES (4, 'Emily', 'Wilson', 'emilywilson@example.com', '555-876-5432')
INTO people(person_id, first_name, last_name, email, phone_number) VALUES (5, 'Michael', 'Brown', 'michaelbrown@example.com', '555-345-6789')
SELECT 1 FROM dual;
5 rows affected
--------------------------------------------------------------------------------
-- products
--------------------------------------------------------------------------------
--DROP TABLE IF EXISTS products;

CREATE TABLE products
(
product_id NUMBER(38) NOT NULL PRIMARY KEY,
product_name VARCHAR2(50) NOT NULL,
price NUMBER(38, 2) NOT NULL
);
INSERT ALL
INTO products (product_id, product_name, price) VALUES (101, 'Smartphone', 499.99)
INTO products (product_id, product_name, price) VALUES (102, 'Laptop', 899.99)
INTO products (product_id, product_name, price) VALUES (103, 'Headphones', 99.99)
INTO products (product_id, product_name, price) VALUES (104, 'TV', 799.99)
INTO products (product_id, product_name, price) VALUES (105, 'Tablet', 299.99)
SELECT 1 FROM dual;
5 rows affected
--------------------------------------------------------------------------------
-- shopping_cart_items
--------------------------------------------------------------------------------
--DROP TABLE IF EXISTS shopping_cart_items;

CREATE TABLE shopping_cart_items
(
cart_item_id NUMBER(38) NOT NULL PRIMARY KEY,
cart_id NUMBER(38) NOT NULL,
product_id NUMBER(38) NOT NULL,
quantity NUMBER(38) NOT NULL
);
INSERT ALL
INTO shopping_cart_items (cart_item_id, cart_id, product_id, quantity) VALUES (1, 1, 101, 2)
INTO shopping_cart_items (cart_item_id, cart_id, product_id, quantity) VALUES (2, 1, 103, 1)
INTO shopping_cart_items (cart_item_id, cart_id, product_id, quantity) VALUES (3, 2, 102, 1)
INTO shopping_cart_items (cart_item_id, cart_id, product_id, quantity) VALUES (4, 2, 104, 1)
INTO shopping_cart_items (cart_item_id, cart_id, product_id, quantity) VALUES (5, 2, 105, 1)
INTO shopping_cart_items (cart_item_id, cart_id, product_id, quantity) VALUES (6, 3, 101, 1)
INTO shopping_cart_items (cart_item_id, cart_id, product_id, quantity) VALUES (7, 3, 104, 1)
INTO shopping_cart_items (cart_item_id, cart_id, product_id, quantity) VALUES (8, 4, 102, 1)
INTO shopping_cart_items (cart_item_id, cart_id, product_id, quantity) VALUES (9, 5, 103, 2)
INTO shopping_cart_items (cart_item_id, cart_id, product_id, quantity) VALUES (10, 5, 105, 1)
SELECT 1 FROM dual
;
10 rows affected
--------------------------------------------------------------------------------
-- shopping_carts
--------------------------------------------------------------------------------
--DROP TABLE IF EXISTS shopping_carts;

CREATE TABLE shopping_carts
(
cart_id NUMBER(38) NOT NULL,
person_id NUMBER(38) NOT NULL,
sale_date DATE
);
INSERT ALL
INTO shopping_carts (cart_id, person_id, sale_date) VALUES (1, 1, to_date('2023-10-18', 'YYYY-MM-DD'))
INTO shopping_carts (cart_id, person_id, sale_date) VALUES (2, 2, to_date('2023-10-18', 'YYYY-MM-DD'))
INTO shopping_carts (cart_id, person_id, sale_date) VALUES (3, 3, to_date('2023-10-17', 'YYYY-MM-DD'))
INTO shopping_carts (cart_id, person_id, sale_date) VALUES (4, 4, to_date('2023-10-17', 'YYYY-MM-DD'))
INTO shopping_carts (cart_id, person_id, sale_date) VALUES (5, 5, to_date('2023-10-16', 'YYYY-MM-DD'))
SELECT 1 FROM dual
;
5 rows affected
--------------------------------------------------------------------------------
-- View Data
--------------------------------------------------------------------------------
SELECT
*
FROM
SHOPPING_CARTS
LEFT JOIN SHOPPING_CART_ITEMS USING ( CART_ID )
LEFT JOIN PRODUCTS USING ( PRODUCT_ID )
LEFT JOIN PEOPLE USING ( PERSON_ID )
;
PERSON_ID PRODUCT_ID CART_ID SALE_DATE CART_ITEM_ID QUANTITY PRODUCT_NAME PRICE FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER
1 101 1 18-OCT-23 1 2 Smartphone 499.99 John Doe johndoe@example.com 555-123-4567
3 101 3 17-OCT-23 6 1 Smartphone 499.99 Robert Johnson robertjohnson@example.com 555-234-5678
2 102 2 18-OCT-23 3 1 Laptop 899.99 Jane Smith janesmith@example.com 555-987-6543
4 102 4 17-OCT-23 8 1 Laptop 899.99 Emily Wilson emilywilson@example.com 555-876-5432
1 103 1 18-OCT-23 2 1 Headphones 99.99 John Doe johndoe@example.com 555-123-4567
5 103 5 16-OCT-23 9 2 Headphones 99.99 Michael Brown michaelbrown@example.com 555-345-6789
2 104 2 18-OCT-23 4 1 TV 799.99 Jane Smith janesmith@example.com 555-987-6543
3 104 3 17-OCT-23 7 1 TV 799.99 Robert Johnson robertjohnson@example.com 555-234-5678
2 105 2 18-OCT-23 5 1 Tablet 299.99 Jane Smith janesmith@example.com 555-987-6543
5 105 5 16-OCT-23 10 1 Tablet 299.99 Michael Brown michaelbrown@example.com 555-345-6789