lab4
.sql
keyboard_arrow_up
School
University of Washington *
*We aren’t endorsed by this school
Course
330
Subject
Information Systems
Date
Dec 6, 2023
Type
sql
Pages
2
Uploaded by UltraIron12989
-- Question 1
--(1a)--
DROP TABLE IF EXISTS results;
DROP TABLE IF EXISTS questions;
DROP TABLE IF EXISTS responses;
select * from alzheimers;
CREATE TABLE results (
id BIGINT,
loc_abbrev VARCHAR(20),
source VARCHAR(20),
class TEXT,
topic TEXT,
question_id VARCHAR(20),
PRIMARY KEY (id, question_id)
);
--(1b) The primary set of keys for the results table would be results(id,
question_id). I picked these attributes
-- as the primary keys because it was givben that (id, question_id) would be
multikeys in the questions and responses table.
-- This tells us that they are likely referencing the results table, thus, they
would be the primary keys there.
INSERT INTO results(id, loc_abbrev, source, class, topic, question_id)
(SELECT id, loc_abbrev, source, class, topic, question_id
FROM alzheimers);
CREATE TABLE questions (
id BIGINT,
question_id VARCHAR(20),
question TEXT,
PRIMARY KEY (id, question_id),
FOREIGN KEY (id, question_id)
REFERENCES results(id, question_id)
);
INSERT INTO questions(id, question_id, question)
(SELECT id, question_id, question
FROM alzheimers);
CREATE TABLE responses (
id BIGINT,
question_id VARCHAR(20),
data_value VARCHAR(100),
data_value_unit VARCHAR(20),
PRIMARY KEY (id, question_id),
FOREIGN KEY (id, question_id)
REFERENCES results(id, question_id)
);
INSERT INTO responses(id, question_id, data_value, data_value_unit)
(SELECT id, question_id, data_value, data_value_unit
FROM alzheimers);
-- (1c) For both the responses and the questions tables, they would have foreign
keys (id, question_id) that reference
-- results(id, question_id) because it was given that they would be multikeys. This
leaves the results table to hold the
-- primary keys (id, question_id).
-- Question 2
CREATE TABLE ev(
id SERIAL PRIMARY KEY,
vin VARCHAR(20),
county VARCHAR(50),
city VARCHAR(50),
state VARCHAR(2),
postal_code VARCHAR(12),
model_year INT,
make VARCHAR(50),
model VARCHAR(50),
ev_type VARCHAR(200),
electric_range INT,
base_msrp INT,
census_tract VARCHAR(20)
);
CREATE temporary TABLE temp (
vin VARCHAR(20),
county VARCHAR(50),
city VARCHAR(50),
state VARCHAR(2),
postal_code VARCHAR(20),
model_year INT,
make VARCHAR(50),
model VARCHAR(50),
ev_type VARCHAR(200),
cafv_elegibility VARCHAR(200),
electric_range INT,
base_msrp INT,
legislative_district INT,
DOL_V_ID BIGINT,
v_location TEXT,
electric_utility TEXT,
census_tract VARCHAR(20)
);
--psql
\copy temp(vin, county, city, state, postal_code, model_year, make, model,ev_type,
cafv_elegibility, electric_range, base_msrp, legislative_district, DOL_V_ID,
v_location, electric_utility, census_tract) FROM '/Users/joshuaauvaa/Desktop/INFO
330/ev.csv' CSV HEADER;
INSERT INTO EV(vin, county, city, state, postal_code, model_year, make, model,
ev_type, electric_range, base_msrp, census_tract)
SELECT vin, county, city, state, postal_code, model_year, make, model, ev_type,
electric_range, base_msrp, census_tract
FROM temp;
DROP TABLE temp;
-- Bonus --
\copy alzheimers TO '/Users/joshuaauvaa/Desktop/INFO 330/alzheimers' CSV HEADER;
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help