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))

go

create 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、id

select customer、id,sum(order、revener) from order,customer where customer、id=customerid group by customer、id

select customer、id, sum(order、revener ) from customer full join order on( order、customerid=customer、id ) group by customer、id

5数据库(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 score

select 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"的学生no

select 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、cname

select 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,loc

emp:

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 b

where job='CLERK' and 1000>(select min(sal) from emp as a where a、deptno=b、deptno) group by b、deptno

4 根据部门号由高而低,工资有低而高列出每个员工的姓名,部门号,工资

select deptno as 部门号,ename as 姓名,sal as 工资from emp order by deptno desc,sal asc

5 写出对上题的另一解决方法

(请补充)

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、deptno

8 列出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 where

a、mgr is not null and a、mgr=

b、empno

10 对于dept表中,列出所有部门名,部门号,同时列出各部门工作为'CLERK'的员工名与工作

select dname as 部门名,dept、deptno as 部门号,ename as 员工名,job as 工作from dept,emp

where 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、deptno

12 对于emp,列出各个部门中平均工资高于本部门平均水平的员工数与部门号,按部门号排序

select count(a、sal) as 员工数,a、deptno as 部门号from emp as a

where a、sal>(select avg(sal) from emp as b where a、deptno=b、deptno) group by a、deptno order by a、deptno

13 对于emp中工资高于本部门平均水平,人数多与1人的,列出部门号,人数,按部门号排序

select count(a、empno) as 员工数,a、deptno as 部门号,avg(sal) as 平均工资from emp as a

where (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、deptno

14 对于emp中低于自己工资至少5人的员工,列出其部门号,姓名,工资,以及工资少于自己的人数

select a、deptno,a、ename,a、sal,(select count(b、ename) from emp as b where b、sal

where (select count(b、ename) from emp as b where b、sal5

数据库笔试题及答案

第一套

一、选择题

1、下面叙述正确的就是CCBAD ______。

A、算法的执行效率与数据的存储结构无关

B、算法的空间复杂度就是指算法程序中指令(或语句)的条数

C、算法的有穷性就是指算法必须能在执行有限个步骤之后终止

D、以上三种描述都不对

2、以下数据结构中不属于线性数据结构的就是______。A、队列B、线性表C、二叉树D、栈

3、在一棵二叉树上第5层的结点数最多就是______。A、8 B、16 C、32 D、15