数据库面试sql

问题一:、有三张表，学生表S，课程表C，学生课程表SC
01:写出建表语句
答:
create table s(id integer primary key,name varchar(20));
create table c(id integer primary key,name varchar(20));
create table sc(
sid integer references s(id),
cid integer references c(id),
primary key(sid,cid)
)

02:写出SQL语句，查询选修了所以选修课程的学生
答:
select stu.id,stu.name from s stu
where(
select COUNT(*) from sc
where sc.sid=stu.id
)=(
select COUNT(*) from c
)

03:写出SQL语句，查询选修了至少5门以上的课程的学生
答:
select stu.id,stu.name from s stu
where (
select COUNT(*) from sc
where sid=stu.id
)>=5

问题二:有三张表、Student学生表(学号、姓名、性别、组织部门)、
Course课程表(编号、课程名称)、
Sc选课表(学号、课程编号、成绩)

01、写一个SQL语句，查询选修了'计算机原理'的学生学号和姓名
答:
select stu.sno,stu.sname from Student stu
where(
select COUNT(*)from sc
where sno=stu.sno
and cno=(
select cno from Course
where cname='计算机原理'
)
)!=0

02、写一个SQL语句，查询'周星驰'同学选修的课程名称
答:
select cname from Course
where cno in(
select cno from sc where sno=(
select sno from Student
where sname='周星驰'
)
)
03、写一个SQL语句，查询选修了5门课程的学生学号和姓名
答:
select stu.sno,stu.sname from Student stu
where (
select COUNT(*) from sc where sno=stu.sno
)=5

04、查询两门以上(包括两门)课程不及格的学生名称以及平均成绩
答:
select s.sno,s,sname,AVG(sc.score)
from Student s,sc
where s.sno=sc.sno
group by s.sno,s.sname
having COUNT(
case when sc.score<60 then 1 end
)>2

问题三:数据库表Test结构如下:

ID NAME AGE MANAGER(所属主管人ID)
106 A 30104
107 B 20108
108 C 19104
109 D 25109
116 E 40120
119 F 45null
要求：列出所有年龄比所属主管年龄大的人的id和姓名？
答:
select employee.name from test employee
where employee.age>(
select manager.age from test manager
where manager.id=employee.manager
)

问题四：有如下两张表
表city:
CityNoCityNameStateNo
bj北京null
sh上海null
gz广州gd
dl大连ln

表state:
StateNoStateName
gd广东
ln辽宁
sd山东
nmg内蒙古

欲得到结果如下:
CityNo CityNameStateNo StateName
bj北京 nullnull
dl大连 ln辽宁
gz广州 gd广东
sh上海 nullnull

答:
select c.cityno,c.cityname,c.stateno,s.statename
from city c,state s
where c.stateno=s.stateno
order by c.cityno

问题五:有三张表
S(SNO,SNAME)学生关系。学号、姓名
C(CNO,CNAME,CTEACHER)课程关系。课程号、课程名称、任课老师
SC(SNO,CNO,SCGRADE)选课关系。SCGRADE成绩

01、找出没有选修过'张三'老师讲授的所有学生姓名
答:
select sname from s
where sno not in(
select sno from sc
where cno in(
select cno from c
where cname='张三'
)
)
02、列出有二门以上(含二门)不及格课程的学生姓名和学号
答:
select sname,(
select AVG(scgrade) from sc where sno=s.sno
) from s
where (select COUNT(*) from sc where sno=s.sno and
scgrade<60
)>=2

03、列出既学过'1'号课程，又学过'2'号课程的所有学生学号和姓名
答:
select sname from s
where sno in(select sno from where cno=1 or cno=2)

04、列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号及姓名
答:
select sname from s
where sno in(
select sno from sc where cno=1 and scgrade>(
select scgrade from sc
where sno=2 and cno=1
)
)

05、列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩
答:
select sname from s
where (select scgrade from sc
where sno=s.no and cno=1
)>(select scgrade from sc
where sno=s.sno and cno=2
)