Category Archives: SQL

SQL MAX()

SQL MAX()  returns the largest value of the selected column.

Table – Students

+—–+——+——-+——-+——-+
| sid | name | mark1 | mark2 | mark3 |
+—–+——+——-+——-+——-+
|   1 | AAAA |    30 |    35 |    40 |
|   2 | BBBB |    36 |    37 |    38 |
|   3 | CCCC |    42 |    43 |    44 |
|   4 | DDDD |    43 |    32 |    39 |
+—–+——+——-+——-+——-+

Example

mysql> SELECT MAX(mark1) FROM students ;
+————+
| MAX(mark1) |
+————+
|         43 |
+————+
1 row in set (0.00 sec)

Share

SQL LAST()

SQL LAST() returns the last value of the selected column.

Table – Students

+—–+——+——-+——-+——-+
| sid | name | mark1 | mark2 | mark3 |
+—–+——+——-+——-+——-+
|   1 | AAAA |    30 |    35 |    40 |
|   2 | BBBB |    36 |    37 |    38 |
|   3 | CCCC |    42 |    43 |    44 |
|   4 | DDDD |    43 |    32 |    39 |
+—–+——+——-+——-+——-+

Example

SELECT LAST(*) AS total FROM students ;

Share

SQL FIRST()

SQL FIRST() returns the first value of the selected column.

Table – Students

+—–+——+——-+——-+——-+
| sid | name | mark1 | mark2 | mark3 |
+—–+——+——-+——-+——-+
|   1 | AAAA |    30 |    35 |    40 |
|   2 | BBBB |    36 |    37 |    38 |
|   3 | CCCC |    42 |    43 |    44 |
|   4 | DDDD |    43 |    32 |    39 |
+—–+——+——-+——-+——-+

Example

SELECT FIRST(*) AS total FROM students ;

Share

SQL COUNT()

SQL COUNT() returns the number of rows

Table – Students

+—–+——+——-+——-+——-+
| sid | name | mark1 | mark2 | mark3 |
+—–+——+——-+——-+——-+
|   1 | AAAA |    30 |    35 |    40 |
|   2 | BBBB |    36 |    37 |    38 |
|   3 | CCCC |    42 |    43 |    44 |
|   4 | DDDD |    43 |    32 |    39 |
+—–+——+——-+——-+——-+

Example

mysql> SELECT COUNT(*) AS total FROM students ;
+——-+
| total |
+——-+
|     4 |
+——-+
1 row in set (0.01 sec)


Share

SQL AVG()

SQL AVG() function returns the average value of a numeric column.

Table – Students
+—–+——+——-+——-+——-+
| sid | name | mark1 | mark2 | mark3 |
+—–+——+——-+——-+——-+
|   1 | AAAA |    30 |    35 |    40 |
|   2 | BBBB |    36 |    37 |    38 |
|   3 | CCCC |    42 |    43 |    44 |
|   4 | DDDD |    43 |    32 |    39 |
+—–+——+——-+——-+——-+

Example

mysql> SELECT AVG(mark1) FROM students ;
+————+
| AVG(mark1) |
+————+
|    37.7500 |
+————+
1 row in set (0.02 sec)


Share

SQL Functions

Below given the SQL functions

  • SQL NOW()– Returns the current date and time
  • SQL CURDATE()– Returns the current date
  • SQL CURTIME()– Returns the current time
  • SQL DATE()– Extracts the date part of a date or date/time expression
  • SQL EXTRACT()- Returns a single part of a date/time
  • SQL DATE_ADD()– Adds a specified time interval to a date
  • SQL DATE_SUB() -Subtracts a specified time interval from a date
  • SQL DATEDIFF()– Returns the number of days between two dates
  • SQL DATE_FORMAT()– Displays date/time data in different formats
  • SQL NULL Functions – We can use these function to manage the null values
  • SQL avg() – returns the average value of a numeric column
  • SQL count() –  returns the number of rows
  • SQL first() –  returns the first value of the selected column.
  • SQL last() –  returns the last value of the selected column.
  • SQL max() – returns the largest value of the selected column.
  • SQL min() –  returns the smallest value of the selected column.
  • SQL sum() – returns the total sum of a numeric column.
  • SQL Group By – should be added with all aggregate functions
  • SQL ucase() –   converts the value of a field to uppercase.
  • SQL lcase() –  converts the value of a field to lowercase.
  • SQL mid() –  used to extract characters from a text field
  • SQL len() – returns the length of the value in a text field.
  • SQL round() –  used to round a numeric field to the number of decimals specified.
Share

SQL NULL Functions

Below given are the SQL NULL functions

ISNULL()
NVL()
IFNULL()
COALESCE()

We can use these function to manage the null values .

mysql> SELECT 1+IFNULL(age,0) as age  FROM users ;
+—–+
| age |
+—–+
|   1 |
|   1 |
|   1 |
|   1 |
|   1 |
+—–+
5 rows in set (0.00 sec)

Share

SQL NULL

SQL NULL values represent missing unknown data.

A table column can hold NULL values.

+—–+——+——-+
| uid | name | Email |
+—–+——+——-+
|   1 | AAAA |       |
|   2 | BBBB |       |
|   3 | CCCC |       |
|   4 | DDDD |       |
|   5 | EEEE |       |
+—–+——+——-+

Share

SQL DATE_FORMAT()

SQL DATE_FORMAT() is used to display date/time data in different formats.

Example

mysql> SELECT DATE_FORMAT(NOW(),’%Y-%m-%D’);
+——————————-+
| DATE_FORMAT(NOW(),’%Y-%m-%D’) |
+——————————-+
| 2007-05-30th                  |
+——————————-+
1 row in set (0.00 sec)

Different formats

%a Abbreviated weekday name
%b Abbreviated month name
%c Month, numeric
%D Day of month with English suffix
%d Day of month, numeric (00-31)
%e Day of month, numeric (0-31)
%f Microseconds
%H Hour (00-23)
%h Hour (01-12)
%I Hour (01-12)
%i Minutes, numeric (00-59)
%j Day of year (001-366)
%k Hour (0-23)
%l Hour (1-12)
%M Month name
%m Month, numeric (00-12)
%p AM or PM
%r Time, 12-hour (hh:mm:ss AM or PM)
%S Seconds (00-59)
%s Seconds (00-59)
%T Time, 24-hour (hh:mm:ss)
%U Week (00-53) where Sunday is the first day of week
%u Week (00-53) where Monday is the first day of week
%V Week (01-53) where Sunday is the first day of week, used with %X
%v Week (01-53) where Monday is the first day of week, used with %x
%W Weekday name
%w Day of the week (0=Sunday, 6=Saturday)
%X Year of the week where Sunday is the first day of week, four digits, used with %V
%x Year of the week where Monday is the first day of week, four digits, used with %v
%Y Year, four digits
%y Year, two digits

Share