SQL数据库经典面试题(修改笔试题)(有答案)(最新精选汇编)-(最新版-已修订).pdf
文本预览下载声明
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 ame=db and o=o
select count(*) from sc where cno=(select cno from c where
ame=db)
2 成绩最高的学生号
select sno from sc where grade=(select max(grade) from sc )
3 每科大于 90 分的人数
select ame,count(*) from c,sc where o=o and sc.grade90
group by ame
select ame,count(*) from c join sc on o=o and
sc.grade90 group by ame
数据库笔试题
*
建表:
dept:
deptno(primary key),dname,loc
emp:
empno(primary key),ename,job,mgr,sal,dept
显示全部