java面试题网

普通会员

216

帖子

75

回复

160

积分

楼主
发表于 2019-05-06 10:29:03 | 查看: 49| 回复: 0

SQL笔试题:下面是学生表(student)的结构说明

字段名称

字段解释

字段类型

字段长度

约束

s_id

学号

字符

10

PK

s_name

学生姓名

字符

50

Not null

s_age

学生年龄

数值

3

Not null

s-sex

学生性别

字符(男:1女:0)

1

Not null

下面是教师表(Teacher )的结构说明

字段名称

字段解释

字段类型

字段长度

约束

t_id

教师编号

字符

10

PK

t_name

教师名字

字符

50

Not null

下面是课程表(Course)的结构说明

字段名称

字段解释

字段类型

字段长度

约束

c_id

课程编号

字符

10

PK

c_name

课程名字

字符

50

Not null

t_id

教师编号

字符

10

Not null

下面是成绩表(SC)的结构说明

字段名称

字段解释

字段类型

字段长度

约束

s_id

学号

字符

10

PK

c_id

课程编号

字符

10

Not null

score

成绩

数值

3

Not null

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

select a.s_id from (select s_id,score from SC where C_ID='001') a,(select s_id,score

from SC where C_ID='002') b
where a.score>b.score and a.s_id=b.s_id;

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

select S_ID,avg(score)
from sc
group by S_ID having avg(score) >60;

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

select Student.S_ID,Student.Sname,count(SC.C_ID),sum(score)
from Student left Outer join SC on Student.S_ID=SC.S_ID
group by Student.S_ID,Sname

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

select count(distinct(Tname))
from Teacher
where Tname like '李%';

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

select Student.S_ID,Student.Sname
from Student
where S_ID not in (select distinct( SC.S_ID) from SC,Course,Teacher where SC.C_ID=Course.C_ID and Teacher.T#=Course.T# and Teacher.Tname='叶平');

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

elect Student.S_ID,Student.Sname from Student,SC where Student.S_ID=SC.S_ID and SC.C_ID='001'and exists( Select * from SC as SC_2 where SC_2.S_ID=SC.S_ID and SC_2.C_ID='002');

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

select S_ID,Sname
from Student
where S_ID in (select S_ID from SC ,Course ,Teacher where SC.C_ID=Course.C_ID and Teacher.T#=Course.T# and Teacher.Tname='叶平' group by S_ID having count(SC.C_ID)=(select count(C_ID) from Course,Teacher where Teacher.T#=Course.T# and Tname='叶平'));

8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;

Select S_ID,Sname from (select Student.S_ID,Student.Sname,score ,(select score from SC SC_2 where SC_2.S_ID=Student.S_ID and SC_2.C_ID='002') score2
from Student,SC where Student.S_ID=SC.S_ID and C_ID='001') S_2 where score2 < score;

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

select S_ID,Sname
from Student
where S_ID not in (select S.S_ID from Student AS S,SC where S.S_ID=SC.S_ID and score>60);

10、查询没有学全所有课的同学的学号、姓名;

select Student.S_ID,Student.Sname
from Student,SC
where Student.S_ID=SC.S_ID group by Student.S_ID,Student.Sname having count(C_ID) <(select count(C_ID) from Course);

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

select distinct S_ID,Sname from Student,SC where Student.S_ID=SC.S_ID and SC.C_ID in (select C_ID from SC where S_ID='1001');

12、查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名;

select distinct SC.S_ID,Sname
from Student,SC
where Student.S_ID=SC.S_ID and C_ID in (select C_ID from SC where S_ID='001');



您的帖子在2019-05-06 10:55:29被专注java技术研究编辑
您需要登录后才可以回帖 登录 | 立即注册

java面试题网无聊看看网与java建站系统提供技术支持V2.1 网站地图 © 2016-2018