1 create database dudu 2 go 3 use dudu 4 go  5 create table bumen 6 ( 7    bcode int primary key,  --部门编号--bcode为主键，不能重复，确保唯一性 8    bname varchar(20),  --部门名字 9    bzhuguan varchar(20),  --部门主管10 )11 create table renyuan12 (13 code int primary key identity(1001,1),   --人员编号 --code为主键，不能重复，确保唯一性。identity(1001,1)自增长主键，表示从1001开始，每次增加1，当添加元素时，不用添加此列14 name varchar(20),  --人员名字15 age int,  --人员年龄16 bc int   --人员所属部门编号17 )

1 insert into bumen values(101,‘财务部‘,‘张三‘)2 insert into bumen values(102,‘销售部‘,‘李四‘)3 insert into bumen values(103,‘技术部‘,‘王五‘)4 go5 insert into renshu values(‘张三‘,34,101)6 insert into renshu values(‘李四‘,35,102)7 insert into renshu values(‘王五‘,24,103)

8 insert into renshu values(‘白居易‘,33,101)9 insert into renshu values(‘李清照‘,45,102)10 insert into renshu values(‘王阳明‘,27,103)

11 insert into renshu values(‘孔子‘,78,101)12 insert into renshu values(‘孟子‘,‘66,102)13 insert into renshu values(‘老子‘,89,103)

-查询年纪最大的人的部门名称select bname from bumen where bcode=(select bc from renyuan where code=(select code from renyuan where age=(select MAX(age) from renyuan)))

--按照年龄排序后的前三个人的所有信息select top 3 * from renyuan order by age--按照年龄排序后的6/7/8名人员的所有信息select top 3 * from renyuan where code not in (select top 5 code from renyuan order by age) order by age

--查询销售部里的年龄大于35岁的人的所有信息select * from renyuan where code in(select code from renyuan where age>35) and bc=(select bcode from bumen where bname=‘销售部‘)

--查看所有人员信息，将部门编号替代为部门名称select code,name,age,(select bname from bumen where bumen.bname=renyuan,bc) as 部门 from renyuan