数据库上机题

现有一个商店的数据库,记录顾客及其购物情况,由下面三个表组成：

商品(商品号,商品名,单价,商品类别,供应商);

顾客(顾客号,姓名,住址);

购买(顾客号,商品号,购买数量);

建表,在定义中要求声明：

1)每个表的主外码;

2)顾客的姓名和商品名不能为空值;

3)单价必须大于0,购买数量必须在0到20之间;

1.往表中插入数据：

商品(

M01,佳洁士,8.00,牙膏,宝洁;

M02,高露洁,6.50,牙膏,高露洁;

M03,洁诺,5.00,牙膏,联合利华;

M04,舒肤佳,3.00,香皂,宝洁;

M05,夏士莲,5.00,香皂,联合利华;

M06,雕牌,2.50,洗衣粉,纳爱斯;

M07,中华,3.50,牙膏,联合利华;

M08,汰渍,3.00,洗衣粉,宝洁;

M09,碧浪,4.00,洗衣粉,宝洁

)

顾客(C01,Dennis,海淀;

C02,John,朝阳;

C03,Tom,东城;

C04,Jenny,东城;

C05,Rick,西城;)

购买(C01,M01,3;C01,M05,2;

C01,M08,2;C02,M02,5;

C02,M06,4;C03,M01,1;

C03,M05,1;C03,M06,3;

C03,M08,1;C04,M03,7;

C04,M04,3;C05,M06,2;

C05,M07,8;)

2.用SQL语句完成下列查询：

(1)检索购买了供应商"宝洁"产品的所有顾客；

(2)检索买的商品包括了顾客"Dennis"所购买商品的顾客(姓名)；

(3)检索牙膏卖出数量最多的供应商。

(4)检索至少购买了两种商品的所有顾客

(5)检索全部顾客都购买过的商品

(6)将所有的牙膏商品单价增加10%。

(7)删除从未被购买的商品记录。

现要为学校建立一个数据库,设学校环境如下：一个系有若干个专业,每个专业一年只招一个班,每个班有若干个学生..现要建立关于系、学生、班级的数据库,关系模式为：学生STUDENT(学号,姓名,年龄,班号)

班CLASS(班号,专业名,系名,入学年份)

系DEPARTMENT(系号,系名)

试用SQL语言完成以下功能：

1建表,在定义中要求声明：

(1)每个表的主外码.

(2)每个班级的人数不能超过30人.

(3)学生的年龄介于15到40岁之间.

(4)学生姓名不能为空.

(5)只有班里没有学生的时候,才能删除该班级.

2插入如下数据

CLASS(

101,软件,计算机,1995;102,微电子,计算机,1996;

111,无机化学,化学,1995;112,高分子化学,化学,1996;

121,统计数学,数学,1995;131,现代语言,中文,1996;

141,国际贸易,经济,1997;142,国际金融,经济,1996;

)

STUDENT(

8101,张三,18,101;8102,钱四,16,121;

8103,王玲,17,131;8105,李飞,19,102;

8109,赵四,18,141;8110,李可,20,142;

8201,张飞,18,111;8302,周瑜,16,112;

8203,王亮,17,111;8305,董庆,19,102;

8409,赵龙,18,101;8510,李丽,20,142

)

DEPARTMENT(

001,数学;

002,计算机;

003,化学;

004,中文;

005,经济;

)

3完成以下查询功能

(1)找出所有姓李的学生,并按其年龄由小到大排序.

(2)列出所有开设超过两个专业的系的名字.

(3)列出学生人数大于等于2的系的编号和名称.

4学校又新增加了一个物理系,编号为006.

5学生张三转到化学系111班,请更新相关的表.

设计零售公司的数据库：

售货员(员工号,姓名,住址),商品(商品号,商品名,单价),制造商(制造商名,制造商地址,电话),多个商品可以由一个售货员售出,一个商品也可以由多个人员售出,一件商品可以有多个制造商. 要求：

1建表：要有主外码定义,商品价格大于0.

2插入：每个表最少4条,保证查询结果不为空.

3找出Mike卖出的所有商品的商品名.

4找出提供商品种类最多的制造商名.

5更新：将“统一冰红茶“的价格降低3%.

6删除：单价低于一元的商品.

有某个学生运动会比赛信息的数据库,保存了如下的表：

运动员(运动员编号,运动员姓名,运动员性别,所属系号)

项目(项目编号,项目名称,项目比赛地点)

成绩(运动员编号,项目编号,积分)

请用SQL语句完成如下功能：

1.建表,注意满足如下要求：

a)表名必须加自己的帐号作前缀,比如“d1*******运动员”

b)定义各个表的主码外码约束

c)运动员的姓名和所属系别不能为空值.

d)积分要么为空值,要么为6,4,2,0,分别代表第一,二,三名和其他名次的积分,注意名次可以有并列名次,后面的排名不往前提升,例如,如果有两个并列第一,则没有第二名.

2.往表中插入数据：

运动员(

1001,李明,男,计算机系

1002,张三,男,数学系

1003,李四,男,计算机系

1004,王二,男,物理系

1005,李娜,女,心理系

1006,孙丽,女,数学系)

项目(x001,男子五千米,一操场

x002,男子标枪,一操场

x003,男子跳远,二操场

x004,女子跳高,二操场x005,女子三千米,三操场)

积分(1001,x001,6

1002,x001,4

1003,x001,2

1004,x001,0

1001,x003,4

1002,x003,6

1004,x003,2

1005,x004,6

1006,x004,4)

3.完成如下查询

a)求出目前总积分最高的系名,及其积分.

b)找出在一操场进行比赛的各项目名称及其冠军的姓名c)找出参加了张三所参加的所有项目的其他同学的姓名4.经查张三因为使用了违禁药品,其成绩都记0分,请在数据库中作出相应修改.

5.经组委会协商,需要删除女子跳高比赛项目.