SQL MID() Function

The MID() Function The MID() function is used to extract characters from a text field. SQL MID() Syntax SELECT MID(column_name,start) AS some_name FROM table_name; Parameter Description column_name Required. The field to extract characters from start Required. Specifies the starting position (starts at 1) length Optional. The number of characters to return. If omitted, the MID() function returns the rest of the text Note: The equivalent function for SQL Server is SUBSTRING(): SELECT SUBSTRING(column_name,start,length) AS some_name FROM table_name; Example SELECT MID(City,1,4) AS ShortCity FROM Customers;

Read More

Posted in SQL Tagged Comments Off on SQL MID() Function
SQL LCASE() Function

The LCASE() Function The LCASE() function converts the value of a field to lowercase. SQL LCASE() Syntax SELECT LCASE(column_name) FROM table_name; Syntax for SQL Server SELECT LOWER(column_name) FROM table_name; Example SELECT LCASE(CustomerName) AS Customer, City FROM Customers;

Read More

Posted in SQL Tagged Comments Off on SQL LCASE() Function
SQL HAVING Clause

The HAVING Clause The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions. SQL HAVING Syntax SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name HAVING aggregate_function(column_name) operator value; Example SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM (Orders INNER JOIN Employees ON Orders.EmployeeID=Employees.EmployeeID) GROUP BY LastName HAVING COUNT(Orders.OrderID) > 10;

Read More

Posted in SQL Tagged Comments Off on SQL HAVING Clause
SQL GROUP BY Statement

Aggregate functions often need an added GROUP BY statement. The GROUP BY Statement The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns. SQL GROUP BY Syntax SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name; SQL GROUP BY Example Now we want to find the number of orders sent by each shipper. The following SQL statement counts as orders grouped by shippers: Example SELECT…

Read More

Posted in SQL Tagged Comments Off on SQL GROUP BY Statement
SQL SUM() Function

The SUM() Function The SUM() function returns the total sum of a numeric column. SQL SUM() Syntax SELECT SUM(column_name) FROM table_name; SQL SUM() Example The following SQL statement finds the sum of all the “Quantity” fields for the “OrderDetails” table: Example SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;

Read More

Posted in SQL Tagged Comments Off on SQL SUM() Function
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