-
-
Save jamesjohnson/ad7c6ed6e44c75d1c722 to your computer and use it in GitHub Desktop.
test.sql
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
DROP USER testdb; | |
CREATE USER testdb; | |
ALTER DATABASE testdb OWNER to testdb; | |
CREATE TABLE companys ( | |
id SERIAL PRIMARY KEY, | |
name TEXT | |
); | |
CREATE TABLE users ( | |
id SERIAL PRIMARY KEY, | |
first_name TEXT, | |
last_name TEXT, | |
company_id INTEGER references companys(id), | |
age INTEGER, | |
created DATE | |
); | |
CREATE TABLE products ( | |
id SERIAL PRIMARY KEY, | |
name TEXT, | |
price INTEGER | |
); | |
CREATE TABLE orders ( | |
id SERIAL PRIMARY KEY, | |
user_id INTEGER references users(id), | |
created DATE | |
); | |
CREATE TABLE order_products ( | |
product_id INTEGER references products(id), | |
order_id INTEGER references orders(id) | |
); | |
INSERT INTO companys(name) VALUES | |
('ACME Company'), | |
('Thinkful'), | |
('Uber'), | |
('LinkedIn'), | |
('Facebook'), | |
('Google'), | |
('Apple'), | |
('Reddit'); | |
INSERT INTO users(first_name, last_name, company_id, age, created) VALUES | |
('James', 'Johnson', 1, 28, '2015-01-10'), | |
('James', 'Schildkrout', 3, 28, '2015-01-10'), | |
('Bob', 'Smith', 1, 20, '2015-01-10'), | |
('Sally', 'Scott', 1, 40, '2015-03-30'), | |
('Michael', 'Douglas', 2, 20, '2015-03-20'), | |
('Michael', 'Apple', 3, 28, '2013-11-10'), | |
('Bob', 'Green', 4, 20, '2014-01-10'), | |
('Dan', 'Green', 5, 40, '2014-01-10'), | |
('Dan', 'Blue', null, 30, '2015-03-10'), | |
('Sam', 'Blue', null, 80, '2015-02-10'); | |
INSERT INTO products(name, price) VALUES | |
('Green Box', 45), | |
('Blue Box', 23), | |
('Red Box', 18), | |
('Yellow Box', 10), | |
('Black Box', 12); | |
INSERT INTO orders(user_id, created) VALUES | |
(1, '2014-01-01'), | |
(1, '2015-01-01'), | |
(2, '2015-03-01'), | |
(3, '2014-04-01'), | |
(3, '2014-08-01'), | |
(4, '2014-09-01'); | |
INSERT INTO order_products(product_id, order_id) VALUES | |
(1, 2), | |
(1, 1), | |
(1, 3), | |
(2, 1), | |
(3, 4), | |
(3, 5), | |
(4, 2), | |
(5, 1), | |
(5, 3); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment