计算机数据库面试题及答案,SQL数据库经典面试题笔试题含答案.docx

SQL数据库经典面试题笔试题含答案

28.数据库：抽出部门，平均工资，要求按部门的字符串顺序排序，不能含有quothumanresourcequot部门，employee 结构如下：employee_id employee_namedepart_iddepart_namewage答:select depart_name avgwagefrom employeewhere depart_name ltgt human resourcegroup by depart_nameorder by depart_name29.给定如下 SQL 数据库：Testnum INT4 请用一条 SQL 语句返回 num 的最小值，但不许使用统计功能，如 MIN，MAX 等答:select top 1 numfrom Testorder by num33.一个数据库中有两个表:一张表为 Customer，含字段 IDName一张表为 Order，含字段 IDCustomerID(连向 Customer 中 ID 的外键)Revenue；写出求每个 Customer 的 Revenue 总和的 SQL 语句。建表 create table customerID int primary keyName char10gocreate table orderID int primary keyCustomerID int foreign key references customerid Revenue floatgo--查询select Customer.ID sum isnullOrder.Revenue0 from customer full join orderon order.customeridcustomer.id group by customer.idselect customer.idsumorder.revener from ordercustomer wherecustomer.idcustomerid group by customer.idselect customer.id sumorder.revener from customer full join orderon order.customeridcustomer.id group by customer.id5 数据库(10)a tabel called “performance”contain ：name and score，please 用 SQL语言表述如何选出 score 最 high 的一个(仅有一个)仅选出分数，Select maxscore from performance仅选出名字，即选出名字，又选出分数：select top 1 score name from perorder by scoreselect name1score from per where score in/select maxscore fromper4 有关系 ssnosname ccnocname scsnocnograde 1 问上课程 quotdbquot的学生 noselect count from csc where c.cnamedb and c.cnosc.cnoselect count from sc where cnoselect cno from c wherec.cnamedb2 成绩最高的学生号select sno from sc where gradeselect maxgrade from sc 3 每科大于 90 分的人数select c.cnamecount from csc where c.cnosc.cno andsc.gradegt90 group by c.cnameselect c.cnamecount from c join sc on c.cnosc.cno andsc.gradegt90 group by c.cname 数据库笔试题建表：dept: deptnoprimary keydnamelocemp: empnoprimary keyenamejobmgrsaldeptno/1 列出 emp 表中各部门的部门号，最高工资，最低工资select maxsal as 最高工资minsal as 最低工资deptno from emp groupby deptno2 列出 emp 表中各部门 job 为CLERK的员工的最低工资，最高工资select maxsal as 最高工资minsal as 最低工资deptno as 部门号 fromemp where job CLERK group by deptno3 对于 emp 中最低工资小于 1000 的部门