SQL题：sql语句查询(一)

来源：互联网发布：淘宝达人不能开店编辑：程序博客网时间：2024/06/09 17:10

题目1:

问题描述:

select * from s

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,

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

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