【优质】SQL数据库经典面试题(笔试题)（有答案）.doc

28.数据库：抽出部门，平均工资，要求按部门的字符串顺序排序，不能含有human resource部门， employee结构如下：employee_id, employee_name, depart_id,depart_name,wage 答:select depart_name, avg wage from employeewhere depart_name human resourcegroup by depart_nameorder by depart_name --------------------------------------------------------------------------29.给定如下SQL数据库：Test num INT 4 请用一条SQL语句返回num的最小值，但不许使用统计功能，如MIN，MAX等 答:select top 1 numfrom Testorder 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,locemp:?