AVG() ans: SELECT AVG(COLUMN NAME) FROM TABLE_NAME
Using it in WHERE CLAUSE so a different form of presentation:
TO LIST ONLY THOSE PRODUCTS AND PRICES WHERE THE AVERAGE IS MORE THAN A SPECIFIC AMOUNT:-
SELECT Produc
...
AVG() ans: SELECT AVG(COLUMN NAME) FROM TABLE_NAME
Using it in WHERE CLAUSE so a different form of presentation:
TO LIST ONLY THOSE PRODUCTS AND PRICES WHERE THE AVERAGE IS MORE THAN A SPECIFIC AMOUNT:-
SELECT ProductName, Price FROM Products
WHERE Price>(SELECT AVG(Price) FROM Products);
COUNT() ans: SELECT COUNT(*) FROM TABLE_NAME
(to select all the NON null values of records in the table
SELECT COUNT(Column Name) FROM TABLE_NAME
(to count all values in a column)
SELECT COUNT(Distinct Column Name) FROM TABLE_NAME
(to count all distinct values in a column)
SELECT COUNT(Distinct Column Name) as NEWNAME FROM TABLE_NAME
(to count all distinct values in a column and label the column with a new name)
COUNT DISTINCT VALUES ans: SQL COUNT(DISTINCT column_name) Syntax
SELECT COUNT(DISTINCT COLUMN NAME) FROM TABLE_NAME
*** Distinct command doesn't work in MS access.Works in SQL and Oracle)***
FIRST() ans: To return the first value of a column.
SELECT FIRST(column_name) FROM table_name;
***ONLY IN ACCESS***
Last() ans: To return the last value of a column
SELECT LASt(column_name) FROM table_name;
AGGREGATE FUNCTIONS ans: •AVG() - Returns the average value
•COUNT() - Returns the number of rows
•FIRST() - Returns the first value
•LAST() - Returns the last value
•MAX() - Returns the largest value
•MIN() - Returns the smallest value
•SUM() - Returns the sum
Scalar Functions ans: •UCASE() - Converts a field to upper case
•LCASE() - Converts a field to lower case
•MID() - Extract characters from a text field
•LEN() - Returns the length of a text field
•ROUND() - Rounds a numeric field to the number of decimals specified
•NOW() - Returns the current system date and time
•FORMAT() - Formats how a field is to be displayed
[Show More]
