o******y
发帖数: 13 | 1 都是面试题。
1. 假设有一TABLE EMP(id, sal, department). 找出所有employees whose
salary more than the average for their RESPECTIVE department.
2. 假设有一TABLE contains goods sold by vendors,GOODS(vendor_id, good_
id). 找出所有vendors who have the same or wider list of goods than vendor
002. | s**********e
发帖数: 63 | 2 *Solved in SAS;
*Q1;
proc sql;
title1 "ID with average salary"
title2 ">department average";
select id, sal, avg(sal) as avgsalary format=dollar11.2;
from emp
group by department
having sal>avg(sal);
order by id;
quit;
*Q2;
proc sql;
create table lists as select vendor_id, count(good_id) as list from table1
group by vendor_id
quit;
proc sql;
select vendor_id from lists where list>(select list from lists where vendor_
id=002);
quit; |
|
