SQL查询——示列代码（二）

--1.创建数据库（EMS），并同时创建Dept部门表。create database [EMS] ;create table [Dept]([Dno] varchar(50) not null ,[Dname] varchar(50) null ,[Daddress] varchar(100) null ,[Dphone] varchar(20) null,primary key([Dno])) ;--2.将职工表中的第二条记录使用SQL语句插入到数据表中。insert into [Emp]([Eno],[Ename],[Esex],[Eposition],[Eaddress],[Dno])values('08002','李四','男','主任','合肥市庐阳区','A002') ;--3.将职工号为“08002”的职工的职务修改为“经理”，性别修改为“女”。update [Emp]set[Eposition] ='经理' ,[Esex] ='女'where[Eno] = '08002' ;--4.将职工编号为“08004”的保健表中的记录删除。delete from [Health] where [Eno] ='08004' ;--5.查找所有女经理的姓名、家庭地址。select [Ename],[Eaddress] from [Emp]where [Esex] ='女' and [Eposition] ='经理' ;--6.查找“研发部”下所有职工的职工号、姓名、职务。select e.[Eno] ,e.[Ename] ,e.[Eposition] from [Emp] as e inner join [Dept] as don e.[Dno] = d.[Dno] where d.[Dname] ='研发部' ;--7.查找职工“李晨”所在的部门名称、部门地址。select d.[Dname] ,d.[Daddress] from [Emp] as e inner join [Dept] as don e.[Dno] = d.[Dno] where e.[Ename] = '李晨' ;--8.查找所有健康状况为“良”的所有职工的职工号、姓名、性别、部门名称。select e.[Eno],e.[Ename],e.[Esex],d.[Dname] from [Emp] as e ,[Dept] as d ,[Health] as hwhere e.[Dno] = d.[Dno] and e.[Sno] = h.[Sno]and h.[Hstatus] ='良' ;--9.查找检查身体日期在“2013-08-20”的所有职工的职工号、姓名。select * from [Emp] as e inner join [Health] as hon e.[Sno] = h.[Sno]where h.[Hdate] >= '2013-08-20' and h.[Hdate] < '2013-08-21' ;--10.将所有员工删除--如果不考虑参照完整性（外码约束），那么很简单delete from [Emp] ;--如果考虑参照完整性，那么需要先清空Health表的数据，然后才能删除Emp中的数据delete from [Health] ;delete from [Emp] ;