《sql基础教程》学习笔记

demo1016

1627

收藏 2020-01-07

1）第一章（完成时间：1小时）练习：

CREATE TABLE Jyushoroku

(toruku_bango INTEGER NOT NULL,
name VARCHAR(128) NOT NULL,
jyusho VARCHAR(256) NOT NULL,
tel_no CHAR(10),
mail_address CHAR(20),
PRIMARY KEY (toruku_bango));

ALTER TABLE Jyushoroku ADD COLUMN yubin_bango CHAR(8) NOT NULL;
DROP TABLE Jyushoroku;

2) 第二章（完成时间：1小时）练习：

SELECT shohin_mei, torokubi FROM Shohin WHERE torokubi >= '2009-04-28';
SELECT shohin_mei, hanbai_tanka, shiire_tanka FROM Shohin WHERE NOT hanbai_tanka - shiire_tanka < 500;
SELECT shohin_mei, shohin_bunrui, hanbai_tanka*0.9 - shirre_tanka AS rieki FROM Shohin WHERE (hanbai_tanka*0.9 - shiire_tanka) > 100 AND (shohin_mei = "办公用具" OR shohin_mei = "厨房用具");

3) 第三章（完成时间：1小时）练习：

SELECT shohin_bunrui, SUM(hanbai_tanka), SUM(shire_tanka) FROM Shohin GROUP BY shohin_bunrui HAVING SUM(hanbai_tanka) = 1.5*SUM(shire_tanka);

4）第四章（完成时间：1小时）练习：

INSERT INTO ShohinSaeki (shohin_id, shohin_mei, hanbai_tanka, shiire_tanka, saeki) SELECT '商品编号'，'商品名称'，'销售单价'，'进货单价'，'销售单价'-'进货单价' FROM Shohin;
UPDATE ShohinSaeki SET hanbai_tanka = hanbai_tanka*2 WHERE shohin_mei = '运动T恤'；
UPDATE ShohinSaeki SET saeki = hanbai_tanka - shiire_tanka WHERE shohin_mei = '运动T恤'；

5）第五章（完成时间：1小时）练习：

CREATE VIEW ViewRenshu5_1 AS SELECT shohin_mei, hanbai_tanka, torokubi FROM Shohin WHERE hanbai_tanka >= 1000 AND torokubi = ‘2009-09-20’;
SELECT shoin_id, shohin_mei, shohin_bunrui, hanbai_tanka, (SELECT AVG(hanbai_tanka) FROM Shohin) AS hanbai_tanka_all FROM Shohin;
CREATE VIEW AvgTankaByBunrui AS SELECT shoin_id, shohin_mei, shohin_bunrui, hanbai_tanka, (SELECT AVG(hanbai_tanka) FROM Shohin S2 WHERE S1.hanbai_tanka = S2.hanbai_tanka GROUP BY S1.shohin_bunrui) AS avg _hanbai_tanka FROM Shohin S1;

6）第六章（完成时间：1小时）练习：
SELECT SUM(CASE hanbai_tanka WHEN <1000 THEN 1 ELSE 0) AS low_price, SUM(CASE hanbai_tanka WHEN BETWEEN 1001 AND 3000 THEN 1 ELSE 0) AS mid_price, SUM(CASE hanbai_tanka WHEN >3000 THEN 1 ELSE 0) AS high_price FROM Shohin
7）第八章（）练习：
SELECT torokubi, shohin_mei, hanbai_tanka, SUM(hanbai_tanka) OVER(ORDER BY torokubi NULL FIRST) AS sum_tanka FROM Shohin