SQL语句

最新推荐文章于 2024-09-21 10:25:52 发布

最初的梦想zsq

最新推荐文章于 2024-09-21 10:25:52 发布

阅读量403

点赞数

分类专栏： Sqlserver 文章标签： sql sql server

Sqlserver 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

(一)

1.写出一条Sql语句：取出表A中第31到第40记录（SQLServer, 以自动增长的ID作为主键, 注意：ID可能不是连续的。）
select top 10 * from A where id not in (select top 30 id from A)
解2: select top 10 * from A where id > (selectmax(id) from (select top 30 id from A )as A)

写sql语句实现下列查询
(表)t
a b(列)
1 2
1 3
1 4
2 1
2 2
3 1
4 1
5 3
5 2
查询结果要求
a b
1 2
2 1
3 1
4 1
5 2

3.一组通话记录（总共500万条）:
ID 主叫号码被叫号码通话起始时间通话结束时间通话时长
1 98290000 0215466546656 2007-02-01 09:49:53.000 2007-02-01 09:50:16.00023
2 98290000 021546654666 2007-02-01 09:50:29.000 2007-02-0109:50:41.000 12
3 98290000 021546654666 2007-02-01 09:50:58.000 2007-02-0109:51:12.000 14
4 68290900 0755133329866 2007-02-01 10:04:31.000 2007-02-01 10:07:13.000162
5 78290000 0755255708638 2007-02-01 10:48:26.000 2007-02-01 10:49:23.00057
6 78290000 0755821119109 2007-02-01 10:49:39.000 2007-02-01 10:52:55.000196
7 78290000 035730928370 2007-02-01 11:30:45.000 2007-02-0111:31:58.000 73
8 78290000 0871138889904 2007-02-01 11:33:47.000 2007-02-01 11:35:00.00073
9 68290000 035730928379 2007-02-01 11:52:20.000 2007-02-0111:54:56.000 156
10 68290000 0298521811199 2007-02-01 12:44:45.000 2007-02-01 12:45:04.000 19
求其中同一个号码的两次通话之间间隔大于10秒的通话记录ID
例如：6，7，8，9，10条记录均符合。

（二）

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,(selects#,score
from SC where C#=’002’) b
where a.score>b.score and a.s#=b.s#;
2、查询平均成绩大于60分的同学的学号和平均成绩；
select S#,avg(score)
from sc
group by S# having avg(score) >60;
3、查询所有同学的学号、姓名、选课数、总成绩；
select Student.S#,Student.Sname,count(SC.C#),sum(score)
from Student left Outer join SC on Student.S#=SC.S#
group by Student.S#,Sname
4、查询姓“李”的老师的个数；
select count(distinct(Tname))
from Teacher
where Tname like ‘李%’;
5、查询没学过“叶平”老师课的同学的学号、姓名；
select Student.S#,Student.Sname
from Student
where S# not in (select distinct( SC.S#) fromSC,Course,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# andTeacher.Tname=’叶平’);
6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
select Student.S#,Student.Sname from Student,SC where Student.S#=SC.S#and SC.C#=’001’and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# andSC_2.C#=’002’);
7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；
select S#,Sname
from Student
where S# in (select S# from SC ,Course ,Teacher where SC.C#=Course.C#and Teacher.T#=Course.T# and Teacher.Tname=’叶平’ groupby S# having count(SC.C#)=(select count(C#) from Course,Teacher whereTeacher.T#=Course.T# and Tname=’叶平’));
8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；
Select S#,Sname from (select Student.S#,Student.Sname,score ,(selectscore from SC SC_2 where SC_2.S#=Student.S# and SC_2.C#=’002’) score2
from Student,SC where Student.S#=SC.S# and C#=’001’) S_2 where score2