员工部门表综合查询60题答案.docVIP

员工部门表综合查询60题答案 /eva%B0%D9%B7%D6%B0%D9/blog/item/1dd7b5d3e5ea419fa1ec9c8a.html 员工部门表综合查询60题(上) 2010-11-24 06:04员工部门表综合查询60题(上) CREATE DATABASE oa; USE oa; CREATE TABLE dept( deptno INT PRIMARY KEY, dname VARCHAR(20), loc VARCHAR(20) ) DROP TABLE emp 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 FOREIGN KEY REFERENCES dept(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); SELECT * FROM emp; select * from dept; 员工部门表综合查询60题 （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 isnull(comm,0)sal （4） 查询奖金高于工资的20%的员工信息。 select * from emp where isnull(comm,0)sal*0.2 （5） 查询10号部门中工种为MANAGER和