What is SQL

SQL stands for Structured Query Language. SQL is used to access and manipulate the RDBMS database. RDBMS stands for Relational Database Management System.

RDBMS is a Transactional Database like Oracle, MySQL, SQL Server, etc.

SQL used for multiple operations.

1) Users can create a Database.

2) Users can drop Database.

3) Users can Rename Database.

4) Users can create Tables.

5) Users can drop the table.

6) Users can rename the table.

7) Users can delete records from the table.

8) Users can truncate records from the table.

9) Users can insert records in the table.

10) Users can update records in the table.

11) Users can create a view.

12) User can create store procedure.

13) Users can create Triggers.

14) Users can create functions.

SQL SELF JOIN

SELF JOIN is basically used when the table joins itself.

For SELECT Statement

Syntax:-


SELECT table_A.column_name(s), table_B.column_name(s)
FROM table_name as table_A
SELF JOIN
table_name AS table_B
ON table_A.column_name=table_B.column_name

Example:- Suppose we have employees table

employees Table:- this table has the employee’s details and manager_id is an employee_id.


+----+------------+-----------+----------------+-----------+------------+
| id | first_name | last_name | email          | country   | manager_id |
+----+------------+-----------+----------------+-----------+------------+
|  1 | John       | Tailor    | john@abc.com   | USA       |          0 |
|  2 | Rom        | Tailor    | rom@abc.com    | USA       |          1 |
|  3 | Andrew     | Symonds   | andrew@abc.com | Australia |          2 |
|  4 | Miacle     | Tailor    | miacle@abc.com | Australia |          1 |
|  5 | Sachin     | Tendulkar | sachin@abc.com | India     |          0 |
|  6 | Virat      | Kohli     | virat@abc.com  | India     |          5 |
|  7 | rohit      | NULL      | rohit@abc.com  | India     |          5 |
+----+------------+-----------+----------------+-----------+------------+

Find all employees who have manager.

Query:-


SELECT employees_A.id,employees_A.first_name,
employees_A.last_name,
employees_A.email,employees_A.country,
employees_B.first_name AS manager 
FROM employees AS employees_A
SELF JOIN
employees AS employees_B
On employees_A.manager_id=employees_B.id

Output:-


+----+------------+-----------+----------------+-----------+---------+
| id | first_name | last_name | email          | country   | manager |
+----+------------+-----------+----------------+-----------+---------+
|  2 | Rom        | Tailor    | rom@abc.com    | USA       | John    |
|  3 | Andrew     | Symonds   | andrew@abc.com | Australia | Rom     |
|  4 | Miacle     | Tailor    | miacle@abc.com | Australia | John    |
|  6 | Virat      | Kohli     | virat@abc.com  | India     | Sachin  |
|  7 | rohit      | NULL      | rohit@abc.com  | India     | Sachin  |
+----+------------+-----------+----------------+-----------+---------+
5 rows in set (0.00 sec)

SQL FULL OUTER JOIN

FULL JOIN returns all records from the left table and right table, if the records do not match then shows NULL value.
FULL JOIN is used between two tables.
FULL JOIN is also called FULL OUTER JOIN.

For SELECT Statement

Syntax:-


SELECT table_name1.column_name(s), table_name2.column_name(s)
FROM table_name1
FULL OUTER JOIN
table_name2
ON table_name1.column_name=table2.column_name

Example:- Suppose we have two table employees and emp_salary.

employees Table:- this table has the employee’s details.


+----+------------+-----------+----------------+-----------+
| id | first_name | last_name | email          | country   |
+----+------------+-----------+----------------+-----------+
|  1 | John       | Tailor    | john@abc.com   | USA       |
|  2 | Rom        | Tailor    | rom@abc.com    | USA       |
|  3 | Andrew     | Symonds   | andrew@abc.com | Australia |
|  4 | Miacle     | Tailor    | miacle@abc.com | Australia |
|  5 | Sachin     | Tendulkar | sachin@abc.com | India     |
|  6 | Virat      | Kohli     | virat@abc.com  | India     |
|  7 | rohit      | NULL      | rohit@abc.com  | India     |

emp_salary table:- this table employee’s salary details.

  
| id | emp_id | salary |
+----+--------+--------+
|  1 |      5 | 200000 |
|  2 |      6 | 180000 |
|  3 |      8 | 300000 |
|  4 |      9 | 400000 |
+----+--------+--------+

Find all employee’s details which has a salary or not.

Query:-


SELECT employees.id,employees.first_name,employees.last_name,
employees.email,employees.country,emp_salary.salary 
FROM employees
FULL OUTER JOIN
emp_salary
On employees.id=emp_salary.emp_id

Output:-


+----+------------+-----------+----------------+-----------+---------
| id | first_name | last_name | email          | country   | salary |
+----+------------+-----------+----------------+-----------+--------+
|  5 | Sachin     | Tendulkar | sachin@abc.com | India     | 200000 |
|  6 | Virat      | Kohli     | virat@abc.com  | India     | 180000 |
|  1 | John       | Tailor    | john@abc.com   | USA       |   NULL |
|  2 | Rom        | Tailor    | rom@abc.com    | USA       |   NULL |
|  3 | Andrew     | Symonds   | andrew@abc.com | Australia |   NULL |
|  4 | Miacle     | Tailor    | miacle@abc.com | Australia |   NULL |
|  7 | rohit      | NULL      | rohit@abc.com  | India     |   NULL |
|NULL| NULL       | NULL      | NULL           | NULL      | 300000 |
|NULL| NULL       | NULL      | NULL           | NULL      | 400000 |
+----+------------+-----------+----------------+-----------+--------+

SQL INNER JOIN

INNER JOIN returns only common records from both tables (left table and right table) if the records do not match then will not show. INNER JOIN is used between two tables.

For SELECT Statement

Syntax:-


SELECT table_name1.column_name(s), table_name2.column_name(s)
FROM table_name1
INNER JOIN
table_name2
ON table_name1.column_name=table2.column_name

Example:- Suppose we have two table employees and emp_salary.

employees Table:- this table has the employee’s details.


+----+------------+-----------+----------------+-----------+
| id | first_name | last_name | email          | country   |
+----+------------+-----------+----------------+-----------+
|  1 | John       | Tailor    | john@abc.com   | USA       |
|  2 | Rom        | Tailor    | rom@abc.com    | USA       |
|  3 | Andrew     | Symonds   | andrew@abc.com | Australia |
|  4 | Miacle     | Tailor    | miacle@abc.com | Australia |
|  5 | Sachin     | Tendulkar | sachin@abc.com | India     |
|  6 | Virat      | Kohli     | virat@abc.com  | India     |
|  7 | rohit      | NULL      | rohit@abc.com  | India     |

emp_salary table:- this table employee’s salary details.


| id | emp_id | salary |
+----+--------+--------+
|  1 |      5 | 200000 |
|  2 |      6 | 180000 |
|  3 |      8 | 300000 |
|  4 |      9 | 400000 |
+----+--------+--------+

Find the employees who have salary.

Query:-


SELECT employees.id,employees.first_name,employees.last_name,
employees.email,employees.country,emp_salary.salary 
FROM employees
INNER JOIN
emp_salary
On employees.id=emp_salary.emp_id

Output:-


+----+------------+-----------+----------------+---------+--------+
| id | first_name | last_name | email          | country | salary |
+----+------------+-----------+----------------+---------+--------+
|  5 | Sachin     | Tendulkar | sachin@abc.com | India   | 200000 |
|  6 | Virat      | Kohli     | virat@abc.com  | India   | 180000 |
+----+------------+-----------+----------------+---------+--------+

SQL RIGHT JOIN

RIGHT JOIN is used between two tables. RIGHT JOIN return all records from the right table and matches to left table, if the records do not match in the left table then shows NULL value.

RIGHT JOIN is also called RIGHT OUTER JOIN.

For SELECT Statement

Syntax:-


SELECT table_name1.column_name(s), table_name2.column_name(s)
FROM table_name1
RIGHT JOIN
table_name2
ON table_name1.column_name=table2.column_name

Example:- Suppose we have two table employees and emp_salary.

employees Table:- this table has employee’s details


+----+------------+-----------+----------------+-----------+
| id | first_name | last_name | email          | country   |
+----+------------+-----------+----------------+-----------+
|  1 | John       | Tailor    | john@abc.com   | USA       |
|  2 | Rom        | Tailor    | rom@abc.com    | USA       |
|  3 | Andrew     | Symonds   | andrew@abc.com | Australia |
|  4 | Miacle     | Tailor    | miacle@abc.com | Australia |
|  5 | Sachin     | Tendulkar | sachin@abc.com | India     |
|  6 | Virat      | Kohli     | virat@abc.com  | India     |
|  7 | rohit      | NULL      | rohit@abc.com  | India     |

emp_salary table:- this table has employee’s salary details


| id | emp_id | salary |
+----+--------+--------+
|  1 |      5 | 200000 |
|  2 |      6 | 180000 |
|  3 |      8 | 300000 |
|  4 |      9 | 400000 |
+----+--------+--------+

Find the employees which are getting salary

Query:-


SELECT employees.id,employees.first_name,employees.last_name,
employees.email,employees.country,emp_salary.salary 
FROM employees
RIGHT JOIN
emp_salary
On employees.id=emp_salary.emp_id

Output:-


+------+------------+-----------+----------------+---------+--------+
| id   | first_name | last_name | email          | country | salary |
+------+------------+-----------+----------------+---------+--------+
|    5 | Sachin     | Tendulkar | sachin@abc.com | India   | 200000 |
|    6 | Virat      | Kohli     | virat@abc.com  | India   | 180000 |
| NULL | NULL       | NULL      | NULL           | NULL    | 300000 |
| NULL | NULL       | NULL      | NULL           | NULL    | 400000 |
+------+------------+-----------+----------------+---------+--------+

SQL LEFT JOIN

LEFT JOIN is used between two tables. LEFT JOIN returns all records from the left table and matches to right table, if the records do not match in the right table then show NULL value.
LEFT JOIN is also called a LEFT OUTER JOIN.

For SELECT Statement

Syntax:-


SELECT table_name1.column_name(s), table_name2.column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

Example:- Suppose we have two table employees and emp_salary.

employees Table:- this table has employees records.


+----+------------+-----------+----------------+-----------+
| id | first_name | last_name | email          | country   |
+----+------------+-----------+----------------+-----------+
|  1 | John       | Tailor    | john@abc.com   | USA       |
|  2 | Rom        | Tailor    | rom@abc.com    | USA       |
|  3 | Andrew     | Symonds   | andrew@abc.com | Australia |
|  4 | Miacle     | Tailor    | miacle@abc.com | Australia |
|  5 | Sachin     | Tendulkar | sachin@abc.com | India     |
|  6 | Virat      | Kohli     | virat@abc.com  | India     |
|  7 | rohit      | NULL      | rohit@abc.com  | India     |

emp_salary table:- this table has employee’s salary details.


+----+--------+--------+
| id | emp_id | salary |
+----+--------+--------+
|  1 |      5 | 200000 |
|  2 |      6 | 180000 |
+----+--------+--------+

Find all the employees details with salary.

Query:-


SELECT employees.id,employees.first_name,employees.last_name,
employees.email,employees.country,emp_salary.salary 
FROM employees
LEFT JOIN emp_salary
On employees.id=emp_salary.emp_id

Output:-


+----+------------+-----------+----------------+-----------+--------+
| id | first_name | last_name | email          | country   | salary |
+----+------------+-----------+----------------+-----------+--------+
|  5 | Sachin     | Tendulkar | sachin@abc.com | India     | 200000 |
|  6 | Virat      | Kohli     | virat@abc.com  | India     | 180000 |
|  1 | John       | Tailor    | john@abc.com   | USA       |   NULL |
|  2 | Rom        | Tailor    | rom@abc.com    | USA       |   NULL |
|  3 | Andrew     | Symonds   | andrew@abc.com | Australia |   NULL |
|  4 | Miacle     | Tailor    | miacle@abc.com | Australia |   NULL |
|  7 | rohit      | NULL      | rohit@abc.com  | India     |   NULL |
+----+------------+-----------+----------------+-----------+--------+

SQL UNION ALL Operator

UNION ALL operator is used to get the combined results of two or more tables.
every select statement of tables must have the same fields name with the same data type and same order.
it is used to get all value from the two tables and value may be dulplcate.

For SELECT Statement

Syntax:-


SELECT column_name(s) 
FROM table_name1

UNION ALL

SELECT column_name(s) 
FROM table_name2

Example:- Suppose we have two table employees and users.

employees Table:-


+----+------------+-----------+----------------+-----------+
| id | first_name | last_name | email          | country   |
+----+------------+-----------+----------------+-----------+
|  1 | John       | Tailor    | john@abc.com   | USA       |
|  2 | Rom        | Tailor    | rom@abc.com    | USA       |
|  3 | Andrew     | Symonds   | andrew@abc.com | Australia |
|  4 | Miacle     | Tailor    | miacle@abc.com | Australia |
|  5 | Sachin     | Tendulkar | sachin@abc.com | India     |
|  6 | Virat      | Kohli     | virat@abc.com  | India     |
|  7 | rohit      | NULL      | rohit@abc.com  | India     |

users table:-


+----+------------+-----------+----------------+---------+
| id | first_name | last_name | email          | country |
+----+------------+-----------+----------------+---------+
|  1 | Ramesh     | Kumar     | ramesh@abc.com | India   |
|  2 | Suresh     | Kumar     | suresh@abc.com | India   |
|  3 | Anna       | Symonds   | anna@abc.com   | USA     |
+----+------------+-----------+----------------+---------+
3 rows in set (0.00 sec)

Find all country names from the employees and users table.

Query:-


SELECT country FROM employees
UNION ALL
SELECT country FROM users

Output:-


+-----------+
| country   |
+-----------+
| USA       |
| USA       |
| Australia |
| Australia |
| India     |
| India     |
| India     |
| India     |
| India     |
| USA       |
+-----------+
10 rows in set (0.00 sec)

SQL UNION Operator

UNION operator is used to get the combine results of two or more tables.
every select statement of tables must have same fields name with same data type and same order.
it used to get unique value from tables.

For SELECT Statement

Syntax:-


SELECT column_name(s) 
FROM table_name1

UNION

SELECT column_name(s) 
FROM table_name2

Example:- Suppose we have two table employees and users.

employees Table:-


+----+------------+-----------+----------------+-----------+
| id | first_name | last_name | email          | country   |
+----+------------+-----------+----------------+-----------+
|  1 | John       | Tailor    | john@abc.com   | USA       |
|  2 | Rom        | Tailor    | rom@abc.com    | USA       |
|  3 | Andrew     | Symonds   | andrew@abc.com | Australia |
|  4 | Miacle     | Tailor    | miacle@abc.com | Australia |
|  5 | Sachin     | Tendulkar | sachin@abc.com | India     |
|  6 | Virat      | Kohli     | virat@abc.com  | India     |
|  7 | rohit      | NULL      | rohit@abc.com  | India     |

users table:-


+----+------------+-----------+----------------+---------+
| id | first_name | last_name | email          | country |
+----+------------+-----------+----------------+---------+
|  1 | Ramesh     | Kumar     | ramesh@abc.com | India   |
|  2 | Suresh     | Kumar     | suresh@abc.com | India   |
|  3 | Anna       | Symonds   | anna@abc.com   | USA     |
+----+------------+-----------+----------------+---------+
3 rows in set (0.00 sec)

Find the country name which has unique name.

Query:-


SELECT country FROM employees
UNION
SELECT country FROM users

Output:-


+-----------+
| country   |
+-----------+
| USA       |
| Australia |
| India     |
+-----------+
3 rows in set (0.00 sec)

SQL EXISTS Operator

EXISTS operator is used to at least two tables. EXISTS operator return true when subquery get the one or more records.It is applicable in CRUD (create/read/update/delete) operation.

For SELECT Statement

Syntax:-


SELECT column_name(s) 
FROM table_name
WHERE EXISTS (subquery)

Example:- Suppose we have two table employees and emp_salary.

employees table:- this table has employee’s details


+----+------------+-----------+----------------+-----------+
| id | first_name | last_name | email          | country   |
+----+------------+-----------+----------------+-----------+
|  1 | John       | Tailor    | john@abc.com   | USA       |
|  2 | Rom        | Tailor    | rom@abc.com    | USA       |
|  3 | Andrew     | Symonds   | andrew@abc.com | Australia |
|  4 | Miacle     | Tailor    | miacle@abc.com | Australia |
|  5 | Sachin     | Tendulkar | sachin@abc.com | India     |
|  6 | Virat      | Kohli     | virat@abc.com  | India     |
|  7 | rohit      | NULL      | rohit@abc.com  | India     |

emp_salary table:- this table has employee’s salary details


+----+--------+--------+
| id | emp_id | salary |
+----+--------+--------+
|  1 |      5 | 200000 |
|  2 |      6 | 180000 |
+----+--------+--------+
2 rows in set (0.00 sec)

Find the employee’s details which has a salary.

Query:-


SELECT * FROM employees 
WHERE EXISTS 
(SELECT * FROM emp_salary WHERE employees.id=emp_salary.emp_id);

Output:-


+----+------------+-----------+----------------+---------+
| id | first_name | last_name | email          | country |
+----+------------+-----------+----------------+---------+
|  5 | Sachin     | Tendulkar | sachin@abc.com | India   |
|  6 | Virat      | Kohli     | virat@abc.com  | India   |
+----+------------+-----------+----------------+---------+
2 rows in set (0.00 sec)

SQL BETWEEN Operator

BETWEEN operator is used to getting the value within a given range. It is applicable in CRUD (create/read/update/delete) operation.

It is used between numeric range value
It is used between date range value
It is used between string range value

For SELECT Statement

Syntax:-


SELECT column_name(s) 
FROM table_name 
WHERE column_name range_value1 BETWEEN value2

Example:- Suppose we have employees table which has 7 records


+----+------------+-----------+----------------+-----------+
| id | first_name | last_name | email          | country   |
+----+------------+-----------+----------------+-----------+
|  1 | John       | Tailor    | john@abc.com   | USA       |
|  2 | Rom        | Tailor    | rom@abc.com    | USA       |
|  3 | Andrew     | Symonds   | andrew@abc.com | Australia |
|  4 | Miacle     | Tailor    | miacle@abc.com | Australia |
|  5 | Sachin     | Tendulkar | sachin@abc.com | India     |
|  6 | Virat      | Kohli     | virat@abc.com  | India     |
|  7 | rohit      | NULL      | rohit@abc.com  | India     |

Find the employees details that have id value between 4 to 6.

Query:-


SELECT * FROM employees WHERE id between 4 AND 6;

Output:-


+----+------------+-----------+----------------+-----------+
| id | first_name | last_name | email          | country   |
+----+------------+-----------+----------------+-----------+
|  4 | Miacle     | Tailor    | miacle@abc.com | Australia |
|  5 | Sachin     | Tendulkar | sachin@abc.com | India     |
|  6 | Virat      | Kohli     | virat@abc.com  | India     |
+----+------------+-----------+----------------+-----------+
3 rows in set (0.00 sec)

SQL IS NOT NULL Operator

IS NOT NULL operator is used to compare the value which has not NULL value. It is applicable in CRUD operation like create a query, read query, update query, and delete query.

For SELECT Statement

Syntax:-


SELECT column_name(s) 
FROM table_name 
WHERE column_name IS NOT NULL

Example:- Suppose we have employees table which has 7 records


+----+------------+-----------+----------------+-----------+
| id | first_name | last_name | email          | country   |
+----+------------+-----------+----------------+-----------+
|  1 | John       | Tailor    | john@abc.com   | USA       |
|  2 | Rom        | Tailor    | rom@abc.com    | USA       |
|  3 | Andrew     | Symonds   | andrew@abc.com | Australia |
|  4 | Miacle     | Tailor    | miacle@abc.com | Australia |
|  5 | Sachin     | Tendulkar | sachin@abc.com | India     |
|  6 | Virat      | Kohli     | virat@abc.com  | India     |
|  7 | rohit      | NULL      | rohit@abc.com  | India     |

Find the employees details that have last_name value is not NULL

Query:-


SELECT * FROM employees WHERE last_name IS NOT NULL;

Output:-


+----+------------+-----------+----------------+-----------+
| id | first_name | last_name | email          | country   |
+----+------------+-----------+----------------+-----------+
|  1 | John       | Tailor    | john@abc.com   | USA       |
|  2 | Rom        | Tailor    | rom@abc.com    | USA       |
|  3 | Andrew     | Symonds   | andrew@abc.com | Australia |
|  4 | Miacle     | Tailor    | miacle@abc.com | Australia |
|  5 | Sachin     | Tendulkar | sachin@abc.com | India     |
|  6 | Virat      | Kohli     | virat@abc.com  | India     |
+----+------------+-----------+----------------+-----------+
6 rows in set (0.00 sec)

Note:- In the above example rohit is not showing in the result Output because last_name has a NULL value.

SQL IS NULL Operator

IS NULL operator is used to comparing the NULL value. It is applicable in CRUD operation like create a query, read query, update query, and delete query.

For SELECT Statement

Syntax:-


SELECT column_name(s) 
FROM table_name 
WHERE column_name IS NULL

Example:- Suppose we have employees table which has 7 records


+----+------------+-----------+----------------+-----------+
| id | first_name | last_name | email          | country   |
+----+------------+-----------+----------------+-----------+
|  1 | John       | Tailor    | john@abc.com   | USA       |
|  2 | Rom        | Tailor    | rom@abc.com    | USA       |
|  3 | Andrew     | Symonds   | andrew@abc.com | Australia |
|  4 | Miacle     | Tailor    | miacle@abc.com | Australia |
|  5 | Sachin     | Tendulkar | sachin@abc.com | India     |
|  6 | Virat      | Kohli     | virat@abc.com  | India     |
|  7 | rohit      | NULL      | rohit@abc.com  | India     |

Find the employees details that have last_name value is NULL

Query:-


SELECT * FROM employees WHERE last_name IS NULL;

Output:-


+----+------------+-----------+---------------+---------+
| id | first_name | last_name | email         | country |
+----+------------+-----------+---------------+---------+
|  7 | rohit      | NULL      | rohit@abc.com | India   |
+----+------------+-----------+---------------+---------+
1 row in set (0.00 sec)

SQL NOT Operator

NOT operator is used to get the records if the condition is not true.
In SELECT Statement

Syntax:-


SELECT column(s) 
FROM table_name 
WHERE NOT column=value

Example:- Suppose we have employees table which has 7 records


+----+------------+-----------+----------------+-----------+
| id | first_name | last_name | email          | country   |
+----+------------+-----------+----------------+-----------+
|  1 | John       | Tailor    | john@abc.com   | USA       |
|  2 | Rom        | Tailor    | rom@abc.com    | USA       |
|  3 | Andrew     | Symonds   | andrew@abc.com | Australia |
|  4 | Miacle     | Tailor    | miacle@abc.com | Australia |
|  5 | Sachin     | Tendulkar | sachin@abc.com | India     |
|  6 | Virat      | Kohli     | virat@abc.com  | India     |
|  7 | rohit      | NULL      | rohit@abc.com  | India     |

Find the employees that country has not the “USA”


SELECT * FROM employees WHERE NOT country='USA';

Output:-


+----+------------+-----------+----------------+-----------+
| id | first_name | last_name | email          | country   |
+----+------------+-----------+----------------+-----------+
|  3 | Andrew     | Symonds   | andrew@abc.com | Australia |
|  4 | Miacle     | Tailor    | miacle@abc.com | Australia |
|  5 | Sachin     | Tendulkar | sachin@abc.com | India     |
|  6 | Virat      | Kohli     | virat@abc.com  | India     |
|  7 | rohit      | NULL      | rohit@abc.com  | India     |
+----+------------+-----------+----------------+-----------+
5 rows in set (0.00 sec)

SQL IN Operator

IN operator is used to match the value from the list of values.
It is a short form of multiple OR condition.

In SELECT Statement

Syntax:-


SELECT column(s) 
FROM table_name 
WHERE column
IN (value1, value2.......valueN)

Example:- Suppose we have employees table which has 7 records


+----+------------+-----------+----------------+-----------+
| id | first_name | last_name | email          | country   |
+----+------------+-----------+----------------+-----------+
|  1 | John       | Tailor    | john@abc.com   | USA       |
|  2 | Rom        | Tailor    | rom@abc.com    | USA       |
|  3 | Andrew     | Symonds   | andrew@abc.com | Australia |
|  4 | Miacle     | Tailor    | miacle@abc.com | Australia |
|  5 | Sachin     | Tendulkar | sachin@abc.com | India     |
|  6 | Virat      | Kohli     | virat@abc.com  | India     |
|  7 | rohit      | NULL      | rohit@abc.com  | India     |
Q:- Find the employees that have last_name Tendulkar OR Kohli

SELECT * FROM employees
WHERE last_name IN ('Tendulkar', 'Kohli');

Output:-


+----+------------+-----------+----------------+---------+
| id | first_name | last_name | email          | country |
+----+------------+-----------+----------------+---------+
|  5 | Sachin     | Tendulkar | sachin@abc.com | India   |
|  6 | Virat      | Kohli     | virat@abc.com  | India   |
+----+------------+-----------+----------------+---------+
2 rows in set (0.00 sec)

SQL LIKE Operator

LIKE operator is used to searching the records, It has two patterns.

1) % pattern It is used to search for single or multiple characters.

2) _ pattern It is used to search for one character.

In SELECT Statement

Syntax:-


SELECT column(s) 
FROM table_name 
WHERE column
LIKE pattern

Example:- Suppose we have employees table which has 7 records


+----+------------+-----------+----------------+-----------+
| id | first_name | last_name | email          | country   |
+----+------------+-----------+----------------+-----------+
|  1 | John       | Tailor    | john@abc.com   | USA       |
|  2 | Rom        | Tailor    | rom@abc.com    | USA       |
|  3 | Andrew     | Symonds   | andrew@abc.com | Australia |
|  4 | Miacle     | Tailor    | miacle@abc.com | Australia |
|  5 | Sachin     | Tendulkar | sachin@abc.com | India     |
|  6 | Virat      | Kohli     | virat@abc.com  | India     |
|  7 | rohit      | NULL      | rohit@abc.com  | India     |

Use % pattern

CASE 1:- Find the record that has “T” in the first position in the last_name field.


SELECT * FROM employees where last_name LIKE 'T%';

Output:-


+----+------------+-----------+----------------+-----------+
| id | first_name | last_name | email          | country   |
+----+------------+-----------+----------------+-----------+
|  1 | John       | Tailor    | john@abc.com   | USA       |
|  2 | Rom        | Tailor    | rom@abc.com    | USA       |
|  4 | Miacle     | Tailor    | miacle@abc.com | Australia |
|  5 | Sachin     | Tendulkar | sachin@abc.com | India     |
+----+------------+-----------+----------------+-----------+
4 rows in set (0.00 sec)

CASE 2:- Find the record that has “in” characters in the last position in the first_name field.


SELECT * FROM employees where first_name LIKE '%in';

Output:-



+----+------------+-----------+----------------+---------+
| id | first_name | last_name | email          | country |
+----+------------+-----------+----------------+---------+
|  5 | Sachin     | Tendulkar | sachin@abc.com | India   |
+----+------------+-----------+----------------+---------+
1 row in set (0.00 sec)

CASE 3:- Find the record that has “ai” in any position in the last_name field.


SELECT * FROM employees where last_name LIKE '%ai%';

Output:-



+----+------------+-----------+----------------+-----------+
| id | first_name | last_name | email          | country   |
+----+------------+-----------+----------------+-----------+
|  1 | John       | Tailor    | john@abc.com   | USA       |
|  2 | Rom        | Tailor    | rom@abc.com    | USA       |
|  4 | Miacle     | Tailor    | miacle@abc.com | Australia |
+----+------------+-----------+----------------+-----------+
3 rows in set (0.00 sec)

Use _ pattern

CASE 1:- Find the record that has the second character “o” in the first_name field.


SELECT * FROM employees where first_name LIKE '_o%';

Output:-


+----+------------+-----------+---------------+---------+
| id | first_name | last_name | email         | country |
+----+------------+-----------+---------------+---------+
|  1 | John       | Tailor    | john@abc.com  | USA     |
|  2 | Rom        | Tailor    | rom@abc.com   | USA     |
|  7 | rohit      | NULL      | rohit@abc.com | India   |
+----+------------+-----------+---------------+---------+
3 rows in set (0.00 sec)

CASE 2:- Find the record that has a character start with “Indi” in the email field.


SELECT * FROM employees where country LIKE 'Indi_%';

Output:-


+----+------------+-----------+----------------+---------+
| id | first_name | last_name | email          | country |
+----+------------+-----------+----------------+---------+
|  5 | Sachin     | Tendulkar | sachin@abc.com | India   |
|  6 | Virat      | Kohli     | virat@abc.com  | India   |
|  7 | rohit      | NULL      | rohit@abc.com  | India   |
+----+------------+-----------+----------------+---------+
3 rows in set (0.00 sec)

SQL AND & OR Operator

Sometimes we need to combine AND & OR condition in one query. We can apply SELECT, INSERT, UPDATE, and DELETE Statement.

In SELECT Statement

Syntax:-



SELECT column(s) 
FROM table_name 
WHERE condition1
AND condition2
-------------
OR conditionN

Example:- Suppose we have employees table which has 7 records


+----+------------+-----------+----------------+-----------+
| id | first_name | last_name | email          | country   |
+----+------------+-----------+----------------+-----------+
|  1 | John       | Tailor    | john@abc.com   | USA       |
|  2 | Rom        | Tailor    | rom@abc.com    | USA       |
|  3 | Andrew     | Symonds   | andrew@abc.com | Australia |
|  4 | Miacle     | Tailor    | miacle@abc.com | Australia |
|  5 | Sachin     | Tendulkar | sachin@abc.com | India     |
|  6 | Virat      | Kohli     | virat@abc.com  | India     |
|  7 | rohit      | NULL      | rohit@abc.com  | India     |
Now, we have to get the employees which last_name is Tailor and country is not Australia OR id is 6.

SELECT * FROM employees 
where (last_name='Tailor' AND country!='Australia') 
OR id=6;

Output:-


+----+------------+-----------+---------------+---------+
| id | first_name | last_name | email         | country |
+----+------------+-----------+---------------+---------+
|  1 | John       | Tailor    | john@abc.com  | USA     |
|  2 | Rom        | Tailor    | rom@abc.com   | USA     |
|  6 | Virat      | Kohli     | virat@abc.com | India   |
+----+------------+-----------+---------------+---------+
3 rows in set (0.00 sec)

SQL OR Operator

OR Operator works togther with WHERE Clause, OR operator display records when any condition is met. OR Operator is a part of the logical operator.

In SELECT Statement

Syntax:-


SELECT column(s) 
FROM table_name 
WHERE condition1 OR condition2..

Example:- Suppose we have employees table which has 7 records


+----+------------+-----------+----------------+-----------+
| id | first_name | last_name | email          | country   |
+----+------------+-----------+----------------+-----------+
|  1 | John       | Tailor    | john@abc.com   | USA       |
|  2 | Rom        | Tailor    | rom@abc.com    | USA       |
|  3 | Andrew     | Symonds   | andrew@abc.com | Australia |
|  4 | Miacle     | clerk     | miacle@abc.com | Australia |
|  5 | Sachin     | Tendulkar | sachin@abc.com | India     |
|  6 | Virat      | Kohli     | virat@abc.com  | India     |
|  7 | rohit      | NULL      | rohit@abc.com  | India     |

Now, we have to get the employees which country has INDIA OR USA.


SELECT * FROM employees where country='India' OR country='USA';

Output:-


+----+------------+-----------+----------------+---------+
| id | first_name | last_name | email          | country |
+----+------------+-----------+----------------+---------+
|  1 | John       | Tailor    | john@abc.com   | USA     |
|  2 | Rom        | Tailor    | rom@abc.com    | USA     |
|  5 | Sachin     | Tendulkar | sachin@abc.com | India   |
|  6 | Virat      | Kohli     | virat@abc.com  | India   |
|  7 | rohit      | NULL      | rohit@abc.com  | India   |
+----+------------+-----------+----------------+---------+
5 rows in set (0.00 sec)

SQL AND Operator

AND Operator is used to applying multiple conditions in the query. AND Operator is a part of the logical operator and It is applicable in INSERT/UPDATE/DELETE operations.

In SELECT Statement

Syntax:-


SELECT column(s) 
FROM table_name 
WHERE condition1 AND condition2 AND condition3....

In UPDATE Statement

Syntax:-


UPDATE table_name
SET column1=value1, column2=value2.....
WHERE condition1 AND condition2 AND condition3....

In INSERT Statement

Syntax:-


INSERT INTO table_name (column1,column2.....) 
VALUES (value1,value2....)
WHERE condition1 AND condition2 AND condition3....

Example:- Suppose we have employees table which has 7 records


+----+------------+-----------+----------------+-----------+
| id | first_name | last_name | email          | country   |
+----+------------+-----------+----------------+-----------+
|  1 | John       | Tailor    | john@abc.com   | USA       |
|  2 | Rom        | Tailor    | rom@abc.com    | USA       |
|  3 | Andrew     | Symonds   | andrew@abc.com | Australia |
|  4 | Miacle     | clerk     | miacle@abc.com | Australia |
|  5 | Sachin     | Tendulkar | sachin@abc.com | India     |
|  6 | Virat      | Kohli     | virat@abc.com  | India     |
|  7 | rohit      | NULL      | rohit@abc.com  | India     |

Now, we have to get the employees who have country INDIA and firstName Virat.


SELECT * FROM employees
where country='India' AND first_name='Virat';

Output:-


 +----+------------+-----------+---------------+---------+
| id | first_name | last_name | email         | country |
+----+------------+-----------+---------------+---------+
|  6 | Virat      | Kohli     | virat@abc.com | India   |
+----+------------+-----------+---------------+---------+
1 row in set (0.00 sec)

SQL Where Clause

WHERE Clause is used to filter the record from the table through condition.

Syntax:-


SELECT column(s)
From table_name
WHERE [condition]

Example:- Suppose you have employees table which has 7 records.


+----+------------+-----------+----------------+-----------+
| id | first_name | last_name | email          | country   |
+----+------------+-----------+----------------+-----------+
|  1 | John       | Tailor    | john@abc.com   | USA       |
|  2 | Rom        | Tailor    | rom@abc.com    | USA       |
|  3 | Andrew     | Symonds   | andrew@abc.com | Australia |
|  4 | Miacle     | clerk     | miacle@abc.com | Australia |
|  5 | Sachin     | Tendulkar | sachin@abc.com | India     |
|  6 | Virat      | Kohli     | virat@abc.com  | India     |
|  7 | rohit      | NULL      | rohit@abc.com  | India     |
+----+------------+-----------+----------------+-----------+

Now you want to get the record, which country belongs to India


SELECT * FROM employees WHERE country='India'

Output:-



+----+------------+-----------+----------------+---------+
| id | first_name | last_name | email          | country |
+----+------------+-----------+----------------+---------+
|  5 | Sachin     | Tendulkar | sachin@abc.com | India   |
|  6 | Virat      | Kohli     | virat@abc.com  | India   |
|  7 | rohit      | NULL      | rohit@abc.com  | India   |
+----+------------+-----------+----------------+---------+
3 rows in set (0.00 sec)


SQL Having Clause

HAVING Clause is used to filter the record based on group rows. Having Clause always used with Group By clause.

Syntax:-


SELECT column(s)
From table_name
WHERE [condition]
GROUP BY column(s)
HAVING [condition]

Example:-

Example:-Suppose you have an employees table that have 7 records.


+----+------------+-----------+----------------+-----------+
| id | first_name | last_name | email          | country   |
+----+------------+-----------+----------------+-----------+
|  1 | John       | Tailor    | john@abc.com   | USA       |
|  2 | Rom        | Tailor    | rom@abc.com    | USA       |
|  3 | Andrew     | Symonds   | andrew@abc.com | Australia |
|  4 | Miacle     | clerk     | miacle@abc.com | Australia |
|  5 | Sachin     | Tendulkar | sachin@abc.com | India     |
|  6 | Virat      | Kohli     | virat@abc.com  | India     |
|  7 | rohit      | NULL      | rohit@abc.com  | India     |
+----+------------+-----------+----------------+-----------+
Now you want to get the number of employees more than 2 belongs to each country.

SELECT COUNT(id), country 
FROM employees 
GROUP BY country HAVING COUNT(id)>2

Output:-


+-----------+---------+
| COUNT(id) | country |
+-----------+---------+
|         3 | India   |
+-----------+---------+
1 row in set (0.00 sec)

SQL Group By Clause

Group By clause is used with SELECT Statement to get the identical data.
Aggregate function like count(), max(), min(), sum() and avg() are also used Group By clause.

Syntax:-


SELECT column(s)
FROM table_name 
WHERE [condition] 
GROUP By coulumn(s)
ORDER BY column(s)

Example:-Suppose you have an employee table that has 7 records.


+----+------------+-----------+----------------+-----------+
| id | first_name | last_name | email          | country   |
+----+------------+-----------+----------------+-----------+
|  1 | John       | Tailor    | john@abc.com   | USA       |
|  2 | Rom        | Tailor    | rom@abc.com    | USA       |
|  3 | Andrew     | Symonds   | andrew@abc.com | Australia |
|  4 | Miacle     | clerk     | miacle@abc.com | Australia |
|  5 | Sachin     | Tendulkar | sachin@abc.com | India     |
|  6 | Virat      | Kohli     | virat@abc.com  | India     |
|  7 | rohit      | NULL      | rohit@abc.com  | India     |
+----+------------+-----------+----------------+-----------+
Now you want to get the number of employees belongs to each country

SELECT count(id), country from employees GROUP BY country

Output:-


+-----------+-----------+
| count(id) | country   |
+-----------+-----------+
|         2 | Australia |
|         3 | India     |
|         2 | USA       |
+-----------+-----------+
3 rows in set (0.00 sec)

SQL Order By Clause

Order By clause is used to sorts the record in Ascending or Descending Order.

1) ORDER BY ASC:- It is used to sorts the record in ascending order.


SELECT * FROM table_name OREDR BY column_name ASC

Example:- Suppose we have employees table which has 7 records


+----+------------+-----------+----------------+------------+
| id | first_name | last_name | email          | address    |
+----+------------+-----------+----------------+------------+
|  1 | John       | Tailor    | john@abc.com   | California |
|  2 | Rom        | Tailor    | rom@abc.com    | California |
|  3 | Andrew     | Symonds   | andrew@abc.com | Sydney     |
|  4 | Miacle     | clerk     | miacle@abc.com | sydney     |
|  5 | Sachin     | Tendulkar | sachin@abc.com | Mumbai     |
|  6 | Virat      | Kohli     | virat@abc.com  | delhi      |
|  7 | rohit      | NULL      | rohit@abc.com  | NULL       |
+----+------------+-----------+----------------+------------+

Now, we want to get the record Order by first_name in ascending order


SELECT * FROM employees ORDER BY first_name ASC

Output:-


+----+------------+-----------+----------------+------------+
| id | first_name | last_name | email          | address    |
+----+------------+-----------+----------------+------------+
|  3 | Andrew     | Symonds   | andrew@abc.com | Sydney     |
|  1 | John       | Tailor    | john@abc.com   | California |
|  4 | Miacle     | clerk     | miacle@abc.com | sydney     |
|  7 | rohit      | NULL      | rohit@abc.com  | NULL       |
|  2 | Rom        | Tailor    | rom@abc.com    | California |
|  5 | Sachin     | Tendulkar | sachin@abc.com | Mumbai     |
|  6 | Virat      | Kohli     | virat@abc.com  | delhi      |
+----+------------+-----------+----------------+------------+

2) ORDER BY DESC:- It is used to sorts the record in descending order.

Syntax:-


SELECT * FROM table_name OREDR BY column_name DESC

Now, we want to get the record Order by first_name in descending order


SELECT * FROM employees ORDER BY first_name DESC

Output:-


+----+------------+-----------+----------------+------------+
| id | first_name | last_name | email          | address    |
+----+------------+-----------+----------------+------------+
|  6 | Virat      | Kohli     | virat@abc.com  | delhi      |
|  5 | Sachin     | Tendulkar | sachin@abc.com | Mumbai     |
|  2 | Rom        | Tailor    | rom@abc.com    | California |
|  7 | rohit      | NULL      | rohit@abc.com  | NULL       |
|  4 | Miacle     | clerk     | miacle@abc.com | sydney     |
|  1 | John       | Tailor    | john@abc.com   | California |
|  3 | Andrew     | Symonds   | andrew@abc.com | Sydney     |
+----+------------+-----------+----------------+------------+

SQL Distinct Clause

DISTINCT command is used to get the unique value from the table. it is not considered duplicate value. It is used with SELECT Statement

Syntax:-


SELECT DISTINCT column_name FROM table_name

Example:- Suppose the employees table has 6 records


+----+------------+-----------+----------------+------------+
| id | first_name | last_name | email          | city       |
+----+------------+-----------+----------------+------------+
|  1 | John       | Tailor    | john@abc.com   | California |
|  2 | Rom        | Tailor    | rom@abc.com    | California |
|  3 | Andrew     | Symonds   | andrew@abc.com | Sydney     |
|  4 | Miacle     | clerk     | miacle@abc.com | sydney     |
|  5 | Sachin     | Tendulkar | sachin@abc.com | Mumbai     |
|  6 | Virat      | Kohli     | virat@abc.com  | delhi      |
+----+------------+-----------+----------------+------------+

Now, we need to get the unique city name.


SELECT DISTINCT city FROM employees

Output:-


+----+-------
| city       |
+----+--------
| California |
| Sydney     |
| Mumbai     |
| delhi      |
+----+--------

SQL Truncate Statement

TRUNCATE command is used to remove the existing records from the table, it works the same as DELETE command.

Syntax:-


TRUNCATE TABLE table_name

Difference between DELETE and TRUNCATE Command

DELETE:- When you delete the records from the table then records will be removed but the space of records will exist in the table.

TRUNCATE:-When you truncate the records from the table then records will be removed with exists space.

Example:-
Suppose you have two records in the employees table


 SELECT * FROM employees

Output:-



+----+------------+-----------+----------------+------------+
| id | first_name | last_name | email          | address    |
+----+------------+-----------+----------------+------------+
|  1 | Virat      | Kohli     | virat@abc.com  | Delhi      |
|  2 | rohit      | Sharma    | rohit@abc.com  | Mumbai     |
+----+------------+-----------+----------------+------------+

Note:- Where id is primary key with auto increment.

Example:-

FIRST CASE:- Now, You want to delete the record from DELETE Command


DELETE FROM employees

Now, check the employees table


SELECT * FROM employees

Output:- returned an empty result set

Now, Add new record into employees table


INSERT INTO employees(first_name,last_name,email,address) 
VALUES ("Sachin","Tendulkar","sachin@abc.com","Mumbai");

Now, check the employees table


SELECT * FROM employees


+----+------------+-----------+----------------+------------+
| id | first_name | last_name | email          | address    |
+----+------------+-----------+----------------+------------+
|  3 | Sachin     | Tendulkar | sachin@abc.com | Mumbai     |
+----+------------+-----------+----------------+------------+

So, This output is showing that after deleting the records from the table, space will exist so that is the reason id is started from 3.

SECOND CASE:- Now use TRUNCATE command to delete the records.


TRUNCATE TABLE employees

Now, No one record will be shown

Add new record into employees table


INSERT INTO employees(first_name,last_name,email,address) 
VALUES ("Sourav","Ganguly","sourav@abc.com","Calcutta");

Now, check the employees table


SELECT * FROM employees

+----+------------+-----------+----------------+------------+
| id | first_name | last_name | email          | address    |
+----+------------+-----------+----------------+------------+
|  1 | Sourav     | Ganguly   | sourav@abc.com | Calcutta   |
+----+------------+-----------+----------------+------------+

So, This output is showing that after deleting the records through TRUNCATE Command then space also deleted so that is the reason id is started from 1.

SQL Delete Statement

DELETE command is used to remove existing records from the table.

Syntax:-


DELETE FROM table_name
WHERE [CONDITION];

Example:-
Suppose you have two records in the employees table which has two records.


 SELECT * FROM employees

Output:-



+----+------------+-----------+----------------+------------+
| id | first_name | last_name | email          | address    |
+----+------------+-----------+----------------+------------+
|  1 | Virat      | Kohli     | virat@abc.com  | Delhi      |
|  2 | rohit      | Sharma    | rohit@abc.com  | Mumbai     |
+----+------------+-----------+----------------+------------+

Apply WHERE Condition

FIRST CASE:- Now, You want to delete the record which has id=1;


DELETE FROM employees WHERE id=1

Now, check the record of the table.



+----+------------+-----------+----------------+------------+
| id | first_name | last_name | email          | address    |
+----+------------+-----------+----------------+------------+
|  2 | rohit      | Sharma    | rohit@abc.com  | Mumbai     |
+----+------------+-----------+----------------+------------+

Without WHERE Condition

SECOND CASE:- If you do not use where condition then all records will be deleted.


DELETE FROM employees

Now check the results of the employees table


 SELECT * FROM employees

Output:- employees table would not have any record.

Note:- If you do not use WHERE condition on DELETE Statement then all records will be deleted from the table, so please delete the record carefully.

SQL Update Statement

UPDATE command is used to modify an existing record in the table.

Syntax:-


UPDATA table_name
SET column_1=new_value_1,
column_2=new_value_2,
column_3=new_value_3
...................
...................
column_n=new_value_n

WHERE [CONDITION]

Example:-

Suppose you have two records in the employees table


 SELECT * FROM employees

Output:-


+----+------------+-----------+----------------+------------+
| id | first_name | last_name | email          | address    |
+----+------------+-----------+----------------+------------+
|  1 | Virat      | Kohli     | virat@abc.com  | Delhi      |
|  2 | rohit      | Sharma    | rohit@abc.com  | Mumbai     |
+----+------------+-----------+----------------+------------+

Apply WHERE Condition

FIRST CASE:- Now, You want to update the address of Virat from Delhi to Mumbai



UPDATE employees SET address='Mumbai' where id=1

Now, check the table


+----+------------+-----------+----------------+------------+
| id | first_name | last_name | email          | address    |
+----+------------+-----------+----------------+------------+
|  1 | Virat      | Kohli     | virat@abc.com  | Mumbai     |
|  2 | rohit      | Sharma    | rohit@abc.com  | Mumbai     |
+----+------------+-----------+----------------+------------+

Without WHERE Condition

SECOND CASE:- If you do not use where condition then the record will be update in all rows.

Example:- If you want to update email of rohit@abc.com to rohitsharma@abc.com without where condition.


UPDATE employees SET email='rohitsharma@abc.com'

Now check the updated results in the employees table


 SELECT * FROM employees

Output:-



+----+------------+-----------+----------------+------------+
| id | first_name | last_name | email          | address    |
+----+------------+-----------+----------------+------------+
|  1 | Virat      | Kohli     | rohitsharma@abc.com | Mumbai|
|  2 | rohit      | Sharma    | rohitsharma@abc.com | Mumbai|
+----+------------+-----------+----------------+------------+
Note:- If you do not use WHERE condition on UPDATE Statement then it will be reflected in all rows, so please update the record carefully.

SQL Insert Statement

INSERT INTO command is used to add new record into the table.

there is two way to insert a record into the table.

1) If you want to insert records in all fields(columns) then do not need defines the columns.

Syntax:-


INSERT INTO table_name 
values(value_1,value_2,value_3,value_4....value_n) 

Example:-


INSERT INTO employees 
values(1,"Virat","Kohli","virat@abc.com","delhi") 

Now check the new record is saved into employees table


 SELECT * FROM employees

Output:-


+----+------------+-----------+----------------+------------+
| id | first_name | last_name | email          | address    |
+----+------------+-----------+----------------+------------+
|  1 | Virat      | Kohli     | virat@abc.com  | delhi      |
+----+------------+-----------+----------------+------------+

2) If you want to insert records in some fields (columns) then it should be defined.

Syntax:-


INSERT INTO table_name(column_1,column_2,column_3)
values(value_1,value_2,value_3) 

Example:-


INSERT INTO employees("id","first_name","email") 
values(2,"Rohit","rohit@abc.com")

Now check the new record is saved into employees table


 SELECT * FROM employees

Output:-


+----+------------+-----------+----------------+------------+
| id | first_name | last_name | email          | address    |
+----+------------+-----------+----------------+------------+
|  1 | Virat      | Kohli     | virat@abc.com  | delhi      |
|  2 | rohit      | NULL      | rohit@abc.com  | NULL       |
+----+------------+-----------+----------------+------------+

SQL Select Limit Statement

Select Limit statement is used to retrieve the limit records from the table.

Syntax:-


SELECT * FROM table_name LIMIT limit_number

Note:- limit_number is a numeric value.

Example:-
We have an employees table which has 5 records.


+----+------------+-----------+----------------+------------+
| id | first_name | last_name | email          | address    |
+----+------------+-----------+----------------+------------+
|  1 | John       | Tailor    | john@abc.com   | California |
|  2 | Rom        | Tailor    | rom@abc.com    | California |
|  3 | Andrew     | Symonds   | andrew@abc.com | Sydney     |
|  4 | Miacle     | clerk     | miacle@abc.com | sydney     |
|  5 | Sachin     | Tendulkar | sachin@abc.com | Mumbai     |

Now, we want to get two records from employees table


SELECT * FROM employees LIMIT 2

Output:-


| id | first_name | last_name | email        | address    |
+----+------------+-----------+--------------+------------+
|  1 | John       | Tailor    | john@abc.com | California |
|  2 | Rom        | Tailor    | rom@abc.com  | California |
+----+------------+-----------+--------------+------------+

SQL Select Statement

Select statement is used to retrieve the records from the table

If you need all records from the table

Syntax:-



SELECT * FROM table_name

Example 1:- Suppose we have employees table and we want all records from the employees table



SELECT * FROM employees

Output:-


+----+------------+-----------+----------------+------------+
| id | first_name | last_name | email          | address    |
+----+------------+-----------+----------------+------------+
|  1 | John       | Tailor    | john@abc.com   | California |
|  2 | Rom        | Tailor    | rom@abc.com    | California |
|  3 | Andrew     | Symonds   | andrew@abc.com | Sydney     |
|  4 | Miacle     | clerk     | miacle@abc.com | sydney     |
|  5 | Sachin     | Tendulkar | sachin@abc.com | Mumbai     |

Example 2:- Suppose we want to get specific records like id,first_name,email from employees table



SELECT id,first_name,email FROM employees

Output:-



+----+------------+----------------+
| id | first_name | email          |
+----+------------+----------------+
|  1 | John       | john@abc.com   |
|  2 | Rom        | rom@abc.com    |
|  3 | Andrew     | andrew@abc.com |
|  4 | Miacle     | miacle@abc.com |
|  5 | Sachin     | sachin@abc.com |
+----+------------+----------------+
5 rows in set (0.00 sec)

SQL Rename Table

RENAME command is used to set the new name of the exists table

Syntax:-


ALTER TABLE table_name RENAME TO new_table_name;  

Example:-

Suppose we have an employees table

Now, we change table name employees to users.


ALTER TABLE employees RENAME TO users;  

Now, get the results from the users table


SELECT * FROM  users;  

Output:-


+----+------------+-----------+----------------+------------+
| id | first_name | last_name | email          | address    |
+----+------------+-----------+----------------+------------+
|  1 | John       | Tailor    | john@abc.com   | California |
|  2 | Rom        | Tailor    | rom@abc.com    | California |
|  3 | Andrew     | Symonds   | andrew@abc.com | Sydney     |
|  4 | Miacle     | clerk     | miacle@abc.com | sydney     |
|  5 | Sachin     | Tendulkar | sachin@abc.com | Mumbai     |

SQL Drop Table

Drop table is used to delete the table with all records.

Syntax:-


DROP TABLE tableName

Example:- Suppose we have an employees table


+----+------------+-----------+----------------+------------+
| id | first_name | last_name | email          | address    |
+----+------------+-----------+----------------+------------+
|  1 | John       | Tailor    | john@abc.com   | California |
|  2 | Rom        | Tailor    | rom@abc.com    | California |
|  3 | Andrew     | Symonds   | andrew@abc.com | Sydney     |
|  4 | Miacle     | clerk     | miacle@abc.com | sydney     |
|  5 | Sachin     | Tendulkar | sachin@abc.com | Mumbai     |

Now Drop the table


DROP TABLE employees

After performing the operation, Now check employees table is exists or not


SELECT * FROM employees

Output:-

Table ’employees_system.employees’ doesn’t exist

SQL Alter Table

ALTER TABLE operation performs on exists table, through ALTER TABLE command we can ADD COLUMN, UPDATE COLUMN, and DROP COLUMN.

ALTER TABLE is also used to add INDEX, drop INDEX, ADD CONSTRAINT and DROP CONSTRAINT like (ADD UNIQUE/DROP UNIQUE/ADD FOREIGN KEY/DROP FOREIGN KEY), etc.

Add COLUMN INTO TABLE


ALTER TABLE tableName ADD columnName datatype

If you want to add multiple columns into the table

ALTER TABLE tableName ADD (columnName1 datatype, columnName2 datatype, columnName3 datatype……columnNameN datatype)

Suppose We have an employees table



+----+------------+-----------+----------------+------------+
| id | first_name | last_name | email          | address    |
+----+------------+-----------+----------------+------------+
|  1 | John       | Tailor    | john@abc.com   | California |
|  2 | Rom        | Tailor    | rom@abc.com    | California |
|  3 | Andrew     | Symonds   | andrew@abc.com | Sydney     |
|  4 | Miacle     | clerk     | miacle@abc.com | sydney     |
|  5 | Sachin     | Tendulkar | sachin@abc.com | Mumbai     |
+----+------------+-----------+----------------+------------+

Now Add column mobile_number into employees table


ALTER TABLE employees ADD mobile_number varchar(10)

Now, check employees table


+----+------------+-----------+----------------+------------+---------------+
| id | first_name | last_name | email          | address    | mobile_number |
+----+------------+-----------+----------------+------------+---------------+
|  1 | John       | Tailor    | john@abc.com   | California | NULL          |
|  2 | Rom        | Tailor    | rom@abc.com    | California | NULL          |
|  3 | Andrew     | Symonds   | andrew@abc.com | Sydney     | NULL          |
|  4 | Miacle     | clerk     | miacle@abc.com | sydney     | NULL          |
|  5 | Sachin     | Tendulkar | sachin@abc.com | Mumbai     | NULL          |
+----+------------+-----------+----------------+------------+---------------+

DROP COLUMN FROM TABLE


ALTER TABLE tableName DROP COLUMN columnName

Example:-
Now, we are going to drop mobile_number column from the employees table



ALTER TABLE employees DROP COLUMN mobile_number

Now, check employees table



+----+------------+-----------+----------------+------------+
| id | first_name | last_name | email          | address    |
+----+------------+-----------+----------------+------------+
|  1 | John       | Tailor    | john@abc.com   | California |
|  2 | Rom        | Tailor    | rom@abc.com    | California |
|  3 | Andrew     | Symonds   | andrew@abc.com | Sydney     |
|  4 | Miacle     | clerk     | miacle@abc.com | sydney     |
|  5 | Sachin     | Tendulkar | sachin@abc.com | Mumbai     |
+----+------------+-----------+----------------+------------+

SQL Create Table

The table is a collection of rows and columns. when you create a table then column must be defined with the datatype.

Syntax:-


CREATE TABLE tableName(
field1 datatype, 
field2 datatype, 
field3 datatype, 
field4 datatype)

Example:-

Now we are creating an employees table


CREATE TABLE employees(
id INT NOT NULL, 
first_name varchar(255) NOT NULL, 
last_name varchar(255), 
email varchar(50) NOT NULL,
address varchar(255) NOT NULL,
PRIMARY KEY (id)  
) 

Now, after created successfully then check the result through


DESC employees

+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | int(11)      | NO   | PRI | NULL    |       |
| first_name | varchar(255) | NO   |     | NULL    |       |
| last_name  | varchar(255) | YES  |     | NULL    |       |
| email      | varchar(50)  | NO   |     | NULL    |       |
| address    | varchar(255) | NO   |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
5 rows in set (0.06 sec)

SQL Select Database

If you have multiple databases and you want to perform the operation in the database then firstly select database after that you can perform the operation.

Syntax:-


USE databaseName

Now, firstly check the all database through below command


SHOW DATABASES;

Output:-


+--------------------+
| Database           |
+--------------------+
| information_schema |
| email_archive      |
| flood              |
| employee_system    |
| performance_schema |

Now, you want to work in employee_system then use below command

Example:-


USE employee_system;

SQL Rename Database

When you want to rename the database then firstly check new database name should not exist.

Syntax:-


RENAME DATABASE oldDatabaseName TO newDatabaseName

Example:-

Suppose you have database employee_management and you want to change the name to employee_system and you want to use below command


RENAME DATABASE employee_management TO employee_system

Now, you can check the database name is changed through below command


SHOW DATABASES

Output:-

employee_system

SQL Drop Database

When you delete the database then all records of the database will be deleted.

Syntax:-


DROP DATABASE databaseName

Example:-

Suppose you have 2 databases school_management and employee_management and you want to delete database school_management then use below command


DROP DATABASE school_management

Now, you can check the remaining database list through below command


SHOW DATABASES

Output:-

employee_management

SQL Create Database

The database is a collection of tables and it has a unique name.

Syntax:-


CREATE DATABASE databaseName

Example:-

If you want to create database employee_management then use below command


CREATE DATABASES employee_management

Now, you can check the created database through below command


SHOW DATABASES

Output:-
employee_management
schools_management