数据库相关代码题

数据库相关代码题

1.北京车辆管理实行尾号限行制度（周一至周五，每天限行2个尾号），交管局管理系统中有如下三张表：车辆所属关系表Ownershjp（车牌号，车主身份证号），车主身份信息表Ownerinfo（车主身份证号，姓名，年龄），限行表Restriction（尾号，限行日期）。
请回答下列问题：
1)写SQL查询：名下有多部车辆的车主列表（车主身份证号，名下车辆数量）
2)写SQL查询：40岁以下的车主名下的车辆牌照列表（车主姓名，车牌号）
3)写SQL查询：周三没有车可以开的车主姓名列表（车主姓名）

答：1)select Ownerid,count() from Ownership group by Ownerid HAVING count()>1;
2)select o1.Name,o2.Platenumber from Ownerinfo o1,Ownership o2 where o1.Ownerid=o2.Ownerid and Age<40;
3)select Platenumber,o1.Ownerid,Namefrom Ownership o1,Ownerinfo o2 whereo1.Ownerid=o2.Ownerid and substring(Platenumber,-1)=(select Lastdigit fromRestriction where Restriction=‘周三’) group by Ownerid;

2.Oracle数据库中,按如下要求编写sql查询?
已知表内容:
日期 成绩
2005-05-09 胜
2005-05-09 胜
2005-05-09 负
2005-05-09 负
2005-05-10 胜
2005-05-10 负
2005-05-10 负

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

日期 胜(数量) 负(数量)
2005-05-09 2 2
2005-05-10 1 2
答：select rq,sum(case when shengfu=’胜’ then 1 else 0 end) as胜,sum(case when shengfu=’负’ then 1 else 0 end) as负from tab3 group by rq

3.使用一条SQL语句，查询每门课都大于80分的学生姓名：
name Kecheng fenshu
张三 语文 81
张三 数学 75
李四 语文 76
李四 数学 90
王五 语文 81
王五 数学 98
王五 英语 90
答案：select distinct [Name] from [表] where [Name] not in (select [Name] from [表] where [fengshu]<=80；
4.假设数据库表zkbc_sales(产品，地区，销售额)表中记录如下：
年度 产品 地区 销量
2012 A 北京 500
2013 A 北京 200
2013 A 上海 300
2013 B 北京 100
2013 B 深圳 100
2012 C 上海 200
2012 C 上海 300

通过一条SQL查出如下结果：
地区 A B C
北京 700 100 0
上海 300 0 500
深圳 0 100 0

答案：SELECT coalesce(name, ‘总数’), SUM(singin) as singin_count FROM zkbc_sales GROUP BY name WITH ROLLUP;

5.存在两张表，表格创建语句如下所示。
create table testtablel
(
id int IDENTITY,
department varchar(12)
)
insert into testtablel values(‘设计’)
insert into testtablel values(‘市场’)
insert into testtablel values(‘售后’)
结果
id department
1 设计
2 市场
3 售后
create table testtable2
(
id int IDENTITY,
dptID int,
name varchar( 12)
)
insert into testtable2 values(1,’ 张三’)
insert into testtable2 values(1，’李四’ )
insert into testtable2 values(2,’ 王五’)
insert into testtable2 values(3，’彭六’)
insert into testtable2 values(4,‘陈七’)

写一条SQL语句显示如下结果：
id dptID department name
1 1 设计 张三
2 1 设计 李四
3 2 市场 王五
4 3 售后 彭六
5 4 无 陈七

答：SELECT testtable2.* , ISNULL(department,‘无’)
FROM testtable1 right join testtable2 on testtable2.dptID = testtable1.ID

6.有两张表，按照要求编写相应的SQL语句：
部门表department （部门编号dept_id 部门名称dept_name ）
员工表employee （员工编号emp_id 员工姓名emp_name 部门编号dept_id 工资emp_wage ）
根据下列题目写出SQL语句：
1)列出工资大于5000的员工所属的部门名、员工id和员工工资。
2)列出员工表中的部门id对应的名称和员工id（左连接）。
3)求各部门的平均工资。
4)假如现在在库中有一个和员工表结构相同的空表employee2,请用一条sql语句将employee表中的所以记录插入到employee2表中。
答案：
1）select d.dept_name,e.emp_id,e.emp_wage from department d ,employee e where e.emp_wage>5000 and d.dept_id = e.dept_id;
2）selsect d.dept_id,d.dept_name,e.emp_id from department d left join employee e where d.dept_id = e.dept_id;
3）select d.dept_name ,round(avg(nvl(e.emp_wage,0)),2) sal from employee e left join department d on e.dept_id=d.dept_id group by d.dept_name;
4） insert into employee2 select * from employee;

7.按照要求编写相应的SQL语句。
客户信息表CIF_PERS_INFO，表中字段说明及数据如下：
*字段说明：
CIF_NO 客户编号（主键）
CIF_NAME 客户姓名
CIF_ID_NO 身份证号码
CIF_AREA_NAME 所在行政区划
CIF_PHONE 手机号码
CIF_BLACKLIST 是否被列入黑名单
*表中数据：
CIF_NO CIF_NAME CI