数据库原理第一次实验报告

目录

• 任务一：（数据库CAP）
  • 1
  • 2
  • 3
  • 4
  • 5
• 任务二：（数据库GlobalToyz）
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
• 写在最后

数据库原理第一次实验报告

任务一：（数据库CAP）

1

创建数据库CAP，包含4张表Customers、Products、Agents和Orders。

复制
Use Master;
 
CREATE Database CAP;
 
Use CAP;
 
CREATE TABLE CUSTOMERS
(
	Cid char(8)  PRIMARY KEY NOT NULL,
	Cname varchar(8) NOT NULL,
	City varchar(8),
	Discnt numeric(10,2)
);
CREATE TABLE AGENTS
(
	Aid char(8)  PRIMARY KEY NOT NULL,
	Aname varchar(8) NOT NULL,
	City varchar(8),
	AgentsPercent int
);
CREATE TABLE PRODUCTS
(
	Pid char(8) PRIMARY KEY NOT NULL,
	Pname varchar(8) NOT NULL,
	City varchar(8), 
	Quantity int,
	Price numeric(10,2)
);
CREATE TABLE ORDERS
(
	Ordno char(8)  PRIMARY KEY NOT NULL,
	OrdersMonth varchar(8),
	cid varchar(8) NOT NULL,
	aid varchar(8) NOT NULL,
	pid varchar(8) NOT NULL,
	Qty int,
	Dollars numeric(10, 2)
);

运行结果：

2

利用SQL语句向4张表中增加一些示例数据。

Use master;
 
Use CAP;
 
INSERT INTO CUSTOMERS VALUES('C001',' TipTip','Duluth', 10.00);
INSERT INTO AGENTS VALUES('A01',' smith','New York', 6);
INSERT INTO PRODUCTS VALUES('P01',' comb','Dallas', 111400, 0.50);
INSERT INTO ORDERS VALUES('1011','jan','C001', 'A01', 'P01', 1000, 450.00);

3

创建一张表Orders_Jan，表的结构与Orders相同，将Orders表中month为‘Jan’的订单记录复制到表Orders_Jan中。

Use master;
 
Use CAP;
 
CREATE TABLE ORDERS_JAN
(
	Ordno char(8)  PRIMARY KEY NOT NULL,
	OrdersMonth varchar(8),
	cid varchar(8) NOT NULL,
	aid varchar(8) NOT NULL,
	pid varchar(8) NOT NULL,
	Qty int,
	Dollars numeric(10, 2)
);
 
INSERT INTO ORDERS_JAN
SELECT *
FROM ORDERS
WHERE OrdersMonth = 'jan'

运行结果：

4

将Orders表中month为‘Jan’的订单记录全部删掉。

USE master;
 
USE CAP;
 
DELETE
FROM ORDERS
WHERE OrdersMonth = 'jan'

运行结果：

5

对曾经下过金额（dollars）大于500的订单的客户，将其discnt值增加2个百分点（+2）。

use master;
 
use CAP;
 
update CUSTOMERS
set Discnt = Discnt + 2.0
WHERE Cid in(
	select distinct Cid
	from ORDERS
	where Dollars > 500
)

运行结果：

任务二：（数据库GlobalToyz）

1

对GlobalToyz，创建数据库关系图。

运行结果：

2

查找属于California州并且使用MasterCard支付的购物者的名、姓和邮件地址。

use master;
 
use GlobalToyz;
 
select vFirstName, vLastName, vEmailId
from Shopper
where cState = 'California' and vCreditCardType = 'Master Card'

运行结果：

3

查找2021年，每个购物者的ID和定单总额，并以定单总额的升序排列。

use master;
 
use GlobalToyz;
 
select Shopper.cShopperId, sum(Orders.mTotalCost) as TotalCost
from Shopper
left outer join Orders
on (Shopper.cShopperId = Orders.cShopperId) and (YEAR(Orders.dOrderDate) = 2021)
group by Shopper.cShopperId
order by TotalCost

运行结果：

4

查找在orderDetail表中vMessage为空值的行。

use master;
 
use GlobalToyz;
 
select *
from OrderDetail
WHERE OrderDetail.vMessage IS NULL

运行结果：

5

查找密码（password）长度不足6个字符的购买者的ID和姓名。

use master;
 
use GlobalToyz;
 
select cShopperId, vFirstName, vLastName
from Shopper
where len(cPassword) < 6

运行结果：

6

查找电话号码以“123”开头的收货人（Recipient）的姓名和所在的国家。

use master;
 
use GlobalToyz;
 
select DISTINCT vFirstName, vLastName, cCity
from Recipient
where cPhone like '123%'

运行结果：

7

根据pickofmonth表，查找2020年销售总量排在前三名的玩具的ID。

use master;
 
use GlobalToyz;
 
select top 3 cToyId, sum(iTotalSold)
from PickOfMonth
where iYear = 2020
group by cToyId
order by -sum(iTotalSold)

运行结果：

8

根据OrderDetail表，查找玩具总价值大于￥50的定单的号码和玩具总价值。

use master;
 
use GlobalToyz;
 
select cOrderNo, mToyCost
from OrderDetail
where mToyCost > 50

运行结果：

9

查找一份包含所有装运信息的报表，包括：Order Number, Shipment Date, Actual Delivery Date, Days in Transit. (提示：Days in Transit = Actual Delivery Date – Shipment Date)

use master;
 
use GlobalToyz;
 
select cOrderNo as Order_Number, dShipmentDate as Shipment_Date, dActualDeliveryDate as Actual_Delivery_Date,
       DATEDIFF(day, dShipmentDate, dActualDeliveryDate) as Days_in_Transit
from Shipment

运行结果：

10

查找所有玩具的名称、品牌和类别（Toy Name, Brand, Category）。

use master;
 
use GlobalToyz;
 
select Toys.vToyName as Toy_Name, ToyBrand.cBrandName as Brand, Category.cCategory as Category
from Toys
left join ToyBrand
on Toys.cBrandId = ToyBrand.cBrandId
left join Category
on Toys.cCategoryId = Category.cCategoryId

运行结果：

11

查找“Activity”这个类别的玩具的最高价格、最低价格和平均价格。

use master;
 
use GlobalToyz;
 
select MAX(Toys.mToyRate) as MaxToyRate, MIN(Toys.mToyRate) as MinToyRate, AVG(Toys.mToyRate) as AvgToyRate
from Toys
left join Category
on Toys.cCategoryId = Category.cCategoryId
where Category.cCategory = 'Activity'

运行结果：

12

查找玩具的名称和所有玩具的购物车ID。如果玩具不在购物车中，也需在结果中出现。

use master;
 
use GlobalToyz;
 
select Toys.vToyName, ShoppingCart.cCartId
from Toys
left outer join ShoppingCart
on Toys.cToyId = ShoppingCart.cToyId

运行结果：

13

以下列格式查找所有购物者的名字和他们的简称：（Initials, vFirstName, vLastName）,例如Angela Smith的Initials为A.S。

use master;
 
use GlobalToyz;
 
select (substring(vFirstName, 1, 1) + '.' + substring(vLastName, 1, 1)) as Initials, vFirstName, vLastName
from Shopper

运行结果：

14

查找“Standard Shipping”这种快递模式的单价最低的国家的名称。

use master;
 
use GlobalToyz;
 
select *
from Country
inner join ShippingRate
on Country.cCountryId = ShippingRate.cCountryID
inner join ShippingMode
on ShippingRate.cModeId = ShippingMode.cModeId
where (cMode = 'Standard Shipping' and mRatePerPound <= ALL (
    select mRatePerPound
    from ShippingRate
    ))

运行结果：

15

查找买过名称为“Kitchen Set”的玩具的购物者的ID、姓名、邮件地址及所在的城市。

use master;
 
use GlobalToyz;
 
select cShopperId, vFirstName, vLastName, vEmailId, cCity
from Shopper
where Shopper.cShopperId in (
    select Orders.cShopperId
    from Orders
    inner join ShoppingCart
    on Orders.cCartId = ShoppingCart.cCartId
    inner join Toys
    on ShoppingCart.cToyId = Toys.cToyId
    where vToyName = 'Kitchen Set'
)

运行结果：

16

查找所有购物者和收货人的名、姓、地址和所在城市，要求保留结果中的重复记录。

use master;
 
use GlobalToyz;
 
select distinct vFirstName, vLastName, vAddress, cCity
from Shopper
union all
select distinct vFirstName, vLastName, vAddress, cCity
from Recipient

运行结果：

17

查找没有包装的所有玩具的名称。（要求用子查询实现）

use master;
 
use GlobalToyz;
 
select distinct vToyName
from Toys
where cToyId not in (
    select cToyId
    from OrderDetail
    where cGiftWrap = 'Y'
)

运行结果：

18

查找已收货定单的定单号码以及下定单的时间。（要求用子查询实现）

use master;
 
use GlobalToyz;
 
select cOrderNo, dOrderDate
from Orders
where cOrderNo in (
    select cOrderNo
    from Shipment
    where cDeliveryStatus = 'd'
)

运行结果：

19

查找从来没有下过订单的购物者。

use master;
 
use GlobalToyz;
 
select *
from Shopper
where Shopper.cShopperId not in (
    select cShopperId
    from Orders
)

运行结果：

20

删除“Largo”牌的所有玩具。

use master;
 
use GlobalToyz;
 
select cToyId into T
from Toys
where Toys.cBrandId in (
    select ToyBrand.cBrandId
    from ToyBrand
    where cBrandName = 'Largo')
 
delete
PickOfMonth
from PickOfMonth, Toys
where (Toys.cToyId in (
        select cToyId
        from T))
    and
    (Toys.cToyId = PickOfMonth.cToyId)
 
delete
ShoppingCart
from ShoppingCart, Toys
where (Toys.cToyId in (
        select cToyId
        from T))
    and
    (Toys.cToyId = ShoppingCart.cToyId)
 
delete
OrderDetail
from OrderDetail, Toys
where (Toys.cToyId in (
        select cToyId
        from T))
    and
    (Toys.cToyId = OrderDetail.cToyId)
 
delete
Toys
from Toys
where Toys.cToyId in (
        select cToyId
        from T)
 
drop table T

运行结果：

写在最后

参考：

• 主要参考：数据库原理及应用教程 第4版︱微课版 （陈志泊 许福 韩慧 崔晓晖 路贺俊 阮豫红编著）
• mysql查某一年的数据_mob64ca12d80f3a的技术博客_51CTO博客
• sql 给列添加缺省 - CSDN文库.html
• SQL Sum()函数 - SQL教程
• mysql之查询前几条或者中间某几行数据_mysql 前几行-CSDN博客
• SQL – 仅选择前10行_极客教程
• SQL中的日期差函数_sql 日期差-CSDN博客
• MySQL字符串的拼接、截取、替换、查找位置-腾讯云开发者社区-腾讯云
• MySQL子查询——再难一看就懂 - 知乎
• 【MySQL】多表查询全解-【多表关系_内外自连接_子查询_多表查询案例链接】（可cv代码＆案例演示）-腾讯云开发者社区-腾讯云
• union的特性，去重与不去重 - 1156740846 - 博客园
• MySQL多表删除数据操作_极客教程
• SQL中多表连接delete删除表数据 - 温故余学 - 博客园
• SQL高级知识——临时表 - 知乎
• Sql Server 在计数(COUNT)的时候去重_sqlserver count前去重-CSDN博客