6) Write a query which displays each survey's id, and average of Q1, Q3, Q5, and Q7 as a column named Construct 1, and average of Q2, Q4, Q6, and Q8 as a column named Construct 2 Sample of expected output: +------+-------------+-------------+ | a_id | construct_1 | construct_2 | +------+-------------+-------------+ | 1 | 2.5000 | 2.2500 | | 2 | 3.0000 | 4.0000 | | 3 | 3.5000 | 3.5000 | | 4 | 2.2500 | 1.5000 | | 5 | 2.7500 | 4.2500 | | 6 | 4.5000 | 2.5000 |
The following tables were designed to store the results of some experiments.
Subjects were asked to complete the same survey in 3 different days. Not all subjects completed the three surveys e.g.
[Survey 1] [Survey 2] [Survey 3]
Subject1999 Complete - -
Subject2001 Complete Complete Complete
Subject2010 Complete - Complete
The survey contains information about 2 constructs in 8 questions.
The files subject.csv and answers.csv contain the following table information:
SUBJECT
s_id: subject's id
s_firstname: subject's first name
s_lastname: subject's last name
s_age: subject's age
s_country: subject's country
ANSWER
a_id: answer's id
s_id: subject's id
a_surveyNum: survey number (from 1 to 3);
it was expected that the same survey was completed at three different times.
Unfortunately, not all subjects completed the surveys all those days. So, some subjects completed it only the first time, others only the second and third time, etc.
a_surveyDate: date of the completion of the survey
a_q1: answer of question 1; a value from 1 to 5; positive scale
a_q2: answer of question 2; a value from 1 to 5; negative scale; this will be converted i.e. from 1 to 5, from 2 to 4, from 3 to 3; from 4 to 2, from 5 to 1
a_q3: answer of question 3; a value from 1 to 5; positive scale
a_q4: answer of question 4; a value from 1 to 5; negative scale; this will be converted i.e. from 1 to 5, from 2 to 4, from 3 to 3; from 4 to 2, from 5 to 1
a_q5: answer of question 5; a value from 1 to 5; positive scale
a_q6: answer of question 6; a value from 1 to 5; negative scale; this will be converted i.e. from 1 to 5, from 2 to 4, from 3 to 3; from 4 to 2, from 5 to 1
a_q7: answer of question 7; a value from 1 to 5; positive scale
a_q8: answer of question 8; a value from 1 to 5; negative scale; this will be converted i.e. from 1 to 5, from 2 to 4, from 3 to 3; from 4 to 2, from 5 to 1
Construct 1 is calculated as the average of q1, q3, q5 and q7; Construct 2 is calculated as the average of the positive scale of q2, q4, q6, and q8.
=======================================================
6) Write a query which displays each survey's id, and average of Q1, Q3, Q5, and Q7 as a column named Construct 1, and average of Q2, Q4, Q6, and Q8 as a column named Construct 2
Sample of expected output:
+------+-------------+-------------+
| a_id | construct_1 | construct_2 |
+------+-------------+-------------+
| 1 | 2.5000 | 2.2500 |
| 2 | 3.0000 | 4.0000 |
| 3 | 3.5000 | 3.5000 |
| 4 | 2.2500 | 1.5000 |
| 5 | 2.7500 | 4.2500 |
| 6 | 4.5000 | 2.5000 |
7) Write a query which displays subject id and the average of construct 1 and construct 2 from the surveys. The construct average will be calculated using the available surveys per subject.
Sample of expected output:
+------+-------------+-------------+
| s_id | construct_1 | construct_2 |
+------+-------------+-------------+
| 1 | 2.75000000 | 2.75000000 |
| 2 | 2.75000000 | 1.50000000 |
| 3 | 2.25000000 | 3.00000000 |
| 4 | 2.50000000 | 2.25000000 |
| 5 | 2.91666667 | 3.08333333 |
| 6 | 3.50000000 | 2.25000000 |
8) Write a query which displays subject id and the average of construct 1 and construct 2 from the surveys of people who completed all three surveys.
Sample of expected output:
+------+-------------+-------------+
| s_id | construct_1 | construct_2 |
+------+-------------+-------------+
| 5 | 2.91666667 | 3.08333333 |
| 9 | 1.66666667 | 3.00000000 |
| 10 | 2.75000000 | 2.58333333 |
| 12 | 2.58333333 | 3.50000000 |
| 13 | 2.08333333 | 2.41666667 |
| 16 | 3.00000000 | 2.75000000 |
9) Write a query which displays subject id and the average of construct 1 and construct 2 from the surveys of people who is 30 years old or less. The construct average will be calculated using the available surveys per subject.
Sample of expected output:
+------+-------------+-------------+
| s_id | construct_1 | construct_2 |
+------+-------------+-------------+
| 1 | 2.75000000 | 2.75000000 |
| 3 | 2.25000000 | 3.00000000 |
| 4 | 2.50000000 | 2.25000000 |
| 5 | 2.91666667 | 3.08333333 |
| 6 | 3.50000000 | 2.25000000 |
| 8 | 3.62500000 | 3.62500000 |
10) Write a query which displays subject id and the average of construct 1 and construct 2 from the surveys which were answered within the first 15 days of January or February. The construct average will be calculated using the available surveys per subject.
Sample of expected output:
+------+-------------+-------------+
| s_id | construct_1 | construct_2 |
+------+-------------+-------------+
| 3 | 2.25000000 | 3.00000000 |
| 4 | 2.00000000 | 2.00000000 |
| 5 | 2.50000000 | 2.75000000 |
| 6 | 3.50000000 | 2.25000000 |
| 7 | 3.00000000 | 3.25000000 |
| 8 | 3.75000000 | 4.00000000 |
Note: The creation of the tables must include the corresponding DROP TABLE IF EXISTS command.
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
