Chapter 9 - Structured Query Language (SQL)

There are many RDBMS such as MySQL, Microsoft SQL Server, PostgreSQL, Oracle, etc. that allow us
to create a database consisting of relations. These RDBMS also allow us to store, retrieve and
manipulate data on that database through queries.

Structured Query Language (SQL): The Structured Query Language (SQL) is the most popular query
language used by major relational database management systems such as MySQL, ORACLE, SQL
Server, etc. SQL is not case sensitive. SQL provides statements for defining the structure of the data,
manipulating data in the database, declaring constraints and retrieving data from the database etc..

Data Types and Constraints in MySQL: We know that a database consists of one or more relations
and each relation (table) is made up of attributes (column). Each attribute has a data type.

Data type of Attribute: Data type of an attribute indicates the type of data value that an attribute can
have. It also decides the operations that can be performed on the data of that attribute. For example,
arithmetic operations can be performed on numeric data but not on character data. Commonly used data
types in MySQL are numeric types, date and time types, and string types.

Commonly used data types in MySQL:

Data type Description

CHAR(n) Specifies character type data of length n where n could be any value from 0 to 255.
CHAR is of fixed length, means, declaring CHAR (10) implies to reserve spaces for 10
characters. If data does not have 10 characters (e.g., ‘city’ has four characters),
MySQL fills the remaining 6 characters with spaces padded on the right.

VARCHAR(n) Specifies character type data of length where n could be any value from 0 to 65535.
But unlike CHAR, VARCHAR(n) is a variable-length data type. That is, declaring
VARCHAR (30) means a maximum of 30 characters can be stored but the actual
allocated bytes will depend on the length of entered string. So ‘city’ in VARCHAR (30)
will occupy space needed to store 4 characters only.

INT INT specifies an integer value. Each INT value occupies 4 bytes of storage. The range
of unsigned values allowed in a 4 byte integer type are 0 to 4,294,967,295. For values
larger than that, we have to use BIGINT, which occupies 8 bytes.

FLOAT Holds numbers with decimal points. Each FLOAT value occupies 4 bytes.

DATE The DATE type is used for dates in 'YYYY-MM-DD' format. YYYY is the 4 digit year,
MM is the 2 digit month and DD is the 2 digit date. The supported range is
'1000-01-01' to '9999-12-31'.

Constraints: Constraints are the certain types of restrictions on the data values that an attribute can
have. They are used to ensure correctness of data. Some of the commonly used constraints in SQL are
Constraint Description

NOT NULL Ensures that a column cannot have NULL values where NULL means missing/
unknown/not applicable value.

UNIQUE Ensures that all the values in a column are distinct/unique

DEFAULT A default value specified for the column if no value is provided

PRIMARY KEY The column which can uniquely identify each row/record in a table.

FOREIGN KEY The column which refers to value of an attribute defined as primary key in another
table
SQL for Data Definition: In order to be able to store data we need to first define the relation schema.
Defining a schema includes creating a relation and giving name to a relation, identifying the attributes in
a relation, deciding upon the datatype for each attribute and also specify the constraints as per the
requirements. SQL allows us to write statements for defining, modifying and deleting relation schemas.
These are part of Data Definition Language (DDL).

CREATE Database: To create a database, we use the CREATE DATABASE statement as shown in the
following syntax:

CREATE DATABASE databasename;

To create a database called StudentAttendance, we will type the following command at mysql prompt.

​ ​ ​ mysql> CREATE DATABASE StudentAttendance;

A DBMS can manage multiple databases on one computer. Therefore, we need to select the
database that we want to use. To know the names of existing databases, we use the statement SHOW
DATABASES. From the listed databases, we can select the database to be used. Once the database is
selected, we can proceed with creating tables or querying data. In order to use the StudentAttendance
database, the following SQL statement is required.
mysql> USE StudentAttendance;

CREATE Table: After creating a database StudentAttendance, we need to define relations in this
database and specify attributes for each relation along with data type and constraint (if any) for each
attribute. This is done using the CREATE TABLE statement.
Syntax:
CREATE TABLE tablename( attributename1 datatype constraint, attributename2 datatype
constraint,.... attributenameN datatype constraint);

Example:
​ mysql> CREATE TABLE STUDENT(RollNumber INT,Name VARCHAR(20),DateofBirth
DATE,GUID CHAR (12),PRIMARY KEY (RollNumber));

Describe Table: We can view the structure of an already created table using the DESCRIBE statement
or DESC statement.
Syntax:​ ​ DESCRIBE tablename;
Example:​ ​ mysql> DESCRIBE STUDENT;

ALTER Table: After creating a table, we may realise that we need to add/remove an attribute or to
modify the datatype of an existing attribute or to add constraint in attribute. In all such cases, we need to
change or alter the structure (schema) of the table by using the alter statement.
(A) Add primary key to a relation: The following MySQL statement adds a primary key to the GUARDIAN
relation:
mysql> ALTER TABLE GUARDIAN ADD PRIMARY KEY (GUID);
(B) Add foreign key to a relation: Once primary keys are added, the next step is to add foreign keys to
the relation (if any). Following points need to be observed while adding foreign key to a relation:
●​ The referenced relation must be already created.
●​ The referenced attribute(s) must be part of the primary key of the referenced relation.
●​ Data types and size of referenced and referencing attributes must be the same.
Syntax:​ ALTER TABLE table_name ADD FOREIGN KEY(attributename) REFERENCES
​ ​ referenced_table_name(attributename);

Example: Let us now add foreign key to the table STUDENT.

​ mysql> ALTER TABLE STUDENT ADD FOREIGN KEY(GUID) REFERENCES GUARDIAN(GUID);
(C) Add constraint UNIQUE to an existing attribute: In GUARDIAN table, the attribute GPhone has a
constraint UNIQUE which means no two values in that column should be the same.

Syntax:​ ALTER TABLE table_name ADD UNIQUE (attributename);

Example: Let us now add the constraint UNIQUE with the attribute GPhone of the table GUARDIAN
​ ​ ALTER TABLE GUARDIAN ADD UNIQUE(GPhone);
(D) Add an attribute to an existing table: Sometimes, we may need to add an additional attribute in a
table. It can be done using the ADD attribute statement.
​ ​ ALTER TABLE table_name ADD attributename DATATYPE;

Example: ​ mysql> ALTER TABLE GUARDIAN ADD income INT;

(E) Modify datatype of an attribute: We can change data types of the existing attributes of a table using
the following ALTER statement.
Syntax:​ ALTER TABLE table_name MODIFY attribute DATATYPE;

Example: Suppose we need to change the size of the attribute GAddress from VARCHAR(30) to
VARCHAR(40) of the GUARDIAN table. The MySQL statement will be:
​ ​ mysql> ALTER TABLE GUARDIAN MODIFY GAddress VARCHAR(40);

(F) Modify constraint of an attribute: When we create a table, by default each attribute takes NULL value
except for the attribute defined as primary key. We can change an attribute’s constraint from NULL to
NOT NULL using an alter statement.
Syntax:​ ALTER TABLE table_name MODIFY attribute DATATYPE NOT NULL;

Example: To associate NOT NULL constraint with attribute Name of table STUDENT, we write the
following MySQL statement:​ ​
mysql> ALTER TABLE STUDENT MODIFY SName VARCHAR(20) NOT NULL;

(G) Add default value to an attribute: If we want to specify default value for an attribute, then
use the following syntax:
​ ​ ALTER TABLE table_name MODIFY attribute DATATYPE DEFAULT default_value;

Example: To set default value of DateofBirth of STUDENT to 15th May 2000, write following statement:
​ ​ mysql> ALTER TABLE STUDENT MODIFY DateofBirth DATE DEFAULT ‘2000-05-15’;

(H) Remove an attribute: Using ALTER, we can remove attributes from a table, as shown in the following
syntax:​​ ALTER TABLE table_name DROP attribute;

Example: To remove the attribute income from table GUARDIAN

​ ​ mysql> ALTER TABLE GUARDIAN DROP income;
​
(I) Remove primary key from the table: Sometime there may be a requirement to remove primary
key constraint from the table. In that case, Alter table command can be used in the following way:
Syntax:​ ​ ALTER TABLE table_name DROP PRIMARY KEY;

Example: To remove primary key of table GUARDIAN

​ ​ ​ mysql> ALTER TABLE GUARDIAN DROP PRIMARY KEY;

DROP Statement: We can use a DROP statement to remove a database or a table permanently from
the system. However, one should be very cautious while using this statement as it cannot be undone.
Syntax to drop a table:​ DROP TABLE table_name;
Syntax to drop a database:​ DROP DATABASE database_name;
SQL for Data Manipulation: Data Manipulation using a database means either insertion of new data,
removal of existing data or modification of existing data in the database.
INSERTION of Records: INSERT INTO statement is used to insert new records in a table. Its
syntax is:
INSERT INTO tablename VALUES(value 1, value 2,....);
Example:
​ mysql> INSERT INTO GUARDIAN VALUES (444444444444, 'Amit Ahuja',
​ ​ 5711492685, 'G-35,Ashok vihar, Delhi' );
If we want to insert values only for some of the attributes in a table, statement.
Syntax:
INSERT INTO tablename (column1, column2, ...) VALUES (value1, value2, ...);
Example:
​ mysql> INSERT INTO GUARDIAN(GUID, GName, GAddress) VALUES
​ ​ (333333333333, 'Danny Dsouza', 'S -13, Ashok Village, Daman' );

SQL for Data Query: SQL provides efficient mechanisms to retrieve data stored in multiple tables in
MySQL database (or any other RDBMS). The SQL statement SELECT is used to retrieve data from the
tables in a database and is also called a query statement.

1)​ SELECT Statement: The SQL statement SELECT is used to retrieve data from the tables in a
database and the output is also displayed in tabular form
Syntax:
SELECT attribute1, attribute2, … FROM table_name WHERE condition;
The WHERE clause is optional and is used to retrieve data that meet specified condition(s).

To select all the data available in a table, we use the following select statement:
SELECT * FROM table_name;
Example: The following query retrieves the name and date of birth of student with roll number 1:
mysql> SELECT SName, SDateofBirth FROM STUDENT WHERE RollNumber = 1;

2)​ Renaming of columns: In case we want to rename any column while displaying the output, it can
be done by using the alias 'AS'. The following query selects Employee name as Name in the
output for all the employees:
mysql> SELECT EName as Name FROM EMPLOYEE;

3)​ Distinct Clause:By default, SQL shows all the data retrieved through query as output. However,
there can be duplicate values. The SELECT statement when combined with DISTINCT clause,
returns records without repetition (distinct records). For example, while retrieving a department
number from employee relation, there can be duplicate values as many employees are assigned
to the same department. To select unique department number for all the employees, we use
DISTINCT as shown below:
mysql> SELECT DISTINCT DeptId FROM EMPLOYEE;

4)​ WHERE Clause : The WHERE clause is used to retrieve data that meet some specified
conditions.
mysql> SELECT DISTINCT Salary FROM EMPLOYEE WHERE Deptid='D01';
In the above example, = operator is used in the WHERE clause. Other relational
operators (<, <=, >, >=, !=) can be used to specify such conditions. The logical operators AND,
OR, and NOT are used to combine multiple conditions.
Example: Display all the details of those employees of D04 department who earn more than
5000.
mysql> SELECT * FROM EMPLOYEE WHERE Salary > 5000 AND DeptId = 'D04';
​ mysql> SELECT Ename, DeptId FROM EMPLOYEE WHERE Salary BETWEEN 20000
​ ​ AND 50000;
 ​ mysql> SELECT * FROM EMPLOYEE WHERE DeptId = 'D01' OR DeptId = 'D02' OR
​ ​ DeptId = 'D04';
5)​ Membership operator IN: The IN operator compares a value with a set of values and returns true
if the value belongs to that set.
​ mysql> SELECT * FROM EMPLOYEE WHERE DeptId IN ('D01', 'D02' , 'D04');
​ mysql> SELECT * FROM EMPLOYEE WHERE DeptId NOT IN('D01', 'D02');

6)​ ORDER BY Clause: ORDER BY clause is used to display data in an ordered form with respect to
a specified column. By default, ORDER BY displays records in ascending order of the specified
column’s values. To display the records in descending order, the DESC (means descending)
keyword needs to be written with that column.
​ mysql> SELECT * FROM EMPLOYEE ORDER BY Salary;
​ mysql> SELECT * FROM EMPLOYEE ORDER BY Salary DESC;

7)​ Handling NULL Values: SQL supports a special value called NULL to represent a missing or
unknown value.
​ mysql> SELECT * FROM EMPLOYEE WHERE Bonus IS NULL;
​ mysql> SELECT * FROM EMPLOYEE WHERE Bonus IS NOT NULL;

8)​ Substring pattern matching: SQL provides a LIKE operator that can be used with the WHERE
clause to search for a specified pattern in a column.
The LIKE operator makes use of the following two wild card characters: • % (per cent)-
used to represent zero, one, or multiple characters. _ (underscore)- used to represent exactly a
single character.
Example: The following query selects details of all those employees whose name starts with 'K'.
mysql> SELECT * FROM EMPLOYEE WHERE Ename like 'K%';
​ mysql> SELECT * FROM EMPLOYEE WHERE Ename like '%a' AND Salary > 45000;
​ mysql> SELECT * FROM EMPLOYEE WHERE Ename like '_ANYA';

Data Updation and Deletion: Updation and deletion of data are also part of SQL Data Manipulation
Language (DML).
1)​ Data Updation: We may need to make changes in the value(s) of one or more columns of existing
records in a table. The UPDATE statement is used to make such modifications in existing data.
Syntax:
UPDATE table_name SET attribute1 = value1, attribute2 = value2, … WHERE
condition;
​ Example:
mysql> UPDATE STUDENT SET GUID = 101010101010 WHERE RollNumber = 3;
​ We can also update values for more than one column using the UPDATE statement.
​ Example: mysql> UPDATE GUARDIAN SET GAddress = 'WZ - 68, Azad Avenue, Bijnour,
​ ​ MP', GPhone = 9010810547 WHERE GUID = 466444444666;
2)​ Data Deletion :DELETE statement is used to delete/remove one or more records from a table.
Syntax:
DELETE FROM table_name WHERE condition;
​ Example: mysql> DELETE FROM STUDENT WHERE RollNumber = 2;

Functions in SQL: Function is used to perform some particular task and it returns zero or more values
as a result. SQL functions are categorised as Single Row functions and Aggregate functions.

Single Row Functions: These are also known as Scalar functions. Single row functions are applied on
a single value and return a single value. Different single row functions under three categories are —
Numeric (Math),String, Date and Time.
​ Math Functions accept numeric value as input and return a numeric value as a result. String
Functions accept character value as input and return either character or numeric values as output. Date
and Time functions accept date and time value as input and return numeric or string or Date and Time as
output.

(A) Math Functions: Three commonly used numeric functions are POWER(), ROUND() and MOD().

Function Description Example with output

POWER(X,Y) Calculates X to the power Y. mysql> SELECT POWER(2,3);

can also be written as Output:
POW(X,Y) 8

ROUND(N,D) Rounds off number N to D mysql>SELECT ROUND(2912.564, 1);

number of decimal places. Output:
Note: If D=0, then it rounds 2912.6
off the number to the nearest mysql> SELECT ROUND(283.2);
integer. Output:
283

MOD(A, B) Returns the remainder after mysql> SELECT MOD(21, 2);

dividing number A by number B. Output:
1

(B) String Functions: String functions can perform various operations on alphanumeric data which are
stored in a table. They can be used to change the case (uppercase to lowercase or vice-versa), extract a
substring, calculate the length of a string and so on.

Function Description Example with output

UCASE(string) converts string into uppercase. mysql> SELECT UCASE(“Informatics

OR Practices”);
UPPER(string) Output:
INFORMATICS PRACTICES

LOWER(string) converts string into lowercase. mysql> SELECT LOWER(“Informatics

OR Practices”);
LCASE(string) Output:
informatics practices

MID(string, pos, n) Returns a substring of size n mysql> SELECT MID(“Informatics”, 3, 4);

OR starting from the specified Output:
SUBSTRING(string, position (pos) of the string. If n form
pos, n) is not specified, it returns the mysql> SELECT
OR substring from the position pos MID(‘Informatics’,7);
SUBSTR(string, pos, n) till end of the string. Output:
atics

LENGTH(string) Return the number of mysql> SELECT LENGTH(“Informatics”);

characters in the specified Output:
string. 11

LEFT(string, N) Returns N number of mysql> SELECT LEFT(“Computer”, 4);

characters from the left side of Output:
the string. Comp

RIGHT(string, N) Returns N number of mysql> SELECT RIGHT(“SCIENCE”, 3);

characters from the right side NCE
of the string.
INSTR(string, Returns the position of the first mysql> SELECT INSTR(“Informatics”,
substring) occurrence of the substring in “ma”);
the given string. Returns 0, if Output:
the substring is not present in 6
the string.

LTRIM(string) Returns the given string after mysql> SELECT LENGTH(“

removing leading white space DELHI”), LENGTH(LTRIM(“
characters. DELHI”));
Output:
+--------+--------+
|7 | 5 |
+--------+--------+

RTRIM(string) Returns the given string after mysql>SELECT LENGTH(“PEN

removing trailing white space “)LENGTH(RTRIM(“PEN “));
characters. Output:
+--------+--------+
|5 | 3 |
+--------+--------+

TRIM(string) Returns the given string after mysql> SELECT LENGTH(“

removing both leading and MADAM “),LENGTH(TRIM(“
trailing white space characters. MADAM “));
Output:
+--------+--------+
|9 | 5 |
+--------+--------+

(C) Date and Time Functions:

Function Description Example with output

NOW() It returns the current system mysql> SELECT NOW();

date and time. Output:
2019-07-11 19:41:17

DATE() It returns the date part from the mysql> SELECT DATE(NOW());
given date/time expression. Output:
2019-07-11

MONTH(date) It returns the month in numeric mysql> SELECT MONTH(NOW());

form from the date. Output:
7

MONTHNAME(date) It returns the month name from mysql> SELECT

the specified date. MONTHNAME(“2003-11-28”);
Output:
November

YEAR(date) It returns the year from the date. mysql> SELECT YEAR(“2003-10-03”);
Output:
2003

DAY(date) It returns the day part from the mysql> SELECT DAY(“2003-03-24”);
date. Output:
24

DAYNAME(date) It returns the name of the day mysql> SELECT

from the date. DAYNAME(“2019-07-11”);
Output: Thursday
Aggregate Functions: Aggregate functions are also called Multiple Row functions. These functions
work on a set of records as a whole and return a single value for each column of the records on which
the function is applied.

Differences between Single and Multiple Row Functions:

Single Row Function Multiple row function

1. It operates on a single row at a time. 1. It operates on groups of rows.

2. It returns one result per row. 2. It returns one result for a group of rows.

3. It can be used in Select, Where, and Order by 3. It can be used in the select clause only.
clause.

4. Math, String and Date functions are examples 4. Max(), Min(), Avg(), Sum(), Count() and
of single row functions. Count(*) are examples of multiple row
functions.

Aggregate Functions in SQL:

Function Description Example with output

MAX(column) Returns the largest value from mysql> SELECT MAX(Price)

the specified column. FROM INVENTORY;
Output:
673112.00

MIN(column) Returns the smallest value from mysql> SELECT MIN(Price)

the specified column. FROM INVENTORY;
Output:
355205.00

AVG(column) Returns the average of the mysql> SELECT AVG(Price)

values in the specified column. FROM INVENTORY;
Output:
576091.625000

SUM(column) Returns the sum of the values mysql> SELECT SUM(Price)

for the specified column. FROM INVENTORY;
Output:
4608733.00

COUNT(*) Returns the number of records mysql> SELECT COUNT(*) from

in a table. MANAGER;
Note: In order to display the +----------+
number of records that matches | count(*) |
a particular criteria in the table, +----------+
we have to use COUNT(*) with |4|
WHERE clause. +----------+

COUNT(column) Returns the number of values mysql> SELECT

in the specified column ignoring COUNT(MEMNAME) FROM
the NULL values. MANAGER;
Note: +----------------+
In this example, let us consider | COUNT(MEMNAME) |
a MANAGER table having two +----------------+
attributes and four records. |3|
+----------------+
Group By clause In SQL: It groups the rows together that contain the same values in a specified
column. We can use the aggregate functions (COUNT, MAX, MIN, AVG and SUM) to work on the
grouped values. HAVING Clause in SQL is used to specify conditions on the rows with Group By clause.

Example:Consider the SALE table from the CARSHOWROOM database:

mysql> SELECT * FROM SALE;
+-------------+-------+----------+----------------+--------------------+---------+--------------+----------------+
|InvoiceNo|CarId |CustId | SaleDate | PaymentMode |EmpID |SalePrice |Commission|
+-------------+-------+----------+----------------+--------------------+---------+--------------+----------------+
| I00001 | D001 | C0001 | 2019-01-24 | Credit Card | E004 |613248.00 | 73589.64 |
| I00002 | S001 | C0002 | 2018-12-12 | Online | E001 |590321.00 | 70838.52 |
| I00003 | S002 | C0004 | 2019-01-25 | Cheque | E010 |604000.00 | 72480.00 |
| I00004 | D002 | C0001 | 2018-10-15 | Bank Finance | E007 |659982.00 | 79198.84 |
| I00005 | E001 | C0003 | 2018-12-20 | Credit Card | E002 |369310.00 | 44318.20 |
| I00006 | S002 | C0002 | 2019-01-30 | Bank Finance | E007 |620214.00 | 74425.68 |
+-------------+--------+---------+----------------+--------------------+--------+--------------+-----------------+

To display the number of Cars purchased by each Customer from SALE table.

mysql> SELECT CustID, COUNT(*) "Number of Cars" FROM SALE GROUP BY CustID;

+--------​---+----------------------+
| CustID | Number of Cars |
+----------+--------------------+ |
| C0001 | ​ 2​ |
| C0002 | ​ 2​ |
| C0003 | ​ 1 ​ |
| C0004 | ​ 1​ |
+-------- +----------------------+

Operations on Relations : We can perform certain operations on relations like Union, Intersection and
Set Difference to merge the tuples of two tables. These three operations are binary operations as they
work upon two tables. Note here that these operations can only be applied if both the relations have the
same number of attributes and corresponding attributes in both tables have the same domain.

UNION ( ∪): This operation is used to combine the selected rows of two tables at a time. If some rows
are the same in both the tables, then the result of the Union operation will show those rows only once.

Let us consider two relations DANCE and MUSIC

DANCE
+------+----------+---------+
| SNo | Name | Class |
+------+----------+---------+
|1 | Aastha | 7A |
|2 | Mahira | 6A |
|3 | Mohit | 7B |
|4 | Sanjay | 7A​ |
+-------+--------+----------+
MUSIC
+------+-------------+--------+
| SNo | Name | Class |
+------+-------------+--------+
|1 | Mehak | 8A |
|2 | Mahira | 6A |
|3 | Lavanya | 7A |
|4 | Sanjay | 7A |
|5 | Abhay | 8A |
+-------+------------+---------+

If we need the list of students participating in either of events, then we have to apply UNION operation

DANCE U MUSIC
+------+-------------+--------+
| SNo | Name | Class |
+------+-------------+--------+
|1 | Aastha | 7A |
|2 | Mahira | 6A |
|3 | Mohit | 7B |
|4 | Sanjay | 7A |
|1 | Mehak | 8A |
|3 | Lavanya | 7A |
|5 | Abhay | 8A |
+------+-------------+--------+

INTERSECT (∩): Intersect operation is used to get the common tuples from two tables and is
represented by symbol ∩.

Suppose, we have to display the list of students who are participating in both the events (DANCE and
MUSIC), then intersection operation is to be applied on these two tables.

DANCE ∩ MUSIC
+------+-------------+---------+
| SNo | Name | Class |
+------+-------------+---------+
|2 | Mahira | 6A |
|4 | Sanjay | 7A |
+------+-------------+---------+

MINUS (-): This operation(also called set difference) is used to get tuples/rows which are in the first
table but not in the second table and the operation is represented by the symbol - (minus).

​
Suppose we want the list of students who are only participating in MUSIC and not in DANCE event.
Then, we will use the MINUS operation,

MUSIC - DANCE
+------+-------------+--------+
| SNo | Name | Class |
+------+-------------+--------+
|1 | Mehak | 8A |
|3 | Lavanya | 7A |
|5 | Abhay | 8A |
+------+-------------+--------+

Cartesian Product (X): Cartesian product operation combines tuples from two relations. It results in all
pairs of rows from the two input relations, regardless of whether or not they have the same values on
common attributes. It is denoted as ‘X’.
​ The degree of the resulting relation is calculated as the sum of the degrees of both the relations
under consideration. The cardinality of the resulting relation is calculated as the product of the cardinality
of relations on which cartesian product is applied. Let us use the relations DANCE and MUSIC to show
the output of cartesian product. Note that both relations are of degree 3. The cardinality of relations
DANCE and MUSIC is 4 and 5 respectively. Applying cartesian product on these two relations will result
in a relation of degree 6 and cardinality 20

DANCE X MUSIC
+------+-------------+--------+------+-------------+-----------+
| SNo | Name | Class | SNo | Name | Class |
+------+-------------+--------+ ------+-------------+-----------+
|1 | Aastha | 7A | 1 | Mehak | 8A |
|2 | Mahira | 6A | 1 | Mehak | 8A |
|3 | Mohit | 7B | 1 | Mehak | 8A |
|4 | Sanjay | 7A | 1 | Mehak | 8A |
|1 | Aastha | 7A | 2 | Mahira | 6A |
|2 | Mahira | 6A | 2 | Mahira | 6A |
|3 | Mohit | 7B | 2 | Mahira | 6A |
|4 | Sanjay | 7A | 2 | Mahira | 6A |
|1 | Aastha | 7A | 3 | Lavanya | 7A |
|2 | Mahira | 6A | 3 | Lavanya | 7A |
|3 | Mohit | 7B | 3 | Lavanya | 7A |
|4 | Sanjay | 7A | 3 | Lavanya | 7A |
|1 | Aastha | 7A | 4 | Sanjay | 7A |
|2 | Mahira | 6A | 4 | Sanjay | 7A |
|3 | Mohit | 7B | 4 | Sanjay | 7A |
|4 | Sanjay | 7A | 4 | Sanjay | 7A |
|1 | Aastha | 7A | 5 | Abhay | 8A |
|2 | Mahira | 6A | 5 | Abhay | 8A |
|3 | Mohit | 7B | 5 | Abhay | 8A |
|4 | Sanjay | 7A | 5 | Abhay | 8A |
+------+-------------+--------+ ------+-------------+-----------+

Using two relations In a Query: When more than one table is to be used in a query, then we must
specify the table names by separating commas in the FROM clause. On execution of such a query, the
DBMS (MySql) will first apply cartesian product on specified tables to have a single table. The following
query applies cartesian product on the two tables DANCE and MUSIC:
From the all possible combinations of tuples of relations DANCE and MUSIC display only those rows
such that the attribute name in both have the same value.
mysql> SELECT * FROM DANCE D, MUSIC M WHERE [Link] = [Link];
+------+-------------+--------+------+-------------+-------------+
| SNo | Name | Class | SNo | Name | Class |
+------+-------------+--------+ ------+-------------+-----------+
|2 | Mahira | 6A | 2 | Mahira | 6A |
|4 | Sanjay | 7A | 4 | Sanjay | 7A |
+------+-------------+--------+------+-------------+-------------+

JOIN on two tables: JOIN operation combines tuples from two tables on specified conditions. This is
unlike cartesian product which make all possible combinations of tuples.
​ While using the JOIN clause of SQL, we specify conditions on the related attributes of two tables
within the FROM clause. Usually, such an attribute is the primary key in one table and foreign key in
another table. Let us create two tables UNIFORM (UCode, UName, UColor) and COST (UCode, Size,
Price) in the SchoolUniform database. UCode is Primary Key in table UNIFORM. UCode and Size is the
Composite Key in table COST. Therefore, Ucode is a common attribute between the two tables which
can be used to fetch the common data from both tables. Hence, we need to define Ucode as foreign key
in the Price table while creating this table.

Uniform table
+---------+-------------+----------+
|UCode | UName | Ucolor |
+---------+-------------+----------+
|1 | Shirt | White |
|2 | Pant​ | Grey |
|3 | Tie | Blue |
+---------+-------------+--------=+

Cost table
+---------+-------------+--------+
|UCode | Size | Price |
+---------+-------------+--------+
|1 |L | 580 |
|1 |M | 500 |
|2 |L | 890 |
|2 |M | 810 |
+---------+-------------+--------+

Example : List the UCode, UName, UColor, Size and Price of related tuples of tables UNIFORM and
COST.
The given query may be written in three different ways as given below.

a) Using condition in where clause

mysql> SELECT * FROM UNIFORM U, COST C WHERE [Link] = [Link];

+---------+-------------+----------+ +---------+-------------+--------+
|UCode | UName | Ucolor | UCode | Size | Price |
+---------+-------------+----------+ +---------+-------------+--------+
|1 | Shirt | White | 1 |L | 580 |
|1 | Shirt | White | 1 |M | 500 |
|2 | Pant​ | Grey | 2 |L | 890 |
|2 | Pant​ | Grey | 2 |M | 810 |
+---------+-------------+----------+ +---------+-------------+--------+
b) Explicit use of JOIN clause

mysql> SELECT * FROM UNIFORM U JOIN COST C ON [Link]=[Link];

The output of the query is the same as previous.

c) Explicit use of NATURAL JOIN clause

The output of queries (a) and (b)has a repetitive column Ucode having exactly the same values. This
redundant column provides no additional information. There is an extension of JOIN operation called
NATURAL JOIN which works similar to JOIN clause in SQL but removes the redundant attribute.

​ mysql> SELECT * FROM UNIFORM NATURAL JOIN COST;

+---------+-------------+----------+-------------+--------+
|UCode | UName | Ucolor | Size | Price |
+---------+-------------+----------+-------------+--------+
|1 | Shirt | White | L | 580 |
|1 | Shirt | White | M | 500 |
|2 | Pant​ | Grey | L | 890 |
|2 | Pant​ | Grey | M | 810 |
+---------+-------------+----------+-------------+--------+

Following are some of the points to be considered while applying JOIN operations on two or more
relations:
• If two tables are to be joined on equality condition on the common attribute, then one may use JOIN
with ON clause or NATURAL JOIN in FROM clause. If three tables are to be joined on equality condition,
then two JOIN or NATURAL JOIN are required.
• In general, N-1 joins are needed to combine N tables on equality condition.
• With JOIN clause, we may use any relational operators to combine tuples of two tables.