数据库面试（一）

1.用一条sql语句，查询出每门课都大于80分的学生姓名。

 

name kecheng fenshu

张三 语文 81

张三 数学 75

李四 语文 76

李四 数学 90

王五 语文 81

王五 数学 100

王五 英语 90

 

select distinct t1.name
from stu_grade t1 
where t1.name not in (
	select distinct t2.name from stu_grade t2 where t2.fenshu < 80
);

 

 

2.学生表如下：

id sn name course_id course_name grade

1 2005001 张三 0001 数学 69

2 2005002 李四 0001 数学 89

3 2005001 张三 0001 数学 69

删除除了id不同，其他都相同的学生冗余信息。

 

#oracle
delete 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代替。

 

 

#mysql
delete 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 b
where a.sn=b.sn
and a.name=b.name
and a.course_id=b.course_id
and a.course_name=b.course_name
and a.grade=b.grade
and a.id>b.id;

 

 

#mysql
delete 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_id course_name score

1 java 70

2 oracle 90

3 xml 40

4 jsp 30

5 servlet 80

为了便于阅读，查询此表后的结果显示如下：（及格分数为60）：

 

course_id course_name score mark

1 java 70 pass

2 oracle 90 pass

3 xml 40 fail

4 jsp 30 fail

5 servlet 80 pass

 

 

 

select *,IF(score<60,"fail","pass") as mark from course;

 

 

6.表a:

year month amount

1991 1 1.1

1991 2 1.2

1991 3 1.3

1991 4 1.4

1992 1 2.1

1992 2 2.2

1992 3 2.3

1992 4 2.4

用sql语句，查询成下面结果：

year m1 m2 m3 m4

1991 1.1 1.2 1.3 1.4

1992 2.1 2.2 2.3 2.4

 

select
year,
(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 m4
from a group by year

 

 

7.有两张表A和B，均有key和value两个字段，如果B的key在A中也有，那么把B的value换为A中对应的value。

 

#oracle
update 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
);

 

 

 

#mysql
update 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
);