常⽤的sql语句，学⽣、课程、成绩、教师表的查询
常用的sql查询语句有哪些Student<span >(</span>S#<span >,</span>Sname<span >,</span>Sage<span >,</span>Ssex<span >) </span>学⽣表
Course(C#,Cname,T#) 课程表
SC<span >(</span>S#<span >,</span>C#<span >, </span>score<span >) </span>成绩表
Teacher(T#,Tname) 教师表
create table Student(S# varchar(20),Sname varchar(10),Sage int,Ssex varchar(2))
前⾯加⼀列序号：
<span >if</span>
exists(select table_name from information_schema.tables
<span > </span><span >where </span>table_name<span >= </span><span >'Temp_Table'</span><span >)</span>
drop table Temp_Table
go
select 排名=identity(int,1,1),* INTO Temp_Table from Student
go
select * from Temp_Table
go
<span >drop database </span>[ ] <span >--删除空的没有名字的数据库</span>
问题：
1、查询“”课程⽐“”课程成绩⾼的所有学⽣的学号；
<span >select </span>a<span >.</span>S# <span >from
</span><span >(</span><span >select </span>s#<span >,</span>score <span >from </span>SC <span >where </span>C#<span >=</span><span >'001'</span><span >) </span>a<span >,(</span><span >select </span>s#<span >,</span>score
from SC where C#='002') b
<span >where </span>a<span >.</span>score<span >> </span>b<span >.</span>score <span >and </span>a<span >.</span>s#<span >=</span>b<span style="col
or: rgb(128, 128, 128);">.</span>s#<span >; </span>
2、查询平均成绩⼤于分的同学的学号和平均成绩；
select S#,avg(score)
<span > </span><span >from </span>sc
group by S# having avg(score) >60;
3、查询所有同学的学号、姓名、选课数、总成绩；
<span >select </span>Student<span >.</span>S#<span >,</span>Student<span >.</span>Sname<span >,</span><span >count</span><span >(</span>SC<span >.</span>C#<span >),</span><span >sum</span><span >
(</span>score<span >) </span>
from Student left Outer join SC on Student.S#=SC.S#
<span >group by </span>Student<span >.</span>S#<span >,</span>Sname
4、查询姓“李”的⽼师的个数；
select count(distinct(Tname))
<span > </span><span >from </span>Teacher
where Tname like '李%';
5、查询没学过“叶平”⽼师课的同学的学号、姓名；
<span >select </span>Student<span >.</span>S#<span >,</span>Student<span >.</span>Sname
from Student
<span >where </span>S# <span >not in (</span><span >select distinct</span><span >( </span>SC<span >.</span>S#<span
>) </span><span >from </span>SC<span >,
</span>Course<span >,</span>Teacher <span >where </span>SC<span >.</span>C#<span >=</span>Course<span >.</span>C# <span >and </span>Teacher<span >.</span>T#<span >= </span>Course<span >.</span>T# <span >and </span>Teacher<span >.</span>Tname<span >=</span><span >'叶平'</span><span
>); </span>
6、查询学过“”并且也学过编号“”课程的同学的学号、姓名；
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# and SC_2.C#='002');
7、查询学过“叶平”⽼师所教的所有课的同学的学号、姓名；
<span >select </span>S#<span >,</span>Sname
from Student
</span>S# <span >from </span>SC <span >,</span>Course <span >,</span>Teacher <span >where </span>SC<span >.</span>C#<span
>=</span>Course<span >.</span>C# <span >and </span>Teacher<span >.</span>T#<span >=</span>Course<span styl
e="color:
rgb(128, 128, 128);">.</span>T# <span >and </span>Teacher<span >.
</span>Tname<span >=</span><span >'叶平' </span><span >group by </span>S# <span >having </span><span >count</span><span >(</span>SC<span >.</span>C#<span >)=(</span><span
>select </span><span >count</span><span >(</span>C# <span >) </span><span >from </span>Course<span >, </span>Teacher <span >where </span>Teacher<span >.</span>T#<span >=</span>Course<span >.</span>T# <span >and
</span>Tname<span >=</span><span >'叶平'</span><span >)); </span>
8、查询课程编号“”的成绩⽐课程编号“”课程低的所有同学的学号、姓名；
Select S#,Sname from (select Student.S#,Student.Sname,score ,(select score from SC SC_2 where SC_2.S#=Student.S# and SC_2.C#='002') score2
<span >from </span>Student<span >,</span>SC <span >where </span>Student<span >.</span>S#<span >=</span>SC<span
>.</span>S# <span >and </span>C#<span >=
</span><span >'001'</span><span >) </span>S_2 <span >where </span>score2 <span ><</span>score<span >; </span>
9、查询所有课程成绩⼩于分的同学的学号、姓名；
select S#,Sname
<span >from </span>Student
where S# not in (select Student.S# from Student,SC where S.S#=SC.S# and score>60);
10、查询没有学全所有课的同学的学号、姓名；
<span >select </span>Student<span >.</span>S#<span >,</span>Student<span >.</span>Sname
from Student,SC
128);">=</span>SC<span >.</span>S# <span >group by </span>Student<span
>.</span>S#<span >,</span>Student<span >.
</span>Sname <span >having </span><span >count</span><span >(</span>C#<span >) <(</span><span >select </span><span >count</span><span >(</span>C#<span >) </span><span
>from </span>Course<span >); </span>
11、查询⾄少有⼀门课与学号为“”的同学所学相同的同学的学号和姓名；
select S#,Sname from Student,SC where Student.S#=SC.S# and C# in select C# from SC where S#='1001';
12、查询⾄少学过学号为“”同学所有⼀门课的其他同学学号和姓名；
<span >select distinct </span>SC<span >.</span>S#<span >,</span>Sname
from Student,SC
<span >where </span>Student<span >.</span>S#<span >=</span>SC<span >.</span>S# <span >and </span>C# <span >in (</span><span >select </span>C# <span >from </span>SC <span >where </span>S#<span >=</span><span >'001'</span><span >); </span>
13、把“SC”表中“叶平”⽼师教的课的成绩都更改为此课程的平均成绩；
update SC set score=(select avg(SC_2.score)
<span > </span><span >from </span>SC SC_2
where SC_2.C#=SC.C# ) from Course,Teacher where Course.C#=SC.C# and Course.T#=Teacher.T# and Teacher.Tname='叶平');
14、查询和“”号的同学学习的课程完全相同的其他同学学号和姓名；
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论