SQL必知必会

SQL必知必会(Book 【美】 Ben Forta)

首先创建表 和 测试数据,之后是各章中的基于Oracle DB的SQL 练习语句。

创建表

 1 ---------------------------------------------
 2 -- Sams Teach Yourself SQL in 10 Minutes
 3 -- http://forta.com/books/0672336073/
 4 -- Example table creation scripts for Oracle.
 5 ---------------------------------------------
 6 
 7 
 8 -------------------------
 9 -- Create Customers table
10 -------------------------
11 CREATE TABLE Customers
12 (
13   cust_id      char(10)  NOT NULL ,
14   cust_name    char(50)  NOT NULL ,
15   cust_address char(50)  NULL ,
16   cust_city    char(50)  NULL ,
17   cust_state   char(5)   NULL ,
18   cust_zip     char(10)  NULL ,
19   cust_country char(50)  NULL ,
20   cust_contact char(50)  NULL ,
21   cust_email   char(255) NULL 
22 );
23 
24 --------------------------
25 -- Create OrderItems table
26 --------------------------
27 CREATE TABLE OrderItems
28 (
29   order_num  int          NOT NULL ,
30   order_item int          NOT NULL ,
31   prod_id    char(10)     NOT NULL ,
32   quantity   int          NOT NULL ,
33   item_price decimal(8,2) NOT NULL 
34 );
35 
36 ----------------------
37 -- Create Orders table
38 ----------------------
39 CREATE TABLE Orders
40 (
41   order_num  int      NOT NULL ,
42   order_date date     NOT NULL ,
43   cust_id    char(10) NOT NULL 
44 );
45 
46 ------------------------
47 -- Create Products table
48 ------------------------
49 CREATE TABLE Products
50 (
51   prod_id    char(10)      NOT NULL ,
52   vend_id    char(10)      NOT NULL ,
53   prod_name  char(255)     NOT NULL ,
54   prod_price decimal(8,2)  NOT NULL ,
55   prod_desc  varchar(1000) NULL 
56 );
57 
58 -----------------------
59 -- Create Vendors table
60 -----------------------
61 CREATE TABLE Vendors
62 (
63   vend_id      char(10) NOT NULL ,
64   vend_name    char(50) NOT NULL ,
65   vend_address char(50) NULL ,
66   vend_city    char(50) NULL ,
67   vend_state   char(5)  NULL ,
68   vend_zip     char(10) NULL ,
69   vend_country char(50) NULL 
70 );
71 
72 ----------------------
73 -- Define primary keys
74 ----------------------
75 ALTER TABLE Customers ADD CONSTRAINT PK_Customers PRIMARY KEY (cust_id);
76 ALTER TABLE OrderItems ADD CONSTRAINT PK_OrderItems PRIMARY KEY (order_num, order_item);
77 ALTER TABLE Orders ADD CONSTRAINT PK_Orders PRIMARY KEY (order_num);
78 ALTER TABLE Products ADD CONSTRAINT PK_Products PRIMARY KEY (prod_id);
79 ALTER TABLE Vendors ADD CONSTRAINT PK_Vendors PRIMARY KEY (vend_id);
80 
81 ----------------------
82 -- Define foreign keys
83 ----------------------
84 ALTER TABLE OrderItems
85 ADD CONSTRAINT FK_OrderItems_Orders FOREIGN KEY (order_num) REFERENCES Orders (order_num);
86 ALTER TABLE OrderItems
87 ADD CONSTRAINT FK_OrderItems_Products FOREIGN KEY (prod_id) REFERENCES Products (prod_id);
88 ALTER TABLE Orders
89 ADD CONSTRAINT FK_Orders_Customers FOREIGN KEY (cust_id) REFERENCES Customers (cust_id);
90 ALTER TABLE Products
91 ADD CONSTRAINT FK_Products_Vendors FOREIGN KEY (vend_id) REFERENCES Vendors (vend_id);

创建测试数据

  1 -----------------------------------------------
  2 -- Sams Teach Yourself SQL in 10 Minutes
  3 -- http://forta.com/books/0672336073/
  4 -- Example table population scripts for Oracle.
  5 -----------------------------------------------
  6 
  7 
  8 ---------------------------
  9 -- Populate Customers table
 10 ---------------------------
 11 INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
 12 VALUES('1000000001', 'Village Toys', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'John Smith', 'sales@villagetoys.com');
 13 INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
 14 VALUES('1000000002', 'Kids Place', '333 South Lake Drive', 'Columbus', 'OH', '43333', 'USA', 'Michelle Green');
 15 INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
 16 VALUES('1000000003', 'Fun4All', '1 Sunny Place', 'Muncie', 'IN', '42222', 'USA', 'Jim Jones', 'jjones@fun4all.com');
 17 INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
 18 VALUES('1000000004', 'Fun4All', '829 Riverside Drive', 'Phoenix', 'AZ', '88888', 'USA', 'Denise L. Stephens', 'dstephens@fun4all.com');
 19 INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
 20 VALUES('1000000005', 'The Toy Store', '4545 53rd Street', 'Chicago', 'IL', '54545', 'USA', 'Kim Howard');
 21 
 22 -------------------------
 23 -- Populate Vendors table
 24 -------------------------
 25 INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
 26 VALUES('BRS01','Bears R Us','123 Main Street','Bear Town','MI','44444', 'USA');
 27 INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
 28 VALUES('BRE02','Bear Emporium','500 Park Street','Anytown','OH','44333', 'USA');
 29 INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
 30 VALUES('DLL01','Doll House Inc.','555 High Street','Dollsville','CA','99999', 'USA');
 31 INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
 32 VALUES('FRB01','Furball Inc.','1000 5th Avenue','New York','NY','11111', 'USA');
 33 INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
 34 VALUES('FNG01','Fun and Games','42 Galaxy Road','London', NULL,'N16 6PS', 'England');
 35 INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
 36 VALUES('JTS01','Jouets et ours','1 Rue Amusement','Paris', NULL,'45678', 'France');
 37 
 38 --------------------------
 39 -- Populate Products table
 40 --------------------------
 41 INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
 42 VALUES('BR01', 'BRS01', '8 inch teddy bear', 5.99, '8 inch teddy bear, comes with cap and jacket');
 43 INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
 44 VALUES('BR02', 'BRS01', '12 inch teddy bear', 8.99, '12 inch teddy bear, comes with cap and jacket');
 45 INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
 46 VALUES('BR03', 'BRS01', '18 inch teddy bear', 11.99, '18 inch teddy bear, comes with cap and jacket');
 47 INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
 48 VALUES('BNBG01', 'DLL01', 'Fish bean bag toy', 3.49, 'Fish bean bag toy, complete with bean bag worms with which to feed it');
 49 INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
 50 VALUES('BNBG02', 'DLL01', 'Bird bean bag toy', 3.49, 'Bird bean bag toy, eggs are not included');
 51 INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
 52 VALUES('BNBG03', 'DLL01', 'Rabbit bean bag toy', 3.49, 'Rabbit bean bag toy, comes with bean bag carrots');
 53 INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
 54 VALUES('RGAN01', 'DLL01', 'Raggedy Ann', 4.99, '18 inch Raggedy Ann doll');
 55 INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
 56 VALUES('RYL01', 'FNG01', 'King doll', 9.49, '12 inch king doll with royal garments and crown');
 57 INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
 58 VALUES('RYL02', 'FNG01', 'Queen doll', 9.49, '12 inch queen doll with royal garments and crown');
 59 
 60 ------------------------
 61 -- Populate Orders table
 62 ------------------------
 63 INSERT INTO Orders(order_num, order_date, cust_id)
 64 VALUES(20005, TO_DATE('2012-05-01', 'yyyy-mm-dd'), '1000000001');
 65 INSERT INTO Orders(order_num, order_date, cust_id)
 66 VALUES(20006, TO_DATE('2012-01-12', 'yyyy-mm-dd'), '1000000003');
 67 INSERT INTO Orders(order_num, order_date, cust_id)
 68 VALUES(20007, TO_DATE('2012-01-30', 'yyyy-mm-dd'), '1000000004');
 69 INSERT INTO Orders(order_num, order_date, cust_id)
 70 VALUES(20008, TO_DATE('2012-02-03', 'yyyy-mm-dd'), '1000000005');
 71 INSERT INTO Orders(order_num, order_date, cust_id)
 72 VALUES(20009, TO_DATE('2012-02-08', 'yyyy-mm-dd'), '1000000001');
 73 
 74 ----------------------------
 75 -- Populate OrderItems table
 76 ----------------------------
 77 INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
 78 VALUES(20005, 1, 'BR01', 100, 5.49);
 79 INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
 80 VALUES(20005, 2, 'BR03', 100, 10.99);
 81 INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
 82 VALUES(20006, 1, 'BR01', 20, 5.99);
 83 INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
 84 VALUES(20006, 2, 'BR02', 10, 8.99);
 85 INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
 86 VALUES(20006, 3, 'BR03', 10, 11.99);
 87 INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
 88 VALUES(20007, 1, 'BR03', 50, 11.49);
 89 INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
 90 VALUES(20007, 2, 'BNBG01', 100, 2.99);
 91 INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
 92 VALUES(20007, 3, 'BNBG02', 100, 2.99);
 93 INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
 94 VALUES(20007, 4, 'BNBG03', 100, 2.99);
 95 INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
 96 VALUES(20007, 5, 'RGAN01', 50, 4.49);
 97 INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
 98 VALUES(20008, 1, 'RGAN01', 5, 4.99);
 99 INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
100 VALUES(20008, 2, 'BR03', 5, 11.99);
101 INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
102 VALUES(20008, 3, 'BNBG01', 10, 3.49);
103 INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
104 VALUES(20008, 4, 'BNBG02', 10, 3.49);
105 INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
106 VALUES(20008, 5, 'BNBG03', 10, 3.49);
107 INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
108 VALUES(20009, 1, 'BNBG01', 250, 2.49);
109 INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
110 VALUES(20009, 2, 'BNBG02', 250, 2.49);
111 INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
112 VALUES(20009, 3, 'BNBG03', 250, 2.49);

 

各章SQL练习语句

  1 
-- '_'下划线通配符,只匹配一个字符;'%' 通配符,匹配 0 个、1 个 或 多个任意字符,NULL除外。
SELECT prod_id,prod_name 2 FROM Products 3 WHERE prod_name LIKE '__ inch teddy bear %'; 4 -- 方括号[] 通配符用来指定一个字符集。
-- 例如,找出所有名字以 J 或 M 起头的联系人,可进行如下查询。 5 SELECT cust_contact 6 FROM Customers 7 WHERE cust_contact LIKE '[JM]%' 8 ORDER BY cust_contact; 9 10 SELECT prod_name 11 FROM Products 12 WHERE NOT vend_id = 'DLL01' 13 ORDER BY prod_name; 14 15 SELECT RTRIM(vend_name) || ' (' || RTRIM(VEND_COUNTRY) || ')' AS vend_title 16 FROM Vendors 17 ORDER BY vend_name; 18 19 SELECT vend_name || ' (' || VEND_COUNTRY || ')' 20 FROM Vendors 21 ORDER BY vend_name; 22 23 SELECT prod_id ,quantity, item_price,quantity * item_price AS expanded_price 24 FROM OrderItems 25 WHERE order_num = 20008; 26 27 SELECT vend_name ,UPPER(vend_name) AS vned_name_upcase 28 FROM Vendors 29 ORDER BY vend_name; 30 31 SELECT cust_name,cust_contact 32 FROM Customers 33 WHERE SOUNDEX(cust_contact) = SOUNDEX('Michael Green'); 34 35 SELECT order_num 36 FROM Orders 37 WHERE to_number(to_char(order_date, 'YYYY')) = 2012; 38 39 SELECT AVG(DISTINCT prod_price) as avg_price 40 FROM Products 41 WHERE vend_id = 'DLL01'; 42 SELECT * -- count(cust_email) AS num_cust 43 FROM Customers 44 45 46 SELECT COUNT(*) AS num_prods 47 FROM Products 48 WHERE vend_id = 'DLL01'; 49 50 SELECT vend_id, COUNT(*) AS num_prods 51 FROM Products 52 GROUP BY vend_id; 53 54 SELECT cust_id, COUNT(*) AS orders 55 FROM Orders 56 GROUP BY cust_id 57 HAVING COUNT(*) >= 2; 58 --HAVING to_number(cust_id) >= 1000000001; 59 60 SELECT vend_id , COUNT(*) AS num_pords 61 FROM Products 62 --WHERE prod_price >=4 63 GROUP BY vend_id 64 HAVING COUNT(*) >=2 ; 65 66 SELECT prod_price 67 FROM Products 68 WHERE vend_id = 'DLL01'; 69 70 SELECT order_num ,COUNT(*) AS items 71 FROM OrderItems 72 GROUP By order_num 73 HAVING COUNT(*) >=3 74 ORDER BY items, order_num; 75 76 81 SELECT vend_name, RTRIM(prod_name) ,RTRIM(prod_price) 82 FROM Vendors, Products 83 WHERE Vendors.vend_id = Products.vend_id 84 85 SELECT vend_name, RTRIM(prod_name) AS PROD_NAME ,RTRIM(prod_price) 86 FROM Vendors INNER JOIN Products 87 ON Vendors.vend_id = Products.vend_id 88 89 SELECT cust_name ,cust_contact 90 FROM Customers , Orders , OrderItems 91 WHERE Customers.cust_id = Orders.cust_id 92 AND OrderItems.order_num = Orders.order_num 93 AND prod_id = 'RGAN01' 94 95 --self join
-- 作为子查询的SELECT语句只能查询单个列
-- 嵌套在其他查询中的查询叫做子查询。
96 SELECT cust_id , cust_name ,cust_contact 97 FROM Customers 98 WHERE cust_name IN (SELECT cust_name 99 FROM Customers 100 WHERE cust_contact = 'Jim Jones') 101 102 SELECT c1.cust_id , c1.cust_name , c1.cust_contact 103 FROM Customers c1 , Customers c2 104 WHERE c1.cust_name = c2.cust_name 105 AND c2.cust_contact = 'Jim Jones'; 106 107 SELECT Customers.cust_id ,Orders.order_num 108 FROM Customers INNER JOIN Orders 109 ON Customers.cust_id = Orders.cust_id; 110 111 112 SELECT Customers.cust_id ,Orders.order_num 113 FROM Customers LEFT OUTER JOIN Orders 114 ON Customers.cust_id = Orders.cust_id; 115 116 SELECT Customers.cust_id ,COUNT(Orders.order_num) AS num_ord 117 FROM Customers INNER JOIN Orders 118 ON Customers.cust_id = Orders.cust_id 119 GROUP BY Customers.cust_id 120 121 --Chapter 14 122 SELECT cust_name ,cust_contact ,cust_email 123 FROM Customers 124 WHERE cust_state in ('IL', 'IN', 'MI') 125 UNION --ALL 126 SELECT cust_name ,cust_contact,cust_email 127 FROM Customers 128 WHERE cust_name = 'Fun4All' 129 ORDER BY cust_name ,cust_contact; 130 131 SELECT * 132 FROM Customers 133 WHERE cust_id = '1000000006' 134 135 INSERT INTO Customers 136 VALUES('1000000006' , 'Toy Land', '123 Any Street' , 'New York' , 'NY' , '11111' ,'USA', NULL, NULL) 137 commit work; 138 139 INSERT INTO Customers(cust_id ,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country) 140 VALUES('1000000007' , 'Toy Land', '123 Any Street' , 'New York' , 'NY' , '11111' ,'USA'); 141 commit work; 142 143 SELECT * 144 FROM Customers 145 WHERE cust_id = '1000000007' 146 147 CREATE TABLE CustCopy AS 148 SELECT * FROM Customers; 149 commit work; 150 151 UPDATE Customers 152 SET cust_email = 'kim@thetoystore.com' 153 WHERE cust_id = '1000000005' 154 155 commit work; 156 157 SELECT cust_email 158 FROM Customers 159 WHERE cust_id = '1000000005' 160 161 UPDATE Customers 162 SET cust_contact = 'Sam Roberts', 163 cust_email = 'sam@toyland.com' 164 WHERE cust_id = '1000000006'165 166 commit work; 167 168 --To delete of the specified column ,can set the value of that column to null(if allowed) 169 UPDATE Customers 170 SET cust_email = NULL 171 WHERE cust_id = '1000000005'; 172 173 commit work; 174 175 SELECT * 176 FROM Custcopy; 177 178 DELETE FROM Custcopy 179 180 INSERT INTO CustCopy 181 SELECT * FROM Customers; 182 commit work; 183 184 DELETE FROM Custcopy 185 WHERE cust_id = '1000000006' 186 187 SELECT * 188 FROM Products; 189 190 TRUNCATE TABLE Custcopy; 191 SELECT * 192 FROM Custcopy; 193 194 CREATE TABLE TABLE_TEST 195 ( 196 prod_id char(10) NOT NULL 197 ); 198 INSERT INTO TABLE_TEST(prod_id) 199 VALUES('1234567890'); 200 commit work; 201 202 ALTER TABLE Vendors 203 ADD vend_phone CHAR(20); 204 commit work; 205 206 ALTER TABLE Vendors 207 DROP COLUMN vend_phone 208 commit work; 209 210 SELECT * 211 FROM Custcopy 212 213 DROP TABLE Custcopy 214 215 216 -- Chapter 18 Use View 217 CREATE VIEW ProductCustomers AS 218 SELECT cust_name , cust_contact, prod_id 219 FROM Customers, Orders ,OrderItems 220 WHERE Customers.cust_id = Orders.cust_id 221 AND OrderItems.order_num = Orders.order_num; 222 223 SELECT * FROM ProductCustomers 224 SELECT cust_name FROM ProductCustomers 225 226 SELECT cust_name ,cust_contact 227 FROM ProductCustomers 228 WHERE prod_id = 'RGAN01' 229 230 CREATE VIEW VendorLocations AS 231 SELECT RTRIM(vend_name) || ' (' || RTRIM(vend_country) || ')' AS vend_title 232 FROM Vendors; 233 234 SELECT * FROM VendorLocations 235 236 237 238 CREATE PROCEDURE MailingListCount( 239 ListCount OUT INTEGER 240 ) 241 IS 242 v_rows INTEGER; 243 244 BEGIN 245 SELECT COUNT(*) INTO v_rows 246 FROM Customers 247 WHERE NOT cust_email IS NULL; 248 ListCount := v_rows; 249 END; 250 251 VAR ReturnValue NUMBER 252 EXEC MAILINGLISTCOUNT(:ReturnValue); 253 254 SELECT * FROM Orders 255 DELETE FROM Orders 256 ROLLBACK 257 258 SET TRANSACTION 259 DELETE OrderItems WHERE order_num = 12345; 260 DELETE Orders WHERE order_num = 12345; 261 COMMIT;

 

转载于:https://www.cnblogs.com/luffystory/p/10125186.html

发布了0 篇原创文章 · 获赞 9 · 访问量 5万+
展开阅读全文
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 编程工作室 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览