The purpose of this repo is to demonstrate some basic to intermediate SQL examples.
The schema is as follows:
-------------------- ------------------ ------------------
| | | | | |
| users |-------------->| orders | | products |
| | | | | |
-------------------- ------------------ ------------------
| |
| |
| |
V |
--------------------- |
| | <--------------|
| orders_products |
| |
---------------------
The SQL is as follows:
CREATE TABLE users (
user_id SERIAL PRIMARY KEY
, first_name TEXT
, last_name TEXT
);
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY
, user_id INTEGER REFERENCES users
, amount NUMERIC(9,2)
);
CREATE TABLE products (
product_id SERIAL PRIMARY KEY
, name TEXT
, unit_price NUMERIC(9,2)
);
CREATE TABLE orders_products (
orders_product_id SERIAL PRIMARY KEY
, order_id INTEGER REFERENCES orders
, product_id INTEGER REFERENCES products
, quantity INTEGER
, cost NUMERIC(9,2)
);
$ createdb sql_exercise
$ psql sql_exercise < schema/sql_exercise.sql