MySQL Notes
show databases;
use world;
show tables;
desc city;
/to show all content of table city/
select * from city limit 10;
/to show name,district,population/
select Name,District,Population from city limit 10;
/order by helps to sort records in ascending/
select Name,Population from city order by Name asc limit 10;
/order by in descending order for name/
select Name,Population from city order by Name desc limit 10;
select Name,Population from city order by Population desc limit 10;
select Name,Population from city order by Population asc;
/order by in multiple column/
select Name,Population from city order by Name asc,Population desc;
/where clause/
/*
MySQL Logical Operators
(i) AND
(ii) OR
(iii) BETWEEN
(IV) NOT BETWEEN
(V) LIKE
(VI) NOT
(VII) IS NULL
(VIII) IN
(IX) NOT IN
(X) IS NOT NULL
(XI) < > <= >= != == (RELATIONAL OPERATORS)
*/
/*
flow of writing mysql
from -> where -> select -> order by
*/
select * from city limit 10;
/display the districts of India/
select District from city where CountryCode="IND";
show tables;
desc country;
select * from country;
select Name,Population,LifeExpectancy from country limit 10;
/*to display name of countries where Population is more 100000 and life expectancy is
more 80*/
select Name,Population,LifeExpectancy
from country
where Population>=100000 and LifeExpectancy>=80;
/*To display the name of countries where Population is less than 100000 or
LifeExpectancy is more than 80
*/
select Name,Population,LifeExpectancy
from country
where Population<=100000 or LifeExpectancy>=80;
/*
to display the name of countries where population is between 10000 and 50000
*/
select Name,Population
from country
where Population
between 10000 and 50000
order by Name asc;
/to display the names of the coutries which are not in Asia,Afria and Europe/
select Name,Continent
from country
where
Continent not in ("Asia","Africa","Europe");
/to display all the countries whicha are not in the Africa continent/
select Name,Continent
from country
where (not Continent="Africa");
/to display all the names of country which begins with alphabet D/
select Name
from country
where Name Like 'D%';
/to display all the names of countries which begins A and ends with N/
select Name
from country
where Name Like 'A%N';
/to display the names of countries which has "and" word between the name/
select Name
from country
where Name Like '%and%';
/to display the independence year is blank/
select Name,IndepYear
from country
where IndepYear is null;
/*to display country names where indepedence year is not blank/
select Name,IndepYear
from country
where IndepYear is not null
order by IndepYear desc;
/to display the names of continent without any duplications/
select distinct Continent
from Country;
/to distinct on multiple columns/
select distinct Name,Continent
from Country;
/count() counts the number of rows in a table/
select count(*) from Country;
/count the number of continent/
select count(distinct continent) as "Number of continents"
from country;
/display the lowest population/
select min(Population)
from country;
/display the highest population/
select max(Population)
from country;
/display the total population/
select sum(Population)
from country;
/display the average population/
select avg(Population)
from country;