SELECT a.*
FROM a03 a
,(SELECT yufen,MAX(money) Debit101ccur FROM a03 WHERE kumu=101 GROUP BY yufen) b
WHERE a.yufen=b.yufen AND a.money>b.Debit101ccur
SELECT a.*
FROM a03 a
,(SELECT yufen,MAX(money) Debit101ccur FROM a03 WHERE kumu=101 GROUP BY yufen) b
WHERE a.yufen=b.yufen AND a.money>b.Debit101ccur
CREATE TABLE dept(id INT PRIMARY KEY,bname VARCHAR(20),addr VARCHAR(30));
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 (
eid INT PRIMARY KEY ,
ename VARCHAR(30),
gangwei VARCHAR(30),
boosid INT ,
ruzhi DATE ,
money DOUBLE,
jiangjin DOUBLE ,
id INT,
FOREIGN KEY(id) REFERENCES dept(id));
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);
CREATE TABLE SALGRADE(dengji INT , zuidi DOUBLE ,zuigao 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);
//1.查找部门30中员工的详细信息。
SELECT * FROM emp WHERE id=30;
//2.找出从事clerk工作的员工的编号、姓名、部门号。
SELECT eid,ename,id FROM emp WHERE gangwei='clerk';
//3.检索出奖金多于基本工资的员工信息。
SELECT * FROM emp WHERE jiangjin>money;
//4.检索出奖金多于基本工资的60%的员工信息。
SELECT * FROM emp WHERE jiangjin>(money*0.6);
//5.找出10部门的经理、20部门的职员 的员工信息。
SELECT * FROM emp WHERE id=20 AND gangwei='clerk' OR id=10 AND gangwei='MANAGER';
//6.找出10部门的经理、20部门的职员 或者既不是经理也不是职员但是工资高于2000元的员工信息。
SELECT * FROM emp WHERE (id=20 AND gangwei='clerk' )
OR
( id=10 AND gangwei='MANAGER' )
OR
(id=10 AND gangwei!='clerk' AND gangwei!='MANAGER' AND money>2000)
OR
(id=20 AND gangwei!='clerk' AND gangwei!='MANAGER' AND money>2000);
//7.找出获得奖金的员工的工作。
SELECT * FROM emp WHERE jiangjin!=0;
//8.找出奖金少于100或者没有获得奖金的员工的信息。
SELECT * FROM emp WHERE jiangjin<100 OR jiangjin IS NULL;
//9.找出姓名以A、B、S开始的员工信息。
SELECT * FROM emp WHERE ename LIKE 'a%' OR ename LIKE 'b%' OR ename LIKE 's%';
//10.找到名字长度为6个字符的员工信息。
SELECT * FROM emp WHERE ename LIKE'______';
//11.名字中不包含R字符的员工信息。
SELECT * FROM emp WHERE ename NOT IN(SELECT ename FROM emp WHERE ename LIKE '%r%');
//12.返回员工的详细信息并按姓名排序。
SELECT * FROM emp ORDER BY ename;
//返回员工的信息并按工作降序工资升序排列
SELECT * FROM emp ORDER BY gangwei DESC,money ASC;
//14.计算员工的日薪(按30天)。
SELECT ename,TRUNCATE(money/30 ,2)FROM emp;
//15.找出姓名中包含A的员工信息。
SELECT * FROM emp WHERE ename LIKE '%a%';
//多表
//1.返回拥有员工的部门名、部门号。
SELECT DISTINCT dept.id,dept.bname FROM dept,emp WHERE dept.id=emp.id
//2.工资水平多于smith的员工信息。
SELECT * FROM emp WHERE money>(SELECT money FROM emp WHERE ename='smith');
//3.返回员工和所属经理的姓名。
SELECT emp.ename,a.ename FROM emp,(SELECT * FROM emp WHERE gangwei='MANAGER')a WHERE emp.id=a.id AND emp.eid!=a.eid;
//4.返回雇员的雇佣日期早于其经理雇佣日期的员工及其经理姓名
SELECT emp.ename,a.ename FROM emp,(SELECT * FROM emp WHERE gangwei='manager')a
WHERE emp.ruzhi>a.ruzhi AND emp.eid!=a.eid AND emp.id=a.id;
//5.返回员工姓名及其所在的部门名称。
SELECT emp.ename,a.bname FROM emp,dept a WHERE emp.id=a.id
//6.返回从事clerk工作的员工姓名和所在部门名称。
SELECT emp.ename,a.bname FROM emp,dept a WHERE emp.id=a.id AND emp.gangwei='clerk'
//7.返回部门号及其本部门的最低工资。
SELECT a.id,MIN(money) FROM (SELECT dept.id,emp.money FROM dept,emp WHERE dept.id=emp.id)a GROUP BY a.id
//8.返回销售部(sales)所有员工的姓名。
SELECT emp.ename FROM emp,dept WHERE emp.id=dept.id AND dept.bname='sales';
//9.返回工资水平多于平均工资的员工。
SELECT * FROM emp WHERE money>(SELECT AVG(money) FROM emp);
//10.返回与SCOTT从事相同工作的员工。
SELECT * FROM emp WHERE gangwei=(SELECT gangwei FROM emp WHERE ename='scott') AND ename!='scott';
//11.返回与30部门员工工资水平相同的员工姓名与工资。
//SELECT emp.* FROM emp,(SELECT money FROM emp WHERE id=30)a WHERE emp.money=a.money AND emp.id!=30;
SELECT b.* FROM
(SELECT * FROM emp GROUP BY money)b,
(SELECT MAX(money)da,MIN(money)xiao FROM emp WHERE id=30)a
WHERE b.money>a.xiao AND b.money<a.da;
//12.返回工资高于30部门所有员工工资水平的员工信息。
SELECT * FROM emp WHERE money>(SELECT MAX(money) FROM emp WHERE id=30)
//13.返回部门号、部门名、部门所在位置及其每个部门的员工总数。
SELECT dept.*,a.count FROM dept,(SELECT id ,COUNT(*) COUNT FROM emp GROUP BY id)a WHERE dept.id=a.id;
//14.返回员工的姓名、所在部门名及其工资。
SELECT emp.ename,dept.bname,emp.money FROM dept,emp WHERE dept.id=emp.id
//15.返回员工的详细信息。(包括部门名)
SELECT emp.*,dept.bname FROM dept,emp WHERE dept.id=emp.id
//16.返回员工工作及其从事此工作的最低工资。
SELECT gangwei ,MIN(money) FROM emp GROUP BY gangwei
//17.计算出员工的年薪，并且以年薪排序。
SELECT ename,money*12 nianmoney FROM emp ORDER BY nianmoney
//18.返回工资处于第四级别的员工的姓名。
SELECT ename,money FROM emp ,(SELECT * FROM salgrade WHERE dengji=4)a
WHERE emp.money>=a.zuidi AND emp.money<=a.zuigao
//19.返回工资为二等级的职员名字、部门所在地、和二等级的最低工资和最高工资
SELECT emp.ename,dept.bname,emp.money,a.zuidi,a.zuigao FROM emp,dept,(SELECT * FROM salgrade WHERE dengji=2)a
WHERE emp.id=dept.id AND emp.money>a.zuidi AND emp.money<a.zuigao
//20.工资等级多于smith的员工信息。
SELECT a.* FROM
(SELECT emp.*,salgrade.dengji FROM emp,salgrade WHERE emp.money>=salgrade.zuidi AND emp.money<=salgrade.zuigao )a,
(SELECT salgrade.* FROM emp,salgrade WHERE emp.money>=salgrade.zuidi AND emp.money<=salgrade.zuigao AND emp.ename='smith')b
WHERE a.dengji>b.dengji;
|
|
收藏
淘帖
踩
