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);

-----------------------------------------------
-- Sams Teach Yourself SQL in 10 Minutes
-- http://forta.com/books/0672336073/
-- Example table population scripts for Oracle.
-----------------------------------------------


---------------------------
-- Populate Customers table
---------------------------
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(‘1000000001‘, ‘Village Toys‘, ‘200 Maple Lane‘, ‘Detroit‘, ‘MI‘, ‘44444‘, ‘USA‘, ‘John Smith‘, ‘sales@villagetoys.com‘);
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES(‘1000000002‘, ‘Kids Place‘, ‘333 South Lake Drive‘, ‘Columbus‘, ‘OH‘, ‘43333‘, ‘USA‘, ‘Michelle Green‘);
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(‘1000000003‘, ‘Fun4All‘, ‘1 Sunny Place‘, ‘Muncie‘, ‘IN‘, ‘42222‘, ‘USA‘, ‘Jim Jones‘, ‘jjones@fun4all.com‘);
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(‘1000000004‘, ‘Fun4All‘, ‘829 Riverside Drive‘, ‘Phoenix‘, ‘AZ‘, ‘88888‘, ‘USA‘, ‘Denise L. Stephens‘, ‘dstephens@fun4all.com‘);
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES(‘1000000005‘, ‘The Toy Store‘, ‘4545 53rd Street‘, ‘Chicago‘, ‘IL‘, ‘54545‘, ‘USA‘, ‘Kim Howard‘);

-------------------------
-- Populate Vendors table
-------------------------
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(‘BRS01‘,‘Bears R Us‘,‘123 Main Street‘,‘Bear Town‘,‘MI‘,‘44444‘, ‘USA‘);
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(‘BRE02‘,‘Bear Emporium‘,‘500 Park Street‘,‘Anytown‘,‘OH‘,‘44333‘, ‘USA‘);
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(‘DLL01‘,‘Doll House Inc.‘,‘555 High Street‘,‘Dollsville‘,‘CA‘,‘99999‘, ‘USA‘);
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(‘FRB01‘,‘Furball Inc.‘,‘1000 5th Avenue‘,‘New York‘,‘NY‘,‘11111‘, ‘USA‘);
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(‘FNG01‘,‘Fun and Games‘,‘42 Galaxy Road‘,‘London‘, NULL,‘N16 6PS‘, ‘England‘);
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(‘JTS01‘,‘Jouets et ours‘,‘1 Rue Amusement‘,‘Paris‘, NULL,‘45678‘, ‘France‘);

--------------------------
-- Populate Products table
--------------------------
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES(‘BR01‘, ‘BRS01‘, ‘8 inch teddy bear‘, 5.99, ‘8 inch teddy bear, comes with cap and jacket‘);
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES(‘BR02‘, ‘BRS01‘, ‘12 inch teddy bear‘, 8.99, ‘12 inch teddy bear, comes with cap and jacket‘);
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES(‘BR03‘, ‘BRS01‘, ‘18 inch teddy bear‘, 11.99, ‘18 inch teddy bear, comes with cap and jacket‘);
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES(‘BNBG01‘, ‘DLL01‘, ‘Fish bean bag toy‘, 3.49, ‘Fish bean bag toy, complete with bean bag worms with which to feed it‘);
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES(‘BNBG02‘, ‘DLL01‘, ‘Bird bean bag toy‘, 3.49, ‘Bird bean bag toy, eggs are not included‘);
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES(‘BNBG03‘, ‘DLL01‘, ‘Rabbit bean bag toy‘, 3.49, ‘Rabbit bean bag toy, comes with bean bag carrots‘);
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES(‘RGAN01‘, ‘DLL01‘, ‘Raggedy Ann‘, 4.99, ‘18 inch Raggedy Ann doll‘);
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES(‘RYL01‘, ‘FNG01‘, ‘King doll‘, 9.49, ‘12 inch king doll with royal garments and crown‘);
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES(‘RYL02‘, ‘FNG01‘, ‘Queen doll‘, 9.49, ‘12 inch queen doll with royal garments and crown‘);

------------------------
-- Populate Orders table
------------------------
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20005, TO_DATE(‘2012-05-01‘, ‘yyyy-mm-dd‘), ‘1000000001‘);
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20006, TO_DATE(‘2012-01-12‘, ‘yyyy-mm-dd‘), ‘1000000003‘);
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20007, TO_DATE(‘2012-01-30‘, ‘yyyy-mm-dd‘), ‘1000000004‘);
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20008, TO_DATE(‘2012-02-03‘, ‘yyyy-mm-dd‘), ‘1000000005‘);
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20009, TO_DATE(‘2012-02-08‘, ‘yyyy-mm-dd‘), ‘1000000001‘);

----------------------------
-- Populate OrderItems table
----------------------------
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 1, ‘BR01‘, 100, 5.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 2, ‘BR03‘, 100, 10.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 1, ‘BR01‘, 20, 5.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 2, ‘BR02‘, 10, 8.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 3, ‘BR03‘, 10, 11.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 1, ‘BR03‘, 50, 11.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 2, ‘BNBG01‘, 100, 2.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 3, ‘BNBG02‘, 100, 2.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 4, ‘BNBG03‘, 100, 2.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 5, ‘RGAN01‘, 50, 4.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 1, ‘RGAN01‘, 5, 4.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 2, ‘BR03‘, 5, 11.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 3, ‘BNBG01‘, 10, 3.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 4, ‘BNBG02‘, 10, 3.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 5, ‘BNBG03‘, 10, 3.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 1, ‘BNBG01‘, 250, 2.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 2, ‘BNBG02‘, 250, 2.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 3, ‘BNBG03‘, 250, 2.49);

SELECT prod_id,prod_name 
FROM Products
WHERE prod_name LIKE  ‘__ inch teddy bear %‘;

SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE ‘[JM]%‘
ORDER BY cust_contact;

SELECT prod_name 
FROM Products
WHERE NOT vend_id = ‘DLL01‘
ORDER BY prod_name;

SELECT RTRIM(vend_name) || ‘ (‘ || RTRIM(VEND_COUNTRY) || ‘)‘ AS vend_title
FROM Vendors
ORDER BY vend_name;

SELECT vend_name || ‘ (‘ || VEND_COUNTRY || ‘)‘ 
FROM Vendors
ORDER BY vend_name;

SELECT prod_id ,quantity, item_price,quantity * item_price AS expanded_price
FROM OrderItems
WHERE order_num = 20008;
-------------------------2018/11/29 below ------------------
SELECT vend_name ,UPPER(vend_name) AS vned_name_upcase
FROM Vendors
ORDER BY vend_name;

SELECT cust_name,cust_contact
FROM Customers
WHERE SOUNDEX(cust_contact) = SOUNDEX(‘Michael Green‘);

SELECT order_num
FROM Orders
WHERE to_number(to_char(order_date, ‘YYYY‘)) = 2012;

SELECT AVG(DISTINCT prod_price) as avg_price 
FROM Products
WHERE vend_id = ‘DLL01‘;
SELECT * -- count(cust_email) AS num_cust
FROM Customers

------------------------2018 Dec 4th below ------------
SELECT COUNT(*) AS num_prods
FROM Products
WHERE vend_id = ‘DLL01‘;

SELECT vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id;

SELECT cust_id,   COUNT(*) AS orders
FROM Orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;
--HAVING to_number(cust_id) >= 1000000001;

SELECT vend_id , COUNT(*) AS num_pords
FROM Products
--WHERE prod_price >=4 
GROUP BY vend_id 
HAVING COUNT(*) >=2 ;

SELECT prod_price
FROM Products
WHERE vend_id = ‘DLL01‘;

SELECT order_num ,COUNT(*) AS items
FROM OrderItems
GROUP By order_num
HAVING COUNT(*) >=3
ORDER BY items, order_num;

------------------------2018 Dec 5th below ------------

------------------------2018 Dec 6th below ------------

------------------------2018 Dec 8th below ------------
SELECT vend_name, RTRIM(prod_name) ,RTRIM(prod_price)
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id

SELECT vend_name, RTRIM(prod_name) AS PROD_NAME ,RTRIM(prod_price)
FROM Vendors INNER JOIN Products
ON Vendors.vend_id = Products.vend_id

SELECT cust_name ,cust_contact
FROM Customers , Orders , OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND OrderItems.order_num = Orders.order_num
AND prod_id = ‘RGAN01‘

--self join
SELECT cust_id , cust_name ,cust_contact
FROM Customers
WHERE cust_name IN (SELECT cust_name 
                   FROM Customers
                   WHERE cust_contact = ‘Jim Jones‘)
                
SELECT c1.cust_id , c1.cust_name , c1.cust_contact
FROM Customers  c1 , Customers  c2
WHERE c1.cust_name = c2.cust_name
AND c2.cust_contact = ‘Jim Jones‘;

SELECT Customers.cust_id ,Orders.order_num
FROM Customers INNER JOIN Orders
ON Customers.cust_id = Orders.cust_id;


SELECT Customers.cust_id ,Orders.order_num
FROM Customers LEFT OUTER JOIN Orders
ON Customers.cust_id = Orders.cust_id;

SELECT Customers.cust_id ,COUNT(Orders.order_num) AS num_ord
FROM Customers INNER JOIN Orders
ON Customers.cust_id = Orders.cust_id
GROUP BY Customers.cust_id

--Chapter 14
SELECT cust_name ,cust_contact ,cust_email 
FROM Customers
WHERE cust_state in (‘IL‘, ‘IN‘, ‘MI‘)
UNION --ALL
SELECT cust_name ,cust_contact,cust_email
FROM Customers
WHERE cust_name = ‘Fun4All‘
ORDER BY cust_name ,cust_contact;

SELECT *
FROM Customers 
WHERE cust_id = ‘1000000006‘

INSERT INTO Customers
VALUES(‘1000000006‘ , ‘Toy Land‘, ‘123 Any Street‘ , ‘New York‘ , ‘NY‘ , ‘11111‘ ,‘USA‘, NULL, NULL)
commit work;

INSERT INTO Customers(cust_id ,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country)
VALUES(‘1000000007‘ , ‘Toy Land‘, ‘123 Any Street‘ , ‘New York‘ , ‘NY‘ , ‘11111‘ ,‘USA‘);
commit work;

SELECT *
FROM Customers 
WHERE cust_id = ‘1000000007‘

CREATE TABLE CustCopy AS
SELECT * FROM Customers;
commit work;

UPDATE Customers 
SET cust_email = ‘kim@thetoystore.com‘
WHERE cust_id = ‘1000000005‘

commit work;

SELECT cust_email 
FROM Customers
WHERE cust_id = ‘1000000005‘

UPDATE Customers
SET cust_contact = ‘Sam Roberts‘,
    cust_email = ‘sam@toyland.com‘
WHERE cust_id = ‘1000000006‘；

commit work;

--To delete of the specified column ,can set the value of that column to null(if allowed)
UPDATE Customers
SET cust_email = NULL
WHERE cust_id = ‘1000000005‘;

commit work;

SELECT * 
FROM Custcopy;

DELETE FROM Custcopy

INSERT INTO CustCopy 
SELECT * FROM Customers;
commit work;

DELETE FROM Custcopy 
WHERE cust_id  = ‘1000000006‘

SELECT *
FROM Products;

TRUNCATE TABLE Custcopy;
SELECT * 
FROM Custcopy;

CREATE TABLE TABLE_TEST
( 
  prod_id char(10) NOT NULL
);
INSERT INTO TABLE_TEST(prod_id) 
VALUES(‘1234567890‘);
commit work;

ALTER TABLE Vendors
ADD vend_phone CHAR(20);
commit work;

ALTER TABLE Vendors
DROP COLUMN vend_phone
commit work;

SELECT * 
FROM Custcopy

DROP TABLE Custcopy

------------------------2018 Dec 9th below ------------
-- Chapter 18 Use View 
CREATE VIEW ProductCustomers AS 
SELECT cust_name , cust_contact, prod_id 
FROM Customers, Orders ,OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND OrderItems.order_num = Orders.order_num;

SELECT * FROM ProductCustomers
SELECT cust_name FROM ProductCustomers

SELECT cust_name ,cust_contact 
FROM ProductCustomers
WHERE prod_id = ‘RGAN01‘

CREATE VIEW VendorLocations AS 
SELECT RTRIM(vend_name) || ‘ (‘ || RTRIM(vend_country) || ‘)‘ AS vend_title
FROM Vendors;

SELECT * FROM VendorLocations

------------------------2018 Dec 10th below ------------

CREATE PROCEDURE MailingListCount(
  ListCount OUT INTEGER
  )
  IS 
  v_rows INTEGER;
  
  BEGIN 
    SELECT COUNT(*) INTO v_rows
    FROM Customers
    WHERE NOT cust_email IS NULL;
    ListCount := v_rows;
 END;
 
VAR ReturnValue NUMBER
EXEC MAILINGLISTCOUNT(:ReturnValue);
 ------------------------2018 Dec 11th below ------------
SELECT * FROM Orders
DELETE FROM Orders
ROLLBACK

SET TRANSACTION
DELETE OrderItems WHERE order_num = 12345;
DELETE Orders WHERE order_num = 12345;
COMMIT;