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

SQL is world’s foremost language to perform crud operations in almost all applications. The versatility of SQL ranges from small mobile applications to high-end enterprise solutions.  Oracle, MySQL, MSSQL and other leading databases have SQL in its fundamental structure. Besides, basic knowledge of SQL is mandatory for beginners before they establish themselves as experienced professionals.

Read about brief history of SQL here

SQL is in the heart of database transactions. So, it is inevitable to have command on its core concepts. In programming profession, at some point in time, you would have come in contact with SQL in one way or the other. Not only it is an easy language to learn but syntactically it is very English.

Yet there are some SQL concepts which should be clear to you before you work as SQL programmer. I can bet that beginners would find this list very useful and beneficial in their tasks. While experienced professionals could sharpen their edge of SQL knowledge to get better benefits.

I have developed four series of useful SQL tips and tricks in this context. Each series will contain a list of 25 set of tips and tricks among which I am releasing the first one here.

If you have any other tips and tricks that I failed to mention, please post me on hioraclemine@gmail.com. After proper verification and testing, that tip will be published in OracleMine along with person’s name.

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 1

1) Column alias cannot be used in WHERE clause.

2) Group functions cannot be used in WHERE clause.

3) When you drop a table, corresponding indexes are dropped automatically.

4) NOT NULL constraint can only be defined at the column level.

5) We can write not equal to operator in three ways!

  1. <>
  2. !=
  3. ^=

6) SYSDATE returns current database server date and time both (Output varies based on the settings of tools you use).

7) Dates are stored as numbers in the database so arithmetic functions are applied on dates.

8) NULL is neither a character nor number. It is an empty variable.

9) Column alias can be used in ORDER BY clause.

10) While creating a table by using SQL subquery only the column data types are copied. Integrity rules do not pass to the new table.

11) You can increase the width of the column in a table even if it has data.

12) You can decrease the width of the column only if it has all values as NULL.

13) NOT NULL constraint can be added using ALTER command if the table is not empty.

14) A column can be defined NOT NULL only when it is entirely NULL or has a value for every row.

15) ALTER command can drop multiple columns at a time even if data is present in it.

16) Inserting values in the table using VALUES clause enters only one row at a time in Oracle.

17) A function should have at least one return variable.

Syntax of Function

18) Stored procedures cannot be called from SQL statements.

Syntax of Procedure

19) DECLARE keyword can only be used in anonymous blocks and not in FUNCTIONS and PROCEDURES.

Refer syntax of FUNCTION and PROCEDURE.

20) SYSDATE is a function without any parameters.

21) Group functions ignore NULL values.

22) In addition to NVL there are two more functions that replace NULL values 1) COALESCE 2) NVL2.

23) OUTER join cannot be used with IN and OR operator.

24) An index always does not help in improving query’s performance. It sometimes degrades the performance.

25) Indexes cannot be altered. You have to drop and recreate it.

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 *