大学天裸体跪求 基础的sql语句 答案

来源：百度知道 编辑：UC知道 时间：2024/06/20 10:54:31
写Sql语句！要绝对准确的答案！自信的留下答案！
悬赏分：20 - 离问题结束还有 14 天 23 小时
假定有一张员工信息表emp_table，表中有ID(工号)，NAME(姓名)，DEPT(部门),AGE(年龄)四个字段
(1)用SQL语句检索记录
ID以字符‘S’开头，NAME不为空(这里的空指数据缺失,也称做NULL)，
AGE最大的员工记录. （8分）

(2)抽出表中有两名以上员工部门的员工记录,及这些部门的员工数。
例:
DEPT_NO NUM
AAA 2
BBB 3
CCC 6

(1)/*一楼的方法，万一有一个以上人数年龄都为最大都合题意就不对了*/
select * from emp_table where ID like 's%' and NAME is not null and AGE in
(select max(AGE) from emp_table);

(2)
select DEPT AS DEPT_NO,count(ID) as NUM
from emp_table
group by DEPT having count(ID)>=2;

1.
select top 1 * from emp_table where left(id,1)='s' and name is not null order by age desc
2.
SELECT *
FROM (SELECT dept, COUNT(*) AS num
FROM emp_table
GROUP BY dept) DERIVEDTBL
WHERE (num >= 2)

貌似是考集计函数的用法
①
select ID,NAME,DEPT,MAX(AGE) AS AGE
from emp_table
where ID like 'S%' and NAME IS NOT NULL
group by ID,NAME,DEPT
②
select count(ID) as num ,DEPT
from emp_table
group by DEPT
having count(ID) > 2

(1)select ID,NAME,max(AGE),DEPT from emp_table where ID like 'S%' and NAME is not null;

(2)select DEPT,count(ID) from emp_table group by DEPT h