The questions are written within /* */
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;