SQL基础练习

use master
go
drop database my_DB
go
create database my_DB
–on primary
–(
–name=‘my_DB’
–filename=‘H:\SQL\my_DB.mdf’
–)
go
use my_DB
go
create table dept
(
DEPTNO int not null ,
DNAME varchar(14),
LOC varchar (13)
)
GO

create table EMP
(
EMPNO int not null,
ENAME varchar (10),
JOB varchar (9),
MGR int,
HIREDATE datetime,
SAL float,
COMM float,
DEPTNO int
)
GO

create table SALGRADE
(
GRADE int,
LOSAL int,
HISAL int
)

–
–1.写出上述表的建表语句。
–在DEPT上创建名为“PK_DEPT”的主键约束，
–在EMP表上创建名为“ PK_EMP”的主键约束以及指向表DEPT的外键约束“ FK_DEPTNO”
alter table dept
add constraint PK_DEPTNO primary key(DEPTNO)

alter table EMP
add constraint PK_EMPNO primary key(EMPNO)

alter table EMP
add constraint FK_PK_DEPTNO foreign key (DEPTNO) references dept(DEPTNO)

–delete from emp

–2.给出相应的INSERT语句来完成题中给出数据的插入。
insert into dept values(10,‘ACCOUNTING’,‘NEW YORK’)
insert into dept values(20,‘RESEARCH’,‘DALLAS’)
insert into dept values(30,‘SALES’,‘CHICAGO’)
insert into dept values(40,‘OPERATIONS’,‘BOSTON’)

insert EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
SELECT 7369,‘SMITH’,‘CLERK’, 7566,‘1980-12-17’,880,NULL,20 union
SELECT 7499,‘ALLEN’,‘SALESMAN’, 7698,‘1981-02-20’,1600,300,30 union
SELECT 7521,‘WARO’,‘SALESMAN’, 7698,‘1981-02-22’,1250,500,30 union
SELECT 7566,‘JONES’,‘MANAGER’, 7839,‘1981-04-02’,2975,NULL,20 union
SELECT 7654,‘MARTIN’,‘salesman’, 7698,‘1981-09-28’,1250,1400,30 union
SELECT 7698,‘BLAKE’,‘MANAGER’, 7839,‘1981-05-01’,2850,NULL,30 union
SELECT 7782,‘CLARK’,‘MANAGER’, 7839,‘1981-06-09’,2450,NULL,10 union
SELECT 7788,‘SCOTT’,‘ANALYST’, 7566,‘1987-06-13’,3000,NULL,20 union
SELECT 7839,‘KING’,‘PRESIDENT’, NULL,‘1981-11-17’,5000,NULL,10 union
SELECT 7844,‘TURN’,‘SALESMAN’, 7698,‘1981-09-08’,1500,0,30 union
SELECT 7876,‘ADAMS’,‘CLERK’, 7788,‘1987-06-13’,1150,NULL,20 union
SELECT 7900,‘JAMES’,‘CLERK’, 7698,‘1981-12-03’,950,NULL,30 union
SELECT 7934,‘MILLER’,‘CLERK’, 7782,‘1982-01-23’,1300,NULL,10

insert SALGRADE(GRADE,LOSAL,HISAL)
select 1,700,1200 union
select 2,1201,1400 union
select 3,1401,2000 union
select 4,2001,3000 union
select 5,3001,9999

select * FROM dept
select * FROM EMP

–将所有员工的工资上浮10%
update emp set sal = sal+0.1*sal;
select ename,sal,comm from emp;

–查询员工(用别名)姓名、薪水、补助
select ename 姓名,sal 薪水,comm 补助 from emp;

–5 查询所有员工的姓名，工资
select ename ,sal from emp;

–4 查看EMP表中部门号为10的员工的(用别名)姓名，职位，参加工作时间，工资
select ename,job,hiredate,sal from emp where deptno=‘10’;

–5 查所有已有的职位(job)，要求去除重复项
select distinct job from emp;

–6、计算每个员工的年薪，并取列名为Salary of Year（emp.sal为员工的月薪），要求输出员工姓名，年薪。
select ename,sal*12 ‘Salary of Year’ from emp;

–7.查询每个员工每个月拿到的总金额（emp.sal为工资，emp.comm为补助）。（提示：isnull（ex1,ex2）表示如果ex1为空则返回ex2）
select ename,sal+isnull(comm,0) as total from emp;

–8 显示职位是主管（manager）的员工的姓名，工资
select ename,sal from emp where job=‘manager’;

–9 显示第3个字符为大写O的所有员工的姓名及工资
select ename,sal from emp where ename like ‘__O%’;

–10 显示职位为销售员（SALESMAN）或主管（MANAGER）的员工的姓名，工资，职位
select ename,sal,job from emp where job=‘salesman’ or job=‘manager’;

–11 显示所有没有补助的员工的姓名
select ename from emp where comm is null;

–12 显示有补助的员工的姓名，工资，补助
select ename,sal,comm from emp where comm is not null;

–13 排序显示所有员工的姓名，工资（按工资降序方式）
select ename,sal from emp order by sal desc;

–14 显示员工的最高工资和最低工资
select max(sal),min(sal) from emp;

–15 显示所有员工的平均工资，总工资
select avg(sal),sum(sal) from emp;

–16.显示补助在员工中的发放比例、即有多少比例的员工有补助。（此题需注意两个问题：1.select语句中进行除法如何保留小数点后数据。2.count函数如何处理null型数据。）
select count(comm)/count(*) from emp;

–17显示每种职业的平均工资
select job,avg(sal) from emp group by job;

–18 显示每个部门每种岗位的平均工资和最高工资
select deptno,job,avg(sal),max(sal) from emp group by deptno,job order by deptno;

–19 显示平均工资低于2500的部门号，平均工资及最高工资
select deptno,avg(sal),max(sal) from emp group by deptno having avg(sal) < 2500;

–20上一条语句以平均工资升序排序
select deptno,avg(sal),max(sal) from emp group by deptno having avg(sal) < 2500 order by avg(sal) asc;

–21显示工资高于2500或岗位为MANAGER的所有员工的姓名，工资，职位，和部门号
select ename,sal,job,deptno from emp where sal>2500 or job=‘manager’;

–22排序显示所有员工的姓名，部门号，工资（以部门号升序，工资降序，雇用日期升序显示）
select ename,deptno,sal from emp order by deptno asc,sal desc,hiredate asc;

–23. 显示部门名以及相应的员工姓名
select dname,ename from emp join dept on emp.deptno=dept.deptno;

–24. 查询SCOTT的上级领导的姓名
select ename from emp where empno=(
select mgr from emp where ename=‘scott’);

–25. 显示部门的部门名称，员工名即使没有员工也显示部门名称
select dname,ename from emp a right join dept b on a.deptno=b.deptno;

–26. 显示所有员工的名称、工资以及工资级别
select ename,sal,grade from emp join salgrade on sal between salgrade.losal and salgrade.hisal;

–27. 显示ACCOUNTING部门所有员工的名称，工资
select ename,sal from emp where deptno=(
select deptno from dept where dname=‘accounting’);

–28. 显示职位属于10号部门所提供职位范围的员工的姓名，职位，工资，部门号。
select ename,job,sal,deptno from emp where job in(
select job from emp where deptno=‘10’);

–29. 显示在所有员工中高于30号部门中任何一个员工工资的员工的姓名，工资和部门号。
select ename,sal,deptno from emp where sal >(
select max(sal) from emp where deptno=‘30’);
–30. 显示工资高于2500或职位为manager的员工的姓名，工资和职位（采用union语法实现）
select ename,sal,job from emp where sal>2500 union select ename,sal,job from emp where job=‘manager’ order by ename;

–31. 显示工资高于2500且职位为manager的员工的姓名，工资和职位（采用intersect语法实现）
select ename,sal,job from emp where sal>2500 intersect select ename,sal,job from emp where job=‘manager’;

–31. 显示工资高于2500但职位不是manager的员工的姓名，工资和职位（采用except语法实现）
select ename,sal,job from emp where sal>2500 except select ename,sal,job from emp where job=‘manager’;