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