数据库查询

来源：互联网发布：淘宝二手钢琴能买吗编辑：程序博客网时间：2024/05/19 23:00

use exercise;
找出这个数据库中所有的表
select name FROM sysobjects WHERE type='U'
1. 创建表
Create table DEPT
(DEPTNO INT NOT NULL, DNAME VARCHAR(14),LOC VARCHAR(13));
Alter table DEPT
add constraint PK_DEPT PRIMARY KEY (DEPTNO);
create table EMP (EMPNO INT NOT NULL,ENAME VARCHAR(10), JOB VARCHAR(9), MGR INT, HIREDATE DATETIME,
SAL FLOAT, COMM FLOAT, DEPTNO INT);
alter table EMP
add constraint PK_EMP PRIMARY KEY (EMPNO);
alter table EMP
add constraint FK_DEPTNO foreign key(DEPTNO) references DEPT(DEPTNO);
create table salgrade
(grade int,losal int,hisal int
);
select * from dept;select * from emp;select * from salgrade;
2. 插入数据
insert into DEPT values(10,'ACCOUNTING','NEW YORK');
insert into DEPT values(20,'PESEARCH','DALLAS');
insert into DEPT values(30,'SALES','CHICAGO');
insert into DEPT values(40,'OPERATIONS','BOSTON');
insert into EMP values(7369,'SMITH','CLERK',7566,'1980-12-17',800,NULL,20);
insert into EMP values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
insert into EMP values(7521,'WARD','SALESMAN',7698,'1981-02-21',1250,500,30);
insert into EMP values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
insert into EMP values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
insert into EMP values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
insert into EMP values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
insert into EMP values(7788,'SCOTT','ANALYST',7566,'1987-06-13',3000,NULL,20);
insert into EMP values(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
insert into EMP values(7844,'TURN','SALESMAN',7698,'1981-09-08',1500,0,30);
insert into EMP values(7876,'ADMAS','CLERK',7788,'1987-06-13',1100,NULL,20);
insert into EMP values(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
insert into EMP values(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
insert into salgrade(grade,losal,hisal)
select 1,700,1200 union all
select 2,1201,1400 union all
select 3,1401,2000 union all
select 4,2001,3000 union all
select 5,3001,9999 ;
3. 将所有员工的工资上浮10%.然后查询员工姓名、薪水、补助。（emp.sal为工资，emp.comm为补助）
select sal from emp;
update emp set sal = sal+0.1*sal;
select ename,sal,comm from emp;
update emp set sal = sal/1.1;
4. 查看EMP表中部门号为10的员工的姓名，职位，参加工作时间，工资。
select ename,job,hiredate,sal from emp where deptno='10';
5. 查所有已有的职位，要求去除重复项。
select distinct job from emp;
6. 计算每个员工的年薪，并取列名为Salary of Year（emp.sal为员工的月薪），要求输出员工姓名，年薪。
select ename,sal*12 as '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) as '最高工资',min(sal) as '最低工资' from emp;
15. 显示所有员工的平均工资和总计工资。
select avg(sal) as '平均工资',sum(sal) as '总计工资' from emp;
16. 显示补助在员工中的发放比例、即有多少比例的员工有补助。
（此题需注意问题：1.select语句中进行除法如何保留小数点后数据。2.count函数如何处理null型数据。3.cast()语法）
select cast(cast(count(comm) as float) / cast(count(*) as float) as numeric(13,12)) from emp;
聚合查询
17. 显示每种职业的平均工资。
select job,avg(sal) as 'average' from emp group by job;
18. 显示每个部门每种岗位的平均工资和最高工资。
select deptno,job,avg(sal) as '平均工资',max(sal) as '最高工资' from emp group by deptno,job order by deptno;
19. 显示平均工资低于2500的部门号，平均工资及最高工资。
select deptno,avg(sal)as 'average',max(sal)as 'max' from emp group by deptno having avg(sal) < 2500;
20. 上一条语句以平均工资升序排序。
select deptno,avg(sal)as 'average',max(sal)as 'max' 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. 采用自然连接原理显示部门名以及相应的员工姓名。（Sql server不支持NATURAL JOIN语法。）
select dname,ename from emp,dept where emp.deptno=dept.deptno order by dname;
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');
select a.ename,b.job from emp a join emp b on a.empno=b.mgr and b.ename = 'scott';
select * from emp a join emp b on a.deptno=b.deptno where a.ename='scott' and b.job='manager';
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 join dept on emp.deptno=dept.deptno and dept.dname='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;
select ename,sal,job from emp where sal>2500 or job='manager'order by ename;
31. 显示工资高于2500且职位为MANAGER的员工的姓名，工资和职位（采用INTERSECT语法实现）。
select ename,sal,job from emp where sal>2500 and job='manager';
select ename,sal,job from emp where sal>2500 intersect select ename,sal,job from emp where job='manager';
32. 显示工资高于2500但职位不是MANAGER的员工的姓名，工资和职位（采用EXCEPT语法实现）。
select ename,sal,job from emp where sal>2500 except select ename,sal,job from emp where job='manager';
select * from dept; select * from emp; select * from salgrade;

0 0