Tag: SQL

The FORMAT() Function The FORMAT() function is used to format how a field is to be displayed. SQL FORMAT() Syntax SELECT FORMAT(column_name,format) FROM table_name;   Parameter Description column_name Required. The field to be formatted. format Required. Specifies the format. Example SELECT ProductName, Price, FORMAT(Now(),’YYYY-MM-DD’) AS PerDate FROM Products;  

Read More

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;

Read More

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;

Read More

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;

Read More

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

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

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

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

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