SQL NOW() Function
The NOW() Function The NOW() function returns the current system date and time. SQL NOW() Syntax SELECT NOW() FROM table_name; Example SELECT ProductName, Price, Now() AS PerDate FROM Products;
The NOW() Function The NOW() function returns the current system date and time. SQL NOW() Syntax SELECT NOW() FROM table_name; Example SELECT ProductName, Price, Now() AS PerDate FROM Products;
The ROUND() Function The ROUND() function is used to round a numeric field to the number of decimals specified. SQL ROUND() Syntax SELECT ROUND(column_name,decimals) FROM table_name; Parameter Description column_name Required. The field to round. decimals Required. Specifies the number of decimals to be returned. Example SELECT ProductName, ROUND(Price,0) AS RoundedPrice FROM Products;
The LEN() Function The LEN() function returns the length of the value in a text field. SQL LEN() Syntax SELECT LEN(column_name) FROM table_name; Example SELECT CustomerName,LEN(Address) as LengthOfAddress FROM Customers;
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;
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;
The UCASE() Function The UCASE() function converts the value of a field to uppercase. SQL UCASE() Syntax SELECT UCASE(column_name) FROM table_name; Syntax for SQL Server SELECT UPPER(column_name) FROM table_name; Example SELECT UCASE(CustomerName) AS Customer, City FROM Customers;
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;
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…
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;
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;