SQL Tips And Tricks Part 2
1) COMMIT cannot be written inside the trigger. You have to use PRAGMA Autonomous_Transactionfor the same.
2) MONTHS_BETWEEN is the date function that returns value in integer.
Example, SELECT MONTHS_BETWEEN (sysdate, '01-JAN-2016') FROM dual;
3) Package specification can exist without package body. But package body cannot exist without package specification.
4) The range of values specified using BETWEEN clause includes from value and to value.
5) Columns in GROUP BY clause does have to necessarily be used in SELECT columns.
Example, SELECT COUNT(*) FROM employees GROUP BY department_id;
6) Only one column at a time can be renamed.
Example, ALTER TABLE employees RENAME COLUMN emp_id TO emp_number;
7) Per table, only one LONG datatype column is allowed.
8) The rowid column has unique data for all rows of from all tables.
9) SQL%ISOPEN always evaluates to false because oracle implicitly closes cursor after processing the statement.
10) You cannot find the spelling of numbers greater than 5373484 if below query is used. It is due to the limitation of Julian date.
Example, SELECT TO_CHAR (TO_DATE (2017, 'j'), 'jsp') FROM dual;
11) The DUAL table has one column called DUMMY and one row containing the value X.
12) Functions can be called from SQL statements.
Example, SELECT calculate_age FROM employees; (Here calculate_age is a function)
13) Return datatype in function does not have size specification or length.
Example, CREATE OR REPLACE function func_1(par1 IN NUMBER) RETURN VARCHAR2 BEGIN … END func_1;
14) Package does not have parameters but the procedures and functions in it have.
15) The variables declared inside package specification can be used by all functions and procedures inside it.
16) NOT NULL and UNIQUE constraints are automatically created when a column is declared as a primary key.
17) You can write maximum 255 subqueries in WHERE clause of SQL statement.
18) An unlimited number of subqueries can be written in the top FROM clause of the SQL statement.
19) From Oracle 8i onwards, a single table can have the maximum of 1000 columns. Prior to that, Oracle 7 had limit up to 254 columns only.
20) Maximum 32 columns can be used to create an index / clustered index.
21) Theoretically, you can enter an unlimited number of rows in a table.
22) Declare variables in functions, procedures, and packages using %TYPE instead of hard coded datatypes.
Example, DECLARE surname employees.last_name%TYPE; BEGIN ... END;
23) Metadata of all objects of the database is found in DICTIONARY view.
Example, SELECT * FROM dictionary;
24) All tables, views and synonyms can be found at TAB view.
Example, SELECT * FROM tab;
25) All the information about columns is available in COL view.
Example, SELECT tname, colno, cname, coltype, width, scale, precision FROM col ORDER BY 1, 2;
No comments:
Post a Comment