test.sql

USE WORLD;

SELECT * FROM COUNTRY;
SELECT DISTINCT * FROM COUNTRY;

-- Arithmetic Operators

SELECT * FROM COUNTRY WHERE Population> 10000000;
SELECT * FROM COUNTRY WHERE Population< 9000;
SELECT * FROM COUNTRY WHERE Code= 'ATA';
SELECT * FROM COUNTRY WHERE GovernmentForm= 'Republic';
SELECT * FROM COUNTRY WHERE IndepYear is null;
SELECT DISTINCT GovernmentForm FROM COUNTRY;

-- Logical Operators

SELECT * FROM COUNTRY WHERE Population<90000 AND Code= 'ATA';
SELECT * FROM COUNTRY WHERE NOT Continent= 'North America';
SELECT * FROM countrylanguage WHERE Percentage BETWEEN 1 AND 3;
SELECT * FROM countrylanguage WHERE Percentage NOT BETWEEN 1 AND 3;
SELECT * FROM COUNTRY WHERE continent IN('Asia','North America');
SELECT * FROM COUNTRY WHERE continent NOT IN('Asia','North America');

-- Pattern Matching Operators (whiled card characters)
-- % --> Many character
-- _ --> Single character

SELECT * FROM COUNTRY WHERE Region LIKE'A%';
SELECT * FROM COUNTRY WHERE Region LIKE'%e';
SELECT * FROM COUNTRY WHERE Region LIKE'c%n';
SELECT * FROM COUNTRY WHERE Code LIKE '%I%';
SELECT * FROM COUNTRY WHERE Code NOT LIKE 'A%';
SELECT * FROM COUNTRY WHERE GovernmentForm LIKE '%i_';
SELECT * FROM COUNTRY WHERE name LIKE '____';

-- Built-in funtions

---------- String Function ------------------

SELECT UPPER(Name) FROM COUNTRY;
SELECT LOWER(Name) FROM COUNTRY;

SELECT LENGTH(Name) FROM COUNTRY;
SELECT * FROM COUNTRY WHERE LENGTH(NAME)=4;

SELECT TRIM('   Caribbean  ');
SELECT  
TRIM('A' FROM Name) ,
TRIM('a' FROM Continent),
TRIM('E' FROM Continent)
FROM COUNTRY;

SELECT INSTR('ASIA', 'I');

-- SELECT SUBSTRING('String',Start Point, End Point);

SELECT SUBSTRING('Caribbean',2,3);
SELECT SUBSTR('Southern Europe',2,10);
SELECT SUBSTR(NAME,3,5) FROM COUNTRY;

-- CONCAT- To join two string

SELECT CONCAT(Name,Continent) FULLNAME FROM COUNTRY; 

---------- Numeric Function ------------------

SELECT * FROM COUNTRY;

SELECT ABS('-7') ABS FROM COUNTRY;
SELECT SQRT(POPULATION) FROM COUNTRY;
SELECT MOD(10,3);
SELECT POWER(2,5);
SELECT TRUNCATE(GNP,1) FROM COUNTRY;
SELECT TRUNCATE(GNP,-2) FROM COUNTRY; -- Last two will be zero

SELECT GREATEST(100,200) FROM COUNTRY;
SELECT LEAST(100,200) FROM COUNTRY;
SELECT LEAST(Population,GNP) FROM COUNTRY;
SELECT GREATEST(Population,GNP) FROM COUNTRY;
SELECT LEAST(100,200) FROM COUNTRY;

---------- Date Function ------------------

SELECT CURRENT_DATE();
SELECT CURRENT_TIME();
SELECT NOW();
SELECT SYSDATE();
SELECT DAY("2024-07-15");
SELECT MONTH("2024-07-15");
SELECT YEAR("2024-07-15");

SELECT * FROM COUNTRY;
DESCRIBE COUNTRY;

ALTER TABLE COUNTRY MODIFY CREATED_AT DATETIME;

ALTER TABLE COUNTRY ADD(DATE VARCHAR(225));
ALTER TABLE COUNTRY RENAME COLUMN DATE TO CREATED_AT;	

UPDATE COUNTRY SET CREATED_AT= DATE(CONCAT(FLOOR(RAND() * 5 + 2020),"-", CEIL(RAND()*12),"-", CEIL(RAND()*28)));
SELECT * FROM COUNTRY WHERE YEAR(CREATED_AT)='2020';

---------- Aggregate Function ------------------

SELECT AVG(POPULATION) FROM COUNTRY;
SELECT SUM(POPULATION) FROM COUNTRY;
SELECT MIN(POPULATION) FROM COUNTRY;
SELECT MAX(POPULATION) FROM COUNTRY;
SELECT SUM(POPULATION) FROM COUNTRY;
SELECT COUNT(*) FROM COUNTRY;

SELECT * FROM CITY;
SELECT Name,Continent,SUM(POPULATION) FROM COUNTRY WHERE Continent= 'asia' GROUP BY Name;
SELECT Name,AVG(POPULATION) FROM COUNTRY GROUP BY Name;

-- Showing min and max population of continent

SELECT Continent,MIN(POPULATION),MAX(POPULATION) FROM COUNTRY GROUP BY Continent;

-- Showing the count of individual countrycode
SELECT CountryCode, COUNT(*) FROM CITY GROUP BY CountryCode;
SELECT CountryCode,DISTRICT, COUNT(*) FROM CITY GROUP BY CountryCode,DISTRICT;

-- NOTE- Before grouping we use where and after grouping having clause
--  WHERE----> GROUP BY----> HAVING
SELECT CountryCode, COUNT(*) FROM CITY GROUP BY CountryCode HAVING Count(*)>50;

SELECT * FROM CITY ORDER BY ID DESC;
SELECT * FROM CITY ORDER BY ID ASC;

SELECT CountryCode,COUNT(*) FROM CITY
GROUP BY CountryCode
HAVING Count(*)>50 
ORDER BY CountryCode DESC;