SQL Tips And Tricks Part 1

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.

Example,
Wrong Method:
SELECT employee_name “name”
FROM employees WHERE name = ‘John Doe’;

2) Group functions cannot be used in WHERE clause.

Example,
Wrong Method:
SELECT * FROM employees WHERE SUM (salary) > 1000;

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

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

Example,
Wrong Method:
CREATE TABLE employees (emp_id number NOT NULL, emp_name VARCHAR2(40));

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

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

Example,
SELECT * FROM employees 
WHERE name <> ‘John’ OR name != ‘Jack’ OR name ^= ‘James’;

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.

Example,
Below query is valid:
SELECT employee_name ename FROM employees ORDER BY ename;

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.

Example,
ALTER TABLE employees DROP (country, state);

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

Example,
Wrong Method:
SELECT calculate_experience FROM employees;
(Here calculate_experience is procedure)

Right Method:
Method 1
BEGIN
   calculate_experience;
END;

Method 2
EXECUTE calculate_experience;
EXEC calculate_experience;

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.

Example,
SELECT sysdate FROM dual;

21) Group functions ignore NULL values.

Example,
SELECT MAX(dept_no) FROM employees;
The MAX function will not consider 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.

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