votr-api/votr.sql at master · shoemakerdr/votr-api · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
DROP DATABASE IF EXISTS votr;
CREATE DATABASE votr;

\c votr;

CREATE TABLE users (
    username VARCHAR(50) PRIMARY KEY,
    password VARCHAR(60) NOT NULL
);

CREATE TABLE polls (
    poll_id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    username VARCHAR(50) NOT NULL
);

CREATE TABLE options (
    option_id SERIAL PRIMARY KEY,
    option VARCHAR(255) NOT NULL,
    poll_id SERIAL NOT NULL
);

CREATE TABLE votes (
    vote_id SERIAL PRIMARY KEY,
    poll_id SERIAL NOT NULL,
    option_id SERIAL NOT NULL
);

INSERT INTO polls (title, username)
VALUES (
    'What is your favorite color?',
    'derek'
);

INSERT INTO polls (title, username)
VALUES (
    'What is your favorite animal?',
    'derek'
);

INSERT INTO options (option, poll_id)
VALUES (
    'blue',
    (SELECT poll_id FROM polls WHERE title = 'What is your favorite color?')
);

INSERT INTO options (option, poll_id)
VALUES (
    'red',
    (SELECT poll_id FROM polls WHERE title = 'What is your favorite color?')
);

INSERT INTO options (option, poll_id)
VALUES (
    'green',
    (SELECT poll_id FROM polls WHERE title = 'What is your favorite color?')
);

INSERT INTO options (option, poll_id)
VALUES (
    'dog',
    (SELECT poll_id FROM polls WHERE title = 'What is your favorite animal?')
);

INSERT INTO options (option, poll_id)
VALUES (
    'cat',
    (SELECT poll_id FROM polls WHERE title = 'What is your favorite animal?')
);

INSERT INTO options (option, poll_id)
VALUES (
    'capybara',
    (SELECT poll_id FROM polls WHERE title = 'What is your favorite animal?')
);