数据库相关代码题
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