SQL (Structured Query Language) is a standardized programming language used to manage and manipulate relational databases. It allows users to perform various operations on the data stored in a database, such as querying data, inserting new data, updating existing data, deleting data, and creating or modifying database structures (like tables and indexes).

OR

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 that includes Oracle, MySQL, SQL Server, etc.

Key Features of SQL:

1. Data Retrieval (Querying): SQL allows you to retrieve data from a database using commands like SELECT.

2. Data Manipulation: You can add, modify, or delete data using SQL commands such as:

INSERT INTO: Adds new data.

UPDATE: Modifies existing data.

DELETE: Removes data.

3. Data Definition: SQL lets you define and modify database structure (tables, relationships, etc.) using commands like:

CREATE: Creates a new database or database object (table, view, index).

ALTER: Modifies an existing database object.

DROP: Deletes a database object.

4. Data Control: SQL provides commands to control access to the data, such as:

GRANT: Grants permissions to users.

REVOKE: Removes permissions from users.

SQL is used for other operations also.

1) Users can create a view.

2) The user can create a store procedure.

3) Users can create Triggers.

4) Users can create functions.

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)

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 |
+----+------------+-----------+----------------+-----------+--------+

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 |
+----+------------+-----------+----------------+---------+--------+

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 |
+------+------------+-----------+----------------+---------+--------+

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 |
+----+------------+-----------+----------------+-----------+--------+

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)

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)

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)

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)

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.

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)

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)

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)

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)

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)

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)

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)

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)

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)

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)

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     |
+----+------------+-----------+----------------+------------+

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      |
+----+--------

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.

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.

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.

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       |
+----+------------+-----------+----------------+------------+

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 |
+----+------------+-----------+--------------+------------+

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)

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     |

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

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     |
+----+------------+-----------+----------------+------------+

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)

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;

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

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

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