2 SECOND DAY.sql

SHOW databases;
USE WORLD;
SHOW tables;
describe CITY;
SELECT * FROM city;
SELECT * FROM CITY WHERE DISTRICT='Zuid-Holland';  -- WHERE CLAUSE WE WILL FILTER THE DATA

SELECT * FROM CITY WHERE COUNTRYCODE='ARG';  -- FILTER THE CITY COUNTRY CODE TO ARG

SELECT * FROM CITY WHERE ID='79'; -- SEARCH ID

SELECT * FROM CITY WHERE POPULATION>600000;  -- LEARN FILTER GREATER AND LESS THEN

SELECT * FROM CITY WHERE COUNTRYCODE!='ARG';  -- NOT EQUAL TOO

-- IN OPERATOR USE
SELECT * FROM COUNTRY WHERE CONTINENT IN ('NORTH AMERICA','EUROPE','ASIA');

-- INDEPYEAR: 

SELECT NAME, CONTINENT,INDEPYEAR FROM COUNTRY WHERE INDEPYEAR IN(1901,1960);  -- SEARCH SPECIFIC VALUE

SELECT NAME, CONTINENT,INDEPYEAR FROM COUNTRY WHERE INDEPYEAR BETWEEN 1901 AND 1960; -- TO SEARCH BETWEEN THR RANGE

SELECT NAME, CONTINENT,INDEPYEAR FROM COUNTRY WHERE INDEPYEAR NOT BETWEEN 1901 AND 1960; -- TO SEARCH NOT BETWEEN THE RANGE

-- QUESTIONS TO SOLVE

SHOW TABLES;
SELECT CODE,NAME,REGION FROM COUNTRY WHERE REGION != 'MIDDLE EAST';

SELECT NAME, INDEPYEAR, POPULATION, POPULATION*0.1+POPULATION AS NEW_POLULATION FROM COUNTRY;

SELECT * FROM COUNTRY WHERE LIFEEXPECTANCY IN (38.3 ,66.4);

-- SELECT NAME, CONTINENT, POPULATION, GNP, GNP NOT BETWEEN(5000 AND 10000) FROM COUNTRY;

-- LIKE OPERATOR 
-- PATTERN SEARCH
-- TO MEET WITH PATTERN
-- SPICIAL CHARACTER IS CALLED WILD CHARACTER
-- %[WE ARE FINDING ZERO OR MORE CHARACTER

SELECT NAME, CONTINENT FROM COUNTRY WHERE NAME LIKE 'A%';  -- FIND FIRST A CHARCTER NAME COLUOUMN

SELECT NAME, CONTINENT FROM COUNTRY WHERE NAME LIKE '%A'; -- FIND LAST A CHACTER

SELECT NAME, CONTINENT FROM COUNTRY WHERE NAME LIKE '%AD%'; -- FIND ANYWHERE AD IN NAME COLOUMN

-- UNDERSCORE 1 CHARACTER 
SELECT NAME, CONTINENT FROM COUNTRY WHERE NAME LIKE 'IN___';