数据库面试（一）
1.用一条sql语句，查询出每门课都大于80分的学生姓名。
?
namekechengfenshu
张三语文81
张三数学75
李四语文76
李四数学90
王五语文81
王五数学100
王五英语90
?
select distinct t1.namefrom stu_grade t1 where t1.name not in (select distinct t2.name from stu_grade t2 where t2.fenshu < 80);
?
?
2.学生表如下：
idsnnamecourse_idcourse_namegrade
12005001张三0001数学69
22005002李四0001数学89
32005001张三0001数学69
删除除了id不同，其他都相同的学生冗余信息。
?
#oracledelete from student where id not in (select min(id) from student as t group by sn,name,course_id,course_name,grade);
?
（1）在where的左右都不能使用组函数；
（2）一般，可以使用exists代替in，但是在in的条件中使用了组函数查询到值，就不能用exists代替。
?
?
#mysqldelete student as a from student as a,(select *, min(id) from student group by sn,name,course_id,course_name,grade having count(1) >1) as bwhere a.sn=b.snand a.name=b.nameand a.course_id=b.course_idand a.course_name=b.course_nameand a.grade=b.gradeand a.id>b.id;
?
?
#mysqldelete from student where id not in (select temp.value from (select min(id) value from student as t group by sn,name,course_id,course_name,grade) as temp);?
?
3. 插入相同记录。
?
insert into student(sn,name,course_id,course_name,grade) select sn,name,course_id,course_name,grade from student where id=7;
?
?
4.现有球队表team，只有一个字段name，共4条记录，A，B，C，D为四只球队的名称。4只球队进行比赛，用一条sql语句写出所有可能比赛的组合。
?
select t2.name, t1.name from team t1, team t2 where t1.name != t2.name;?
?
5. 原表为：
course_idcourse_namescore
1java70
2oracle90
3xml40
4jsp30
5servlet80
为了便于阅读，查询此表后的结果显示如下：（及格分数为60）：
?
course_idcourse_namescoremark
1java70pass
2oracle90pass
3xml40fail
4jsp30fail
5servlet80pass
?
?
?
select *,IF(score<60,"fail","pass") as mark from course;?
?
6.表a:
yearmonthamount
199111.1
199121.2
199131.3
199141.4
199212.1
199222.2
199232.3
199242.4
用sql语句，查询成下面结果：
yearm1m2m3m4
19911.11.21.31.4
19922.12.22.32.4
?
selectyear,(select amount from a a1 where a1.year=a.year and month=1 ) as m1,(select amount from a a1 where a1.year=a.year and month=2) as m2,(select amount from a a1 where a1.year=a.year and month=3) as m3,(select amount from a a1 where a1.year=a.year and month=4) as m4from a group by year
?
?
7.有两张表A和B，均有key和value两个字段，如果B的key在A中也有，那么把B的value换为A中对应的value。
?
#oracleupdate B set B.value=(select A.value from A where B.mykey=A.mykey) where B.mykey in (select B.mykey from B,A where B.mykey=A.mykey);?
?
?
#mysqlupdate B set value=(select A.value from A where A.mykey = B.mykey)where mykey in (select temp.mykey from (select B.* from B,A where B.mykey=A.mykey) as temp);?
?
?
?
?
