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

文档介绍：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
--------------------------------------------------------------------------
:
一张表为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 , sum( isnull([Order].Revenue,0) )
from customer full join [order] on( [order].customerid= )
group by
select ,sum() from order,customer where =customerid group by
select , sum( ) from customer full join order
on( = ) group by
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 ='db' and =
select count(*) from sc where cno=(select cno from c where ='db')
 2 成绩最高的学生号
select sno from sc where grade=(select max(grade) from sc )
 3 每科大于90分的人数
select ,count(*) from c,sc where = and >90 group by
 
select ,count(*