(SQL Tutorials for Citizen Data Scientist)
SQL Functions
SQL has many built-in functions that enable you to perform calculations on data.
SQL Aggregate Functions
SQL aggregate functions perform calculation on a set of values and return a single value. The following table summarizes some useful aggregate functions:
| Function | Description |
|---|---|
AVG() |
Returns the average of values |
SUM() |
Returns the sum of values |
COUNT() |
Returns the number of rows in a result set |
MAX() |
Returns the maximum value |
MIN() |
Returns the minimum value |
SQL String Functions
SQL string functions perform operation on a string input value and return a string or numeric value. The following table summarizes some useful string functions:
| Function | Description |
|---|---|
CONCAT() |
Returns a string by concatenating two or more string values. |
CONCAT_WS() |
Returns a string by concatenating two or more string values with a separator. |
FORMAT() |
Returns a value formatted with the specified format. |
LOWER() |
Converts a string to lowercase. |
UPPER() |
Converts a string to uppercase. |
TRIM() |
Remove leading and trailing spaces from a string. |
REVERSE() |
Returns the reverse order of a string value. |
SUBSTRING() |
Returns a substring from string. |
SQL Date Functions
Date functions are used to manipulate or perform operation on date values.
MySQL Date Functions
The following table summarizes some most important MySQL’s built-in date functions:
| Function | Description |
|---|---|
NOW() |
Returns the current date and time. |
CURDATE() |
Returns the current date. |
CURTIME() |
Returns the current time |
DATE() |
Extract the date part of a date or datetime expression. |
DAY() |
Returns the day of the month (0-31). |
DAYNAME() |
Returns the name of the weekday. |
MONTH() |
Returns the month from the date passed (1-12). |
MONTHNAME() |
Returns the name of the month. |
YEAR() |
Returns the year. |
DATE_FORMAT() |
Displays date and time value in other formats. |
EXTRACT() |
Extract part of a date. |
DATE_ADD() |
Adds a specified time value (or interval) to a date value. |
DATE_SUB() |
Subtracts a specified time value (or interval) from a date value. |
DATEDIFF() |
Returns the number of days between two dates |
Note: The date and time returned by these functions is the date and time of the computer on which the instance of database server is running.
SQL Server Date Functions
The following table summarizes some most important SQL Server’s built-in date functions:
| Function | Description |
|---|---|
GETDATE() |
Returns the current date and time. |
DATEPART() |
Returns the specified datepart of the specified date e.g. DATEPART(year,'2016-10-25') return 2016. |
DAY() |
Returns the day of the month (0-31). |
MONTH() |
Returns the month from the specified date (0-12). |
YEAR() |
Returns the year from the specified date. |
DATEADD() |
Adds or subtracts a specified time interval from a date. |
DATEDIFF() |
Returns the date or time between two specified dates. |
CONVERT() |
Displays date and time value in other formats. |
Excel formula for Beginners – How to Filter data between dates in Excel
Disclaimer: The information and code presented within this recipe/tutorial is only for educational and coaching purposes for beginners and developers. Anyone can practice and apply the recipe/tutorial presented here, but the reader is taking full responsibility for his/her actions. The author (content curator) of this recipe (code / program) has made every effort to ensure the accuracy of the information was correct at time of publication. The author (content curator) does not assume and hereby disclaims any liability to any party for any loss, damage, or disruption caused by errors or omissions, whether such errors or omissions result from accident, negligence, or any other cause. The information presented here could also be found in public knowledge domains.
Learn by Coding: v-Tutorials on Applied Machine Learning and Data Science for Beginners
Latest end-to-end Learn by Coding Projects (Jupyter Notebooks) in Python and R:
All Notebooks in One Bundle: Data Science Recipes and Examples in Python & R.
End-to-End Python Machine Learning Recipes & Examples.
End-to-End R Machine Learning Recipes & Examples.
Applied Statistics with R for Beginners and Business Professionals
Data Science and Machine Learning Projects in Python: Tabular Data Analytics
Data Science and Machine Learning Projects in R: Tabular Data Analytics
Python Machine Learning & Data Science Recipes: Learn by Coding
R Machine Learning & Data Science Recipes: Learn by Coding
Comparing Different Machine Learning Algorithms in Python for Classification (FREE)
There are 2000+ End-to-End Python & R Notebooks are available to build Professional Portfolio as a Data Scientist and/or Machine Learning Specialist. All Notebooks are only $29.95. We would like to request you to have a look at the website for FREE the end-to-end notebooks, and then decide whether you would like to purchase or not.