mysql练习题

Mysql 60道练习题：

我的mysql版本为

 

创建数据库：create database oa;

使用数据库：use oa;

创建数据库表：

CREATE TABLE dept(

deptno INT PRIMARY KEY,

dname VARCHAR(20),

loc VARCHAR(20)

)

CREATE TABLE emp(

empno INT PRIMARY KEY,

ename VARCHAR(20) NOT NULL,

job VARCHAR(20) CHECK (job IN ('CLERK','SALESMAN','MANAGER','SALESMAN','ANALYST')),

mgp INT ,

hiredate DATETIME ,

sal DECIMAL(10,2),

comm DECIMAL(10,2),

deptno int

)

alter table emp add constraint emp_deptno foreign key (deptno) references emp(deptno);

 

插入数据：

INSERT INTO dept VALUES (10,'ACCOUNTING','NEWTORK')

INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');

INSERT INTO dept VALUES (30,'SALES','CHICAGO');

INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');

 

insert into emp values(7369,'SMITH','CLERK',7902,'1980-12-17',1640,NULL,20);

insert into emp values(7499,'ALLEN','SALESMAN',7698,'1981-2-20',11400,300,30);

insert into emp values(7521,'WARD','SALESMAN',7698,'1981-2-22',5200,500,30);

insert into emp values(7566,'JOENS','MANAGER',7839,'1981-4-2',7015,NULL,20);

insert into emp values(7654,'MARTIN','SALESMAN',7698,'1981-9-28',5200,1400,30);

insert into emp values(7698,'BLAKE','MANAGER',7839,'1981-5-1',5900,NULL,30);

insert into emp values(7782,'CLARK','MANAGER',7839,'1981-6-9',2470,NULL,10);

insert into emp values(7788,'SCOTT','ANALYST',7566,'1987-4-19',3040,NULL,20);

insert into emp values(7844,'TURNER','SALESMAN',7698,'1980-12-17',6200,0,30);

insert into emp values(7876,'ADAMS','CLERK',7788,'1981-9-8',2240,NULL,20);

insert into emp values(7900,'JAMES','CLERK',7698,'1987-5-23',4000,NULL,30);

insert into emp values(7902,'FORD','ANALYST',7566,'1981-12-3',3040,NULL,20);

insert into emp values(7934,'MILLER','CLERK',7782,'1982-12-3',2620,NULL,10);

 

1、查询部门号为20的员工信息

select * from emp where deptno=20;

 

2、查询所有工种为clerk的员工的员工号、员工名、部门号

select empno ,ename ,deptno from emp where job=’clerk’;

 

3、查询奖金comm高于工资sal的员工的信息

select * from emp where  comm is not null and comm>sal;

4、查询奖金高于工资20%的员工的信息

select * from emp where comm>sal*0.2;

 

5、查询10号部门中工种为manager和20号部门工种为clerk的员工信息

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

 

6、查询所有工种不是manager和clerk且工资大于或等于2000的员工的详细信息

select * from emp where sal>=2000 and job not in(‘’manager,’clerk’);

 

7、查询有奖金的员工的不同工种

select distinct job from emp where comm is not null;