知乎50题目

SQL笔试题50道答案整理

CREATE TABLE Student (

s_id  VARCHAR(20),

s_name  VARCHAR(20) NOT NULL DEFAULT '',

s_birth  VARCHAR(20) NOT NULL DEFAULT '',

s_sex  VARCHAR(10) NOT NULL DEFAULT '',

PRIMARY KEY( s_id )

);

--课程表

CREATE TABLE  Course (

c_id  VARCHAR(20),

c_name  VARCHAR(20) NOT NULL DEFAULT '',

t_id  VARCHAR(20) NOT NULL,

PRIMARY KEY( c_id )

);

--教师表

CREATE TABLE  Teacher (

t_id  VARCHAR(20),

t_name  VARCHAR(20) NOT NULL DEFAULT '',

PRIMARY KEY( t_id )

);

--成绩表

CREATE TABLE  Score (

s_id  VARCHAR(20),

c_id  VARCHAR(20),

s_score  INT ,

PRIMARY KEY( s_id , c_id )

);

--插入学生表测试数据

insert into Student values('01' , '赵雷' , '1990-01-01' , '男');

insert into Student values('02' , '钱电' , '1990-12-21' , '男');

insert into Student values('03' , '孙风' , '1990-05-20' , '男');

insert into Student values('04' , '李云' , '1990-08-06' , '男');

insert into Student values('05' , '周梅' , '1991-12-01' , '女');

insert into Student values('06' , '吴兰' , '1992-03-01' , '女');

insert into Student values('07' , '郑竹' , '1989-07-01' , '女');

insert into Student values('08' , '王菊' , '1990-01-20' , '女');

--课程表测试数据

insert into Course values('01' , '语文' , '02');

insert into Course values('02' , '数学' , '01');

insert into Course values('03' , '英语' , '03');

--教师表测试数据

insert into Teacher values('01' , '张三');

insert into Teacher values('02' , '李四');

insert into Teacher values('03' , '王五');

--成绩表测试数据

insert into Score values('01' , '01' , 80);

insert into Score values('01' , '02' , 90);

insert into Score values('01' , '03' , 99);

insert into Score values('02' , '01' , 70);

insert into Score values('02' , '02' , 60);

insert into Score values('02' , '03' , 80);

insert into Score values('03' , '01' , 80);

insert into Score values('03' , '02' , 80);

insert into Score values('03' , '03' , 80);

insert into Score values('04' , '01' , 50);

insert into Score values('04' , '02' , 30);

insert into Score values('04' , '03' , 20);

insert into Score values('05' , '01' , 76);

insert into Score values('05' , '02' , 87);

insert into Score values('06' , '01' , 31);

insert into Score values('06' , '03' , 34);

insert into Score values('07' , '02' , 89);

insert into Score values('07' , '03' , 98);

select * from[dbo].[Course]

select * from[dbo].[Score]

select * from[dbo].[Student]

select * from[dbo].[Teacher]

--11、查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名

select distinct a.s_id, a.s_name

from Student a join Score b on a.s_id= b.s_id

where c_id in

(select c_id from Score where s_id = '01')

and a.s_id<>'01';

--※10、查询没有学全所有课的学生的学号、姓名

--select[s_id], [s_name]from [dbo].[Student] where not in(select [s_id] from [dbo].[Score] where c_id)

--写法一：

select Student.s_id, Student.s_name

from Student join Score on Score.s_id = Student.s_id

group by Student.s_id, s_name

having count(Score.c_id) < (select count(c_id) from Course);

--写法二：

select s_id, s_name

from Student

where s_id IN (SELECT s_id FROM Score group by s_id

having count(c_id)<(select count(c_id) from Course))

--小于还可以是不等于

--9、查询所有课程成绩小于60分的学生的学号、姓名

select s1.s_id,s1.s_name from  [dbo].[Student] s1 join [dbo].[Score] s2

on s1.s_id=s2.s_id where s_score<60--mistake

select[s_id], [s_name]from [dbo].[Student] where [s_id]

not in(select [s_id]from [dbo].[Score] where [s_score]>=60)--√

select s_id, s_name

from Student

where s_id NOT IN (SELECT s_id FROM Score where s_score >=60)--answer

--8、查询课程编号为“02”的总成绩

select sum(s_score) from [dbo].[Score] where [c_id]='02'

select sum(s_score)

from Score

where c_id = '02';--answer

--※7、查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名

select s1.s_id,s1.s_name  from [dbo].[Student] s1 join [dbo].[Score] s2

on s1.s_id=s2.s_id where s2.c_id in('01','02')--mistake


  --写法一：

select s_id, s_name from Student

where s_id in

(select a.s_id from

(select s_id from Score where c_id = '01') as a

join (select s_id from Score where c_id ='02') as b

on a.s_id= b.s_id);

--写法二：

select s_id,s_name from Student

where s_id in

(select s_id from Score where c_id = '01')

AND s_id in

(select s_id from Score where c_id = '02')

--写法三：

select a.s_id,a.s_name

from Student a JOIN Score b ON a.s_id=b.s_id

JOIN Score c ON a.s_id=c.s_id

where b.c_id='01' and c.c_id='02'

and b.s_id=c.s_id ;

--6、查询学过“张三”老师所教的所有课的同学的学号、姓名

select s1.s_id,s1.s_name from [dbo].[Student] s1 join [dbo].[Score] s2 on s1.s_id=s2.s_id join [dbo].[Course] c

on s2.c_id=c.c_id join [dbo].[Teacher] t on c.t_id=t.t_id where t.t_name='张三' --√

select s.s_id,s_name from [dbo].[Student] s where s.s_id in(select s.s_id from

[dbo].[Course] c join [dbo].[Teacher] t on c.t_id=t.t_id where t.t_name='张三')--mistake

--写法一：

select s_id, s_name

from Student

where s_id in

(select s_id from Score join Course on Score.c_id = Course.c_id

join Teacher on Course.t_id = Teacher.t_id

where t_name = '张三');

--写法二：

select Student.s_id, s_name

from Student JOIN Score on Student.s_id = Score.s_id

JOIN Course on Score.c_id = Course.c_id

JOIN Teacher on Teacher.t_id = Course.t_id

where t_name='张三';---连接4个表√

--※5.查询没学过“张三”老师课的学生的学号、姓名(重点)

select s1.s_id,s1.s_name from [dbo].[Student] s1 join [dbo].[Score] s2 on s1.s_id=s2.s_id join [dbo].[Course] c

on s2.c_id=c.c_id join [dbo].[Teacher] t on c.t_id=t.t_id where t.t_name!='张三'--mistake

select s.s_id,s_name from [dbo].[Student] s,[dbo].[Course] c ,[dbo].[Teacher] t where s.s_id=c.c_id and

c.t_id=t.t_id and t.t_name !='张三'--mistake

select s.s_id,s_name from [dbo].[Student] s join [dbo].[Course] c on s.s_id=c.c_id join [dbo].[Teacher] t on

c.t_id=t.t_id where t.t_name!='张三'--mistake

select s_id, s_name

from Student

where s_id not in

(select s_id from Score join Course on Score.c_id = Course.c_id

join Teacher on Course.t_id = Teacher.t_id

where t_name = '张三');--answer

--4、查询姓“张”的老师的个数

select count(*) from [dbo].[Teacher] where t_name like '张%'

select count(t_id)

from Teacher

where t_name like '张%';---answer

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

select s1.[s_id] ,[s_name],count([c_id]),sum([s_score]) from [dbo].[Score] s1

join [dbo].[Student]  s2 on s1.s_id=s2.s_id group by s1.[s_id],[s_name]

select Score.s_id AS '学号', s_name AS '姓名',count(c_id)AS '选课数',sum(s_score)AS '总成绩'

from Score join Student on Score.s_id = Student.s_id

group by Score.s_id,s_name;--answer

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

select [s_id],AVG([s_score]) from [dbo].[Score] where [s_score]>60 group by [s_id]

select s_id, avg(s_score)

from Score

group by s_id

having avg(s_score)>60;--标准答案

--※1、查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号。

--select * from [dbo].[Course] c,[dbo].[Score] s1,[dbo].[Student] s2 where c.c_id=s1.c_id and  s1.s_id=s2.s_id and

-- 写法一：

select a.s_id from [dbo].[Score] a join [dbo].[Score] b on a.s_id=b.s_id and a.s_score>b.s_score where

a.c_id='01' and b.c_id='02'

select a.s_id

from Score a join Score b on a.s_id = b.s_id and a.s_score >b.s_score

where a.c_id = '01' and b.c_id= '02';

-- 写法二：

select a.s_id

from (select * from Score where c_id = '01') as a

join (select * from Score where c_id='02') as b

on a.s_id = b.s_id

where a.s_score > b.s_score;

©著作权归作者所有,转载或内容合作请联系作者
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

推荐阅读更多精彩内容

  • sql
    50个常用的sql语句Student(S#,Sname,Sage,Ssex) 学生表Course(C#,Cname...
    哈哈海阅读 1,261评论 0赞 7
  • mysql练习题
    Student(S#,Sname,Sage,Ssex) 学生表 Course(C#,Cname,T#) 课程表 S...
    忘了呼吸的那只猫阅读 2,946评论 0赞 8
  • 摘录的一些知识点
    ORACLE自学教程 --create tabletestone ( id number, --序号usernam...
    落叶寂聊阅读 1,156评论 0赞 0
  • SQL SERVER
    说明：以下五十个语句都按照测试数据进行过测试，最好每次只单独运行一个语句。 问题及描述： --1.学生表 Stud...
    lijun_m阅读 1,339评论 0赞 1
  • 【sql注入天书】黑客必学
    MSSQL 跨库查询(臭要饭的!黑夜) 榨干MS SQL最后一滴血 SQL语句参考及记录集对象详解 关于SQL S...
    碧海生曲阅读 5,729评论 0赞 1