练习所用数据表
• 部门表
CREATE TABLE DEPT(
DEPTNO INT PRIMARY KEY, – 部门编号
DNAME VARCHAR(14) , – 部门名称
LOC VARCHAR(13) – 部门地址
) ;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’);• 员工表
CREATE TABLE EMP
(
EMPNO INT PRIMARY KEY, – 员工编号
ENAME VARCHAR(10), – 员工名称
JOB VARCHAR(9), – 工作
MGR DOUBLE, – 直属领导编号
HIREDATE DATE, – 入职时间
SAL DOUBLE, – 工资
COMM DOUBLE, – 奖金
DEPTNO INT, – 部门号
FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO));
INSERT INTO EMP VALUES
(7369,‘SMITH’,‘CLERK’,7902,‘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-22’,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-07-13’,3000,NULL,20);
INSERT INTO EMP VALUES
(7839,‘KING’,‘PRESIDENT’,NULL,‘1981-11-17’,5000,NULL,10);
INSERT INTO EMP VALUES
(7844,‘TURNER’,‘SALESMAN’,7698,‘1981-09-08’,1500,0,30);
INSERT INTO EMP VALUES
(7876,‘ADAMS’,‘CLERK’,7788,‘1987-07-13’,1100,NULL,20);
INSERT INTO EMP VALUES
(7900,‘JAMES’,‘CLERK’,7698,‘1981-12-03’,950,NULL,30);
INSERT INTO EMP VALUES
(7902,‘FORD’,‘ANALYST’,7566,‘1981-12-03’,3000,NULL,20);
INSERT INTO EMP VALUES
(7934,‘MILLER’,‘CLERK’,7782,‘1982-01-23’,1300,NULL,10);• 工资等级表
CREATE TABLE SALGRADE
( GRADE INT, – 工资等级
LOSAL DOUBLE, – 最低工资
HISAL DOUBLE ); – 最高工资
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);习题&答案
使用emp表进行查询
- 查询出部门编号为30的员工
SELECT * FROM emp WHERE DEPTNO =30;- 查询所有销售员的姓名、编号、部门编号
SELECT ENAME, EMPNO, DEPTNO FROM emp WHERE JOB='SALESMAN';- 找出奖金高于工资的员工
SELECT * FROM emp WHERE COMM>SAL;- 找出奖金高于工资60%的员工
SELECT * FROM emp WHERE COMM>SAL*0.6;- 找出部门编号为10的所有经理， 和部门编号为20的所有销售员的详细资料
SELECT * FROM emp WHERE (DEPTNO=10 AND JOB='MANAGER') OR (DEPTNO=20 and JOB='SALESMAN');- 找出部门编号为10的所有经理， 和部门编号为20的所有销售员,还有既不是经理也不是销售员但工资大于或等于2000的所有员工的详细资料
SELECT * FROM emp WHERE (DEPTNO=10 AND JOB='MANAGER') OR (DEPTNO=20 AND JOB='SALESMAN') OR (NOT JOB IN('MANAGER', 'SALESMAN')AND SAL>2000);- 无奖金或奖金低于100的员工
SELECT * FROM emp WHERE COMM<100 OR COMM IS NULL;- 查询名字为五个字符的员工
SELECT * FROM emp WHERE ENAME LIKE '_____';- 查询1981年入职的员工
SELECT * FROM emp WHERE HIREDATE LIKE '1981%';- 查询所有员工的详细信息，使用编号升序排列
SELECT * FROM EMP ORDER BY EMPNO ASC;- 查询所有员工的详细信息，用工资降序排序， 如果工资相同则使用入职日期升序排序
SELECT * FROM emp ORDER BY SAL DESC,HIREDATE ASC;- 查询每个部门的平均工资
SELECT DEPTNO,AVG(SAL) FROM emp GROUP BY DEPTNO;- 查询每个部门的员工数量
SELECT DEPTNO, COUNT(*) FROM emp GROUP BY DEPTNO;- 查询每种工作的最高工资、最低工资、人数,并按照人数升序排列，若人数相同则按照最低工资降序排列
SELECT JOB, MAX(SAL),MIN(SAL), COUNT(SAL) FROM emp GROUP BY JOB ORDER BY COUNT(SAL), MIN(SAL) DESC;
