北大数据库原理上机考题练习及参考 答 案 练习四(商品、部门、供应商、销售记录、供应记录).docVIP

题目：数据库原理练习2/*设有一个公司，分为若干部门，现需要保存商品、公司各部门、销售记录、供应商、供应记录的信息。其中销售记录表记录了部门与其卖出的商品的联系。供应记录表记录了供应商、购买该供应商商品的部门、供应商所提供的商品的联系。表结构的具体描述如下：商品（商品号，商品名，颜色）部门（部门号，部门名，楼层）销售记录（部门号，商品号，商品数量）供应商（供应商号，供应商名，级别）供应记录（供应商号，部门号，商品号，商品数量）请用SQL语句完成下面的功能：1. 建表，要求满足如下约束：a) 商品名、部门名、供应商名不能为空b) 销售记录的数量要求大于1000，小于50000c) 定义必要的主外码2. 往表中插入如下数据：商品（ c00001, shoe, red; c00002, shoe, blue; c00003, shoe, yellow; c00004, clothing, black; c00005, clothing, pink; c00006, clothing, red;）部门（ d00001, commodity, 1; d00002, marketing, 2; d00003, service, 2; d00004, consulting, 3;）销售记录（ d00001, c00001, 5000; d00001, c00002, 8000; d00001, c00006, 10000; d00002, c00005, 40000; d00003, c00004, 9000;）供应商（ s00001, dangdang, 0; s00002, sina, 0; s00003, 8848, 0;）供应记录（ s00001, d00001, c00001, 5000; s00001, d00002, c00005, 40000; s00002, d00001, c00002, 8000; s00002, d00001, c00006, 10000; s00003, d00003, c00004, 9000;）3. 完成如下查询a) 列出销售了所有颜色为red的商品的部门名。b) 列出每个供应商供应给每个部门的商品总量，并按总量由大到小来排序。c) 列出销售商品的总量排在第一位的部门的名称。4. 当某个供应商供应的商品数量之和每超过10000时，则该供应商的级别加一（例如供应总量为25000，则级别为二，如果又供应了6000，总量变为31000，则级别为三）；使用游标，根据现有的供应记录，来设置供应商的正确级别。5. 找出供应商品总数量排名最后的两个供应商的编号和名称。参考答案：*/drop table e075_供应记录 drop table e075_供应商drop table e075_销售记录drop table e075_部门drop table e075_商品create table e075_商品(商品号 char(6) primary key,商品名 varchar(20) not null,颜色 varchar(20))create table e075_部门(部门号 char(6) primary key,部门名 varchar(20) not null,楼层 smallint)create table e075_销售记录(部门号 char(6),商品号 char(6),商品数量 int,primary key (部门号, 商品号),foreign key (部门号) references e075_部门(部门号),foreign key (商品号) references e075_商品(商品号),check (商品数量 between 1000 and 50000))create table e075_供应商(供应商号 char(6) primary key,供应商名 varchar(20) not null,级别 tinyint)create table e075_供应记录(供应商号 char(6),部门号 char(6),商品号 char(6),商品数量 int,primary key (供应商号, 部门号, 商品号),foreign key (供应商号) references e075_供应商(供应商号),foreign key (部门号) references e075_部门(部门号),foreign key (商品号) references e075_商品(商品号))goinsert e075_商品 values(c00001, shoe, red)insert e075_商品 values(c00002, shoe, blue)insert e075_商品 values(c00