SQL-Structure Query Language

 Introduction to SQL
 Data types
 Constraints
 SQL Commands (DDL, DML & DQL Commands)
 Operators in MySQL
 Introduction to SQL
 SQL (Structured Query Language) is the standard programming language used to communicate with,
manage, and manipulate relational databases.
Year/Era Milestone Event Key Impact
Introduced the concept of the Relational Data Model,
Dr. Edgar F. Codd publishes a groundbreaking
1970 proving that data could be stored in tables without
academic paper.
physical links.
They developed SEQUEL (Structured English Query
IBM researchers Donald Chamberlin and Raymond
Early 1970s Language) to easily query IBM's relational prototype
Boyce create a new language.
system, System R.

The acronym is officially shortened from SEQUEL to Changed due to a trademark dispute with a UK
Late 1970s
SQL. Hawker Siddeley aviation company.

Relational Software Inc. enters the commercial Released Oracle V2, the first commercially available
1979
market. SQL relational database system.

IBM releases its own commercial relational IBM debuted SQL/DS, cementing the commercial
1981
database system. viability of the relational model.
The American National Standards Institute (ANSI)
Major standardization bodies officially recognize
1986 & 1987 standardized SQL in 1986, and the ISO followed in
the language.
1987.
 Data types of MySQL
 Numeric Data types:
(a) INT : Used for normal sized integer both signed and unsigned.
Allowable range is -2147483648 to 2147483647
We can specify the width upto 11 digits.
(b) FLOAT : Used for flaoting point numbers (Decimal Values). We can define the display
length(M) and the number of decimals(D). It is default to 10 ,2 where 2 is the
number of decimals and 10 is the total number of digits including decimals.
Decimal precision can go to 24 places for a float.
(c) DATE: Used for entering dates in the format of YYYY-MM-DD. For example, December
30th, 1988 would be stored as 1973-12-30.
(d) CHAR: It is a fixed length string between 1 and 255 characters in length. For example,
CHAR(5) right padded with spaces to te specified length when stored. Defining
the length is not required, but the default is 1.
(e) VARCHAR: A variable length string bewwwen1 and 255 characters in length.
Eg: VARCHAR(15). We must define a length when creating a VARCHAR field.
 DDL Commands (Data Definition Language)
 A database scheme is defined by a set of definitions which are expressed by a special
language called a Data Definition Language.
 DDL Commands are used to create database & tables, alter table, drop table, rename
tables.
1. Creation of Databases & Utilization: [Link] open a database:
mysql> CREATE DATABASE ponvidyashram;
Query OK, 1 row affected (0.00 sec) mysql> USE ponvidyashram;
Database changed
2. To show the list of databases created: 4. To Delete or remove the Database:
mysql> SHOW databases;
+------------------------+ mysql> DROP DATABASE
| Database | ponvidyashram;
+------------------------+
| Ponvidyasharam | Query OK, 1 row affected (0.01 sec)
| student |
| employee |
+------------------------+
 DDL Commands (Data Definition Language)
1. Creation of a TABLE:
mysql> CREATE TABLE student [Link] view the attributes of a table:
-> (rollno int primary key NOT NULL, mysql> DESC customers;
+---------+--------------+------+-----+---------+-------+----------+
-> stuname varchar(20) NOT NULL, | Field | Type | Null | Key | Default | Extra |
-> dob date, +---------+--------------+------+-----+---------+-------+----------+
-> address varchar(50)); | name | varchar(255) | YES | | NULL | |
| address | varchar(255) | YES | | NULL | |
Query OK, 0 rows affected (0.01 sec) | salary | int | NO | | NULL | |
2. To show the list of TABLES created: +---------+--------------+------+-----+---------+---------+--------+
3 rows in set (0.01 sec)
mysql> SHOW TABLES;
+------------------------------------+
4. To add a new attribute to the existing table:
| Tables_in_ponvidyashram;|
mysql> ALTER TABLE customers add(salary int
+------------------------------------+
NOT NULL);
| student | Query OK, 0 rows affected (0.04 sec)
| customers | Records: 0 Duplicates: 0 Warnings: 0
+------------------------------------+
2 rows in set (0.00 sec)
 DDL Commands (Data Definition Language)
[Link] remove or delete a column from table: 8. To add a CONSTRAINT to the existing table:
mysql> ALTER TABLE home DROP homeplace; mysql> ALTER TABLE home ADD CONSTRAINT
Query OK, 0 rows affected (0.06 sec) UNIQUE(homeowner;
Records: 0 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
6. To add a PRIMARY KEY to the existing table: [Link] remove or delete a table:
mysql> ALTER TABLE home ADD PRIMARY KEY mysql> DROP TABLE student;
(homeno); Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
[Link] remove all the rows from a table:
mysql> TRUNCATE TABLE student;
Query OK, 0 rows affected (0.01 sec)
Note: The structure of the table remains same.
7. To remove a PRIMARY KEY from the
existing table: [Link] rename a table:
mysql> ALTER TABLE hut DROP PRIMARY KEY; mysql> RENAME TABLE home TO hut;
Query OK, 0 rows affected (0.07 sec) Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
 DML Commands (Data Manipulation Language)
1. Insert new values into
the table: [Link] a row/tuple from a table:
mysql> INSERT INTO employee VALUES mysql> DELETE FROM employee where
id=104;
(104,"gowri","kumar",'2008-06-30', Query OK, 1 row affected (0.00 sec)
56000,"Tiruvallur");
Query OK, 1 row affected (0.00 sec)

2. Update/Change the attribute

values in the table: 4. To delate more than one row from a table.:
mysql> UPDATE employee SET mysql> DELETE FROM employee WHERE
id in(101,103);
salary = 55000 WHERE id = 101; Query OK, 2 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
 SELECT Command in MySQL
 SELECT statement is used to retrieve and read data from one or more tables within a
database.
 It fetches the requested information and returns it in a structured layout of rows and
columns called a result-set.
a. To view all the columns and rows of data in a table:
Syntax: SELECT *FROM table_name;
Eg: SELECT *FROM employee;
+-----+------------+-----------+------------+----------+---------+----------------+
| id | first_name | last_name | hire_date | salary | address |
+-----+------------+-----------+------------+----------+---------+----------------+
| 101 | vimal | raj | 2012-12-20 | 55000.00 | NULL |
| 102 | Kamal | raj | 2017-05-05 | 55000.00 | NULL |
| 103 | prem | kumar | 2008-06-30 | 56000.00 | chennai |
+-----+------------+-----------+------------+----------+---------+------------------+
 SELECT Command in MySQL
b. To view selected columns of data in a table:
Syntax: SELECT column1,column2......FROM table_name;
+-----+------------+-----------+------------+----------+---------+----------------+
| id | first_name | last_name | hire_date | salary | address |
+-----+------------+-----------+------------+----------+---------+----------------+
| 101 | vimal | raj | 2012-12-20 | 55000.00 | NULL |
| 102 | Kamal | raj | 2017-05-05 | 55000.00 | NULL |
| 103 | prem | kumar | 2008-06-30 | 56000.00 | chennai |
+-----+------------+-----------+------------+----------+---------+------------------+
Eg: SELECT id,first_name,salary FROM employee;
+-----+---------------+-----------+
| id | first_name | salary |
+-----+---------------+-----------+
| 102 | Kamal | 55000.00 |
+-----+---------------+------------+
1 row in set (0.00 sec)
 SELECT Command in MySQL
c. To eliminate the duplicate/redundant data from the table using DISTINCT keyword:
Syntax: SELECT DISTINCT column_name FROM table_name;
+-------+-----------+
Eg:
| name | place |
mysql> SELECT DISTINCT place FROM address;
+-------+-----------+
+-----------+
| vimal | tiruttani | | place |
| Kamal | kalpakkam | +-----------+
| kumar | tiruttani | | tiruttani |
| malar | chennai | | kalpakkam |
| jaya | kalpakkam | | chennai |
| Guna | Avadi | | Avadi |
+-------+-----------+ +-----------+
6 rows in set (0.00 sec)
4 rows in set (0.00 sec)
 SELECT Command in MySQL
d. To display all rows of data using ALL keyword:
Syntax: SELECT ALL column_name FROM table_name;
+-------+-----------+ Eg:
| name | place | mysql> SELECT ALL place from address;
+-------+-----------+ +-----------+
| vimal | tiruttani | | place |
| Kamal | kalpakkam | +-----------+
| kumar | tiruttani | | tiruttani |
| malar | chennai |
| kalpakkam |
| tiruttani |
| jaya | kalpakkam |
| chennai |
| Guna | Avadi |
| kalpakkam |
+-------+-----------+ | Avadi |
6 rows in set (0.00 sec) +-----------+
6 rows in set (0.00 sec)
 SELECT Command in MySQL
e. To display the specific rows of data using WHERE clause:
Syntax: SELECT ALL column_name FROM table_name;
+-------+-----------+ Eg:
| name | place | mysql> SELECT name FROM address WHERE
+-------+-----------+ place=”tiruttani;
| vimal | tiruttani | +-----------+
| Kamal | kalpakkam | | name |
| kumar | tiruttani | +-----------+
| malar | chennai |
| vimal |
| kumar |
| jaya | tiruttani |
| jaya |
| Guna | Avadi |
+------------+
+-------+-----------+ 6 rows in set (0.00 sec)
6 rows in set (0.00 sec)
 SELECT Command in MySQL
e. To display the specific rows of data using IN operator:
Syntax: SELECT column1,column2...FROM tablename WHERE column_name IN(value1, value2, value3...);
+-------+-----------+ Eg:
| name | place | mysql> SELECT name, place FROM address WHERE
+-------+-----------+ place IN(“tiruttani”,”avadi”);
| vimal | tiruttani | +-------+-----------+
| Kamal | kalpakkam | | name | place |
| kumar | tiruttani | +-------+-----------+
| malar | chennai |
| vimal | tiruttani |
| kumar | tiruttani |
| jaya | tiruttani |
| jaya | tiruttani |
| Guna | Avadi |
| Guna | Avadi |
+-------+-----------+ +-------+-----------+
6 rows in set (0.00 sec) 4 rows in set (0.00 sec)
 SELECT Command in MySQL
e. To display the specific rows of data using BETWEEN operator:
Syntax: SELECT column1,column2...FROM tablename WHERE column_name BETWEEN value1 AND value2;
+-------+-----------+-----+--------+ Eg:
| name | place | AGE | mysql> SELECT name, place FROM address
+-------+-----------+-----+--------+ WHERE age BETWEEN 40 AND 45;
| vimal | tiruttani | 40 | +-------+-----------+
| Kamal | kalpakkam | 44 | | name | place |
| kumar | tiruttani | 42 | +-------+-----------+
| malar | chennai | 43 |
| vimal | tiruttani |
| kumar | tiruttani |
| jaya | tiruttani | 42 |
| jaya | tiruttani |
| Guna | Avadi | 46 |
| Guna | Avadi |
+-------+-----------+-----+--------+ +-------+-----------+
6 rows in set (0.00 sec) 4 rows in set (0.00 sec)
 SELECT Command in MySQL
e. To display the specific rows of data using ORDER BY:
Syntax: SELECT column1, column2 FROM table_name ORDER BY column1 [ASC | DESC];
+-------+-----------+-----+--------+ Eg:
| name | place | AGE | mysql> SELECT name, place,age FROM address
+-------+-----------+-----+--------+ ORDER BY age ;
| vimal | tiruttani | 40 | +----------+--------------- +------+
| Kamal | kalpakkam | 44 | | name | place | age |
| kumar | tiruttani | 42 | +----------+----------------+------+
| malar | chennai | 43 |
| vimal | tiruttani | 40 |
| kumar | tiruttani | 42 |
| jaya | tiruttani | 42 |
| jaya | tiruttani | 42 |
| Guna | Avadi | 46 |
| malar | chennai | 43 |
+-------+-----------+-----+--------+ | Kamal | kalpakkam | 44 |
6 rows in set (0.00 sec) | Guna | Avadi | 46 |
+----------+-----------------+------+
6 rows in set (0.00 sec)
 SELECT Command in MySQL
e. To display the specific rows of data using ORDER BY (DESC):
Syntax: SELECT column1, column2 FROM table_name ORDER BY column1 [ASC | DESC];
+-------+-----------+-----+--------+ Eg:
| name | place | AGE | mysql> SELECT name, place,age FROM address
+-------+-----------+-----+--------+ ORDER BY age DESC;
| vimal | tiruttani | 40 | +-------+------------------+-----+
| Kamal | kalpakkam | 44 | | name | place | age |
| kumar | tiruttani | 42 | +-------+------------------+------+
| malar | chennai | 43 |
| Guna | Avadi | 46 |
| Kamal | kalpakkam | 44 |
| jaya | tiruttani | 42 |
| malar | chennai | 43 |
| Guna | Avadi | 46 |
| kumar | tiruttani | 42 |
+-------+-----------+-----+--------+ | jaya | tiruttani | 42 |
6 rows in set (0.00 sec) | vimal | tiruttani | 40 |
+-------+-----------+-------+------+
6 rows in set (0.00 sec)
 MySQL Operators
a. Mathematical Operators:
Eg: select num1,num2, num1*num2 as
1. Addition ( + )
2. Substraction ( - )
product from numbers;
3. Multiplication ( * )
4. Division ( / ) +------+------+---------+
5. Integer Division ( DIV )
6. Modulus ( % or MOD )
| num1 | num2 | product |
+----------+---------+ +------+------+---------+
| num1 | num2 | | 5 | 10 | 50 |
+----------+---------+ | 15 | 20 | 300 |
| 5 | 10 | | 40 | 10 | 400 |
| 15 | 20 |
| 40 | 10 |
| 28 | 34 | 952 |
| 28 | 34 | | 12 | 12 | 144 |
| 12 | 12 | | 40 | 20 | 800 |
| 40 | 20 | +------+------+---------+
+------+------+ 6 rows in set (0.00 sec)
6 rows in set (0.00 sec)
 MySQL Operators
b. Relational Operators
Operator Meaning Example Condition
= Equal to salary = 50000

!= or <> Not equal to status != 'Active'

> Greater than age > 21

< Less than price < 10.00

>= Greater than or equal to marks >= 90

<= Less than or equal to stock <= 5

<=> NULL-safe equal to bonus <=> NULL

 MySQL Operators
c. Logical Operators

Operator Description Example

Returns true if all WHERE status = 'Active'

AND
conditions are true. AND age > 21

Returns true if at least WHERE city = 'Boston'

OR
one condition is true. OR city = 'Austin'

Reverses the value of a WHERE NOT status =

NOT
logical expression. 'Terminated'

WHERE condition1 XOR

Returns true if exactly condition2
XOR
one operand is true.
 Aliasing in MySQL ( ‘as’ command)
 Column aliases rename the heading of an output column in the query results.
 They are especially useful when working with calculations, string concatenations, or aggregate functions
(like SUM or AVG) that would otherwise return an unnamed or ugly column.
Syntax: SELECT column_name AS alias_name
FROM table_name;
Eg: select num1,num2, num1*num2 as product from
Eg:
numbers;
+----------+---------+
+------+------+---------+
| num1 | num2 |
| num1 | num2 | product |
+----------+---------+
+------+------+---------+
| 5 | 10 |
| 5 | 10 | 50 |
| 15 | 20 |
| 15 | 20 | 300 |
| 40 | 10 |
| 40 | 10 | 400 |
| 28 | 34 |
| 28 | 34 | 952 |
| 12 | 12 | | 12 | 12 | 144 |
| 40 | 20 | | 40 | 20 | 800 |
+------+------+ +------+------+---------+
6 rows in set (0.00 sec) 6 rows in set (0.00 sec)
 MEANING OF NULL, IS NULL, IS NOT NULL in MySQL
a. Meaning of Null:
 In SQL, NULL represents a completely missing, unknown, or inapplicable piece of data.
 A NULL is a marker used to indicate that a data value does not exist in the database .
(i) IS NULL: A comparison operator used in a WHERE clause to filter and display rows where data is missing.
Eg: select *from student;
+--------+---------+------------+-----------+
> select *from student where dob is null;
| rollno | stuname | dob | address |
+--------+---------+------+-----------+
+--------+---------+------------+-----------+
| rollno | stuname | dob | address |
| 1001 | kumar | NULL | tiruttani |
+--------+---------+------+-----------+
| 1002 | BABU | 2014-12-23 | avadi | | 1001 | kumar | NULL | tiruttani |
| 1003 | suja | 2015-08-10 | chennai | | 1005 | sam | NULL | chennai |
| 1004 | gabi | 2017-12-23 | tiruttani | +--------+---------+------+-----------+
| 1005 | sam | NULL | chennai |
+--------+---------+------------+-----------+
 MEANING OF NULL, IS NULL, IS NOT NULL in MySQL
(ii) IS NOT NULL: A comparison operator used to filter and display rows where data is
present.

> select *from student where dob is not null;

Eg: select *from student;
+--------+---------+------------+-------------------+
+--------+---------+------------+-----------+
| rollno | stuname | dob | address |
| rollno | stuname | dob | address |
+--------+---------+------------+-------------------+
+--------+---------+------------+-----------+ | 1002 | BABU | 2014-12-23 | avadi |
| 1001 | kumar | NULL | tiruttani | | 1003 | suja | 2015-08-10 | chennai |
| 1002 | BABU | 2014-12-23 | avadi | | 1004 | gabi | 2017-12-23 | tiruttani |
| 1003 | suja | 2015-08-10 | chennai | +--------+---------+------------+--------------------+
| 1004 | gabi | 2017-12-23 | tiruttani |
| 1005 | sam | NULL | chennai |
+--------+---------+------------+-----------+
 LIKE operator in MySQL
 LIKE operator is used in a WHERE clause to search for a specified pattern within a text
column.
 The two foundational wildcards used with LIKE are:
(a) % (Percent sign): Represents zero, one, or multiple characters.
(b) _ (Underscore): Represents exactly one, single character.
Syntax:
mysql> select col1,col2....from table_name where col_name LIKE “<pattern>”;
Pattern Query Condition Match Result Behavior
WHERE Column LIKE 'A%' Finds any values that start with "A".
WHERE Column LIKE '%Z' Finds any values that end with "Z".
WHERE Column LIKE '%text%' Finds any values that contain "text" in any position.
WHERE Column LIKE '_b%' Finds any values that have "b" in the second
position.
WHERE Column LIKE 'A__%' Finds values starting with "A" that are at least 3 characters long.
WHERE Column LIKE 'A%Z' Finds values that start with A and end with Z.
 LIKE operator in MySQL - Examples
a) LIKE ‘A%’: Finds any values that start with "A".
Eg: select name from customer where name LIKE “A%”;
b) LIKE '%Z': Finds any values that end with "Z".
Eg: select name from customer where name LIKE “%A”;
c) '%text%’: Finds any values that contain "text" in any position.
Eg: select name from customer where name LIKE “%MA%”;
d) LIKE '_a%’ : Finds any values that have "a" in the second position.
Eg: select name from customer where name LIKE “%_a%”;
e) LIKE 'A__%’ : Finds values starting with "A" that are at least 3 characters
long.
Eg: select name from customer where name LIKE “%A___%”;
f) LIKE 'A%Z’ : Finds values that start with A and end with Z.
Eg: select name from customer where name LIKE “S%A”;
 Aggregate functions (max, min, avg, sum, count)
b. min(): It returns the smallest value in the column.
a. max(): It returns the highest value in the column. Eg: select min(physics) from marks;
Eg: select *from marks; +--------------+
| min(physics) |
+--------+-------+------------------+---------+-------+
+--------------+
| rollno | sname | computer_science | physics | maths | | 97 |
+--------+-------+------------------+---------+-------+ +--------------+
c. avg(): It returns the average value of the column.
| 1001 | vimal | 87 | 98 | 89 | Eg: select avg(physics) from marks;
| 1002 | kamal | 94 | 97 | 96 | +--------------+
| 1003 | suja | 92 | 98 | 96 |
| avg(physics) |
+--------------+
| 1004 | ravi | 94 | 98 | 93 | | 97.8000 |
| 1005 | SONIA | 95 | 98 | 98 | +--------------+
d. sum(): It returns the sum value of the column.
+--------+-------+------------------+---------+-------+
Eg: select sum(physics) from marks;
5 rows in set (0.00 sec) +--------------+
Eg:
| sum(physics) |
+--------------+
mysql> select max(physics) from marks; | 489 |
+--------------+ +--------------+
e. count(): It returns the number of occurence of a value in the column
| max(physics) |
select count(*) from marks where physics=98;
+--------------+ +----------+
| 98 | | count(*) |
+----------+
+--------------+ | 4 |
+----------+
 GROUP BY Command in MySQL
GROUP BY clause is used in SELECT statements to display the table contents based on similar values in a
column into groups. GROUP BY almost used with the aggregate functions.
Syntax: SELECT column_name, aggregate_function(another_column) FROM table_name WHERE condition GROUP BY column_name;
+----------+--------------+-----------+----------+
| rollno | name | section | marks |
+--------+----------+---------+-------+----------+
Eg-1: select section,avg(marks) as average_marks from student GROUP BY section;
+---------+------------------------+
| 12107 | ajay | A | 90 |
| section | average_marks |
| 12108 | Dharane | A | 95 | +---------+------------------------+
| 12109 | Haswath | A | 92 | |A | 87.4000 |
| 12110 | Hitesh |A | 75 | |B | 82.4000 |
| 12111 | karunesh | A | 85 | |C | 84.2500 |
| 12209 | aslam |B | 75 |
+---------+---------------+
| 12210 | bharath | B | 65 |
Eg-2: select section,count(*)from student where marks>=90 GROUP BY section;
| 12211 | dhanush | B | 85 | +---------+----------+
| 12212 | dhiraj |B | 95 | | section | count(*) |
| 12213 | hariram | B | 92 | +---------+----------+
| 12301 | vimal |C | 91 | |A | 3|
| 12302 | raj |C | 85 |
|B | 2|
|C | 1|
| 12303 | kamal |C | 88 |
+---------+----------+
| 12304 | suja |C | 73 |
 HAVING clause in MySQL
• The HAVING clause places conditions on groups in contrast to WHERE clause that places conditions on individual [Link] conditions cannot include
aggregate functions but HAVING conditions can do so.
Syntax: SELECT column_name, aggregate_function(another_column) FROM table_name WHERE condition GROUP BY column_name;
+----------+--------------+-----------+----------+
| rollno | name | section | marks |
+--------+----------+---------+-------+----------+
Eg-1: select section,avg(marks) from student GROUP BY section HAVING section=”A”;
| 12107 | ajay | A | 90 |
+---------+------------------------+
| 12108 | Dharane | A | 95 | | section | average_marks |
| 12109 | Haswath | A | 92 | +---------+------------------------+
| 12110 | Hitesh |A | 75 | |A | 87.4000 |
| 12111 | karunesh | A | 85 |
+---------+---------------+
| 12209 | aslam |B | 75 |
Eg-2: select section,count(*)from student where marks>=90 GROUP BY section having
| 12210 | bharath | B | 65 |
section="A";
| 12211 | dhanush | B | 85 | +---------+----------+
| 12212 | dhiraj |B | 95 | | section | count(*) |
| 12213 | hariram | B | 92 | +---------+----------+
| 12301 | vimal |C | 91 | |A | 3 |
+---------+----------+
| 12302 | raj |C | 85 |
| 12303 | kamal |C | 88 |
| 12304 | suja |C | 73 |
 SQL Joins
• SQL Joins are used to display data from more than
one table.
• SQL JOIN clause is used to combine rows from two or
more tables, based on a common field between
them.
• SQL provides various types of joins:
• 1. Cartesian Product or Cross Join
• 2. Equi-Join
• 3. Natural Join.
 SQL Joins - Types
1. Cartesian Product or Cross Join:
• Cartesian product of two tables is obtained by pairing up each row of one table with
each row of the other table.
• The number of columns(Degree) in the Cartesian product is the sum of the number of
columns in both the tables.
• The number of rows (Cardinality) in the Cartesian product is the product of rows of
the tables. Eg:

mysql>select *from table1; myaql>select *from table2; mysql>select *from table1 cross join table2;
+--------+-------+----------+ +----------+------------+ +--------+-------+----------+---------------+-------------+
| rollno | name | h_id | | h_id | h_name | | rollno | name | h_id | h_id | h_name
+--------+-------+----------+ +----------+------------+ +--------+-------+-------------+-------------+-------------
| 1001 | ajay | HT01 | | HT01 | emerald | +
| 1002 | bala | HT02 | | HT02 | saphire | | 1001 | ajay | HT01 | HT01 | emerald |
+--------+-------+----------+ +----------+------------+ | 1001 | ajay | HT01 | HT02 | saphire |
| 1002 | bala | HT02 | HT01 | emerald|
| 1002 | bala | HT02 | HT02 | saphire |
 SQL Joins - Types
2. Equi Join:
 A join which is obtained by putting a condition of equality(=) on cross join is called
an 'equi join'.
 We can extract meaningful information from the Cross join by placing some
conditions in the statement.
 The join in which columns are compared for equality is called equi-join.
 In this type of join we put * in the select list therefore the common column will
appear twice in the output.
mysql>select *from table1; myaql>select *from table2; mysql>select *from table1 cross join table2
+--------+-------+----------+ +----------+------------+ where table1.h_id = table2.h_id;
| rollno | name | h_id | | h_id | h_name | +--------+-------+----------+---------------+-------------+
+--------+-------+----------+ +----------+------------+ | rollno | name | h_id | h_id | h_name
| 1001 | ajay | HT01 | | HT01 | emerald | +--------+-------+-------------+-------------+-------------
| 1002 | bala | HT02 | | HT02 | saphire | +
| 1003 | vimal |HT01 | +----------+------------+ | 1001 | ajay | HT01 | HT01 | emerald |
| 1004 | suja | HT02 | | 1002 | bala | HT02 | HT02 | saphire |
+--------+-------+----------+ | 1003 | vimal | HT01 | HT01 | emerald|
 SQL Joins - Types
3. Natural Join:
 The join in which only one of the identical columns exists is called natural join.
 It is similar to equi-join except that duplicate columns are eliminated in natural join
that would otherwise appear in equi-join.

mysql>select *from table1; myaql>select *from table2; mysql>select *from table1 natural join table2;
+--------+-------+----------+ +----------+------------+ +--------+-------+----------+---------------+
| rollno | name | h_id | | h_id | h_name | | h_id |rollno | name | h_name
+--------+-------+----------+ +----------+------------+ +--------+-------+-------------+-------------+
| 1001 | ajay | HT01 | | HT01 | emerald | | HT01 |1001 | ajay | emerald |
| 1002 | bala | HT02 | | HT02 | saphire | | HT02 |1002 | bala | saphire |
| 1003 | vimal |HT01 | +----------+------------+ | HT01 |1003 | vimal | emerald |
| 1004 | suja | HT02 | | HT02 |1004 | suja | saphire |
+--------+-------+----------+ +--------+-------+--------------+-------------+