CBSE CS and IP

CBSE Class 11 & 12 Computer Science and Informatics Practices Python Materials, Video Lecture

Showing posts with label MySQL. Show all posts
Showing posts with label MySQL. Show all posts

MYSQL ORDER BY

Class 12 CS , Class 12 IP
MYSQL ORDER BY

 

order by

MYSQL ORDER BY clause

When you use the SELECT statement to query data from a table, the result set is not ordered. It means that the rows in the result set can be in any order.

To sort the result set, you add the ORDER BY clause to the SELECT statement. 
The SQL ORDER BY clause is used to sort the records in the result set for a SELECT statement.

The syntax for using ORDER BY -:

SELECT expressions
FROM tables
[WHERE conditions]
[GROUP BY <COL_NAME>
HAVING <CONDITION ON GROUPS>]
ORDER BY COL1,COL2,.. [ ASC | DESC ];

ASC –:
 Ascending order by COLUMNS [Default]
DESC – :
Descending order by COLUMNS

Note -:

ORDER BY clause is always evaluated after the FROM and SELECT clause.





Read more »
By at 1 comment:
Labels: , ,

MYSQL GROUP BY and HAVING

Class 12 CS , Class 12 IP
MYSQL GROUP BY and HAVING



 MySQL  GROUP BY

The GROUP BY statement in SQL is used to arrange data into groups with the help of some functions. GROUP BY statement is used to retrieve grouped records based on one or more columns.

The aggregate function on SELECT Statement can be used with the GROUP BY Statement.
Aggregate Functions : AVG(),COUNT(),MAX(),MIN(),SUM().

The syntax for GROUP BY Statement in SQL -:

SELECT  <COL_NAME>
FROM  <TABLE>
WHERE  <CONDITION>
GROUP BY <COL_NAME>
HAVING <CONDITION ON GROUPS>;

Description MySQL GROUP BY -:

The GROUP BY clause combines all those records (Rows) that have identical values in a particular field (Column) or a group of fields.
This grouping results in one summary record per group.

If you want to total salary for all employees then we used the SUM function and you will get a sum of the total salary.
group by

If you want to total salary department wise like D1 or D2 then we have used GROUP BY.
group by MYSQL

Now you have also used the SUM function and using GROUP BY  tHen you will get the department-wise total salary  You can see all D1 and D2 columns combine in one row.
group by MYSQL

GROUP BY(Nested Grouping) using more than one column -

If you want to GROUP BY using more than one column, you can give all columns by using comma separation.  

Syntex -

SELECT Col1, Col2,AGG_FUN(Col3)
FROM Tab1
[WHERE <Condition>]
GROUP BY Col1, Col2 ;

group by MYSQL Nested grouping

GROUP BY Condition -

The GROUP BY clause is used with Aggregate Functions.
 All the fields (Columns) other than used in aggregate function should be put in GROUP BY clause.

Syntex -

SELECT AGG_FUN(Col1), Col2, Col3
FROM Tab1
WHERE <Condition>
GROUP BY Col2, Col3;










Read more »
By at No comments:
Labels: , ,

MySQL Aggregate Functions

Class 12 IP , MySQL
MySQL Aggregate Functions

 

MySQL Aggregate Functions

An aggregate function performs a calculation on multiple values and returns a single value.


1. MAX ()

MAX function returns the maximum value of an expression.

Syntax:
SELECT MAX(aggregate_expression)
FROM tables
[WHERE conditions];

Parameter or Arguments:
aggregate_expression: This is the column or expression from which the maximum value will be returned.

2. MIN ()

MIN function returns the minimum value of an expression.

Syntax:
SELECT MIN(aggregate_expression)
FROM tables
[WHERE conditions];

Parameter or Arguments:
aggregate_expression: This is the column or expression from which the minimum value will be returned.

3. AVG ()

AVG function returns the average value of an expression.

Syntax:
SELECT AVG(aggregate_expression)
FROM tables
[WHERE conditions];

Parameter or Arguments:
aggregate_expression: This is the column or expression that will be averaged.


4. SUM ()

SUM function returns the summed value of an expression.

Syntax:
SELECT SUM(aggregate_expression)
FROM tables
[WHERE conditions];

Parameter or Arguments:
aggregate_expression: This is the column or expression that will be summed.

5. COUNT () and COUNT (*)

COUNT function returns the count of an expression.

Syntax:
SELECT COUNT(aggregate_expression)
FROM tables
[WHERE conditions];

Parameter or Arguments:
aggregate_expression: This is the column or expression whose non-null values will be counted.




Read more »
By at No comments:
Labels: ,

MySQL Date Functions

Class 12 IP , MySQL
MySQL Date Functions

 

MySQL Date Functions

For preforming the operations on dates, MySQL has provided some function:

1. NOW ()

NOW function returns the current date and time.

Syntax:
NOW( )

2. DATE ()

DATE function extracts the date value from a date or datetime expression.

Syntax:
DATE( expression )

Parameter or Arguments:
expression: The date or datetime value from which the date should be extracted.

3. MONTH ()

MONTH function returns the month portion of a date value.

Syntax:
MONTH( date_value )

Parameter or Arguments:
date_value: A date or datetime value from which to extract the month.

4. MONTHNAME ()

MONTHNAME function returns the full name of the month for a date.

Syntax:
MONTHNAME( date_value )

Parameter or Arguments:
date_value: A date or datetime value from which to extract the full month name.

5. YEAR ()

YEAR function returns the year portion of a date value.

Syntax:
YEAR( date_value )

Parameter or Arguments:
date_value: A date or datetime value from which to extract the year.

6. DAY ()

DAY function returns the day portion of a date value.

Syntax:
DAY( date_value )

Parameter or Arguments:
date_value: The date or datetime value from which to extract the day.

7. DAYNAME ()

DAYNAME function returns the weekday name for a date.

Syntax:
DAYNAME( date_value )

Parameter or Arguments:
date_value: The date or datetime value from which to extract the weekday name.



Read more »
By at No comments:
Labels: ,

MySQL Text Function

Class 12 IP , MySQL
MySQL Text Function

 

MySQL Text Function

For preforming the operations on strings, MySQL has provided some function:

1. UCASE ()/UPPER () 

It converts all characters in the specified string to uppercase. If there are characters in the string that are not letters, they are unaffected by this function.

Syntax:
UCASE( string )

Parameter or Arguments:
string: The string to convert to uppercase.

2. LCASE ()/LOWER ()

It converts all characters in the specified string to lowercase. If there are characters in the string that are not letters, they are unaffected by this function.

Syntax:
LCASE( string )

Parameter or Arguments:
string: The string to convert to lowercase.

3. MID ()/SUBSTRING ()/SUBSTR () 

This function allows you to extract a substring from a string.

Syntax:
MID( string, start_position, [length] )
SUBSTR( string, start_position, [ length ] )
SUBSTRING( string, start_position, [ length ] )

Parameter or Arguments:
string: The string from which Substring is required.
start_position: The position to begin extraction. The first position in the string is always 1.
Length: The number of characters to extract

4. LENGTH ()

This function returns the length of the specified string (measured in bytes).

Syntax:
LENGTH( string )

Parameter or Arguments:
string: The string to return the length for.

5. LEFT ()

LEFT function allows you to extract a substring from a string, starting from the left-most character.

Syntax:
LEFT( string, number_of_characters )

Parameter or Arguments:
string: The string that you wish to extract from.
number_of_characters: The number of characters that you wish to extract from a string starting from the left-most character.

6. RIGHT ()

The RIGHT function allows you to extract a substring from a string, starting from the right-most character.

Syntax:
RIGHT( string, number_of_characters )

Parameter or Arguments:
string: The string that you wish to extract from.
number_of_characters: The number of characters that you wish to extract from a string starting from the right-most character.

7. INSTR ()

INSTR function returns the location of a substring in a string.

Syntax:
INSTR( string, substring )

Parameter or Arguments:
string: The string to search.
substring: The substring to search for in string.

8. LTRIM ()

LTRIM function removes all space characters from the left-hand side of a string.

Syntax:
LTRIM( string )

Parameter or Arguments:
string: The string to trim the space characters from the left-hand side.

9. RTRIM ()

RTRIM function removes all space characters from the right-hand side of a string.

Syntax:
RTRIM( string )

Parameter or Arguments:
string: The string to trim the space characters from the right-hand side.

10. TRIM ()

TRIM function removes all specified characters either from the beginning or the end of a string.

Syntax:
TRIM( [ LEADING | TRAILING | BOTH ] [ trim_character FROM ] string )

Parameter or Arguments:
LEADING: Optional. Removes the trim_character from the front of the string.
TRAILING: Optional. Removes the trim_character from the end of the string.
BOTH: Optional. Removes the trim_character from the front and end of string.
trim_character: Optional. The character that will be removed from the string. If this parameter is omitted, it will remove space characters from the string.
string: The string to trim.





Read more »
By at No comments:
Labels: ,