35 lines
No EOL
949 B
SQL
35 lines
No EOL
949 B
SQL
CREATE TABLE
|
|
IF NOT EXISTS Respondents (name VARCHAR(255) PRIMARY KEY);
|
|
|
|
CREATE TABLE
|
|
IF NOT EXISTS Answers (
|
|
answerOne INT,
|
|
answerTwo INT,
|
|
answerThree INT,
|
|
answerFour INT,
|
|
name VARCHAR(255) NOT NULL,
|
|
projectName VARCHAR(255) NOT NULL,
|
|
FOREIGN KEY (name) REFERENCES Respondents (name),
|
|
FOREIGN KEY (projectName) REFERENCES ProjectDetails (projectName)
|
|
);
|
|
|
|
CREATE TABLE
|
|
IF NOT EXISTS ProjectDetails (
|
|
projectName VARCHAR(255) PRIMARY KEY,
|
|
projectDescription TEXT
|
|
);
|
|
|
|
CREATE VIEW
|
|
IF NOT EXISTS SurveyResults AS
|
|
SELECT
|
|
Respondents.name,
|
|
Answers.answerOne,
|
|
Answers.answerTwo,
|
|
Answers.answerThree,
|
|
Answers.answerFour,
|
|
Answers.projectName,
|
|
ProjectDetails.projectDescription
|
|
FROM
|
|
Respondents
|
|
JOIN Answers ON Respondents.name = Answers.name
|
|
JOIN ProjectDetails ON Answers.projectName = ProjectDetails.projectName; |