SQL笔试经.docx
《SQL笔试经.docx》由会员分享,可在线阅读,更多相关《SQL笔试经.docx(23页珍藏版)》请在冰豆网上搜索。
SQL笔试经
/*
建表:
dept:
deptno(primarykey),dname,loc
emp:
empno(primarykey),ename,job,mgr,sal,deptno
*/
1列出emp表中各部门的部门号,最高工资,最低工资
selectmax(sal)as最高工资,min(sal)as最低工资,deptnofromempgroupbydeptno;
2列出emp表中各部门job为'CLERK'的员工的最低工资,最高工资
selectmax(sal)as最高工资,min(sal)as最低工资,deptnoas部门号fromempwherejob='CLERK'groupbydeptno;
3对于emp中最低工资小于1000的部门,列出job为'CLERK'的员工的部门号,最低工资,最高工资
selectmax(sal)as最高工资,min(sal)as最低工资,deptnoas部门号fromempasb
wherejob='CLERK'and1000>(selectmin(sal)fromempasawherea.deptno=b.deptno)groupbyb.deptno
4根据部门号由高而低,工资有低而高列出每个员工的姓名,部门号,工资
selectdeptnoas部门号,enameas姓名,salas工资fromemporderbydeptnodesc,salasc
5写出对上题的另一解决方法
(请补充)
6列出'张三'所在部门中每个员工的姓名与部门号
selectename,deptnofromempwheredeptno=(selectdeptnofromempwhereename='张三')
7列出每个员工的姓名,工作,部门号,部门名
selectename,job,emp.deptno,dept.dnamefromemp,deptwhereemp.deptno=dept.deptno
8列出emp中工作为'CLERK'的员工的姓名,工作,部门号,部门名
selectename,job,dept.deptno,dnamefromemp,deptwheredept.deptno=emp.deptnoandjob='CLERK'
9对于emp中有管理者的员工,列出姓名,管理者姓名(管理者外键为mgr)
selecta.enameas姓名,b.enameas管理者fromempasa,empasbwherea.mgrisnotnullanda.mgr=b.empno
10对于dept表中,列出所有部门名,部门号,同时列出各部门工作为'CLERK'的员工名与工作
selectdnameas部门名,dept.deptnoas部门号,enameas员工名,jobas工作fromdept,emp
wheredept.deptno*=emp.deptnoandjob='CLERK'
11对于工资高于本部门平均水平的员工,列出部门号,姓名,工资,按部门号排序
selecta.deptnoas部门号,a.enameas姓名,a.salas工资fromempasa
wherea.sal>(selectavg(sal)fromempasbwherea.deptno=b.deptno)orderbya.deptno
12对于emp,列出各个部门中平均工资高于本部门平均水平的员工数和部门号,按部门号排序
selectcount(a.sal)as员工数,a.deptnoas部门号fromempasa
wherea.sal>(selectavg(sal)fromempasbwherea.deptno=b.deptno)groupbya.deptnoorderbya.deptno
13对于emp中工资高于本部门平均水平,人数多与1人的,列出部门号,人数,按部门号排序
selectcount(a.empno)as员工数,a.deptnoas部门号,avg(sal)as平均工资fromempasa
where(selectcount(c.empno)fromempascwherec.deptno=a.deptnoandc.sal>(selectavg(sal)fromempasbwherec.deptno=b.deptno))>1
groupbya.deptnoorderbya.deptno
14对于emp中低于自己工资至少5人的员工,列出其部门号,姓名,工资,以及工资少于自己的人数
selecta.deptno,a.ename,a.sal,(selectcount(b.ename)fromempasbwhereb.salwhere(selectcount(b.ename)fromempasbwhereb.sal5
--------------------------------------------------------
--sqlstructuredquerylanguage
--DML--DataManipulationLanguage--数据操作语言
queryinformation(SELECT),
addnewrows(INSERT),
modifyexistingrows(UPDATE),
deleteexistingrows(DELETE),
performaconditionalupdateorinsertoperation(MERGE),
seeanexecutionplanofSQL(EXPLAINPLAN),
andlockatabletorestrictaccess(LOCKTABLE).
--DDL--DataDefinitionLanguage--数据定义语言
create,modify,drop,orrenameobjects(CREATE,ALTER,DROP,RENAME),
removeallrowsfromadatabaseobjectwithoutdroppingthestructure(TRUNCATE),
manageaccessprivileges(GRANT,REVOKE),
auditdatabaseuse(AUDIT,NOAUDIT)
andaddadescriptionaboutanobjecttothedictionary(COMMENT).
--TransactionControl事务控制语句
savethechanges(COMMIT)
ordiscardthechanges(ROLLBACK)madebyDMLstatements.
Alsoincludedinthetransaction-controlstatementsarestatementstosetapointormarkerinthetransactionforpossiblerollback(SAVEPOINT)
andtodefinethepropertiesforthetransaction(SETTRANSACTION).
Usedtomanagethepropertiesofthedatabase.
Thereisonlyonestatementinthiscategory(ALTERSYSTEM).
--DCL--DataControlLanguage--与开发关系不是很密切,用于权限的分配与回收
grant,revoke,datacontrol
--SessionControl
controlthesessionproperties(ALTERSESSION)
andtoenable/disableroles(SETROLE).
--SystemControl
--------------------------------------------------------
select的用法
--每个员工的所有信息
select*fromemp
--每个人的部门编号,姓名,薪水
selectdeptno,ename,salfromemp;
--每个人的年薪
selectename,sal*12fromemp;
--计算2*3的值
select2*3fromemp;
--计算2*3的值(dual)
select2*3fromdual;
select*fromdual;
--得到当前时间
selectsysdatefromdual
--可以给列起别名,比如求每个人的年薪
selectename,sal*12salperyearfromemp;
--如果别名中有空格,需要用双引号
selectename,sal*12"salperyear"fromemp;
--如果没有内容,则为空
selectcommfromemp;
--当空字段参与计算,则结果是null
--例如:
计算每个人的全年的收入包括月薪和年终奖
selectename,sal*12+commfromemp;
--可以将多个字符串拼在一起。
比如:
求每个人的薪水,格式为smith-sal-123
selectename||'-sal-'||salfromemp;
--如果字符串中有单引号,需要用另外一个单引号转义,比如:
这样一个字符串:
he'sfriend
selectename||'''ssalis'||salfromemp;
--------------------------------------------------------
--distinct关键词的用法
--求有哪些个部门
selectdistinctdeptnofromemp
--可以用来修饰多个字段。
比如:
求有哪些个部门和job的组合
selectdistinctdeptno,jobfromemp
--------------------------------------------------------
where关键词的用法
--可以是数值类型的等值判断。
比如:
求10这个部门的所有员工
select*fromempwheredeptno=20
--可以是字符串类型的等值判断。
比如:
求叫KING的这个人的信息
select*fromempwhereename='KING'
--也可以是不等值判断。
比如:
求薪水小于2000的员工信息
select