Chapter 9

Structured Query
Language (SQL)

9.1 Introduction
RDBMS allow us to create a database consisting of relations, store,
retrieve and manipulate data on that database through queries.
Ex: MySQL, Microsoft SQL Server, PostgreSQL, Oracle, etc.

9.2 Structured Query Language

Query language is used to access and manipulate data from the
database. The Structured Query Language (SQL) is the query language
used by major relational database management systems such as MySQL,
ORACLE, SQL Server, etc.
SQL provides statements for defining the structure of the data,
manipulating data in the database, declaring constraints and retrieving
data from the database.
• SQL is case insensitive. For example, the column names ‘salary’
and ‘SALARY’ are the same for SQL.
• Always end SQL statements with a semicolon (;).
• To enter multiline SQL statements, we don’t write “;” after the first
line. We press the Enter key to continue on the next line. The prompt
mysql> then changes to “->”, indicating that statement is continued
to the next line. After the last line, put “;” and press enter.

9.3 Data Types and Constraints in MySQL

Database consists of one or more relations and each relation (table) is
made up of attributes (column). Each attribute has a data type. We can
also specify constraints for each attribute of a relation.
9.3.1 Data Types
Data type of an attribute indicates the type of data value that an
attribute can have.

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.

1
 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'.

9.3.1 Constraints
Constraints are the types of restrictions on the data values that an
attribute can have.
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.

9.4 SQL for Data Definition

Defining a relation 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. Sometimes, we may require to make changes to the
relation schema also. SQL allows us to write statements for defining,
modifying and deleting relation schemas. These are part of Data
Definition Language (DDL).
9.4.1 CREATE database
Syntax: CREATE DATABASE databasename;
EX: mysql> CREATE DATABASE StudentAttendance;

2
To list the names of existing databases, we use the statement
mysql> SHOW DATABASES.

We have to select the database to perform the operations. Once the

database is selected, we can proceed with creating tables or querying
data.
To select the database.
mysql> USE StudentAttendance;
Database changed.

Initially, the created database is empty. It can be checked by using the

show tables statement that lists names of all the tables within a
database.
mysql> SHOW TABLES;

9.4.2 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);
• The number of columns in a table defines the degree of that relation,
which is denoted by N.
• Attribute name specifies the name of the column in the table.
• Datatype specifies the type of data that an attribute can hold.
• Constraint indicates the restrictions imposed on the values of an
attribute. By default, each attribute can take NULL values except for
the primary key.
Ex: mysql> CREATE TABLE STUDENT( RollNumber INT, SName VARCHAR(20),
SDateofBirth DATE, GUID CHAR (12), PRIMARY KEY (RollNumber));

STUDENT Relation
Attribute Name Data expected to be Data type Constraint
stored
Numeric value consisting PRIMARY
RollNumber INT
of maximum 3 digits KEY
Variant length string of
SName
maximum 20 characters VARCHAR(20) NOT NULL
SDateofBirth Date value DATE NOT NULL
Numeric value consisting of FOREIGN
GUID CHAR (12)
12 digits KEY

GUARDIAN Relation
Attribute Name Data expected to be stored Data type Constraint
3
 Numeric value consisting of PRIMARY
GUID CHAR (12)
12 digit Aadhaar number KEY
Variant length string of
GName
maximum 20 characters VARCHAR(20) NOT NULL
Numeric value consisting of NULL
GPhone CHAR(10)
10 digits UNIQUE
Variant length String of size
GAddress VARCHAR(30) NOT NULL
30 characters

ATTENDACE Relation
Attribute Name Data expected to be stored Data type Constraint
PRIMARY
AttendanceDate Date value DATE
KEY*
PRIMARY
Numeric value consisting of KEY*
RollNumber INT
maximum 3 digits FOREIGN
KEY
‘P’ for present and ‘A’ for
AttendanceStatus CHAR(1) NOT NULL
absent

9.4.3 Describe table

DESCRIBE statement or DESC statement is used to see the structure
of an already created table.
Syntax: DESCRIBE tablename;
Ex: mysql> DESCRIBE STUDENT;
+--------------+-------------+------+-------+---------+----------------- +
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+------------------- +
| RollNumber | int | NO | PRI | NULL | |
| SName | varchar(20) | YES | | NULL | |
| SDateofBirth | date | YES | | NULL | |
| GUID | char(12) | YES | | NULL | |
+--------------+-------------+------+-----+---------+------------------- +
9.4.4 ALTER table
It is used to change or alter the structure (schema) of the table,
which means add/remove an attribute or to modify the datatype of an
existing attribute or to add constraint in attribute.
(A) Add primary key to a relation
Syntax: ALTER TABLE table_name ADD PRIMARY KEY(attribute name);
Ex: mysql> ALTER TABLE GUARDIAN ADD PRIMARY KEY (GUID);

Now let us add the primary key to the ATTENDANCE relation. The
primary key of this relation is a composite key made up of two attributes
- AttendanceDate and RollNumber.

4
mysql> ALTER TABLE ATTENDANCE ADD PRIMARY KEY(AttendanceDate,
RollNumber);

(B) Add foreign key to a relation

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(attribute name) REFERENCES
referenced_table_name(attribute name);

mysql> ALTER TABLE STUDENT ADD FOREIGN KEY(GUID) REFERENCES

GUARDIAN(GUID);

(C) Add constraint UNIQUE to an existing attribute

Syntax: ALTER TABLE table_name ADD UNIQUE (attribute name);
Let us now add the constraint UNIQUE with the attribute GPhone of
the table GUARDIAN.
mysql> ALTER TABLE GUARDIAN ADD UNIQUE(GPhone);

(D) Add an ATTRIBUTE to an existing table

Syntax: ALTER TABLE table_name ADD attribute_name DATATYPE;
Ex: mysql> ALTER TABLE GUARDIAN ADD income INT;

(E) Modify datatype of an attribute

Syntax: ALTER TABLE table_name MODIFY attribute_name DATATYPE;
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

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;
Ex: mysql> ALTER TABLE STUDENT MODIFY SName VARCHAR(20) NOT NULL;

(G) Add default value to an attribute

Syntax:
ALTER TABLE table_name MODIFY attribute DATATYPE DEFAULT default_value;
Ex: mysql> ALTER TABLE STUDENT MODIFY SDateofBirth DATE DEFAULT ‘2000-
05- 15’;
5
(H) Remove an attribute
Syntax: ALTER TABLE table_name DROP attribute;
Ex: mysql> ALTER TABLE GUARDIAN DROP income;

(I) Remove primary key an attribute

Syntax: ALTER TABLE table_name DROP PRIMARY KEY;
Ex: mysql> ALTER TABLE GUARDIAN DROP PRIMARY KEY;

9.4.5 Drop Statement

We can use a DROP statement to remove a database or a table
permanently from the system.

Syntax to drop a table: DROP TABLE table_name;

Syntax to drop a database: DROP DATABASE database_name;

9.5 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.
9.5.1 INSERTION of records
INSERT INTO statement is used to insert new records in a table.
Syntax: INSERT INTO tablename VALUES(value 1, value 2,… );
Here, value 1 corresponds to attribute 1, value 2 corresponds to attribute
2 and so on. Note that we need not to specify attribute names in the
insert statement if there are exactly the same numbers of values in the
INSERT statement as the total number of attributes in the table.
Ex:
mysql> INSERT INTO GUARDIAN VALUES (2401, 'Arun Kumar', 5711492685,
'Saraswathipuram, Mysuru');
Note: Text and date values must be enclosed in ‘ ’ (single quotes).

If we want to insert values only for some of the attributes in a table

then we shall specify the attribute names in which the values are to be
inserted.
Sytax:
INSERT INTO tablename (column1, column2,...., columnN) VALUES (value1,
value2,……valueN);
mysql> INSERT INTO GUARDIAN(GUID, GName, GAddress) VALUES (57265244,
'Anil', 'S -13, Kuvempunagar, Mysuru' );

9.6 SQL for Data Query

The SQL statement SELECT is used to retrieve data from the tables
in a database and is also called a query statement.

6
9.6.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;
Here, 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:
Syntax: SELECT * FROM table_name;
Ex: SELECT * FROM GUARDIAN;
+--------------+--------------+-----------+ ------------------------------------------------- +
| GUID | GName | Gphone | GAddress |
+--------------+--------------+-----------+ ------------------------------------------------- +
| 333333333333 | Danny Dsouza | NULL | S -13, Ashok Village,Daman |
| 444444444444 | Amit Ahuja | 5711492685| G-35, Ashok vihar, Delh |
+--------------+--------------+-----------+ ------------------------------------------------- +
9.6.2 Querying using SELECT
Employee Relation
EmpNo Ename Salary Bonus Deptld
101 Aaliya 10000 234 D02
102 Kritika 60000 123 D01
103 Shabbbir 45000 566 D01
104 Gurpreet 19000 565 D04
105 Joseph 34000 875 D03
106 Sanya 48000 695 D02
107 Vergese 15000 D01
108 Nachaobi 29000 D05
109 Daribha 42000 D04
110 Tanya 50000 467 D05

(A) Retrieve selected columns

The following query selects employee numbers of all the employees:
mysql> SELECT EmpNo FROM EMPLOYEE;
+ -------- +
| EmpNo |
+ -------- +
| 101 |
| 102 |
| 103 |
| 104 |
| 105 |
| 106 |
| 107 |
| 108 |

7
 | 109 |
| 110 |
+ +
The following query selects the employee number and employee name of
all the employees: mysql> SELECT EmpNo, Ename FROM EMPLOYEE;
+-------+ ------------- ----+
| EmpNo | Ename |
+-------+ ------------- ----+
| 101 | Aaliya |
| 102 | Kritika |
| 103 | Shabbir |
| 104 | Gurpreet |
| 105 | Joseph |
| 106 | Sanya |
| 107 | Vergese |
| 108 | Nachaobi |
| 109 | Daribha |
| 110 | Tanya |
+-------+ ------------- ----+
(B) Renaming the 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;
+ ----------- ------+
| Name |
+ ----------- ------+
| Aaliya |
| Kritika |
| Shabbir |
| Gurpreet |
| Joseph |
| Sanya |
| Vergese |
| Nachaobi |
| Daribha |
| Tanya |
+ ----------- ------+
Select names of all employees along with their annual income (calculated as
Salary*12). While displaying the query result, rename the column EName
as Name
mysql> SELECT EName as Name, Salary*12 as ‘Annual Income’ FROM EMPLOYEE;
+----------+ ----------------- +
| Name | Annual |
| | Income |
+----------+ ----------------- +
| Aaliya | 120000 |

8
 | Kritika | 720000 |
| Shabbir | 540000 |
| Gurpreet | 228000 |
| Joseph | 408000 |
| Sanya | 576000 |
| Vergese | 180000 |
| Nachaobi | 348000 |
| Daribha | 504000 |
| Tanya | 600000 |
+----------+ ----------------- +
Note: If an aliased column name has space as in the case of Annual
Income, it should be enclosed in quotes as 'Annual Income'.

(C) Distinct Clause

The SELECT statement when combined with DISTINCT clause, returns
records without repetition (distinct records).
Ex: mysql> SELECT DISTINCT DeptId FROM EMPLOYEE;
+ --------- +
| DeptId |
+ --------- +
| D02 |
| D01 |
| D04 |
| D03 |
| D05 |
+ --------- +
(D) WHERE Clause
The WHERE clause is used to retrieve data that meet some
specified conditions.
mysql> SELECT DISTINCT Salary FROM EMPLOYEE WHERE Deptid='D01';
As the column DeptId is of string type, its values are enclosed in quotes
('D01').
+ ---------- +
| Salary |
+ ---------- +
| 60000 |
| 45000 |
| 15000 |
+ ---------- +
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.
mysql> SELECT * FROM EMPLOYEE WHERE Salary > 5000 AND DeptId = 'D04';
+-------+----------+--------+-------+ --------------------- +
| EmpNo | Ename | Salary | Bonus | DeptId |
+-------+----------+--------+-------+ --------------------- +
9
 | 104 | Gurpreet | 19000 | 565 | D04 |
| 109 | Daribha | 42000 | NULL | D04 |
+-------+----------+--------+-------+ --------------------- +
The query defines a range that can also be checked using a comparison
operator BETWEEN:
mysql> SELECT Ename, DeptId FROM EMPLOYEE WHERE Salary BETWEEN
20000 AND 50000;
+----------+ ------------- +
| Ename | DeptId |
+----------+ ------------- +
| Shabbir | D01 |
| Joseph | D03 |
| Sanya | D02 |
| Nachaobi | D05 |
| Daribha | D04 |
| Tanya | D05 |
+----------+ ------------- +
(E) 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');
+-------+----------+--------+-------+ --------------------- +
| EmpNo | Ename | Salary | Bonus | DeptId |
+-------+----------+--------+-------+ --------------------- +
| 101 | Aaliya | 10000 | 234 | D02 |
| 102 | Kritika | 60000 | 123 | D01 |
| 103 | Shabbir | 45000 | 566 | D01 |
| 104 | Gurpreet | 19000 | 565 | D04 |
| 106 | Sanya | 48000 | 695 | D02 |
| 107 | Vergese | 15000 | NULL | D01 |
| 109 | Daribha | 42000 | NULL | D04 |
+-------+----------+--------+-------+ --------------------- +
(F) ORDER BY clause
It 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;
+-------+----------+--------+-------+ --------------------- +
| EmpNo | Ename | Salary | Bonus | DeptId |
+-------+----------+--------+-------+ --------------------- +
| 101 | Aaliya | 10000 | 234 | D02 |
| 107 | Vergese | 15000 | NULL | D01 |
| 104 | Gurpreet | 19000 | 565 | D04 |
| 108 | Nachaobi | 29000 | NULL | D05 |
| 105 | Joseph | 34000 | 875 | D03 |
| 109 | Daribha | 42000 | NULL | D04 |
10
Employees in descending order of their salaries.
mysql> SELECT * FROM EMPLOYEE ORDER BY Salary DESC;

(G) Handling NULL value

SQL supports a special value called NULL to represent a missing or
unknown value. It is important to note that NULL is different from 0 (zero).
Also, any arithmetic operation performed with NULL value gives NULL.
Ex, 5 + NULL = NULL because NULL is unknown hence the result is also
unknown. In order to check for NULL value in a column, we use IS NULL
operator.
mysql> SELECT * FROM EMPLOYEE WHERE Bonus IS NULL;
+-------+----------+--------+-------+ --------------------- +
| EmpNo | Ename | Salary | Bonus | DeptId |
+-------+----------+--------+-------+ --------------------- +
| 107 | Vergese | 15000 | NULL | D01 |
| 108 | Nachaobi | 29000 | NULL | D05 |
| 109 | Daribha | 42000 | NULL | D04 |
+-------+----------+--------+-------+ --------------------- +
(H) 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.
For example, to find out names starting with “T” or to find out pin codes
starting with ‘60’. This is called substring pattern matching. We cannot
match such patterns using = operator as we are not looking for an exact
match.
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.
Ex:
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';

+-------+-------+--------+-------+ --------------- +
| EmpNo | Ename | Salary | Bonus | DeptId |
+-------+-------+--------+-------+ --------------- +
| 106 | Sanya | 48000 |695 | D02|
| 110 | Tanya | 50000 |467 | D05 |
+-------+-------+--------+-------+ --------------- +
mysql> SELECT Ename FROM EMPLOYEE WHERE Ename like '%se%';

mysql> SELECT EName FROM EMPLOYEE WHERE Ename like '_a%';

9.7 Data Updation and Deletion

11
 Updation and deletion of data are also part of SQL Data
Manipulation Language (DML).

9.7.1 Data Updation

We may need to make changes in the value(s) of one or more columns
of existing records in a table.
Syntax:
UPDATE table_name SET attribute1 = value1, attribute2 = value2, ... WHERE
condition;
mysql> UPDATE GUARDIAN SET GAddress = 'WZ - 68, Azad Avenue, Bijnour, MP',
GPhone = 9010810547 WHERE GUID = 466444444666;

9.7.2 Data Deletion

DELETE statement is used to delete/remove one or more records from a
table.
Syntax: DELETE FROM table_name WHERE condition;
Ex: mysql> DELETE FROM STUDENT WHERE RollNumber = 2;

9.8 Functions in SQL

Let us create a database called CARSHOWROOM having the schema as
shown below. It has the following four relations:
1. INVENTORY: Stores name, price, model, year of manufacturing, and
fuel type for each car in inventory of the showroom,
2. CUSTOMER: Stores customer id, name, address, phone number and
email for each customer,
3. SALE: Stores the invoice number, car id, customer id, sale date, mode
of payment, sales person’s employee id and selling price of the car sold,
4. EMPLOYEE: Stores employee id, name, date of birth, date of joining,
designation and salary of each employee in the showroom.

12
 mysql> SELECT * FROM INVENTORY;
+--------+--------------+------------+-------------+-------------------+------------------------------------------- +
| CarId | CarName | Price | Model | YearManufacture | Fueltype |
+--------+--------------+------------+-------------+-------------------+------------------------------------------- +
| D001 | Dzire | 582613.00 | LXI | 2017 | Petrol |
| D002 | Dzire | 673112.00 | VXI | 2018 | Petrol |
| B001 | Baleno | 567031.00 | Sigma1.2 | 2019 | Petrol |
| B002 | Baleno | 647858.00 | Delta1.2 | 2018 | Petrol |
| E001 | EECO | 355205.00 | 5 STR STD | 2017 | CNG |
| E002 | EECO | 654914.00 | CARE | 2018 | CNG |
| S001 | SWIFT | 514000.00 | LXI | 2017 | Petrol |
| S002 | SWIFT | 614000.00 | VXI | 2018 | Petrol |
+--------+--------------+-------------------------+------------+ ------------------------------------------------ +

mysql> SELECT * FROM CUSTOMER;

+--------+--------------+-------------------------+------------+ ------------------------------------------------ +
| CustId | CustName | CustAdd | Phone | Email |
+--------+--------------+-------------------------+------------+ ------------------------------------------------ +
| C0001 | Amit Saha | L-10, Pitampura | 4564587852 | amitsaha2@gmail.com |
| C0002 | Rehnuma | J-12, SAKET | 5527688761 | rehnuma@hotmail.com |
| C0003 | Charvi Nayyar| 10/9, FF, Rohini | 6811635425 | charvi123@yahoo.com |
| C0004 | Gurpreet | A-10/2, SF, Mayur Vihar | 3511056125 | gur_singh@yahoo.com |
+--------+--------------+-------------------------+------------+ ------------------------------------------------ +

mysql> SELECT * FROM SALE;

+-----------+-------+--------+------------+--------------+-------+ ---------------------------------------------- +
| InvoiceNo | CarId | CustId | SaleDate | PaymentMode | EmpID | SalePrice |
+-----------+-------+--------+------------+--------------+-------+ ---------------------------------------------- +
| I00001 | D001 | C0001 | 2019-01-24 | Credit Card | E004 | 613248.00 |
| I00002 | S001 | C0002 | 2018-12-12 | Online | E001 | 590321.00 |
| I00003 | S002 | C0004 | 2019-01-25 | Cheque | E010 | 604000.00 |
| I00004 | D002 | C0001 | 2018-10-15 | Bank Finance | E007 | 659982.00 |
| I00005 | E001 | C0003 | 2018-12-20 | Credit Card | E002 | 369310.00 |
| I00006 | S002 | C0002 | 2019-01-30 | Bank Finance | E007 | 620214.00 |
+-----------+-------+--------+------------+--------------+-------+ ---------------------------------------------- +

mysql> SELECT * FROM EMPLOYEE;

+-------+----------+------------+------------+--------------+ ----------------------------------------+
| EmpID | EmpName | DOB | DOJ | Designation | Salary |
+-------+----------+------------+------------+--------------+ ----------------------------------------+
| E001 | Rushil | 1994-07-10 | 2017-12-12 | Salesman | 25550 |
| E002 | Sanjay | 1990-03-12 | 2016-06-05 | Salesman | 33100 |
| E003 | Zohar | 1975-08-30 | 1999-01-08 | Peon | 20000 |
| E004 | Arpit | 1989-06-06 | 2010-12-02 | Salesman | 39100 |
| E006 | Sanjucta | 1985-11-03 | 2012-07-01 | Receptionist | 27350 |
| E007 | Mayank | 1993-04-03 | 2017-01-01 | Salesman | 27352 |
| E010 | Rajkumar | 1987-02-26 | 2013-10-23 | Salesman | 31111 |
+-------+----------+------------+------------+--------------+ ----------------------------------------+

9.8.1 Single Row Functions

These are also known as Scalar functions. Single row functions are
applied on a single value and return a single value.
Types of single Row functions.
Numeric (Math), String, Date and Time.

13
(A) Math Functions
Function Description Example with output
POWER(X,Y) Calculates X to the mysql> SELECT POWER(2,3);
can also be power Y. Output:
written as
8
POW(X,Y)
ROUND(N,D) Rounds off number N mysql>SELECT
to D number of ROUND(2912.564, 1);
decimal places. Output:
Note: If D=0, then it 2912.6
rounds off the mysql> SELECT ROUND(283.2);
number to the
Output:
nearest integer.
283
MOD(A, B) Returns the remain mysql> SELECT MOD(21, 2);
der after dividing
Output:
number A by number
1
B.
Ex:
mysql> SELECT ROUND(12/100*Price,1) "GST" FROM INVENTORY;
mysql> SELECT CarId, FinalPrice, MOD(FinalPrice,10000) "Remaining Amount"
FROM INVENTORY;

(B) String Functions

Function Description Example with
output
UCASE(string) converts string into mysql> SELECT
OR uppercase. UCASE(“Informatics
UPPER(string) Practices”); Output:
INFORMATICS PRACTICES

14
 LOWER(string) converts string into mysql> SELECT
OR lowercase. LOWER(“Informatics
LCASE(string) Practices”); Output:
informatics practices
MID(string, pos, Returns a substring of size mysql> SELECT
n) OR n starting from the specified MID(“Informatics”, 3, 4);
SUBSTRING(stri position (pos) of the string. If Output:
ng, pos, n) n is not specified, it returns
form
OR the substring from the
SUBSTR(string, position pos till end of the mysql> SELECT
pos, n) string.
MID(‘Informatics’,7); Output:
atics
LENGTH(string) Return the number of mysql> SELECT
characters in the specified LENGTH(“Informatics”);
string. Output:
11
LEFT(string, N) Returns N number of mysql> SELECT
characters from the left side LEFT(“Computer”, 4); Output:
of the string. Comp
RIGHT(string, N) Returns N number of mysql> SELECT
characters from the right side RIGHT(“SCIENCE”, 3);
of the string. NCE
INSTR(string Returns the position of the mysql> SELECT
, substring) first occurrence of the INSTR(“Informatics”,
substring in the given string. “ma”);
Returns 0, if the substring is Output:
not present in the string.
6
LTRIM(string) Returns the given string mysql> SELECT LENGTH(“
after removing leading white DELHI”), LENGTH(LTRIM(“
space characters. DELHI”));
Output:
+--------+------------------ +
|7 | 5 |
+--------+------------------ +
RTRIM(string) Returns the given string mysql>SELECT LENGTH(“PEN
after removing trailing white “)LENGTH(RTRIM(“PEN “));
space characters. Output:
+--------+------------------ +
|5 | 3 |
+--------+------------------ +
TRIM(string) Returns the given string mysql> SELECT LENGTH(“
after removing both leading MADAM “),LENGTH(TRIM(“
and trailing white space MADAM “));
characters. Output:
+--------+------------------ +
|9 | 5 |
+--------+------------------ +
mysql> SELECT LOWER(CustName), UPPER(Email) FROM CUSTOMER;

15
mysql> SELECT MID(Phone,3,4) FROM CUSTOMER WHERE CustAdd like
‘%Rohini%’;
+ -------------------------- +
| MID(Phone,3,4) |
+ -------------------------- +
| 1163 |
+ -------------------------- +
mysql> SELECT TRIM(“.com” from Email) FROM CUSTOMER;

(C) Date and Time Functions

Function Description Example with output
NOW() It returns the current mysql> SELECT NOW();
system date and time. Output: 2019-07-11 19:41:17

DATE() It returns the date part mysql> SELECT DATE(NOW());

from the given date/time Output: 2019-07-11
expression.
MONTH(date) It returns the month in mysql> SELECT MONTH(NOW());
numeric form from the Output: 7
date.
MONTHNAME(date) It returns the month mysql> SELECT
name from the specified MONTHNAME(“2003-11-28”);
date. Output: November
YEAR(date) It returns the year from mysql> SELECT YEAR(“2003-10- 03”);
the date. Output: 2003
DAY(date) It returns the day part mysql> SELECT DAY(“2003-03- 24”);
from the date. Output: 24
DAYNAME(date) It returns the name of mysql> SELECT DAYNAME(“2019-
the day from the date. 07-11”);
Output: Thursday
mysql> SELECT DAY(DOJ), MONTH(DOJ), YEAR(DOJ) FROM EMPLOYEE;

9.8.2 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. Note that column must be of numeric type.
Function Description Example with output
MAX(column) Returns the largest value mysql> SELECT MAX(Price) FROM
from the specified column. INVENTORY;
Output:673112.00

MIN(column) Returns the smallest mysql> SELECT MIN(Price) FROM

value from the specified INVENTORY;
column. Output:355205.00

16
 AVG(column) Returns the average of mysql> SELECT AVG(Price) FROM
the values in the INVENTORY;
specified column.
Output: 576091.625000
SUM(column) Returns the sum of the mysql> SELECT SUM(Price) FROM
values for the specified INVENTORY;
column.
Output: 4608733.00
COUNT(*) Returns the number of mysql> SELECT COUNT(*) from
records in a table. MANAGER;
Note: In order to display +-----------+
the number of records | count(*) |
that matches a +-----------+
particular criteria in the | 4 |
table, we have to use +-----------+
COUNT(*) with WHERE
clause.
COUNT(column) Returns the number of mysql> SELECT * from MANAGER;
values in the specified
+------------- +----------------+
column ignoring the
NULL values. | MNO | MEMNAME |
+------------ +-----------------+
Note: | 1 | AMIT |
In this example, let us
| 2 | KAVREET |
consider a MANAGER
table having two | 3 | KAVITA |
attributes and four | 4 | NULL |
records.
+-----------+ ------------------+
mysql> SELECT COUNT(MEMNAME)
FROM MANAGER;
+---------------------------+
| COUNT(MEMNAME) |
+---------------------------+
| 3 |
+---------------------------+
Single Row Function Multiple row function
1. It operates on a single row at a 1. It operates on groups of rows.
time. 2. It returns one result for a group of
2. It returns one result per row. rows.
3. It can be used in Select, Where, 3. It can be used in the select clause
and Order by clause. only.
4. Math, String and Date functions 4. Max(), Min(), Avg(), Sum(), Count()
are examples of single row and Count(*) are examples of
functions. multiple row functions.

9.9 GROUP BY clause in SQL

It is used to fetch a group of rows on the basis of common values in
a column. It groups the rows together that contains the same values in a
specified column.
Ex: mysql> SELECT * FROM SALE;
17
+---------+------+-------+------------+-------------+------+----------+ ------------------------------------------- +

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

CarID, CustID, SaleDate, PaymentMode, EmpID, SalePrice are the columns

that can have rows with the same values in it. So, Group by clause can
be used in these columns to find the number of records of a particular type
(column), or to calculate the sum of the price of each car type.
Ex:
1. 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|
+--------+------------------------ +
2. Display the Customer Id and number of cars purchased if the
customer purchased more than 1 car from SALE table.
mysql> SELECT CustID, COUNT(*) FROM SALE GROUP BY CustID HAVING
Count(*)>1;
+--------+---------------- +
| CustID | COUNT(*) |
+--------+---------------- +
| C0001 | 2|
| C0002 | 2|
+--------+---------------- +
9.10 Operations on Relations
We can perform Union, Intersection and Set Difference to merge the
tuples of two tables. These three operations are binary operations as they
work upon two tables. 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.
9.10.1 UNION
This operation is used to combine the selected rows of two tables at a time.
If some rows are same in both the tables, then result of the Union
operation will show those rows only once.

18
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 |
+------+---------+-------------- +
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 |
+------+---------+ ------------- +
9.10.2 INTERSECT
Intersect operation is used to get the common tuples from two
tables and is represented by symbol ∩.
DANCE ∩ MUSIC
+----+---------+ ------------- +
| SNo| Name | Class |
+----+---------+ ------------- +
| 2 | Mahira | 6A |
| 4 | Sanjay | 7A |
+----+---------+ ------------- +
9.10.3 MINUS
This operation 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).
DANCE - MUSIC
+------+---------+-------------- +
| SNo | Name | Class |

19
 +------+---------+-------------- +
|1 | Mehak | 8A |
|3 | Lavanya | 7A |
|5 | Abhay | 8A |
+------+---------+-------------- +
9.10.4 Cartesian Product
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.
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 |
+--+-------+-------+------+---------+ -------------- +

9.11 Using Two Relations in a Query

9.11.1 Cartesian product on Two tables
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.
Ex:
1. Display all possible combinations of tuples of relations DANCE and
MUSIC. mysql> SELECT * FROM DANCE, MUSIC;

20
As we are using SELECT * in the query, the output will be the Table
having degree 6 and cardinality 20.

2. 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 D.Name = M.Name;
+------+--------+-------+------+--------+ -------------------- +
| Sno | Name | Class | Sno | Name | class |
+------+--------+-------+------+--------+ -------------------- +
| 2 | Mahira | 6A | 2 | Mahira | 6A |
| 4 | Sanjay | 7A | 4 | Sanjay | 7A |
+------+--------+-------+------+--------+ -------------------- +
9.11.2 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.
Ex: 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 |
+-------+------+------------- +
mysql> SELECT * FROM UNIFORM U, COST C WHERE U.UCode = C.UCode;
or
mysql> SELECT * FROM UNIFORM U JOIN COST C ON U.Ucode=C.Ucode;
21
 +-------+-------+--------+-------+-----+ -------------------- +
| 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 |
+-------+-------+--------+-------+-----+ -------------------- +
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;

22