/*------------String functions -------------- */

SELECT CONCAT(Continent, " --> ", name) AS Continent_ctry

FROM [Link];

Select CONCAT(Continent," : ",name," : ",GovernmentForm)

from [Link];

SELECT CONCAT_WS(" || ", Continent, name, GovernmentForm)

AS CombinedFields from [Link];

/*from n postion, replace x characters with "- - -" */

SELECT INSERT(Continent, 3, 2, "xx") from [Link];

/*Search America in Continent and return position*/

SELECT Continent,INSTR(Continent, "America") AS Position

from [Link];

/*Upper and Lower case*/

SELECT LCASE(Continent) as continent_lower from [Link];

SELECT UCASE(Continent) as continent_upper from [Link];

/*Extract n character from the left of the string*/

Select LEFT(name,3) as ctry_code from [Link];

Select ucase(LEFT(name,3)) as ctry_code from [Link];

/*Extract n character from the right of the string*/

Select Right(name,3) as ctry_code from [Link];

/*Extract n character from m position in the string*/

/*Use MID or SUBSTR or Substring function*/

Select mid(name,2,4) as ctry_code from [Link];

SELECT SUBSTR(name,2,4) as ctry_code from [Link];

SELECT Substring(name,2,4) as ctry_code from [Link];

/*Substring index to get string before a given delimiter*/

SELECT SUBSTRING_INDEX("SQL-is-amazing", "-", 2);

Select Substring_Index(Continent," ",1) from [Link];

Select * from [Link];

/*String Length*/

SELECT LENGTH(name) AS str_len from [Link];

SELECT CHAR_LENGTH(name) AS str_len from [Link];

SELECT CHARACTER_LENGTH(name) AS str_len from [Link];

/*Combine strings using concat */

SELECT CONCAT(Continent, " : ", name) AS Continent_ctry

FROM [Link];

/*Padding characters to make the string of stanmdard length*/

SELECT LPAD(Continent, 15, "x") as Code from [Link];

SELECT RPAD(Continent, 15, "*") as Code from [Link];

/*Remove Spaces using TRIM, LTRIM. RTRIM*/

Select trim(continent) as continent_2 from [Link];

Select Ltrim(continent) as continent_2 from [Link];

Select Rtrim(continent) as continent_2 from [Link];

/*Replace function*/

Select replace(Continent, "Asia", "APAC") as Geo from

[Link];

Select replace(continent,"Europe","EMEA") as Geo,

ucase(name),round(Population/1000,0) as Population_in_K

from [Link];

/*Print country name in sentence case (proper case) */

Select concat(ucase(mid(name,1,1)),

lower(mid(name,2,char_length(name)))) as

ProperName from [Link];

/*Reverse string*/

Select reverse(name) as name2 from [Link];

Select * from [Link];

Select coalesce(IndepYear,Population, LifeExpectancy)

as MyColumn from [Link];

 /*---------------Number functions-------------------*/
/*SUM, Count, Min, Max, Avg, Power, Pow, SQRT, Round,Truncate*/

/*Find count of countries, Total Population. Miminum Poplation,

Maximum Population, Average population */

Select Count(Name) as CountryCount, Sum(Population) as TotalPopulation,

Min(Population) as Minimum,Max(Population) as Maximum,

Avg(Population) as Average

from [Link];

/*Ceiling an dFloor Function*/

Select ceil(LifeExpectancy) from [Link];

Select ceiling(LifeExpectancy) from [Link];

Select ceil(12.3);

Select floor(12.3);

Select floor(LifeExpectancy) from [Link];

/*Round and Truncate Function*/

Select Round(12.357,1);

Select truncate(12.357,2);

/*Mod function to find the remainder*/

Select mod(10,3);

/*Power function*/

Select pow(3,4);

Select power(3,4);
/*Find the greates and least value from multiple columns*/

Select greatest(10,2,29,12,15);

Select greatest(Population, LifeExpectancy, SurfaceArea) as Bigger

from [Link];

Select least(10,2,29,12,15);

Select least(Population, LifeExpectancy, SurfaceArea) as Lesser

from [Link];

/*Show Country wise per person land area*/

Select name, (SurfaceArea/Population) as area_per_head

from [Link];
 /*---------- Date function ---------------*/

select * from [Link];

drop table [Link];

Create table [Link]

stu_id int primary key auto_increment,

Mobile int,

email varchar(40),

admsn_date date

);

insert into [Link]

(Mobile,email,admsn_date) values

(123,'john@[Link]','2012-04-01'),

(219,'jim@[Link]','2015-04-01');

insert into [Link]

(email,admsn_date) values

('ram@[Link]','2013-04-01'),

('sam@[Link]','2015-04-01');
insert into [Link]

(Mobile,admsn_date) values

(516,'2017-06-01'),

(789,'2018-04-01');

Select * from [Link];

/*Show current system date*/

Select CURRENT_DATE();

Select CURDATE();

/*Show current system date*/

Select CURRENT_TIME();

Select CURTIME();

/*Show current date and time*/

Select CURRENT_TIMESTAMP();

/*Extract the date part from timestamp (having date and time both)*/

Select DATE(admsn_date) from [Link];

Select DATE(CURRENT_TIMESTAMP());

/*Add 30 days to admission date*/

Select ADDDATE(admsn_date, Interval 30 day)

from [Link];

Select DATE_ADD(admsn_date, Interval 30 day) from [Link];

/*Subtract 30 days from admission date*/

Select DATE_SUB(admsn_date, Interval 30 day)

from [Link];

/*Create a difference between current dat and admsn_date in number of days*/

Select DATEDIFF(CURDATE(), admsn_date) as TenureInDays from [Link];

/*Create a difference between current dat and admsn_date in number of years*/

Select DATEDIFF(CURDATE(), admsn_date)/365 as TenureInYears from [Link];

/*Find the age of an employee*/

Select DATEDIFF(Curdate(),'2018-01-01')/365;

/*Format admsn_date to show year*/

Select DATE_FORMAT(admsn_date,"%Y") from [Link];

/*Format admsn_date to show month name*/

Select DATE_FORMAT(admsn_date,"%M") from [Link];

/*Extract month name from date*/

Select MONTHNAME(admsn_date) from [Link];

/*Format admsn_date to show day number*/

Select DATE_FORMAT(admsn_date,"%D") from [Link];

/*use admsn_date to show day name*/

Select DAYNAME(admsn_date) from [Link];

/*Use admsn_date to show day of week*/

Select DAYOFWEEK(admsn_date) from [Link];

/*Use admsn_date to show day of year*/

Select DAYOFYEAR(admsn_date) from [Link];

/*EXTRACT FUNCTION*/

/*Extract month number*/

Select EXTRACT(MONTH from admsn_date) from [Link];

/*Extract year*/

Select EXTRACT(YEAR from admsn_date) from [Link];

/*Extract week number*/

Select EXTRACT(WEEK from admsn_date) from [Link];

/*Extract calendar quarter number*/

Select EXTRACT(QUARTER from admsn_date) from [Link];

Select QUARTER(admsn_date) from [Link];

/*Extract Hour, minute, seconds (if there is a timestamp)*/

Select EXTRACT(HOUR from admsn_date) from [Link];

Select EXTRACT(MINUTE from admsn_date) from [Link];

Select EXTRACT(SECOND from admsn_date) from [Link];

/*Extract last day of the month from a given date*/

Select LAST_DAY(admsn_date) from [Link];

 /* ----- Other important functions-------*/
/*IF Function*/

/*Show YES if population>0 else print NO*/

SELECT name,IF(Population>0, "YES", "NO") as LifeExists from [Link];

/*Print Not Available, wherever IndepYear is NULL*/

SELECT name,IFNULL(IndepYear,"Not Available") from [Link];

/*------- CASE FUNCTION --------*/

Select name,Continent,

Case Continent

when "Asia" Then "APAC"

When "North America" Then "AMS"

When "South America" Then "AMS"

When "Europe" Then "EMEA"

When "Africa" Then "EMEA"

Else "OTHER"

END) as Geo

from [Link];

SELECT * FROM [Link];

Select name, coalesce(IndepYear,LifeExpectancy) as Temp from [Link];

/*Coalesce Function*/

/*Extract first non null value from mobile and email*/

Select stu_id,COALESCE(Mobile,email) as Contact from [Link];