R3A6DJfM

1DROP TABLE IF EXISTS employments_resumes;
2DROP TABLE IF EXISTS employments;
3DROP TABLE IF EXISTS resumes;
4DROP TABLE IF EXISTS users;
5
6-- create users table
7
8CREATE TABLE users (
9  id serial PRIMARY KEY,
10  first_name varchar(50),
11  last_name varchar(50),
12  email varchar(50),
13  created_at timestamp NOT NULL DEFAULT current_timestamp
14);
15
16-- create resumes table
17
18CREATE TABLE resumes (
19  id serial PRIMARY KEY,
20  name varchar(50),
21  user_id integer,
22  created_at timestamp NOT NULL DEFAULT current_timestamp
23);
24
25-- create employments table
26
27CREATE TABLE employments (
28  id serial PRIMARY KEY,
29  user_id integer,
30  title varchar(50),
31  organization varchar(50),
32  description varchar(100),
33  start_year integer,
34  end_year integer,
35  created_at timestamp NOT NULL DEFAULT current_timestamp
36);
37
38-- create employments_resumes table
39
40CREATE TABLE employments_resumes (
41  id serial PRIMARY KEY,
42  resume_id integer references resumes(id),
43  employment_id integer references employments(id)
44);
45
46-- POPULATE DATABASE:
47
48-- create Ty Cobb as a user
49INSERT INTO users (first_name, last_name, email) VALUES ('Ty', 'Cobb', 'Ty@baseball.org');
50
51-- create a "First Attempt Resume" for Ty Cobb
52INSERT INTO resumes (name, user_id) VALUES ('First Attempt Resume', (SELECT id FROM users WHERE first_name = 'Ty' AND last_name = 'Cobb'));
53
54-- create employments for Ty Cobb:
55
56-- Detroit Tigers (1905–1926)
57INSERT INTO employments(user_id, title, organization, description, start_year, end_year) VALUES ((SELECT id FROM users WHERE first_name = 'Ty' AND last_name = 'Cobb'), 'player', 'Detroit Tigers', 'Hit the ball, do NOT strike out!', 1905, 1926);
58
59-- Philadelphia Athletics (1927–1928)
60INSERT INTO employments(user_id, title, organization, description, start_year, end_year) VALUES ((SELECT id FROM users WHERE first_name = 'Ty' AND last_name = 'Cobb'), 'player', 'Philadelphia Athletics', 'Hit the ball, do NOT strike out!', 1927, 1928);
61
62-- As manager
63-- Detroit Tigers (1921–1926)
64INSERT INTO employments(user_id, title, organization, description, start_year, end_year) VALUES ((SELECT id FROM users WHERE first_name = 'Ty' AND last_name = 'Cobb'), 'manager', 'Detroit Tigers', 'Tell other people to hit the ball!', 1921, 1926);
65
66-- Associate employments with a resume:
67INSERT INTO employments_resumes (resume_id, employment_id) VALUES (
68  (SELECT id FROM resumes WHERE user_id = (SELECT id FROM users WHERE first_name = 'Ty' AND last_name = 'Cobb')),
69  (SELECT id FROM employments WHERE user_id = (SELECT id FROM users WHERE first_name = 'Ty' AND last_name = 'Cobb') AND title='player' AND organization='Detroit Tigers')
70);
71
72INSERT INTO employments_resumes (resume_id, employment_id) VALUES (
73  (SELECT id FROM resumes WHERE user_id = (SELECT id FROM users WHERE first_name = 'Ty' AND last_name = 'Cobb')),
74  (SELECT id FROM employments WHERE user_id = (SELECT id FROM users WHERE first_name = 'Ty' AND last_name = 'Cobb') AND title='player' AND organization='Philadelphia Athletics')
75);
76
77INSERT INTO employments_resumes (resume_id, employment_id) VALUES (
78  (SELECT id FROM resumes WHERE user_id = (SELECT id FROM users WHERE first_name = 'Ty' AND last_name = 'Cobb')),
79  (SELECT id FROM employments WHERE user_id = (SELECT id FROM users WHERE first_name = 'Ty' AND last_name = 'Cobb') AND title='manager' AND organization='Detroit Tigers')
80);
81
82
83-- create Joe DiMaggio as a user
84INSERT INTO users (first_name, last_name, email) VALUES ('Joe', 'DiMaggio', 'Joe@baseball.org');
85
86-- create a "My only" resume for Joe DiMaggio
87INSERT INTO resumes (name, user_id) VALUES ('My only', (SELECT id FROM users WHERE first_name = 'Joe' AND last_name = 'DiMaggio'));
88
89-- create employments for Joe DiMaggio:
90
91-- New York Yankees (1936–1942, 1946–1951)
92INSERT INTO employments(user_id, title, organization, description, start_year, end_year) VALUES ((SELECT id FROM users WHERE first_name = 'Joe' AND last_name = 'DiMaggio'), 'player', 'New York Yankees', 'Hit the ball every time!', 1936, 1942);
93
94INSERT INTO employments(user_id, title, organization, description, start_year, end_year) VALUES ((SELECT id FROM users WHERE first_name = 'Joe' AND last_name = 'DiMaggio'), 'player', 'New York Yankees', 'Much easier than my time in World War 2...', 1946, 1951);
95
96-- Associate employments with the "My only" resume
97
98INSERT INTO employments_resumes (resume_id, employment_id) VALUES (
99  (SELECT id FROM resumes WHERE user_id = (SELECT id FROM users WHERE first_name = 'Joe' AND last_name = 'DiMaggio')),
100  (SELECT id FROM employments WHERE user_id = (SELECT id FROM users WHERE first_name = 'Joe' AND last_name = 'DiMaggio') AND organization='New York Yankees' AND start_year = 1936)
101);
102
103INSERT INTO employments_resumes (resume_id, employment_id) VALUES (
104  (SELECT id FROM resumes WHERE user_id = (SELECT id FROM users WHERE first_name = 'Joe' AND last_name = 'DiMaggio')),
105  (SELECT id FROM employments WHERE user_id = (SELECT id FROM users WHERE first_name = 'Joe' AND last_name = 'DiMaggio') AND organization='New York Yankees' AND start_year = 1946)
106);
107
108
109-- create Hank Aaron as a user
110INSERT INTO users (first_name, last_name, email) VALUES ('Hank', 'Aaron', 'Hank@baseball.org');
111
112-- create a "My Favorite Rezzy" resume for Hank Aaron
113INSERT INTO resumes (name, user_id) VALUES ('My Favorite Rezzy', (SELECT id FROM users WHERE first_name = 'Hank' AND last_name = 'Aaron'));
114
115-- create employments for Hank Aaron
116
117-- Milwaukee / Atlanta Braves (1954–1974)
118INSERT INTO employments(user_id, title, organization, description, start_year, end_year) VALUES ((SELECT id FROM users WHERE first_name = 'Hank' AND last_name = 'Aaron'), 'player', 'Milwaukee / Atlanta Braves', 'Hit the ball sometimes, sometimes miss the ball.', 1954, 1974);
119-- Milwaukee Brewers (1975–1976)
120INSERT INTO employments(user_id, title, organization, description, start_year, end_year) VALUES ((SELECT id FROM users WHERE first_name = 'Hank' AND last_name = 'Aaron'), 'player', 'Milwaukee Brewers', 'Always hit the ball! Never lose.', 1975, 1976);
121
122-- Associate employments with the "My Favorite Rezzy" resume
123
124INSERT INTO employments_resumes (resume_id, employment_id) VALUES (
125  (SELECT id FROM resumes WHERE user_id = (SELECT id FROM users WHERE first_name = 'Hank' AND last_name = 'Aaron')),
126  (SELECT id FROM employments WHERE user_id = (SELECT id FROM users WHERE first_name = 'Hank' AND last_name = 'Aaron') AND organization='Milwaukee / Atlanta Braves')
127);
128
129INSERT INTO employments_resumes (resume_id, employment_id) VALUES (
130  (SELECT id FROM resumes WHERE user_id = (SELECT id FROM users WHERE first_name = 'Hank' AND last_name = 'Aaron')),
131  (SELECT id FROM employments WHERE user_id = (SELECT id FROM users WHERE first_name = 'Hank' AND last_name = 'Aaron') AND organization='Milwaukee Brewers')
132);
133
134
135-- create Ted Williams as a user
136INSERT INTO users (first_name, last_name, email) VALUES ('Ted', 'Williams', 'Ted@baseball.org');
137
138-- create a "My Favorite Rezzy" resume for Hank Aaron
139INSERT INTO resumes (name, user_id) VALUES ('Player Resume', (SELECT id FROM users WHERE first_name = 'Ted' AND last_name = 'Williams'));
140
141INSERT INTO resumes (name, user_id) VALUES ('Manager Resume', (SELECT id FROM users WHERE first_name = 'Ted' AND last_name = 'Williams'));
142
143-- create employments for Ted Williams
144
145-- Milwaukee / Atlanta Braves (1954–1974)
146INSERT INTO employments(user_id, title, organization, description, start_year, end_year) VALUES ((SELECT id FROM users WHERE first_name = 'Hank' AND last_name = 'Aaron'), 'player', 'Milwaukee / Atlanta Braves', 'Hit the ball sometimes, sometimes miss the ball.', 1954, 1974);
147
148
149-- Boston Red Sox (1939–1942, 1946–1960)
150INSERT INTO employments(user_id, title, organization, description, start_year, end_year) VALUES ((SELECT id FROM users WHERE first_name = 'Ted' AND last_name = 'Williams'), 'player', 'Boston Red Sox', 'Always strive to be the best player on the field.', 1939, 1942);
151
152INSERT INTO employments(user_id, title, organization, description, start_year, end_year) VALUES ((SELECT id FROM users WHERE first_name = 'Ted' AND last_name = 'Williams'), 'player', 'Boston Red Sox', 'So much easier than jumping out of planes fighting the Axis.', 1946, 1960);
153
154-- As manager
155-- Washington Senators / Texas Rangers (1969–1972)
156INSERT INTO employments(user_id, title, organization, description, start_year, end_year) VALUES ((SELECT id FROM users WHERE first_name = 'Ted' AND last_name = 'Williams'), 'manager', 'Boston Red Sox', 'So much easier to coach people than  actually play!', 1969, 1972);
157
158-- Associate employments with the "Player Resume" resume
159INSERT INTO employments_resumes (resume_id, employment_id) VALUES (
160  (SELECT id FROM resumes WHERE user_id = (SELECT id FROM users WHERE first_name = 'Ted' AND last_name = 'Williams') AND name = 'Player Resume'),
161  (SELECT id FROM employments WHERE user_id = (SELECT id FROM users WHERE first_name = 'Ted' AND last_name = 'Williams') AND organization='Boston Red Sox' AND start_year = 1939)
162);
163
164INSERT INTO employments_resumes (resume_id, employment_id) VALUES (
165  (SELECT id FROM resumes WHERE user_id = (SELECT id FROM users WHERE first_name = 'Ted' AND last_name = 'Williams') AND name = 'Player Resume'),
166  (SELECT id FROM employments WHERE user_id = (SELECT id FROM users WHERE first_name = 'Ted' AND last_name = 'Williams') AND organization='Boston Red Sox' AND start_year = 1946)
167);
168
169-- Associate employments with the "Manager Resume" resume
170INSERT INTO employments_resumes (resume_id, employment_id) VALUES (
171  (SELECT id FROM resumes WHERE user_id = (SELECT id FROM users WHERE first_name = 'Ted' AND last_name = 'Williams') AND name = 'Manager Resume'),
172  (SELECT id FROM employments WHERE user_id = (SELECT id FROM users WHERE first_name = 'Ted' AND last_name = 'Williams') AND organization='Boston Red Sox' AND title = 'manager')
173);