题目
1:
问题描述
:
insert
into s values('1','
张亚京
'
)
insert
into s values('2','
李红
'
)
insert
into s values('3','
刘婷
'
)
insert
into s values('4','
赵静
'
)
select
* from c
insert
into c values('1','
语文
'
,
'
李明
'
)
insert
into c values('2','
数学
'
,
'
刘老师
'
)
insert
into c values('3','
英语
'
,
'
王老师
'
)
select
* from sc
insert
into sc values('1','1','78')
insert
into sc values('1','2','90')
insert
into sc values('1','3','86')---
insert
into sc values('2','1','55')
insert
into sc values('2','2','59')
insert
into sc values('2','3','75')---
insert
into sc values('3','1','59')
insert
into sc values('3','2','44')
insert
into sc values('3','3','55')---
insert
into sc values('4','2','88')
insert
into sc values('4','3','56')
S (SNO,SNAME)
学生关系。
SNO
为学号,
SNAME
为姓名
C (CNO,CNAME,CTEACHER)
课程关系。
CNO
为课程号,
CNAME
为课程名,
CTEACHER
为任课教师
SC(SNO,CNO,SCGRADE)
选课关系。
SCGRADE
为成绩
1.
(
1
)找出选修过
“
李明
”
老师讲授课程的所有学生姓名
--
实现代码
:
法一
Select
*
FROM
SC,C,S
Where
SC.CNO=C.CNO AND SC.SNO=S.SNO AND CTEACHER='
李明
'
---
法二:
-------------
select
*
from
s join sc
on
s.sno=sc.sno join c
on
sc.cno=c.cno
where
c.cteacher='
李明
'
---
法三:
--------------
Select
*
FROM
S
Where
Sno IN( Select Sno
FROM C,SC
Where C.[Cno]=SC.[Cno] AND CTEACHER='
李明
'
)
(
2
)找出没有选修过
“
李明
”
老师讲授课程的所有学生姓名
--
实现代码
:
法一
Select
SName
FROM
S
Where
[Sno] NOT IN( Select SC.[Sno]
FROM SC,C
Where SC.CNO=C.CNO AND CTEACHER='
李明
'
)
---
法二:
------------
Select
SNAME
FROM
S
Where
NOT EXISTS( Select * FROM SC,C Where SC.CNO=C.CNO AND CTEACHER='
李明
'
AND SC.SNO=S.SNO)
(
3
)找出没有选修过课程号为"
1"
的所有学生姓名
--
实现代码
Select
SName
FROM
S
Where
[Sno] NOT IN( Select [Sno] FROM SC Where CNO='1')
2.
列出有二门以上
(
含两门
)
不及格课程的学生姓名及其平均成绩
--
实现代码
:
Select
S.SNO,S.SNAME,AVG_SCGRADE=AVG(SC.SCGRADE)
FROM
S,SC,( Select SNO
FROM SC
Where SCGRADE<60
GROUP BY SNO
HAVING COUNT(DISTINCT CNO)>=2 )A
Where
S.SNO=A.SNO AND SC.SNO=A.SNO
GROUP
BY S.SNO,S.SNAME
--
以下方法错误:这样求出的平均分为不及格科目的平均分,未加入及格科目的分数
-----------
select
count(*),S.sname,avg(SCGRADE)
from
S,SC
where
S.SNO=SC.SNO and SCGRADE<60
group
by sname
having
count(*)>=2
--------------------------------------------------------------------------------------------------------------------------------------
3. (1)
列出既学过
“
语文
”
课程,又学过
“
数学
”
课程的所有学生姓名
--
实现代码
:
法一
Select
S.SNO,S.SNAME
FROM
S,(
Select SC.SNO
FROM SC,C
Where SC.CNO=C.CNO AND C.CNAME IN('
语文
'
,
'
数学
'
)
GROUP BY SNO
HAVING COUNT(DISTINCT c.CNO)=2 )SC
Where
S.SNO=SC.SNO
---
法二:只是子查询代码与法一不同
Select
S.SNO,S.SNAME
FROM
S,(
select sc.sno
from sc inner join c
on sc.cno=c.cno
where c.cname in('
语文
'
,
'
数学
'
)
group by sc.sno
having count(*)=2)SC
Where
S.SNO=SC.SNO
---
法三:
--------------------------
select *
from (select s.sno from s,sc,c where s.sno=sc.sno and sc.cno=c.cno and c.cname='语文')t1,
(select s.sno from s,sc,c where s.sno=sc.sno and sc.cno=c.cno and c.cname='数学')t2,
s
where t1.sno=t2.sno and t1.sno=s.sno
------------------
select
s.sno
into
#t1
from
s join sc
on
s.sno=sc.sno join c
on
sc.cno=c.cno
where
c.cname='
语文
'
select
s.sno
into
#t2
from
s join sc
on
s.sno=sc.sno join c
on
sc.cno=c.cno
where
c.cname='
数学
'
select
*
from
#t1,#t2,s
where
#t1.sno=#t2.sno and #t1.sno=s.sno
(2)使用标准SQL嵌套语句查询选修全部课程的学员姓名
法一:一句SQL语句即可。注意有下划线的部分!
Select S.SNO,S.SNAME
FROM S,(
Select SC.SNO
FROM SC,C
Where SC.CNO=C.CNO
GROUP BY SNO
HAVING COUNT(*)=(select count(*) from c) )SC
Where S.SNO=SC.SNO
法二:思路同上,只是生成了存储过程。
create
proc allclass
as
declare
@cnum int
select
@cnum=count(*) from c
Select
S.SNO,S.SNAME
FROM
S,(
Select SC.SNO
FROM SC,C
Where SC.CNO=C.CNO
GROUP BY SNO
HAVING COUNT(DISTINCT c.CNO)=@cnum )SC
Where
S.SNO=SC.SNO
exec
allclass
(3)查询选修课程超过2门的学员学号
--实现代码:
Select Sno FROM SC GROUP BY Sno HAVING COUNT(Cno)>2
查询选修课程超过2门的学员学号及姓名
--
实现代码
:
Select
SNo,SNAME
FROM
S
Where
Sno IN(Select Sno
FROM SC
GROUP BY Sno
HAVING COUNT(Cno)>2)
(4)列出人数大于3的各科最高成绩的列表,显示科目号、成绩两个字段
---
正解如下:
(
科目号为2、3的选课人数大于3人
)
Select cno,max(scgrade)as'最高分'
FROM SC
Where cno IN (Select cno FROM SC GROUP BY cno HAVING COUNT(sno)>3)
group by cno
order by cno
4.
列出
“
语文
”
课比
“
数学
”
课同学该门课成绩高的所有学生的学号
--
实现代码
:法一
select *
from (select s.sno,sc.SCGRADE from s,sc,c
where s.sno=sc.sno and sc.cno=c.cno and c.cname='语文' )t1,
(select s.sno,sc.SCGRADE from s,sc,c
where s.sno=sc.sno and sc.cno=c.cno and c.cname='数学' )t2
where t1.sno=t2.sno and t1.SCGRADE>t2.SCGRADE
---法二:--------------------------
select
s.sno,sc.SCGRADE
into
#tt1
from
s join sc
on
s.sno=sc.sno join c
on
sc.cno=c.cno
where
c.cname='
语文
'
select
s.sno,sc.SCGRADE
into
#tt2
from
s join sc
on
s.sno=sc.sno join c
on
sc.cno=c.cno
where
c.cname='
数学
'
select
*
from
#tt1,#tt2
where
#tt1.sno=#tt2.sno and #tt1.SCGRADE>#tt2.SCGRADE
------------------------------------------------------------------------------------------------------------------------------------
5.
查询选修了课程的学员人数
--
实现代码
:
Select
学员人数
=
COUNT
(
DISTINCT
[Sno]) FROM SC
总结:
1
、
select
* from a,b where a.id=b.id
和
select * from a inner join b on a.id=b.id
结果集相同;
2
、在作
“
找出没有选修过
“
李明
”
老师讲授课程的所有学生姓名
”
这种题时,
--
用
Where [Sno] NOT IN(
在此可以是
SQL
语句生成的一列值
)
比较好。
Select SName
FROM S
Where [Sno] NOT IN( Select SC.[Sno]
FROM SC,C
Where SC.CNO=C.CNO AND CTEACHER='
李明
'
)
3
、可以将
select
count(*) from c
直接代入
SQL
语句,而不是非要写成存储过程;
Select S.SNO,S.SNAME
FROM S,(
Select SC.SNO
FROM SC,C
Where SC.CNO=C.CNO
GROUP BY SNO
HAVING COUNT(*)=(select count(*) from c) )SC
Where S.SNO=SC.SNO