mysql练习题2

建表
– 创建部门表
DROP TABLE IF EXISTS dept;
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’);

– 创建员工表
DROP TABLE IF EXISTS emp;
CREATE TABLE emp(
– 员工编号
empno int PRIMARY KEY,
– 员工姓名
ename VARCHAR(10),
– 工作岗位
job VARCHAR(9),
– 直属领导
mgr int,
– 入职时间
hiredate DATE,
– 工资
sal double,
– 奖金
comm double,
– 所属部门
deptno int
);

– 为员工表表添加外键约束
ALTER TABLE emp ADD CONSTRAINT FOREIGN KEY EMP(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-03’,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,‘1981-01-23’,1300,NULL,10);

– 创建工资等级表
DROP TABLE IF EXISTS salgrade;
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);

– 01、查询部门编号为30的部门的员工详细信息

SELECT * from emp where deptno = '30';

– 02、查询从事clerk工作的员工的编号、姓名以及其部门号

SELECT empno,ename,deptno from emp where job = 'clerk';

– 03、查询奖金多于基本工资的员工的信息

select * from emp where comm>sal;

– 04、查询奖金多于基本工资60%的员工的信息

select * from emp where comm>sal*0.6;

– 05、查询部门编号为10的部门经理 (job=manager) 和部门编号为20的部门中工作为CLERK的职员信息

select * from emp 
where (job='manager' and deptno=10) or (deptno=20 and job='clerk');

– 06、查询部门编号为10的部门经理或部门编号为20的部门工作为CLERK的职员信息或者既不是经理也不是CLERK但是工资高于2000的员工信息

select * from emp where (deptno=10 and job='manager') or (deptno=20 and job='clerk') 
or