SQL RIGHT JOIN Keyword

select * from employeeinfo;

name age address dob
Komal 25 Mumbai 1997-10-19
Ramesh 35 Delhi 1995-08-29
Kaushik 31 Indore 1991-05-05
Hardik 22 Hyderabad 1998-01-25
Glory 28 Kanpur 1990-12-15
Akshaya 29 Delhi 1985-01-28
 select * from employeedep;
name id department
Komal I010 Dev
Akshaya I016 Dev
Ramesh I026 Quality
Hardik I038 Ops
Kaushik I046 Quality
Glory I058 Ops

RIGHT JOIN Keyword:

SELECT employeedep.id, employeeinfo.name, employeedep.department from employeedep RIGHT JOIN employeeinfo ON employeeinfo.name = employeedep.name;

id name department
I010 Komal Dev
I026 Ramesh Quality
I046 Kaushik Quality
I038 Hardik Ops
I058 Glory Ops
I016 Akshaya Dev

SQL LEFT JOIN Keyword

select * from employeeinfo;

name age address dob
Komal 25 Mumbai 1997-10-19
Ramesh 35 Delhi 1995-08-29
Kaushik 31 Indore 1991-05-05
Hardik 22 Hyderabad 1998-01-25
Glory 28 Kanpur 1990-12-15
Akshaya 29 Delhi 1985-01-28
 select * from employeedep;
name id department
Komal I010 Dev
Akshaya I016 Dev
Ramesh I026 Quality
Hardik I038 Ops
Kaushik I046 Quality
Glory I058 Ops

LEFT JOIN Keyword:

SELECT employeeinfo.name, employeedep.department from employeeinfo LEFT JOIN employeedep ON employeeinfo.name = employeedep.name;

name department
Komal Dev
Ramesh Quality
Kaushik Quality
Hardik Ops
Glory Ops
Akshaya Dev

SQL INNER JOIN Keyword

select * from employeeinfo;

name age address dob
Komal 25 Mumbai 1997-10-19
Ramesh 35 Delhi 1995-08-29
Kaushik 31 Indore 1991-05-05
Hardik 22 Hyderabad 1998-01-25
Glory 28 Kanpur 1990-12-15
Akshaya 29 Delhi 1985-01-28
 select * from employeedep;
name id department
Komal I010 Dev
Akshaya I016 Dev
Ramesh I026 Quality
Hardik I038 Ops
Kaushik I046 Quality
Glory I058 Ops

INNER JOIN Keyword:

SELECT employeeinfo.name, employeedep.name from employeeinfo INNER JOIN employeedep ON employeeinfo.name = employeedep.name;

name name
Komal Komal
Akshaya Akshaya
Ramesh Ramesh
Hardik Hardik
Kaushik Kaushik
Glory Glory

SQL ALTER TABLE Statement

SHOW Table:

select * from employeeinfo;

name age address salary
Komal 25 Mumbai 25000
Ramesh 35 Delhi 25000
Kaushik 31 Indore 25000
Hardik 22 Hyderabad 25000
Glory 28 Kanpur 25000
Akshaya 29 Delhi 25000

ALTER Table:

ALTER Table employeeinfo DROP Column salary;

select * from employeeinfo;

name age address
Komal 25 Mumbai
Ramesh 35 Delhi
Kaushik 31 Indore
Hardik 22 Hyderabad
Glory 28 Kanpur
Akshaya 29 Delhi

ALTER TABLE employeeinfo ADD dob date;

select * from employeeinfo;

name age address dob
Komal 25 Mumbai NULL
Ramesh 35 Delhi NULL
Kaushik 31 Indore NULL
Hardik 22 Hyderabad NULL
Glory 28 Kanpur NULL
Akshaya 29 Delhi NULL

UPDATE employeeinfo SET dob=’1985/01/28′ where name=’Akshaya’;

select * from employeeinfo;

name age address dob
Komal 25 Mumbai 1997-10-19
Ramesh 35 Delhi 1995-08-29
Kaushik 31 Indore 1991-05-05
Hardik 22 Hyderabad 1998-01-25
Glory 28 Kanpur 1990-12-15
Akshaya 29 Delhi 1985-01-28

SQL Aliases

SHOW Table:

select * from employeeinfo;

name age address salary
Komal 25 Mumbai 25000
Ramesh 35 Delhi 25000
Kaushik 31 Indore 25000
Hardik 22 Hyderabad 25000
Glory 28 Kanpur 25000
Akshaya 29 Delhi 25000

select * from employeedep;

name id department
Komal I010 Dev
Akshaya I016 Dev
Ramesh I026 Quality
Hardik I038 Ops
Kaushik I046 Quality
Glory I058 Ops

SQL Aliases:

select d.id, i.name, i.age, d.department from employeeinfo AS i, employeedep AS d where i.name=’Komal’ AND d.id=’I010′;

id name age department
I010 Komal 25 Dev

SQL BETWEEN Operator

SHOW Table:

select * from employeeinfo;

name age address salary
Komal 25 Mumbai 25000
Ramesh 35 Delhi 25000
Kaushik 31 Indore 25000
Hardik 22 Hyderabad 25000
Glory 28 Kanpur 25000
Akshaya 29 Delhi 25000

SQL BETWEEN Operator:

select * from employeeinfo where age BETWEEN 20 AND 25;

name age address salary
Komal 25 Mumbai 25000
Hardik 22 Hyderabad 25000

select * from employeeinfo where age NOT BETWEEN 20 AND 25;

name age address salary
Ramesh 35 Delhi 25000
Kaushik 31 Indore 25000
Glory 28 Kanpur 25000
Akshaya 29 Delhi 25000

select * from employeeinfo where name BETWEEN ‘A’ AND ‘B’;

name age address salary
Akshaya 29 Delhi 25000

SQL IN Operator

SHOW Table:

select * from employeeinfo;

name age address salary
Komal 25 Mumbai 25000
Ramesh 35 Delhi 25000
Kaushik 31 Indore 25000
Hardik 22 Hyderabad 25000
Glory 28 Kanpur 25000
Akshaya 29 Delhi 25000

SQL IN Operator:

select * from employeeinfo where name IN(‘Komal’,’Ramesh’);

name age address salary
Komal 25 Mumbai 25000
Ramesh 35 Delhi 25000

SQL Wildcard Characters

SHOW Table:

select * from employeeinfo;

name age address salary
Komal 25 Mumbai 25000
Ramesh 35 Delhi 25000
Kaushik 31 Indore 25000
Hardik 22 Hyderabad 25000
Glory 28 Kanpur 25000
Akshaya 29 Delhi 25000

SQL Wildcards:

select * from employeeinfo where address LIKE ‘hyd%’;

name age address salary
Hardik 22 Hyderabad 25000

select * from employeeinfo where name LIKE ‘%mesh%’;

name age address salary
Ramesh 35 Delhi 25000

select * from employeeinfo where name LIKE ‘_omal’;

name age address salary
Komal 25 Mumbai 25000

select * from employeeinfo where name LIKE ‘K_m_l’;

name age address salary
Komal 25 Mumbai 25000

SQL LIKE Operator

SHOW Table:

select * from employeeinfo;

name age address salary
Komal 25 Mumbai 25000
Ramesh 35 Delhi 25000
Kaushik 31 Indore 25000
Hardik 22 Hyderabad 25000
Glory 28 Kanpur 25000
Akshaya 29 Delhi 25000

LIKE Operator:

select * from employeeinfo where name LIKE ‘K%’;

name age address salary
Komal 25 Mumbai 25000
Kaushik 31 Indore 25000

select * from employeeinfo where name LIKE ‘%l’;

name age address salary
Komal 25 Mumbai 25000

select * from employeeinfo where name LIKE ‘%me%’;

name age address salary
Ramesh 35 Delhi 25000

select * from employeeinfo where name NOT LIKE ‘%me%’;

name age address salary
Komal 25 Mumbai 25000
Kaushik 31 Indore 25000
Hardik 22 Hyderabad 25000
Glory 28 Kanpur 25000
Akshaya 29 Delhi 25000

SQL SELECT TOP Clause

SHOW Table:

select * from employeeinfo;

name age address salary
Komal 25 Mumbai 25000
Ramesh 35 Delhi 25000
Kaushik 31 Indore 25000
Hardik 22 Hyderabad 25000
Glory 28 Kanpur 25000
Akshaya 29 Delhi 25000

SELECT TOP Clause:

select * from employeeinfo LIMIT 2;

name age address salary
Komal 25 Mumbai 25000
Ramesh 35 Delhi 25000
1 2 3