原标题:数据库面试题-sql语句
1,写出一条Sql语句:取出表A中第31到第40记录(SQLServer,以自动增长的ID作为主键,注意:ID可能不是连续的。
答:
→ 解1: select top 10 * from A where id not in (select top 30 id from A)
演变步骤:
1)select top 30 id from T_FilterWords–取前条
2)select * from T_FilterWords where id not in (select top 30 id from T_FilterWords)–取id不等于前三十条的–也就是把前条排除在外
3)select top 10 * from T_FilterWords where id not in (select top 30 id from T_FilterWords)
–取把前条排除在外的前条,也就是-40条
→ 解2: select top 10 * from A where id > (select max(id) from (select top 30 id from A )as A)
→ 解3:用ROW_NUMBER实现
/———————————————–+———————————————–\
下面的SQL题是常考题中的常考题,必须重视!!!!!!!!
2,横表、纵表转换(常考!!!)
1)纵表结构 TableA
NameCourseGrade张三语文75张三数学80张三英语90李四语文95李四数学55
横表结构 TableB
Name语文数学英语张三758090李四95550
先理解:
select Name,
(case Course when ‘语文‘ then Grade else 0 end) as 语文,
(case Course when ‘数学‘ then Grade else 0 end) as 数学,
(case Course when ‘英语‘ then Grade else 0 end) as 英语
from TableA
然后理解标准答案:
select Name,
sum(case Course when ‘语文‘ then Grade else 0 end) as 语文,
sum(case Course when ‘数学‘ then Grade else 0 end) as 数学,
sum(case Course when ‘英语‘ then Grade else 0 end) as 英语
from TableA
group by Name
2)、横表转纵表的”SQL”示例
横表结构: TEST_H2Z
ID 姓名 语文 数学 英语
1 张三 80 90 70
2 李四 90 85 95
3 王五 88 75 90
转换后的表结构:
ID 姓名 科目 成绩
1 张三 语文 80
2 张三 数学 90
3 张三 英语 70
4 李四 语文 90
5 李四 数学 80
6 李四 英语 99
7 王五 语文 85
8 王五 数学 96
9 王五 英语 88
横表转纵表SQL示例:
SELECT 姓名,’语文’ AS 科目,语文 AS 成绩 FROM TEST_H2Z UNION ALL
SELECT 姓名,’数学’ AS 科目,数学 AS 成绩 FROM TEST_H2Z UNION ALL
SELECT 姓名,’英语’ AS 科目,英语 AS 成绩 FROM TEST_H2Z
ORDER BY 姓名,科目 DESC;
3,删除姓名、年龄重复的记录(只保留Id最大的一条)(常考!!!)
Id name age salary
1 yzk 80 1000
2 yzk 80 2000
3 tom 20 20000
4 tom 20 20000
5 im 20 20000
//取得不重复的数据
select * from Persons
where Id in
(
SELECT MAX(Id) AS Expr1
FROM Persons
GROUP BY Name, Age
)
→ 根据姓名、年龄分组,取出每组的Id最大值,然后将Id最大值之外的排除。
删除重复的数据:
delete from Persons
where Id not in
(
SELECT MAX(Id) AS Expr1
FROM Persons
GROUP BY Name, Age
)
4,下面是一个由*号组成的4行倒三角形图案。
→ 要求:1、输入倒三角形的行数,行数的取值3-21之间,对于非法的行数,要求抛出提示“非法行数!”;2、在屏幕上打印这个指定了行数的倒三角形。
*******
*****
***
*
5,一个文本文件含有如下内容:
4580616022644994|3000|赵涛
4580616022645017|6000|张屹
4580616022645090|3200|郑欣夏
→ 上述文件每行为一个转账记录,第一列表示帐号,第二列表示金额,第三列表示开户人姓名。
创建一张数据库表(MS SQLServer数据库,表名和字段名自拟),请将上述文件逐条插入此表中。
6、一个文本文件含有如下内容,分别表示姓名和成绩:
张三 90
李四 96
王五 78
赵六 82
→ 提供用户一个控制台界面,允许用户输入要查询的姓名,输入姓名并且按回车以后,打印出此人的成绩,如果不输入姓名直接按回车则显示所有人的姓名以及成绩。(注意:不能使用数据库)
7,表A字段Id为numberic(18,0),哪个SQL语句是错误的:
select * from A where id=”;
select * from A where id=’13′;
select * from A where id=null;
select * from A where id=’ 13′;
8,在SQLServer中求当前时间与2012-01-01 0:0:0相差的秒数?
9,做一个表格,三行三列,第一列,前两行合一;第二行,后两列合一。
10,
表一:student_info
学号姓名性别出生年月家庭住址备注0001张三男1981-8-9北京NULL
表二:curriculum
课程编号课程名称学分0001计算机基础20002C语言2
表三:grade
学号课程编号分数00010001800001000290
题目:
→ 条件查询:
在GRADE表中查找80-90份的学生学号和分数
select 学号,分数 from grade where 分数 between 80 and 90
在GRADE 表中查找课程编号为003学生的平均分
select avg(分数) from grade where 课程编号=’003′
在GRADE 表中查询学习各门课程的人数
Select课程编号,count(学号) as 人数from grade group by 课程编号
查询所有姓张的学生的学号和姓名
select 姓名,学号 from student_info where 姓名 like ‘张%’
→ 嵌套查询:
1,查询和学号’0001’的这位同学性别相同的所有同学的姓名和出生年月
select 姓名,出生年月 from student_info where 性别 in(select 性别 from student_info where sno=’0001′)
2,查询所有选修课程编号为0002 和0003的学生的学号、姓名和性别
select 学号,姓名,性别 from student_info where 学号 in(select 学号 from grade where 课程编号=’0002′ and 学号 in(select 学号 from grade where 课程编号=’0001′))
3,查询出学号为0001的学生的分数比0002号学生最低分高的课程编号的课程编号和分数
select 课程编号, 分数 from grade where 学号=’0001′ and 分数>(select min(分数) from grade where 学号=’0002′)
→ 多表查询:
1,查询分数在80-90分的学生的学号、姓名、分数
select student_info.学号,student_info.姓名,grade.分数 from student_info,grade where grade.分数 between 80 and 90
2,查询学习了’C语言’课程的学生学号、姓名和分数
select student_info.学号,student_info.姓名,grade.成绩from student_info,grade,curriculum where student_info.学号=grade.学号and grade.课程号=curriculum.课程号and curriculum.课程名=’C语言’
3,查询所有学生的总成绩,要求列出学号、姓名、总成绩,没有选课的学生总成绩为空。
select grade.学号,student_info.姓名,sum(grade.成绩) as 总成绩from student_info,grade where grade.学号=student_info.学号group by grade.学号,student_info.姓名
11,题目、活期存款中,“储户”通过“存取款单”和“储蓄所”发生联系。假定储户包括:账号,姓名,电话,地址,存款额;“储蓄所”包括:储蓄所编号,名称,电话,地址(假定一个储户可以在不同得储蓄所存取款)
1、写出设计以上表格的语句(4分)
2、创建一个触发器TR1完成下面内容:
→ 当向“存取款单”表中插入数据时,如果存取标志=1则应该更改储户表让存款额加上存取金额,如果存取标志=0则应该更改储户表让存款额减去存取金额,如果余额不足显示余额不足错误。
CREATE TABLE CREATE TRIGGER tr1 on qukuan after insert
AS
BEGIN
declare @sid nvarchar(50)
declare @type int
declare @qian int
declare @yuer int
select @sid=sid,@type=[type],@m=m from inserted
select @yuer=yuer from cunkuan
if(@type=1)
begin
update cunkuan set yuer=yuer+@qian
end
else
begin
if(@yuer
begin
print ‘余额不足’
end
else
begin
update cunkuan set yuer=yuer-@qian<