檔名store_select01_999.sql
相關教學文件: http://db.tt/rOsoYFqv
使用上一次建立的資料庫..
use bookstore_062
SELECT * FROM dbo.Author_062
SELECT * FROM dbo.Browse_062
SELECT * FROM dbo.Cart_062
SELECT * FROM dbo.Member_062
SELECT * FROM dbo.Order_062
SELECT * FROM dbo.Product_062
SELECT * FROM dbo.Record_062
SELECT * FROM dbo.Transaction_062
--滿足條件
/* 1. 單價等於 500的商品*/
Select * from Product_062 where unitPrice = 500
/* 2. 會員 su 的會員編號*/
Select mid from Member_062 where name='su'
/* 3. 交易方式(method)透過傳真fax購買的資料*/
Select * from Transaction_062 where method = 'fax'
/* 4. cardType以 visa 付款的的交易資料*/
Select * from Transaction_062 where cardType = 'visa'
--比較大小條件
/* 5. 單價大於 500的商品*/
Select * from Product_062 where unitPrice > 500
--多個條件 AND
/* 6. 價格介於 500 與 650 元之間的商品 */
Select * from Product_062 where unitPrice >= 500 and unitPrice <= 650
/* 7. 價格低於600元的書*/
Select * from Product_062 where catelog = 'book' and unitPrice < 600
--多個條件 OR
/*8. 交易方式透過fax 或 email 的交易資料*/
Select * from Transaction_062 where method = 'fax' or method = 'email'
--否定 Not
/* 9. 不是透過購物車的交易資料*/
Select * from Transaction_062 where not method = 'cart'
--空值 IS NULL /非空值 IS NOT NULL
/* 10. 沒有透過介紹人(introducer)的會員id, name */
Select pId, name from Member_062 where introducer is null
/* 11. 經由介紹進來的會員id, name (即introducer 不是空值 )*/
Select pId,name from Member_062 where introducer is not null
/* 12. 沒有聯絡方式的會員(即沒有email, 也沒有聯絡電話)*/
Select * from Member_062 where email is null and phone is null
--模糊相似條件 LIKE ,
/* 13. 所有的女會員…pid like '_2%'*/
Select * from Member_062 where pId like '_2%'
/* 14. 住台北市的會員姓名及地址*/
Select name, address from Member_062 where address like '台北市%'
--IN 集合條件 / Not IN 集合條件
/* 15. 透過fax ,及 email 的交易資料 請使用(in (‘fax’,’email’)*/
Select * from Transaction_062 where method in('fax','email')
/* 16. 不是透過fax ,及 email 的交易資料 (請使用not in (‘fax’,’email’)*/
Select * from Transaction_062 where method not in ('fax','email')
-- 範圍條件 BETWEEN .. AND
/* 17. 價格介於 500 與 650 元之間的商品)*/
Select * from Product_062 where unitPrice between 500 and 650
--排序(由高到低ORDER BY DESC)及排名次(TOP N, TOP N PERCENT)
/* 18. 價格由高至低列出商品資料*/
Select * from Product_062 order by unitPrice desc
/* 19. 列出價格最高的3樣商品之商品名稱及商品價格*/
Select top 3 pName,unitPrice from Product_062 order by unitPrice desc
/* 20. 列出價格在前百分之10的商品資料*/
select top 10 percent * from Product_062 order by unitPrice desc
- 使用distinct 查詢不重覆的資料
/* 21. 有購買記錄的會員 id (一個人如果有多次購買,只出現一次名字, distinct)*/
select distinct transMid from Transaction_062
/* 22. 列出有被購買過的商品編號 (同一商品只要出現一次) */
select distinct pNo from Product_062
--日期函數
/* 23. 本月份生日的會員id, name,生日 -- month(birthday)=month(getdate())*/
select mid,name,birthday from Member_062 where month(birthday)=month(getdate())
/* 24. 2003年的交易…… year函數*/
select * from dbo.Transaction_062 where year(transTime) = 2003檔名store_select02_999.sql
相關教學文件: http://db.tt/pUIdNIVs
使用上一次建立的資料庫..
/* 1. 列出商品編號,名稱與創作人姓名(product, author) */
/* 2. 列出你所出版的商品編號,商品名稱 (product, author) */
/* 3. 列出交易編號，會員id 及會員姓名 (transaction, member) */
/* 4. 列出會員id, 姓名及介紹人id,姓名 (沒有介紹人的不必列出) (member, member) P204, * /
/* 5. 列出會員id, 姓名及介紹人 id,姓名 (所有人都要列出,不管有沒有介紹人) P204, LEFT OUTER JOIN */
/* 6. 列出有被購買過的商品編號, 商品名稱 (同一商品只要出現一次) (DISTINCT) */
/* 7. 列出沒有被購買過的商品編號與名稱 (record, product, not in ) P199 In 運算子 */
use bookstore_062 SELECT * FROM dbo.Author_062; SELECT * FROM dbo.Browse_062 SELECT * FROM dbo.Cart_062 SELECT * FROM dbo.Member_062 SELECT * FROM dbo.Order_062 SELECT * FROM dbo.Product_062 SELECT * FROM dbo.Record_062 SELECT * FROM dbo.Transaction_062 /* 1. 列出商品編號,名稱與創作人姓名(product, author)*/ SELECT dbo.Product_062.pNo , pName , name FROM dbo.Product_062, dbo.Author_062 WHERE dbo.Product_062.pNo = dbo.Author_062.pNo /* 2. 列出你所出版的商品編號,商品名稱 (product, author)*/ SELECT Product_062.pNo , pName FROM dbo.Product_062 , dbo.Author_062 WHERE Product_062.pNo = Author_062.pNo and name = '張崴凱' /* 3. 列出交易編號，會員id 及會員姓名 (transaction, member)*/ SELECT tNo , Member_062.mId , name FROM Transaction_062 , Member_062 WHERE Member_062.mId = Transaction_062.transMid /* 4. 列出會員id, 姓名及介紹人id,姓名 (沒有介紹人的不必列出) (member, member) P204, */ SELECT m1.mid , m1.name , m1.introducer , m2.name FROM Member_062 as m1 , Member_062 as m2 WHERE m1.introducer = m2.mId /* 5. 列出會員id, 姓名及介紹人 id,姓名 (所有人都要列出,不管有沒有介紹人) P204, LEFT OUTER JOIN */ SELECT m1.mid , m1.name , m1.introducer , m2.name FROM Member_062 as m1 left outer join Member_062 as m2 on m1.introducer = m2.mId /* 6. 列出有被購買過的商品編號, 商品名稱 (同一商品只要出現一次) (DISTINCT)*/ select distinct Product_062.pNo , pName from Product_062 , Record_062 WHERE Product_062.pNo = Record_062.pNo /* 7. 列出沒有被購買過的商品編號與名稱 (record, product, not in ) P199 In 運算子*/ Select pNo, pName from Product_062 where pNo not in (select pNo from Record_062)
seclect03 (group分群)
相關教學文件: http://db.tt/QB4NMr7O
相關教學文件: http://db.tt/rOsoYFqv
use bookstore_062 SELECT * FROM dbo.Author_062; SELECT * FROM dbo.Browse_062 SELECT * FROM dbo.Cart_062 SELECT * FROM dbo.Member_062 SELECT * FROM dbo.Order_062 SELECT * FROM dbo.Product_062 SELECT * FROM dbo.Record_062 SELECT * FROM dbo.Transaction_062 /* (01)列出所有商品數、平均價、最高價，和最低價 (P.208 Q14)*/ SELECT COUNT(*) AS 商品數 , AVG(unitPrice) AS平均價, MAX(unitPrice) AS最高價, MIN(unitPrice) AS最低價 FROM Product_062 /* (02A)透過介紹人進來的會員人數*/ SELECT COUNT(introducer) as 透過介紹人進來的會員人數 FROM dbo.Member_062 /* (02A)住台北市的會員數*/ SELECT COUNT(*) as 住台北市的會員數 FROM Member_062 WHERE address = '台北市%' /* (02B) 2003年完成的交易數 */ select COUNT(*) as 二零零三年完成的交易數 from dbo.Transaction_062 where year(transTime)=2003 /* (02C)單價 >500元的商品數 */ select COUNT(*) as 單價高於五百元 from dbo.Product_062 where unitPrice > 500 /*(02D)12月份生日的會員數*/ select COUNT(*) as 十二月份生日 from dbo.Member_062 where MONTH(birthday) = 12 /* (03)列出類別，商品樣數*/ SELECT catalog , COUNT(*) as 列出類別商品樣數 FROM Product_062 GROUP BY catalog /* (03A) 商品樣數> 2的,請列出商品類別及商品樣數*/ SELECT catalog , COUNT(*) as 商品樣數大於二 FROM Product_062 GROUP BY catalog HAVING COUNT(*) > 2 /*(04)列出每一筆交易的交易編號和交易總金額 P208-Q15*/ SELECT tNo , SUM(salePrice) as 交易總金額 FROM Record_062 GROUP BY tNo /*(04A)列出每筆交易總金額超過???錢的交易編號及其交易總金額 (Having)*/ SELECT tNo , sum(salePrice) as 交易總金額 FROM dbo.Record_062 GROUP BY tNo Having SUM(salePrice) >2000 /* (04B)：列出每一筆上網 (method=’cart’)達成的交易之交易編號和購買商品總樣數 P208-Q16 */ SELECT Record_062.tNo , COUNT(*) as 交易編號和購買商品總樣數 FROM Record_062, dbo.Transaction_062 WHERE Record_062.tNo=dbo.Transaction_062.tNo AND method='cart' GROUP BY Record_062.tNo select tNo , COUNT(*) as 交易編號和購買商品總樣數 from Record_062 where tNo in (select tNo from Transaction_062 where method='cart') group by tNo /*(05)列出每一位會員的會員編號和交易總金額 (沒有交易的會員不必列出 P209-Q18*/ SELECT transMid , SUM(salePrice) as 會員編號和交易總金額 FROM dbo.Transaction_062 , dbo.Record_062 WHERE dbo.Transaction_062.tNo = dbo.Record_062.tNo GROUP BY transMid /*(05A)列出每一位會員的會員編號和交易總金額 (沒有交易的會員也要列出*/ SELECT mId , SUM(salPrice) as 交易總金額 FROM dbo.Member_062 LEFT OUTER JOIN dbo.Record_062 ON WHERE dbo.Transaction_062.tNo = dbo.Record_062.tNo GROUP BY mId /*(06)對每一個商品，列出其商品編號，總銷售數量及總銷售金額P208-Q16*/ SELECT pNo , SUM(amount)as 總銷售數量 , SUM(salePrice) as 總銷售金額 FROM Record_062 GROUP BY pNo /* (07)列出商品編號及作者人數 )*/ SELECT pNo , COUNT(*)as 作者人數 FROM Author_062 GROUP BY pNo /* (07A) 列出每商品的商品編號及作者人數(沒有作者的商品也要列出*/ SELECT Product_062.pNo ,COUNT(Author_062.name) as 作者人數 FROM Product_062 LEFT OUTER JOIN Author_062 ON Product_062.pNo=Author_062.pNo GROUP BY Product_062.pNo /* (08)每個月份的生日人數*/ SELECT MONTH(birthday) as 月份, COUNT(*) as 人數 FROM dbo.Member_062 GROUP BY MONTH(birthday) having month(birthday) is not null --Subquery子查詢 /* 沒有交易的會員id,姓名 */ SELECT mId, name FROM Member_062 WHERE mId NOT IN (SELECT Transmid FROM Transaction_062) /* 沒有被購買的商品 */ select * from Product_062 where pNo not in (select pNo from Record_062)store_update999.sql
6.5.1 SQL的修改語法
UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;
1.刪除會員 c0927777 所有的瀏覽記錄
2.把所有書類商品定價提高1成
3 設定你的介紹人是 jenny (請查一下 jenny 的員工代號
6.3.3 更改資料表(Alter Table)
**改變欄位
4. 在「會員」資料表新增一個sex 欄位, 固定長度1個字元的文字
**新增欄位
ALTER TABLE table_name
ADD column_name datatype
5. 將「商品」資料表的pName欄位 的寬度改為變動長度40
ALTER TABLE table_name
ALTER COLUMN column_name datatype
-- 6.將「商品」v00111 改為"A123456789B123456789C123456789D123456789"
**刪除欄位
ALTER TABLE table_name
DROP COLUMN column_name
6.5.1 SQL的修改語法 select * from dbo.Browse_062 select * from dbo.Product_062 select * from dbo.Member_062 --1.刪除會員 c0927777 所有的瀏覽記錄 select * from dbo.Browse_062 where mId='c0927777' delete dbo.Browse_062 where mId='c0927777' --2.把所有書類商品定價提高1成 select * from dbo.Product_062 where catalog='book' update dbo.Product_062 set unitPrice = unitPrice*1.1 where catalog='book' --3 設定你的介紹人是 jenny (請查一下 jenny 的員工代號 select mId from dbo.Member_062 where name='jenny' update dbo.Member_062 set introducer = (select mId from dbo.Member_062 where name='jenny') where name='陳奕安' --6.3.3 更改資料表(Alter Table) --**改變欄位 --4. 在「會員」資料表新增一個sex 欄位, 固定長度1個字元的文字 --**新增欄位 ALTER TABLE dbo.Member_062 ADD sex char(1) --5. 將「商品」資料表的pName欄位 的寬度改為變動長度40 ALTER TABLE dbo.Product_062 ALTER COLUMN pName varchar(40) -- 6.將「商品」v00111 改為"A123456789B123456789C123456789D123456789" --**刪除欄位 update dbo.Product_062 set pName = 'A123456789B123456789C123456789D123456789' where pNo = 'v00111'
沒有留言:
張貼留言