现有三张表:
部门表:(branch)
部门(主键) 名字 城市
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON雇员表:(servants)
雇员编 名字 职务 上级编号 雇佣日期 工资 津贴 部门(外键)
7369 SMITH CLERK 7902 1980-12-17 800 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600 300 30
7521 WARD SALESMAN 7698 1981-2-22 1250 500 30
7566 JONES MANAGER 7839 1981-4-2 3975 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250 1400 30
7698 BLAKE MANAGER 7839 1981-5-1 850 30
7788 SCOTT ANALYST 7566 1987-4-19 3000 20
7844 TURNER SALESMAN 7698 1981-9-8 1500 0 30
7876 ADAMS CLERK 7788 1987-5-23 1100 20
7900 JAMES CLERK 7698 1981-12-3 950 30
7902 FORD ANALYST 7566 1981-12-3 3000 20工资档次:(grade)
工资档次 最低工资 最高工资
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999 题目如下:(要求用一条T-SQL语句做出)
(例如:列出部门名称和这些部门的雇员,同时列出那些没有雇员的部门
select 部门名称=Bname,部门雇员=sname from Branch left join servants on bid=sbid)--1、列出所有雇员的姓名及其直接上级的姓名
--2、列出入职日期早于其直接上级的所有雇员
--3。列出各种工作类别的最低薪金,并使最低薪金大于1500
--4、列出每个部门的信息以及该部门中雇员的数量
--5、列出从事同一种工作但属于不同部门的雇员的不同组合
--6、列出分配有雇员数量的所有部门的详细信息,即使是分配有0个雇员
--7、列出各种类别工作的最低工资
--8、列出按计算的字段排序的所有雇员的年薪
--9、列出薪金水平处于第四位的雇员
第一种理解答案:按工资位次排,处于第4位的员工
第二种理解答案:按工资水平排,处于第4档的员工
部门表:(branch)
部门(主键) 名字 城市
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON雇员表:(servants)
雇员编 名字 职务 上级编号 雇佣日期 工资 津贴 部门(外键)
7369 SMITH CLERK 7902 1980-12-17 800 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600 300 30
7521 WARD SALESMAN 7698 1981-2-22 1250 500 30
7566 JONES MANAGER 7839 1981-4-2 3975 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250 1400 30
7698 BLAKE MANAGER 7839 1981-5-1 850 30
7788 SCOTT ANALYST 7566 1987-4-19 3000 20
7844 TURNER SALESMAN 7698 1981-9-8 1500 0 30
7876 ADAMS CLERK 7788 1987-5-23 1100 20
7900 JAMES CLERK 7698 1981-12-3 950 30
7902 FORD ANALYST 7566 1981-12-3 3000 20工资档次:(grade)
工资档次 最低工资 最高工资
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999 题目如下:(要求用一条T-SQL语句做出)
(例如:列出部门名称和这些部门的雇员,同时列出那些没有雇员的部门
select 部门名称=Bname,部门雇员=sname from Branch left join servants on bid=sbid)--1、列出所有雇员的姓名及其直接上级的姓名
--2、列出入职日期早于其直接上级的所有雇员
--3。列出各种工作类别的最低薪金,并使最低薪金大于1500
--4、列出每个部门的信息以及该部门中雇员的数量
--5、列出从事同一种工作但属于不同部门的雇员的不同组合
--6、列出分配有雇员数量的所有部门的详细信息,即使是分配有0个雇员
--7、列出各种类别工作的最低工资
--8、列出按计算的字段排序的所有雇员的年薪
--9、列出薪金水平处于第四位的雇员
第一种理解答案:按工资位次排,处于第4位的员工
第二种理解答案:按工资水平排,处于第4档的员工
go
if exists(select * from sysdatabases where name='tmpDB')
drop database tmpDB
go
create database tmpDB
go
--1.建表--
use tmpDB
go
if exists(select * from sysobjects where name='branch')
drop table branch
go
--部门表
create table branch
(
Bid int primary key,--部门ID
Bname varchar(20), --部门名字
Bcity varchar(20) --城市
)
go
if exists(select * from sysobjects where name='servants')
drop table servants
go
--雇员表
create table servants
(
Sid int primary key, --雇员编号
Sname varchar(20), --雇员姓名
Sduty varchar(20), --职务
Ssupid int, --上级编号
Sdate datetime, --雇佣日期
Smoney money, --工资
Ssubsidy money, --津贴
Sbid int foreign key(sbid) references branch(bid)--部门id,外键
)
go
if exists(select * from sysobjects where name='grade')
drop table grade
go
--工资档次
create table grade
(
Gid int,
Gmin money,
Gmax money
)
go--插入测试数据
insert into branch
select 10,'accounting','new york' union
select 20,'research','dallas' union
select 30,'sales','chicago' union
select 40,'operations','boston'go
insert into servants
select 7369,'SMITH','CLERK',7902,'1980-12-17','800','0',20 union
select 7499,'ALLEN','SALESMAN',7698,'1981-2-20','1600','300',30 union
select 7521,'WARD','SALESMAN',7698,'1981-2-22','1250','500',30 union
select 7566,'JONES','MANAGER',7839,'1981-4-2','3975','0',20 union
select 7654,'MARTIN','SALESMAN',7698,'1981-9-28','1250','1400',30 union
select 7698,'BLAKE','MANAGER',7839,'1981-5-1','850','0',30 union
select 7788,'SCOTT','ANALYST',7566,'1987-4-19','3000','0',20 union
select 7844,'TURNER','SALESMAN',7698,'1981-9-8','1500','0',30 union
select 7876,'ADAMS','CLERK',7788,'1987-5-23','1100','0',20 union
select 7900,'JAMES','CLERK',7698,'1981-12-3','950','0',30 union
select 7902,'FORD','ANALYST',7566,'1981-12-3','3000','0',20 go
insert into grade
select 1,'700','1200' union
select 2,'1201','1400' union
select 3,'1401','2000' union
select 4,'2001','3000' union
select 5,'3001','9999'
go
--select * from servants
/*------1.列出所有雇员的姓名及其直接上级的姓名-----*/select a.sname as 雇员姓名,b.sname as 上级姓名 from servants as a left join servants as b on a.ssupid=b.sid
/*-----2.列出入职日期早于其直接上级的所有雇员------*/select a.sname as 雇员姓名 from servants as a left join servants as b on a.ssupid=b.sid
where a.sdate<b.sdate/*-----3.列出各种工作类别的最低薪金,并使最低薪金大于1500-----*/
--没看懂题
--select min(smoney) from servants group by sbid having min(smoney)>1500
/*-----4.列出每个部门的信息以及该部门中雇员的数量---------*/select bname as 部门名称,count(sbid) as 雇员人数 from servants as s inner join branch as b on s.sbid=b.bid group by bname
/*-----5.列出从事同一种工作但属于不同部门的雇员的不同组合-----*/--select distinct(sduty),sbid from servants/*-----6.列出分配有雇员数量的所有部门的详细信息,即使是分配有0个雇员-----*/select bname as 部门名称,count(sbid) as 雇员人数 from servants as s right join branch as b on s.sbid=b.bid group by bname
/*-----7.列出各种类别工作的最低工资-----*/
select * from servants
select min(smoney) from servants group by sduty
go
if exists(select * from sysdatabases where name='tmpDB')
drop database tmpDB
go
create database tmpDB
go
--1.建表--
use tmpDB
go
if exists(select * from sysobjects where name='branch')
drop table branch
go
--部门表
create table branch
(
Bid int primary key,--部门ID
Bname varchar(20), --部门名字
Bcity varchar(20) --城市
)
go
if exists(select * from sysobjects where name='servants')
drop table servants
go
--雇员表
create table servants
(
Sid int primary key, --雇员编号
Sname varchar(20), --雇员姓名
Sduty varchar(20), --职务
Ssupid int, --上级编号
Sdate datetime, --雇佣日期
Smoney money, --工资
Ssubsidy money, --津贴
Sbid int foreign key(sbid) references branch(bid)--部门id,外键
)
go
if exists(select * from sysobjects where name='grade')
drop table grade
go
--工资档次
create table grade
(
Gid int,
Gmin money,
Gmax money
)
go--插入测试数据
insert into branch
select 10,'accounting','new york' union
select 20,'research','dallas' union
select 30,'sales','chicago' union
select 40,'operations','boston'go
insert into servants
select 7369,'SMITH','CLERK',7902,'1980-12-17','800','0',20 union
select 7499,'ALLEN','SALESMAN',7698,'1981-2-20','1600','300',30 union
select 7521,'WARD','SALESMAN',7698,'1981-2-22','1250','500',30 union
select 7566,'JONES','MANAGER',7839,'1981-4-2','3975','0',20 union
select 7654,'MARTIN','SALESMAN',7698,'1981-9-28','1250','1400',30 union
select 7698,'BLAKE','MANAGER',7839,'1981-5-1','850','0',30 union
select 7788,'SCOTT','ANALYST',7566,'1987-4-19','3000','0',20 union
select 7844,'TURNER','SALESMAN',7698,'1981-9-8','1500','0',30 union
select 7876,'ADAMS','CLERK',7788,'1987-5-23','1100','0',20 union
select 7900,'JAMES','CLERK',7698,'1981-12-3','950','0',30 union
select 7902,'FORD','ANALYST',7566,'1981-12-3','3000','0',20 go
insert into grade
select 1,'700','1200' union
select 2,'1201','1400' union
select 3,'1401','2000' union
select 4,'2001','3000' union
select 5,'3001','9999'
go
--select * from servants
/*------1.列出所有雇员的姓名及其直接上级的姓名-----*/select a.sname as 雇员姓名,b.sname as 上级姓名 from servants as a left join servants as b on a.ssupid=b.sid
/*-----2.列出入职日期早于其直接上级的所有雇员------*/select a.sname as 雇员姓名 from servants as a left join servants as b on a.ssupid=b.sid
where a.sdate<b.sdate/*-----3.列出各种工作类别的最低薪金,并使最低薪金大于1500-----*/
--没看懂题
--select min(smoney) from servants group by sbid having min(smoney)>1500
/*-----4.列出每个部门的信息以及该部门中雇员的数量---------*/select bname as 部门名称,count(sbid) as 雇员人数 from servants as s inner join branch as b on s.sbid=b.bid group by bname
/*-----5.列出从事同一种工作但属于不同部门的雇员的不同组合-----*/--select distinct(sduty),sbid from servants/*-----6.列出分配有雇员数量的所有部门的详细信息,即使是分配有0个雇员-----*/select bname as 部门名称,count(sbid) as 雇员人数 from servants as s right join branch as b on s.sbid=b.bid group by bname
/*-----7.列出各种类别工作的最低工资-----*/
select * from servants
select min(smoney) from servants group by sduty
create table branch(部门 int primary key not null,名字 varchar(20) null,城市 varchar(20) null)
insert into branch
select 10,'ACCOUNTING','NEW YORK' union all
select 20,'RESEARCH','DALLAS' union all
select 30,'SALES','CHICAGO' union all
select 40,'OPERATIONS','BOSTON'*/create table servants(
雇员编号 int primary key not null,
名字 varchar(20) null,
职务 varchar(20) null,
上级编号 int null,
雇佣日期 datetime null,
工资 int null,
津贴 int null,
部门 int null)
insert into servants
select 7369,'SMITH','CLERK',7902,'1980-12-17',800,20,null union all
select 7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600, 300,30 union all
select 7521,'WARD','SALESMAN',7698,'1981-2-22',1250, 500,30 union all
select 7566,'JONES','MANAGER',7839,'1981-4-2',3975, 20,null union all
select 7654,'MARTIN','SALESMAN',7698,'1981-9-28',1250,1400, 30 union all
select 7698,'BLAKE','MANAGER',7839,'1981-5-1',850,30,null union all
select 7788,'SCOTT','ANALYST',7566,'1987-4-19',3000,20,null union all
select 7844,'TURNER','SALESMAN',7698,'1981-9-8',1500,0,30 union all
select 7876,'ADAMS','CLERK',7788,'1987-5-23',1100,20,null union all
select 7900,'JAMES','CLERK',7698,'1981-12-3',950,30,null union all
select 7902,'FORD','ANALYST',7566,'1981-12-3',3000,20,null create table grade(
工资档次 int primary key not null,
最低工资 int null,
最高工资 int null)
insert into grade
select 1,700,1200 union all
select 2,1201,1400 union all
select 3,1401,2000 union all
select 4,2001,3000 union all
select 5,3001,9999--1、列出所有雇员的姓名及其直接上级的姓名
select a.名字 as 雇员姓名,b.名字 as 上级姓名 from servants a left join servants b on a.上级编号 = b.雇员编号
--2、列出入职日期早于其直接上级的所有雇员
select a.名字 as 雇员姓名,a.雇佣日期,b.名字 as 上级姓名,b.雇佣日期 from servants a left join servants b on a.上级编号 = b.雇员编号 where a.雇佣日期<b.雇佣日期
--3。列出各种工作类别的最低薪金,并使最低薪金大于1500
select 工资档次,最低工资 from grade where 最低工资>1500
--4、列出每个部门的信息以及该部门中雇员的数量
select *,雇员数量=(select count(*) from servants where 部门=a.部门 ) from branch a
--5、列出从事同一种工作但属于不同部门的雇员的不同组合
select 职务,部门 from servants group by 职务,部门
--6、列出分配有雇员数量的所有部门的详细信息,即使是分配有0个雇员 (其实和4同)
select *,雇员数量=(select count(*) from servants where 部门=a.部门 ) from branch a
--7、列出各种类别工作的最低工资
select min(最低工资) from grade
--8、列出按计算的字段排序的所有雇员的年薪
select *,(工资+津贴)*12 as 年薪 from servants order by 年薪
--9、列出薪金水平处于第四位的雇员
--第一种理解答案:按工资位次排,处于第4位的员工
select a.* from servants a,(select 最低工资,最高工资 from grade where 工资档次=4) b where 工资+津贴 between b.最低工资 and b.最高工资
--第二种理解答案:按工资水平排,处于第4档的员工
select top 1 *,(工资+津贴) as 月薪
from servants
where 雇员编号 not in (
select top 3 雇员编号 from servants order by 工资+津贴
)
order by 月薪我是来当练手的,仅供参考
SQL code第五小题,有点不对题,列出同一职业不同部门的雇员的不同组合