SQL简单查询语句专项练习

题目参考文章:http://blog.csdn.net/friendan/article/details/8072668

 笔者整理了一些简单的数据库查询例题,分享给大家.

(因为笔者的系统为日文系统,所以部分内容会显示为繁体字)

学生信息表结构(S)

No	Name	Hours
C01	JAVA	70
C02	C	90
C03	C++	80
C04	DB	106
C05	数学	180
C06	DATE結構	72

选课表结构(C)

No	Name	Sex	Age	Dept
9512101	李勇	男	19	電子系
9512102	劉晨	男	20	電子系
9512103	王敏	女	20	電子系
9521101	張立	男	22	電子系
9521102	呉	男	21	信息系
9521103	張海	男	20	信息系
9531101	銭小力	女	18	数学系
9531102	王大力	男	19	数学系

学生成绩表结构(SC)

SNO	CNO	Grade
9512101	C01	90
9512101	C02	86
9512101	C06	NULL
9512102	C02	78
9512102	C04	66
9521102	C01	82
9521102	C02	75
9521102	C04	92
9521102	C05	50
9521103	C02	68
9521103	C06	NULL
9531101	C01	80
9531101	C05	95
9531102	C05	85

表信息

insert into S select 9512101,'李勇','男',19,'電子系'
insert into S select 9512102,'劉晨','男',20,'電子系'
insert into S select 9512103,'王敏','女',20,'電子系'
insert into S select 9521101,'張立','男',22,'電子系'
insert into S select 9521102,'呉','男',21,'信息系'
insert into S select 9521103,'張海','男',20,'信息系'
insert into S select 9531101,'銭小力','女',18,'数学系'
insert into S select 9531102,'王大力','男',19,'数学系'

insert into C select 'C01','JAVA','70'
insert into C select 'C02','C','90'
insert into C select 'C03','C++','80'
insert into C select 'C04','DB','106'
insert into C select 'C05','数学','180'
insert into C select 'C06','DATE結構','72'

insert into SC select 9512101,'C01','90'
insert into SC select 9512101,'C02','86'
insert into SC (SNO,CNO) values (9512101,'C06')
insert into SC select 9512102,'C02','78'
insert into SC select 9512102,'C04','66'
insert into SC select 9521102,'C01','82'
insert into SC select 9521102,'C02','75'
insert into SC select 9521102,'C04','92'
insert into SC select 9521102,'C05','50'
insert into SC select 9521103,'C02','68'
insert into SC (SNO,CNO) values (9521103,'C06')
insert into SC select 9531101,'C01','80'
insert into SC select 9531101,'C05','95'
insert into SC select 9531102,'C05','85'

下面是简单的查询语句

(笔者使用的数据库是SQLSERVER2014)

(因为准备仓促,如有错误,劳烦请指正)

--1.分别查询学生表和学生修课表中的全部数据。
select * from S
select * from C

--2.查询成绩在70到80分之间的学生的学号、课程号和成绩。
select S.NO,C.NO,SC.Grade from S,C,SC
where S.no = SC.sno
and C.no = SC.cno
and SC.Grade >=70
and SC.Grade <=80

--3．查询C01号课程成绩最高的分数
select max(Grade)
from C,SC
where C.NO = CNO
and CNO like 'C01'

--4.查询学生都选修了哪些课程,要求列出课程号。
SELECT S.NAME,C.NO,C.NAME
FROM S,C,SC
WHERE S.NO = SC.SNO
AND C.NO = SC.CNO

--5.查询修了C02号课程的所有学生的平均成绩、最高成绩和最低成绩。
SELECT AVG(SC.Grade),MAX(SC.GRADE),MIN(SC.GRADE)
FROM SC
WHERE SC.CNO = 'C02'

--6.统计每个系的学生人数。
SELECT Dept,COUNT(S.NO)
FROM S
GROUP BY Dept

--7.统计每门课程的修课人数和考试最高分。
SELECT C.NAME,COUNT(SC.CNO),MAX(SC.Grade)
FROM S,C,SC
WHERE S.NO = SC.SNO
AND C.NO = SC.CNO
GROUP BY C.NAME

--8.统计每个学生的选课门数,并按选课门数的递增顺序显示结果。
SELECT S.NAME,COUNT(S.NO) AS COUNT1
FROM S,C,SC
WHERE S.NO =SC.SNO
AND C.NO = SC.CNO
GROUP BY S.Name
ORDER BY COUNT1

--9.统计选修课的学生总数和考试的平均成绩。
SELECT C.NAME ,COUNT(S.NO),AVG(SC.GRADE)
FROM S,C,SC
WHERE S.NO =SC.SNO
AND C.NO = SC.CNO
GROUP BY C.NAME

--10.查询选课门数超过2门的学生的平均成绩和选课门数。
SELECT S.NAME,COUNT(C.NO),AVG(SC.GRADE)
FROM S,C,SC
WHERE S.NO =SC.SNO
AND C.NO = SC.CNO
GROUP BY S.NAME
HAVING COUNT(C.NO) > 2

--11.列出总成绩超过200分的学生,要求列出学号、总成绩。
SELECT S.NAME,SUM(SC.GRADE)
FROM S,SC
WHERE S.NO =SC.SNO
GROUP BY S.NAME
HAVING SUM(SC.GRADE) >= 200

--12.查询选修了c02号课程的学生的姓名和所在系。
SELECT S.NAME,S.DEPT
FROM S,SC
WHERE S.NO =SC.SNO
AND SC.CNO = 'C02'

--13.查询成绩80分以上的学生的姓名、课程号和成绩,并按成绩的降序排列结果。
SELECT S.NAME,C.NO,SC.GRADE
FROM S,C,SC
WHERE S.NO =SC.SNO
AND C.NO = SC.CNO
ORDER BY SC.GRADE DESC
HAVING SC.GRADE >=80

--14.查询计算机系男生修了"java"的学生的姓名、性别、成绩。
SELECT S.NAME,SC.GRADE
FROM S,C,SC
WHERE S.NO =SC.SNO
AND C.NO = SC.CNO
AND C.Name = 'JAVA'
AND S.SEX = '男'

--15.查询哪些学生的年龄相同,要求列出年龄相同的学生的姓名和年龄。
SELECT DISTINCT(S1.NAME),S1.AGE
FROM S AS S1,S AS S2
WHERE S1.NAME <> S2.NAME
AND S1.AGE = S2.AGE

--16.查询哪些课程没有人选,要求列出课程号和课程名。
SELECT C.NO,C.NAME
FROM C
WHERE C.NO NOT IN (SELECT DISTINCT CNO FROM SC)

--17.查询有考试成绩的所有学生的姓名、修课名称及考试成绩
SELECT S.NAME,C.NAME,SC.GRADE
FROM S,C,SC
WHERE S.NO =SC.SNO
AND C.NO = SC.CNO
AND SC.GRADE IS NOT NULL

--18.分别查询信息系和计算机系的学生的姓名、性别、修课名称、修课成绩,
--并要求将这两个查询结果合并成一个结果集,
--并以系名、姓名、性别、修课名称、修课成绩的顺序显示各列。
;WITH T1 AS(SELECT S.NAME SNAME,S.SEX,C.NAME CNAME,SC.GRADE,S.DEPT
FROM S,C,SC
WHERE S.NO =SC.SNO
AND C.NO = SC.CNO)
SELECT * FROM T1 WHERE T1.DEPT ='コンピュータ系'
UNION
SELECT * FROM T1 WHERE T1.DEPT ='信息系'
ORDER BY DEPT,SNAME,SEX,CNAME,Grade

--19.删除修课成绩小于50分的学生的修课记录
DELETE S
FROM S,C,SC
WHERE S.NO =SC.SNO
AND C.NO = SC.CNO
AND SC.GRADE<50

--20.将所有选修了"c01"课程的学生的成绩加10分。
UPDATE SC SET GRADE = (GRADE+10)
WHERE SC.CNO = 'C01' 

--21.新建一张临时表SC_C.将每一科的课番号,课名以及该课的平均成绩插入其中.

DECLARE @SC_C TABLE(
NO VARCHAR(50),
NAME VARCHAR(50),
AVG_GRADE INT

)

INSERT INTO @SC_C

SELECT C.NO,C.NAME,AVG(SC.GRADE)
FROM SC,C
WHERE C.NO= SC.CNO

GROUP BY C.NO,C.NAME

SELECT * FROM @SC_C

--28从SC表中删除掉大于150小时的课程的选课记录
DELETE FROM SC WHERE
SC.CNO IN (SELECT C.NO FROM C WHERE C.HOURS > 150)


--29.列出电子系所有没有选修JAVA的学生的姓名
SELECT s.NAME
FROM S
WHERE S.DEPT = '電子系'
AND NOT EXISTS(SELECT S.NAME
FROM C,SC
WHERE S.NO = SC.SNO
AND C.NO = SC.CNO
AND C.NAME = 'JAVA'
)

以下问题请思考,如何用exists语句实现:

查询选修了全部课程的学生
查找学号9512101没有选修的课程
查找没有选修课程的学生