数据库应用实践教程(铁道出版社)实验八部分答案

s_c_info b on a.c_no=b.c_no
(3)
select c_info.c_no,c_name,s_c_info.c_no,s_c_info.score
from c_info full outer join
s_c_info on c_info.c_no=s_c_info.c_no
8
9
select s.st_id,st_name,c.c_name,sc.score
from s_c_info sc inner join c_info c on sc.c_no=c.c_no inner join st_info s on s.st_id=sc.st_id
where(s.st_sex='男')
select st_name,year(getdate())-year(Born_date) as '年龄'
from st_info
where year(getdate())-year(born_date)>all
(select year(getdate())-year(born_date)
思考题
1
select st_name
from st_info
where st_id in(select st_id from s_c_info group by st_id having
count(*)=(select count(*) from c_info))
2
select st_name,st_id,year(getdate())-year(born_date) as '年龄'
2(1)
select a.c_no,a.c_name,b.st_id,b.score
from c_info a left outer join
s_c_info b on a.c_no=b.c_no
(2)
select a.c_no,a.c_name,b.st_id,b.score
from c_info a right outer join
实验九
1（1）
select st_info.st_id,st_info.st_name,s_c_info.score
from st_info,s_c_info
where st_info.st_id=s_c_info.st_id and score>=80 and score<=90
(2)
3
select a.st_id,st_name,b.c_no,score
from st_info a inner join s_c_info b on a.st_id=b.st_id
where b.score<60
4
select a.c_no,c_name,c_credit,b.st_name,c.score
3
select distinct st_info.st_id,st_info.st_name
from st_info,s_c_info
where st_info.st_id=s_c_info.st_id and c_no in
( select c_no from s_c_info where st_id='2001050105')
from st_info
where cl_name='材料科学0601班')
select st_id,score
from s_c_info
where c_no='9710041' and score>any
(select score from s_c_info where c_no='29000011')
实验9
5
select a.st_id,a.st_name,b.c_no,max(b.score)
from st_info a join s_c_infob on a.st_id=b.st_id
group by a.st_id,a.st_name,b.c_no
存储过程习题6.3
CREATE PROCEDURE SCOREINFO @STNAME VARCHAR(20),@CTYPE CHAR(4) AS
where cl_name=
(select cl_name from st_info
where st_name='杨平娟')
select st_id,score
from s_c_info
where c_no in
(select c_no from c_info
where c_name='体育')
FROM ST_INFO ST,S_C_INFO SC
WHERE ST.ST_ID=SC.ST_ID AND SC.SCORE IN(SELECT MAX(SCORE) FROM S_C_INFO JOIN ST_INFO
ON ST_INFO.ST_ID=S_C_INFO.ST_ID
GROUP BY ST_INFO.ST_ID)
SELECT ST_INFO.ST_NAME,C_INFO.C_TYPE,C_INFO.C_NAME,S_C_INFO.SCORE
FROM ST_INFO,S_C_INFO,C_INFO
WHERE ST_INFO.ST_ID=s_c_info.st_id and s_c_info.c_no=c_info.c_no and
where st_info.st_id=@stid
DECLARE @get_clname char(20)
exec stclass'0603060109',@get_clname output
print @get_clname
from st_info
wheredate)not in(select year(getdate())-year(born_date)
from st_info where cl_name='口腔(七)0601班')
此处not in 也可以是<>all
from s_c_info c join st_info b on c.st_id=b.st_id
join c_info a on a.c_no=c.c_no
where a.c_type='必修'
5
select DISTINCT ST.St_iD,st_name, sc.score,c_NO
st_info.st_name=@stname and c_info.c_type=@ctype
6.4
create procedure stclass @stid char(10),@class_name char(20) output as
select @class_name=cl_name from st_info
4
select st_id,max(score)
from s_c_info group by st_id
5
select c_no,score
from s_c_info
where score>(select min(score) from s_c_info where st_id='2001050105' ) and st_id='2001050108'
select st_info.st_id,st_info.st_name,s_c_info.score
from st_info,s_c_info,c_info
where st_info.st_id=s_c_info.st_id and c_info.c_no=s_c_info.c_no and c_info.c_name='c语言程序设计基础'
select st_id,st_name
from st_info
where st_id in
(select st_id from s_c_info
where c_no in (select c_no
from c_info
where c_name='大学计算机基础' or c_name='c语言程序设计基础'))