《sql基础教程》学习笔记

是 否 +2 论坛币

k人 参与回答

经管之家送您一份

应届毕业生专属福利!

求职就业群

赵安豆老师微信：zhaoandou666

经管之家联合CDA

送您一个全额奖学金名额~ !

立即领取

感谢您参与论坛问题回答

经管之家送您两个论坛币！

+2 论坛币

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

扫码加我 拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

分享0 收藏0 回帖

关键词：基础教程 学习笔记 习笔记 sql Integer

还是不错的，重点在于每天能坚持。