SQL数据库经典面试题(修改笔试题)(有答案)

28、数据库:抽出部门,平均工资,要求按部门的字符串顺序排序,不能含有"human resource"部门,employee结构如下:employee_id, employee_name,depart_id,depart_name,wage答:select depart_name, avg(wage)from employee where depart_name <> 'human resource'group by depart_name order by depart_name--------------------------------------------------------------------------29、给定如下SQL数据库:Test(num INT(4)) 请用一条SQL语句返回num的最小值,但不许使用统计功能,如MIN,MAX等答:select top 1 num from Test order by num--------------------------------------------------------------------------33、一个数据库中有两个表:一张表为Customer,含字段ID,Name;一张表为Order,含字段ID,CustomerID(连向Customer中ID的外键),Revenue; 写出求每个Customer的Revenue总与的SQL语句。

建表create table customer(ID int primary key,Name char(10))gocreate table [order](ID int primary key,CustomerID int foreign key references customer(id) , Revenue float)go--查询select Customer、ID, sum( isnull([Order]、Revenue,0) )from customer full join [order] on( [order]、customerid=customer、id ) group by customer、idselect customer、id,sum(order、revener) from order,customer where customer、id=customerid group by customer、idselect customer、id, sum(order、revener ) from customer full join order on( order、customerid=customer、id ) group by customer、id5数据库(10)a tabel called “performance”contain :name and score,please 用SQL语言表述如何选出score最high的一个(仅有一个)仅选出分数,Select max(score) from performance仅选出名字,即选出名字,又选出分数:select top 1 score ,name from per order by scoreselect name1,score from per where score in/=(select max(score) from per)、、、、、4 有关系s(sno,sname) c(cno,cname) sc(sno,cno,grade)1 问上课程"db"的学生noselect count(*) from c,sc where c、cname='db' and c、cno=sc、cno select count(*) from sc where cno=(select cno from c where c、cname='db')2 成绩最高的学生号select sno from sc where grade=(select max(grade) from sc )3 每科大于90分的人数select c、cname,count(*) from c,sc where c、cno=sc、cno and sc、grade>90 group by c、cnameselect c、cname,count(*) from c join sc on c、cno=sc、cno and sc、grade>90 group by c、cname数据库笔试题*建表:dept:deptno(primary key),dname,locemp:empno(primary key),ename,job,mgr,sal,deptno*/1 列出emp表中各部门的部门号,最高工资,最低工资select max(sal) as 最高工资,min(sal) as 最低工资,deptno from emp group by deptno;2 列出emp表中各部门job为'CLERK'的员工的最低工资,最高工资select max(sal) as 最高工资,min(sal) as 最低工资,deptno as 部门号from emp where job = 'CLERK' group by deptno;3 对于emp中最低工资小于1000的部门,列出job为'CLERK'的员工的部门号,最低工资,最高工资select max(sal) as 最高工资,min(sal) as 最低工资,deptno as 部门号from emp as bwhere job='CLERK' and 1000>(select min(sal) from emp as a where a、deptno=b、deptno) group by b、deptno4 根据部门号由高而低,工资有低而高列出每个员工的姓名,部门号,工资select deptno as 部门号,ename as 姓名,sal as 工资from emp order by deptno desc,sal asc5 写出对上题的另一解决方法(请补充)6 列出'张三'所在部门中每个员工的姓名与部门号select ename,deptno from emp where deptno = (select deptno from emp where ename = '张三')7 列出每个员工的姓名,工作,部门号,部门名select ename,job,emp、deptno,dept、dname from emp,dept where emp、deptno=dept、deptno8 列出emp中工作为'CLERK'的员工的姓名,工作,部门号,部门名select ename,job,dept、deptno,dname from emp,dept where dept、deptno=emp、deptno and job='CLERK'9 对于emp中有管理者的员工,列出姓名,管理者姓名(管理者外键为mgr)select a、ename as 姓名,b、ename as 管理者from emp as a,emp as b wherea、mgr is not null and a、mgr=b、empno10 对于dept表中,列出所有部门名,部门号,同时列出各部门工作为'CLERK'的员工名与工作select dname as 部门名,dept、deptno as 部门号,ename as 员工名,job as 工作from dept,empwhere dept、deptno *= emp、deptno and job = 'CLERK'11 对于工资高于本部门平均水平的员工,列出部门号,姓名,工资,按部门号排序select a、deptno as 部门号,a、ename as 姓名,a、sal as 工资from emp as a where a、sal>(select avg(sal) from emp as b where a、deptno=b、deptno) order by a、deptno12 对于emp,列出各个部门中平均工资高于本部门平均水平的员工数与部门号,按部门号排序select count(a、sal) as 员工数,a、deptno as 部门号from emp as awhere a、sal>(select avg(sal) from emp as b where a、deptno=b、deptno) group by a、deptno order by a、deptno13 对于emp中工资高于本部门平均水平,人数多与1人的,列出部门号,人数,按部门号排序select count(a、empno) as 员工数,a、deptno as 部门号,avg(sal) as 平均工资from emp as awhere (select count(c、empno) from emp as c where c、deptno=a、deptno and c、sal>(select avg(sal) from emp as b where c、deptno=b、deptno))>1 group by a、deptno order by a、deptno14 对于emp中低于自己工资至少5人的员工,列出其部门号,姓名,工资,以及工资少于自己的人数select a、deptno,a、ename,a、sal,(select count(b、ename) from emp as b where b、sal<a、sal) as 人数from emp as awhere (select count(b、ename) from emp as b where b、sal<a、sal)>5数据库笔试题及答案第一套一、选择题1、下面叙述正确的就是CCBAD ______。

A、算法的执行效率与数据的存储结构无关B、算法的空间复杂度就是指算法程序中指令(或语句)的条数C、算法的有穷性就是指算法必须能在执行有限个步骤之后终止D、以上三种描述都不对2、以下数据结构中不属于线性数据结构的就是______。