Trunc function in SQL

In sql when when we use the BETWEEN function to bring out data for a period of date range, some of the records might not come up in the result

for e.g.

SELECT *
FROM orders
WHERE order_date between to_date ('2003/01/01', 'yyyy/mm/dd')
AND to_date ('2003/12/31', 'yyyy/mm/dd');

If the order_date column in database has a time componenet in it, then the results might not come up for the dates 2003/01/01 and 2003/12/31

To solve this problem, we remove the time componenet from the query using the TRUNC function

SELECT *
FROM orders
WHERE trunc(order_date) between to_date ('2003/01/01', 'yyyy/mm/dd')
AND to_date ('2003/12/31', 'yyyy/mm/dd');

DECODE function in SQL

In sql, we can put validation logic by using the DECODE statement

For e.g. if we have a scenario such that all First Names are to be printed out for people who are aged 60, this can be written using decode statement such as

decode(age, 60, first_name, null),


If we add an extra condition to it such that print out the First Names only if they are a Male, then we write as below

decode(age, 60, decode(gender, male, first_name, null), null)

Subquery with NVL

Example 1
select
"EMPLOYEENUMBER",
"LASTNAME",
"FIRSTNAME",
NVL((select extension from classiccars.employee where lastname = 'Murphy'), 'Unavailable'),
"EMAIL",
"OFFICECODE",
"REPORTSTO",
"JOBTITLE"
from
"CLASSICCARS"."EMPLOYEE"

Example 2

SELECT
Parcel.Alt_Key,
substr(trim(Parcel.Full_Parcel_ID),1,8) SubDiv_Number,
NVL(LS.Sub_Name,
(SELECT NVL(V2.Sub_name,'Unknown Subdivision')
FROM SUBDIV V2
WHERE substr(trim(Parcel.Full_Parcel_ID),1,8) =
substr(trim(V2.Sub_Number),1,8)
AND V2.rowid = (select min(sub2.rowid)
from SUBDIV sub2
where (trim(substr(V2.sub_number,1,8))) =
(trim(substr(sub2.sub_number,1,8)))))) SubDiv_Name,
Parcel.Full_Parcel_ID
FROM PARCEL INNER JOIN SALES
ON Parcel.Alt_Key = Sales.Alt_Key
LEFT JOIN
SUBDIV LS
ON substr(trim(Parcel.Full_Parcel_ID),1,10) =
substr(trim(LS.Sub_Number),1,10)

Subselects in database queries

While inserting records into a table A, if a need arises to fill a column with value from a table B, then use the following sql

insert into "CLASSICCARS"."EMPLOYEE"
(
"EMPLOYEENUMBER",
"OFFICECODE"

)
values
(
1703,
(select max(officecode) from classiccars.OFFICE)
)

Querying a date column in database

To query a date column, use

select * from person_table where date_of_birth >= to_date('01/01/2000', 'dd/MM/yyyy')