/*------------String functions -------------- */
SELECT CONCAT(Continent, " --> ", name) AS Continent_ctry
FROM world.country;
Select CONCAT(Continent," : ",name," : ",GovernmentForm)
from world.country;
SELECT CONCAT_WS(" || ", Continent, name, GovernmentForm)
AS CombinedFields from world.country;
/*from n postion, replace x characters with "- - -" */
SELECT INSERT(Continent, 3, 2, "xx") from world.country;
/*Search America in Continent and return position*/
SELECT Continent,INSTR(Continent, "America") AS Position
from world.country;
/*Upper and Lower case*/
SELECT LCASE(Continent) as continent_lower from world.country;
SELECT UCASE(Continent) as continent_upper from world.country;
/*Extract n character from the left of the string*/
Select LEFT(name,3) as ctry_code from world.country;
Select ucase(LEFT(name,3)) as ctry_code from world.country;
/*Extract n character from the right of the string*/
Select Right(name,3) as ctry_code from world.country;
/*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 world.country;
SELECT SUBSTR(name,2,4) as ctry_code from world.country;
SELECT Substring(name,2,4) as ctry_code from world.country;
/*Substring index to get string before a given delimiter*/
SELECT SUBSTRING_INDEX("SQL-is-amazing", "-", 2);
Select Substring_Index(Continent," ",1) from world.country;
Select * from world.country;
/*String Length*/
SELECT LENGTH(name) AS str_len from world.country;
SELECT CHAR_LENGTH(name) AS str_len from world.country;
SELECT CHARACTER_LENGTH(name) AS str_len from world.country;
/*Combine strings using concat */
SELECT CONCAT(Continent, " : ", name) AS Continent_ctry
FROM world.country;
/*Padding characters to make the string of stanmdard length*/
SELECT LPAD(Continent, 15, "x") as Code from world.country;
SELECT RPAD(Continent, 15, "*") as Code from world.country;
/*Remove Spaces using TRIM, LTRIM. RTRIM*/
Select trim(continent) as continent_2 from world.country;
Select Ltrim(continent) as continent_2 from world.country;
Select Rtrim(continent) as continent_2 from world.country;
/*Replace function*/
Select replace(Continent, "Asia", "APAC") as Geo from
world.country;
Select replace(continent,"Europe","EMEA") as Geo,
ucase(name),round(Population/1000,0) as Population_in_K
from world.country;
/*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 world.country;
/*Reverse string*/
Select reverse(name) as name2 from world.country;
Select * from world.country;
Select coalesce(IndepYear,Population, LifeExpectancy)
as MyColumn from world.country;
/*---------------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 world.country;
/*Ceiling an dFloor Function*/
Select ceil(LifeExpectancy) from world.country;
Select ceiling(LifeExpectancy) from world.country;
Select ceil(12.3);
Select floor(12.3);
Select floor(LifeExpectancy) from world.country;
/*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 world.country;
Select least(10,2,29,12,15);
Select least(Population, LifeExpectancy, SurfaceArea) as Lesser
from world.country;
/*Show Country wise per person land area*/
Select name, (SurfaceArea/Population) as area_per_head
from world.country;
/*---------- Date function ---------------*/
select * from world.country;
drop table abc.school;
Create table abc.school
stu_id int primary key auto_increment,
Mobile int,
email varchar(40),
admsn_date date
);
insert into abc.school
(Mobile,email,admsn_date) values
(123,'john@yahoo.com','2012-04-01'),
(219,'jim@gmail.com','2015-04-01');
insert into abc.school
(email,admsn_date) values
('ram@gmail.com','2013-04-01'),
('sam@gmail.com','2015-04-01');
insert into abc.school
(Mobile,admsn_date) values
(516,'2017-06-01'),
(789,'2018-04-01');
Select * from abc.school;
/*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 abc.school;
Select DATE(CURRENT_TIMESTAMP());
/*Add 30 days to admission date*/
Select ADDDATE(admsn_date, Interval 30 day)
from abc.school;
Select DATE_ADD(admsn_date, Interval 30 day) from abc.school;
/*Subtract 30 days from admission date*/
Select DATE_SUB(admsn_date, Interval 30 day)
from abc.school;
/*Create a difference between current dat and admsn_date in number of days*/
Select DATEDIFF(CURDATE(), admsn_date) as TenureInDays from abc.school;
/*Create a difference between current dat and admsn_date in number of years*/
Select DATEDIFF(CURDATE(), admsn_date)/365 as TenureInYears from abc.school;
/*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 abc.school;
/*Format admsn_date to show month name*/
Select DATE_FORMAT(admsn_date,"%M") from abc.school;
/*Extract month name from date*/
Select MONTHNAME(admsn_date) from abc.school;
/*Format admsn_date to show day number*/
Select DATE_FORMAT(admsn_date,"%D") from abc.school;
/*use admsn_date to show day name*/
Select DAYNAME(admsn_date) from abc.school;
/*Use admsn_date to show day of week*/
Select DAYOFWEEK(admsn_date) from abc.school;
/*Use admsn_date to show day of year*/
Select DAYOFYEAR(admsn_date) from abc.school;
/*EXTRACT FUNCTION*/
/*Extract month number*/
Select EXTRACT(MONTH from admsn_date) from abc.school;
/*Extract year*/
Select EXTRACT(YEAR from admsn_date) from abc.school;
/*Extract week number*/
Select EXTRACT(WEEK from admsn_date) from abc.school;
/*Extract calendar quarter number*/
Select EXTRACT(QUARTER from admsn_date) from abc.school;
Select QUARTER(admsn_date) from abc.school;
/*Extract Hour, minute, seconds (if there is a timestamp)*/
Select EXTRACT(HOUR from admsn_date) from abc.school;
Select EXTRACT(MINUTE from admsn_date) from abc.school;
Select EXTRACT(SECOND from admsn_date) from abc.school;
/*Extract last day of the month from a given date*/
Select LAST_DAY(admsn_date) from abc.school;
/* ----- Other important functions-------*/
/*IF Function*/
/*Show YES if population>0 else print NO*/
SELECT name,IF(Population>0, "YES", "NO") as LifeExists from world.country;
/*Print Not Available, wherever IndepYear is NULL*/
SELECT name,IFNULL(IndepYear,"Not Available") from world.country;
/*------- 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 world.country;
SELECT * FROM world.country;
Select name, coalesce(IndepYear,LifeExpectancy) as Temp from world.country;
/*Coalesce Function*/
/*Extract first non null value from mobile and email*/
Select stu_id,COALESCE(Mobile,email) as Contact from abc.school;
