SQL functions
SQL has many built-in functions that are used to perform various operations on existing data of a table. These functions are grouped together into two categories:
1. Aggregate Functions
Aggregate functions are used to perform operations on a set of values of different rows which returns a single value. These are often used along with the GROUP BY and HAVING clauses of the SELECT statement.
Syntax:
SELECT aggregate_function (column_name)
FROM table_name
WHERE condition;
Following are the commonly used aggregate functions:
COUNT()
The COUNT() function returns the number of values based on specific condition.
Syntax:
SELECT COUNT (column_name)
FROM table_name
WHERE condition;
In case where a column contains duplicate or unique values, to return number of distinct values, use the below form of query:
SELECT COUNT (DISTINCT column_name)
FROM table_name;
COUNT (*) is a special implementation of the COUNT() function which returns the total number of rows in a table. Asterisk (*) specifies entire records of a table if condition is omitted. It also returns entire values based on specified condition by the "WHERE" Clause.
AVG()
The AVG() function returns average of the input values.
Syntax:
SELECT AVG (column_name)
FROM table_name
WHERE condition;
SUM()
The SUM() function returns the total sum of the input values.
Syntax:
SELECT SUM (column_name)
FROM table_name
WHERE condition;
MIN()
The MIN() function returns the smallest value of the provided input values or of the specified column.
Syntax:
SELECT MIN (column_name)
FROM table_name
WHERE condition;
MAX()
The MAX() function returns the largest value of the provided input values or of the specified column.
Syntax:
SELECT MAX (column_name)
FROM table_name
WHERE condition;
FIRST()
The FIRST() function returns the first value of specific column.
Syntax:
SELECT FIRST (column_name)
FROM table_name;
LAST()
The LAST() function returns the last value of specific column.
Syntax:
SELECT LAST (column_name)
FROM table_name;
Now, let's understand the concept of aggregate functions by solving below questions. Consider the below table named "Shapes" and write SQL queries based on given data.
Q 1. Count total number of records from table "Shapes".
Ans- SELCT COUNT (*) FROM Shapes;
Q 2. Count the number of names of 6-faced shapes.
Ans- SELECT COUNT (NAMES) FROM Shapes
WHERE FACES = 6;
Q 3. Find the average of vertices.
Ans- SELECT AVG (VERTICES)
FROM Shapes;
Q 4. Find the average of vertices whose faces are more than 3.
Ans- SELECT AVG (VERTICES)
FROM Shapes
WHERE FACES > 3;
Q 5. Find the minimum vertex from table "Shapes".
Ans- SELECT MIN (VERTICES)
FROM Shapes;
Q 6. Find maximum edge from table "Shapes".
Ans- SELECT MAX (EDGES)
FROM Shapes;
Q 7. Select the name of the first geometrical shape.
Ans- SELECT FIRST (NAMES) FROM Shapes;
Q 8. Select the name of the last geometrical shape.
Ans- SELECT LAST (NAMES) FROM Shapes;
Q 9. Count the numbers of distinct vertices from table "Shapes".
Ans- SELECT COUNT (DISTINCT VERTICES) FROM Shapes;
Q 10. Calculate the summation of total faces from the "Shapes" table.
Ans- SELECT SUM (FACES) FROM Shapes;
Q 11. Calculate the total summation of distinct faces from the "Shapes" table.
Ans- SELECT SUM (DISTINCT FACES) FROM Shapes;
2. Scalar Functions
Scalar functions return values based on specific conditions. The different scalar functions are:
UCASE()
The UCASE() function converts the values of specific field to uppercase.
Syntax:
SELECT UCASE (column_name)
FROM table_name;
LCASE()
The LCASE() function converts the value of specific field to lowercase.
Syntax:
SELECT LCASE (column_name)
FROM table_name;
MID()
The MID() function extracts characters from columns with string data type.
Syntax:
SELECT MID (column_name, start, length)
FROM table_name;
Explanation:
Start specifies the strating position. Length specifies the number of characters to be returned. Length is optional and if omitted, the rest of the text is returned.
LEN()
The LEN() function retrieves the lenth of input string.
Syntax:
SELECT LENGTH (column_name)
FROM table_name;
ROUND()
The ROUND() function rounds off the numeric value to the specified number of decimals.
Syntax:
SELECT ROUND (column_name, decimals)
FROM table_name;
Decimals implies the number of decimals we want to fetch.
NOW()
The NOW() function returns the current system date and time in the form of "YYYY-MM-DD HH-MM-SS".
Syntax:
SELECT NOW() FROM table_name;
(FORMAT)
The FORMAT() functions returns a value in specific format in which it is to be displayed.
Syntax:
SELECT FORMAT (input_value, format);
Consider the above table named as "Shapes". Write SQL queries based on given questions.
Q 1. Convert the names of different shapes in uppercase of table "Shapes".
Ans- SELECT UCASE (NAMES) FROM Shapes;
Q 2. Convert the names of different shapes in lowercase of table "Shapes".
Ans- SELECT LCASE (NAMES) FROM Shapes;
Q 3. Extract three characters of the names starting from the 2nd character.
Ans- SELECT MID (NAMES, 2, 3) FROM Shapes.
Q 4. Find the length of characters of the names.
Ans- SELECT LENGTH (NAMES) FROM Shapes;
Q 5. Find the current system date and time.
And- SELECT NOW() FROM Shapes;
Q 6. Format current system date as 'YYYY_MM_DD'.
Ans- SELECT FORMAT (NOW(), 'YYYY_MM_DD' FROM Shapes.
0 Comments