SQL数据库经典面试题(修改笔试题)(有答案).doc
文本预览下载声明
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,deptno */
1 列出emp表中各部门的部门号，最高工资，最低工资 select max(sal) as 最高工资,min(sal) as 最低工资,deptno from emp group by deptno;
2 列出emp表中各部门job为CLERK的员工的最低工资，最高工资 select m
显示全部