Sql面试题

(1)、

已知一个表的结构为:

姓名 科目 成绩
张三 语文 20
张三 数学 30
张三 英语 50
李四 语文 70
李四 数学 60
李四 英语 90

1.怎样通过select语句把他变成以下结构:

姓名 语文成绩 数学成绩 英语成绩
张三      20          30          50
李四      70          60          90
----法一: 正解如下:
select A.姓名,A.成绩as语文成绩,B.成绩as数学成绩,C.成绩as英语成绩
from student A,student B,student C
where A.姓名=B.姓名and B.姓名=C.姓名
and A.科目='语文' and B.科目='数学' and C.科目='英语'
----法二:正解如下
select姓名,
sum(case科目when '语文' then 成绩else 0 end) as 语文成绩,
sum(case科目when '数学' then 成绩else 0 end) as 数学成绩,
sum(case科目when '英语' then 成绩else 0 end) as 英语成绩,
avg(成绩) as 平均成绩,sum(成绩) as 总成绩
from student
group by 姓名
order by 姓名desc

2.查询出每门课都大于80 分的学生姓名

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)、

已知表的结构

Year Quarter Amount
2000 1 1.1
2000 2 1.2
2000 3 1.3
2000 4 1.4
2001 1 2.1
2001 2 2.2
2001 3 2.3
2001 4 2.4

其中每行表表示一个季度的数据。

如果处理表A中的数据,得到如下的结果。

Year Quarter1 Quarter2 Quarter3 Quarter4
2000   1.1      1.2     1.3     1.4
2001   2.1      2.2     2.3     2.4
---法一:正解如下:
select T1.YEAR,T1.amount as Quarter1,T2.amount as Quarter2,T3.amount as Quarter3,T4.amount as Quarter4
from A T1,A T2,A T3,A T4
WHERE T1.YEAR=T2.YEAR AND T2.YEAR=T3.YEAR AND T3.YEAR=T4.YEAR
AND T1.Quarter='1' and T2.Quarter='2' and T3.Quarter='3' and T4.Quarter='4'
select year,
sum(case Quarter when '1' then Amount else 0 end) as Quarter1,
sum(case Quarter when '2' then Amount else 0 end) as Quarter2,
sum(case Quarter when '3' then Amount else 0 end) as Quarter3,
sum(case Quarter when '4' then Amount else 0 end) as Quarter4,
sum(Amount) as ALLAmount
from A
group by year
order by year

(3)、

学生表 如下:
自动编号 学号 姓名 课程编号 课程名称 分数

1        2005001 张三 0001     数学    69
2        2005002 李四 0001      数学    89
3        2005001 张三 0001      数学    69

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

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

(4)、

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

select a.name, b.name
from team a, team b 
where a.name < b.name

(5)、

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

SQL:

select * into b from a where 1<>1       (where1=1,拷贝表结构和数据内容)
insert into b(a, b, c) select d,e,f from a; 

ORACLE:

create table b
As
Select * from a where 1=2

(6)、

说明:日程安排提前五分钟提醒

SQL:

select * from 日程安排 where datediff('minute', 开始时间,getdate())>5

(7)、

说明:两张关联表,删除主表中已经在副表中没有的信息

SQL:

Delete from a where not exists (select * from b where a.id=b.id )

(8)、

有两个表A 和B ,均有key 和value 两个字段,如果B 的key 在A 中也有,就把B 的value 换为A 中对应的value

update b set b.value=(select a.value from a where a.key=b.key) where b.id in(select b.id from b,a where b.key=a.key);

(9)、
原表:
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 ,decode(sign(score-60),-1,'fail','pass') as mark from course

(10)、

有表A,结构如下:
A: p_ID p_Num s_id
1 10 01
1 12 02
2 8 01
3 11 01
3 8 03
其中:p_ID为产品ID,p_Num为产品库存量,s_id为仓库ID。请用SQL语句实现将上表中的数据合并,合并后的数据为:
p_ID s1_id s2_id s3_id
1 10 12 0
2 8 0 0
3 11 0 8
其中:s1_id为仓库1的库存量,s2_id为仓库2的库存量,s3_id为仓库3的库存量。如果该产品在某仓库中无库存量,那么就是0代替。

结果:

select p_id ,
sum(case when s_id=1 then p_num else 0 end) as s1_id
,sum(case when s_id=2 then p_num else 0 end) as s2_id
,sum(case when s_id=3 then p_num else 0 end) as s3_id
from myPro group by p_id

(11)、

查询A(ID,Name)表中第31至40条记录,ID作为主键可能是不是连续增长的列,完整的查询语句如下:

第一种:
select top 10 * from A where ID >(select max(ID) from (select top 30 ID from A order by A.ID ) T) order by A.ID
第二种:
select top 10 * from A where ID not in(select top 30 ID from A)


(12)、

查询表A中存在ID重复三次以上的记录

select * from(select count(ID) as count from table group by ID)T where T.count>3
阅读更多
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/hyunbar/article/details/80316410
文章标签: sql
个人分类: SQL 面试
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭