经典SQL语句一:
- 创建表
create table BONUS
(
ENAME VARCHAR(10),
JOB VARCHAR(9),
SAL int,
COMM int
);
create table DEPT
(
DEPTNO int(2) not null,
DNAME VARCHAR(14),
LOC VARCHAR(13)
);
create table EMP
(
EMPNO int(4) not null,
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR int(4),
HIREDATE DATE,
SAL double,
COMM int(7),
DEPTNO int(2)
);
create table SALGRADE
(
GRADE int,
LOSAL int,
HISAL int
);
- 插入数据
insert into DEPT (DEPTNO, DNAME, LOC)
values (10, 'ACCOUNTING', 'NEW YORK');
insert into DEPT (DEPTNO, DNAME, LOC)
values (20, 'RESEARCH', 'DALLAS');
insert into DEPT (DEPTNO, DNAME, LOC)
values (30, 'SALES', 'CHICAGO');
insert into DEPT (DEPTNO, DNAME, LOC)
values (40, 'OPERATIONS', 'BOSTON');
commit;
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7369, 'SMITH', 'CLERK', 7902, '19800608', 800, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7499, 'ALLEN', 'SALESMAN', 7698, '19810605',1600, 300, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7521, 'WARD', 'SALESMAN', 7698, '19830608', 1250, 500, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7566, 'JONES', 'MANAGER', 7839, '19810608', 2975, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7654, 'MARTIN', 'SALESMAN', 7698, '19860608', 1250, 1400, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7698, 'BLAKE', 'MANAGER', 7839, '19880608', 2850, null, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7782, 'CLARK', 'MANAGER', 7839, '19890603', 2450, null, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7788, 'SCOTT', 'ANALYST', 7566, '19870602', 3000, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7839, 'KING', 'PRESIDENT', null, '19810618', 5000, null, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7844, 'TURNER', 'SALESMAN', 7698, '19820628', 1500, 0, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7876, 'ADAMS', 'CLERK', 7788, '19870628', 1100, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7900, 'JAMES', 'CLERK', 7698, '19810608', 950, null, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7902, 'FORD', 'ANALYST', 7566, '19820308', 3000, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7934, 'MILLER', 'CLERK', 7782, '19820316', 1300, null, 10);
commit;
insert into SALGRADE (GRADE, LOSAL, HISAL)
values (1, 700, 1200);
insert into SALGRADE (GRADE, LOSAL, HISAL)
values (2, 1201, 1400);
insert into SALGRADE (GRADE, LOSAL, HISAL)
values (3, 1401, 2000);
insert into SALGRADE (GRADE, LOSAL, HISAL)
values (4, 2001, 3000);
insert into SALGRADE (GRADE, LOSAL, HISAL)
values (5, 3001, 9999);
commit;
- 查询数据
--简单、限定、模糊、排序练习--
-------简单查询------
1、查询员工表中的员工姓名、员工工资;
SELECT ENAME,SAL FROM EMP;
2、查询部门表中的部门编号、部门名称;
SELECT DEPTNO,DNAME FROM DEPT;
3、查询工资等级表中的工资等级编号、此等级的最低工资;
SELECT GRADE,LOSAL FROM SALGRADE;
4、查询奖金表中的员工姓名和奖金;
SELECT ENAME,COMM FROM BONUS;
5、在员工表中的查询员工信息,按下面格式输出:
编号为XXXX的员工,姓名为ename,工资为sal
SELECT '编号为'||EMPNO ||'的员工,姓名为'||ENAME ||'工资为'||SAL FROM EMP;
6、查询员工的年薪加奖金的和
SELECT SAL*12+NVL(COMM,0)AS 年薪 FROM EMP;
7、查询工资大于1000并且小于3000的员工信息
SELECT * FROM EMP WHERE SAL>1000 AND SAL<3000;
8、查询工资大于等于1000并且小于3000的员工信息
SELECT * FROM EMP WHERE SAL>=1000 AND SAL<3000;
9、查询员工资金大于500的员工信息;
SELECT * FROM EMP WHERE COMM>500;
10、查询1980年和1981年入职的员工信息
SELECT * FROM EMP WHERE HIREDATE BETWEEN '1-1月-80' AND '31-12月-81';
11、查询编号不是7788的所有员工信息
SELECT * FROM EMP WHERE EMPNO <>7788;
12、查询部门是20中员工工资大于2000的员工信息
SELECT * FROM EMP WHERE DEPTNO=20 AND SAL>2000;
13、查询工资大于奖金的员工信息
SELECT * FROM EMP WHERE SAL>COMM;
14、查询没有奖金的员工信息
SELECT * FROM EMP WHERE COMM IS NULL;
15、查询没有奖金且工资低于1500的员工信息
SELECT * FROM EMP WHERE SAL>1500 AND COMM IS NULL;
16、查询部门30没有奖金且工资低于1500的员工信息
SELECT * FROM EMP WHERE DEPTNO =30 AND SAL <1500 AND COMM IS NULL;
17、查询职务是经理的所有员工信息
SELECT * FROM EMP WHERE JOB='MANAGER';
18、查询工作是经理、普通员工、销售员的所有员工信息
SELECT * FROM EMP WHERE JOB IN ('MANAGER','CLERK','SALESMAN');
-------使用LIKE模糊查询------
19、查询员工编号大于7000且姓名中包含“S”字符的员工信息
SELECT * FROM EMP WHERE EMPNO>7000 AND ENAME LIKE '%S%';
20、查询员工编号大于7000且姓名中不包含“S”字符的员工信息
SELECT * FROM EMP WHERE EMPNO >7000 AND ENAME NOT LIKE '%S%';
21、查询员工职务是以“S”开头的员工信息
SELECT * FROM EMP WHERE JOB LIKE 'S%';
22、查询员工职务是以“R”结尾的员工信息
SELECT * FROM EMP WHERE JOB LIKE '%R';
-------使用WHERE和ORDER BY子句限定排序------
23、查询员工信息按编号降序排序
SELECT * FROM EMP ORDER BY EMPNO DESC;
24、查询员工信息按部门升序,部门相同按工号降序排序
SELECT * FROM EMP ORDER BY DEPTNO ASC,EMPNO DESC;
25、查询员工信息按工资与奖金的和的升序排序
SELECT EMP.*, SAL+nvl(COMM,0) 薪金和 FROM EMP ORDER BY 2;
26、查询员工信息按员工姓名降序排序,如果相同,按编号升序排序
SELECT * FROM EMP ORDER BY ENAME DESC,EMPNO ASC;
27. 创建一个查询显示工资大于2850美元的雇员的姓名及工资
SELECT ENAME,SAL FROM EMP WHERE SAL>2850;
28. 显示工资不在1500到2850美元之间的雇员的姓名及工资
SELECT ENAME,SAL FROM EMP WHERE SAL NOT BETWEEN 1500 AND 2850;
29. 显示在10和30部门工作并且工资大于1500美元的雇员的姓名和工资,列标题显示为Employee和Monthly Salary
SELECT ENAME EMPLOYEE ,SAL "MONTHLY SALARY" FROM EMP WHERE DEPTNO IN(10,30)AND SAL>1500;
30. 显示奖金比工资多10%以上的雇员的姓名、工资及奖金。
SELECT ENAME,SAL ,COMM FROM EMP WHERE COMM>SAL*1.1;
-------思考练习题------
31. 创建一个查询显示雇员编号为7566的雇员的姓名和部门编号。
SELECT ENAME,DEPTNO FROM EMP WHERE EMPNO=7566;
32. 显示受雇时间在February 20,1981和May 1,1981之间的雇员的姓名、工资、及受雇时间,并以受雇时间升序排列。
SELECT ENAME,SAL,HIREDATE FROM EMP WHERE HIREDATE BETWEEN '20-2月-81' AND '1-3月-81' ORDER BY HIREDATE;
33. 显示在10号和30号部门工作的雇员的姓名及其部门编号,并以字母顺序排列。
SELECT ENAME,DEPTNO FROM EMP WHERE DEPTNO IN(10,30) ORDER BY ENAME;
34. 显示所有受雇于1982年的雇员的姓名和受雇时间。
SELECT ENAME,HIREDATE FROM EMP WHERE TO_CHAR(HIREDATE,'YYYY')='1982';
35. 显示没有上级管理员的雇员的姓名及其工作。
SELECT ENAME,JOB FROM EMP WHERE MGR IS NULL;
36. 显示能挣得奖金的雇员的姓名、工资、奖金,并以工资和奖金降序排列。
SELECT ENAME,SAL,COMM FROM EMP WHERE COMM IS NOT NULL ORDER BY(COMM+SAL);
37. 显示姓名中第三个字母为A的雇员的姓名。
SELECT ENAME FROM EMP WHERE ENAME LIKE '__A%';
38. 显示姓名中两次出现字母L并且在30部门工作或者其管理员编号是7782的雇员的姓名。
SELECT ENAME FROM EMP WHERE ENAME LIKE '%L%L%' AND DEPTNO =30 OR MGR =7782;
39. 显示工作为Clerk或Analyst并且工资不等于$1000、$3000、$5000的雇员的姓名、工作及工资。
SELECT ENAME,JOB ,SAL FROM EMP WHERE JOB IN ('CLERK','ANALYST') AND SAL NOT IN(1000,3000,5000);