sql

常见面试sql

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

name   kecheng   fenshu

张三    语文    81

张三    数学    75

李四    语文    76

李四    数学     90

王五    语文    81

王五    数学    100

王五    英语    90

A: select distinct name from table where name not in (select distinct name from table where fenshu<=80)

B:select name from table group by name having min(fenshu)>80

2. 学生表 如下:

自动编号   学号  姓名 课程编号 课程名称 分数

1     2005001 张三   0001   数学   69

2     2005002 李四   0001   数学   89

3     2005001 张三   0001   数学   69

删除除了自动编号不同, 其他都相同的学生冗余信息

A: delete tablename where 自动编号 not in(select min(自动编号) from tablename group by学号, 姓名, 课程编号, 课程名称, 分数)

3.一个叫team的表,里面只有一个字段name,一共有4条纪录,分别是a,b,c,d,对应四个球队,现在四个球队进行比赛,用一条sql语句显示所有可能的比赛组合.

答:select a.name, b.name

from team a, team b 

where a.name < b.name

4.面试题:怎么把这样一个

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

查成这样一个结果

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 aaa m where month=1 and m.year=aaa.year) as m1,

(select amount from aaa m where month=2 and m.year=aaa.year) as m2,

(select amount from aaa m where month=3 and m.year=aaa.year) as m3,

(select amount from  aaa m where month=4 and m.year=aaa.year) as m4

from aaa group by year

*********************************************************************

5.说明:复制表(只复制结构,源表名:a新表名:b) 

SQL: select * into b from a where 1<>1 (where1=1,拷贝表结构和数据内容)

ORACLE:create table b

As

Select * from a where 1=2


[<>(不等于)(SQL Server Compact)

比较两个表达式。 当使用此运算符比较非空表达式时,如果左操作数不等于右操作数,则结果为 TRUE。 否则,结果为 FALSE。]

6.

原表:

courseid coursename score

-------------------------------------

1 java 70

2 oracle 90

3 xml 40

4 jsp 30

5 servlet 80

-------------------------------------

为了便于阅读,查询此表后的结果显式如下(及格分数为60):

courseid coursename score mark

---------------------------------------------------

1 java 70 pass

2 oracle 90 pass

3 xml 40 fail

4 jsp 30 fail

5 servlet 80 pass

---------------------------------------------------

写出此查询语句

select courseid, coursename ,score ,if(score>=60, "pass","fail")  as mark from course

7.表名:购物信息

购物人      商品名称    数量

A            甲          2

B            乙          4

C            丙          1

A            丁          2

B            丙          5

……

给出所有购入商品为两种或两种以上的购物人记录

答:select * from 购物信息 where 购物人 in (select 购物人 from 购物信息 group by 购物人 having count(*) >= 2);

8.

info 表

date result

2005-05-09 win

2005-05-09 lose

2005-05-09 lose

2005-05-09 lose

2005-05-10 win

2005-05-10 lose

2005-05-10 lose

如果要生成下列结果, 该如何写sql语句?

        win lose

2005-05-09  2  2

2005-05-10  1  2

答案:

(1) select date, sum(case when result = "win" then 1 else 0 end) as "win", sum(case when result = "lose" then 1 else 0 end) as "lose" from info group by date;

(2) select a.date, a.result as win, b.result as lose

  from

  (select date, count(result) as result from info where result = "win" group by date) as a

  join

  (select date, count(result) as result from info where result = "lose" group by date) as b

  on a.date = b.date;