CREATE TABLE class(
empno INT, ename VARCHAR(4), job VARCHAR(4), mgr INT, hiredate DATETIME, sal NUMERIC(10,2), COMM NUMERIC(10,2), deptno INT)SELECT * FROM class;INSERT INTO class VALUE (1001,'甘宁','文员',1013,20001217,8000.00,NULL,20);INSERT INTO class VALUE (1002,'黛琦丝','销售员',1006,20010220,16000.00,3000.00,30);INSERT INTO class VALUE (1003,'殷天正','销售员',1006,20010222,12500.00,5000.00,30);INSERT INTO class VALUES (1004,'刘备','经理',1009,20010402,29750.00,NULL,20);INSERT INTO class VALUES (1005,'谢逊','销售员',1006,20010928,12500.00,14000.00,30);INSERT INTO class VALUES (1006,'关羽','经理',1009,20010501,28500.00,NULL,30);INSERT INTO class VALUES (1007,'张飞','经理',1009,20010901,24500.00,NULL,10);INSERT INTO class VALUES (1008,'诸葛亮','分析师',1004,20070419,30000.00,NULL,20);INSERT INTO class VALUES (1009,'曾阿牛','董事长',NULL,20011117,50000.00,NULL,10);INSERT INTO class VALUES (1010,'韦一笑','销售员',1006,20010908,15000.00,0.00,30);INSERT INTO class VALUES (1001,'周泰','文员',1008,20070523,11000.00,NULL,20);INSERT INTO class VALUES (1012,'程普','文员',1006,20011203,9500.00,NULL,30);INSERT INTO class VALUES (1013,'庞统','分析师',1004,20011203,30000.00,NULL,20);INSERT INTO class VALUES (1014,'黄盖','文员',1007,20020123,13000.00,NULL,10);INSERT INTO class VALUES (1015,'张三','保洁员',1001,20130501,80000.00,50000.00,50);/*编号为30的员工*/SELECT * FROM class WHERE deptno=30;/*所有销售员的姓名、编号和部门编号。*/SELECT ename,empno,deptno FROM class WHERE job='销售员' /*找出奖金高于工资的员工。*/SELECT * FROM class WHERE COMM>sal; /*找出奖金高于工资的60%的员工。*/SELECT * FROM class WHERE COMM>sal*0.6; /*找出部门编号为10中所有经理，和部门编号为20中所有销售员的详细资料。*/SELECT * FROM class WHERE (deptno=10 AND job='经理')OR(deptno=20 AND job='销售员'); /*找出部门编号为10中所有经理，部门编号为20中所有销售员， 还有即不是经理又不是销售员但其工资大或等于20000的所有员工详细资料。*/SELECT * FROM class WHERE (deptno=10 AND job='经理')OR(deptno=20 AND job='销售员')OR (sal>=20000);/*无奖金或奖金低于1000的员工。*/SELECT * FROM class WHERE (COMM IS NULL)OR(COMM<1000); /*查询名字由三个字组成的员工。*/SELECT * FROM class WHERE ename LIKE '___'/*查询2000年入职的员工。*/SELECT * FROM class WHERE hiredate LIKE '2000%'; SELECT * FROM class WHERE YEAR(hiredate)=2000