100+ SQL & PLSQL TIPS

100+ SQL & PLSQL TIPS

1. Zero is a number and a Space is a character.
2. Character strings and date values are enclosed in single quotation marks.
3. Character values are case sensitive and date values are format sensitive.
4.  The default date format is DD-MON-RR. Oracle database stores date in a format: Century, year, month, day, hours, minutes and seconds.
5.  An Alias cannot be used in the WHERE clause.
6. The symbol != and ^= also represents to not equal to condition.
7. Values specified with the BETWEEN condition are inclusive.
8. The IN condition is also called membership condition.
9. The IN condition can be used with any data type.
10. A NULL value cannot be equal (=) or unequal to any value.
11. NULL values are displayed last for ascending sequences and first for descending
12. Column Alias can be used in the ORDER BY clause.
13. Sysdate is a function that does not contain any argument but return current database server date and time.
14. Database stores dates as Numbers, so arithmetic operators such as addition and subtraction can be performed on dates.
15. All Group Functions ignores null values. To substitutes a value for null values, use the     NVL, NVL2, or COALESCE functions.
16. The oracle server implicitly sorts the result set in ascending order when using a Group By clause. To override this default ordering, DESC can be used in an Order By clause.
17. MIN and MAX functions can be used for any data type.
18. AVG, SUM, VARIANCE, and STDDEV functions can be used only with Numeric data types.
19. You cannot use a column alias in the GROUP BY clause.
20. All columns in the SELECT list that are not in group functions must be in the GROUP BY clause.
21. The GROUP BY column does not have to be in the SELECT list.
22. To restrict groups use HAVING clause not WHERE clause.
23. Don’t use Group functions in the Where clause.
24. Using the Table prefixes (alias) with the column names improves Performance, because it tells the oracle server exactly where to find the columns. It also helps to keep SQL code smaller, therefore using less memory.
25. A condition involving an Outer Join cannot use IN and OR operator.
26. The statement with the values clause adds only one row at a time to a table.
27. You can use a subquery in place of the table name in to INTO clause of the INSERT statement.
28. When creating a table by using a subquery, the integrity rules are not passed on to the new table, only the column data type definitions are passed.
29. When you add a new column in a table using ALTER command, than the new column become the last column in that table.
30. During modifying a column You can increase the width of a numeric or character column.
31. During modifying a column You can decrease the width of a column only if the column contains only null values or if the table has no rows.
32. During modifying a column You can change the data type only if the column contains null values
33. You can convert a CHAR column to the VARCHAR2 data type or convert a VARCHAR2 column to the CHAR data type only if the column contains null values or if you do not change the size.
34. A change to the default value of a column affects only subsequent insertions to the table.
35.  Using the ALTER TABLE statement, only one column can be dropped at time and the table must have at least one column remaining in it after it is altered.
36. You can add a comment of up to 2000 bytes about a column, table , view or snapshot by using the COMMENT statement.
37. You can define any constraint at the Table level except NOT NULL which is defined only at column level.
38. Without the ON DELETE CASCADE or the ON DELETE SET NULL options, the Row in the parent table cannot be deleted if it is referenced in the child table.
39. You can add a NOT NULL constraint to an existing column by using the MODIFY clause of the ALTER TABLE statement.
40. You can define a NOT NULL column only if the table is empty or if the column has a value for every row
41. A View can be modifying by using the CREATE or REPLACE option.
42. When you drop a table, corresponding indexes are also dropped.
43. More Indexes on a table does not mean faster queries. Each DML operations that is committed on a table with indexes must be updated. The more indexes you have associated with a table, the more effort the oracle server must make to update all the indexes after a DML operation.
44. You cannot modify indexes. To change an Index, you must drop it and the recreates it.
45. If you drop a table, Indexes and Constraints are automatically dropped but Views and Sequences remains.
46. A user can have access to several Roles and several users can be assigned the same Role. Roles are typically created for database application.
47. An owner of a table can grant access to all users by using the PUBLIC keyword.
48. Privileges cannot be granted on remote objects
49. You cannot alter the check constraint value, instead need to drop and create the check constraint again with new value.
50. PL/SQL Engine processes the entire PL/SQL block and filters out the SQL and procedural Statements separately, this reduces the amount of work that is sent to the oracle server and the number of memory cursors that are required.
51. PL/SQL code can be stored in oracle server as subprograms and can be referenced by any number of applications that are connected to the database.
52. In PL/SQL, an error is called an Exception.
53. If using NOT NULL or CONSTANT with variable declaration, you must assign a value to it.
54.  To assign a value into a variable from a database, use SELECT or FETCH statements.
55.  A slash (/) runs the PL/SQL block in a script file or in some tools such as iSQL*Plus.
56.  DECODE and Group Functions (Avg, Sum, Min, Max, Count, Stddev, Variance) are not available in procedural statements. Can be used in an SQL statement in a PL/SQL block.
57. An exception section can contain nested blocks.
58.  The Scope of the identifier is that region of a program unit (block, subprogram, or package) from which you can reference the identifier.
59.  PL/SQL does not directly support DDL statements such as Create , Alter, Drop table.
60.  PL/SQL does not directly support DCL statements such as GRANT and REVOKE.
61.  PL/SQL supports DML (Insert, Update, Delete) and Transaction control commands of SQL (Commit, Rollback, Savepoint)
62.  Select statements within PL/SQL block must return one and only one row. A query that returns more than one row (TOO_MANY_ROWS) or no row (NO_DATA_FOUND) generates an error.
63. PL/SQL does not return an error if a DML statement does not affect any rows in the underlying table, however if a SELECT statement does not retrieve any rows PL/SQL returns an exception.
64. SQL%ISOPEN always evaluates to FALSE because PL/SQL closes the implicit cursors immediately after they are executed.
65.  The PL/SQL program opens a cursor, processes rows returned by a query and then closes the cursor. The cursor makes the current position in the Active set.
66. Declare variables before the cursor declaration.
67. If the query returns no rows, no exception is raised.
68. You cannot reference cursor attributes directly in a SQL statement.
69. Before the first fetch %NOTFOUND evaluates to Null. So if Fetch never executes successfully, the Loop is never exited, that is because the EXIT WHEN statement. executes only if its WHEN condition is true. To be safe, use the following EXIT statement: EXIT WHEN emp_cursor%NOTFOUND or emp_cursor%NOTFOUND is Null.
70. When the same cursor is referenced repeatedly we can pass parameters to the cursor. We can also use cursor for loop with parameters.
71.  A block always terminates when PL/SQL raises an exception.
72.  Exception cannot appear in assignment statements or SQL statements.
73. You cannot use SQLCODE and SQLERRM directly in a SQL statement. Instead, you must assign their values to local variables, then use the variables in the SQL statement.
74. The keyword DECLARE that is used to indicate the starting of the declaration section in anonymous blocks is not used with the subprograms (Procedures or functions).
75. There must be at least one statement existing in the executable section, there must be atleast A NULL statement that is considered an executable statement.
76. You cannot reference host or bind variables in the PL/SQL block of a stored Procedure.
77. You cannot restrict the size of the data type in the parameters of the subprograms.
78. You must declare the subprogram in the declaration section of the block and must be the last item after all the other program items. For example: A variable declared after the end of the subprogram, before the begin of the procedure, will cause the compilation error.
79.  A Function must have a Return Clause in the Header and at least one Return Statement in the executable section.
80. Return data type in function declaration must not include a size specification.
81. You cannot reference host or bind variables in the PL/SQL block of a stored Function.
82. Although multiple Return statements are allowed in a function (usually within an IF statement) only one Return statement is executed.
83.  It is a good programming practice to assign a return value to a variable and use a single return Statement in the executable section of the code. There can be a return statement in the exception Section of the program also.
84. A Function may accept one or many parameter, but must return a single value.
85.  Although the three parameters modes (IN, OUT, INOUT) can be used with any subprogram, avoid Using the OUT and INOUT modes with Functions.
86. Anywhere a built-in function can be placed, a user-defined function can be placed as well.
87. Only stored functions are callable from SQL statements. Stored Procedures cannot be called.
88. Functions that are callable from SQL expressions cannot contain OUT and INOUT parameters, Other functions can contain parameters wit these modes but it is not recommended.
89. A Procedure containing one OUT Parameter can rewritten as a function containing a    RETURN Statement.
90.  Package cannot be invoked, parameterized or nested.
91. Package Specification and Body are stored separately in the database but must have the same name.
92.  Define the Package Specification before the Body.
93. A Package Specification can exist without a package Body, but a package Body cannot exist without a package Specification.
94. It is quite common in the package body to see all private variables and subprograms defined first and the public subprograms defined last.
95. If a Specification declares only types, constants, variables, exceptions and call specifications, the package body is unnecessary. However, the body can still be used to initialize items declared in the package specification.
96. Changes to the package specification requires recompilation of each referencing subprogram. So place few construct as possible in a package specification.
97. Only local or packaged subprograms can be overloaded. You cannot overload stand-alone.
98. Most built-in functions are overloaded. For ex: TO_CHAR function of standard package.
99. PL/SQL does not allow forward references; you must declare an identifier before using it. Therefore, a program must be declared before calling it.
100. The excessive use of Triggers can result in complex inter-dependencies, which may be difficult to maintain in large applications. Only use Triggers when necessary and beware of recursive and cascading effects.
101. If the logic for the Trigger is very lengthy, create stored procedures with the logic and invoke them in the Trigger body using CALL statement. There is no semicolon at the end of call statement.
102. Note that database triggers fire for every user each time the event occurs for which the Trigger is created.
103.  The size of a Trigger cannot be more than 32K.
104. Using column names along with the Update clause in the Trigger improves performance, because the Trigger fires only when the particular column is updated and thus avoids unintended firing when any other column is updated.
105. You can combine several Triggering events into one by taking advantage of a special conditional predicates INSERTING, UPDATING, DELETING with the Trigger body.
106. The OLD and NEW qualifiers are available only in ROW Triggers and prefix these qualifiers with a colon (:) in every SQL and PL/SQL statement.
107. There is no colon (:) prefix if the qualifiers are referenced in the WHEN restricting ROW Trigger can decrease the performance if you do a lot of updates on larger tables.
108. When you specify ON SCHEMA, the Trigger fires for the specific user, if you specify ON DATABASE, the Trigger fires for all users.
109.  A Table is not considered Mutating for statement Triggers.
110.  Statements in the Trigger Body operate under the privilege of the Trigger owner, not the Trigger user.
111. If the Package body changes and the Package specification does not changes, the stand-alone Procedure referencing a package construct remains valid.
112. If the package specification changes, the outside procedure referencing a package construct is invalidated, as is the package body.
113.  If a Stand-alone procedure referenced within the package changes, the entire package body is invalidated, but the package specification remains valid. Therefore, it is recommended that you bring the procedure into the package.
114.  Dynamic SQL supports all the SQL data types but does not supports PL/SQL specific types except PL/SQL record.
115.  You can use the INTO clause for a single-row query, but you must use OPEN-FOR, FETCH and CLOSE for a multi row query.
116.  The oracle 9i server performs implicit conversion between CLOB and VARCHAR2 datatypes. The other implicit conversions between LOB’s are not possible. Foe ex: if the user creates a table T with a CLOB column and a table S with a BLOB column, the data is not directly transferable between these two columns.
117. Binary Files (BFILE’s) can be accessed only in read-only mode from an oracle server.