Class 12 IP Database Query Using SQL Important NCERT Notes

Share with others

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
Database Query using SQL
Database Query using SQL

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

Function NameDescriptionExample 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
Database Query using SQL

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 NameDescriptionExample with output
UCASE(string)
OR
UPPER(string)
Converts string into uppercaseSELECT UCASE(“Informatics”);

Output:
INFORMATICS

SELECT UCASE(“MySQL”);

Output:
MYSQL

LOWER(string)
OR
LCASE(string)
Converts string into lowercaseSELECT 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, substringReturns 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
7
RTRIM(string)Returns the given string after
removing trailing spaces.
SELECT LENGTH(RTRIM(“DELHI ”));

Output
5

SELECT LENGTH(RTRIM(“ DELHI ”));

Output
7
TRIM(string)Returns the given string after
removing both leading and trailing
spaces.
SELECT LENGTH(RTRIM(“ DELHI ”));

Output
5
Database Query using SQL

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 NameDescriptionExample 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
Database Query using SQL

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_idE_Salary
125000
220000
325000
415000
530000
Database Query using SQL
Function NameDescriptionExample 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
Database Query using SQL

Differences between Single row and Multiple row Functions :

Single row FunctionsMultiple row functions
It operates on a single row at a time.It operates on multiple rows.
It returns one result per rowIt 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.
Database Query using SQL

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

idSalaryDesignationBanknameCardtype
125000ClerkSBICredit
222000ClerkSIBDebit
318000ReceptionistAxisCredit
450000ManagerSIBCredit
545000ManagerAxisDebit
640000ManagerAxisDebit
715000ReceptionistSBICredit
Database Query using SQL

Q1. Display the total salary received by each designation.

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

OUTPUT :

sum(Salary)designation
47000Clerk
33000Receptionist
135000Manager
Database Query using SQL

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

RollnoNameClass
1Amit6
2Anil7
3Sonam8
4Suman7

Table : Hockey

RollnoNameClass
1Amit6
5Sahil7
3Sonam8
7Ravi7

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

RollnoNameClass
1Amit6
2Anil7
3Sonam8
4Suman7
5Sahil7
7Ravi7
Cricket (U) Hockey

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 :

RollnoNameClass
1Amit6
3Sonam8
Cricket (∩) Hockey

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

RollnoNameClass
2Anil7
4Suman7

OUTPUT of Hockey (-) Cricket

RollnoNameClass
5Sahil7
7Ravi7
Hockey (-) Cricket

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

RollnoNameClass
1Amit6
2Anil7
3Sonam8
4Suman7

Table : Hockey

RollnoNameClass
5Sahil7
3Sonam8

Cartesian Product of Cricket and Hockey (Cricket X Hockey)

RollnoNameClassRollnoNameClass
1Amit65Sahil7
2Anil75Sahil7
3Sonam85Sahil7
4Suman75Sahil7
1Amit63Sonam8
2Anil73Sonam8
3Sonam83Sonam8
4Suman73Sonam8
Cricket X Hockey

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

AdmnoNameClass
1Suman6
2Ravi6
3Sonam7

Table : Book

AdmnoB_idSubject
1B101English
2B102Math
3B103Science

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.

Class 12 Computer Science Sample Paper 2020-2021.

Class 12 Computer Science Sample Paper Marking Scheme

Class 12 Computer Science Test Series


Share with others

Leave a Reply

%d bloggers like this: