sql语句练习

(1)表名:购物信息
购物人 商品名称 数量
A 甲 2
B 乙 4
C 丙 1
A 丁 2
B 丙 5
……

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

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

(2)表名:成绩表
姓名 课程 分数
张三 语文 81
张三 数学 75
李四 语文 56
李四 数学 90
王五 语文 81
王五 数学 100
王五 英语 49


给出成绩全部合格的学生信息(包含姓名、课程、分数),注:分数在60以上评为合格

答:select * from 成绩表 where 姓名 not in (select distinct 姓名 from 成绩表 where 分数 < 60)
或者:
select * from 成绩表 where 姓名 in (select 姓名 from 成绩表 group by 姓名 having min(分数) >=60)


(3)表名:商品表
名称 产地 进价
苹果 烟台 2.5
苹果 云南 1.9
苹果 四川 3
西瓜 江西 1.5
西瓜 北京 2.4
……

给出平均进价在2元以下的商品名称

答:select 名称 from 商品表 group by 名称 having avg(进价) < 2

(4)表名:高考信息表
准考证号 科目 成绩
语文 119
数学 108
物理 142
化学 136
物理 127
数学 149
英语 110
语文 105
英语 98
化学 129
……

给出高考总分在600以上的学生准考证号

答:select 准考证号 from 高考信息表 group by 准考证号 having sum(成绩) > 600

(5)表名:高考信息表
准考证号 数学 语文 英语 物理 化学
119 98 127 136
105 110 142 129
……

给出高考总分在600以上的学生准考证号

答:select 准考证号 from 高考信息表 where (数学+语文+英语+物理+化学) > 600



(四部分)
(一)表名:club

id gender age
M 19
F 30
F 27
F 16
M 32
……

查询出该俱乐部里男性会员和女性会员的总数

答:select gender,count(id) from club group by gender

(二)表名:team
ID(number型) Name(varchar2型)
a
b
b
a
c
c
要求:执行一个删除语句,当Name列上有相同时,只保留ID这列上值小的
例如:删除后的结果应如下:
ID(number型) Name(varchar2型)
a
b
c
请写出SQL语句。

delete from team where id not in (select min(id) from team group by name)

(三)表名:student

name course score
张青 语文 72
王华 数学 72
张华 英语 81
张青 物理 67
李立 化学 98
张燕 物理 70
张青 化学 76

查询出“张”姓学生中平均成绩大于75分的学生信息

答:select * from student where name in (select name from student
where name like '张%' group by name having avg(score) > 75)



1.一道SQL语句面试题,关于group by表内容:

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;



2.表中有A B C三列,用SQL语句实现:当A列大于B列时选择A列否则选择B列,当B列大于C列时选择B列否则选择C列

select (case when a > b then a else b end), (case when b > c then b else c end) from table;

3.请取出tb_send表中日期(SendTime字段)为当天的所有记录? (SendTime字段为datetime型,包含日期与时间)

select * from tb where datediff(dd,SendTime,getdate())=0

4.有一张表,里面有3个字段:chinese,math,english。其中有一条记录chinese 70分,math 80分,english 58分,请用一条sql语句查询出所有记录并按以下条件显示出来(并写出您的思路):

大于或等于80表示excellent,大于或等于60表示pass,小于60分表示fail。

显示格式: 以上面的chinese 70分,math 80分,english 58分

chinese math english

pass excellent fail



select (case when chinese >= 80 then "excellent" when chinese >= 60 then "pass" else "fail" end) as chinese,

  (case when math >= 80 then "excellent" when math >= 60 then "pass" else "fail" end) as math,

  (case when english >= 80 then "excellent" when english >= 60 then "pass" else "fail" end) as english

  from grade;



5.请用一个sql语句得出结果

从table1,table2中取出如table3所列格式数据,注意提供的数据及结果不准确,只是作为一个格式向大家请教。

如使用存储过程也可以。

table1

月份mon 部门dep 业绩yj

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

一月份 01 10

一月份 02 10

一月份 03 5

二月份 02 8

二月份 04 9

三月份 03 8



table2

部门dep 部门名称dname

--------------------------------
国内业务一部
国内业务二部
国内业务三部
国际业务部

table3 (result)

部门dep 一月份 二月份 三月份

--------------------------------------
null null
8 null
null 5 8
null null 9

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

select t1.dep,

sum(case when mon = 1 then yj else 0 end) as jun,

sum(case when mon = 2 then yj else 0 end) as feb,

sum(case when mon = 3 then yj else 0 end) as mar

from

t1 right join t2 on t1.dep = t2.dep

group by t1.dep;

题目一、
有两个表:

TableX有三个字段Code、 Name、 Age、 其中Code为主键;
TableY有三个字段Code、 Class、Score, 其中Code + Class 为主键。两表记录如下:

Code Name Age Code Class Score
张三 22 97001 数学 80
赵四 21 97002 计算机 59
张飞 20 97003 计算机 60
李五 22 97004 数学 55


1、请写出SQL,找出所有姓张的学生,并按年龄从小到大排列;


2、请写出SQL,取出计算机科考成绩不及格的学生;


3、通过等值联接,取出Name、Class、Score,请写出SQL即输出结果


4、通过外联接,取出每个学生的Name、Class、Score、请写SQL输出结果


5、请写SQL,在TableX 表中增加一条学生记录(学号:97005 姓名:赵六 年龄:20);


6、李五的年龄记录错了,应该是21,请写SQL,根据主键进行更新;


7、请写SQL,删除TableX中没有考试成绩的学生记录,请使用not in条件;


题目二、
有两个表定义如下:
create tableindividual (
firstname varchar2(20) not null
lastname vatchar2(20) not null
birthdate date
gender varchar2(1)
initial number(2)
farorite varchar2(6)
type varchar2(8)
);

在此表中建唯一索引 firstname + lastname

create table chile_detail(
firstname varchar2(20)
lastname varchar2(20)
cname varchar2(8)
coment varchar2(2)
type varchar2(8)
);


1、写一个简单的SQL语句实现:删除表individual中一条出生日期(brithdate)为 1990年10月2日 出生的人的记录

2、写一修改语句实现: 将表child_detail 中的type 为 “kkd” 的记录的Cname 值为“declear”,coment的值为“02”
TableX有三个字段Code、 Name、 Age、 其中Code为主键;
TableY有三个字段Code、 Class、Score, 其中Code + Class 为主键。两表记录如下:

Code Name Age Code Class Score
张三 22 97001 数学 80
赵四 21 97002 计算机 59
张飞 20 97003 计算机 60
李五 22 97004 数学 55


1、请写出SQL,找出所有姓张的学生,并按年龄从小到大排列;
select * from TableX where name like '张%' order by age

2、请写出SQL,取出计算机科考成绩不及格的学生;
select * from tableX where code in (select code from tableY WEHRE class='计算机' and score <60)

3、通过等值联接,取出Name、Class、Score,请写出SQL即输出结果
select a.name,b.class,b.score from tableX a,tableY b where a.code=b.code

4、通过外联接,取出每个学生的Name、Class、Score、请写SQL输出结果
select a.name,b.class,b.score from tableX full join tableY on a.code=b.code

5、请写SQL,在TableX 表中增加一条学生记录(学号:97005 姓名:赵六 年龄:20);
insert into tablex values('97005','赵六',20)


6、李五的年龄记录错了,应该是21,请写SQL,根据主键进行更新;
update tablex set age=21 where code='97004'

7、请写SQL,删除TableX中没有考试成绩的学生记录,请使用not in条件;
delete tablex where code not in (select code from tabley)

DELETE TABLEX WHERE CODE IN (
SELECT CODE FROM TABLEX WHERE CODE NOT IN(SELECT Y.CODE FROM TABLEY))
但看了其它人的写法,感觉自己写的不简洁,学习一下.

1、请写出SQL,找出所有姓张的学生,并按年龄从小到大排列;
SELECT * FROM TableX WHERE Name LIKE '张%' ORDER BY Age;


2、请写出SQL,取出计算机科考成绩不及格的学生;
SELECT * FROM TableX x, TableY y WHERE x.Code = y.Code AND Class = '计算机' AND Score < 60;


3、通过等值联接,取出Name、Class、Score,请写出SQL即输出结果
SELECT x.Name, y.Class, y.Score FROM TableX x, TableY y WHERE x.Code = y.Code


4、通过外联接,取出每个学生的Name、Class、Score、请写SQL输出结果
Left Out:SELECT x.Name, y.Class, y.Score FROM TableX x, TableY y WHERE x.Code = y.Code(+)
Right Out: SELECT x.Name, y.Class, y.Score FROM TableX x, TableY y WHERE x.Code(+) = y.Code
Full Out:Left join union all right join


5、请写SQL,在TableX 表中增加一条学生记录(学号:97005 姓名:赵六 年龄:20);
INSERT INTO TableX(Code, Name, Age) VALUES('97005','赵六',20);
COMMIT;

6、李五的年龄记录错了,应该是21,请写SQL,根据主键进行更新;
UPDATE TableX SET Age = 21 WHERE Code in (SELECT Code FROM TableX WHERE Name = '李五')


7、请写SQL,删除TableX中没有考试成绩的学生记录,请使用not in条件;
DELETE FROM TableX WHERE Code Not in (SELECT Code FROM TableY WHERE NVL(Score,0) = 0)


在此表中建唯一索引 firstname + lastname
CREATE UNIQUE INDEX NAME_UNINDEX ON individual(firstname,lastname)


1、写一个简单的SQL语句实现:删除表individual中一条出生日期(brithdate)为 1990年10月2日 出生的人的记录
DELETE FROM individual WHERE TO_CHAR(birthdate,'YYYY-MM-DD') = '1990-10-02';
COMMIT;

2、写一修改语句实现: 将表child_detail 中的type 为 “kkd” 的记录的Cname 值为“declear”,coment的值为“02”
UPDATE chile_detail SET Cname = 'declear', coment = '02'
WHERE type = 'kkd';
COMMIT;




http://www.51testing.com/?uid-165528-action-viewspace-itemid-99563

http://www.cnblogs.com/zemliu/archive/2012/10/09/2717629.html
http://bbs.csdn.NET/topics/270058476



[sql] view plain copy
一 单词解释(2分/个) 34
Data 数据Database 数据库RDBMS 关系数据库管理系统GRANT 授权
REVOKE取消权限DENY 拒绝权限DECLARE 定义变量PROCEDURE存储过程
Transaction事务TRIGGER触发器继续 continue唯一 unqiue
主键 primary key标识列 identity外键 foreign kdy 检查 check
约束 constraint
二 编写SQL语句(5分/题)50
1) 创建一张学生表,包含以下信息,学号,姓名,年龄,性别,家庭住址,联系电话
Create table stu (学号 int ,
姓名 varchar(8),
年龄 int,
性别 varchar(4),
家庭地址 varchar(50),
联系电话 int
);
2) 修改学生表的结构,添加一列信息,学历
Alter table stu add 学历 varchar(6);
3) 修改学生表的结构,删除一列信息,家庭住址
Alter table stu drop column 家庭地址
4) 向学生表添加如下信息:
学号 姓名年龄性别联系电话学历
1A22男123456小学
2B21男119中学
3C23男110高中
4D18女114大学
Insert into stu values(1,’A’,22,’男’,123456,’小学’)
Insert into stu values(2,’B’,21,’男’,119,’中学’)
Insert into stu values(3,’C’,23,’男’,110,’高中’)
Insert into stu values(4,’D’,18,’女’,114,’大学’)
修改学生表的数据,将电话号码以11开头的学员的学历改为“大专”
Update stu set 学历=’大专’ where 联系电话 like ‘11%’
6) 删除学生表的数据,姓名以C开头,性别为‘男’的记录删除
Delect from stu where 性别=’男’ and 姓名 like ‘c%’
7) 查询学生表的数据,将所有年龄小于22岁的,学历为“大专”的,学生的姓名和学号示出来
Select 姓名,学号 from stu where 年龄<22 and 学历=’大专’
8) 查询学生表的数据,查询所有信息,列出前25%的记录
Select top 25 percent * from stu
9) 查询出所有学生的姓名,性别,年龄降序排列
Select 姓名,性别 from stu order by 年龄 desc
10) 按照性别分组查询所有的平均年龄
Select avg(年龄) from stu group by 性别
三 填空(3分/题) 36
1) 索引分为__聚集索引___和__非聚集索引__在一张表上最多可以创建1个 聚集索引_索引。但是可以创建_249个非 聚集索引 索引。
2) 系统存储过程_sp-helptext__是用来显示规则,默认值,未加密的存储过程,用户定义函数,触发或视图的文本
3) 事务开始:begin Transction
提交事务:commit Transction
回滚事务:rollback Transction
四 问答题(5分/题)60
1) 数据库包含哪些那几种后缀名的文件必须,这些文件分别存放在什么的信息?
主要数据文件(.mdf) 包含数据用户收集的信息,还有数据库其他相关的信息,
日志数据文件(.ndf) 存放用户对数据库的增删改查的信息,用于备份恢复使用
2) TRUNCATE TABLE 命令是什么含义?和Delete from 表名有什么区?
TRUNCATE TABLE: 提供了一种删除表中所有记录的快速方法
Delete from 表名:可以删除表的一个或多条记录
3) 说出以下聚合数的含义:avg ,sum ,max ,min , count ,count(*)
AVG:求平均值
SUM:求和
MAX:求最大值
MIN:求最小值
COUNT(*):返回所有行数
COUNT返回满足指定条件的记录值
inner join 是什么意思?作用是什么?写出基本语法结构
INNER JOIN 内联接,用于返回两个表中要查询的列数据通信
Select * from 表名1 inner join 表名2 on 条件表达式
5) 左向外联接,右向外联接,全联接的关健字如何写?
Left outer join 左向外联接
Right outer join 右向外联接
Full outer join 全联接
6) 子查询分为几类,说明相互之间的别
了查询分三种基本子查询: 1.使用in 查询返回一列或更多值
2.比较运算符,返回单个值勤做为外查询的参数
3.用exists 查询时相当于进行一次数据测试
7) 实现实体完整性,实现域完整性,实现 完整性(引用完整性),实现自定义完整性分别使用什么手段?
实现实体完整性: 主键约束 唯一约束 标识列
实现域完整性: 默认值约束 检查约束 非空属性
引和完整性: 外键引用
8) 视图可以更新吗?会影响到实际表吗?
视图是可以更新的,视图只是基于基本表上的虚拟表,对视图的更新会直接影响到实际表
9) 谈谈这样几个角色, dbo , Sysadmin public
Dbo : 是数据库的拥有者,对数据库拥有所有操作的权限
Sysadmin : 可以对SQL SERVER执行任何活动
Public : 自动创建的,能捕获数据库中用户的所有默认权限
10) 何为动态游标?何为静态游标?
动态游标与静态游标相对,反映结果集中所做的所有更改,
静态游标的结果集在游标打开时,建立在tempdb中,总按照游标打开时的原样显示
11) 什么是存储过程?为什么存储过程要比单纯的Sql 语句执行起来要快?
存储过程:是一组预先编译好的T-SQL代码
在创建存储过程时经过了语法和性能优化,执行不必重复的步骤,使用存储过程可提高运行效率
12)什么是Inserted 表 ?什么是Deleted 表?
Inserted表用于存储inserted和update语句影响的副本
Deleted 表用于存储delect 和 update语句影响的行的副本
(1)表名:购物信息
购物人 商品名称 数量
A 甲 2
B 乙 4
C 丙 1
A 丁 2
B 丙 5
……

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

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

(2)表名:成绩表
姓名 课程 分数
张三 语文 81
张三 数学 75
李四 语文 56
李四 数学 90
王五 语文 81
王五 数学 100
王五 英语 49


给出成绩全部合格的学生信息(包含姓名、课程、分数),注:分数在60以上评为合格

答:select * from 成绩表 where 姓名 not in (select distinct 姓名 from 成绩表 where 分数 < 60)
或者:
select * from 成绩表 where 姓名 in (select 姓名 from 成绩表 group by 姓名 having min(分数) >=60)


(3)表名:商品表
名称 产地 进价
苹果 烟台 2.5
苹果 云南 1.9
苹果 四川 3
西瓜 江西 1.5
西瓜 北京 2.4
……

给出平均进价在2元以下的商品名称

答:select 名称 from 商品表 group by 名称 having avg(进价) < 2

(4)表名:高考信息表
准考证号 科目 成绩
语文 119
数学 108
物理 142
化学 136
物理 127
数学 149
英语 110
语文 105
英语 98
化学 129
……

给出高考总分在600以上的学生准考证号

答:select 准考证号 from 高考信息表 group by 准考证号 having sum(成绩) > 600

(5)表名:高考信息表
准考证号 数学 语文 英语 物理 化学
119 98 127 136
105 110 142 129
……

给出高考总分在600以上的学生准考证号

答:select 准考证号 from 高考信息表 where (数学+语文+英语+物理+化学) > 600



(四部分)
(一)表名:club

id gender age
M 19
F 30
F 27
F 16
M 32
……

查询出该俱乐部里男性会员和女性会员的总数

答:select gender,count(id) from club group by gender

(二)表名:team
ID(number型) Name(varchar2型)
a
b
b
a
c
c
要求:执行一个删除语句,当Name列上有相同时,只保留ID这列上值小的
例如:删除后的结果应如下:
ID(number型) Name(varchar2型)
a
b
c
请写出SQL语句。

delete from team where id not in (select min(id) from team group by name)

(三)表名:student

name course score
张青 语文 72
王华 数学 72
张华 英语 81
张青 物理 67
李立 化学 98
张燕 物理 70
张青 化学 76

查询出“张”姓学生中平均成绩大于75分的学生信息

答:select * from student where name in (select name from student
where name like '张%' group by name having avg(score) > 75)



1.一道SQL语句面试题,关于group by表内容:

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;



2.表中有A B C三列,用SQL语句实现:当A列大于B列时选择A列否则选择B列,当B列大于C列时选择B列否则选择C列

select (case when a > b then a else b end), (case when b > c then b else c end) from table;

3.请取出tb_send表中日期(SendTime字段)为当天的所有记录? (SendTime字段为datetime型,包含日期与时间)

select * from tb where datediff(dd,SendTime,getdate())=0

4.有一张表,里面有3个字段:chinese,math,english。其中有一条记录chinese 70分,math 80分,english 58分,请用一条sql语句查询出所有记录并按以下条件显示出来(并写出您的思路):

大于或等于80表示excellent,大于或等于60表示pass,小于60分表示fail。

显示格式: 以上面的chinese 70分,math 80分,english 58分

chinese math english

pass excellent fail



select (case when chinese >= 80 then "excellent" when chinese >= 60 then "pass" else "fail" end) as chinese,

  (case when math >= 80 then "excellent" when math >= 60 then "pass" else "fail" end) as math,

  (case when english >= 80 then "excellent" when english >= 60 then "pass" else "fail" end) as english

  from grade;



5.请用一个sql语句得出结果

从table1,table2中取出如table3所列格式数据,注意提供的数据及结果不准确,只是作为一个格式向大家请教。

如使用存储过程也可以。

table1

月份mon 部门dep 业绩yj

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

一月份 01 10

一月份 02 10

一月份 03 5

二月份 02 8

二月份 04 9

三月份 03 8



table2

部门dep 部门名称dname

--------------------------------
国内业务一部
国内业务二部
国内业务三部
国际业务部

table3 (result)

部门dep 一月份 二月份 三月份

--------------------------------------
null null
8 null
null 5 8
null null 9

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

select t1.dep,

sum(case when mon = 1 then yj else 0 end) as jun,

sum(case when mon = 2 then yj else 0 end) as feb,

sum(case when mon = 3 then yj else 0 end) as mar

from

t1 right join t2 on t1.dep = t2.dep

group by t1.dep;

题目一、
有两个表:

TableX有三个字段Code、 Name、 Age、 其中Code为主键;
TableY有三个字段Code、 Class、Score, 其中Code + Class 为主键。两表记录如下:

Code Name Age Code Class Score
张三 22 97001 数学 80
赵四 21 97002 计算机 59
张飞 20 97003 计算机 60
李五 22 97004 数学 55


1、请写出SQL,找出所有姓张的学生,并按年龄从小到大排列;


2、请写出SQL,取出计算机科考成绩不及格的学生;


3、通过等值联接,取出Name、Class、Score,请写出SQL即输出结果


4、通过外联接,取出每个学生的Name、Class、Score、请写SQL输出结果


5、请写SQL,在TableX 表中增加一条学生记录(学号:97005 姓名:赵六 年龄:20);


6、李五的年龄记录错了,应该是21,请写SQL,根据主键进行更新;


7、请写SQL,删除TableX中没有考试成绩的学生记录,请使用not in条件;


题目二、
有两个表定义如下:
create tableindividual (
firstname varchar2(20) not null
lastname vatchar2(20) not null
birthdate date
gender varchar2(1)
initial number(2)
farorite varchar2(6)
type varchar2(8)
);

在此表中建唯一索引 firstname + lastname

create table chile_detail(
firstname varchar2(20)
lastname varchar2(20)
cname varchar2(8)
coment varchar2(2)
type varchar2(8)
);


1、写一个简单的SQL语句实现:删除表individual中一条出生日期(brithdate)为 1990年10月2日 出生的人的记录

2、写一修改语句实现: 将表child_detail 中的type 为 “kkd” 的记录的Cname 值为“declear”,coment的值为“02”
TableX有三个字段Code、 Name、 Age、 其中Code为主键;
TableY有三个字段Code、 Class、Score, 其中Code + Class 为主键。两表记录如下:

Code Name Age Code Class Score
张三 22 97001 数学 80
赵四 21 97002 计算机 59
张飞 20 97003 计算机 60
李五 22 97004 数学 55


1、请写出SQL,找出所有姓张的学生,并按年龄从小到大排列;
select * from TableX where name like '张%' order by age

2、请写出SQL,取出计算机科考成绩不及格的学生;
select * from tableX where code in (select code from tableY WEHRE class='计算机' and score <60)

3、通过等值联接,取出Name、Class、Score,请写出SQL即输出结果
select a.name,b.class,b.score from tableX a,tableY b where a.code=b.code

4、通过外联接,取出每个学生的Name、Class、Score、请写SQL输出结果
select a.name,b.class,b.score from tableX full join tableY on a.code=b.code

5、请写SQL,在TableX 表中增加一条学生记录(学号:97005 姓名:赵六 年龄:20);
insert into tablex values('97005','赵六',20)


6、李五的年龄记录错了,应该是21,请写SQL,根据主键进行更新;
update tablex set age=21 where code='97004'

7、请写SQL,删除TableX中没有考试成绩的学生记录,请使用not in条件;
delete tablex where code not in (select code from tabley)

DELETE TABLEX WHERE CODE IN (
SELECT CODE FROM TABLEX WHERE CODE NOT IN(SELECT Y.CODE FROM TABLEY))
但看了其它人的写法,感觉自己写的不简洁,学习一下.

1、请写出SQL,找出所有姓张的学生,并按年龄从小到大排列;
SELECT * FROM TableX WHERE Name LIKE '张%' ORDER BY Age;


2、请写出SQL,取出计算机科考成绩不及格的学生;
SELECT * FROM TableX x, TableY y WHERE x.Code = y.Code AND Class = '计算机' AND Score < 60;


3、通过等值联接,取出Name、Class、Score,请写出SQL即输出结果
SELECT x.Name, y.Class, y.Score FROM TableX x, TableY y WHERE x.Code = y.Code


4、通过外联接,取出每个学生的Name、Class、Score、请写SQL输出结果
Left Out:SELECT x.Name, y.Class, y.Score FROM TableX x, TableY y WHERE x.Code = y.Code(+)
Right Out: SELECT x.Name, y.Class, y.Score FROM TableX x, TableY y WHERE x.Code(+) = y.Code
Full Out:Left join union all right join


5、请写SQL,在TableX 表中增加一条学生记录(学号:97005 姓名:赵六 年龄:20);
INSERT INTO TableX(Code, Name, Age) VALUES('97005','赵六',20);
COMMIT;

6、李五的年龄记录错了,应该是21,请写SQL,根据主键进行更新;
UPDATE TableX SET Age = 21 WHERE Code in (SELECT Code FROM TableX WHERE Name = '李五')


7、请写SQL,删除TableX中没有考试成绩的学生记录,请使用not in条件;
DELETE FROM TableX WHERE Code Not in (SELECT Code FROM TableY WHERE NVL(Score,0) = 0)


在此表中建唯一索引 firstname + lastname
CREATE UNIQUE INDEX NAME_UNINDEX ON individual(firstname,lastname)


1、写一个简单的SQL语句实现:删除表individual中一条出生日期(brithdate)为 1990年10月2日 出生的人的记录
DELETE FROM individual WHERE TO_CHAR(birthdate,'YYYY-MM-DD') = '1990-10-02';
COMMIT;

2、写一修改语句实现: 将表child_detail 中的type 为 “kkd” 的记录的Cname 值为“declear”,coment的值为“02”
UPDATE chile_detail SET Cname = 'declear', coment = '02'
WHERE type = 'kkd';
COMMIT;




http://www.51testing.com/?uid-165528-action-viewspace-itemid-99563

http://www.cnblogs.com/zemliu/archive/2012/10/09/2717629.html
http://bbs.csdn.NET/topics/270058476



[sql] view plain copy
一 单词解释(2分/个) 34
Data 数据Database 数据库RDBMS 关系数据库管理系统GRANT 授权
REVOKE取消权限DENY 拒绝权限DECLARE 定义变量PROCEDURE存储过程
Transaction事务TRIGGER触发器继续 continue唯一 unqiue
主键 primary key标识列 identity外键 foreign kdy 检查 check
约束 constraint
二 编写SQL语句(5分/题)50
1) 创建一张学生表,包含以下信息,学号,姓名,年龄,性别,家庭住址,联系电话
Create table stu (学号 int ,
姓名 varchar(8),
年龄 int,
性别 varchar(4),
家庭地址 varchar(50),
联系电话 int
);
2) 修改学生表的结构,添加一列信息,学历
Alter table stu add 学历 varchar(6);
3) 修改学生表的结构,删除一列信息,家庭住址
Alter table stu drop column 家庭地址
4) 向学生表添加如下信息:
学号 姓名年龄性别联系电话学历
1A22男123456小学
2B21男119中学
3C23男110高中
4D18女114大学
Insert into stu values(1,’A’,22,’男’,123456,’小学’)
Insert into stu values(2,’B’,21,’男’,119,’中学’)
Insert into stu values(3,’C’,23,’男’,110,’高中’)
Insert into stu values(4,’D’,18,’女’,114,’大学’)
修改学生表的数据,将电话号码以11开头的学员的学历改为“大专”
Update stu set 学历=’大专’ where 联系电话 like ‘11%’
6) 删除学生表的数据,姓名以C开头,性别为‘男’的记录删除
Delect from stu where 性别=’男’ and 姓名 like ‘c%’
7) 查询学生表的数据,将所有年龄小于22岁的,学历为“大专”的,学生的姓名和学号示出来
Select 姓名,学号 from stu where 年龄<22 and 学历=’大专’
8) 查询学生表的数据,查询所有信息,列出前25%的记录
Select top 25 percent * from stu
9) 查询出所有学生的姓名,性别,年龄降序排列
Select 姓名,性别 from stu order by 年龄 desc
10) 按照性别分组查询所有的平均年龄
Select avg(年龄) from stu group by 性别
三 填空(3分/题) 36
1) 索引分为__聚集索引___和__非聚集索引__在一张表上最多可以创建1个 聚集索引_索引。但是可以创建_249个非 聚集索引 索引。
2) 系统存储过程_sp-helptext__是用来显示规则,默认值,未加密的存储过程,用户定义函数,触发或视图的文本
3) 事务开始:begin Transction
提交事务:commit Transction
回滚事务:rollback Transction
四 问答题(5分/题)60
1) 数据库包含哪些那几种后缀名的文件必须,这些文件分别存放在什么的信息?
主要数据文件(.mdf) 包含数据用户收集的信息,还有数据库其他相关的信息,
日志数据文件(.ndf) 存放用户对数据库的增删改查的信息,用于备份恢复使用
2) TRUNCATE TABLE 命令是什么含义?和Delete from 表名有什么区?
TRUNCATE TABLE: 提供了一种删除表中所有记录的快速方法
Delete from 表名:可以删除表的一个或多条记录
3) 说出以下聚合数的含义:avg ,sum ,max ,min , count ,count(*)
AVG:求平均值
SUM:求和
MAX:求最大值
MIN:求最小值
COUNT(*):返回所有行数
COUNT返回满足指定条件的记录值
inner join 是什么意思?作用是什么?写出基本语法结构
INNER JOIN 内联接,用于返回两个表中要查询的列数据通信
Select * from 表名1 inner join 表名2 on 条件表达式
5) 左向外联接,右向外联接,全联接的关健字如何写?
Left outer join 左向外联接
Right outer join 右向外联接
Full outer join 全联接
6) 子查询分为几类,说明相互之间的别
了查询分三种基本子查询: 1.使用in 查询返回一列或更多值
2.比较运算符,返回单个值勤做为外查询的参数
3.用exists 查询时相当于进行一次数据测试
7) 实现实体完整性,实现域完整性,实现 完整性(引用完整性),实现自定义完整性分别使用什么手段?
实现实体完整性: 主键约束 唯一约束 标识列
实现域完整性: 默认值约束 检查约束 非空属性
引和完整性: 外键引用
8) 视图可以更新吗?会影响到实际表吗?
视图是可以更新的,视图只是基于基本表上的虚拟表,对视图的更新会直接影响到实际表
9) 谈谈这样几个角色, dbo , Sysadmin public
Dbo : 是数据库的拥有者,对数据库拥有所有操作的权限
Sysadmin : 可以对SQL SERVER执行任何活动
Public : 自动创建的,能捕获数据库中用户的所有默认权限
10) 何为动态游标?何为静态游标?
动态游标与静态游标相对,反映结果集中所做的所有更改,
静态游标的结果集在游标打开时,建立在tempdb中,总按照游标打开时的原样显示
11) 什么是存储过程?为什么存储过程要比单纯的Sql 语句执行起来要快?
存储过程:是一组预先编译好的T-SQL代码
在创建存储过程时经过了语法和性能优化,执行不必重复的步骤,使用存储过程可提高运行效率
12)什么是Inserted 表 ?什么是Deleted 表?
Inserted表用于存储inserted和update语句影响的副本
Deleted 表用于存储delect 和 update语句影响的行的副本


(1)表名:购物信息
购物人 商品名称 数量
A 甲 2
B 乙 4
C 丙 1
A 丁 2
B 丙 5
……

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

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

(2)表名:成绩表
姓名 课程 分数
张三 语文 81
张三 数学 75
李四 语文 56
李四 数学 90
王五 语文 81
王五 数学 100
王五 英语 49


给出成绩全部合格的学生信息(包含姓名、课程、分数),注:分数在60以上评为合格

答:select * from 成绩表 where 姓名 not in (select distinct 姓名 from 成绩表 where 分数 < 60)
或者:
select * from 成绩表 where 姓名 in (select 姓名 from 成绩表 group by 姓名 having min(分数) >=60)


(3)表名:商品表
名称 产地 进价
苹果 烟台 2.5
苹果 云南 1.9
苹果 四川 3
西瓜 江西 1.5
西瓜 北京 2.4
……

给出平均进价在2元以下的商品名称

答:select 名称 from 商品表 group by 名称 having avg(进价) < 2

(4)表名:高考信息表
准考证号 科目 成绩
语文 119
数学 108
物理 142
化学 136
物理 127
数学 149
英语 110
语文 105
英语 98
化学 129
……

给出高考总分在600以上的学生准考证号

答:select 准考证号 from 高考信息表 group by 准考证号 having sum(成绩) > 600

(5)表名:高考信息表
准考证号 数学 语文 英语 物理 化学
119 98 127 136
105 110 142 129
……

给出高考总分在600以上的学生准考证号

答:select 准考证号 from 高考信息表 where (数学+语文+英语+物理+化学) > 600



(四部分)
(一)表名:club

id gender age
M 19
F 30
F 27
F 16
M 32
……

查询出该俱乐部里男性会员和女性会员的总数

答:select gender,count(id) from club group by gender

(二)表名:team
ID(number型) Name(varchar2型)
a
b
b
a
c
c
要求:执行一个删除语句,当Name列上有相同时,只保留ID这列上值小的
例如:删除后的结果应如下:
ID(number型) Name(varchar2型)
a
b
c
请写出SQL语句。

delete from team where id not in (select min(id) from team group by name)

(三)表名:student

name course score
张青 语文 72
王华 数学 72
张华 英语 81
张青 物理 67
李立 化学 98
张燕 物理 70
张青 化学 76

查询出“张”姓学生中平均成绩大于75分的学生信息

答:select * from student where name in (select name from student
where name like '张%' group by name having avg(score) > 75)



1.一道SQL语句面试题,关于group by表内容:

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;



2.表中有A B C三列,用SQL语句实现:当A列大于B列时选择A列否则选择B列,当B列大于C列时选择B列否则选择C列

select (case when a > b then a else b end), (case when b > c then b else c end) from table;

3.请取出tb_send表中日期(SendTime字段)为当天的所有记录? (SendTime字段为datetime型,包含日期与时间)

select * from tb where datediff(dd,SendTime,getdate())=0

4.有一张表,里面有3个字段:chinese,math,english。其中有一条记录chinese 70分,math 80分,english 58分,请用一条sql语句查询出所有记录并按以下条件显示出来(并写出您的思路):

大于或等于80表示excellent,大于或等于60表示pass,小于60分表示fail。

显示格式: 以上面的chinese 70分,math 80分,english 58分

chinese math english

pass excellent fail



select (case when chinese >= 80 then "excellent" when chinese >= 60 then "pass" else "fail" end) as chinese,

  (case when math >= 80 then "excellent" when math >= 60 then "pass" else "fail" end) as math,

  (case when english >= 80 then "excellent" when english >= 60 then "pass" else "fail" end) as english

  from grade;



5.请用一个sql语句得出结果

从table1,table2中取出如table3所列格式数据,注意提供的数据及结果不准确,只是作为一个格式向大家请教。

如使用存储过程也可以。

table1

月份mon 部门dep 业绩yj

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

一月份 01 10

一月份 02 10

一月份 03 5

二月份 02 8

二月份 04 9

三月份 03 8



table2

部门dep 部门名称dname

--------------------------------
国内业务一部
国内业务二部
国内业务三部
国际业务部

table3 (result)

部门dep 一月份 二月份 三月份

--------------------------------------
null null
8 null
null 5 8
null null 9

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

select t1.dep,

sum(case when mon = 1 then yj else 0 end) as jun,

sum(case when mon = 2 then yj else 0 end) as feb,

sum(case when mon = 3 then yj else 0 end) as mar

from

t1 right join t2 on t1.dep = t2.dep

group by t1.dep;

题目一、
有两个表:

TableX有三个字段Code、 Name、 Age、 其中Code为主键;
TableY有三个字段Code、 Class、Score, 其中Code + Class 为主键。两表记录如下:

Code Name Age Code Class Score
张三 22 97001 数学 80
赵四 21 97002 计算机 59
张飞 20 97003 计算机 60
李五 22 97004 数学 55


1、请写出SQL,找出所有姓张的学生,并按年龄从小到大排列;


2、请写出SQL,取出计算机科考成绩不及格的学生;


3、通过等值联接,取出Name、Class、Score,请写出SQL即输出结果


4、通过外联接,取出每个学生的Name、Class、Score、请写SQL输出结果


5、请写SQL,在TableX 表中增加一条学生记录(学号:97005 姓名:赵六 年龄:20);


6、李五的年龄记录错了,应该是21,请写SQL,根据主键进行更新;


7、请写SQL,删除TableX中没有考试成绩的学生记录,请使用not in条件;


题目二、
有两个表定义如下:
create tableindividual (
firstname varchar2(20) not null
lastname vatchar2(20) not null
birthdate date
gender varchar2(1)
initial number(2)
farorite varchar2(6)
type varchar2(8)
);

在此表中建唯一索引 firstname + lastname

create table chile_detail(
firstname varchar2(20)
lastname varchar2(20)
cname varchar2(8)
coment varchar2(2)
type varchar2(8)
);


1、写一个简单的SQL语句实现:删除表individual中一条出生日期(brithdate)为 1990年10月2日 出生的人的记录

2、写一修改语句实现: 将表child_detail 中的type 为 “kkd” 的记录的Cname 值为“declear”,coment的值为“02”
TableX有三个字段Code、 Name、 Age、 其中Code为主键;
TableY有三个字段Code、 Class、Score, 其中Code + Class 为主键。两表记录如下:

Code Name Age Code Class Score
张三 22 97001 数学 80
赵四 21 97002 计算机 59
张飞 20 97003 计算机 60
李五 22 97004 数学 55


1、请写出SQL,找出所有姓张的学生,并按年龄从小到大排列;
select * from TableX where name like '张%' order by age

2、请写出SQL,取出计算机科考成绩不及格的学生;
select * from tableX where code in (select code from tableY WEHRE class='计算机' and score <60)

3、通过等值联接,取出Name、Class、Score,请写出SQL即输出结果
select a.name,b.class,b.score from tableX a,tableY b where a.code=b.code

4、通过外联接,取出每个学生的Name、Class、Score、请写SQL输出结果
select a.name,b.class,b.score from tableX full join tableY on a.code=b.code

5、请写SQL,在TableX 表中增加一条学生记录(学号:97005 姓名:赵六 年龄:20);
insert into tablex values('97005','赵六',20)


6、李五的年龄记录错了,应该是21,请写SQL,根据主键进行更新;
update tablex set age=21 where code='97004'

7、请写SQL,删除TableX中没有考试成绩的学生记录,请使用not in条件;
delete tablex where code not in (select code from tabley)

DELETE TABLEX WHERE CODE IN (
SELECT CODE FROM TABLEX WHERE CODE NOT IN(SELECT Y.CODE FROM TABLEY))
但看了其它人的写法,感觉自己写的不简洁,学习一下.

1、请写出SQL,找出所有姓张的学生,并按年龄从小到大排列;
SELECT * FROM TableX WHERE Name LIKE '张%' ORDER BY Age;


2、请写出SQL,取出计算机科考成绩不及格的学生;
SELECT * FROM TableX x, TableY y WHERE x.Code = y.Code AND Class = '计算机' AND Score < 60;


3、通过等值联接,取出Name、Class、Score,请写出SQL即输出结果
SELECT x.Name, y.Class, y.Score FROM TableX x, TableY y WHERE x.Code = y.Code


4、通过外联接,取出每个学生的Name、Class、Score、请写SQL输出结果
Left Out:SELECT x.Name, y.Class, y.Score FROM TableX x, TableY y WHERE x.Code = y.Code(+)
Right Out: SELECT x.Name, y.Class, y.Score FROM TableX x, TableY y WHERE x.Code(+) = y.Code
Full Out:Left join union all right join


5、请写SQL,在TableX 表中增加一条学生记录(学号:97005 姓名:赵六 年龄:20);
INSERT INTO TableX(Code, Name, Age) VALUES('97005','赵六',20);
COMMIT;

6、李五的年龄记录错了,应该是21,请写SQL,根据主键进行更新;
UPDATE TableX SET Age = 21 WHERE Code in (SELECT Code FROM TableX WHERE Name = '李五')


7、请写SQL,删除TableX中没有考试成绩的学生记录,请使用not in条件;
DELETE FROM TableX WHERE Code Not in (SELECT Code FROM TableY WHERE NVL(Score,0) = 0)


在此表中建唯一索引 firstname + lastname
CREATE UNIQUE INDEX NAME_UNINDEX ON individual(firstname,lastname)


1、写一个简单的SQL语句实现:删除表individual中一条出生日期(brithdate)为 1990年10月2日 出生的人的记录
DELETE FROM individual WHERE TO_CHAR(birthdate,'YYYY-MM-DD') = '1990-10-02';
COMMIT;

2、写一修改语句实现: 将表child_detail 中的type 为 “kkd” 的记录的Cname 值为“declear”,coment的值为“02”
UPDATE chile_detail SET Cname = 'declear', coment = '02'
WHERE type = 'kkd';
COMMIT;




http://www.51testing.com/?uid-165528-action-viewspace-itemid-99563

http://www.cnblogs.com/zemliu/archive/2012/10/09/2717629.html
http://bbs.csdn.NET/topics/270058476



[sql] view plain copy
一 单词解释(2分/个) 34
Data 数据Database 数据库RDBMS 关系数据库管理系统GRANT 授权
REVOKE取消权限DENY 拒绝权限DECLARE 定义变量PROCEDURE存储过程
Transaction事务TRIGGER触发器继续 continue唯一 unqiue
主键 primary key标识列 identity外键 foreign kdy 检查 check
约束 constraint
二 编写SQL语句(5分/题)50
1) 创建一张学生表,包含以下信息,学号,姓名,年龄,性别,家庭住址,联系电话
Create table stu (学号 int ,
姓名 varchar(8),
年龄 int,
性别 varchar(4),
家庭地址 varchar(50),
联系电话 int
);
2) 修改学生表的结构,添加一列信息,学历
Alter table stu add 学历 varchar(6);
3) 修改学生表的结构,删除一列信息,家庭住址
Alter table stu drop column 家庭地址
4) 向学生表添加如下信息:
学号 姓名年龄性别联系电话学历
1A22男123456小学
2B21男119中学
3C23男110高中
4D18女114大学
Insert into stu values(1,’A’,22,’男’,123456,’小学’)
Insert into stu values(2,’B’,21,’男’,119,’中学’)
Insert into stu values(3,’C’,23,’男’,110,’高中’)
Insert into stu values(4,’D’,18,’女’,114,’大学’)
修改学生表的数据,将电话号码以11开头的学员的学历改为“大专”
Update stu set 学历=’大专’ where 联系电话 like ‘11%’
6) 删除学生表的数据,姓名以C开头,性别为‘男’的记录删除
Delect from stu where 性别=’男’ and 姓名 like ‘c%’
7) 查询学生表的数据,将所有年龄小于22岁的,学历为“大专”的,学生的姓名和学号示出来
Select 姓名,学号 from stu where 年龄<22 and 学历=’大专’
8) 查询学生表的数据,查询所有信息,列出前25%的记录
Select top 25 percent * from stu
9) 查询出所有学生的姓名,性别,年龄降序排列
Select 姓名,性别 from stu order by 年龄 desc
10) 按照性别分组查询所有的平均年龄
Select avg(年龄) from stu group by 性别
三 填空(3分/题) 36
1) 索引分为__聚集索引___和__非聚集索引__在一张表上最多可以创建1个 聚集索引_索引。但是可以创建_249个非 聚集索引 索引。
2) 系统存储过程_sp-helptext__是用来显示规则,默认值,未加密的存储过程,用户定义函数,触发或视图的文本
3) 事务开始:begin Transction
提交事务:commit Transction
回滚事务:rollback Transction
四 问答题(5分/题)60
1) 数据库包含哪些那几种后缀名的文件必须,这些文件分别存放在什么的信息?
主要数据文件(.mdf) 包含数据用户收集的信息,还有数据库其他相关的信息,
日志数据文件(.ndf) 存放用户对数据库的增删改查的信息,用于备份恢复使用
2) TRUNCATE TABLE 命令是什么含义?和Delete from 表名有什么区?
TRUNCATE TABLE: 提供了一种删除表中所有记录的快速方法
Delete from 表名:可以删除表的一个或多条记录
3) 说出以下聚合数的含义:avg ,sum ,max ,min , count ,count(*)
AVG:求平均值
SUM:求和
MAX:求最大值
MIN:求最小值
COUNT(*):返回所有行数
COUNT返回满足指定条件的记录值
inner join 是什么意思?作用是什么?写出基本语法结构
INNER JOIN 内联接,用于返回两个表中要查询的列数据通信
Select * from 表名1 inner join 表名2 on 条件表达式
5) 左向外联接,右向外联接,全联接的关健字如何写?
Left outer join 左向外联接
Right outer join 右向外联接
Full outer join 全联接
6) 子查询分为几类,说明相互之间的别
了查询分三种基本子查询: 1.使用in 查询返回一列或更多值
2.比较运算符,返回单个值勤做为外查询的参数
3.用exists 查询时相当于进行一次数据测试
7) 实现实体完整性,实现域完整性,实现 完整性(引用完整性),实现自定义完整性分别使用什么手段?
实现实体完整性: 主键约束 唯一约束 标识列
实现域完整性: 默认值约束 检查约束 非空属性
引和完整性: 外键引用
8) 视图可以更新吗?会影响到实际表吗?
视图是可以更新的,视图只是基于基本表上的虚拟表,对视图的更新会直接影响到实际表
9) 谈谈这样几个角色, dbo , Sysadmin public
Dbo : 是数据库的拥有者,对数据库拥有所有操作的权限
Sysadmin : 可以对SQL SERVER执行任何活动
Public : 自动创建的,能捕获数据库中用户的所有默认权限
10) 何为动态游标?何为静态游标?
动态游标与静态游标相对,反映结果集中所做的所有更改,
静态游标的结果集在游标打开时,建立在tempdb中,总按照游标打开时的原样显示
11) 什么是存储过程?为什么存储过程要比单纯的Sql 语句执行起来要快?
存储过程:是一组预先编译好的T-SQL代码
在创建存储过程时经过了语法和性能优化,执行不必重复的步骤,使用存储过程可提高运行效率
12)什么是Inserted 表 ?什么是Deleted 表?
Inserted表用于存储inserted和update语句影响的副本
Deleted 表用于存储delect 和 update语句影响的行的副本

posted @ 2019-06-26 17:23  枫叶痕雪  阅读(783)  评论(0编辑  收藏  举报