实验室3 sql server 产品销售数据库

某公司的产品销售数据库company,company数据库中存在人事表employee、客户表customer、销售表sales、销售明细表sale_item、产品表product。各表的结构如下:

员工人事表employee

emp_no

char(5)

Not null

primary key

员工编号

emp_name

varchar(10)

Not null

员工姓名

sex

char(2)

Not null

性别

dept

varchar(10)

Not null

所属部门

title

varchar(10)

Not null

职称

date_hired

datetime

Not null

雇佣日

birthday

datetime

Null

生日

salary

int

Not null

薪水

telephone

varchar(20)

Null

电话

addr

varchar(50)

null

住址

客户表customer

cust_id

char(5)

Not null

primary key

客户号

cust_name

varchar(20)

Not null

客户名称

addr

varchar(40)

Not null

客户住址

tel_no

varchar(20)

Not null

客户电话

zip

char(6)

null

邮政编码

销售主表sales

order_no

int

Not null

primary key

订单编号

cust_id

char(5)

Not null

客户号

sale_id

char(5)

Not null

业务员编号

tot_amt

numeric(9,2)

Not null

订单金额

order_date

datetime

Not null

订货日期

销货明细表sale_item

order_no

int

Not null

订单编号

prod_id

char(5)

Not null

产品编号

qty

int

Not null

销售数量

unit_price

numeric(7,2)

Not null

单价

order_date

datetime

null

订单日期

产品名称表product

prod_id

char(5)

Not null

primary key

产品编号

prod_name

varchar(20)

Not null

产品名称

分别在SQL SERVER 2000企业管理器和在查询分析分析器中使用T—SQL语句完成以下操作:

1.在数据库company中创建以上五张表,并设置各表的主键。

操作步骤:

/*员工人事表*/
create table employee (
	emp_no	char(5)	Not null primary key,
	emp_name	varchar(10)	Not null,
	sex	char(2)	Not null,
	dept	varchar(10)	Not null,
	title	varchar(10)	Not null,
	date_hired	datetime	Not null,
	birthday	datetime	Null,
	salary	int	Not null,
	telephone	varchar(20)	Null,
	addr	varchar(50)	null
)
/*客户表*/
create table customer(
	cust_id	char(5)	Not null	primary key,
	cust_name	varchar(20)	Not null,	
	addr	varchar(40)	Not null,	
	tel_no	varchar(20)	Not null,	
	zip	char(6)	null
)
/*销售主表*/
create table sales( 
	order_no	int	Not null	primary key,
	cust_id	char(5)	Not null	,
	sale_id	char(5)	Not null	,
	tot_amt	numeric(9,2)	Not null,	
	order_date	datetime	Not null	
)
/*销货明细表*/
create table sale_item(
	order_no	int	Not null,
	prod_id	char(5)	Not null,
	qty	int	Not null,
	unit_price	numeric(7,2)	Not null,
	order_date	datetime	null,
)
/*产品名称表*/
create table product(
	prod_id	char(5)	Not null	primary key,
	prod_name	varchar(20)	Not null,	
)

/*人事表(employee)数据:*/

insert employeevalues('E0001','王大华','男','业务','经理','1976-10-13','1951-08-01',8000,'0218120440','上海市')

insert employeevalues('E0003','陈自强','男','会计','科长','1986-09-15','1963-06-09',4800,'0255344441','南京市')

insert employeevalues('E0014','周小梅','女','业务','职员','1996-03-01','1970-03-28',3200,'0218128079','上海市')

insert employeevalues('E0009','陈建国','男','管理','科长','1987-04-15','1967-09-01',4500,'0224507863','天津市')

insert employeevalues('E0017','林光华','男','业务','职员','1995-10-13','1973-08-17',3000,'0218344560','上海市')

insert employeevalues('E0006','李珠珠','女','管理','经理','1988-01-01','1961-07-12',6000,'0106750321','北京市')

insert employeevalues('E0002','李庄敬','男','人事','科长','1980-09-15','1958-05-13',8000,'0207180787','广州市')

insert employeevalues('E0010','王成功','男','信息','职员','1993-02-15','1969-04-15',4500,'0106543475','北京市')

insert employeevalues('E0013','陈中华','男','业务','职员','1993-02-15','1966-07-01',4300,'0224506541','天津市')

insert employeevalues('E0008','刘  刚','男','业务','职员','1994-11-01','1968-08-01',4000,'0218128727','上海市')

insert employeevalues('E0005','李珊珊','女','会计','职员','1990-03-20','1967-04-25',3800,'0218344787','上海市')

insert employeevalues('E0011','李小蓉','女','人事','职员','1994-11-01','1970-11-18',3000,'0811545412','重庆市')

insert employeevalues('E0012','蔡文钦','男','制造','厂长','1984-08-15','1960-07-21',5000,'0218120636','上海市')

insert employeevalues('E0015','张大山','男','制造','职员','1993-12-15','1968-09-23',3500,'0218344472','上海市')

insert employeevalues('E0007','吴铁雄','男','信息','科长','1989-10-01','1965-04-18',5000,'0277758521','武汉市')

insert employeevalues('E0016','方美美','女','业务','职员','1992-05-20','1966-06-23',4000,'0218507470','上海市')

insert employeevalues('E0004','刘中兴','男','制造','经理','1984-05-01','1960-05-23',6000,'0218506110','上海市')

insert employeevalues('E0019','王仁华','男','信息','经理','1985-09-15','1959-03-24',6000,'0218128091','上海市')

insert employeevalues('E0020','陈火旺','男','业务','职员','1992-08-01','1964-05-12',4000,'0224120477','天津市')

insert employeevalues('E0018','林中正','男','管理','总经理','1974-10-01','1953-05-04',10000,'0218120564','上海市')

/*  客户(customer)表数据:*/

insert customervalues('C0001','ABC股份有限公司','上海市','电话甲','100')

insert customervalues('C0002','客户乙','天津市','电话乙','400')

insert customervalues('C0003','客户丙','北京市','电话丙','800')

insert customervalues('C0004','客户丁','上海市','电话丁','104')

insert customervalues('C0005','客户戊','北京市','电话戊','803')

insert customervalues('C0006','客户己','上海市','电话己','103')

insert customervalues('C0007','客户庚','上海市','电话庚','102')

/*销售(sales)主表数据:*/

insertsales values (10002,'C0002','E0013',22700.00,'1996-11-10','I000000002')

insertsales values(10003,'C0003','E0014',13960.00,'1996-10-15','I000000003')

insertsales values(10004,'C0003','E0014',33000.00,'1996-11-10','I000000004')

insertsales values (10001,'C0001','E0008',60000.00,'1996-11-10','I000000001')

insertsales values (10007,'C0007','E0008',20000.00,'1996-11-10','I000000008')

/*产品名称(product)表数据:*/

insertproduct values ('P0001','16M DRAM')

insertproduct values ('P0002','14寸显示器')

insertproduct values ('P0003','1.2GB硬盘')

insertproduct values ('P0004','3.5寸软驱')

insertproduct values ('P0005','键盘')

insertproduct values ('P0006','VGA显示卡')

insertproduct values ('P0007','网卡')

insertproduct values ('P0008','Pentium100CPU')

insertproduct values ('P0009','激光打印机')

insertproduct values ('P0010','8倍速光驱')

insertproduct values ('P0011','计算机字典')

insertproduct values('P0012','9600bits-s调制解调器')

insertproduct values('P0013','Pentium主板')

/*销售明细表(sale_item)数据:*/

insertsale_item values (10001,'P0001',5 , 2500.00,'1996-10-22')

insertsale_item values (10001,'P0002',3 , 6500.00,'1996-10-22')

insertsale_item values (10001,'P0003',2 , 5300.00,'1996-10-22')

insertsale_item values (10001,'P0004',2 , 1600.00,'1996-10-22')

insertsale_item values (10002,'P0001',3 , 2600.00,'1996-11-10')

insertsale_item values (10002,'P0003',1 , 5300.00,'1996-11-10')

insertsale_item values (10002,'P0008',2 , 4800.00,'1996-11-10')

insertsale_item values (10003,'P0001',4 , 2700.00,'1996-10-15')

insertsale_item values (10003,'P0004',2 , 1580.00,'1996-10-15')

2.在销售主表sales中添加字段“发票号码” invoice_no,char(10),NOT NULL。

alter table sales add invoice_no char(10) NOT NULL;

3.添加外键约束:

a)       在销售主表sales的业务员编号字段sale_id上添加外键约束,参照字段为员工表employee中的字段员工编号emp_no,约束名为FK_sale_id。

alter table sales add foreign key (sale_id) references employee(emp_no);

b)       在销售主表sales的客户号字段cust_id上添加外键约束,参照字段为客户表customer中的字段客户号cust_id,约束名为FK_cust_id。

alter table sales add foreign key (cust_id) references customer(cust_id);

c)       在销售明细表sale_item的订单编号字段order_no上添加外键约束,参照字段为销售主表sales中的字段订单编号order_no,约束名为FK_order_no。

alter table sale_item add constraint FK_order_no foreign key(order_no) references sales(order_no)

d)      在销售明细表sale_item的产品编号字段prod_id上添加外键约束,参照字段为产品名称表product中的产品编号字段prod_id,约束名为FK_prod_id。

alter table sale_item add constraint FK_order_no foreign key(prod_id) references product(prod_id)

4.添加核查约束:

a) 将员工表employee中的薪水字段salary的值限定在1000至10000间,约束名为CK_salary。

alter table employee add constraint CK_salary check(salary between 1000 and 10000);

b) 将员工表employee中的员工编号字段emp_no设定为以“E”字母开头, 后面跟4位数的编号,约束名为CK_emp_no。

alter table employee add constraint CK_emp_no check(emp_nolike'E____')

c)     将员工表employee中的性别字段设定这取值只能是“男”和“女”。约束名为CK_sex。

alter table employee add constraint CK_sex check(sexin('男','女'))

d)    将销售主表sales中的发票号码字段invoice_no设定为以“I”字母开头,后面跟9位数的编号,约束名为CK_inno。

alter table sales add constraintCK_innocheck(invoice_nolike'I_________')

5.为销售主表sales中的字段发票号码invoice_no设置为唯一约束,约束名为UN_inno。

alter table  sales add constraint UN_inno unique(invoice_no)

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏freesan44

关于 iOS 10 中 ATS 的问题

WWDC 15 提出的 ATS (App Transport Security) 是 Apple 在推进网络通讯安全的一个重要方式。在 iOS 9 和 OS X...

21920
来自专栏SmartSql

SmartCode.ETL 这不是先有鸡还是蛋的问题!

相信不少同学都用过各种代码生成器,这里我就不做详细介绍了,如果想体验 SmartCode.Generator 请至 https://www.cnblogs.co...

21360
来自专栏cmazxiaoma的架构师之路

蛋疼的ElasticSearch(四)之基本用法、高级查询

ElasticSearch系列文章: 1.蛋疼的ElasticSearch(一)之安装ElasticSearch 2.蛋疼的ElasticSearch(二)...

10140
来自专栏杨建荣的学习笔记

关于纠结的recycle pool的设置(r3笔记第5天)

现在生产环境中目前有一个很大的中继表,作为多个流程的数据流动所用,数据量很大。里面有clob字段,加上庞大的数据量,表就显得很臃肿了。 目前在做大批量的数据处理...

35270
来自专栏铭毅天下

抢先 | 支持sql的Elasticsearch6.3全景概览

像操作Mysql一样使用Elasticsearch,缩减DSL的学习成本,更多人爱上ES的特性。

24940
来自专栏web编程技术分享

sql实现简单自增长

32260
来自专栏Greenplum

Greenplum常用导数据方法及性能测试

# 生成隐藏字符 soh=`echo 1 | awk '{printf("%c", $1)}'` replaceWord=$1

88340
来自专栏24K纯开源

Premiere&After Effects的实时预览插件开发

一、介绍         Adobe Premiere和After Effects在影视编辑、渲染领域已经得到广泛应用。全景视频在相应工具拼接好后也可以导入Pr...

25990
来自专栏腾讯Bugly的专栏

黑科技:程序员如何打造属于自己的分体键盘

前言 作为一名程序员,键盘在手,天下我有啊,不整个高大上的键盘怎么提升B 格。之前一直想买个机械键盘,听说机械键盘敲代码时格外舒爽,实在是提升效率与 B 格的神...

38750
来自专栏张善友的专栏

SQL Express - Client Synchronization Sample

Synchronization Services for ADO.NET 是微软推出的同步框架( Microsoft Synchronization Frame...

22890

扫码关注云+社区

领取腾讯云代金券