SQL Quick Reference - ELTE



SQL StatementSyntaxALL | ANYSELECT column_name(s)FROM table_nameWHERE condition =|<=|<|... ANY|ALL (value1, value2, …)AND | ORSELECT column_name(s)FROM table_nameWHERE condition AND conditionALTER TABLEALTER TABLE table_name ADD column_name datatypeALTER TABLE table_name DROP COLUMN column_nameBETWEENSELECT column_name(s)FROM table_nameWHERE column_nameBETWEEN value1 AND value2CONSTRAINTCREATE TABLE table_name(column(s),CONSTRAINT constraint_name1 PRIMARY KEY(column_name),CONSTRAINT constraint_name2 FOREIGN KEY(column_name) REFERENCES table_name(col_name)CONSTRAINT constraint_name3 CHECK logical_formulaCONSTRAINT constraint_name4 UNIQUE (column_name(s)))CREATE DATABASECREATE DATABASE database_nameCREATE INDEXCREATE INDEX index_name ON table_name (column_name)CREATE UNIQUE INDEX index_name ON table_name (column_name)CREATE TABLECREATE TABLE table_name(column_name1 data_type,column_name2 data_type, …)See also: CONSTRAINTCREATE VIEWCREATE VIEW view_name(column_name(s)) AS (SELECT column_name(s)FROM table_name)DELETEDELETE FROM table_name WHERE some_column=some_valueDELETE * FROM table_nameDROP DATABASEDROP DATABASE database_nameDROP TABLE | INDEX | VIEWDROP TABLE table_nameEXISTSSELECT column_name(s)FROM table_nameWHERE NOT EXISTS (????SELECT column_name(s) FROM table_name2)GROUP BYSELECT column_name, aggregate_function(column_name)FROM table_nameWHERE column_name operator valueGROUP BY column_nameHAVINGSELECT column_name, aggregate_function(column_name)FROM table_nameWHERE column_name operator valueGROUP BY column_nameHAVING aggregate_function(column_name) operator valueINSELECT column_name(s)FROM table_nameWHERE column_name IN (value1,value2,..)INSERT INTOINSERT INTO table_name VALUES (value1, value2, value3, …)INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3, …)INNER JOINSELECT column_name(s)FROM table_name1INNER JOIN table_name2ON table_name1.column_name=table_name2.column_nameLEFT JOINSELECT column_name(s)FROM table_name1LEFT JOIN table_name2 ON table_name1.column_name=table_name2.column_nameRIGHT JOINSELECT column_name(s)FROM table_name1RIGHT JOIN table_name2 ON table_name1.column_name=table_name2.column_nameFULL JOINSELECT column_name(s)FROM table_name1FULL JOIN table_name2 ON table_name1.column_name=table_name2.column_nameLIKESELECT column_name(s)FROM table_nameWHERE column_name LIKE <pattern with % _>ORDER BYSELECT column_name(s)FROM table_nameORDER BY column_name [ASC|DESC]PRIORSELECT column_name(s)FROM table_nameSTART WITH column_name operator valueCONNECT BY PRIOR column_name operator column_nameORDER SIBLINGS BY column_name [NULLS FIRST | LAST]SELECTSELECT column_name(s) | *FROM table_nameSELECT DISTINCTSELECT DISTINCT column_name(s)FROM table_nameSELECT INTOSELECT column_name(s) | *INTO new_table_name [IN external_database]FROM old_table_nameSELECT column_name(s) | *INTO new_table_name [IN external_database]FROM old_table_nameSELECT TOPSELECT TOP number | percent column_name(s)FROM table_nameTRUNCATE TABLETRUNCATE TABLE table_nameUNION | INTERSECT | MINUSSELECT column_name(s) FROM table_name1UNIONSELECT column_name(s) FROM table_name2UNION | INTERSECT | MINUS ALLSELECT column_name(s) FROM table_name1UNION ALLSELECT column_name(s) FROM table_name2UPDATEUPDATE table_nameSET column1=value, column2=value, … WHERE some_column=some_valueWITHWITH temp_table_name AS (????SELECT column_name(s) FROM table_name) WHERESELECT column_name(s)FROM table_nameWHERE column_name operator valueLOWER | UPPER | TO_NUMBERString functionsFLOOR | CEIL | ABS | TO_CHARNumber functionsSYSDATE | TO_CHARDate functionsAVG | SUM | MAX | MIN | COUNTAggregate functions (GROUP BY)PL/SQL statementSyntax/Flushes all buffered commandsALTER FUNCTIONALTER FUNCTION name COMPILE [DEBUG]ALTER PACKAGEALTER PACKAGE name COMPILE [DEBUG][PACKAGE | SPECIFICATION | BODY];ALTER PROCEDUREALTER PROCEDURE name COMPILE [DEBUG]ALTER TRIGGERALTER TRIGGER [shema.]nameENABLE | DISABLE | RENAME TO new_name | COMPILE [DEBUG] [REUSE SETTINGS]];ARRAYTYPE name IS VARRAY | VARRYING ARRAY (maximum_size)OF element_type[NOT NULL]BEGIN[DECLARE declarations]BEGIN statement [statement […]][EXCEPTION exception handler]END;BULK COLLECTBULK COLLECT INTO collection_name [, collection_name …]CALLCALL [package.]method_name(parameter(s));CASECASE [selector/expression]????WHEN expression THEN statement [statement …]????[WHEN expression THEN statement [statement …] …]????[ELSE statement [statement …]]END CASE;variable := CASE selector????WHEN expression THEN result????[WHEN expression THEN result …]????[ELSE result]END;CASTCAST(expression | (SELECT …) | MULTISET(SELECT …) AS type_name)CLOSECLOSE cursor_name;COMMITCOMMIT [WORK];Finalizes the last transactionCREATE FUNCTIONCREATE [OR REPLACE] function_header[AUTHID DEFINER | CURRENT USER]function_bodySee also: FUNCTIONCREATE PACKAGECREATE [OR REPLACE] PACKAGE name[AUTHID DEFINER | CURRENT USER]ISpackage_content(s)END [name];/ -- Flushes the package definition before the executable code definitionsCREATE [OR REPLACE] PACKAGE BODY name ISdeclaration(s)[BEGINexecutable code definitions]END [name];CREATE PROCEDURECREATE [OR REPLACE] procedure_header[AUTHID DEFINER | CURRENT USER]procedure_bodySee also: PROCEDURECREATE TRIGGERCREATE [OR REPLACE] TRIGGER [shema.]name BEFORE | AFTER | INSTEAD OF????INSERT | DELETE | UPDATE … [OF attribute] ON DATABASE | [shema.]SCHEMA | table_name????[REFERENCING [OLD ROW AS name] [NEW ROW AS name]] [WHEN (expression)]????[FOR EACH ROW]????pl/sql_block | procedure_callFor DML triggers, use the :OLD and :NEW special variables to reference the affected rows.CURSORCURSOR name [(parameter_name [IN] type [:= expression] [, parameter …)] [RETURN row_type] IS????SELECT … FROM … WHERE …;See also: OPEN, CLOSE, FETCH, %FOUND, %ISOPEN, %ROWCOUNTDROP FUNCTIONDROP FUNCTION function_name;DROP PACKAGEDROP PACKAGE package_name;DROP PROCEDUREDROP PROCEDURE procedure_name;DROP TRIGGERDROP TRIGGER trigger_name;EXCEPTIONBEGIN…EXCEPTION????WHEN exception_name THEN statement????…????[WHEN OTHERS THEN statement]END;Declaration of a new exception:name EXCEPTION;See also: RAISEFETCHFETCH cursor_name INTO record_name | variable_name[, variable_name …];FORFOR variable IN [REVERSE] lowbound..highbound LOOP????statement [statement …]END LOOP;FOR variable IN cursor_name[(parameter(s))] | SELECT … FROM … WHERE … LOOP????statement [statement …]END LOOP;FORALLFORALL variable IN lowbound..highbound[SAVE EXCEPTIONS] INSERT | DELETE | UPDATEFOR UPDATECURSOR cursor_name [parameter(s)] [RETURN row_type] IS????SELECT … FROMtable_nameWHERE … FOR UPDATE;…OPEN cursor_name [(parameters(s))];FETCH cursor_name INTO variable;UPDATE table_name SET field_name = new_valueWHERE CURRENT OF cursor_name;CLOSE cursor_name;Updates rows returned by the SELECT statement one by one with the UPDATE commandFUNCTIONdeclaration: FUNCTION name [(parameter(s))] RETURN type_name;definition:PROCEDURE name [(param_name [IN | OUT | INOUT [NOCOPY]] type [:= expression]...)] RETURN type IS[declaration(s)]BEGIN … END;IFIF expression THEN statement [statement …][ELSIF expression THEN statement [statement …]][ELSE statement [statement …]]END IFLOOPLOOP????statement [statement …];????[EXIT WHEN expression;] -- Exits the loop????END LOOP;NULLThe empty statementOPENOPEN cursor_name [(parameter [,parameter …])]PROCEDUREdeclaration: PROCEDURE name [(parameter(s))];definition:PROCEDURE name [(param_name [IN | OUT | INOUT [NOCOPY]] type [:= expression]...)] IS[declaration(s)]BEGIN … END;RAISERAISE exception_name;RECORDTYPE name IS RECORD (????fieldname type [NOT NULL] [:= expression]???? [, fieldname type [NOT NULL] [:= expression]] …]);REF CURSOR declarationTYPE name IS REF CURSOR [RETURN result_type];REF CURSOR usagevariable_name type_name;…OPEN variable_name FOR SELECT … FROM … WHERE …;FETCH variable_name INTO variable;CLOSE variable_name;ROLLBACKROLLBACK [WORK] [TO [SAVEPOINT] savepoint_name];Rolls transaction changes back (partially if savepoint is specified). See also: SAVEPOINTSAVEPOINTSAVEPOINT name;Saves a transaction state to partially roll back changes. See also: ROLLBACKSELECT INTOSELECT column_name(s) | *INTO variableFROM table_name;See also: %TYPE, %ROWTYPESETSET environment_variable valueExample: SET serveroutput ON See also: DBMS_OUTPUT package in Oracle referenceSHOWSHOW expression [NONL]Example: SHOW ERRORS;SQLThe implicit cursor variable that can be used to traverse the last SQL statement’s result. See also: CURSORTABLETYPE name IS TABLE OF element_type [NOT NULL] [INDEX BY BINARY_INTEGER];Example: SELECT … FROM TABLE(name) … WHERE …;WHILEWHILE expression LOOP????statement [statement …]END LOOP%FOUNDcursor%FOUND: Returns NULL if the cursor is closed, TRUE if a line’s loaded otherwise FALSEcursor%NOTFOUND: Returns the logical inverse of %FOUND%ISOPENcursor%ISOPEN: Returns TRUE if the cursor is open otherwise FALSE%ROWCOUNTcursor%ROWCOUNT: Returns the number of the row the cursor last read otherwise 0%ROWTYPEtable/cursor%ROWTYPE: Returns the record type of a line of a table or cursor%TYPEvariable%TYPE: Returns the type of the variableCollection methodsEXISTS(i), COUNT, LIMIT, FIRST, LAST, NEXT(i), LAST(i), EXTEND[(n[, m])], TRIM(i) DELETE[(n[, m])]Random number generationAbs(dbms_random.random) Mod (max_number – min_number) + min_numberoperator ||String concatenation ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download