Examples of INITCAP, SUBSTR, TO_CHAR, NEXT_DAY, and more sql functions

 Given Tables is

  Instructor table

SQL queries Create Table, Select, Insert, Update, Rename a table, Modifying existing column, ALTER TABLE, DELETE, DROP TABLE, TRUNCATE Syntax

a) Display your name with the first letter being capital, where the entered name is in lower case.

SELECT INITCAP('sourav kumar patra') "Capitalized" FROM Dual; b) Display 2 - 6 characters of your name.

SELECT SUBSTR('SOURAV KUMAR PATRA',2,6) FROM DUAL; c) Find the length of your full university name.

SELECT LENGTH ('Siksha O Anusandhan') FROM DUAL; d) Display all the Instructor names with their first letter in upper case.

SELECT INITCAP(NAME) "Capitalized" FROM INSTRUCTOR; e) List the department name of each instructor as a three-letter code.

SELECT SUBSTR(DEPARTMENT,0,3) FROM INSTRUCTOR; f) Display the month of the joining of each instructor.

SELECT TO_CHAR(DATE_OF_JOIN,'MONTH') FROM INSTRUCTOR; g) Display the date of joining of each instructor in dd/mm/yy format.

SELECT TO_CHAR(DATE_OF_JOIN,'DD/MM/YYYY') FROM INSTRUCTOR; h) Display the experience of each instructor in terms of months.

SELECT MONTHS_BETWEEN(SYSDATE,DATE_OF_JOIN) FROM INSTRUCTOR;

I) Display the experience of each instructor in terms of years and months.

SELECT MONTHS_BETWEEN(SYSDATE,DATE_OF_JOIN),MONTHS_BETWEEN(SYSDATE,DATE_OF_JOIN)/12 FROM INSTRUCTOR; j) Display the date of the next Friday after today's date.

SELECT NEXT_DAY(SYSDATE,'FRIDAY') FROM DUAL; k) Display the day of joining of each instructor.

SELECT TO_CHAR(DATE_OF_JOIN,'DAY') FROM INSTRUCTOR; l) Display the date corresponding to 15 days after today's date.

SELECT SYSDATE+15 FROM DUAL;

L) Which query is used to get the current date in SQL? or get only date from datetime in sql mm/dd/yyyy

SELECT TO_CHAR(SYSDATE,'DD-MM-YY') FROM DUAL;

SELECT TO_CHAR(SYSDATE,'MONTH DD,YYYY') FROM DUAL;

Mathematical functions in SQL with Examples

SELECT ABS(-15) "Absolute" FROM DUAL;

SELECT CEIL(15.7) "Ceiling" FROM DUAL;

SELECT FLOOR(15.7) "Floor" FROM DUAL;

SELECT EXP(4) "e to the 4th power" FROM DUAL;

SELECT LOG(10,100) "Log base 10 of 100" FROM DUAL;

SELECT SIGN(0) "Sign" FROM DUAL;

SELECT GREATEST(SALARY,SALARY*1.5,SALARY*0.5) FROM INSTRUCTOR;

SELECT LEAST(3,5,1,8,9) FROM DUAL;

SELECT MOD(11,4) "Modulus" FROM DUAL;

String functions in SQL

SELECT NAME, LENGTH (NAME) FROM INSTRUCTOR;

SELECT INSTR ('aabbccaabb', 'cc') FROM DUAL;

SELECT SUBSTR ('welcome', 4) FROM Dual;

SELECT SUBSTR ('welcome', -3) FROM Dual;

SELECT SUBSTR ('welcome', -5, 2) FROM Dual;

SELECT REPLACE ('welcomme', 'mm', 'm') FROM Dual;

SELECT RTRIM ('welcome     ') FROM Dual;

SELECT LTRIM ('    welcome') FROM Dual;

SELECT TRIM (' idb ') FROM Dual;

• LEARN SQL