数据库实验四
本实验和前三个实验用到的表不同,题目中提供了实验示例——销售数据库:
所以首先根据所给表格通过SQL去新建数据库表,就像是第一个实验那样去做
员工人事表employee
CREATE TABLE employee(
emp_no char(5) Not null primary key check (emp_no LIKE 'E%' AND LEN(emp_no)=5),
emp_name varchar(10) Not null,
sex char(2) Not null check(sex IN('M','F')),
dept varchar(4) Not null,
title varchar(6) Not null,
date_hired datetime Not null,
birthday datetime Null,
salary int Not null,
addr varchar(50) null
)
客户表customer
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(10) Not null,
zip char(6) null
)
销售主表sales
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,
ship_date datetime Not null,
invoice_no char(10) UNIQUE
)
销货明细表sale_item
(注意:题中有两个字段都是主键。虽然一个表中不能有两个主键,但是可以两个字段组合成一个主键,也就是联合主键)
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,
)
联合主键要同时选中两个字段,然后右键点击“设置主键”即可,如图:
(当然这是可视化操作,如果想写SQL解决的参考 这篇博客)
产品名称表product
create table product(
prod_id char(5) Not null primary key,
prod_name varchar(20) Not null,
)
现在,这五个表就在原有的数据库基础上建立完成了,填数据的过程由于用SQL的INSERT过于麻烦,所以在这里就直接在可视化进行编辑了,这里的数据建议不要太少,数据合理即可
如果忘了数据库的存储过程可以参考这篇博客
(1)利用存储过程计算出“1”号订单的总金额
--首先创建存储过程
CREATE PROCEDURE sp_salelot
AS
--在BEGIN...END中写存储过程的语句
BEGIN
SELECT SUM(sale_item.qty * sale_item.unit_price)
FROM sales,sale_item
WHERE sales.order_no = '1'
END
GO
EXEC sp_salelot
如图:
(2)模糊查询职员的名字来返回个人信息,比如模糊查询“陈”姓氏的员工信息:
【感觉这里写的有点问题】
CREATE PROCEDURE sp_empname @name varchar(10)
AS
BEGIN
SELECT sale_id,order_no,SUM(tot_amt) total
FROM sales
WHERE sale_id IN(SELECT emp_no FROM employee
WHERE emp_name LIKE @name)
GROUP BY sale_id,order_no
END
GO
EXEC sp_empname '陈%'
实验四结束。