# Class 12 IP Database Query Using SQL Important NCERT Notes

Share with others

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

### 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:

### 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 :

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

# 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

Q1. Display the total salary received by each designation.

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

OUTPUT :

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

Table : Hockey

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

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 :

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

OUTPUT of 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

Table : Hockey

Cartesian Product of Cricket and Hockey (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 : Book

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

OR

OR

Select * from Student Natural Join Book

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

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
error: Content is protected !!