SQL Diving

TRUNC Syntax: TRUNC ( source date [, date precision format ] ) Parameters: source date is the date which is to be truncated. Data precision format is the unit of measure to apply for truncating. It is an optional parameter. If date precision format is omitted, the TRUNC function will truncate the date to the day value. Date precision formats include century (CC), year (YYYY), quarter (Q), month (MM), week (W), day (DD), hour (HH), and minute (MI). Return type: The TRUNC function (as it applies to dates) returns date value. Usage:  According to company's policy, the start date of the health insurance of the employees will start from the 1st day of the month of their joining date. Manager to Data analyst: Bring me the list of all employees with the start date of their health insurance. Data analyst runs below query to fetch the required data. SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, TRUNC(HIRE_DATE, ‘MM’) FROM EMPLOYEES;

ROUND Syntax : ROUND ( source date [, date precision format ] ) Parameters : source date is the date which is to be rounded. Data precision format is the unit of measure to apply for rounding. It is an optional parameter. If date precision format is omitted, the round function will round to the nearest day. Date precision formats include century (CC), year (YYYY), quarter (Q), month (MM), week (W), day (DD), hour (HH), and minute (MI). Return Type : The ROUND function (as it applies to dates) returns date value. Usage : According to company's policy, every month employees get shopping coupons. For the employees who have joined the organization after 15th of the month will start getting their shopping coupons from the next month. Manager wants the months from which employees get their shopping coupons. Manager to Data analyst: Bring me the list of all employees with the month from which employee is getting their shopping coupons. Data analyst runs below query to

REPLACE Syntax: REPLACE (var, var_to_replace [, replacement_string]) Parameters: - var: Char/Varchar2/Number/Date Type - var_to_replace: Char/Varchar2/Number/Date Type Return Type: returns a string value. Used Case:  If manager want to know the Job title of the employees. However, in database, the job title are separated with underscore if the job title has two words. To make it more presentable, blank space should be used. select first_name,job_id,replace(job_id,'_',' ')  from employees;

INSTR Syntax:  INSTR ( source string, search string [, start position, nth occurance]) Parameters:  source string is the string in which search string is to be searched. Start position is the starting position for searching. It is optional. If it is omitted, its default value is 1. The first position in the source string is 1. If the start position is negative, The INSTR function counts back start position number of characters from the end of string and the searches towards the beginning of string. Nth occurrence is the occurrence of substring which we want. It is an optional parameter. If it is omitted, its default value is 1. Return type:  The INSTR function returns a numeric value. If the search string is not found in source string, INSTR function returns 0. Usage:  Manager to data analyst: Bring me the second word in the street address of each employee. Data analyst runs below query to fetch the required data. SELECT EMPLOYEE_ID, FIRST_NAME, L

SUBSTR Syntax: SUBSTR ( source string, start position [, number of characters to extract]) Parameters: source string is the string from which sub-string is to be extracted. Start position is the starting position of extraction. Number of characters to extract is the optional parameter. If this parameter is omitted, the SUBSTR function will return the entire string. Return type: The SUBSTR function returns a string value. If number of characters to extract is a negative number, then SUBSTR function will return a NULL value. Usage:  Manager got an offer from mobile phone carrier provider for all the employees of the organization. To avail this offer, Manager has to group the employees based on the area code in their phone number.  Manager to Data analyst: Bring me the list of all employees with first three digits of their phone no. sort the result based on phone no. Data analyst runs below query to fetch the required data. SELECT EMPLOYEE_ID, FIRST_NAME, L

TRIM Syntax : TRIM ([ [ LEADING/TRAILING/BOTH] trim_character FROM] variable) Parameters : Variable/trim_character -> String/Char/Number/Date type Return Type : It returns VARCHAR2 Use Case :  (i) If manager asks for the phone number and salary of the employees. But he wants to make sure that unneccary zero should not proceed in the phone number, if it exists. SELECT FIRST_NAME,TRIM( LEADING 0 FROM SALARY)AS SALARY,TRIM(LEADING 0 FROM PHONE_NUMBER) FROM EMPLOYEES; (ii) If manager wants email to do further computation. There might be unneccary blank spaces. SELECT EMAIL,TRIM(LOWER(EMAIL)) FROM EMPLOYEES; (iii) Manager wants to include address of employee in the report. But he doesn't want that extra blank space should not come in the report which would impact format. SELECT TRIM (' ' FROM STREET_ADDRESS) FROM LOCATIONS;

RPAD Syntax : RPAD( var, padded_length ,x ) Paramters : var -> String/DateType/Number x -> String/DateType/Number Return Type : String Value Use Case :  If manager wants to postal code of all the locations where its organization has offices in presentable manner. select city,rpad(postal_code,12,'*') from locations;

LPAD Syntax : LPAD( var, padded_length ,x ) Parameters : var -> String/Date Type/Number x -> String/Date Type/Number Return Type : String Value Use Case (i) If manager wants to know the salary of the employee in the neat format. select first_name,lpad(salary,5,'*') from employees; (ii) If the manager is interested in knowing the initials of the countries in which its organization has presence. select lpad(upper(country_name),2) from countries;

LENGTH Syntax : LENGTH ( string ) Parameters : The string to return the length for. Return type : The LENGTH function returns a numeric value. If string is NULL, LENGTH function will return NULL. Usage : HR Manager has decided to assign cadre on the basis of the number of digits in the salary of employee. Manager to Data analyst: Bring me the list of all employees and the number of digits in their salaries. Data analyst runs below query to fetch the required data. SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, LENGTH(SALARY) AS NO_OF_DIGITS FROM EMPLOYEES;

CONCAT Syntax:  CONCAT (String1, string2) Parameters:  string1 and string2 are strings to concat. Return type:  The CONCAT function returns a string value. Usage:  Manager to Data analyst: Bring me the list of all employees and their full name. Data analyst runs below query to fetch the required data. SELECT EMPLOYEE_ID, CONCAT(FIRST_NAME, CONCAT(' ',LAST_NAME)) FROM EMPLOYEES;