database:SQL in 10 minutes

SELECT vend_name,prod_name,prod_price
FROM Vendors INNER JOIN Products
ON Vendors.vend_id = Products.vend_id;


SELECT cust_name,cust_contact 
FROM Customers
WHERE cust_id IN (SELECT cust_id
				FROM Orders
				WHERE order_num IN(SELECT order_num
								FROM OrderItems
								WHERE prod_id = 'RGAN01'));


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

SELECT cust_name,cust_contact 
FROM Customers AS C, 
	Orders AS O, 
	OrderItems AS OI 
WHERE C.cust_id = O.cust_id
	AND OI.order_num = O.order_num
		AND prod_id = 'RGAN01';

SELECT C1.cust_id,C1.cust_name,C1.cust_contact
FROM Customers AS C1,
	Customers AS C2
WHERE C1.	cust_name = C2.cust_name
	AND C2.cust_contact = 'jim Jones';

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


SELECT C.cust_id,O.order_num
FROM  Orders O FULL OUTER JOIN Customer C
	ON  O.cust_id =  C.cust_id;



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
;




INSERT INTO Customers
VALUES('100000006',
		'Toy land',
		'123 any street',
		'New York',
		'NY',
		'111111',
		'USA',
		null,
		NULL
			
		);


CREATE TABLE Custcopy AS 
SELECT * 
FROM Customers;


UPDATE Custcopy
SET cust_email = 'kim@fksociety.com'
WHERE cust_id = '1000000005';


DELETE FROM Custcopy
WHERE cust_id = '1000000006';


ALTER TABLE Vendors
ADD vend_phone CHAR(20);



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 cust_name,cust_contact
FROM ProductCustomers
WHERE prod_id = 'RGAN01';


//拼接
SELECT Concat(vend_name,' (',vend_country,')')
		FROM Vendors
		ORDER BY vend_name;



CREATE VIEW CustomerEmaillist AS 
SELECT cust_id,cust_name,cust_email
FROM Customers
WHERE cust_email IS NOT NULL;


SELECT * 
FROM CustomerEmaillist;



CREATE PROCEDURE


START TRANSACTION

DELETE FROM ORDER;
ROLLBACK;

COMMIT

SAVEPOINT


SAVEPOINT delete1;
SAVE TRANSACTION delete1;
ROLLBACK TO delete1;



DECLARE CustCursor CURSOR
FOR 
SELECT  * FROM Customers
WHERE cust_email IS NULL;

OPEN CURSOR CustCusor
FECTH NEXT FROM CustCursor
CLOSE CustCursor


PRIMARY KEY

ALTER TABLE Vendors
ADD CONSTRAINT PRIMARY KEY (vend_id);

ALTER TABLE Orders
ADD CONSTRAIT
FOREIGN KEY (cust_id) REFFERENCES Customers (cust_id)



CREATE INDEX prod_name_ind
ON PRODUCTS (prod_name);
	

the code in the book of Ben Forta.