数据库导入:
drop table if
exists
`question_practice_detail`
;
CREATE TABLE
`question_practice_detail`
(
`id`
int
,
`device_id`
int
,
`question_id`
int
,
`result`
varchar
(
32
)
);
INSERT INTO
question_practice_detail
VALUES
(
1
,
2138
,
111
,
'wrong'
);
INSERT INTO
question_practice_detail
VALUES
(
2
,
3214
,
112
,
'wrong'
);
INSERT INTO
question_practice_detail
VALUES
(
3
,
3214
,
113
,
'wrong'
);
INSERT INTO
question_practice_detail
VALUES
(
4
,
6543
,
111
,
'right'
);
INSERT INTO
question_practice_detail
VALUES
(
5
,
2315
,
115
,
'right'
);
INSERT INTO
question_practice_detail
VALUES
(
6
,
2315
,
116
,
'right'
);
INSERT INTO
question_practice_detail
VALUES
(
7
,
2315
,
117
,
'wrong'
);
INSERT INTO
question_practice_detail
VALUES
(
8
,
5432
,
118
,
'wrong'
);
INSERT INTO
question_practice_detail
VALUES
(
9
,
5432
,
112
,
'wrong'
);
INSERT INTO
question_practice_detail
VALUES
(
10
,
2131
,
114
,
'right'
);
INSERT INTO
question_practice_detail
VALUES
(
11
,
5432
,
113
,
'wrong'
);
drop table if exists
`user_profile`
;
CREATE TABLE
`user_profile`
(
`id`
int
,
`device_id`
int
,
`gender`
varchar
(
14
) ,
`age`
int
,
`university`
varchar
(
32
) ,
`gpa`
float
,
`active_days_within_30`
int
,
`question_cnt`
int
,
`answer_cnt`
int
);
INSERT INTO
user_profile
VALUES
(
1
,
2138
,
'male'
,
21
,
'
北京大
学
'
,
3.4
,
7
,
2
,
12
);
INSERT INTO
user_profile
VALUES
(
2
,
3214
,
'male'
,
null
,
'
复旦
大学
'
,
4.0
,
15
,
5
,
25
);
INSERT INTO
user_profile
VALUES
(
3
,
6543
,
'female'
,
20
,
'
北京
大学
'
,
3.2
,
12
,
3
,
30
);
INSERT INTO
user_profile
VALUES
(
4
,
2315
,
'female'
,
23
,
'
浙江
大学
'
,
3.6
,
5
,
1
,
2
);
INSERT INTO
user_profile
VALUES
(
5
,
5432
,
'male'
,
25
,
'
山东大
学
'
,
3.8
,
20
,
15
,
70
);
INSERT INTO
user_profile
VALUES
(
6
,
2131
,
'male'
,
28
,
'
山东大
学
'
,
3.3
,
15
,
7
,
13
);
INSERT INTO
user_profile
VALUES
(
7
,
4321
,
'male'
,
28
,
'
复旦大
学
'
,
3.6
,
9
,
6
,
52
);
(
1
)查看所有来自浙江大学的用户题目回
答明细情况
select a.device_id,question_id,result
from question_practice_detail a JOIN user_profile b
on a.device_id=b.device_id
where university = "浙江大学"
why?不出来结果,栓q
2
)查看问题
“111”
答对的同学的详细信
息。
select *
from question_practice_detail q JOIN user_profile u
on q.device_id=u.device_id
where q.question_id=111
(3)查看北京大学女生的答题情况
select q.device_id,q.question_id,q.result
from question_practice_detail q
JOIN user_profile u on q.device_id=u.device_id
where u.gender='female'
and u.university='北京大学'