+--------+------------+----------+
| deptno | dname | loc |
+--------+------------+----------+ dept表
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+

+-------+--------+-----------+------+------------+------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+-----------+------+------------+------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-07-03 | 3000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1981-01-23 | 1300 | NULL | 10 |
+-------+--------+-----------+------+------------+------+------+--------+

--部門表
CREATE TABLE dept(
deptno int PRIMARY KEY,
dname VARCHAR(14) , --部門名稱
loc VARCHAR(13) ---部門地址
) ;
--員工表
CREATE TABLE emp(
empno int PRIMARY KEY, --員工編號(hào)
ename VARCHAR(10), ---員工姓名
job VARCHAR(9), --員工工作
mgr int, ----員工直屬領(lǐng)導(dǎo)編號(hào)

hiredate DATE, ----入職時(shí)間
sal double, ---工資
comm double, --獎(jiǎng)金
deptno int REFERENCES dept); --關(guān)聯(lián)dept表

1、查找部門30中員工的詳細(xì)信息。

select * from dept d, emp e where d.deptno=e.deptno and d.deptno=30;

答案都沒(méi)我寫的這個(gè)難

2、找出從事clerk工作的員工的編號(hào)、姓名、部門號(hào)。

select deptno,empno,ename from emp where job ='CLERK';

3、檢索出獎(jiǎng)金多于基本工資的員工信息。

select * from emp where ifnull(comm,0)>sal;

4、檢索出獎(jiǎng)金多于基本工資60%的員工信息。

select * from emp where ifnull(comm,0)>(sal*0.6);   

5、找出10部門的經(jīng)理、20部門的職員 的員工信息。

select *from emp where deptno=10 and job='MANAGER'or deptno=20 and job='CLERK';

6、找出10部門的經(jīng)理、20部門的職員 或者既不是經(jīng)理也不是職員但是工資高于2000元的員工信息。

select * from emp where (deptno=10 and job='MANAGER') or  ( deptno=20 and job='CLERK') or  ( job not in ('MANAGER','CLERK') and sal>2000);

select * from emp where (deptno = 10 and job='MANAGER') or (deptno=20 and job='CLERK') or (job!='MANAGER' and job!='CLERK' and sal>2000);


select * from emp where job!='MANAGER' and job!='CLERK' and sal>2000;

7、找出獲得獎(jiǎng)金的員工的工作.

select ename,job from emp where not comm is null && comm >0;    

8、找出獎(jiǎng)金少于100或者沒(méi)有獲得獎(jiǎng)金的員工的信息。

select * from emp where comm is null or comm<100;

9、找出姓名以A、B、S開(kāi)始的員工信息。

select * from emp where ename like 'A%' or ename like 'B%' or ename like 'S%';

10、找到名字長(zhǎng)度為7個(gè)字符的員工信息。

select * from emp where ename like '_______';

11、名字中不包含R字符的員工信息。

select * from emp where ename not like '%R%';

12、返回員工的詳細(xì)信息并按姓名排序。

select * from emp group by ename;

13、返回員工的信息并按工作降序工資升序排列。

select * from emp order by job desc,sal asc;

14、計(jì)算員工的日薪(按30天)。

select sal/30 rx from emp;  

15、找出姓名中包含A的員工信息。

select * from emp where ename  like '%A%';