数据库复习一

摘要：创建数据库，创建数据表，为数据表插入数据，数据查询

1.创建数据库scott

create database scott
2.创建数据表一emp(员工表)
create table scott.emp
(
empno integer(4) not null primary key, #员工号
ename varchar(10),  #员工姓名
job varchar(9),    #工作
mgr integer(4),    #上级编号
hiredate date,     #雇佣日期
sale integer(7),    #薪金
comm integer(7),    #佣金
deptno integer(2)    #部门编号
)
3.创建数据表二dept(部门表)
create table scott.dept
(
deptno integer(4) not null primary key, #部门编号
dname varchar(14), #部门名称
loc varchar(13) #地点
)
4.为emp表填充数据
insert into scott.emp values(7369,'smith','clerk',7902,'1980-12-17',800,null,20);


insert into scott.emp values(7499,'allen','salesman',7698,'1981-2-20',1600,300,30);
insert into scott.emp values(7521,'ward','salesman',7698,'1981-2-22',1250,500,30);
insert into scott.emp values(7566,'jones','manager',7839,'1981-4-2',2975,null,20);
insert into scott.emp values(7654,'martin','salesman',7698,'1981-9-8',1250,1400,30);


insert into scott.emp values(7698,'blake','manager',7839,'1981-5-1',2850,null,30);
insert into scott.emp values(7782,'clark','manager',7839,'1981-6-9',2450,null,10);
insert into scott.emp values(7788,'scott','analyst',7566,'1987-4-19',4000,null,20);
insert into scott.emp values(7839,'king','president',null,'1981-11-17',5000,null,10);


insert into scott.emp values(7844,'turner','salesman',7698,'1981-9-8',1500,0,30);
insert into scott.emp values(7876,'adams','clerk',7788,'1987-5-22',1100,null,20);
insert into scott.emp values(7900,'james','clerk',7698,'1981-12-3',950,null,30);
insert into scott.emp values(7902,'ford','analyst',7566,'1981-12-3',3000,null,20);


insert into scott.emp values(7934,'miller','clerk',7782,'1982-1-23',1300,null,10);
insert into scott.emp values(102,'erichu','developer',1455,'2011-5-26',5500,14,10);
insert into scott.emp values(104,'huyong','pm',7839,'2011-5-26',5500,14,10);
insert into scott.emp values(105,'wangjing','developer',1455,'2011-5-26',5500,14,10);
5.为dept表填充数据
insert into scott.dept values(10,'accounting','new york');
insert into scott.dept values(20,'research','dallas');
insert into scott.dept values(30,'sales','chicago');


insert into scott.dept values(40,'operations','boston');
insert into scott.dept values(50,'50abc','50def');
insert into scott.dept values(60,'developer','haikou');

#-----------------------------------------sql查询--------------------------------------------------------#

用sql完成以下问题列表：
1.列出至少有一个员工的所有部门
#第一种方法
select dname from scott.dept where deptno=some #some用法
(
select deptno from scott.emp
)
#第二种方法
select dname from scott.dept where deptno in #in用法
(
select deptno from scott.emp
)
#第三种方法
select dname from scott.dept where deptno in
(
select deptno from scott.emp group by deptno having count(deptno)>=1 #group by,having,count()函数用法
)
#第四种方法
select dname from scott.dept A where exists  #exist用法
(
select null from scott.emp B where A.deptno=B.deptno
)
2.列出薪金比‘smith’多的所有员工
select * from scott.emp where sale>some
(
select sale from scott.emp where ename='smith'
)
3.列出所有员工的姓名及其上级的姓名
select A.ename as empName,B.ename as mgrName from scott.emp A left join scott.emp B on A.mgr=B.empno #左外连接
4.列出受雇日期早于其直接上级的所有员工
select A.ename from scott.emp A where A.hiredate < some #日期的比较
(
select B.hiredate from scott.emp B where A.mgr = B.empno
)