SQL数据库试(笔试题).docVIP

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 答:select top 1 numfrom Testorder by num 建表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:?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 max sal as 最高工资,min sal as 最低工资,deptno as 部门号 from emp where job CLERK group by deptno; 3 对于emp中最低工资小于1000的部门，列出job为CLERK的员工的部门号，最低工资，最高工资select max sal