1:抽出部门,平均工资,要求按部门的字符串顺序排序,不能含
有"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
-------------------------------------------------------------
2:给定如下SQL数据库:Test(num INT(4)) 请用一条SQL语句返回
num的最小值,但不许使用统计功能,如MIN,MAX等
答:
select top 1 num
from Test
order by num desc
--------------------------------------------------------------------------
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