2021年SQL数据库经典面试题修改笔试题有答案.docVIP

28.数据库：抽出部门，平均工资，要求按部门字符串次序排序，不能含有human resource部门，employee结构以下： employee_id, employee_name,depart_id,depart_name,wage 答:select depart_name, avg(wage)from employee where depart_name human resourcegroup 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；写出求每个CustomerRevenue总和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.grade90 group by c.cname ? select c.cname,count(*) from c join sc on c.cno=sc.cno and sc.grade90 group by c.cname  ?数据库笔试题 ?? ?? *建表：dept:?deptno(primary key),dname,locemp:?empno(primary key),ename,job,mgr,sal,deptno*/ 1 列出emp表中各部门部门号，最高工资，最低工资select max(sal) as 最高工资,min(sal) as 最低工资,deptno from emp grou