Mysql数据库测试练习题1215含答案.docx

1、Mysql数据库测试练习题1215含答案Mysql数据库测试练习题1. 数据库结构和数据DROPDATABASEIFEXISTStest1;CREATEDATABASEtest1;USEtest1;#部门表#DROPIFEXISTSTABLEDEPT;CREATETABLEDEPT(DEPTNOintPRIMARYKEY, #部门编号DNAMEVARCHAR(14) , #部门名称LOCVARCHAR(13) #部门地址) ;INSERTINTODEPTVALUES(10,ACCOUNTING,NEW YORK);INSERTINTODEPTVALUES(20,RESEARCH,DALLAS)

2、;INSERTINTODEPTVALUES(30,SALES,CHICAGO);INSERTINTODEPTVALUES(40,OPERATIONS,BOSTON);#员工表#DROPIFEXISTSTABLEEMP;CREATETABLEEMP(EMPNOintPRIMARYKEY, #员工编号ENAMEVARCHAR(10), #员工姓名JOBVARCHAR(9), #员工工作MGRint, #员工直属领导编号HIREDATEDATE, #入职时间SALdouble, #工资COMMdouble, #奖金DEPTNOint #对应dept表的外键);# 添加 部门 和 员工 之间的主外键关

3、系ALTERTABLEEMPADDCONSTRAINTFOREIGNKEYEMP(DEPTNO)REFERENCESDEPT (DEPTNO);INSERTINTOEMPVALUES(7369,SMITH,CLERK,7902,1980-12-17,800,NULL,20);INSERTINTOEMPVALUES(7499,ALLEN,SALESMAN,7698,1981-02-20,1600,300,30);INSERTINTOEMPVALUES(7521,WARD,SALESMAN,7698,1981-02-22,1250,500,30);INSERTINTOEMPVALUES(7566,

4、JONES,MANAGER,7839,1981-04-02,2975,NULL,20);INSERTINTOEMPVALUES(7654,MARTIN,SALESMAN,7698,1981-09-28,1250,1400,30);INSERTINTOEMPVALUES(7698,BLAKE,MANAGER,7839,1981-05-01,2850,NULL,30);INSERTINTOEMPVALUES(7782,CLARK,MANAGER,7839,1981-06-09,2450,NULL,10);INSERTINTOEMPVALUES(7788,SCOTT,ANALYST,7566,198

5、7-07-03,3000,NULL,20);INSERTINTOEMPVALUES(7839,KING,PRESIDENT,NULL,1981-11-17,5000,NULL,10);INSERTINTOEMPVALUES(7844,TURNER,SALESMAN,7698,1981-09-08,1500,0,30);INSERTINTOEMPVALUES(7876,ADAMS,CLERK,7788,1987-07-13,1100,NULL,20);INSERTINTOEMPVALUES(7900,JAMES,CLERK,7698,1981-12-03,950,NULL,30);INSERTI

6、NTOEMPVALUES(7902,FORD,ANALYST,7566,1981-12-03,3000,NULL,20);INSERTINTOEMPVALUES(7934,MILLER,CLERK,7782,1981-01-23,1300,NULL,10);#工资等级表#DROPIFEXISTSTABLESALGRADE;CREATETABLESALGRADE(GRADEint, #等级LOSALdouble, #最低工资HISALdouble); #最高工资INSERTINTOSALGRADEVALUES(1,700,1200);INSERTINTOSALGRADEVALUES(2,1201

7、,1400);INSERTINTOSALGRADEVALUES(3,1401,2000);INSERTINTOSALGRADEVALUES(4,2001,3000);INSERTINTOSALGRADEVALUES(5,3001,9999);2. SQL查询设计（1）单表查询#1、查找部门30中员工的详细信息。select*fromempwheredeptno =30;#2、找出从事clerk工作的员工的编号、姓名、部门号。selectempno,ename,deptnofromempwherejob =clerk;#3、检索出奖金多于基本工资的员工信息。select*fromempwhere

8、comm sal;#4、检索出奖金多于基本工资60%的员工信息。select*fromempwherecomm sal *0.6;#5、找出10部门的经理、20部门的职员 的员工信息。select*fromempwheredeptno =10andjob=MANAGERordeptno =20andjob =CLERK;#6、找出10部门的经理、20部门的职员 或者既不是经理也不是职员但是工资高于2000元的员工信息。select*fromempwheredeptno =10andjob=MANAGERordeptno =20andjob =CLERKorjob!=MANAGERandjob

9、!=CLERKandsal 2000;# job not in (MANAGER,CLERK)#7、找出获得奖金的员工的工作。select*fromempwherecomm 0;#8、找出奖金少于100或者没有获得奖金的员工的信息。select*fromempwherecomm (selectsalfromempwhereename =smith);#3、返回员工和所属经理的姓名。selecte.ename,m.enamefromemp e leftouterjoinemp mone.mgr = m.empno;selecte.ename ,(selectm.enamefromemp mwhe

10、rem.empno = e.mgr) enamefromemp e;selecte.ename , m.enamefromemp e , emp mwheree.mgr = m.empno;selecte.ename,m.enamefromemp einnerjoinemp mone.mgr = m.empno;#4、返回雇员的雇佣日期早于其经理雇佣日期的员工及其经理姓名。selecte.ename,m.enamefromemp einnerjoinemp mone.mgr = m.empno wheree.hiredate m.hiredate;selecte.ename,m.enamefr

11、omemp e,emp mwheree.mgr=m.empnoande.hiredate (selectavg(sal)fromemp);#10、返回与SCOTT从事相同工作的员工。select*fromempwherejob = (selectjobfromempwhereename =scott);selecte1.*fromemp e1 , (selectempno,jobfromempwhereename =scott) e2wheree1.job = e2.jobande1.empno != e2.empno;#11、返回与30部门员工工资水平相同的员工姓名与工资。selectena

12、me,salfromempwheresalin(selectsalfromempwheredeptno =30);#12、返回工资高于30部门所有员工工资水平的员工信息。select*fromempwheresal all(selectsalfromempwheredeptno =30);select*fromempwheresal (selectmax(sal)fromempwheredeptno =30);#13、返回部门号、部门名、部门所在位置及其每个部门的员工总数。selectdept.deptno,dept.dname,dept.loc,count(emp.deptno)number

13、fromdept,empwheredept.deptno = emp.deptnogroupbyemp.deptno;#14、返回员工的姓名、所在部门名及其工资。selectename,dname,salfromemp ,deptwhereemp.deptno = dept.deptno;#15、返回员工的详细信息。(包括部门名)selecte.* , d.dnamefromemp e, dept dwheree.deptno = d.deptno;#16、返回员工工作及其从事此工作的最低工资。selectjob ,min(sal) salfromempgroupbyjob#17、计算出员工的

14、年薪，并且以年薪排序。selectename, sal *12asySalaryfromemporderbyySalary;#18、返回工资处于第四级别的员工的姓名。selectename,salfromemp e ,salgrade swheree.sal = s.losalande.sal = losalandsal hisal;#20.工资等级多于smith的员工信息。selectgradefromsalgrade s ,emp ewheres.losal e.salande.ename =smith;selecte.*fromemp e, salgrade swheres.hisal e.salands.grade =1;selecte.*fromemp e, salgrade swheres.hisal e.salands.grade = (selectgradefromsalgrade s ,emp ewheres.losal e.salande.ename =smith);