SQL Relation Class 12 Informatics Practices Notes

SQL Relation Class 12 Informatics Practices Notes And Questions

CBSE Notes Class 12

Refer to SQL Relation Class 12 Informatics Practices Notes and Questions below. These revision notes and solved important questions for SQL Relation have been prepared as per the latest syllabus for class 12 Informatics Practices issued for current academic year. Students should go through these Class 12 Informatics Practices Notes as they will help them to revise all important concepts prior to their examinations

SQL Relation Class 12 Informatics Practices Notes

MySQL Order By

MySQL Order By clause is used to sort the table data in either Ascending order or Descending order. By default, data is not inserted into Tables in any order unless we have an index.

So, If we want to retrieve the data in any particular order, we have to sort it by using MySQL Order By statement.
Syntax:-SELECT Column_Names
FROM Table_Name
ORDER BY {Column1}[ASC | DESC] {Column2}[ASC | DESC]

MySQL Order by– e.g.
Suppose we are having student table with following data.

SQL Relation Class 12 Informatics Practices Notes And Questions

Now we write the query – select * from student order by class;

SQL Relation Class 12 Informatics Practices Notes And Questions

Query result will be in ascending order of class.If we not specify asc/desc in query then ascending clause is applied by default

MySQL Order by– e.g.
Suppose we are having student table with following data.

SQL Relation Class 12 Informatics Practices Notes And Questions

Now we write the query – select * from student order by class desc;

SQL Relation Class 12 Informatics Practices Notes And Questions

Query result will be in descending order of class

MySQL Order by – e.g.
Suppose we are having student table with following data.

SQL Relation Class 12 Informatics Practices Notes And Questions

Now we write query–select * from student order by class asc, marks asc;

SQL Relation Class 12 Informatics Practices Notes And Questions

Query result will be ascending order of class and if same class exists then ordering will done on marks column(ascending order)

MySQL Order by– e.g.
Suppose we are having student table with following data.

SQL Relation Class 12 Informatics Practices Notes And Questions

Now we write query–select * from student order by class asc, marks desc;

SQL Relation Class 12 Informatics Practices Notes And Questions

Query result will be ascending order of class and if same class exists then ordering will done on marks column(descending order)

The GROUP BY clause groups a set of rows/records into a set of summary rows/records by values of columns or expressions. It returns one row for each group.

We often use the GROUP BY clause with aggregate functions such as SUM, AVG, MAX, MIN, and COUNT. The aggregate function that appears in the SELECT clause provides information about each group.

The GROUP BY clause is an optional clause of the SELECT statement.
Syntax –
SELECT 1, c2,…, cn, aggregate_function(ci)
FROM table WHERE where_conditions GROUP BY c1 , c2,…,cn;
Here c1,c2,ci,cn are column name

MySQL group by – e.g.
Suppose we are having student table with following data.

SQL Relation Class 12 Informatics Practices Notes And Questions

Now we write query–select class from student group by class;

SQL Relation Class 12 Informatics Practices Notes And Questions

Query result will be unique occurrences of class values,just similar to use distinct clause like(select distinct class from student).

MySQL GROUP BY with aggregate functions
The aggregate functions allow us to perform the calculation of a set of rows and return a single value. The GROUP BY clause is often used with an aggregate function to perform calculation and return a single value for each subgroup.
For example, if we want to know the number of student in each class, you can use the COUNT function with the GROUP BY clause as follows:Suppose we are having student table with following data.

SQL Relation Class 12 Informatics Practices Notes And Questions

Now we write query–select class,count(*) from student group by class;

SQL Relation Class 12 Informatics Practices Notes And Questions

Query result will be unique occurrences of class values along with counting of students(records) of each class(sub group).

MySQL GROUP BY with aggregate functions
we are having student table with following data.

SQL Relation Class 12 Informatics Practices Notes And Questions

Now we write query–select class,avg(marks) from student group by class;

Query result will be   unique occurrences of class values along with average marks of each class(sub group).

MySQL GROUP BY with aggregate functions (with where and order by clause) we are having student table with following data.

Now we write query–select class,avg(marks) from student where class<10 group by class order by marks desc;

Query result will be  unique occurrences of class values where class<10 along with average marks of each class(sub group) and descending ofer of marks.

MySQL Group by with Having

The HAVING clause is used in the SELECT statement to specify filter conditions for a group of rows or aggregates. The HAVING clause is often used with the GROUP BY clause to filter groups based on a specified condition. To filter the groups returned by GROUP BY clause, we use a HAVING clause. WHERE is applied before GROUP BY, HAVING is applied after (and can filter on aggregates).

MySQL GROUP BY with aggregate functions we are having student table with following data.

Now we write query–select class,avg(marks) from student group by class having avg(marks)<90;

Query result will be   unique occurrences of class values along with average marks of each class(sub group) and each class having average marks<90.

MySQL GROUP BY with aggregate functions
we are having student table with following data.

Now we write query–select class,avg(marks) from student group by class having count(*)<3;

Query result will be unique occurrences of class values along with average marks of each class(sub group) and each class having less than 3 rows.

SQL Relation Class 12 Informatics Practices Notes