客戶訂購商品

order_schema_A0061062.sql

create database order_A0061062;
go

use order_A0061062;
go

CREATE TABLE Client_062
  (cId   CHAR(8)     NOT NULL,
   cName          NVARCHAR(30)       NOT NULL,
   cPhone         VARCHAR(10),
   fax   VARCHAR(10),
  PRIMARY  KEY (cId));

CREATE TABLE Product_062
  (pNo    CHAR(4)          NOT NULL,
   pName    VARCHAR(30),
   pUprice   INT,
  PRIMARY KEY (pNo));

CREATE TABLE Order_062
  (oNo   CHAR(3)          NOT NULL,
   cId   CHAR(8),
   oDate      DATETIME,
  PRIMARY KEY (oNo),
  FOREIGN KEY (cId) REFERENCES Client_062(cId));

CREATE TABLE OrderDetail_062
  (oNo   CHAR(3)          NOT NULL,
   pNo   CHAR(4)          NOT NULL,
   num      INT,
  PRIMARY KEY (oNo,pNo),
  FOREIGN KEY (oNo) REFERENCES Order_062(oNo),
  FOREIGN KEY (pNo) REFERENCES Product_062(pNo));
go

order_data_A0061062.sql

use order_A0061062
go

--客戶資料表:
--cid : 至少 5 位,其中一個是你, 客戶代號為你的學號
--cName :你的名字必需是中文
Insert into Client_062
   (cId, cName, cPhone, fax)
 Values
   ('A0061062', '陳奕安', '06-3210321', '06-3210330');
Insert into Client_062
   (cId, cName, cPhone, fax)
 Values
   ('A0061063', '陳二安', '07-3210321', '07-3210330');
Insert into Client_062
   (cId, cName, cPhone, fax)
 Values
   ('A0061064', '陳三安', '08-3210321', '08-3210330');
Insert into Client_062
   (cId, cName, cPhone, fax)
 Values
   ('A0061065', '陳四安', '09-3210321', '09-3210330');
Insert into Client_062
   (cId, cName, cPhone, fax)
 Values
   ('A0061066', '陳五安', '10-3210321', '10-3210330');
   go
--產品資料表
--至少輸入 5 種商品
Insert into Product_062
   (pNo, pName, pUprice)
 Values
   ('0101', '經綸歷險記', 50);
Insert into Product_062
   (pNo, pName, pUprice)
 Values
   ('0102', '汪汪歷險記', 60);
Insert into Product_062
   (pNo, pName, pUprice)
 Values
   ('0103', '喵喵歷險記', 70);
Insert into Product_062
   (pNo, pName, pUprice)
 Values
   ('0104', '哞哞歷險記', 80);
Insert into Product_062
   (pNo, pName, pUprice)
 Values
   ('0105', '啾啾歷險記', 90);
   go
--訂單檔:
--至少5 筆,且能反映出 ERD 的參與度與基數比
Insert into Order_062
   (oNo, cId, oDate)
 Values
   ('011', 'A0061062', '2005');
Insert into Order_062
   (oNo, cId, oDate)
 Values
   ('012', 'A0061062', '2005');
Insert into Order_062
   (oNo, cId, oDate)
 Values
   ('013', 'A0061062', '2005');
Insert into Order_062
   (oNo, cId, oDate)
 Values
   ('014', 'A0061063', '2005');
Insert into Order_062
   (oNo, cId, oDate)
 Values
   ('015', 'A0061064', '2005');
Insert into Order_062
   (oNo, cId, oDate)
 Values
   ('016', 'A0061064', '2005');
   go
--訂單明細
--至少輸入10 筆,且要能表達出  ERD 的參與度與基數比   
Insert into OrderDetail_062
   (oNo, pNo, num)
 Values
   ('011', '0101', 50);
Insert into OrderDetail_062
   (oNo, pNo, num)
 Values
   ('011', '0101', 20);
Insert into OrderDetail_062
   (oNo, pNo, num)
 Values
   ('012', '0101', 40);
Insert into OrderDetail_062
   (oNo, pNo, num)
 Values
   ('011', '0102', 50);
Insert into OrderDetail_062
   (oNo, pNo, num)
 Values
   ('011', '0103', 50);
Insert into OrderDetail_062
   (oNo, pNo, num)
 Values
   ('013', '0104', 50);
Insert into OrderDetail_062
   (oNo, pNo, num)
 Values
   ('014', '0104', 50);
Insert into OrderDetail_062
   (oNo, pNo, num)
 Values
   ('014', '0101', 20);
Insert into OrderDetail_062
   (oNo, pNo, num)
 Values
   ('015', '0102', 60);
Insert into OrderDetail_062
   (oNo, pNo, num)
 Values
   ('016', '0101', 10);
   go
   

order_query_A0061062.sql

use order_A0061062
--1. 4個slect  * , 列出 4 個 table 的內容.
SELECT * FROM dbo.Client_062
SELECT * FROM dbo.Order_062
SELECT * FROM dbo.OrderDetail_062
SELECT * FROM dbo.Product_062

--2. 另至少 6 個查詢,能滿足以下的要求
--每個查詢需先有題目說明  ---
--(1)有文字條件的設定,有數值欄位條件的設定,有日期條件的設定
--(1)訂單中2005年的所有資料
select * from dbo.Order_062 where year(oDate) = 2005
--(2)有 2 個以上的條件
--(2)訂單明細中數量大於20、小於50的所有資料
Select  *  from   dbo.OrderDetail_062  where  num >= 20 and num <= 50 
--(3)會用到 order by , 
--(3)前三高價格商品的所有資料
select top 3 * from Product_062 order by pUprice desc
--(4)會用的 Between .. and, 
--(4)商品價格介於60-80之間的所有資料
Select  *  from  Product_062  where pUprice between 60 and 80  
--(5)會用到 in 
--(5)訂單明細中編號為011的所有商品資料
Select  *  from  dbo.OrderDetail_062   where oNo in('011') 
--(3)查詢會join 一個以上的table
--(3)客戶姓名及訂單編號在訂單和客戶裡
SELECT dbo.Client_062.cName, dbo.Order_062.oNo
FROM dbo.Client_062
INNER JOIN dbo.Order_062
ON dbo.Client_062.cId=dbo.Order_062.cId
--(4)會用到至少2個彙總函數(SUM, AVERAGE,MAX, MIN )
--(4)訂單明細中最大單筆數量及訂單總和
SELECT MAX(num) as 最大數量,SUM(num)as 訂單總和 FROM dbo.OrderDetail_062;