Mysql_SQL期中测试题2_练习

题目

注意：

考试时间2小时，总分100分；
考试结束试卷必须交回，不交回试卷者成绩无效。

题目：餐馆管理系统数据库设计

语言和环境
实现技术：SQL server。
环境要求：Microsoft sql server2008。
功能需求

餐馆管理系统数据库设计，要求完成数据库的创建、数据表的创建、约束的创建、数据的添加、修改、删除以及查询。

注意：考试完成后，提交数据库代码文件（.sql文件）

1.创建数据库（ReManager）
2.创建菜品类别表（FoodsType）,表结构如下：

Typeid int 主键，自增，标识种子和标识增量都是1
TypeName varchar(50) 唯一约束，不允许为空

创建菜品表（Menu）,表结构如下：

Menuid int 主键，自增，标识种子和标识增量都是1
MenuName varchar(50) 唯一约束，不允许为空
Typeid int 外键约束（与FoodsType表关联）
Price decimal(6,2) 加check约束，取值只能在0-10000之间

创建销售表（Sales）

Salesid int 主键，自增，标识种子和标识增量都是1
Menuid int 外键约束（与Menu表相关联）
SalesDate date 默认约束，默认值是系统时间
Amount decamal(10,2)

为3张表分别添加数据(方法不限)：

菜品类别表（FoodsType）数据如下：

川菜
湘菜
粤菜
浙菜
苏菜

菜品表（Menu）,数据如下：

1 麻婆豆腐 1 12
2 霸王别姬 2 18
3 鱼香肉丝 1 16
4 叫花鸡 5 25
5 脆皮乳猪 3 30
6 文昌鸡 3 28
7 西湖醋鱼 4 25
8 洞庭野鸭 2 50

销售表（Sales），数据如下：

1 2 2010-1-2 19
2 5 2011-2-3 25
3 8 2012-5-5 50
4 7 2012-8-1 25
5 1 2011-11-23 12
6 3 2009-1-1 16
7 4 2010-1-1 25
8 6 2014-3-6 28
9 1 2013-1-1 12

使用T-SQL代码实现如下题目：

1.把菜品表中文昌鸡的价格修改为 30
2.由于浙菜销售情况不好，要求把菜品类别表中的类别名为浙菜的行删掉。
3.把菜品中，菜品类别为川菜并且价格大于15的菜品的信息查询出来
4.把菜品表中的菜品按照销售价格降序排列
5.要求查询出每种菜品类别的菜品数量，菜品平均价格。并且只显示菜品数量大于1的信息
6.要求查询出菜品名，菜品价格，菜品销售日期，并且按菜品销售日期降序排
7. 查询川菜和粤菜的销售情况，显示菜名，菜品价格，菜系名称，销售日期和销售数量

评分标准

题目：菜馆管理系统数据库设计
该程序的评分标准如下：
5	新建数据库。
30	新建3张数据表，并且正确添加各种约束。
10	正确添加3张表中的测试数据。
5	把菜品表中文昌鸡的价格修改为 30。
5	把菜品类别表中的类别名为浙菜的行删掉。
5	把菜品中，菜品类别为川菜并且价格大于15的菜品的信息查询出来
5	把菜品表中的菜品按照销售价格降序排列
10	要求查询出每种菜品类别的菜品数量，菜品平均价格。并且只显示菜品数量大于1的信息
10	要求查询出菜品名，菜品价格，菜品销售日期，并且按菜品销售日期降序排列
10	查询川菜和粤菜的销售情况，显示菜名，菜品价格，菜系名称，销售日期和销售数量
5	注释和规范

解答（可能有误）

-- 1.创建数据库（ReManager）
CREATE DATABASE IF NOT EXISTS ReManager;
USE ReManager;

--   2.创建菜品类别表 （FoodsType）,表结构如下：
-- Typeid    int          主键，自增，标识种子和标识增量都是1
-- TypeName  varchar(50)  唯一约束，不允许为空
CREATE TABLE IF NOT EXISTS FoodsType (
    Typeid INT AUTO_INCREMENT PRIMARY KEY,
    TypeName VARCHAR(50) UNIQUE NOT NULL
);
-- 创建菜品表 （Menu）,表结构如下：
-- Menuid      int          主键，自增，标识种子和标识增量都是1
-- MenuName    varchar(50)  唯一约束，不允许为空
-- Typeid      int          外键约束（与FoodsType表关联）
-- Price       decimal(6,2) 加check约束，取值只能在0-10000之间
CREATE TABLE IF NOT EXISTS Menu (
    Menuid INT AUTO_INCREMENT PRIMARY KEY,
    MenuName VARCHAR(50) UNIQUE NOT NULL,
    Typeid INT,
    Price DECIMAL(6,2) CHECK (Price >= 0 AND Price <= 10000),
    FOREIGN KEY (Typeid) REFERENCES FoodsType(Typeid)
);

-- 创建销售表（Sales）
-- Salesid     int          主键，自增，标识种子和标识增量都是1
-- Menuid      int          外键约束（与Menu表相关联）
-- SalesDate   date         默认约束，默认值是系统时间
-- Amount      decamal(10,2)
CREATE TABLE IF NOT EXISTS Sales (
    Salesid INT AUTO_INCREMENT PRIMARY KEY,
    Menuid INT,
    SalesDate date DEFAULT (curdate()),
    Amount DECIMAL(10,2),
    FOREIGN KEY (Menuid) REFERENCES Menu(Menuid)
);

-- 为3张表分别添加数据(方法不限)：
-- 菜品类别表 （FoodsType）数据如下：
-- 1	川菜
-- 2	湘菜
-- 3	粤菜
-- 4	浙菜
-- 5	苏菜
INSERT INTO FoodsType (TypeName) VALUES ('川菜');
INSERT INTO FoodsType (TypeName) VALUES ('湘菜');
INSERT INTO FoodsType (TypeName) VALUES ('粤菜');
INSERT INTO FoodsType (TypeName) VALUES ('浙菜');
INSERT INTO FoodsType (TypeName) VALUES ('苏菜');

-- 菜品表 （Menu）,数据如下：
-- 1   麻婆豆腐   1   12
-- 2   霸王别姬   2   18
-- 3   鱼香肉丝   1   16
-- 4   叫花鸡     5   25
-- 5   脆皮乳猪   3   30
-- 6   文昌鸡     3   28
-- 7   西湖醋鱼   4   25
-- 8   洞庭野鸭   2   50
INSERT INTO Menu (MenuName, Typeid, Price) VALUES ('麻婆豆腐', 1, 12.00);
INSERT INTO Menu (MenuName, Typeid, Price) VALUES ('霸王别姬', 2, 18.00);
INSERT INTO Menu (MenuName, Typeid, Price) VALUES ('鱼香肉丝', 1, 16.00);
INSERT INTO Menu (MenuName, Typeid, Price) VALUES ('叫花鸡', 5, 25.00);
INSERT INTO Menu (MenuName, Typeid, Price) VALUES ('脆皮乳猪', 3, 30.00);
INSERT INTO Menu (MenuName, Typeid, Price) VALUES ('文昌鸡', 3, 28.00);
INSERT INTO Menu (MenuName, Typeid, Price) VALUES ('西湖醋鱼', 4, 25.00);
INSERT INTO Menu (MenuName, Typeid, Price) VALUES ('洞庭野鸭', 2, 50.00);

-- 销售表（Sales），数据如下：
-- 1   2   2010-1-2    19
-- 2   5   2011-2-3    25
-- 3   8   2012-5-5    50
-- 4   7   2012-8-1    25
-- 5   1   2011-11-23  12
-- 6   3   2009-1-1    16
-- 7   4   2010-1-1    25
-- 8   6   2014-3-6    28
-- 9   1   2013-1-1    12
INSERT INTO Sales (Menuid, SalesDate, Amount) VALUES (2, '2010-01-02', 19.00);
INSERT INTO Sales (Menuid, SalesDate, Amount) VALUES (5, '2011-02-03', 25.00);
INSERT INTO Sales (Menuid, SalesDate, Amount) VALUES (8, '2012-05-05', 50.00);
INSERT INTO Sales (Menuid, SalesDate, Amount) VALUES (7, '2012-08-01', 25.00);
INSERT INTO Sales (Menuid, SalesDate, Amount) VALUES (1, '2011-11-23', 12.00);
INSERT INTO Sales (Menuid, SalesDate, Amount) VALUES (3, '2009-01-01', 16.00);
INSERT INTO Sales (Menuid, SalesDate, Amount) VALUES (4, '2010-01-01', 25.00);
INSERT INTO Sales (Menuid, SalesDate, Amount) VALUES (6, '2014-03-06', 28.00);
INSERT INTO Sales (Menuid, SalesDate, Amount) VALUES (1, '2013-1-1',12.00);

-- 使用T-SQL代码实现如下题目：
-- 1.把菜品表中文昌鸡的价格修改为 30
UPDATE Menu
SET Price = 30.00
WHERE MenuName = '文昌鸡';
--  2.由于浙菜销售情况不好，要求把菜品类别表中的 类别名为 浙菜的 行删掉。
DELETE FROM FoodsType WHERE TypeName = '浙菜';
--  3.把菜品中，菜品类别为 川菜并且价格大于15的菜品的信息查询出来
SELECT *
FROM Menu
INNER JOIN FoodsType ON Menu.Typeid = FoodsType.Typeid
WHERE FoodsType.TypeName = '川菜' AND Menu.Price > 15;
--  4.把菜品表中的菜品按照销售价格降序排列
SELECT *
FROM Menu
ORDER BY Price DESC;
--  5.要求查询出每种菜品类别的 菜品数量，菜品平均价格。并且只显示菜品数量大于1的信息
SELECT FoodsType.TypeName, COUNT(Menu.Menuid) AS 菜品数量, AVG(Menu.Price) AS 平均价格
FROM FoodsType
LEFT JOIN Menu ON FoodsType.Typeid = Menu.Typeid
GROUP BY FoodsType.TypeName
HAVING COUNT(Menu.Menuid) > 1;
--  6.要求查询出 菜品名，菜品价格，菜品销售日期，并且按 菜品销售日期降序排列
SELECT Menu.MenuName, Menu.Price, Sales.SalesDate
FROM Menu
INNER JOIN Sales ON Menu.Menuid = Sales.Menuid
ORDER BY Sales.SalesDate DESC;
-- 7. 查询川菜和粤菜的销售情况，显示菜名，菜品价格，菜系名称，销售日期和销售数量
SELECT Menu.MenuName, Menu.Price, FoodsType.TypeName AS Cuisine, Sales.SalesDate, Sales.Amount
FROM Menu
INNER JOIN FoodsType ON Menu.Typeid = FoodsType.Typeid
INNER JOIN Sales ON Menu.Menuid = Sales.Menuid
WHERE FoodsType.TypeName IN ('川菜', '粤菜');

温馨提示：本文最后更新于2024-05-13 18:29:51，某些文章具有时效性，若有错误或已失效，请在下方留言或联系站长。

本站未注明转载的文章均为原创，并采用 CC BY-NC-SA 4.0 授权协议，转载请注明来源，谢谢！

分享是一种美德，当你分享时请你附带上本文链接。

THE END