完整的数据库创建练习

完整的数据库创建练习
2019/7/22

--建库
USE master
GO
IF EXISTS(SELECT * FROM SYSDATABASES WHERE NAME = 'emp_db')
DROP DATABASE emp_db
GO                                                            
CREATE DATABASE emp_db
ON PRIMARY
(
	NAME = 'emp_db',
	FILENAME = 'D:\MySqlserver\a\emp_db.mdf',
	SIZE = 5MB,
	FILEGROWTH = 1MB
)                                                             
LOG ON
(
	NAME = 'emp_db_log',
	FILENAME = 'D:\MySqlserver\a\emp_db_log.ldf',
	SIZE = 1MB,
	FILEGROWTH = 10%
)
GO                                                            

USE emp_db
GO

IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME = 'dept')
DROP TABLE dept
GO                                                             
CREATE TABLE dept						--部门表
(
	DEPTNO INT PRIMARY KEY not null,	--部门号
	DNAME varchar(14),					--部门名
	LOC  varchar(14)					--部门所在地
)
GO                                                              

IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME = 'emp')
DROP TABLE emp
GO                                                             
CREATE TABLE emp							--员工表
(
	EMPNO INT PRIMARY KEY ,					--员工号
	ENAME VARCHAR(10),						--员工名
	JOB VARCHAR(9),							--职务
	MGR INT,								--经理
	HIREDATE DATETIME,						--日期
	SAL decimal(7,2),						--薪水
	COMM decimal(7,2),						--
	DEPTNO INT REFERENCES dept(DEPTNO)		--部门号
)                                                              
GO



select * from dept 
select * from emp    

insert dept values(10,'ACCOUNTING','NEW YORK')                                                  
insert dept values(20,'RESEARCH','DALLAS')
insert dept values(30,'SALES','CHICAGO')
insert dept values(40,'OPERATIONS','BOSTON')


delete from emp
insert emp values(7369,'SMITH','CLERK',7902,convert(datetime,'1980-12-17'),800.00,0,20)
insert emp values(7499,'ALLEN','SALESMAN',7698,convert(datetime,'1981-02-20'),1600.00,300.00,30)
insert emp values(7521,'WARD','SALESMAN',7698,convert(datetime,'1981-02-22'),1250.00,500.00,30)
insert emp values(7566,'JONES','MANAGER',7839,convert(datetime,'1981-04-02'),2975.00,0,20)
insert emp values(7654,'MARTIN','SALESMAN',7698,convert(datetime,'1981-09-28'),1250.00,1400.00,30)
insert emp values(7698,'BLAKE','MANAGER',7839,convert(datetime,'1981-05-01'),2850.00,0,30)
insert emp values(7782,'CLARK','MANAGER',7839,convert(datetime,'1981-06-09'),2450.00,0,10)
insert emp values(7788,'SCOTT','ANALYST',7566,convert(datetime,'1987-04-19'),3000.00,0,20)
insert emp values(7839,'KING','PRESIDENT',' ',convert(datetime,'1981-11-17'),5000.00,0,10)
insert emp values(7844,'TURNER','SALESMAN',7698,convert(datetime,'1981-09-08'),1500.00,0,30)
insert emp values(7876,'ADAMS','CLERK',7788,convert(datetime,'1987-05-23'),1100.00,0,20)
insert emp values(7900,'JAMES','CLERK',7698,convert(datetime,'1981-12-03'),950.00,0,30)
insert emp values(7902,'FORD','ANALYST',7566,convert(datetime,'1981-12-03'),3000.00,0,20)
insert emp values(7934,'MILLER','CLERK',7782,convert(datetime,'1982-01-23'),1300.00,0,10)

--1.选择部门30中的所有员工.	
SELECT EMPNO,ENAME,JOB FROM emp WHERE DEPTNO=30
SELECT * FROM emp WHERE DEPTNO=30

--2.列出所有办事员(clerk)的姓名，编号和部门编号.	
SELECT EMPNO,ENAME,DEPTNO FROM emp WHERE JOB='CLERK'

--3.找出部门10中所有经理(manager)和部门20中所有办事员(clerk)的详细资料.
SELECT * FROM emp WHERE(DEPTNO=10 AND JOB='MANAGER' ) OR (DEPTNO=20 AND JOB='CLERK' )

--4.找出部门10中所有经理(manager),部门20中所有办事员(clerk),既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料. 	
SELECT * FROM emp WHERE(DEPTNO=10 AND JOB='MANAGER' ) OR (DEPTNO=20 AND JOB='CLERK' ) OR ( JOB!='MANAGER' AND JOB!='CLERK'AND SAL>=2000)
SELECT * FROM emp WHERE(DEPTNO=10 AND JOB='MANAGER' ) OR (DEPTNO=20 AND JOB='CLERK' ) OR ( JOB NOT IN ('MANAGER','CLERK') AND SAL>=2000)

--5.以字母小写的方式显示所有员工的姓名.	LOWER(),UPPER()
SELECT LOWER(ENAME) FROM emp

--6.显示正好为5个字符的员工的姓名.	
SELECT ENAME FROM emp WHERE ENAME LIKE'_____'
SELECT ENAME FROM emp WHERE len(ENAME) = 5

--7.显示不带有"r"的员工的姓名.	
SELECT ENAME 员工名 FROM emp WHERE ENAME  NOT IN(SELECT ENAME FROM emp WHERE  ENAME LIKE '%R%')
SELECT ENAME 员工名 FROM emp WHERE ENAME  NOT LIKE '%R%'

--8.显示所有员工姓名的前三个字符.
SELECT left(ENAME,3) 员工名 FROM emp 
SELECT SUBSTRING(ENAME,1,3) 员工名 FROM emp 

--9.显示所有员工的姓名,用a替换所有"A"	
SELECT replace(ENAME,'A','a') FROM emp 

--10.显示员工的详细资料,按姓名排序.	
SELECT * FROM emp ORDER BY ENAME 

--11.显示员工的姓名和受雇日期,根据其服务年限,将最老的员工排在最前面.	
SELECT ENAME,HIREDATE FROM emp ORDER BY HIREDATE

--12.显示所有员工的姓名、工作和薪金,按工作的降序排序,若工作相同则按薪金排序.
SELECT ENAME,JOB,SAL FROM emp  ORDER BY JOB DESC ,SAL

--13.显示姓名字段的任何位置包含"A"的所有员工的姓名.
SELECT ENAME FROM emp WHERE ENAME LIKE'%A%'



--1.列出在每个部门工作的员工数量、平均工资和平均服务期限。

SELECT COUNT(DEPTNO) AS 员工数量 ,AVG(SAL+COMM) AS 平均工资, 
AVG(DATEDIFF(DAY,HIREDATE,GETDATE()))  AS 平均服务天数 FROM emp GROUP BY DEPTNO

--2.列出所有部门的详细信息和部门人数。
SELECT * FROM (SELECT DEPTNO,COUNT(*) AS 部门人数 FROM emp GROUP BY DEPTNO ) e,dept d
WHERE e.DEPTNO =  d.DEPTNO

--3.列出各种工作的最低工资。
SELECT JOB,MIN(SAL) AS 最低工资 FROM emp  GROUP BY JOB

--4.列出各个部门的clerk(职员)的最低薪金。
SELECT DEPTNO,MIN(SAL) AS 最低工资 FROM emp WHERE JOB='CLERK' GROUP BY DEPTNO 

--5.列出员工表中每个部门的员工数（员工数必须大于3），和部门名称     
SELECT DNAME,COUNT(EMPNO)员工数 FROM emp,dept WHERE emp.DEPTNO = dept.DEPTNO  GROUP BY emp.DEPTNO,DNAME HAVING COUNT(EMPNO)>3 

--6.找出工资比ford高的员工
SELECT ENAME,EMPNO FROM emp WHERE (SAL+COMM)>(SELECT (SAL+COMM) FROM emp WHERE ENAME='FORD' )

--7.以职位分组,找出平均工资最高的两种职位
SELECT TOP 2 JOB,AVG(SAL+COMM) AS 平均工资 FROM emp GROUP BY JOB ORDER BY AVG(SAL+COMM)DESC

--8.查找不在部门30,且比部门30中任何一个人工资都高的员工姓名
SELECT ENAME,DEPTNO FROM emp WHERE DEPTNO!=30 AND (SAL+COMM)>(SELECT TOP 1 (SAL+COMM) FROM emp WHERE DEPTNO=30 ORDER BY (SAL+COMM) DESC)
--只要工资大于部门30中最高的，自然是不在部门30中的人.
--改：查找不在部门20,且比部门30中任何一个人工资都高的员工姓名
SELECT ENAME,DEPTNO FROM emp WHERE DEPTNO!=20 AND (SAL+COMM)>(SELECT TOP 1 (SAL+COMM) FROM emp WHERE DEPTNO=30 ORDER BY (SAL+COMM) DESC)

--9.得到平均工资大于2000的工作职种
SELECT JOB,AVG(SAL+COMM) FROM emp GROUP BY JOB HAVING AVG(SAL+COMM)>2000 

--10.得到每个月工资总数最多的那个部门的部门编号,名称,部门所在地,工资总数,部门人数
SELECT TOP 1 dept.DEPTNO,DNAME,LOC,SUM(SAL+COMM) AS 部门工资总数 ,COUNT(dept.DEPTNO) FROM emp,dept 
	WHERE emp.DEPTNO =dept.DEPTNO GROUP BY dept.DEPTNO,DNAME,LOC ORDER BY SUM(SAL+COMM)DESC

--11.查找出部门20和部门30中，工资最高的第1名到工资第3名的员工的员工名字，部门名字，部门所在
select e.ENAME,d.DNAME,d.LOC,SAL from (select top 3 * from emp where deptno = 20 order by sal desc) e,dept d where e.DEPTNO = d.DEPTNO
union all
select e.ENAME,d.DNAME,d.LOC,SAL from (select top 3 * from emp where deptno = 30 order by sal desc) e,dept d where e.DEPTNO =d.DEPTNO

--12.查找出收入（工资+奖金），下级比自己上级还高的员工编号，姓名，收入						
SELECT e.EMPNO,e.ENAME,(e.SAL+e.COMM) AS 员工收入,e2.EMPNO,e2.ENAME,(e2.SAL+e2.COMM)对应经理收入 
	FROM emp e,emp e2 where e.EMPNO=e2.MGR AND e2.SAL + e2.COMM > e.SAL + e.COMM 

--13.按部门统计员工数,查出 1.(员工数最多的部门) 中 2.(工资第1名到第3名) 的 所有员工信息       
select top 3 * from emp where deptno = (select top 1 deptno from emp group by deptno order by count(*) desc) order by sal desc

--14.查出 (ADAMS所在部门) (工作年限最大的员工名字)
SELECT top 1 ENAME AS 工作年限最大的员工 FROM emp WHERE DEPTNO=(SELECT DEPTNO FROM emp WHERE ENAME='ADAMS')  ORDER BY HIREDATE 

SELECT ENAME AS 工作年限最大的员工 FROM emp WHERE HIREDATE IN (SELECT min( HIREDATE) AS 最大工作年限 FROM emp 
	WHERE DEPTNO=(SELECT DEPTNO FROM emp WHERE ENAME='ADAMS') )

--15.使用分页写法列出emp表中该查询的第1和第2页数据（每页10条）
--分页的规则：(page - 1) * size + 1 <= rn <= page * size
SELECT TOP 10* FROM emp WHERE EMPNO NOT IN (SELECT TOP 0 EMPNO FROM emp ORDER BY  EMPNO)ORDER BY EMPNO  
SELECT TOP 10* FROM emp WHERE EMPNO NOT IN (SELECT TOP 10 EMPNO FROM emp ORDER BY  EMPNO)ORDER BY EMPNO 

select * from (select ROW_NUMBER() over(order by sal) rn,* from emp) t where t.rn between 1 and 10
select * from (select ROW_NUMBER() over(order by sal) rn,* from emp) t where t.rn between 11 and 20
--select ROW_NUMBER() over(order by sal) rn 第一列的数字
--order by sal 工资由低到高排列