在数据库中建立设备表和部门表 表名：sb

if exists (select from sysobjects where id = OBJECT_ID('[sb]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE [sb]

CREATE TABLE [sb] ( [编 号] [varchar] (5) NOT NULL , [名 称] [char] (6) NOT NULL , [启用日期] [datetime] NULL , [价 格] [real] NULL , [部门编号] [char] (2) NOT NULL , [主要设备] [bit] NULL )

INSERT [sb] ( [编 号] , [名 称] , [启用日期] , [价 格] , [部门编号] , [主要设备] ) VALUES ( '100-1' , '复印机' , '2011-09-30 00:00:00000' , 10000 , '01' , 0 )

INSERT [sb] ( [编 号] , [名 称] , [启用日期] , [价 格] , [部门编号] , [主要设备] ) VALUES ( '100-2' , '复印机' , '2011-09-30 00:00:00000' , 20000 , '01' , 0 )

INSERT [sb] ( [编 号] , [名 称] , [启用日期] , [价 格] , [部门编号] , [主要设备] ) VALUES ( '100-3' , '复印机' , '2011-09-30 00:00:00000' , 30000 , '01' , 1 )

INSERT [sb] ( [编 号] , [名 称] , [启用日期] , [价 格] , [部门编号] , [主要设备] ) VALUES ( '200-1' , '打印机' , '2011-09-28 00:00:00000' , 3000 , '02' , 0 )

INSERT [sb] ( [编 号] , [名 称] , [启用日期] , [价 格] , [部门编号] , [主要设备] ) VALUES ( '200-2' , '打印机' , '2011-09-28 00:00:00000' , 8000 , '02' , 1 )

if exists (select from sysobjects where id = OBJECT_ID('[bm]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE [bm]

CREATE TABLE [bm] ( [部门编号] [char] (2) NOT NULL , [名 称] [varchar] (20) NOT NULL , [负责人] [char] (8) NOT NULL , [部门功能] [varchar] (50) NULL )

INSERT [bm] ( [部门编号] , [名 称] , [负责人] , [部门功能] ) VALUES ( '01' , '办公室' , '小红 ' , '文秘' )

INSERT [bm] ( [部门编号] , [名 称] , [负责人] , [部门功能] ) VALUES ( '02' , '财务室' , '王强 ' , '搞财务' )

INSERT [bm] ( [部门编号] , [名 称] , [负责人] ) VALUES ( '03' , '总经理室' , '大王 ' )

INSERT [bm] ( [部门编号] , [名 称] , [负责人] ) VALUES ( '04' , '客户部' , '小王 ' )

INSERT [bm] ( [部门编号] , [名 称] , [负责人] ) VALUES ( '05' , '市场部' , '王磊 ' )

--这个应该不是SQL server 2000 ,做的对不对我就不知道了：

--1:

select 职工 职工号,职工姓名,部门名 from 职工 inner join 部门 on 职工部门号=部门部门号 order by 部门名 ASC

--2:

select 职工职工号,职工姓名,职工性别,职工出生日期,部门部门名 from 职工 inner join 部门 on 职工部门号=部门部门号 where convert(char(10),参加工作,111)<‘2008/07/01’

--3:

select 职工职工号,职工姓名,工资日期,工资 from inner join 部门 on 职工部门号=部门部门号

inner join 工资 on 职工职工号=工资职工号 where 部门号=1

--就是不知道那个是什么，应该就是这样了，敲了这么多，嘿嘿

只会SQL

1select 部门名称,部门联系电话 from 部门 inner join 职工 on 职工部门号=部门部门号 where 职工名称='卢仪'

2select 部门名称,职工职工号,职工姓名 from 职工 inner join 部门 on 职工部门号=部门部门号 where 职工职工号 not in (select 职工职工号 from 职工 inner join 医疗 on 职工职工号=医疗医疗号)

3update 医疗 set 医疗费用=医疗费用/2 where 医疗职工号 in (select 职工职工号 from 职工 where 职称='工程师' and 性别='女')

4你这个最后一问，让我有点模糊，是否我该设置一个触发器来完成。还是什么意思？

1--删除表

drop table work;

drop table dep;

drop table employee;

--雇员

create table employee(

empid char(3) primary key,

empname varchar2(10) not null,--雇员姓名不能为空

age number(2) check (age between 18 and 55),--雇员的年龄介于18到55之间

address varchar2(20),

sal number(5) check (sal between 1000 and 50000) --薪水取1000到50000之间整数

);

--部门

create table dep(

deptno char(3) primary key ,

dname varchar2(20) not null,--部门名不能为空

mgr char(3) references employee(empid)

);

--工作

create table work(

empid char(3) references employee(empid),

deptno char(3) references dep(deptno),

worktime number(1),

primary key (empid,deptno)

);

②

insert into employee values ('E01','tom',35,'海淀',8000);

insert into employee values ('E02','dennis',24,'朝阳',5000);

insert into employee values ('E03','john',20,'海淀',4000);

insert into employee values ('E04','rick',41,'朝阳',7000);

insert into employee values ('E05','bill',45,'西城',7500);

insert into employee values ('E06','frank',35,'海淀',6000);

insert into employee values ('E07','jank',25,'东城',3000);

insert into employee values ('E08','mike',23,'东城',3500);

insert into dep values ('D01','人事','E04');

insert into dep values ('D02','信息','E01');

insert into dep values ('D03','销售','E05');

insert into dep values ('D04','财务','E06');

insert into work values ('E01','D02',6);

insert into work values ('E01','D03',1);

insert into work values ('E01','D04',2);

insert into work values ('E02','D01',2);

insert into work values ('E02','D04',0);

insert into work values ('E03','D02',0);

insert into work values ('E04','D01',4);

insert into work values ('E04','D02',1);

insert into work values ('E05','D03',2);

insert into work values ('E06','D04',4);

insert into work values ('E07','D04',1);

insert into work values ('E08','D03',2);

③用SQL语句完成下列查询

⑴求同时在三个部门工作的雇员姓名

select empname from employee a where (select count(deptno) from work where empid = aempid) = 3;

⑵列出各部门的职工的工资总额，并按照总额大小降序排列

select bdname,sum(asal) as tot from employee a,dep b,work c

where aempid = cempid and cdeptno = bdeptno

group by bdname

order by tot desc;

⑶求和其部门经理住址相同的员工的姓名及经理姓名

select dempname,cleader from (select deptno,empname as leader,address from employee a,dep b

where aempid = bmgr) c,employee d,work e

where cdeptno = edeptno and eempid = dempid

and daddress = caddress and cleader <> dempname;

④为财务部门的雇员加薪，5年以下加5%，5年以上加3%(PL/SQL和语句)

update employee a set sal = sal 105 where

(select worktime from work where empid = aempid and

deptno = (select deptno from dep where dname='财务')) < 5;

update employee a set sal = sal 103 where

(select worktime from work where empid = aempid and

deptno = (select deptno from dep where dname='财务')) >= 5;

⑤删除部门工作年限为0的工作记录，若删除后有雇员没有任何部门工作，删除其雇员记录（PL/SQL和语句）

delete from work where worktime = 0;

delete from employee a where (select count() from work where empid = aempid) = 0;

以上就是关于在数据库中建立设备表和部门表 表名：sb全部的内容，包括:在数据库中建立设备表和部门表 表名：sb、数据库作业，求大神指导、求解答数据库应用题！等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！