SQL 查询问题
表t_student：
s_id s_name s_age s_dept
0001 lamar 23sgdsgdg
0002 faf 32fsdfsfds
0003 ht 14ghgfjd
0004 oigd 26kiajge
0005 jkhfs 25fsdgha
表t_course：
c_id c_name c_time
001 jtyjt 20
002 gdgd 25
003 nhfg 40
004 dfg 30
005 latg 35
表：t_study
st_id st_s_id st_c_id st_sc_chengji
01 0001 001 78
02 0001 002 85
03 0001 004 80
04 0002 002 75
05 0002 003 86
06 0004 002 90
07 0004 005 85
1，查询各门成绩都在80以上的学生ID和姓名
2，查询每个学生所学课程的总时间
[解决办法]
表t_student和表t_course有联系吗？如何查？
[解决办法]
CREATE TABLE t_student
(
s_idCHAR(5),
s_nameCHAR(10),
s_agetinyint,
s_deptCHAR(10)
)
GO
INSERT INTO t_student SELECT '0001 ', 'lamar ',23, 'sgdsgdg '
UNION SELECT '0002 ', 'faf ',32, 'fsdfsfds '
UNION SELECT '0003 ', 'ht ',14, 'ghgfjd '
UNION SELECT '0004 ', 'oigd ',26, 'kiajge '
UNION SELECT '0005 ', 'jkhfs ',25, 'fsdha '
GO
CREATE TABLE t_course
(
c_idCHAR(5),
c_nameCHAR(10),
c_timeINT
)
GO
INSERT INTO t_course SELECT '001 ', 'jtyjt ',20
UNION SELECT '002 ', 'gdgd ',25
UNION SELECT '003 ', 'nhfg ',40
UNION SELECT '004 ', 'dfg ',30
UNION SELECT '005 ', 'latg ',35
GO
DROP TABLE t_study
CREATE TABLE t_study
(
st_idCHAR(5),
st_s_idCHAR(5),
st_c_idCHAR(5),
st_sc_chengjiINT
)
GO
DELETE FROM t_study
INSERT INTO t_study SELECT '01 ', '0001 ', '001 ',82
UNION SELECT '02 ', '0001 ', '002 ',85
UNION SELECT '03 ', '0001 ', '003 ',89
UNION SELECT '04 ', '0001 ', '004 ',80
UNION SELECT '05 ', '0001 ', '005 ',86
UNION SELECT '07 ', '0002 ', '002 ',75
UNION SELECT '08 ', '0002 ', '003 ',86
UNION SELECT '09 ', '0004 ', '002 ',90
UNION SELECT '10 ', '0004 ', '005 ',85
GO
1.
SELECT t_s.s_id,t_s.s_name
FROM t_student t_s INNER JOIN
(SELECT ts.s_id
FROM t_student ts INNER JOIN t_study ty
ON ts.s_id=ty.st_s_id
WHERE ty.st_sc_chengji> =80
GROUP BY s_id
HAVING COUNT(s_id)=5) AS t1
ON t_s.s_id=t1.s_id
2.
SELECT t_st.s_name,SUM(c_time) AS '所有课程总时间 '
FROM t_student t_st INNER JOIN t_study t_sy
ON t_st.s_id=t_sy.st_s_id INNER JOIN t_course tc
ON t_sy.st_c_id=tc.c_id
GROUP BY t_st.s_name
