Page 2 :
9.LENGTH:caculate legnth of given string, select length(Address) from DBMS_EMP_TEST, 10.LPAD(Main string,length,character to be padded):This function returns the left padded to the given length., SELECT lpad(EMPNAME,12,'*') from DBMS_EMP_TEST2, 10.RPAD(Main string,length,character to be padded):This function returns the right padded to the given length., SELECT rpad(EMPNAME,14,'*') from DBMS_EMP_TEST2, 11.CONCAT(string1, string2) :This function returns a string by concatenating all the arguments. It can have more th, an one argument., SELECT CONCAT (EMPID,EMPNAME) from DBMS_EMP_TEST2, ==================================================================================, 2)Numeric and arithmatic function, DUAL is a table automatically created by Oracle Database along with the data dictionary. DUAL is in the schema of, the user SYS but is accessible by the name DUAL to all users. It has one column, DUMMY, defined to be VARCH, AR2(1), and contains one row with a value X. Selecting from the DUAL table is useful for computing a constant exp, ression with the SELECT statement. Because DUAL has only one row, the constant is returned only once., 1.abs(num) :This function is used to get the absolute value of the given number. The distance from zero is called abs, olute value., Select abs(-25) from dual, Select abs(-25.4) from dual, Select abs(-25.4+9) from dual, 2.ceil(num):This function returns the smallest value which is greater than or equal to the given number., select ceil(11.5) from dual; 12, select ceil(11.2) from dual; 12, select ceil(-12) from dual;, 3.floor(num):This function is used to find the greatest integer which is equal to or less than the given number., select floor(11.3) from dual 11, select floor(11.5) from dual 11, 4.TRUNC(num,certain decimal places):This function is used to truncate the given number upto given certain decima, l places., Select trunc(25.67,0) from dual;, Select trunc(25.67,1) from dual;
Page 3 :
5.ROUND(number, decimal_place):This function is used to round of given number up to given decimal places., Select round(25.67,0) from dual;, Select round(25.67,1) from dual;, TRUNC, 25.67,0 25, 25.67,1 25.6, 25.34,1 25.3, 25.34,2 25.34, , ROUND, 26, 25.7, 25.3, 25.34, , 6.exp(num) :This function is used to find e raised to the power of number, i.e. e^number.here e=2.71828183., select exp(11) from dual;, 2.71828183 ^ 11, select exp(5) from dual;, 7.POWER(m,n): This function is used to get the power of the given values., m: It is base value in the calculation, n: It is exponent value in the calculation, Select POWER(3,2) from dual;, Select POWER(22,9) from dual;, 8.SQRT(number):This function is used to get the square root of the given number., Select sqrt(25) from dual;, Select sqrt(27) from dual;, 9.MOD(n, m) :This function is used to get the remainder of given values., n: number to be divided by m, m: number that will divide n, Select mod(3,2) from dual;, Select mod(45.2,12) from dual;, ==================================================================================, 3)Aggregate Functions, 1.AVG(column):It returns average value of column in selection.Null value are not included in calculation., SELECT AVG(SALARY) FROM DBMS_EMP_TEST1, 2.COUNT(column):It returns the number of rows without NULL value in specified column.without null rows, SELECT COUNT(SALARY) FROM DBMS_EMP_TEST1
Page 4 :
3.COUNT(*):It returns thr number of selected rows in selection.all rows, SELECT COUNT(*) FROM DBMS_EMP_TEST1, 4.MAX(column):It returns highest value of column, SELECT MAX(SALARY)FROM DBMS_EMP_TEST1, 5.MIN(column):It returns smallest value of column, SELECT MIN(SALARY)FROM DBMS_EMP_TEST1, 6.SUM(column):It returns total sum of column, SELECT SUM(SALARY)FROM DBMS_EMP_TEST1, ==================================================================================, 4)Different clauses:, 1)ORDER BY clauses is used to sort the results.It is used in SELECT statement to sort either in ascending or descen, ding order., Ascending order:, SELECT * FROM DBMS_EMP_TEST1 ORDER BY EMPNAME ASC;, Descending Order:, SELECT * FROM DBMS_EMP_TEST1 ORDER BY EMPNAME DESC;, 2)GROUP BY clauses is used to group rows that have the same values. The GROUP BY clause is used in the SELE, CT statement. Optionally it is used in conjunction with aggregate functions to produce summary reports from the dat, abase., SELECT Address,count(*)"EMP_Address" FROM DBMS_EMP_TEST1 GROUP BY Address;, 3)Where and having clause:, a)In where clause specifies the rows to be retrieved., SELECT * FROM DBMS_EMP_TEST1 where salary < 65000;, b)Having clause added to SQL beacause WHERE could not be used against aggregate function like SUM.With havi, ng we cans use SUM function., SELECT ADDRESS,SUM(SALARY) FROM DBMS_EMP_TEST1 GROUP BY ADDRESS HAVING SUM(SAL, ARY)>20000, ==================================================================================, 5)DATE and TIME Function:, 1.SYSDATE:Return the current system date
Page 5 :
SELECT SYSDATE FROM dual;, 2.ADD_MONTHS:Add the specifieed number of months to the date(Subtracts months if the number of months is ne, gative), SELECT add_months(sysdate,5) from dual;, 3.LAST_DAY:returns the last day of the month that contains the date., SELECT last_day(sysdate) from dual;, 4.MONTHS_BETWEEN(date1, date2) :It is used to get the difference between given two dates, i.e., date1 and date2, ., select MONTHS_BETWEEN('20-nov-2018','20-feb-2019') from dual; older date-newer date=negative, select MONTHS_BETWEEN('20-jun-2021','20-feb-2021') from dual; 6-2=4 new date-older date=positive, 5.ROUND(datetime,format):This function returns the date rounded by the given measure., select ROUND(TO_DATE ('25-NOV-20'),'YEAR') from dual;, select ROUND(TO_DATE ('22-NOV-20'),'MONTH') from dual;, 6.TRUNC ( date , format):This function is used to truncate the given date with the given unit of measure., select TRUNC(TO_DATE('25-NOV-18'),'YEAR')from dual, select TRUNC(TO_DATE('25-NOV-18'),'MONTH')from dual, 7.CURRENT_DATE : It is used to get the current date., select CURRENT_DATE from dual;, 8.NEXT_DAY(date, weekday) :This function returns the first weekday from the given date and week name., select NEXT_DAY('11-feb-19', 'MONDAY') from dual, 9.GREATEST(exp1,exp2):returns the greatest value in a list od expression., SELECT GREATEST('10-JAN-20','10-OCT-20')from dual;, 10.LOCALTIMESTAMP :It is used to get the Date/Time of the current SQL session., select LOCALTIMESTAMP from dual;
