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

I hope you might have found part 25 Useful SQL Tips And Tricks That You Should Know – Part 1 very useful. Before I show you another list of awesome tips and tricks, I will request you to try them out practically. Unless your memory is photographic you will forget what you read.

Also, after your practical execution, if you find that any of my points are incorrect, feel free to mail me at hioraclemine@gmail.com.

Your suggestions and advice are valuable to OracleMine.

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 2

1) COMMIT cannot be written inside the trigger. You have to use PRAGMA Autonomous_Transaction for the same.

2) MONTHS_BETWEEN is the date function that returns value in integer.

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.

SELECT COUNT(*) FROM employees GROUP BY department_id;

6) Only one column at a time can be renamed.

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.

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.

SELECT calculate_age FROM employees;
(Here calculate_age is a function)

13) Return datatype in function does not have size specification or length.

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.

surname employees.last_name%TYPE;

23) Metadata of all objects of the database is found in DICTIONARY view.

SELECT * FROM dictionary;

24) All tables, views and synonyms can be found at TAB view.


25) All the information about columns is available in COL view.

SELECT tname, colno, cname, coltype, width, scale, precision 
FROM col ORDER BY 1, 2;

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

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

Leave a Reply

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