数据库习题

这里写图片描述

show databases;
CREATE DATABASE HR CHARSET UTF8;
USE HR;
CREATE TABLE TbDept(deptno tinyint primary key,dname varchar(30) not null,dloc varchar(30) not null);

create table TbEmp(empno int primary key,ename varchar(20) not null,job varchar(20) not null,mgr int,sal int not null,dno tinyint);
alter table TbEmp add constraint fk_dno FOREIGN key(dno) references TbDept(deptno);

insert into TbDept  VALUES('10','会计部','北京');
insert into TbDept  values('20','研发部','成都');
insert into TbDept  values('30','销售部','重庆');
insert into TbDept  values('40','运维部','深圳');
select * from TbDept;

insert into TbEmp  values(7800,'张三丰','总裁',null,9000,20);
insert into TbEmp  values(2056,'乔峰','分析师',7800,5000,20);
insert into TbEmp  values(3088,'李莫愁','设计师',2056,3500,20);
insert into TbEmp  values(3211,'张无忌','程序员',2056,3200,20);
insert into TbEmp  values(3233,'丘处机','程序员',2056,3400,20);
insert into TbEmp  values(3251,'张翠山','程序员',2056,4000,20);
insert into TbEmp  values(5566,'宋明月','会计师',7800,4000,10);
insert into TbEmp  values(5234,'郭靖','出纳',5566,2000,10);
insert into TbEmp  values(3344,'黄蓉','销售主管',7800,3000,10);
insert into TbEmp  values(1359,'胡一刀','销售员',3344,1800,30);
insert into TbEmp  values(4466,'苗人凤','销售员',3088,3200,30);
insert into TbEmp  values(3244,'欧阳锋','程序员',3088,3200,20);
insert into TbEmp  values(3577,'杨过','会计师',5566,2200,10);
insert into TbEmp  values(3588,'朱九真','会计师',5566,2200,10);
update TbEmp set dno=30 where job='销售主管';
select * from TbEmp;

1.select sal,ename from TbEmp where sal = (SELECT max(sal) from TbEmp);
-- select sal,ename from(select max(sal) s from TbEmp) t1 join TbEmp t2 on t1.s=t2.sal;
2.select ename,sal * 10 from TbEmp;
3.select count(*) ,d.deptno,d.dname from TbEmp e join TbDept d on e.dno=d.deptno GROUP BY d.deptno;
4.SELECT t1.deptno,t1.dname,IFNULL(t2.c,0) from TbDept t1 left join (SELECT dno,count(*) as c from TbEmp GROUP BY dno) t2 on t1.deptno=t2.dno;
-- 左连接
5.-- select sal,ename from (select max(sal) s FROM TbEmp where empno not in (7800)) t1 join TbEmp t2 on t1.s=t2.sal ;
select sal,ename from TbEmp t1 where t1.sal=(select max(sal) s from TbEmp t where empno not in (7800));
6.-- select sal,ename,empno from (select avg(sal) s from TbEmp) t1 join TbEmp t2 on t1.s<t2.sal;
select sal,ename,empno from TbEmp t where t.sal>(select avg(sal) s from TbEmp);
7.select * from TbDept t3 join(
select t1.dno,ename,sal from TbEmp t1 
join(
select avg(sal) as avgsal,dno from TbEmp GROUP BY dno) t2 
on t1.dno=t2.dno and t1.sal>avgsal) t4 
on t3.deptno=t4.dno;
8.select * from TbDept t3
join(
select ename,t1.dno,sal from TbEmp t1 
join(
select max(sal) as maxsal,dno from TbEmp GROUP BY dno) t2
on t1.dno=t2.dno and t1.sal=maxsal) as t4
on t3.deptno=t4.dno
9.SELECT * from TbEmp where empno in (SELECT distinct mgr from TbEmp where mgr is not null)
-- distinct 去重
10.select * from TbEmp ORDER BY sal LIMIT 3;
11.SELECT * from TbEmp ORDER BY sal LIMIT 5 offset 3;









阅读更多
文章标签: mysql
个人分类: 数据库
上一篇mysql数据库配置环境变量及CRUD
下一篇数据库练习题2
想对作者说点什么? 我来说一句

数据库习题答案,好东西

2009年04月08日 299KB 下载

数据库习题加答案详解

2010年12月22日 1.07MB 下载

数据库第四版答案.zip

2009年12月13日 165KB 下载

三十套SQL数据库习题

2010年10月12日 256KB 下载

数据库工程师习题参考答案

2009年05月24日 282KB 下载

数据库基础SQL习题

2008年10月30日 83KB 下载

没有更多推荐了,返回首页

关闭
关闭