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

Example,
SELECT salary FROM employees WHERE emp_id LIKE 1;

4) Mixing datatypes can slow down your query considerably.

Example,
DECLARE
  v_1 NUMBER := ‘4’;
  v_name employees.name%TYPE;
BEGIN
  SELECT emp_name INTO v_name FROM employees WHERE emp_id = v_1;
END;

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.

Example,
SELECT emp_name, dept_no, salary FROM employees 
WHERE emp_id =1 ORDER BY 1, 2;

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

SELECT SYS_CONTEXT ('userenv', 'current_schema') 
FROM DUAL;

15) Oracle database version can be determined by below query

SELECT * FROM v$version;

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

SELECT ROUND (DBMS_RANDOM.VALUE () * 100) + 1 AS random_num 
FROM DUAL;

Here the random number between 0 and 100 is generated.
If you want to increase the limit put that maximum number 
instead of 100.

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

Syntax,
column_name [datatype] [GENERATED ALWAYS] AS [expression] [VIRTUAL]

CREATE TABLE employees (
Emp_id NUMBER,
Emp_name VARCHAR2(10), 
salary NUMBER(9,2),
dailysal AS ROUND((salary/30), 2) dailysal,
yearlysal NUMBER GENERATED ALWAYS AS ROUND((salary*12), 2) VIRTUAL);

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 *