25 Useful SQL Tips And Tricks That You Should Know – Part 4

This is the part 4 of 25 Useful SQL Tips And Tricks That You Should Know series. Till now we have gone through 3 series of the same topic. Hope you have enjoyed it well enough.

Feel free to mail me your suggestions and advice at hioraclemine@gmail.com. You can also post comments below.

Happy SQLing!

Note: All Tips and Tricks will be of Oracle SQL and PL/SQL. I have not considered MySQL, MSSQL or any other leading database.

SQL Tips And Tricks Part 4

1) The difference between UNIQUE and PRIMARY KEY is that there can be only one primary key in the table. While you can have as many as UNIQUE columns as you want.

2) Make use of SQL Analytical function for reporting purposes instead of sticking to basic things.

Analytical functions: RANK, DENSE_RANK, FIRST, LAST, FIRST_VALUE, LAST_VALUE, LAG and LEAD

3) Wherever possible use equality instead of LIKE predicate as it slow down the query

4) Mixing datatypes can slow down your query considerably.

5) Use NOT EXISTS instead of NOT IN and HAVING clause. It will make the query execution faster.

6) IN and OR operators cannot be used with OUTER JOIN column.

7) Be careful while using triggers. If triggers are used, an extensively complex structure will be created that will be difficult to maintain.

8) Combine the triggering events using INSERTING, UPDATING and DELETING if possible instead of writing trigger for each and every event.

9) Assign an initial value to the variables that are declared CONSTANT or NOT NULL.

10) Always Use WHEN_OTHERS exception last. If you are sure of an exception make use of that particular exception instead of WHEN_OTHERS.

11) You can also use ORDER BY 1,2 referencing first and second column respectively in the query rather than using column names.

12) Columns declared as LONG RAW cannot be queried like columns of other datatypes using SELECT statement.

13) Maximum size of CHAR and VARCHAR2 in PL/SQL is 32767 bytes

14) You can find out current schema using below query

15) Oracle database version can be determined by below query

16) You can generate random number in Oracle using below query

17) Oracle 11g introduced a concept of VIRTUAL COLUMN. The values in these virtual columns are derived from other columns.

18) Oracle Database 12c has come with many interesting features. Few of them are as below:

  • You can create sequence for specific session
  • Columns can be made invisible
  • Introduction of Identity keyword for sequential number generation
  • Direct analytical functions
  • Pluggable database facility. This means master child kind of relationship. All child databases are plugged into one master database.

19) There can be only one long raw column per table.

20) Comparing NULL with anything will always return NULL.

21) In PL/SQL, the SELECT statement must return only one row. If it is returning more than 1 row it throws TOO_MANY_ROWS exception. If it does not return any row it throws NO_DATA_FOUND exception.

22) The execution of block in PL/SQL terminates as soon as the exception is raised.

23) Tables dropped by using TRUNCATE command and cannot be retrieved back.

24) You can write cursor query directly into the FOR loop instead of writing it in DECLARE section.

25) “g” in 11g stands for grid computing while “c” in 12c stands for cloud computing. Simple tip but useful.

If you liked the above post, please leave your comments below.

Subscribe here to stay updated on latest posts of OracleMine.com.

One thought on “25 Useful SQL Tips And Tricks That You Should Know – Part 4

Leave a Reply

Your email address will not be published. Required fields are marked *