一、建表和插入数据
/******************************************************************************/
/* */
/* Kroenke and Auer - Database Processing (14th Edition) Chapter 02 */
/* */
/* The Morgan Importing (MI-CH02) Database - Create Tables */
/* */
/* These are the MySQL 5.6 code solutions */
/* */
/******************************************************************************/
CREATE TABLE ITEM (
ItemID Int NOT NULL auto_increment,
Description VarChar(255) NOT NULL,
PurchaseDate Date NOT NULL,
Store Char(50) NOT NULL,
City Char(35) NOT NULL,
Quantity Int NOT NULL,
LocalCurrencyAmount Numeric(18,2) NOT NULL,
ExchangeRate Numeric(12,6) NOT NULL,
CONSTRAINT Item_PK PRIMARY KEY (ItemID)
);
CREATE TABLE SHIPMENT (
ShipmentID Int NOT NULL auto_increment,
ShipperName Char(35) NOT NULL,
ShipperInvoiceNumber Int NOT NULL,
DepartureDate Date NULL,
ArrivalDate Date NULL,
InsuredValue Numeric(12,2) NOT NULL,
CONSTRAINT Shipment_PK PRIMARY KEY (ShipmentID)
);
CREATE TABLE SHIPMENT_ITEM (
ShipmentID Int NOT NULL,
ShipmentItemID Int NOT NULL,
ItemID Int NOT NULL,
Value Numeric(12,2) NOT NULL,
CONSTRAINT ShipmentItem_PK PRIMARY KEY(ShipmentID, ShipmentItemID),
CONSTRAINT Ship_Item_Ship_FK FOREIGN KEY(ShipmentID)
REFERENCES SHIPMENT(ShipmentID)
ON DELETE CASCADE,
CONSTRAINT Ship_Item_Item_FK FOREIGN KEY(ItemID)
REFERENCES ITEM(ItemID)
);
插入数据
/******************************************************************************/ /* */ /* Kroenke and Auer - Database Processing (14th Edition) Chapter 02 */ /* */ /* The Morgan Importing (MI-CH02) Database - Insert Data */ /* */ /* These are the MySQL 5.6 code solutions */ /* */ /******************************************************************************/ /***** ITEM Data *************************************************/ INSERT INTO ITEM VALUES( null, 'QE Dining set','2015-04-07', 'Eastern Treasures', 'Manila', 2, 403405, 0.01774); INSERT INTO ITEM VALUES( null, 'Willow Serving Dishes','2015-07-15', 'Jade Antiques', 'Singapore', 75, 102, 0.5903); INSERT INTO ITEM VALUES( null, 'Large Bureau','2015-07-17', 'Eastern Sales', 'Singapore', 8, 2000, 0.5903); INSERT INTO ITEM VALUES( null, 'Brass Lamps','2015-07-20', 'Jade Antiques', 'Singapore', 40, 50, 0.5903); COMMIT; /***** SHIPMENT Data ***************************************************/ INSERT INTO SHIPMENT VALUES( null, 'ABC Trans-Oceanic', 2008651,'2014-12-10','2015-03-15', 15000.00); INSERT INTO SHIPMENT VALUES( null, 'ABC Trans-Oceanic', 2009012,'2015-01-10','2015-03-20', 12000.00); INSERT INTO SHIPMENT VALUES( null, 'Worldwide', 49100300,'2015-05-05','2015-06-17', 20000.00); INSERT INTO SHIPMENT VALUES( null, 'International', 399400,'2015-06-02','2015-07-17', 17500.00 ); INSERT INTO SHIPMENT VALUES( null, 'Worldwide', 84899440,'2015-07-10','2015-07-28', 25000.00); INSERT INTO SHIPMENT VALUES( null, 'International', 488955,'2015-08-05','2015-09-11', 18000.00); COMMIT; /***** SHIPMENT_ITEM Data **********************************************/ INSERT INTO SHIPMENT_ITEM VALUES(3, 1, 1, 15000); INSERT INTO SHIPMENT_ITEM VALUES(4, 1, 4, 1200); INSERT INTO SHIPMENT_ITEM VALUES(4, 2, 3, 9500); INSERT INTO SHIPMENT_ITEM VALUES(4, 3, 2, 4500); COMMIT;
二、问答题
-- ITEM -- SHIPMENT -- SHIPMENT_ITEM -- A.列出所有表的数据 select * from ITEM; select * from SHIPMENT; select * from SHIPMENT_ITEM; -- B.列出所有船运的ShipmentID, ShipperName和ShipperInvoiceNumber select ShipmentID, ShipperName, ShipperInvoiceNumber from SHIPMENT; -- C.列出所有保险额超过10000元的船运的ShipmentID, ShipperName和ShipperInvoiceNumber select ShipmentID, ShipperName, ShipperInvoiceNumber from SHIPMENT where InsuredValue > 10000; -- D.列出所有名字以'AB'开头的船运的ShipmentID, ShipperName和ShipperInvoiceNumber select ShipmentID, ShipperName, ShipperInvoiceNumber from SHIPMENT where ShipperName like 'AB%'; -- E.假设Departure和ArrivalDate的格式为月/日/年.列出所有于12月出发的船运ShipmentID, -- ShipperName, ShipperInvoiceNumber和ArrivalDate select ShipmentID, ShipperName, ShipperInvoiceNumber, ArrivalDate from SHIPMENT where month(DepartureDate) = 12; -- F.假设Departure和ArrivalDate的格式为月/日/年.列出所有于某月10日出发的船运ShipmentID, -- ShipperName, ShipperInvoiceNumber和ArrivalDate select ShipmentID, ShipperName, ShipperInvoiceNumber, ArrivalDate from SHIPMENT where day(DepartureDate) = 10; -- G.确定最大值和最小值的InsuredValue select max(InsuredValue) as maxInsuredValue, min(InsuredValue) as minInsuredValue from SHIPMENT; -- H.确定平均的InsuredValue select avg(InsuredValue) from SHIPMENT; -- I.计算船运的总数 select count(*) from SHIPMENT; -- J.对于表ITEM_PURCHASE中的每一行, 显示Item, Store和一个计算得到的名为 -- StdCurrencyAmount的列。该列的值为LocalCurrencyAmt乘以ExchangeRate select ItemID, Store, LocalCurrencyAmount * ExchangeRate as StdCurrencyAmount from ITEM; -- K.按照City和Store对购买的物品进行分组 select ITEM.City, ITEM.Store from ITEM group by ITEM.City, ITEM.Store; -- L.按照City和Store对购买的物品进行分组, 计算每组的数目 select ITEM.City, ITEM.Store, count(*) as n from ITEM group by ITEM.City, ITEM.Store; -- M.使用子查询, 显示所有包含单价超过1000的物品船运的ShipperName -- ShipmentID和DepartureDate. 结果先按照ShipperName升序排序, 再按照DepartureDate降序排序 select SHIPMENT.ShipperName, SHIPMENT.ShipmentId, SHIPMENT.DepartureDate from SHIPMENT where SHIPMENT.ShipmentId in (select distinct SHIPMENT_ITEM.ShipmentID from SHIPMENT_ITEM where Value > 1000) order by SHIPMENT.ShipperName asc, SHIPMENT.DepartureDate desc; -- O.使用子查询, 显示所有包含在新加坡购买的物品船运的ShipperName -- ShipmentID和DepartureDate. 结果先按照ShipperName升序排序, 再按照DepartureDate降序排序s select SHIPMENT.ShipperName, SHIPMENT.ShipmentId, SHIPMENT.DepartureDate from SHIPMENT where SHIPMENT.ShipmentId in (select ShipmentID from SHIPMENT_ITEM where SHIPMENT_ITEM.ItemID in (select ItemId from ITEM where ITEM.City = 'Singapore') ) order by SHIPMENT.ShipperName asc, SHIPMENT.DepartureDate desc;