SQL一些常见的查询练习

1、删除表中重复记录

delete from table where id not in ( select min(id) from table group by 字段1，字段2)

2、查询数据的最大排序问题（只能用一条语句写）

CREATE TABLE hard (qu char (11) ,co char (11) ,je numeric(3, 0))

 

insert into hard values ('A','1',3)

insert into hard values ('A','2',4)

insert into hard values ('A','4',2)

insert into hard values ('A','6',9)

insert into hard values ('B','1',4)

insert into hard values ('B','2',5)

insert into hard values ('B','3',6)

insert into hard values ('C','3',4)

insert into hard values ('C','6',7)

insert into hard values ('C','2',3)

 

 

要求查询出来的结果如下：

 

qu co je

----------- ----------- -----

A 6 9

A 2 4

B 3 6

B 2 5

C 6 7

C 3 4

 

 

就是要按qu分组，每组中取je最大的前2位！！

而且只能用一句sql语句！！！

select *  from hard a where (select count(*) from hard b where a.qu=b.qu and b.je>=a.je)<=2 ORDER BY qu,je  DESC

 3、一个组合问题!

Student(S#,Sname,Sage,Ssex) 学生表
Course(C#,Cname,T#) 课程表 
SC(S#,C#,score) 成绩表
Teacher(T#,Tname) 教师表

问题：
1、查询“001”课程比“002”课程成绩高的所有学生的学号；

select a.S# from (select s#,score from SC where C#=’001′) a,(select s#,score from SC where C#=’002′) bwhere a.score>b.score and a.s#=b.s#; 

2、查询平均成绩大于60分的同学的学号和平均成绩；

select S#,avg(score)from scgroup by S# having avg(score) >60; 

3、查询所有同学的学号、姓名、选课数、总成绩；

select Student.S#,Student.Sname,count(SC.C#),sum(score)from Student left join SC on Student.S#=SC.S#group by Student.S#,Sname 

4、查询姓“李”的老师的个数；

select count(distinct(Tname))from Teacherwhere Tname like ‘李%’; 

5、查询没学过“叶平”老师课的同学的学号、姓名；

select Student.S#,Student.Snamefrom Studentwhere S# not in (select distinct( SC.S#) from SC,Course,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname=’叶平’); 

6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；

select Student.S#,Student.Snamefrom Student,SCwhere Student.S#=SC.S# and SC.C#=’001′and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#=’002′);

7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；

select S#,Snamefrom Studentwhere S# in(select S#from SC ,Course ,Teacherwhere SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname=’叶平’ group by S# having count(SC.C#)=(select count(C#) from Course,Teacher where Teacher.T#=Course.T# and Tname=’叶平’)); 

8、查询所有课程成绩小于60分的同学的学号、姓名；

select S#,Snamefrom Studentwhere S# not in (select Student.S# from Student,SC where S.S#=SC.S# and score>60);

 

9、查询没有学全所有课的同学的学号、姓名；

select Student.S#,Student.Snamefrom Student,SCwhere Student.S#=SC.S#group by Student.S#,Student.Sname having count(C#) <(select count(C#) from Course); 

10、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名；

select S#,Snamefrom Student,SCwhere Student.S#=SC.S# and C# in （select C# from SC where S#='1001'）;

11、删除学习“叶平”老师课的SC表记录；

Delect SCfrom course ,Teacherwhere Course.C#=SC.C# and Course.T#= Teacher.T# and Tname='叶平'; 

12、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

SELECT L.C# 课程ID,L.score 最高分,R.score 最低分FROM SC L ,SC RWHERE L.C# = R.C#andL.score = (SELECT MAX(IL.score)FROM SC IL,Student IMWHERE IL.C# = L.C# and IM.S#=IL.S#GROUP BY IL.C#)andR.Score = (SELECT MIN(IR.score)FROM SC IRWHERE IR.C# = R.C#GROUP BY IR.C# ); 

13、查询学生平均成绩及其名次

SELECT 1+(SELECT COUNT( distinct 平均成绩)FROM (SELECT S#,AVG(score) 平均成绩FROM SCGROUP BY S# ) T1WHERE 平均成绩 > T2.平均成绩) 名次, S# 学生学号,平均成绩FROM (SELECT S#,AVG(score) 平均成绩 FROM SC GROUP BY S# ) T2ORDER BY 平均成绩 desc;

14、查询各科成绩前三名的记录:(不考虑成绩并列情况)

SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数FROM SC t1WHERE score IN (SELECT TOP 3 scoreFROM SCWHERE t1.C#= C#ORDER BY score DESC)ORDER BY t1.C#; 

15、查询每门功成绩最好的前两名

SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数FROM SC t1WHERE score IN (SELECT TOP 2 scoreFROM SCWHERE t1.C#= C#ORDER BY score DESC )ORDER BY t1.C#;

4、查询A(ID,Name)表中第31至40条记录，ID作为主键可能是不是连续增长的列，完整的查询语句如下：
方法一：

select top 10 *from Awhere ID >(select max(ID) from (select top 30 ID from A order by ID ) T) order by ID

方法二：

select top 10 *from Awhere ID not In (select top 30 ID from A order by ID)order by ID