6.08练习

create database gongsi
use gongsi
go

create table bumen
(
    bcode int primary key not null,
    bname varchar(20),
    bceo varchar(20),
    btel varchar(20),
)
go
create table renyuan
(
    code int primary key identity(10001,1) not null,
    name varchar(20),
    sex char(10),
    age int,
    cid varchar(20),
    tel varchar(20),
    bumen int 
)
go

insert into bumen values(1001,'财务部','张三','1234567')
insert into bumen values(1002,'企划部','李四','2345678')
insert into bumen values(1003,'市场部','王五','3456789')
insert into bumen values(1004,'客服部','赵六','4567890')
go

insert into renyuan values('张三','男',33,'123456789012345678','1234567',1001)
insert into renyuan values('张全蛋','男',29,'234567890123456789','7654321',1001)
insert into renyuan values('张','男',33,'3456789012345677777','8765432',1001)

insert into renyuan values('李四','女',45,'789012345678903456','2345678',1002)
insert into renyuan values('李莲英','男',55,'890789078907895622','6789056',1002)
insert into renyuan values('李虎','女',45,'456789076543265443','8765434',1002)---------

insert into renyuan values('王五','男',37,'876542345798765434','6543234',1003)
insert into renyuan values('王二麻','女',32,'23456876542345873','2376532',1003)
insert into renyuan values('王二丫','女',23,'12345654322345654','6542346',1003)
insert into renyuan values('王查查','女',23,'12345654322345654','6542346',1003)
insert into renyuan values('王甜','女',23,'12345654322345654','6542346',1003)


insert into renyuan values('赵六','女',26,'234765423456222','9874533',1004)
insert into renyuan values('赵敏','女',25,'5434567765433456','7623456',1004)
insert into renyuan values('赵英俊','男',32,'125432886543225','8565424',1004)
go



--查找--查找所有男同志里面年龄最大的人的信息
select * from renyuan  where code=
(select top 1 code from renyuan where age=
(select  MAX(age) from renyuan where sex='男'))

select * from bumen 
select * from renyuan


---查询人数最多的部门里年龄最大的人的信息
select bumen from renyuan group by bumen having COUNT(*)>4
select MAX(age) from renyuan where bumen=1003
select code from renyuan where bumen=1003 and age =37
select * from renyuan where code=1007


select * from renyuan where code=
(select code from renyuan where bumen=1003 and age =37)

go
--按照年龄从小到大排序,取789号人员的所有信息
select top 3 * from renyuan where code not in
(select top 6 code from renyuan order by age) order by age


--将人员表显示出来   并且将部门编号变为部门名称显示
select name , sex ,(select bname from bumen where bumen.bcode = renyuan.bumen) from renyuan


--分页查询
select top 5 * from renyuan
--6~10
select top 5 * from renyuan where code not in
(select top 5 code from renyuan)