经典SQL题3

1、  如何删除有自增长ID的数据表中的重复数据？

2、为管理岗位业务培训信息，建立3个表:

　　S (S#,SN,SD,SA) S#,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄

　　C (C#,CN ) C#,CN 分别代表课程编号、课程名称

　　SC ( S#,C#,G ) S#,C#,G 分别代表学号、所选修的课程编号、学习成绩

题目：

(1). 使用标准SQL语句查询选修课程名称为’税收基础’的学员学号和姓名

(2). 使用标准SQL语句查询选修课程编号为’C 2 ’ 的学员姓名和所属单位

(3). 使用标准SQL语句查询不选修课程编号为’C 5 ’ 的学员姓名和所属单位

(4). 使用标准SQL语句查询选修全部课程的学员姓名和所属单位

(5). 查询选修了课程的学员人数

(6). 查询选修课程超过5门的学员学号和所属单位

 

3、某个表有两个字段（phone和sendtime）
phone                   sendtime
13908000800           2006-3-8  16:20:25
13918001800           2006-3-8  17:15:20
13908000800           2006-3-8  16:25:25
13908000800           2006-3-9  07:15:20

......

请写出一个sql语句，实现的功能为：取出每个号码（phone）的最新发送时间(sendtime)的记录？

4、在SQL Server 数据库中，有一个表StudentAchievement（学生成绩）,它有三个字段：StudentID(varchar(8),学生编号)，CourseID(varchar(10),课程编号)，Achievement(int ，成绩)，写一条SQL语句，筛选出每门课程的前两名的学生编号，课程编号，成绩并排序。

5、用一条SQL语句，查询表中从第20到第30行的数据

 

参考答案：

--1、如何删除有自增长ID的数据表中的重复数据？
DELETE FROM S
WHERE [S#] NOT IN(SELECT MIN([S#]) [s#],sn,sd FROM S GROUP BY SN,SD)

或

SELECT MIN([S#]) [s#],sn,sd FROM S GROUP BY SN,SD
SELECT * FROM S
order by sd

2、CREATE TABLE S (S# INT,SN VARCHAR(10),SD VARCHAR(50),SA INT) --S#,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄

CREATE TABLE C (C# INT,CN VARCHAR(10)) --C#,CN 分别代表课程编号、课程名称

CREATE TABLE SC ( S# INT,C# INT,G INT ) --S#,C#,G 分别代表学号、所选修的课程编号、学习成绩

INSERT INTO S VALUES(1,'XA','微软',20)
INSERT INTO S VALUES(2,'XB','IBM',25)
INSERT INTO S VALUES(3,'XC','SABEAS',20)
INSERT INTO S VALUES(4,'XD','CA',20)

INSERT INTO C VALUES(1,'SQLServer')
INSERT INTO C VALUES(2,'.NET')
INSERT INTO C VALUES(3,'UML')
INSERT INTO C VALUES(4,'税收基础')
INSERT INTO C VALUES(5,'设计模式')
INSERT INTO C VALUES(6,'中国历史')
INSERT INTO C VALUES(7,'XP')

INSERT INTO SC VALUES(1,1,60)
INSERT INTO SC VALUES(1,2,70)
INSERT INTO SC VALUES(1,3,60)
INSERT INTO SC VALUES(2,1,60)
INSERT INTO SC VALUES(3,1,60)
INSERT INTO SC VALUES(2,2,60)
INSERT INTO SC VALUES(2,3,60)
INSERT INTO SC VALUES(2,4,60)
INSERT INTO SC VALUES(2,5,60)
INSERT INTO SC VALUES(2,6,55)
INSERT INTO SC VALUES(3,3,65)

INSERT INTO SC VALUES(2,7,65)
INSERT INTO SC VALUES(3,4,65)
INSERT INTO SC VALUES(3,5,65)
---------------------------------------------------------------------------------------

--(1). 使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名
--实现代码:
解1
SELECT SN,SD FROM S
INNER JOIN SC
ON S.S#=SC.S#
INNER JOIN C
ON C.C#=SC.C#
WHERE CN='税收基础'

解2
SELECT SN,SD FROM  S
WHERE [s#] IN(
SELECT [S#] FROM C,SC
WHERE C.[C#]=SC.[C#]
AND CN='税收基础')

--(2). 使用标准SQL嵌套语句查询选修课程编号为2的学员姓名和所属单位
--实现代码:
解1
SELECT S.SN,S.SD FROM S
INNER JOIN SC
ON S.[S#]=SC.[S#]
WHERE  SC.[C#]=2

解2
SELECT S.SN,S.SD FROM S,SC
WHERE S.[S#]=SC.[S#]
AND SC.[C#]=2

--(3). 使用标准SQL嵌套语句查询不选修课程编号为2的学员姓名和所属单位
--实现代码:

SELECT SN,SD FROM S
WHERE [S#] NOT IN(
SELECT [S#] FROM SC
WHERE [C#]=2)

--(4). 使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位
--实现代码:
解1
select s.s#,sd from s
where  (select count(*) from sc where s.s#=sc.s#)=(select count(*) from c)

解2
SELECT SN,SD FROM S
WHERE [S#] IN(
SELECT [S#] FROM SC
RIGHT JOIN
C ON SC.[C#]=C.[C#] GROUP BY [S#]
HAVING COUNT(*)=(SELECT COUNT(*) FROM C))

--(5). 查询选修了课程的学员人数
--实现代码:
SELECT 学员人数=COUNT(DISTINCT [S#]) FROM SC

--(6). 查询选修课程超过5门的学员学号和所属单位
--实现代码:
解1
select s.s#,sd from s
where  (select count(*) from sc where s.s#=sc.s#)>=5

解2
SELECT SN,SD FROM S
WHERE [S#] IN(
SELECT [S#] FROM SC
GROUP BY [S#]
HAVING COUNT(DISTINCT [C#])>5)

5、解1:
select top 10 * from A where id not in (select top 30 id from A)
--解2: 
select top 10 * from A where id > (select max(id) from (select top 30 id from A )as A)
--解3： 

select * from (select top 3 * from
(select top 6 * from employees ) as e order by EmployeeID desc) as s
order by EmployeeID asc