CLASS-XII , IP(065) UNIT-II

Unit 2: Database Query using SQL

SYLLABUS

Math functions: POWER (), ROUND (), MOD ().

Text functions: UCASE ()/UPPER (), LCASE ()/LOWER (), MID ()/SUBSTRING ()/SUBSTR (),

LENGTH (), LEFT (), RIGHT (), INSTR (), LTRIM (), RTRIM (), TRIM ().

Date Functions: NOW (), DATE (), MONTH (), MONTHNAME (), YEAR (), DAY (), DAYNAME ().

Aggregate Functions: MAX (), MIN (), AVG (), SUM (), COUNT (); using COUNT (*).

Querying and manipulating data using Group by, Having, Order by.

WRITTEN BY:- ER. JAY PRAKASH SINGH(9453264720) 1

 CLASS-XII , IP(065) UNIT-II

WRITTEN BY:- ER. JAY PRAKASH SINGH(9453264720) 2

 CLASS-XII , IP(065) UNIT-II

WRITTEN BY:- ER. JAY PRAKASH SINGH(9453264720) 3

 CLASS-XII , IP(065) UNIT-II

Math functions: POWER (), ROUND (), MOD ().

POWER ()

Returns the value of one expression raised to the power of another expression

POWER(X,Y)

These two functions return the value of X raised to the power of Y.

SQL> SELECT POWER(3,3);
POWER(3,3)
27

ROUND ()

Returns numeric expression rounded to an integer. Can be used to round an expression to a number of decimal
points

ROUND(X,D)

This function returns X rounded to the nearest integer. If a second argument, D, is supplied, then the function
returns X rounded to D decimal places. D must be positive or all digits to the right of the decimal point will be
removed. Consider the following example −
SQL>SELECT ROUND(5.693893);
ROUND(5.693893)
6

SQL>SELECT ROUND(5.693893,2);
ROUND(5.693893,2)
5.69

WRITTEN BY:- ER. JAY PRAKASH SINGH(9453264720) 4

 CLASS-XII , IP(065) UNIT-II

MOD ()

Returns the remainder of one expression by diving by another expression.

MOD(N,M)

This function returns the remainder of N divided by M. Consider the following example −
SQL>SELECT MOD(29,3);
MOD(29,3)
2

Text functions:
UCASE ()/UPPER ():- Converts a string to upper-case, This function is equal to the UCASE() function.

Example
Convert the text in "CustomerName" to upper-case:

SELECT UPPER(CustomerName) AS UppercaseCustomerName

FROM Customers;

LCASE ()/LOWER ():-Converts a string to lower-case. The LCASE() function is equal to the LOWER()
function.

Example
Convert the text in "CustomerName" to lower-case:

SELECT LOWER(CustomerName) AS LowercaseCustomerName

FROM Customers;

MID ()/SUBSTRING ()/SUBSTR ():-Extracts a substring from a string (starting at any position)

Syntax
MID(string, start, length)

WRITTEN BY:- ER. JAY PRAKASH SINGH(9453264720) 5

 CLASS-XII , IP(065) UNIT-II

Parameter Values

Parameter Description

string Required. The string to extract from

start Required. The start position. Can be both a positive or negative

number. If it is a positive number, this function extracts from the
beginning of the string. If it is a negative number, this function
extracts from the end of the string

length Required. The number of characters to extract

Example
Extract a substring from the text in a column (start at position 2, extract 5 characters):

SELECT MID(CustomerName, 2, 5) AS ExtractString

FROM Customers;

LENGTH ():- Returns the length of a string (in bytes)

Example
Return the length of the string, in bytes:

SELECT LENGTH("SQL Tutorial") AS LengthOfString;

LEFT ():-Extracts a number of characters from a string (starting from left)

Example
Extract 3 characters from a string (starting from left):

SELECT LEFT("SQL Tutorial", 3) AS ExtractString;

WRITTEN BY:- ER. JAY PRAKASH SINGH(9453264720) 6

 CLASS-XII , IP(065) UNIT-II

RIGHT ():-Extracts a number of characters from a string (starting from right)

Example
Extract 4 characters from a string (starting from right):

SELECT RIGHT("SQL Tutorial is cool", 4) AS ExtractString;

INSTR ():-Inserts a string within a string at the specified position and for a certain number of characters

Syntax
INSTR(string1, string2)

Parameter Values
Parameter Description

string1 Required. The string that will be searched

string2 Required. The string to search for in string1. If string2 is not found, this
function returns 0

Example
Search for "COM" in string "mvmpublicschool.com", and return position:

SELECT INSTR("W3Schools.com", "COM") AS MatchPosition;

LTRIM ():-Removes leading spaces from a string

Example
Remove leading spaces from a string:

SELECT LTRIM(" SQL Tutorial") AS LeftTrimmedString;

WRITTEN BY:- ER. JAY PRAKASH SINGH(9453264720) 7

 CLASS-XII , IP(065) UNIT-II

RTRIM ():-Removes trailing spaces from a string

Example
Remove trailing spaces from a string:

SELECT RTRIM("SQL Tutorial ") AS RightTrimmedString;

TRIM ():-Removes leading and trailing spaces from a string

Example
Remove leading and trailing spaces from a string:

SELECT TRIM(' SQL Tutorial ') AS TrimmedString;

Date Functions:
NOW ():-Returns the current date and time, The date and time is returned as "YYYY-MM-DD HH-MM-SS"
(string) or as YYYYMMDDHHMMSS.uuuuuu (numeric).

Example
Return current date and time:

SELECT NOW();

DATE ():-The DATE() function extracts the date part from a datetime expression.

Example
Extract the date part:

SELECT DATE("2017-06-15 09:34:21");

MONTH ():-The MONTH() function returns the month part for a given date (a number from 1 to 12).

Example
Return the month part of a date:

SELECT MONTH("2017-06-15 09:34:21");

WRITTEN BY:- ER. JAY PRAKASH SINGH(9453264720) 8

 CLASS-XII , IP(065) UNIT-II

MONTHNAME ():-The MONTHNAME() function returns the name of the month for a given date.

Example
Return the name of the month for a date:

SELECT MONTHNAME("2017-06-15 09:34:21");

YEAR ():-The YEAR() function returns the year part for a given date (a number from 1000 to 9999).

Example
Return the year part of a date:

SELECT YEAR("2017-06-15 09:34:21");

DAY ():- The DAY() function returns the day of the month for a given date (a number from 1 to 31).

Example
Return the day of the month for a date:

SELECT DAY("2017-06-15 09:34:21");

DAYNAME ():-The DAYNAME() function returns the weekday name for a given date.

Example
Return the weekday name for a date:

SELECT DAYNAME("2017-06-15");

Aggregate Functions:
Aggregate functions in DBMS take multiple rows from the table and return a value according to the query.
All the aggregate functions are used in Select statement.
Syntax −

SELECT <FUNCTION NAME> (<PARAMETER>) FROM <TABLE NAME>

WRITTEN BY:- ER. JAY PRAKASH SINGH(9453264720) 9

 CLASS-XII , IP(065) UNIT-II

MAX ( ):-

The MAX function is used to find maximum value in the column that is supplied as a parameter. It can be used
on any type of data.
Example − Write a query to find the maximum salary in employee table.

Select MAX(salary) from Employee

MIN ( ):-

The MIN function is used to find minimum value in the column that is supplied as a parameter. It can be used
on any type of data.
Example − Write a query to find the minimum salary in employee table.

Select MIN(salary) from Employee

AVG ( ):-

This function returns the average value of the numeric column that is supplied as a parameter.
Example: Write a query to select average salary from employee table.

Select AVG(salary) from Employee

SUM ( ):-

This function sums up the values in the column supplied as a parameter.

Example: Write a query to get the total salary of employees.

Select SUM(salary) from Employee

WRITTEN BY:- ER. JAY PRAKASH SINGH(9453264720) 10

 CLASS-XII , IP(065) UNIT-II

COUNT () using COUNT (*):-

The count function returns the number of rows in the result. It does not count the null values.
Example: Write a query to return number of rows where salary > 20000.

Select COUNT(*) from Employee where Salary > 20000;

Types −
 COUNT(*): Counts all the number of rows of the table including null.
 COUNT( COLUMN_NAME): count number of non-null values in column.
 COUNT( DISTINCT COLUMN_NAME): count number of distinct values in a column.

FOR MORE DETAILS JOINS………

WRITTEN BY:- ER. JAY PRAKASH SINGH(9453264720) 11

 CLASS-XII , IP(065) UNIT-II

HAVING Clause :- enables you to specify conditions that filter which group results appear in the results.
The WHERE clause places conditions on the selected columns, whereas the HAVING clause places conditions
on groups created by the GROUP BY clause.

Syntax
The following code block shows the position of the HAVING Clause in a query.

SELECT column1, column2

FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2

Example
Consider the CUSTOMERS table having the following records.
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
Following is an example, which would display a record for a similar age count that would be more than or
equal to 2.
SQL > SELECT ID, NAME, AGE, ADDRESS, SALARY
FROM CUSTOMERS
GROUP BY age
HAVING COUNT(age) >= 2;
This would produce the following result −
+----+-----------+-------+--------------+-------------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+-----------+-------+--------------+-------------+
| 2 | Khilan | 25 | Delhi | 1500.00 |
+----+-----------+-------+---------------+------------+

WRITTEN BY:- ER. JAY PRAKASH SINGH(9453264720) 12

 CLASS-XII , IP(065) UNIT-II

ORDER BY :- clause is used to sort the data in ascending or descending order, based on one or
more columns. Some databases sort the query results in an ascending order by default.

Syntax
The basic syntax of the ORDER BY clause is as follows −
SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];
You can use more than one column in the ORDER BY clause. Make sure whatever column you are
using to sort that column should be in the column-list.

Example

Consider the CUSTOMERS table having the following records −

+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
The following code block has an example, which would sort the result in an ascending order by the
NAME and the SALARY −
SQL> SELECT * FROM CUSTOMERS
ORDER BY NAME, SALARY;
This would produce the following result −
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |

WRITTEN BY:- ER. JAY PRAKASH SINGH(9453264720) 13

 CLASS-XII , IP(065) UNIT-II

GROUP BY clause is used in collaboration with the SELECT statement to arrange identical data
into groups. This GROUP BY clause follows the WHERE clause in a SELECT statement and
precedes the ORDER BY clause.

Syntax

The basic syntax of a GROUP BY clause is shown in the following code block. The GROUP BY
clause must follow the conditions in the WHERE clause and must precede the ORDER BY clause if
one is used.
SELECT column1, column2
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2
ORDER BY column1, column2

Example
Consider the CUSTOMERS table is having the following records −
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
If you want to know the total amount of the salary on each customer, then the GROUP BY query
would be as follows.
SQL> SELECT NAME, SUM(SALARY) FROM CUSTOMERS
GROUP BY NAME;
This would produce the following result −
+----------+-------------+
| NAME | SUM(SALARY) |
+----------+-------------+
| Chaitali | 6500.00 |
| Hardik | 8500.00 |
| kaushik | 2000.00 |
| Khilan | 1500.00 |
| Komal | 4500.00 |
| Muffy | 10000.00 |
| Ramesh | 2000.00 |

WRITTEN BY:- ER. JAY PRAKASH SINGH(9453264720) 14