SQL Cheat Sheet PDF

SQL CHEAT SHEET http://www.sqltutorial.org QUERYING DATA FROM A TABLE QUERYING FROM MULTIPLE TABLES USING SQL OPERATORS SELECT c1, c2 FROM t; SELECT c1, c2 SELECT c1, c2 FROM t1 Query data in col...

SQL CHEAT SHEET http://www.sqltutorial.org QUERYING DATA FROM A TABLE QUERYING FROM MULTIPLE TABLES USING SQL OPERATORS SELECT c1, c2 FROM t; SELECT c1, c2 SELECT c1, c2 FROM t1 Query data in columns c1, c2 from a table FROM t1 UNION [ALL] INNER JOIN t2 ON condition; SELECT c1, c2 FROM t2; SELECT * FROM t; Inner join t1 and t2 Combine rows from two queries Query all rows and columns from a table SELECT c1, c2 SELECT c1, c2 FROM t1 SELECT c1, c2 FROM t FROM t1 INTERSECT WHERE condition; LEFT JOIN t2 ON condition; SELECT c1, c2 FROM t2; Query data and filter rows with a condition Left join t1 and t1 Return the intersection of two queries SELECT DISTINCT c1 FROM t SELECT c1, c2 WHERE condition; FROM t1 SELECT c1, c2 FROM t1 Query distinct rows from a table RIGHT JOIN t2 ON condition; MINUS Right join t1 and t2 SELECT c1, c2 FROM t2; Subtract a result set from another result set SELECT c1, c2 FROM t ORDER BY c1 ASC [DESC]; SELECT c1, c2 Sort the result set in ascending or descending FROM t1 SELECT c1, c2 FROM t1 order FULL OUTER JOIN t2 ON condition; WHERE c1 [NOT] LIKE pattern; Perform full outer join Query rows using pattern matching %, _ SELECT c1, c2 FROM t ORDER BY c1 SELECT c1, c2 SELECT c1, c2 FROM t LIMIT n OFFSET offset; FROM t1 WHERE c1 [NOT] IN value_list; Skip offset of rows and return the next n rows CROSS JOIN t2; Query rows in a list Produce a Cartesian product of rows in tables SELECT c1, aggregate(c2) FROM t SELECT c1, c2 SELECT c1, c2 FROM t GROUP BY c1; FROM t1, t2; WHERE c1 BETWEEN low AND high; Group rows using an aggregate function Another way to perform cross join Query rows between two values SELECT c1, aggregate(c2) SELECT c1, c2 SELECT c1, c2 FROM t FROM t FROM t1 A WHERE c1 IS [NOT] NULL; GROUP BY c1 INNER JOIN t2 B ON condition; Check if values in a table is NULL or not HAVING condition; Join t1 to itself using INNER JOIN clause Filter groups using HAVING clause SQL CHEAT SHEET http://www.sqltutorial.org MANAGING TABLES USING SQL CONSTRAINTS MODIFYING DATA CREATE TABLE t ( CREATE TABLE t( INSERT INTO t(column_list) id INT PRIMARY KEY, c1 INT, c2 INT, c3 VARCHAR, VALUES(value_list); name VARCHAR NOT NULL, PRIMARY KEY (c1,c2) Insert one row into a table price INT DEFAULT 0 ); ); Set c1 and c2 as a primary key INSERT INTO t(column_list) Create a new table with three columns VALUES (value_list), CREATE TABLE t1( (value_list), ….; DROP TABLE t ; c1 INT PRIMARY KEY, Insert multiple rows into a table Delete the table from the database c2 INT, FOREIGN KEY (c2) REFERENCES t2(c2) INSERT INTO t1(column_list) ); SELECT column_list ALTER TABLE t ADD column; Set c2 column as a foreign key FROM t2; Add a new column to the table Insert rows from t2 into t1 CREATE TABLE t( ALTER TABLE t DROP COLUMN c ; c1 INT, c1 INT, UPDATE t Drop column c from the table UNIQUE(c2,c3) SET c1 = new_value; ); Update new value in the column c1 for all rows ALTER TABLE t ADD constraint; Make the values in c1 and c2 unique Add a constraint UPDATE t CREATE TABLE t( SET c1 = new_value, c1 INT, c2 INT, c2 = new_value ALTER TABLE t DROP constraint; WHERE condition; Drop a constraint CHECK(c1> 0 AND c1 >= c2) ); Update values in the column c1, c2 that match Ensure c1 > 0 and values in c1 >= c2 the condition Rename a table from t1 to t2 DELETE FROM t; CREATE TABLE t( Delete all data in a table c1 INT PRIMARY KEY, ALTER TABLE t1 RENAME c1 TO c2 ; c2 VARCHAR NOT NULL Rename column c1 to c2 ); DELETE FROM t Set values in c2 column not NULL WHERE condition; Delete subset of rows in a table TRUNCATE TABLE t; Remove all data in a table SQL CHEAT SHEET http://www.sqltutorial.org MANAGING VIEWS MANAGING INDEXES MANAGING TRIGGERS CREATE VIEW v(c1,c2) CREATE INDEX idx_name CREATE OR MODIFY TRIGGER trigger_name AS ON t(c1,c2); WHEN EVENT SELECT c1, c2 Create an index on c1 and c2 of the table t ON table_name TRIGGER_TYPE FROM t; EXECUTE stored_procedure; Create a new view that consists of c1 and c2 CREATE UNIQUE INDEX idx_name Create or modify a trigger ON t(c3,c4); CREATE VIEW v(c1,c2) Create a unique index on c3, c4 of the table t WHEN AS BEFORE – invoke before the event occurs SELECT c1, c2 AFTER – invoke after the event occurs FROM t; DROP INDEX idx_name; WITH [CASCADED | LOCAL] CHECK OPTION; Drop an index Create a new view with check option EVENT INSERT – invoke for INSERT SQL AGGREGATE FUNCTIONS UPDATE – invoke for UPDATE CREATE RECURSIVE VIEW v DELETE – invoke for DELETE AS AVG returns the average of a list select-statement -- anchor part COUNT returns the number of elements of a list UNION [ALL] TRIGGER_TYPE select-statement; -- recursive part SUM returns the total of a list FOR EACH ROW Create a recursive view FOR EACH STATEMENT MAX returns the maximum value in a list CREATE TEMPORARY VIEW v MIN returns the minimum value in a list CREATE TRIGGER before_insert_person AS BEFORE INSERT SELECT c1, c2 ON person FOR EACH ROW FROM t; EXECUTE stored_procedure; Create a temporary view Create a trigger invoked before a new row is inserted into the person table DROP VIEW view_name Delete a view DROP TRIGGER trigger_name Delete a specific trigger