SQL MIN() Function

The MIN() Function The MIN() function returns the smallest value of the selected column. SQL MIN() Syntax SELECT MIN(column_name) FROM table_name; SQL MIN() Example The following SQL statement gets the smallest value of the “Price” column from the “Products” table: Example SELECT MIN(Price) AS SmallestOrderPrice FROM Products;

Read More

Posted in SQL Tagged Comments Off on SQL MIN() Function
SQL MAX() Function

The MAX() Function The MAX() function returns the largest value of the selected column. SQL MAX() Syntax SELECT MAX(column_name) FROM table_name; SQL MAX() Example The following SQL statement gets the largest value of the “Price” column from the “Products” table: Example SELECT MAX(Price) AS HighestPrice FROM Products;

Read More

Posted in SQL Tagged Comments Off on SQL MAX() Function
SQL LAST() Function

The LAST() Function The LAST() function returns the last value of the selected column. SQL LAST() Syntax SELECT LAST(column_name) FROM table_name; Note: The LAST() function is only supported in MS Access. SQL LAST() Workaround in SQL Server, MySQL and Oracle SQL Server Syntax SELECT TOP 1 column_name FROM table_name ORDER BY column_name DESC; Example SELECT TOP 1 CustomerName FROM Customers ORDER BY CustomerID DESC; MySQL Syntax SELECT column_name FROM table_name ORDER BY column_name DESC LIMIT 1; Example SELECT CustomerName FROM Customers ORDER BY CustomerID DESC LIMIT 1; Oracle Syntax SELECT column_name FROM table_name…

Read More

Posted in SQL Tagged Comments Off on SQL LAST() Function
SQL FIRST() Function

The FIRST() Function The FIRST() function returns the first value of the selected column. SQL FIRST() Syntax SELECT FIRST(column_name) FROM table_name; Note: The FIRST() function is only supported in MS Access. SQL FIRST() Workaround in SQL Server, MySQL and Oracle SQL Server Syntax SELECT TOP 1 column_name FROM table_name ORDER BY column_name ASC; Example SELECT TOP 1 CustomerName FROM Customers ORDER BY CustomerID ASC; MySQL Syntax SELECT column_name FROM table_name ORDER BY column_name ASC LIMIT 1; Example SELECT CustomerName FROM Customers ORDER BY CustomerID ASC LIMIT 1; Oracle Syntax SELECT column_name FROM table_name…

Read More

Posted in SQL Tagged Comments Off on SQL FIRST() Function
SQL COUNT() Function

The COUNT() function returns the number of rows that matches a specified criteria. SQL COUNT(column_name) Syntax The COUNT(column_name) function returns the number of values (NULL values will not be counted) of the specified column: SELECT COUNT(column_name) FROM table_name; SQL COUNT(*) Syntax The COUNT(*) function returns the number of records in a table: SELECT COUNT(*) FROM table_name; SQL COUNT(DISTINCT column_name) Syntax The COUNT(DISTINCT column_name) function returns the number of distinct values of the specified column: SELECT COUNT(DISTINCT column_name) FROM table_name;…

Read More

Posted in SQL Tagged Comments Off on SQL COUNT() Function
SQL AVG() Function

The AVG() function returns the average value of a numeric column. SQL AVG() Syntax SELECT AVG(column_name) FROM table_name SQL AVG() Example The following SQL statement gets the average value of the “Price” column from the “Products” table: Example SELECT AVG(Price) AS PriceAverage FROM Products; The following SQL statement selects the “ProductName” and “Price” records that have an above average price: Example SELECT ProductName, Price FROM Products WHERE Price>(SELECT AVG(Price) FROM Products);

Read More

Posted in SQL Tagged Comments Off on SQL AVG() Function
Trigger That Will Remove All Blanks Spaces From a Field

If you are importing data into a table and some of the data like a customer fields has blank spaces on it and you want to get rid of then a trigger for Insert event may a quick solution. Here is a request: “ Is it possible to create a trigger that will remove all blanks from a field? They have an check number and when we read it through Import process, it keeps putting random spaces in the…

Read More

Posted in MySQL, SQL Tagged , Comments Off on Trigger That Will Remove All Blanks Spaces From a Field
SQL NULL Functions

SQL ISNULL(), NVL(), IFNULL() and COALESCE() Functions We have the following SELECT statement: SELECT ProductName,UnitPrice*(UnitsInStock+UnitsOnOrder) FROM Products In the example above, if any of the “UnitsOnOrder” values are NULL, the result is NULL. Microsoft’s ISNULL() function is used to specify how we want to treat NULL values. The NVL(), IFNULL(), and COALESCE() functions can also be used to achieve the same result. In this case we want NULL values to be zero. Below, if “UnitsOnOrder” is NULL it will…

Read More

Posted in SQL Tagged Comments Off on SQL NULL Functions
SQL NULL Values

NULL values represent missing unknown data. By default, a table column can hold NULL values. This chapter will explain the IS NULL and IS NOT NULL operators. SQL NULL Values If a column in a table is optional, we can insert a new record or update an existing record without adding a value to this column. This means that the field will be saved with a NULL value. NULL values are treated differently from other values. NULL is used…

Read More

Posted in SQL Tagged Comments Off on SQL NULL Values
SQL Date Functions

As long as your data contains only the date portion, your queries will work as expected. However, if a time portion is involved, it gets complicated. Before talking about the complications of querying for dates, we will look at the most important built-in functions for working with dates. MySQL Date Functions The following table lists the most important built-in date functions in MySQL: Function Description NOW() Returns the current date and time CURDATE() Returns the current date CURTIME() Returns…

Read More

Posted in SQL Tagged Comments Off on SQL Date Functions