SQL Diving

UPPER Syntax: upper(variable) Parameters: Variable à String/Date Type/Number Type/Character Return Type If the variable is String/Char type than it gets converted into upper case. If the variable is Number Type/Date Type then it gets converted into String type(VARCHAR) without impacting its value. Used Case: (i) If Manager wants to fetch details of employee with same surname. SQL consider entities with different case as different entitles. So, by converting all in same case can fetch us required data. SELECT FIRST_NAME, LAST_NAME, PHONE_NUMBER FROM EMPLOYEES WHERE UPPER(FIRST_NAME) = 'JOHN'; ( ii) In case manager wants the details of employees with same hire date and salary to be displayed. In this case, upper() could be used to convert number and date type in varchar so that comparison can be done with given value. SELECT * FROM EMPLOYEES WHERE UPPER(HIRE_DATE)='25-JUN-97' AND UPPER(SALARY)='4800...

INITCAP Syntax:  initcap(variable) Parameters:  Variable -> String/Char/Number/Date type Return Type: If the variable is String/Char type than first character in each word will be converted to uppercase and all remaining characters converted to lowercase. If the variable is Number Type/Date Type then it gets converted into String type(VARCHAR) without impacting its value. Usage/Used Cases: (i) When manager wants useful information that could be extracted from given string. However, for presentation purposes, the extracted substring’ s first character should be in Upper case. SELECT INITCAP(SUBSTR(STREET_ADDRESS,1,10)) FROM LOCATIONS; (ii) If manager wants to extract name of the countries such as USA, UK where organization has its presence. SELECT * FROM COUNTRIES WHERE INITCAP(COUNTRY_NAME) LIKE '%U'; (iii) If manager wants to format the record and wants to make it more representable. ...