TheLazy.Dev
Portfolio GitHub

Blog

SQL - Dia 8

technonotes

technonotes-hacker

Posted on November 2, 2024

SQL - Dia 8

Number Function

  • Round
  • Trunc
  • Mod
  • Ceil
  • Floor
  • Abs
  • Sign

Image description

Round

  • If there is number => 5 after the decimal point , it will round off to the next number otherwise it will remain the same. 
select round(67.34) from dual;
select round(67.49) from dual;
select round(67.57) from dual;
Enter fullscreen mode Exit fullscreen mode

Image description

Trunc

  • It wont see any number after decimal.
  • Trunc is used only with the NUMBERS.
  • Lets do one more operation with trunc --> trunc(56.89898,2) means it will take the two digit after the decimal. 
select trunc(67.7846434) from dual;
select trunc(67.10001) from dual;
select trunc(67.011234) from dual;

select sysdate from dual; --> it will display time also
select trunc(sysdate) from dual;
Enter fullscreen mode Exit fullscreen mode

Image description

Image description

Image description

Mod

  • It will give the remainder. 
select mod(10,7) from dual;
select mod(61,10) from dual;
select mod(6,2) from dual;
Enter fullscreen mode Exit fullscreen mode

Image description

Image description

Ceil

  • It will go to the next number, whatever may the number. 
select ceil(67.7846434) from dual;
select ceil(67.00001) from dual;
select ceil(67.011234) from dual;
Enter fullscreen mode Exit fullscreen mode

Image description

Floor

  • It will crop the decimal.
  • Then what is the use of Trunc ? In trunc we can use, how many decimal can be included. Hope you remember.

Image description

select floor(67.7846434) from dual;
select floor(67.00001) from dual;
select floor(67.011234) from dual;

select floor(67.7846434),trunc(67.7846434,1) from dual;
select floor(67.00001),trunc(67.00001) from dual;
select floor(67.011234) , trunc(67.011234,0) from dual;
Enter fullscreen mode Exit fullscreen mode

Image description

Abs

  • Only + & - are allowed.
  • It will remove the sign.
  • There is one more function similar to this sign i.e SIGN, it will give 1 & -1. 
select abs(67.7846434), abs(-67.7846434), abs(10-15), sign(-10) , sign(+67.89) from dual;
Enter fullscreen mode Exit fullscreen mode

Image description

Date Function

  • add_months
  • months_between
  • next_day
  • last_day

Image description

add_months

  • if you want to add MONTHS then this function is used. 
select sysdate, sysdate+2 , sysdate+20 , sysdate-10 from dual; -- Days increases
select sysdate from dual;
select add_months(sysdate,1) from dual;
select add_months('01-DEC-2024',1) from dual;
select add_months(sysdate,-1) from dual; -- months increases
Enter fullscreen mode Exit fullscreen mode

Image description

Image description

Image description

select * from hr.employees where hire_date < add_months(sysdate,-276) -- hired before 23 years , 276 is the month
Enter fullscreen mode Exit fullscreen mode

Image description

months_between

  • Displays the no of months , sometime it will give in negative number that means subtraction happens between months Jan is 1 and Dec is 12.
  • Even the date will be considered.

Image description

Image description

-- select months_between((01-01-20024), (01-12-2024)) from dual;
-- select months_between((01-jan-2024), (01-dec-2024)) from dual;
select months_between(('01-JAN-2024'), ('01-DEC-2024')) , months_between(('01-DEC-2024'), ('01-jan-2024')) from dual;
select months_between(('17-JAN-2024'), ('01-DEC-2024')) , months_between(('01-DEC-2024'), ('25-jan-2024')) from dual;
Enter fullscreen mode Exit fullscreen mode

Image description

  • If you don't like the decimal , what you can use ? For sure I forgot . Can't remember very quickly . After reading my notes , found its "TRUNC or FLOOR".
  • Find my age ? How ? 
select months_between(('01-DEC-2024'), ('01-DEC-1989'))/12 from dual;
select months_between(('01-DEC-2024'), ('01-DEC-1989'))/12 AS AGE from dual;
Enter fullscreen mode Exit fullscreen mode

Image description

Image description

next_day

  • Also will order you can specify.
  • Sunday --> 1 , Saturday --> 7 
select sysdate from dual ;
select next_day(sysdate,'sunday') from dual;
select next_day(sysdate,1) from dual;
Enter fullscreen mode Exit fullscreen mode

Image description

Image description

last_day

  • To find the last day of the month. 
select last_day(sysdate) from dual;
select last_day('01-feb-2024') from dual;
Enter fullscreen mode Exit fullscreen mode

Image description

General

  • greatest
  • least
  • distinct
  • unique
  • case
  • decode
  • concat
  • ||

Image description

Greatest 

select 12,67,98,90,100 from dual;
select greatest(12,67,98,90,100) , least (12,67,98,90,100) from dual;
Enter fullscreen mode Exit fullscreen mode

Image description

Least 

select 12,67,98,90,100 from dual;
select greatest(12,67,98,90,100) , least (12,67,98,90,100) from dual;
Enter fullscreen mode Exit fullscreen mode

Distinct & Unique

  • It will check the duplicate.
  • Both will be same . 
select unique FIRST_NAME , last_name from hr.employees;
select distinct FIRST_NAME , last_name from hr.employees;
Enter fullscreen mode Exit fullscreen mode

Image description

Concat & || 

select concat(concat(FIRST_NAME,LAST_NAME),salary) from hr.employees;
select FIRST_NAME || LAST_NAME ||' '|| salary from hr.employees;
Enter fullscreen mode Exit fullscreen mode

Image description

Image description

Note

  1. In the Date function --> only months_between --> it returns number.
đź’– đź’Ş đź™… đźš©
technonotes
technonotes-hacker

Posted on November 2, 2024

Join Our Newsletter. No Spam, Only the good stuff.

Sign up to receive the latest update from our blog.

Related

I am purposefully super nice to AI because I imagine they are trained on data where nice people get better help. Doing this, though, has an existential uneasiness to it.
ai I am purposefully super nice to AI because I imagine they are trained on data where nice people get better help. Doing this, though, has an existential uneasiness to it.

November 28, 2024

The best way to get better at writing code is...
development The best way to get better at writing code is...

November 28, 2024

If you're a beginner, definitely check this open source guide. I've explained almost everything you need to know.
undefined If you're a beginner, definitely check this open source guide. I've explained almost everything you need to know.

November 28, 2024

HMPL has a new logo. What do you think?
webdev HMPL has a new logo. What do you think?

November 28, 2024

AI Innovations at Microsoft Ignite 2024 What You Need to Know (Part 2)
githubcopilot AI Innovations at Microsoft Ignite 2024 What You Need to Know (Part 2)

November 29, 2024