Database Query Using SQL :

Informatics Practices (065) Class 12 Syllabus 2021-22

This Unit : Database Query Using SQL comprises of 25 Marks out of 70

Syllabus of Unit 2: Database Query using SQL

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.
Operations on Relations – Union, Intersection, Minus, Cartesian Product, JOIN (Cartesian Join, Equi Join, Natural Join)

Functions in SQL :

Functions are used to perform some particular task and it returns zero or more values as a result. SQL functions
are categorized as Single row functions and Aggregate functions.

1) Single Row Functions :

Those functions which are applied on a single value and return a single value are called Single Row Functions.

There are three categories of single row functions in SQL

1. Numeric Function
2. String Function
3. Date Function

1) Numeric Functions : Three commonly used numeric functions are POWER( ), ROUND( ) and MOD( ). These functions are also called Math Functions

Function Name	Description	Example with output
POWER(X,Y) or POW(X,Y)	Calculates X to the power Y.	mysql> SELECT POWER(2,3); Output: 8 mysql> SELECT POWER(3,3); Output: 27
ROUND(N,D)	Rounds off number N to D number of decimal places.	mysql>SELECT ROUND(2912.564, 2); Output: 2912.56 mysql> SELECT ROUND(283.2); Output: 283
MOD(A, B)	Returns the remainder after dividing number A by number B.	mysql> SELECT MOD(21, 2); Output: 1 mysql> SELECT MOD(2, 21); Output: 2

Practice Questions :

Q1. Write the output of the following statements :

1. Select round(1245.231, 1);
2. Select round(7564.254, 2);
3. Select round(8925.86,1);
4. Select round(369.825,0);
5. Select round(569.235,-1);
6. Select round(478.723,-2);
7. Select pow(3, 3);
8. Select pow(4, 2);
9. Select mod(23, 3);
10. Select mod(3, 12);

Ans.

1. 1245.2
2. 7564.25
3. 8925.9
4. 370
5. 570
6. 500
7. 27
8. 16
9. 2
10. 3

2) String Functions : String functions can perform various operations on alphanumeric data. String functions and their usage are:

Function Name	Description	Example with output
UCASE(string) OR UPPER(string)	Converts string into uppercase	SELECT UCASE(“Informatics”); Output: INFORMATICS SELECT UCASE(“MySQL”); Output: MYSQL
LOWER(string) OR LCASE(string)	Converts string into lowercase	SELECT LCASE(“Informatics”); Output: informatics SELECT LCASE(“MySQL”); Output: mysql
MID(string, pos, n) OR SUBSTRING(string, pos, n) OR SUBSTR(string, pos, n)	Returns a substring of size n starting from the specified position (pos) of the string. If n is not specified, it returns the substring from the position pos till end of the string.	SELECT MID(“Informatics”, 2, 4); Output: nfor SELECT MID(“Practices”, 2); Output: ractices
LENGTH(string)	Return the length of the string.	Select length(“Sumit”) Output : 5
LEFT(string, N)	Returns N number of characters from the left side of the string.	SELECT LEFT(“Science”, 4); Output: Scie
RIGHT(string, N)	Returns N number of characters from the right side of the string.	SELECT RIGHT(“csiplearninghub”, 3); Output: hub
INSTR(string, substring	Returns the position of the first occurrence of the substring in the given string. Returns 0, if the substring is not present in the string.	SELECT INSTR(“Informatics”, “m”); Output: 6 SELECT INSTR(“Informatics”, “am”); Output: 0
LTRIM(string)	Returns the given string after removing leading spaces.	SELECT LENGTH(LTRIM(“ DELHI”)); Output 5 SELECT LENGTH(LTRIM(“ DELHI ”)); Output 6
RTRIM(string)	Returns the given string after removing trailing spaces.	SELECT LENGTH(RTRIM(“DELHI ”)); Output 5 SELECT LENGTH(RTRIM(“ DELHI ”)); Output 6
TRIM(string)	Returns the given string after removing both leading and trailing spaces.	SELECT LENGTH(RTRIM(“ DELHI ”)); Output 5

Practice Questions :

Q1. Write the output of the following statements :

1. Select length(“Sagar”);
2. Select lower(“12-A”);
3. Select mid(“Database Query using SQL”, 3, 7);
4. Select instr(“Database Query using SQL”, “Q”);
5. Select left(“Database Query using SQL”, 3);
6. Select right(“Database Query using SQL”, 3);
7. Select lower(upper(“Database Query using SQL”))
8. Select length(left(“Database Query using SQL”, 7));
9. Select length(substr(“Database Query using SQL”, 4,7));
10. Select length(trim(” SQL “));

Ans.

1. 5
2. 12-a
3. tabase
4. 10
5. Dat
6. SQL
7. database query using sql
8. 7
9. 7
10. 3

3) Date and Time Functions : These functions perform operations on date and time data. Various Date and Time functions are :

Function Name	Description	Example with output
NOW()	It returns the current system date and time.	SELECT NOW(); Output: 2021-06-02 13:42:37
DATE()	It returns the date part from the given date/ time expression.	SELECT DATE(NOW()); Output: 2021-06-02
MONTH(date)	It returns the month in numeric form from the date.	SELECT MONTH(NOW()); Output: 6
MONTHNAME(date)	It returns the month name from the specified date.	SELECT MONTHNAME(“”2021-06-2”) Output: June
YEAR(date)	It returns the year from the date.	SELECT YEAR(“2021-06-2”) Output: 2021
DAY(date)	It returns the day part from the date.	SELECT DAY(“2021-06-2”) Output: 2
DAYNAME(date)	It returns the name of the day from the date	SELECT DAYNAME(“2021-06-2”) Output: Wednesday

Practice Questions :

Q1. Write the output of the following statements :

1. SELECT DAY(“2021-06-21”)
2. SELECT YEAR(“2020-06-2”)
3. SELECT MONTHNAME(“2021-05-2”)
4. SELECT MONTH(“2021-05-2”)
5. SELECT DAYNAME(DATE(NOW()))

Ans.

1. 21
2. 2020
3. May
4. 5
5. Name of Current Day

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 on which the function is applied.

Table : EMP

E_id	E_Salary
1	25000
2	20000
3	25000
4	15000
5	30000

Function Name	Description	Example with output
MAX(column)	It returns the largest value from the specified column.	Select MAX(E_Salary) from EMP; Output: 30000
MIN(column)	It returns the minimum value from the specified column.	Select MIN(E_Salary) from EMP; Output: 15000
AVG(column)	Returns the average of the values in the specified column	Select AVERAGE(E_Salary) from EMP; Output: 23000
COUNT(column)	Returns the number of values in the specified column ignoring the NULL values.	Select COUNT(E_Salary) from EMP; Output: 5
COUNT(*)	Returns the number of records in a table.	Select COUNT(*) from EMP; Output: 5

Differences between Single row and Multiple row Functions :

Single row Functions	Multiple row functions
It operates on a single row at a time.	It operates on multiple rows.
It returns one result per row	It returns one result for multiple rows.
It can be used in Select, Where, and Order by clause.	It can be used in the select clause only.
Math, String and Date functions are examples of single row functions.	Max(), Min(), Avg(), Sum(), Count() and Count(*) are examples of multiple row functions.

GROUP BY in SQL :

GROUP BY clause groups the rows together that contain the same values in a specified column. HAVING Clause in SQL is used to specify conditions on the rows with GROUP BY clause.

Consider the following table :

Table : Bank

id	Salary	Designation	Bankname	Cardtype
1	25000	Clerk	SBI	Credit
2	22000	Clerk	SIB	Debit
3	18000	Receptionist	Axis	Credit
4	50000	Manager	SIB	Credit
5	45000	Manager	Axis	Debit
6	40000	Manager	Axis	Debit
7	15000	Receptionist	SBI	Credit

Q1. Display the total salary received by each designation.

Ans. Select sum(Salary) , designation from Bank Group by designation;

OUTPUT :

sum(Salary)	designation
47000	Clerk
33000	Receptionist
135000	Manager

Q2. Display the number of people in each category of Cardtype from the table Bank.

Ans. Select Cardtype, count(Cardtype) from Bank Group By Cardtype;

Operations on Relations :

We can perform following operations on table :

1. Union
2. Intersection
3. Set Difference

1) Union (U) : This operation is used to combine the 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 Cricket and Hockey

Table : Cricket

Rollno	Name	Class
1	Amit	6
2	Anil	7
3	Sonam	8
4	Suman	7

Table : Hockey

Rollno	Name	Class
1	Amit	6
5	Sahil	7
3	Sonam	8
7	Ravi	7

UNION operation (represented by symbol U). The output of UNION operation on Cricket and Hockey table is :

Rollno	Name	Class
1	Amit	6
2	Anil	7
3	Sonam	8
4	Suman	7
5	Sahil	7
7	Ravi	7

2) INTERSECT (∩) : Intersect operation is used to get the common tuples from two tables. The output of INTERSECT operation on Cricket and Hockey table (given above) is :

Rollno	Name	Class
1	Amit	6
3	Sonam	8

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).

OUTPUT of Cricket (-) Hockey

Rollno	Name	Class
2	Anil	7
4	Suman	7

OUTPUT of Hockey (-) Cricket

Rollno	Name	Class
5	Sahil	7
7	Ravi	7

Cartesian Product :

Cartesian product combines tuples from two relations. It results in all pairs of rows from the two relations, regardless of whether or not they have the same values on common attributes.

Let us consider the two tables : Cricket and Hockey

Table : Cricket

Rollno	Name	Class
1	Amit	6
2	Anil	7
3	Sonam	8
4	Suman	7

Table : Hockey

Rollno	Name	Class
5	Sahil	7
3	Sonam	8

Cartesian Product of Cricket and Hockey (Cricket X Hockey)

Rollno	Name	Class	Rollno	Name	Class
1	Amit	6	5	Sahil	7
2	Anil	7	5	Sahil	7
3	Sonam	8	5	Sahil	7
4	Suman	7	5	Sahil	7
1	Amit	6	3	Sonam	8
2	Anil	7	3	Sonam	8
3	Sonam	8	3	Sonam	8
4	Suman	7	3	Sonam	8

NOTE : Query to produce the above result is : Select * from Cricket, Hockey;

JOIN on two tables :

JOIN operation combines tuples from two tables on specified conditions. In Joining of two tables, we specify the condition on common field. This field is the primary key in one table and foreign key in another table.

Consider the following two tables : Student and Book

Table : Student

Admno	Name	Class
1	Suman	6
2	Ravi	6
3	Sonam	7

Table : Book

Admno	B_id	Subject
1	B101	English
2	B102	Math
3	B103	Science

Q1. Write a query to join two tables Student and Book on the basis of field Admission Number.

Ans. Select * from Student, Book where Student.Admno = Book.Admno;

OR

Select * from Student Join Book on Student.Admno = Book.Admno

OR

Select * from Student Natural Join Book

Q2. Display Admission number, Student Name and Subject from table Student and Book.

Ans. Select Admno, Name, Subject from Student, Book where Student.Admno = Book.Admno;

Q3. Display Name and Class of a student who is having book of subject Math.

Ans. Select Name, Class, Subject from Student, Book where Student.Admno = Book.Admno;

Summary :

A Function is used to perform a particular task and return a value as a result.

Single row functions work on a single row to return a single value.

Multiple row functions work on a set of records as a whole and return a single value.

Numeric functions perform operations on numeric values and return numeric values.

String functions perform operations on character type values and return either character or numeric
values.

Date and time functions allow us to deal with date type data values.

GROUP BY function is used to group the rows together that contain similar values in a specified
column. Some of the group functions are COUNT, MAX, MIN, AVG and SUM.

Join is an operation which is used to combine rows from two or more tables based on one or
more common fields between them.