Important Mysql Function notes for class 12 informatics practices with python

What is a Function? A Function is a predefined command set that performs some special operations and returns a Single value. Functions operate on zero, one or more values that are provided to them, these values are called Parameters or Arguments. In MySQL functions have been categorized into : 

Single Row Functions:

These are also known as Scalar functions. Single row functions are applied on a single value and return a single value. 

(A) Numeric Functions Three commonly used numeric functions are POWER() / POW(), ROUND() and MOD(), truncate().

String Functions String functions can perform various operations on alphanumeric data which are stored in a table. They can be used to change the case (uppercase to lowercase or vice-versa), extract a substring, calculate the length of a string and so on.

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.

Date and Time Functions There are various functions that are used to perform operations on date and time data. Some of the operations include displaying the current date, extracting each element of a date (day, month and year), displaying day of the week and so on.

GROUP BY in SQL At times we need to fetch a group of rows on the basis of common values in a column. This can be done using a GROUP BY clause. It groups the rows together that contain the same values in a specified column. We can use the aggregate functions (COUNT, MAX, MIN, AVG and SUM) to work on the grouped values. HAVING Clause in SQL is used to specify conditions on the rows with GROUP BY clause.

SYNTAX

SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) ORDER BY column_name(s);

Difference between Where and Clause in SQL

Where Clause in SQL	Having Clause in SQL
Filter table based data to specific condition	Group based data under set condition
Applicable without GROUP BY clause	Does not function without GROUP BY clause
Applied before GROUP BY clause	Used after GROUP BY clause
Used with single row operations such as Upper, Lower and so on	Applicable with multiple row functions such as Sum, count and so on

Operations on Relations 

We can perform certain operations on relations like Union, Intersection, and Set Difference to merge the tuples of two tables. These three operations are binary operations as they work upon two tables. Note here, that these operations can only be applied if both the relations have the same number of attributes, and corresponding attributes in both tables have the same domain.  

UNION (U) This operation is used to combine the selected 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.

 INTERSECT (∩) Intersect operation is used to get the common tuples from two tables and is represented by the symbol ∩. 

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

Cartesian Product Cartesian product operation combines tuples from two relations. It results in all pairs of rows from the two input relations, regardless of whether or not they have the same values on common attributes. It is denoted as ‘X’.

QUERY 

mysql> SELECT * FROM DANCE, MUSIC;

mysql> SELECT * FROM DANCE D, MUSIC M  WHERE D.Name = M.Name; 

Source: content and some image taken from ncert class 12 ip book. 

searches ip class 12 handwritten notes societal impact class 12 ip notes pdf informatics practices class 12 informatics practices class 12 book pdf introduction to computer networks class 12 ip notes informatics practices with python class 12 pdf ip notes class 11 introduction to computer networks class 12 ip notes mysql class 12 notes pdf mysql notes class 12 ip querying and sql functions class 12 notes class 12 ip mysql practical questions with answers class 12 ip sql notes pdf querying and sql functions class 12 pdf database query using sql class 12 questions and answers mysql functions class 12 questions and answers