Tips & Tricks

Every now and then you would be writing queries to get the count of records on the table, mostly people tend to use count(*), but instead use count(1) this will increase the performance of the query.


SYSDATE returns current database server date and time both (Output varies based on the nls_date_format).

-- To get the current date

SELECT SYSDATE FROM DUAL;


Views are named and stored SQL queries. They do not hold any kind of raw data.


UNION returns only distinct dataset between two queries.

UNION ALL returns all the data, even the duplicates!!!

Because UNION does an extra level of filtering when compared to UNION ALL, it a bit slower in a large dataset.


NULL is an absence of a value, it's neither 0 nor empty space, neither a character nor number. It is an empty variable.

And  always remember NULL <> NULL.


Column alias can be used only in the ORDER BY clause.

Not in the WHERE clause nor in the GROUP BY clause, only ORDER BY clause.

SELECT FIRST_NAME FNAME, COUNT(1)
FROM EMPLOYEES
WHERE FIRST_NAME = 'Ellen'
GROUP BY FIRST_NAME
ORDER BY FNAME;


You can INCREASE the size of the column, even if it has data.

-- Drop the table if its already exists
DROP TABLE HR.EMP_DETAILS;

-- Create a table with following defination

CREATE TABLE HR.EMPLOYEE_DETAILS
( EMP_ID NUMBER,
EMP_NAME VARCHAR2(50),
SALARY NUMBER
);

-- Insert some data into the taable

INSERT INTO HR.EMPLOYEE_DETAILS VALUES (1,'Kishan',NULL);
INSERT INTO HR.EMPLOYEE_DETAILS VALUES (2,'Steve', NULL);

--commit the inserts

COMMIT;

-- Check the data

SELECT * FROM HR.EMPLOYEE_DETAILS;

-- Try and increase the size of the column, it will allow

ALTER TABLE HR.EMPLOYEE_DETAILS
MODIFY EMP_NAME VARCHAR2(60);

-- Check the new column defination

DESC HR.EMPLOYEE_DETAILS;

Name     Null Type         
-------- ---- ------------ 
EMP_ID        NUMBER       
EMP_NAME      VARCHAR2(60) 
SALARY        NUMBER  


You can DECREASE the size of the column even if it has data loaded into it, but not to the level that any of its data gets truncated, in such a case you will get the following error.

 

Error report:
SQL Error: ORA-01441: cannot decrease column length because some value is too big
01441. 00000 -  "cannot decrease column length because some value is too big"
*Cause:    
*Action:

DROP TABLE HR.EMP_DETAILS;

CREATE TABLE HR.EMPLOYEE_DETAILS
( EMP_ID NUMBER,
EMP_NAME VARCHAR2(60),
SALARY NUMBER
);

INSERT INTO HR.EMPLOYEE_DETAILS VALUES (1,'Kishan',NULL);
INSERT INTO HR.EMPLOYEE_DETAILS VALUES (2,'Steve', NULL);

COMMIT;

SELECT * FROM HR.EMPLOYEE_DETAILS;

ALTER TABLE HR.EMPLOYEE_DETAILS
MODIFY EMP_NAME VARCHAR2(6);

DESC HR.EMPLOYEE_DETAILS;

Name     Null Type         
-------- ---- ------------ 
EMP_ID        NUMBER       
EMP_NAME      VARCHAR2(6) 
SALARY        NUMBER  


ALTER TABLE HR.EMPLOYEE_DETAILS
MODIFY EMP_NAME VARCHAR2(4);

Error report:
SQL Error: ORA-01441: cannot decrease column length because some value is too big
01441. 00000 -  "cannot decrease column length because some value is too big"
*Cause:    
*Action:
 


 A NULL column can be altered to a NOT NULL column only when it is entirely NULL or has a value for every row.


DROP TABLE HR.EMP_DETAILS;

CREATE TABLE HR.EMPLOYEE_DETAILS
( EMP_ID NUMBER,
EMP_NAME VARCHAR2(60),
SALARY NUMBER
);

INSERT INTO HR.EMPLOYEE_DETAILS VALUES (1,'Kishan',NULL);
INSERT INTO HR.EMPLOYEE_DETAILS VALUES (2,'Steve', NULL);

COMMIT;

SELECT * FROM HR.EMPLOYEE_DETAILS;

-- below code will execute successfully because the EMP_ID column is not having any null data

ALTER TABLE HR.EMPLOYEE_DETAILS
MODIFY EMP_ID NUMBER NOT NULL;

-- below code will execute successfully because the SALARY column is having some null records

ALTER TABLE HR.EMPLOYEE_DETAILS
MODIFY SALARY NUMBER NOT NULL;

Error report:
SQL Error: ORA-02296: cannot enable (HR.) - null values found
02296. 00000 - "cannot enable (%s.%s) - null values found"
*Cause:    an alter table enable constraint failed because the table
           contains values that do not satisfy the constraint.
*Action:   Obvious


A function should always return something.

The function has RETURN <datatype> clause in its defincation.


A function can be called from a SELECT statement. Why?? because they always return something to the calling environment.

SELECT <function_name>

FROM DUAL;


A procedure cannot be called from a SELECT statement.

DECLARE

    procedure_name

END;

/


NVL, NVL2, NULLIF, COALESCE can be useful when dealing with NULL values.


When checking for NULL data, use IS NULL or IS NOT NULL, and never use the = operator. Eg. SELECT * FROM EMP WHERE FIRST_NAME IS NULL.


The columns in the GROUP BY clause does not need to be present in the select statement, but the columns in the SELECT clause has to be present in the GROUP BY CLAUSE.

Eg: SELECT COUNT(*) FROM employees GROUP BY department_id;

Eg: SELECT salary, COUNT(*) FROM employees GROUP BY salary;

Not doing so will lead to ORA-00937: not a single-group group function
00937. 00000 -  "not a single-group group function"
*Cause:    
*Action:


A package specification can exist without package body, but a package body cannot be present without package specification.


You cannot have a COMMIT inside a trigger, to do so you need to use PRAGMA AUTONOMOUS_TRANSACTION