数据库系统概论实验==第三章

JUMPLANB

已于 2022-03-31 22:29:28 修改

阅读量3.4k

点赞数 8

文章标签： sql

于 2022-03-31 22:15:29 首次发布

本文链接：https://blog.csdn.net/B_planzp/article/details/123868994

版权

创建数据库

create database YYGL
on
(
name='YYGL_data',
filename='D:\sql\data\YYGL.mdf',
size=10 mb,
maxsize=50 mb,
filegrowth=5%
)
log on
(
name='YYGL_1log',
filename='D:\sql\data\YYGL.ldf',
size=2 mb,
maxsize=5 mb,
filegrowth= 1 mb
);

插入数据

USE YYGL;

create table Employees
(
EmployeeID char(6)  NOT NULL primary key,/*员工编号、主键*/
Name char(10) NOT NULL,/*姓名*/
Education char(4) NOT NULL,		/*学历*/
Birthday date NOT NULL,		/*出生日期*/
Sex bit NOT NULL  default(1),			/*性别，默认值为1*/
WorkYear tinyint  NULL,		/*工作时间*/
Address varchar (40)  NULL,	/*地址*/
PhoneNumber char(12)  NULL,	/*电话号码*/
DepartmentID char(3) not null	/*员工部门号，外键*/
);

create table Departments
(
DepatmentID char(3)  not null primary key, /*部门编号，主键*/
DepatmentName char(20) not null,/*部门名*/
Note varchar(100) null,/*备注*/
);
create table Salary
(
EmployeeID char(6)  not null primary key ,/*员工编号，主键*/
InCome float null,/*收入*/
OutCome float null,/*支出*/
);
--插入数据

insert into Employees
values
( '000001','王林','大专','1966-01-23',1,8,'中山路32-1-508','83355668','2'),
( '010008','李四','本科','1976-03-28',1,3,'北京东路100-2','83321321','1'),
( '020010','王蓉','硕士','1982-12-09',1,2,'四牌楼10-0-108','83792361','1'),
( '020018','李丽','大专','1960-07-30',0,6,'中山东路102-2','83413301','1'),
( '102201','刘明','本科','1972-10-18',1,3,'虎踞路100-2','83606608','5'),
( '102208','朱军','硕士','1965-09-28',1,2,'牌楼巷5-3-106','84708817','5'),
( '108991','钟敏','硕士','1979-08-10',0,4,'中山路10-3-105','83346722','3'),
( '111006','张石兵','本科','1974-10-01',1,1,'解放路34-1-203','84563418','5'),
( '210678','林涛','大专','1977-04-02',1,2,'中山北路24-35','83467336','3'),
( '302566','李玉','本科','1968-09-20',1,3,'热和路209-3','58765991','4'),
( '308759','叶凡','本科','1978-11-18',1,2,'北京西路3-7-52','83308901','4'),
( '504209','陈琳','大专','1969-09-03',0,5,'汉中路120-4-12','84468158','4')
;
insert into Departments
values
( '1','财务部',null),
( '2','人力资源部',null),
( '3','经理办公室',null),
( '4','研发部',null),
( '5','市场部',null);
--------
insert into Salary 
values
('000001',2100.8,123.09),
('010008',1582.62,88.03),
( '102201',2569.88,185.65),
( '111006',1987.01,79.58),
( '504209',2066.15,108.0),
( '302566',2980.7,210.2),
( '108991',3259.98,281.52),
( '020010',2860.0,198.0),
( '020018',2347.68,180.0),
( '308759',2531.98,199.08),
( '210678',2240.0,121.0),
( '102208',1980.0,100.0);
--添加外键约束
--alter table student add constraint 约束名称 约束类型 (列名) references 被引用的表名称 （列名）
alter table Employees add constraint shiyan1 foreign key (DepartmentID) references Departments (DepatmentID);

实验内容

/*
实验内容
*/
select * from Departments;
select * from Employees;
select * from Salary ;
--查询每个雇员的所有数据
select * 
from Employees ;
--查询地址和电话
select
address ,phonenumber
from
Employees ;

/*
实验内容
*/
select * from Departments;
select * from Employees;
select * from Salary ;
--查询每个雇员的所有数据
select * 
from Employees ;
--查询地址和电话
select
address ,phonenumber
from
Employees ;
--查询部门号和性别
select 
distinct departmentid,sex  --去重
from
Employees ;
--查询固定人员数据
select 
address ,phonenumber
from
Employees 
where EmployeeID ='000001';
--月收入高于两千的员工号码
select EmployeeID, income
from Salary 
where InCome >2000;
use yygl;
--查询1970年以后出生的员工的姓名和住址。
select name,address
from Employees
where year(birthday)>1970;
--查询所有财务部员工的号码和姓名。
select EmployeeID ,name,Depatmentname
from  Employees,Departments 
where DepatmentName = '财务部';
--（4）查询Employees表中女雇员的地址和电话，并将结果中各列的标题分别制定为地址、电话。
select address as '地址', phonenumber as '电话'
from Employees
where Sex = '0';
--查询Employees表中男员工的姓名和出生日期，要求将各列标题用中文表示。
select  name '姓名' ,Birthday  as '出生日期', phonenumber as '电话'
from Employees
where Sex = '1';
--（5）查询Employees表中男员工的姓名和性别，要求Sex值为1时显示为“男”，为“0”时显示为“女”。
select  name '姓名' ,sex  as '性别', phonenumber as '电话'
from Employees
where Sex = '1';
--答案
select name as 姓名,
	case
	when sex='1' then '男'
	when sex='0' then '女'
	end as 性别
from Employees;
--计算每个雇员的实际收入
select   
Employees.Employeeid, name '姓名',income 收入, outcome '支出',
income-outcome 实际收入
--from Employees left outer join Salary on (Employees.Employeeid = Salary.Employeeid);
from Employees left outer join Salary using (Employees.Employeeid);
--计算Salary表中员工月收入的平均数。
select AVG(income)
from Salary ;
--获得Employees表中最大的员工号码。
select max(Employeeid)
from Employees  ;
--计算Salary表中所有员工的总支出。
select sum(outcome )
from Salary ;
--查询财务部雇员的最高和最低实际收入。
select max (income-outcome) '最高实际收入'
, min (income-outcome) '最低实际收入'
from  Employees,Departments,salary 
where Depatmentname in(
select DepatmentName 
from Departments
where Depatmentname = '财务部');
--找出所有姓王的雇员的部门号
select name,departmentid
from Employees
where Name like '王%';
--找出所有其他地址中含有“中山”的雇员的号码及部门号。
select  EmployeeID,name,departmentid
from Employees
where Name like '%中山';
--查找员工号码中倒数第二个数字为“0”的员工的姓名、地址和学历。
select  EmployeeID,name,address,Education
from Employees
where EmployeeID like '____0%';
--找出所有收入在2000—3000元之间的员工号码
select  EmployeeID,income
from salary
where income between 2000 and 3000;
--找出所有在部门1或2工作的雇员的号码。
select  EmployeeID,departmentID
from Employees
where departmentID ='1' or departmentID = '2';
 
--查找在财务部工作的雇员情况。
select *
from   Employees
where DepartmentID =(
select  DepartmentID
from Departments
where DepatmentName = '财务部');
--用子查询的方法查找所有收入在2500元以下的雇员的情况
select Employees.*, income
from  Employees left outer join Salary on
 (Employees.Employeeid = Salary.Employeeid)
where Employees.EmployeeID in(
select EmployeeID
from salary
where InCome  < 2500);
--查询每个雇员的情况及其薪水的情况。
select Employees.*, income
from  Employees left outer join Salary on
 (Employees.Employeeid = Salary.Employeeid);
 --查询每个雇员的情况及其工作部门的情况。
select Employees.*, DepatmentName
from  Employees left outer join Departments on
 (Employees.DepartmentID = Departments.DepatmentID);
 --财务部雇员的平均收入。
 select AVG(income)
 from Salary 
 where EmployeeID in(
 select EmployeeID 
 from Employees
 where DepartmentID in (
 select depatmentid
 from Departments 
 where DepatmentName = '财务部'
 ));
 --查询财务部雇员的最高和最低收入
  select max(income)  '最高收入',min(income) '最低收入',AVG(income-outcome) '平均实际收入'
 from Salary 
 where EmployeeID in(
 select EmployeeID 
 from Employees
 where DepartmentID in (
 select depatmentid
 from Departments 
 where DepatmentName = '财务部'
 ));
 --查找Employees表中男性和女性的人数。
 select sex ,count(sex)
 from Employees 
 group by Sex ;
 --按部门列出在该部门工作的员工的人数。
 select DepartmentID ,count(DepartmentID ),
 case 
	when DepartmentID = '1' then '财务部'
	when DepartmentID = '2' then '人力资源部'
	when DepartmentID = '3' then '经理办公室'
	when DepartmentID = '4' then '研发部'
	when DepartmentID = '5' then '市场部'
	end 部门
	 from Employees 
group by DepartmentID;
 --按员工的学历分组，排出本科、大专和硕士的人数。
 select Education ,count(Education )
 from Employees 
group by Education;
--查找员工数超过2人的部门名称和员工数量
 select DepartmentID ,count(DepartmentID ),
 case 
	when DepartmentID = '1' then '财务部'
	when DepartmentID = '2' then '人力资源部'
	when DepartmentID = '3' then '经理办公室'
	when DepartmentID = '4' then '研发部'
	when DepartmentID = '5' then '市场部'
	end 部门
	 from Employees 
group by DepartmentID
having count(DepartmentID )>2;
--将各雇员的情况按收入由高到低排列
select Employees.*
from  Employees left outer join Salary on
 (Employees.Employeeid = Salary.Employeeid)
order BY INCOME DESC;

文件地址

database: 数据库系统概论代码 (gitee.com)

JUMPLANB

关注

8
点赞
踩
47

收藏

觉得还不错? 一键收藏
0
评论
数据库系统概论实验==第三章

/*实验内容*/select * from Departments;select * from Employees;select * from Salary ;--查询每个雇员的所有数据select * from Employees ;--查询地址和电话selectaddress ,phonenumberfromEmployees ;
复制链接

扫一扫