sql语句相关测试

sql语句相关测试

篇一：SQL语句测试

1、（10分）要求:

选择受理时间在2008-5-1 到 2008-6-1之间的所有申请人姓氏为“刘”的数据，并把“新受理编号”列表示成当前机器时间的年月和原受理编号年月后的数值组合

格式如下：

受理编号，新受理编号，受理时间， 申请人

200801112 201008112 2008-01-13刘XX

需要的表

I_Optinst 业务实例表

REGDATE（受理日期）

Regnum(受理编号)

Proposer（申请人）

解答：select Regnum as 受理编号, as 新受理编号,REGDATE as 受理时间,Proposer as申请人 from Optinst 2、（15分）要求：

前提：只统计业务小类“存量房买卖”

①按照月份分12月列出2008年每个月份的月份对应月份的交易总面积

②按照月份分12月列出2008年每个月份的月份对应月份的交易均价（申报价格/建筑面积）

格式

年度月份 交易总面积 年度月份交易均价（元/平方米）

2008-01 23232 2008-01 2323

2008-02 23232008-02 232

2008-03 232323 2008-03 7656

2008-04 232323 2008-03 565

2008-05 232323 2008-03 5656

2008-06 232323 2008-03 565

2008-07 232323 2008-03 67

2008-08 232323 2008-03 676

2008-09 232323 2008-03 6767

2008-10 232323 2008-03 8686

2008-11 232323 2008-03 867

2008-12 232323 2008-03 454

需要的表：

Fc_room 房间表

BAREA（建筑面积）

I_Optinst业务实例表

regdate（受理时间）

fc_owner 产权表

COSTVAL(申报价格)

EVLVAL（评估价格）

fc_owoom 房间明细表

1.select regdate as年度月份,BAREA as 交易总面积 from Fc_room,Optinst where

3、（20分）要求：

①：按照时间统计收费明细 统计格式如下

受理编号缴费人收费日期 收费名称收费金额核费人收费人

②：按照时间汇总（2008年度）统计收费项目分类 统计格式如下

收费名称 总金额

需要的表:

I_OptInst（业务实例表）

Regnum 受理编号

Proposer 申请人

I_Charge（收费实例表）

HEFEIMAN 核费人

PAYEE 收款人

CDATE 收费日期

I_ChrDtl（收费实例明细表

CNAME 收费名称

MONEY 收费金额

4、（15分）要求：用途是住宅并且建筑面积<=140>140 定义为 “非普通住宅”

用途是商业并且建筑面积>140 定义为 “商业A级”

其他情况定义为“非住宅”

根据用途和面积列表出所有数据

格式

受理编号， 用途

200406000386 普通住宅

200406004631 非普通住宅

200406004633 普通住宅

200406004638 普通住宅

200406004641 非住宅

200501000004 普通住宅

200406004568 非住宅

200406005677 商业A级

表：

fc_room

barea 建筑面积

BUse 用途

i_optinst

regnum 受理编号

5、（30分）工作量统计

① 选择出以下格式的数据；并创建视图名称为view_AAAA

业务小类业务实例 交易价格建筑面积 登记时间

1 存量房买卖 14100 19400.0029.98 2005-11-10 11:32:50 2 新建商品房 15041 229530.00 124.07 2005-11-21 08:59:36 3 新建商品房 15043 177363.00 101.35 2005-11-21 09:15:59 4 新建商品房 13046 71130.0023.71 2005-11-02 10:15:37 5 新建商品房 11280 148563.00 87.39 2005-10-11 09:50:48 6 新建商品房 11353 267369.00 116.04 2005-10-11 15:34:53 7 房改售房 2689 35.22 2004-06-17 08:43:00 8 产权人名称变更 11701 724.18 2005-10-17 10:05:20 9 新建商品房 7206 158257.00 88.69 2005-09-16 14:50:57 10 存量房买卖 (转 载于:wWw.cnboThwiN.cOM 博 威范文 网: sql语句测试 )10100 103.07 2005-08-31 20:27:06 11 存量房买卖 12980 51500.0046.66 2005-11-01 14:41:32 12 新建商品房 13000 136782.00 80.46 2005-11-01 15:37:05 13 新建商品房 16946 300844.00 146.33 2005-12-15 14:15:07 14 存量房买卖 10091 509.18 2005-08-31 19:19:25 ② 使用视图 view_AAAA 当做表 选择出如下样式数据

业务小类 件数合计金额 合计面积

1 用途变更 1151.3

2 转移登记 184.03

3 新建商品房 31 263243643197.34

4 房改售房 8252.43

5 产权人名称变更 3 778.6

6 单位产新建 311697.49

7 赠与 1 28.48

8 存量房买卖 24 4379004134.67

9 判决仲裁 2 439.41

10 继承遗赠 1 49.17

11 换证 2 228.88

12 新建房屋 17 928.91

③ 用水晶报表关联视图 view_AAAA 设计出类似于②的数据格式 需要的表

FC_Owner

EvlVal交易价格

I_OptInst,

SOName 业务小类

fc_owoom,

BArea 建筑面积

FC_Room

regdate 受理日期

篇二：sql查询语句学习测试答案

第一部分SQL查询语句的学习

单表查询 1、--查询订购日期在1996年7月1日至1996年7月15日之间的订单的订购日期、订单ID、客户ID和雇员ID等字段的值

use eee

SELECT 订购日期,订单ID,客户ID,

雇员ID

FROM 订单

WHERE 订购日期BETWEEN '1996-7-1 00:00:00' AND '1996-7-15 23:59:59'

2、--查询“Northwind”示例数据库中供应商的ID、公司名称、地区、城市和电话字段的值。条件是“地区等于华北”并且“联系人头衔等于销售代表”。

use eee

SELECT 供应商ID,公司名称,地区,城市,电话

FROM 供应商

WHERE 地区='华北' AND 联系人职务='销售代表'

3、--查询“Northwind”示例数据库中供应商的ID、公司名称、地区、城市和电话字段的值。其中的一些供应商位于华东或华南地区，另外一些供应商所在的城市是天津 use eee

SELECT 供应商ID,公司名称,地区,城市,电话

FROM 供应商

WHERE 地区IN('华东', '华南') OR 城市='天津'

4、--查询“Northwind”示例数据库中位于“华东”或“华南”地区的供应商的ID、公司名称、地区、城市和电话字段的值

use eee

SELECT 供应商ID,公司名称,地区,城市,电话

FROM 供应商

WHERE 地区IN('华东', '华南')

多表查询 5、--查询订购日期在1996年7月1日至1996年7月15日之间的订单的订购日期、订单ID、相应订单的客户公司名称、负责订单的雇员的姓氏和名字等字段的值，并将查询结果按雇员的“姓氏”和“名字”字段的升序排列，“姓氏”和“名字”值相同的记录按“订单 ID”的降序排列

use eee

SELECT 订购日期,订单ID,公司名称,姓氏,名字

FROM 订单,雇员,客户

WHERE 订购日期BETWEEN '1996-7-1 00:00:00' AND '1996-7-15 23:59:59'

AND 订单.雇员ID = 雇员.雇员ID

AND 订单.客户ID = 客户.客户ID

ORDER BY 姓氏,名字ASC,订单ID DESC

6、--查询“10248”和“10254”号订单的订单ID、运货商的公司名称、订单上所订购的产品的名称

use eee

SELECT 订单.订单ID,公司名称,产品名称

FROM 订单,运货商,产品,订单明细

WHERE 订单.订单ID IN('10248','10254')

AND 订单.订单ID = 订单明细.订单ID

AND 订单明细.产品ID = 产品.产品ID

AND

订单.运货商= 运货商.运货商ID

7、--查询“10248”和“10254”号订单的订单ID、订单上所订购的产品的名称、数量、单价和折扣

use eee

SELECT 订单.订单ID,产品名称,数量,订单明细.单价,折扣

FROM 订单,产品,订单明细

WHERE 订单.订单ID IN('10248','10254')

AND 订单.订单ID = 订单明细.订单ID

AND 订单明细.产品ID = 产品.产品ID

篇三：sql语句练习题及答案

一 在数据库 school 中建立student , sc, course 表。

学生表、课程表、选课表属于数据库 School ，其各自的数据结构如下：

学生 Student (Sno,Sname,Ssex,Sage,Sdept)

课程表 course(Cno,Cname,Cpno,Ccredit)

学生选课 SC(Sno,Cno,Grade)

二 设定主码

1 Student表的主码：sno2 Course表的主码：cno 3 Sc表的主码：sno，cno

1写出使用 Create Table 语句创建表 student , sc, course 的SQL语句

2

3 删除student表中的元组

4在数据库school中删除关系student

5在student表添加属性sbirthdate 类型 datetime

Delete

1 删除所有 JSJ 系的男生 from Student where Sdept=’JSJ’ and Ssex=’男’2 删除“数据库原理”的课的选课纪录

from SC where Cno in (select Cno fromCourse where Cname=’数据库原理’)

Update

1 修改 0001 学生的系科为: JSJ

2 把陈小明的年龄加1岁，性别改为女。 2 修改李文庆的1001课程的成绩为 93 分 3 把“数据库原理”课的成绩减去1分

Select 查询语句

一 单表

1查询年龄在19至21岁之间的女生的学号,姓名,年龄,按年龄从大到小排列。 2查询姓名中第2个字为“明”字的学生学号、性别。 3查询 1001课程没有成绩的学生学号、课程号

4查询JSJ 、SX、WL 系的年龄大于25岁的学生学号,姓名，结果按系排列 5按10分制查询学生的sno,cno,10分制成绩

（1-10分 为1 ，11-20分为2 ，30-39分为3，。。。90-100为10） 6查询 student 表中的学生共分布在那几个系中。（distinct） 7查询0001号学生1001，1002课程的成绩。

二 统计

1查询姓名中有“明”字的学生人数。 2计算‘JSJ’系的平均年龄及最大年龄。 3查询学生中姓名为张明、赵英的人数

4计算每一门课的总分、平均分，最高分、最低分，按平均分由高到低排列 5 计算 1001,1002 课程的'平均分。

6 查询平均分大于80分的学生学号及平均分 7 统计选修课程超过 2 门的学生学号

8 统计有10位成绩大于85分以上的课程号。 9 统计平均分不及格的学生学号

10 统计有大于两门课不及格的学生学号

三 连接

1查询 JSJ 系的学生选修的课程号

2查询选修1002 课程的学生的学生姓名 (不用嵌套及嵌套2种方法） 3查询数据库原理不及格的学生学号及成绩

4查询选修“数据库原理”课且成绩 80 以上的学生姓名(不用嵌套及嵌套2种方法） 5查询平均分不及格的学生的学号，姓名,平均分。 6查询女学生平均分高于75分的学生姓名。

7查询男学生学号、姓名、课程号、成绩。(一门课程也没有选修的男学生也要列出，不能

四 嵌套、相关及其他

1 查询平均分不及格的学生人数

2 查询没有选修1002 课程的学生的学生姓名

3 查询平均分最高的学生学号及平均分 （2种方法 TOP , any , all） *4 查询没有选修1001，1002课程的学生姓名。

5 查询1002课程第一名的学生学号（2种方法） 6 查询平均分前三名的学生学号

7 查询 JSJ 系的学生与年龄不大于19岁的学生的差集

8 查询1001号课程大于90分的学生学号、姓名及平均分大于85分的学生学号、姓名 9 查询每门课程成绩都高于该门课程平均分的学生学号 10 查询大于本系科平均年龄的学生姓名

答案

参考答案

1 create table student (sno6), sname var8), ssex2), sagesmallint, sdept var15), primary key(sno))

create table sc

(sno6), cno 4),

grade decimal(12,2), primary key(sno,cno))

into student

values( ’4001’,’赵茵’,’男’,20,’SX’)

from student

student

alter table student add sbirthdate datetime

1 select sno, sname, sage from student

where ssex=’女’ and sage between 19 and 21order by sage desc2 select sno, ssexfrom student

where sname like ’_明% ’ 3 select sno, cnofrom sc

where grade is null and cno=’1001’ 4 select sno, sname from student

where sdept in (’JSJ’,’SX’,’WL’) and sage>25 group by sdept

select sno, cno, grade/10.0+1 as levelfrom sc

select distinct sdept from student select grade from sc

where sno=’0001’ and (cno=’1001’ or cno=’1002’)

select count(*) from student where sname like ’%明% ’ select avg(sage),max(sage) from student where sdept=’JSJ’ select cno,sum(grade),avg(grade),max(grade),min(grade) from sc group by cno

order by avg(grade) desc

select cno, avg(grade) from sc where cno in(‘1001’,’1002’) group by cno

select sc.sno ,avg(grade) from scgroup by sc.sno

having avg(grade)>80

select sno from sc group by sno having count(*)>2

select cno from sc where grade>85 group by cno having count(*)=10 select sno from sc group by sno having avg(grade)<60

select sno from sc where grade<60 group="" by="" sno="" having="">2

select cno from student,sc where student.sno=sc.sno and sdept=’JSJ’ a:select sname from student,sc where student.sno=sc.sno and cno=’1002’

b:select sname from student where sno in (select sno from sc where cno=’1002’)

select sno,grade from sc,course

where sc.cno=course.cno and cname=’数据库原理’ and grade<60 a:select sname from student ,sc,course

where student.sno=sc.sno and sc.cno=course.cno and grade>80 and cname=’ 数据库原理’ b:select sname from student where sno in (select sno from sc where grade>80 and cno in (select cno from course where cname=’ 数据库原理’)) select sno,sname,avg(grade) from sc,studentwhere student.sno=sc.snogroup by student.sno having avg(grade)<60

a:select sname from student where ssex=’女’ and sno in(select sno from sc group by sno having avg(grade)>75)

b:select sname from sc,student where student.sno=sc.sno and ssex=’女’group by student.sno having avg(grade)>75

select student.sno,sname,cno,grade from student left join sc on student.sno=sc.sno and ssex=’男’

select count(*) from student where sno in( select sno from sc group by sno havingavg(grade)<60)

select sname from student where sno not in（select sno from sc where cno=’1002’）

student

0001 aaX 0002 bb

0003 ccX Sc

0001 1001 0001 1002 0002 1001 0003 1002

Select sname from student where not exists(select* from sc where cno=’1002’ and sc.sno=student.sno)

a:select top 1 sno,avg(grade) from sc group by sno order by avg(grade) desc b:select sno, avg(grade) from sc group by sno

having avg(grade)=(select top 1 avg(grade) from scgroup by sno order by avg(grade) desc) c:select sno, avg(grade) from sc group by sno

having avg(grade)>=all ( select avg(grade) from sc group by sno)

select sname from student where not exists(

select * from course where cno in(‘1001’,’1002’) and

not exists(select * from sc where sno =student.sno and cno=course.cno) ) a:select top 1 sno from sc cno=’1002’ order by grade desc b:select sno from sc where cno=’1002’ and grade >=all (

1. 查询雇员（employee）的姓和名

Select substring(username,1,1) as 姓 from employee

Select substring(username,2,2) as 名 from employee

2. 查询雇员的姓名

Select username from employee

3. 查询雇员数

Select count(*) from employee

4. 查询雇员的姓名和职务

Select username,,duty from employee

5. 查询雇员的工龄

Select year(getdate())-开始工作日期 as 工龄 from employee

任务2：条件查询

1. 查询雇员（employee）从事"Sales Representative"职务的有哪些人

Select * from employee where duty=’ Sales Representative’

2. 查询工龄超过15年的雇员

Select * from employee where cast( (year(getdate())-开始工作日期) as int)>=15

3. 查询姓以a开头的雇员

Select * from employee where username like ‘a%’

4. 查询姓的开头字母在m以后的雇员

Select * from employee where cast((substring(username,1,1) as varchar)>=’m’

5. 认为hire_date是雇员生日，查询巨蟹座的雇员

Select * from employee where birthday between ‘6-22 ‘ and ‘7-22’

任务3：联合查询

1. 查询雇员和雇员职位

Select a.id,b.duty from employee, as a,jobs as b

2. 查询雇员、雇员职位和雇员所在出版社

Select a.id,b.duty, b.publishing from employee as a,jobs as b on a.id=b.id

3. 查询雇员、雇员工资、雇员离本职位最高工资的差值

select a. ID,a.username,a.[雇员工资],b.[最高工资]-a.[雇员工资] as [差值] from employee a,jobs b where a.[职位]=b.[职位]

1

create table 学生 (学号 char(9) not null primary key,

姓名 varchar(20) not null,

性别 char(2) check (性别='男' or 性别='女'),

年龄 int,

专业 varchar(20))

2

update 课程 set 课程名='SQL数据库' where 课程号='100003'

3

delete from 课程 where 课程号='100002'

4

create table 选课

( id int identify(1,1),

学号 char(9),

课程号 char(6),

分数 int,

foreign key(学号) references 学生(学号),

foreign key(课程号) references 课程(课程号))

5

insert into 课程 select '100001','C语言',2

union all

select '100002','数据结构',2

union all

select '100003','数据库原理',2

6

create view v_选课 as

select 学生.学号,学生.姓名,课程.课程号,课程.课程名,课程.学分,选课.分数

from 学生,课程,选课

where 学生.学号=选课.学号

and 课程.课程号=选课.课程号

7

select 姓名,学号,专业 from 学生 where 姓名 like '张%' and 性别='女'

8

select 学号,姓名 from 学生 where 学号 in (select 学号 from 成绩 where 分数<60)

---