SQL常用语句-1

(CREATE TABLE Order_T ( OrderID NUMBER(5,0) NOT NULL, OrderDate DATE DEFAULT SYSDATE,CustomerID NUMBER(3,0), CONSTRAINT Order_PK PRIMARY KEY (OrderID));
(Default DATE format: DD-MON-YY HH:MI:SS AM/PM
(DROP TABLE Order_T; DROP TABLE Customer-T CASCADE CONSTRAINTS;
(INSERT INTO Order_T VALUES (12001, ’10/16/2015′, 99);
(DELETE FROM Customer_T WHERE CustomerID = 101;
(ALTER TABLE Customer_T MODIFY CustomerAddress VARCHAR2(30);
(ALTER TABLE Order_T
ADD CONSTRAINT Order_FK FOREIGN KEY (CustomerID) REFERENCES Customer_T(CustomerID);
(ALTER TABLE Customer_T ADD (CustomerCity VARCHAR2(20), CustomerState CHAR(2), CustomerPostalCode VARCHAR2(9));
(UPDATE Order_T SET OrderDate = ’12/28/2015′ WHERE OrderID = 12002;
(SELECT AVG(ProductStandardPrice) AS Average_Price FROM Product_T;

SELECT * FROM Product_T WHERE ProductDescription LIKE ‘%Desk%’; [(%): any collection of characters (_): exactly one character (e.g., ‘_-Drawer%’) Note: it is case sensitive]
SELECT ProductDescription, ProductStandardPrice FROM Product_T WHERE ProductStandardPrice BETWEEN 200 AND 300; (both 200 and 300 are included)
SELECT * FROM Customer_T WHERE CustomerPostalCode IS NULL;
SELECT ProductDescription, ProductStandardPrice FROM Product_T WHERE ProductDescription LIKE ‘%Desk’ OR ProductDescription LIKE ‘%Table’ AND ProductStandardPrice > 300; [Condition AND, only works on like“%Table”, not on“%Desk”, NOT ( AND ( OR]
SELECT CustomerName,CustomerCity,CustomerState FROM Customer_T WHERE CustomerState IN (‘FL’,’TX’,’CA’) ORDER BY CustomerState,CustomerName;
SELECT ProductID, ProductDescription, ProductStandardPrice FROM Product_T WHERE ProductStandardPrice = (SELECT MAX(ProductStandardPrice) FROM Product_T WHERE ProductFinish=’Natural Ash’) AND ProductFinish=’Natural Ash’;
SELECT ProductFinish, COUNT(ProductID) AS Total FROM Product_T GROUP BY ProductFinish;
SELECT ProductLineID, MIN(ProductStandardPrice) AS Min_Price, MAX(ProductStandardPrice) AS Max_Price FROM Product_T GROUP BY ProductLineID;
SELECT CustomerState,COUNT(CustomerID) FROM Customer_T GROUP BY CustomerState HAVING COUNT(CustomerID) > 1;[HAVING only used with a GROUP BY clause]
SELECT CustomerState AS State, COUNT(CustomerID) AS Total_Customers FROM Customer_T GROUP BY CustomerState HAVING COUNT(CustomerID) > 1 ORDER BY Total_Customers DESC; [CANNOT use Total_Customers in HAVING]
SELECT ProductFinish, ROUND(AVG(ProductStandardPrice), 2) AS Avg_Price FROM Product_T WHERE ProductFinish IN (‘Cherry’,’Natural Ash’,’Natural Maple’,’White Ash’) GROUP BY ProductFinish;
SELECT * FROM Student, Department WHERE Student.dno = Department.dno; [Equi-Join, Common columns appear (redundantly) in the result table.]
SELECT * FROM Student INNER JOIN Department ON Student.dno = Department.dno; [inner join]
SELECT * FROM Student INNER JOIN Department USING dno; [inner join, Require identical column names for the PK and FKs of dno, Only MATCHED rows are selecteded, Establish an equi-join in the FROM clause]
SELECT * FROM Student NATURAL JOIN Department ON aStudent.dno = Department.dno; [Natural join, Same as an equi-join except that one of the duplicate columns is eliminated in the result table]
SELECT * FROM Student LEFT OUTER JOIN Department ON Student.dno = Department.dno; [Left Outer Join, Return all rows of the left-hand-side table no matter whether there is a match or not, and leave non matched column with null value]
SELECT * FROM Customer_T o LEFT JOIN Order_T c ON c.Customerid = o.Customerid [List all customers associated with their order info, including customers with no order info.]
SELECT FLOOR(year/10)10 AS decade, Round(COUNT()/Total100,1) AS Action_Distribution FROM movies, (select count() as Total from movies where genre = ‘Action’) WHERE genre = ‘Action’ GROUP BY FLOOR(year/10)*10 ORDER BY decade; [Calculate the distribution of Action movies (genre = ‘Action’) in different decades]
SELECT decade1 as decade, Round(Action_Movies/All_Movies100,1) AS Action_Proportion from (SELECT FLOOR(year/10)10 AS decade1, COUNT() AS Action_Movies FROM movies WHERE genre = ‘Action’ GROUP BY FLOOR(year/10)10), (SELECT FLOOR(year/10)10 AS decade2, COUNT() AS All_Movies FROM movies GROUP BY FLOOR(year/10)*10) where decade1 = decade2; [Find the proportion of Action movies in different decades]