DATABASE QUERY USING SQL

Revision of database concepts and SQL commands covered in class XI

Database Concepts: Introduction to database concepts and its need, Database Management System.

Database Management System : A database management system (DBMS) is a software that can be used to create,
manage, store and manage databases. Some examples of open source and commercial DBMS include MySQL,
Oracle, PostgreSQL, SQL Server, Microsoft Access, MongoDB etc.

RDBMS:
RDBMS stands for Relational Database Management System. RDBMS is the basis for SQL, and for all
modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access. The data in
RDBMS is stored in database objects called tables. A table(Relation) is a collection of related data entries
and it consists of columns and rows.

Common Terms used in DBMS :

Attributes : The columns of a relation are the attributes which are also referred to as fields.
Tuple : Each row of data in a relation (table) is called a tuple. It is also known as a record.
Domain : It is a set of values from which an attribute can take a value in each row. Usually, a data type is used to
specify a domain for an attribute.
Degree : The number of attributes/columns in a relation is called the Degree of the relation.
Cardinality : The number of tuples/rows in a relation is called the Cardinality of the relation.

Keys in Relational Database :

Primary Key : A field which uniquely identifies each and every record in the table is called primary key.
Candidate Key : Those fields which can act as a primary key in a table are called candidate Key. Composite
Primary Key : If no single attribute in a relation is able to uniquely identify the tuples, then more than one
attribute is taken together as primary key. Such a primary key consisting of more than one attribute is called
Composite Primary key.
Foreign Key : A foreign key is used to represent the relationship between two tables/relations. A foreign key
is an attribute whose value is derived from the primary key of another relation.
Note: In some cases, foreign key can take a NULL value if it is not part of the primary key of the foreign table. The
Table that has the Primary Key is called parent Table and the table having Foreign Key is called Child Table.

What is MySQL?:
MySQL is a open source Relational Database Management System. MySQL is very fast reliable and flexible Database
Management System. It provides a very high performance and it is multi threaded and multi user Relational
Database management system.
MySQL Features
1. MySQL is an open source RDBMS application.
2. MySQL command line tool is very powerful and can be used to run SQL queries against database.
3. It is fast, realiable and more cheaper than other DBMS softwares.
4. MySQL is compatible and portable.
5. MySQL has a client server archtecture.
6. MySQL has a strong data security layer.
7. MySQL is easy to manage.
Classification of SQL statements
SQL provides many different types of commands used for different purposes. SQL can be divided into following
parts:
1. Data Manipulation Language (DML) commands
2. Data Definition Language (DDL) commands.
1. DML commands: A DML is a language that enables users to access manipulated data as organized by the
appropriate data model.
● SELECT - extracts data from a database (DQL).
● DELETE- removes fields/rows from a table.
● UPDATE- change data in a table.
● INSERT INTO - inserts new data into a database
2. DDL commands: The DDL commands, as the name suggests, allow you to perform tasks related to data
definition. The DDL part of SQL permits database tables to be created or deleted. The most important DDL
statements in SQL are:
● CREATE DATABASE - creates a new database
● ALTER DATABASE - modifies a database
● CREATE TABLE - creates a new table
● ALTER TABLE - modifies a table
● DROP TABLE - deletes a table
MySQL Data Types
In MySQL there are three main data types: text, number, and Date/Time.
Text types:
Data type Description

CHAR(size) Holds a fixed length string (can contain letters, numbers, and special characters). The
fixed size is specified in parenthesis. Can store up to 255 characters

VARCHAR(size) Holds a variable length string. The maximum size: up to 255 characters. Note: If you put a
greater value than 255 it will be converted to a TEXT type

TEXT Holds a string with a maximum length of 65,535 characters

Number types:
Data type Description

INT(size) -2147483648 to 2147483647 normal. 0 to 4294967295 UNSIGNED*. The

maximum number of digits may be specified in parenthesis

FLOAT(size,d) A small number with a floating decimal point. The maximum number of digits may be
specified in the size parameter. The maximum number of digits to the right of the
decimal point is specified in the d parameter

DECIMAL(size,d) A DOUBLE stored as a string, allowing for a fixed decimal point. The maximum number
of digits may be specified in the size parameter. The maximum number of digits to
the right of the decimal point is specified in the d parameter

*The integer types have an extra option called UNSIGNED. Normally, the integer goes from a negative to positive
value. Adding the UNSIGNED attribute will move that range up so it starts at zero instead of a negative number.
Date types:
Data type Description

DATE() A date. Format: YYYY-MM-DD

DATETIME() *A date and time combination. Format: YYYY-MM-DD HH:MM:SS

TIMESTAMP() *A timestamp. TIMESTAMP values are stored as the number of seconds since the
Unix epoch ('1970-01-01 00:00:00' UTC). Format: YYYY-MM- DD
HH:MM:SS Range is from '1970-01-01 00:00:01' UTC to '2038-01- 09
03:14:07' UTC

TIME() A time. Format: HH:MM:SS

YEAR() A year in two-digit or four-digit format.

*Even if DATETIME and TIMESTAMP return the same format, they work very differently. In an INSERT or UPDATE
query, the TIMESTAMP automatically set itself to the current date and time. TIMESTAMP also accepts various
formats, like YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD, or YYMMDD.
The CREATE TABLE Statement (DDL)
The CREATE TABLE statement is used to create a table in a database. SQL
CREATE TABLE Syntax
CREATE TABLE table_name (
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
...........)
CREATE TABLE Example
Now we want to create a table called "Persons" that contains five columns: P_Id, LastName, FirstName, Address, and
City.
We use the following CREATE TABLE statement:
CREATE TABLE Persons (
P_Id int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
The P_Id column is of type int and will hold a number. The LastName, FirstName, Address, and City columns are
of type varchar with a maximum length of 255 characters.
The empty "Persons" table will now look like this:
P_Id LastName FirstName Address City

The empty table can be filled with data with the INSERT INTO statement.
The INSERT INTO Statement (DML)
The INSERT INTO statement is used to insert a new row in a table. SQL
INSERT INTO Syntax
It is possible to write the INSERT INTO statement in two forms.
The first form doesn't specify the column names where the data will be inserted, only their values:
INSERT INTO table_name
VALUES (value1, value2, value3,...)
The second form specifies both the column names and the values to be inserted: INSERT INTO
table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)
SQL INSERT INTO Example
We have the following "Persons" table:
P_Id LastName FirstName Address City

1 Halwani Oneer Tapovan 10 Surat

2 Siddiqui Taimur 23 Banjara Hills Surat

3 Prasad Kishan Shonitpuram Srinagar

Now we want to insert a new row in the "Persons" table.We use the following SQL statement:
INSERT INTO Persons
VALUES (4,'Nilsen', 'Johan', 'Bharat Nagar', 'Srinagar'); The
"Persons" table will now look like this:

P_Id LastName FirstName Address City

1 Halwani Oneer Tapovan 10 Surat

2 Siddiqui Taimur 23 Banjara Hills Surat

3 Prasad Kishan Shonitpuram Srinagar

4 Nilsen Johan Bharat Nagar Srinagar

Insert Data Only in Specified Columns

It is also possible to only add data in specific columns.
The following SQL statement will add a new row, but only add data in the "P_Id", "LastName" and the
"FirstName" columns:
INSERT INTO Persons (P_Id, LastName, FirstName)
VALUES (5, 'Teja', 'Jaeb');
The "Persons" table will now look like this:
P_Id LastName FirstName Address City
1 Halwani Oneer Tapovan 10 Surat

2 Siddiqui Taimur 23 Banjara Hills Surat

3 Prasad Kishan Shonitpuram Srinagar

4 Nilsen Johan Bharat Nagar Srinagar

5 Teja Jaeb NULL NULL

SQL SELECT Statement (DQL)

The SELECT statement is used to select data from a database. The result is stored in a result table, called the result-set.
Syntax:
SELECT column_name1, column_name2,................column_n FROM table_name;
and
SELECT * FROM table_name;

SQL SELECT Example

SELECT LastName, FirstName FROM Persons; Output:
LastName FirstName

Halwani Oneer

Siddiqui Taimur

Prasad Kishan
SELECT * Example
Now we want to select all the columns from the "Persons" table. We use the following SELECT statement: SELECT *
FROM Persons; // The asterisk (*) is a quick way of selecting all columns
Output:
P_Id LastName FirstName Address City

1 Halwani Oneer Tapovan 10 Surat

2 Siddiqui Taimur 23 Banjara Hills Surat

3 Prasad Kishan Shonitpuram Srinagar

The SQL SELECT DISTINCT Statement

In a table, some of the columns may contain duplicate values. This is not a problem, however, sometimes you will want
to list only the different (distinct) values in a table. The DISTINCT keyword can be used to return only distinct
(different) values.
SQL SELECT DISTINCT Syntax
SELECT DISTINCT column_name(s)
FROM table_name
SELECT DISTINCT Example
SELECT DISTINCT City FROM Persons
Output: City

Surat
Srinagar
The WHERE Clause
The WHERE clause is used to extract only those records that fulfill a specified criterion/condition. SQL
WHERE Syntax
SELECT <column_name(s)>
FROM <table_name>
WHERE <column_name> operator <value>

WHERE Clause Example

The "Persons" table:
P_Id LastName FirstName Address City

1 Halwani Oneer Tapovan 10 Surat

2 Siddiqui Taimur 23 Banjara Hills Surat

3 Prasad Kishan Shonitpuram Srinagar

Now we want to select only the persons living in the city "Surat" from the table above. We use the following
SELECT statement:
SELECT * FROM Persons
WHERE City='Surat'
The result-set will look like this:
P_Id LastName FirstName Address City
1 Halwani Oneer Tapovan 10 Surat

2 Siddiqui Taimur 23 Banjara Hills Surat

Quotes Around Text Fields

SQL uses single quotes around text values (most database systems will also accept double quotes). Although,
numeric values should not be enclosed in quotes.
For text values:
This is correct:
SELECT * FROM Persons WHERE FirstName='Taimur'
This is wrong:
SELECT * FROM Persons WHERE FirstName=Taimur For
numeric values:
This is correct:
SELECT * FROM Persons WHERE Year=1965
This is wrong:
SELECT * FROM Persons WHERE Year='1965'
Operators Allowed in the WHERE Clause
With the WHERE clause, the following operators can be used:
Operator Description

= Equal

<> Not equal

> Greater than

< Less than

>= Greater than or equal

<= Less than or equal

BETWEEN Between an inclusive range

LIKE Search for a pattern

IN If you know the exact value you want to return for at least one of the columns
Note: In some versions of SQL the <> operator may be written as !=
The AND & OR Operators
The AND & OR operators are used to filter records based on more than one condition. The AND operator displays a
record if both the first condition and the second condition is true. And OR operator displays a record if either the
first condition or the second condition is true.
AND Operator Example
SELECT * FROM Persons
WHERE FirstName='Taimur' AND
LastName='Siddiqui'
The result-set will look like this:
P_Id LastName FirstName Address City

2 Siddiqui Taimur 23 Banjara Hills Surat

OR Operator Example
Now we want to select only the persons with the first name equal to "Taimur" OR the first name equal to "Oneer":
We use the following SELECT statement:
SELECT * FROM Persons
WHERE FirstName='Taimur' OR
FirstName='Oneer'
The result-set will look like this:
P_Id LastName FirstName Address City
1 Halwani Oneer Tapovan 10 Surat

2 Siddiqui Taimur 23 Banjara Hills Surat

SQL LIKE Operator
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. It is used to search for a
specified pattern in a column.
SQL LIKE Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern
LIKE Operator Example
SELECT * FROM Persons
WHERE City LIKE 's%'
Pattern (SQL Wildcards)
SQL wildcards can substitute for one or more characters when searching for data in a database.
SQL wildcards must be used with the SQL LIKE operator. With SQL, the following wildcards can be used:

Wildcard Description

% A substitute for zero or more characters

_ A substitute for exactly one character

[charlist] Any single character in charlist

[^charlist] or [!charlist] Any single character not in charlist
SQL Wildcard Examples
Using the % Wildcard
Now we want to select the persons living in a city that starts with "su" from the "Persons" table. We use the following
SELECT statement:
SELECT * FROM Persons
WHERE City LIKE 'su%'
The result-set will look like this:
P_Id LastName FirstName Address City

1 Halwani Oneer Tapovan 10 Surat

2 Siddiqui Taimur 23 Banjara Hills Surat
Next, we want to select the persons living in a city that contains the pattern "rat" from the "Persons" table. We use
the following SELECT statement:
SELECT * FROM Persons
WHERE City LIKE '%rat%'
The result-set will look like this:
P_Id LastName FirstName Address City

1 Halwani Oneer Tapovan 10 Surat

2 Siddiqui Taimur 23 Banjara Hills Surat

Using the _ Wildcard
Now we want to select the persons with a first name that starts with any character, followed by "neer" from the
"Persons" table.
We use the following SELECT statement:
SELECT * FROM Persons WHERE
FirstName LIKE '_neer'
The result-set will look like this:
P_Id LastName FirstName Address City

1 Halwani Oneer Tapovan 10 Surat

Next, we want to select the persons with a last name that starts with "S", followed by any character, followed by
"dd", followed by any character, followed by "ui" from the "Persons" table.
We use the following SELECT statement:
SELECT * FROM Persons WHERE
LastName LIKE 'S_dd_ui'
The result-set will look like this:
P_Id LastName FirstName Address City

2 Siddiqui Taimur 23 Banjara Hills Surat

Using the [charlist] Wildcard

We use the following SELECT statement: To select Persons whose LastName has ‘q’ or ‘r’ characters at
any positions in Person table.
SELECT * FROM Persons
WHERE LastName LIKE '[qr]%'
The result-set will look like this:
P_Id LastName FirstName Address City

2 Siddiqui Taimur 23 Banjara Hills Surat

3 Prasad Kishan Shonitpuram Srinagar

We use the following SELECT statement: To select Persons whose LastName does not have ‘e’, ‘q’ or ‘r’
characters at any positions in Person table.
SELECT * FROM Persons
WHERE LastName LIKE '[!eqr]%' The
result-set will look like this:

P_Id LastName FirstName Address City

1 Halwani Oneer Tapovan 10 Surat

The IN Operator
The IN operator allows you to specify multiple values in a WHERE clause.
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...)
IN Operator Example
We use the following SELECT statement: To show details of Persons with LastName as ‘Halwani’ or ‘Prasad’.
SELECT * FROM Persons
WHERE LastName IN ('Halwani','Prasad') The
result-set will look like this:

P_Id LastName FirstName Address City

1 Halwani Oneer Tapovan 10 Surat

3 Prasad Kishan Shonitpuram Srinagar

The BETWEEN Operator
The BETWEEN operator selects a range of data between two values. The values can be numbers, text, or dates.
SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2
BETWEEN Operator
Example The "Persons" table:
P_Id LastName FirstName Address City

1 Halwani Oneer Tapovan 10 Surat

2 Siddiqui Taimur 23 Banjara Hills Surat

3 Prasad Kishan Shonitpuram Srinagar

Now we want to select the persons with a last name alphabetically between "Halwani" and "Prasad" from the table
above.
We use the following SELECT statement:
SELECT * FROM Persons
WHERE LastName
BETWEEN 'Halwani' AND 'Prasad' The
result-set will look like this:

P_Id LastName FirstName Address City

1 Halwani Oneer Tapovan 10 Surat

Note: The BETWEEN operator is treated differently in different databases.

In some databases, persons with the LastName of "Halwani" or "Prasad" will not be listed, because the BETWEEN
operator only selects fields that are between and excluding the test values).
In other databases, persons with the LastName of "Halwani" or "Prasad" will be listed, because the BETWEEN
operator selects fields that are between and including the test values).
And in other databases, persons with the LastName of "Halwani" will be listed, but "Prasad" will not be listed (like
the example above), because the BETWEEN operator selects fields between the test values, including the first test
value and excluding the last test value.
Therefore: Check how your database treats the BETWEEN operator. Example 2
To display the persons outside the range in the previous example, use NOT BETWEEN:
SELECT * FROM Persons
WHERE LastName
NOT BETWEEN 'Halwani' AND 'Prasad' The
result-set will look like this:

P_Id LastName FirstName Address City

2 Siddiqui Taimur 23 Banjara Hills Surat

3 Prasad Kishan Shonitpuram Srinagar

ALTER TABLE(Added as per Q.33 of sample paper 2023-24)
Whenever the definition of existing table needs to be changed the DDL command ALTER is used to modify the
table.
ALTER Command works on Columns.
SYNTAX:-
ALTER TABLE Table_name
ADD/MODIFY/DROP
(column1_name datatype(size),
column2_name datatype(size), …………………………………
column_name datatype(size) );
e.g. 1:- Add a column stdcode char(7) in table companies. Ans.:-
ALTER TABLE companies
ADD (stdcode char(7));
e.g. 2:- Modify the column stdcode varchar(7) in table companies. Ans.:-
ALTER TABLE companies MODIFY
(stdcode Varchar(7));
e.g. 3:- Remove the column stdcode in table companies. Ans.:-
ALTER TABLE companies DROP stdcode;

DROP TABLE(Added as per Q.33 of sample paper 2023-24)

This DDL command is used to permanently remove a table or database.
SYNTAX:-
DROP TABLE Table_Name ;
DROP Database Database_name;
e.g. :- To Permanently remove table ‘companies’ from database.
Ans.:-DROP TABLE companies ;
eg:-To Permanently remove database organization. Ans.:-
DROP DATABASE organization ;

UPDATE (Added as per Q.33 of sample paper 2023-24)

Two clause UPDATE & SET are used for changing the values stored in any cell.
SYNTAX:::-
UPDATE Table_name SET column_name = value_expression [WHERE condition];
e.g. 1:- update all companies with stdcode 011. Ans.:-
UPDATE companies SET stdcode = ‘011’;
e.g. 2 :- Change the stdcode to 022 for city Mumbai.
Ans.:- UPDATE companies SET stdcode = ‘022’ WHERE city='Mumbai;
e.g. 3 :- Modify the stdcode to 011,cname to sony and city to delhi for cnum 1002.
Ans.:- UPDATE companies SET cname=‘Sony’ , city = ‘Delhi’ , stdcode = ‘011’ WHERE cnum=1002;
e.g. 4 :- Change the stdcode to 011 where city is Delhi in table companies . Ans.:-
UPDATE companies SET stdcode = ‘011’ WHERE city=‘Delhi’;

DELETE(Added as per Q.33 of sample paper 2023-24)

We can delete particular row(s)from a table.
SYNTAX:::-
DELETE FROM Table_Name [WHERE condition];
e.g. :- delete all rows from companies where cnum is 1003. Ans.:-
DELETE FROM companies WHERE cnum=1003;

SQL Constraints (Added as per Q.33 of sample paper 2023-24)

Constraints are used to limit the type of data that can go into a table. Constraints can be specified when a table is
created (with the CREATE TABLE statement) or after the table is created (with the ALTER TABLE statement). We
will focus on the following constraints:
● NOT NULL
● UNIQUE
● PRIMARY KEY
● FOREIGN KEY
● CHECK
● DEFAULT
SQL PRIMARY KEY Constraint
- The PRIMARY KEY constraint uniquely identifies each record in a database table.
- Primary keys must contain unique values. A primary key column cannot contain NULL values. - - Each table
should have a primary key, and each table can have only one primary key.
SQL PRIMARY KEY Constraint on CREATE TABLE
The following SQL creates a PRIMARY KEY on the "P_Id" column when the "Persons" table is created:
Example:
CREATE TABLE Persons (
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255), City
varchar(255), PRIMARY
KEY (P_Id)
)
Note: We will use the following table for subsequent queries: Persons
P_Id LastName FirstName Address City
1 Halwani Oneer Tapovan 10 Surat
2 Siddiqui Taimur 23 Banjara Hills Surat
3 Prasad Kishan Shonitpuram Srinagar
4 Nilsen Johan Bharat Nagar Srinagar

SQL Alias (Not part of the syllabus)

You can give a table or a column another name by using an alias. This can be a good thing to do if you have very
long or complex table names or column names. An alias name could be anything, but usually it is short.
SQL Alias Syntax for Columns
SELECT column_name AS alias_name
FROM table_name;

Differences between DELETE and DROP

DELETE DROP
1. It is a DDL Command It is a DML command.
2. It is used to remove table from database. It is used to remove rows/records/data from table.
3. E.g. DROP TABLE Student; E.g. DELETE FROM Customer WHERE Name is NULL;

Differences between ALTER and UPDATE

ALTER UPDATE
1. It is a DDL Command It is a DML command.
2. It is used to add, modify, remove the column. It is used to modify the data stored in rows
3. E.g. ALTER TABLE Student ADD Marks INT; E.g. UPDATE Student SET Class=”XII” WHERE
Name=”Kavita”;
Differences between DDL and DML

DDL(DATA DEFINATION LANGUAGE) DML(DATA MANIPULATION LANGUAGE)

1.It deals with structure of table. Like Table Name, It deals with data stored in rows.
Columns.
2. It is used to create, alter, drop table from database. It is used to insert, update, delete the rows from table.

E.g. CREATE TABLE, ALTER TABLE, DROP E.g. INSERT , UPDATE, DELETE, SELECT
TABLE etd.

Functions in SQL
Function :
A function is a predefined command set that performs some operations and returns the single value. A function
can have single, multiple or no arguments at all.
SQL Functions: SQL supports two kinds of functions:
1. Single Row Functions : These functions work on a single value at a time and produce a result for each value
they operate on. They may obtain the data as an argument or from the value of a column of a table specified as
an argument.

2. Multirow Functions/Aggregate Functions : These functions work on a group of values at a time but
produce a single result for each group they work on.
Single Row Functions : Single row functions can be further classified into various types. Few of them are :
a. Text Function:
b. Math Functions
c. Date and Time functions

We will be using the following table to demonstrate various functions in the coming text.
Table : emp
Empid Ename Job Sal DeptNo Date_of_joining

1425 Jack Manager 1500 10 1978-12-22

1422 Jill Manager 1600 20 1988-10-22

1421 Aryaman Analyst 1550 30 1988-06-15

1427 Vikram Salesman 1200 10 1982-06-23

1429 George Salesman 1200 20 1983-06-17

1477 Sandeep NULL 1500 NULL 1999-12-12

a. Text/String Functions : Text functions generally perform an operation on a string input value and
return a string or numeric value. Various text function are described below:
i. Ucase()/Upper() : Converts given string to Uppercase. Examples : Select
upper('Apple Is reD');
Output:
APPLE IS RED

Select Upper(‘f/kvschool/2001-12/LIB/22’);
Output
F/KVSCHOOL/2001-12/LIB/22

ii. Initcap() :
iii. Lcase()/Lower(): Converts given string to lowercase. Examples : Select
lower('Apple Is reD');
Output:
apple is red
Select lcase(‘f/kvschool/2001-12/LIB/22’);
Output:
f/kvschool/2001-12/lib/22’

iv. Length() : It counts the number of characters in a given string. It includes all upper and lower
case alphabets, digits, spaces and other special characters.

Examples:
Select length('Hockey is our national game'); output:
27

Select (‘f/kvschool/2001-12/LIB/22’);
Output:
25

v. Left() : It extracts N characters from the left side of a given String. Syntax
left(String, No of Characters to be extracted)
Examples :
Select Left ( ‘Orange’, 3)
Output :
Ora

Select Left ( ‘Orange’, 10)

Output :
Orange

Note: If the number of characters extracted are more than the length of the string, the left function returns the
same string without any leading or trailing spaces.

Select left(Ename,4) from emp where deptno=10;

left(Ename,4)

Jack
Vikr

vi. Right() : It extracts N characters from the right side of a given String. Syntax:
right(String, No of Characters to be extracted)
Examples :
Select Right ( ‘Orange’, 3)
Output :
nge

Select Right ( ‘Orange’, 10)

Output :
Orange

Note: if the number of characters extracted are more than the length of the string, the right function returns
the same string without any leading or trailing spaces.

vii. MID()/Substr()/Substring() : function extracts some characters from a string.

Syntax: SUBSTRING(string, start, No. of characters)
Examples:
Select Substring(‘Incredible Rajasthan’, 3, 5);
credi

Select Substr (‘Incredible Rajasthan’, 5,3);

edi

Select MID(‘Incredible Rajasthan’, 12);

Rajasthan

Select MID(‘Incredible Rajasthan’, -4,2);

th

Note :
● proving length/ no of characters to be extracted is optional. In case it is not provided, the
function extracts all characters from the given position till the end of the string.
● If the second argument (start) is negative, it will count from the right side of the string.
● No. of characters can not be negative, it will return empty string if supplied.

Select mid(date_of_joining, 6,2) from emp where deptno=20;

mid(date_of_joining, 6,2)

10
06

viii. Instr() : returns the position of the first occurrence of a string in another string.
Example:
SELECT INSTR(‘CBSE Exam’, ‘E’);
INSTR(‘CBSE Exam’, ‘E’)

SELECT INSTR(‘CBSE Exam’, ‘KV’);

INSTR(‘CBSE Exam’, ‘KV’)
0

Note: if the substring is not found in the main string, Instr() returns 0.
ix. Ltrim() : Removes Spaces on the left side of a given string.
Select Ltrim(‘### India Shining##‘)

Ltrim('### India Shining## ')

India Shining##

Note: assume there are three spaces before and two spaces after India Shining respectively.
Spaces have been represented by #

x. Rtrim() : Removes Spaces on the Right side of a given string.

Select Rtrim(‘### India Shining## ‘)

Rtrim('### India Shining## ')

###India Shining

xi. Trim() : Removes both leading (left) and Trailing (right ) Spaces from a given string.
Select Trim### India Shining## ‘)

Trim('### India Shining## ')

India Shining

Select length(Trim(‘### India Shining## ‘)

Length(Trim('### India Shining## ')

13

b. Math Functions:
i. power(x,y)/pow(x,y): It returns the x raised to the power of y (xy).
select power(2,3);
power(2,3)
8

select pow(-1,5);
pow(-1,5)
-1
select pow(-1,4);
pow(-1,4)
1

select pow(10,-2);
pow(10,-2)
0.01
Note: Here the concept of negative power will be applied.
select pow(144, 1/2);

pow(144,
Note: 1/2)
Alternate way of finding square root.
12
ii. Round(N,D) : Rounds number N upto given D no. of digits (by default D=0, if not specified) select
round(4534.9767);
round(4534.9767)
4535
select round(4534.9767,0);
round(4534.9767,0)
4535

select round(4534.9767);
round(4534.9767)
4535

select round(4534.97378778,2);
round(4534.97378778,2)
4534.97

select round(4534.97578778,2);
round(4534.97578778,2)
4534.98

select round(4534.997,2);
round(4534.997,2)
4535.00

select round(4534.997,4);
round(4534.997,4)
4534.9970
Select round(4534.997,-1);
round(4534.997,-1)
4530

select round(4584.997,-2);
round(4584.997,-2)
4600

iii. MOD() : Remainder of X/Y

select mod(13,5);
mod(13,5)
3
select mod(6,10);
+ +
| mod(6,10) |
+ +
| 6|
+ +

select mod(-17, 5) rem;

+ +
| rem |
+ +
| -2 |
+ +

select mod(-17, -5) as rem;

+ +
| rem |
+ +
| -2 |
+ +

select mod(17, -5) rem;

+ +
| rem |
+ +
| 2|
+ +

c. Date Functions():
i. Now() : returns the current date and time, as "YYYY-MM-DD HH:MM:SS" (string)
Select Now();
+ +
| now() |
+ +
| 2022-10-19 18:31:32 |
+ +
Assuming that the current date in the system is 19-Oct-2022 and time is 6:31pm
ii. Date() : returns the date part(yyyy-mm-dd) of date time value supplied as argument.
select date('1978-02-23 00:00:10')
+ +
| date('1978-02-23 00:00:10') |
+ +
| 1978-02-23 |
+ +
iii. Day() : returns the day part of the date/date-time value supplied as argument.
select day('1978-02-23');
+ +
| day('1978-02-23') |
+ +
| 23 |
+ +

select day('1978-02-09 18:31:32');

+ +
| day('1978-02-09 18:31:32') |
+ +
| 9 |
+ +

iv. Month() : returns the month part for a given date/date-time (a number from 1 to 12).
select month('1978-02-23');
+ +
| month('1978-02-23') |
+ +
| 2|
+ +
v. Year() : returns the year part for a given date/date-time.
select year('1978-02-23');
+ +
| year('1978-02-23') |
+ +
| 1978 |
+ +

vi. MonthName() : returns the name of the month for a given date/date-time.
select monthname('2017-09-14');
+ +
| monthname('2017-09-14') |
+ +
| September |
+ +
vii. DayName() : returns the Day Name corresponding to date/date-time value supplied as
argument.
select dayname('2017-09-14');
+ +
| dayname('2017-09-14') |
+ +
| Thursday |
+ +
Group by Functions/Multi-Row functions/Aggregate Functions :
i. SUM() : it returns the sum of values of a numeric column.
● It gives the arithmetic sum of all the values present in a particular column.
● It can take only one argument.
● NULL values are not included in the calculations. Select
Sum(Sal) from emp;
Sum(sal)

7050

ii. COUNT(): the COUNT() function returns the number of rows that matches a specified criterion.
● Takes only one argument, which can be a column name or *.
● Count doesn’t count Null Values.
● Count(*) counts the number of rows in the table. A row is counted even if all the values in the row are
null values.
Select Count(*) from emp;

Count(*)

Select Count(job) from emp;

Count(job)
5

Note: The number of values in job column was 6 but it return 5 as one of the value is null
iii. AVG() : the average value of a numeric column.
● It gives the arithmetic average of all the values present in a particular column.
● It can take only one argument.
● NULL values are not included in the calculations. If in a column there are 7 values out of which 2
are NULL, to calculate the average, MySQL will divide the sum of 5 NOT NULL values by 5.
Select avg(sal) from emp;

avg(sal)
1410.0000

iv. MAX() : The Maximum value of a column

● It gives the maximum of all the values present in a particular column.
● Value can be integer, float, decimal, varchar, char or date
● It can take only one argument.
● NULL values are not included in the calculations.
● If all the values in the column are NULL, the function returns Null Value.

Select Max(sal) from emp;

Max(sal)

1600

Select Max(ename) from emp;

Max(ename)

Vikram

Select Max(Date_of_joining) from emp

Max(Date_of_joining)

1988-10-22

v. MIN() : The Minimum value of a column.

● It gives the minimum of all the values present in a particular column.
● Value can be integer, float, decimal, varchar, char or date
● It can take only one argument.
● NULL values are not included in the calculations.
● If all the values in the column are NULL, the function returns Null Value.
select Min(sal) from emp;
Min(sal)
1200
select Min(ename) from emp;
Min(ename)
Aryaman

select Min(Date_of_joining) from emp;

Min(Date_of_joining)
1978-12-22

Group by :

The GROUP BY Clause is utilized in SQL with the SELECT statement to organize similar data into groups. It
combines the multiple records in single or more columns using some functions.
select deptno, sum(sal) from emp group by deptno;
deptno sum(sal)

10 2700
20 2800

30 1550

select job, max(sal), min(sal) from emp group by job;

job max(sal) min(sal)
Analyst 1550 1550

Manager 1600 1500

Salesman 1200 1200

Having clause : The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate
functions.

select deptno, sum(sal) from emp group by deptno having sum(sal)>2000

deptno sum(sal)

10 2700
20 2800

Difference between where and having clause

Where Clause Having Clause

WHERE Clause is used to filter the records from the HAVING Clause is used to filter the records from
table or used while joining more than one the groups based on the given condition in the
table.Only those records will be extracted who are HAVING Clause. Those groups who will satisfy
satisfying the specified condition in WHERE the given condition will appear in the final result
clause.

WHERE clause is used before GROUP BY HAVING clause is used after GROUP BY

Order by :The ORDER BY clause is used to sort the query result-set in ascending or descending order. It sorts the
records in ascending order by default. To sort the records in descending order, use the DESC keyword.
Select empid, Ename, deptno from emp order by empid;

empid Ename deptno

1421 Aryaman 30
1422 Jill 20
1425 Jack 10
1427 Vikram 10
1429 George 20
Select Ename, deptno, sal from emp order by sal desc;

Ename deptno sal

Jill 20 1600
Aryaman 30 1550
Jack 10 1500
Vikram 10 1200
George 20 1200
Select Ename, deptno,sal from emp order by deptno, sal desc;

Ename deptno sal

Jack 10 1500
Vikram 10 1200
Jill 20 1600
George 20 1200
Aryaman 30 1550
OPERATION IN RELATIONSHIP
UNION: the UNION Operator allows manipulation of results returned by two or more queries by combining
the results of each query into a single result set.
Example :Relation A and B

Rolln Name Age

o Rolln Name Age
101 Sidharth 19 o
102 Kushagra 18 301 Anubha 17
103 Usman 18 102 Kushagra 18
303 Shaba 18
The UNION of these two relations can be created as :-
Select * from A union select * from B;
+------+----------+------+
| Rollno | name | age |
+------+----------+------+
| 101 | sidharth | 19 |
| 102 | kushagra | 18 |
| 103 | usman | 18 |
| 301 | anubha | 17 |
| 303 | shaba | 18 |
+------+----------+------+
5 rows in set (0.01 sec)

INTERSECTION
The INTERSECTION of these two relations can be created as :-
Select * from A intersect select * from B;
+------+----------+------+
| Rollno | name | age |
+------+----------+------+
| 102 | kushagra | 18 |
+------+----------+------+
1 row in set (0.00 sec)

Working with two tables using join

A JOIN is a query through which we can extract queries from two or more tables. It means, it combines rows
fro two or more tables. Rows in one table can be joined to rows in another table according to common
values existing in corresponding columns.

EQUI-JOIN

In an EQUI-JOIN operation, the values in the columns are being joined and compared through equality
operator(=) is called Equi join. All the columns in the tables being joined are included in the results,

Syntax :

SELECT column_list FROM table1, table2.... WHERE table1.column_name = table2.column_name;

Select * from Student;

id name class city

3 Hina 3 Delhi

4 Megha 2 Delhi

6 Gouri 2 Delhi
Select * from Record;
id class city

9 3 Delhi

10 2 Delhi

12 2 Delhi

Example
SELECT student.name, student.id, record.class, record.city
FROM student, recordWHERE student.city = record.city;
name id class city

Hina 3 3 Delhi

Megha 4 3 Delhi

Gouri 6 3 Delhi

Hina 3 2 Delhi

Megha 4 2 Delhi

Gouri 6 2 Delhi

Hina 3 2 Delhi

Megha 4 2 Delhi

Gouri 6 2 Delhi

NON- EQUI JOIN

A non-equi join is a join condition containing something other than an equality operator.
Syntax:
SELECT * FROM table_name1, table_name2 WHERE table_name1.column [> | < | >= | <= ]
table_name2.column;
Example –
SELECT student.name, record.id, record.city FROM student, record WHERE Student.id < Record.id ;

Output :
name id city

Hina 9 Delhi

Megha 9 Delhi

Gouri 9 Delhi

1
Hina Delhi
0
name id city

1
Megha Delhi
0

1
Gouri Delhi
0

1
Hina Delhi
2

1
Megha Delhi
2

1
Gouri Delhi
2

NATURAL JOIN

The result of an equi-join contains two identical columns. Here by restarting the query, we can
eliminate one of the two identical columns. It is known as Natural Join.
We can also join two tables using the natural join using NATURAL JOIN clause.

CARTESIAN PRODUCT
The cartesian product is a binary operation and is denoted by (x). The degree of new relation is the sum of
the degrees of two relations on which cartesian product is operated. The number of tuples, of the new
relation is equal to the product of the number of tuples, of the two relations on which cartesian product is
performed.
e.g. if A = {1, 2, 3} and B = {a, b, c}, find A x B.

A x B = ((1, a), (1, b), (1, c), (2, a), (2, b), (2, c), (3, a), (3, b), (3, c))
In sql, the CROSS JOIN or CARTESIAN JOIN is used to produce the cartesian product of two tables.