Customize Data Outputs with Single Row Functions in SQL

6.Format account balances with commas for easier reading.
SELECT TO_CHAR(Balance, '9,999,999.99') AS FormattedBalance FROM Accounts;
In Oracle, use the TO_CHAR function to format the balance with commas as thousand separators and to ensure two decimal places. For example, the balance "1000000" will be displayed as "1,000,000.00." In MySQL, SQL Server and PostgreSQL, a similar function like FORMAT() can be used. The formatting makes financial data more readable.

7.Extract the year from the tDate column in the Transactions table.
SELECT EXTRACT(YEAR FROM tDate) AS Year FROM Transactions;
EXTRACT is used to get the year of the tDate column, where the transaction date is held. For instance, when you have the date as "2023-11-21" it will return you the year "2023." This query is helpful when you need to group or filter data by year. This function is supported in all databases with slight differences in syntax for different DBMS like PostgreSQL and SQL Server.

8.Extract the month from the tDate column in the Transactions table.
SELECT EXTRACT(MONTH FROM tDate) AS Month FROM Transactions;
Use the EXTRACT function to retrieve the month part from the tDate column. So, if the transaction date is "2023-11-21," this query will return "11" for November. This way, it's easy to group or filter data by month. All four databases support this function, although the syntax may vary a little.

9.Extract the day from a transaction date in the Transactions table.
SELECT EXTRACT(DAY FROM tDate) AS Day FROM Transactions;
This query uses the EXTRACT function to display the day part of a transaction date. If the date is "2023-11-21," it will show "21." It's always beneficial if you have to process certain days or filter data by the day of a month. The function is implemented in all database systems though with slight syntactic variations.

10.Determine the length of customer names in the Customers table.
SELECT LENGTH(Name) AS NameLength FROM Customers;
The LENGTH function returns the number of characters in the Name column. For instance, the name "Sarah" would have a length of 5. This is helpful if you are required to analyze or validate text lengths. The query syntax above works the same way across MySQL, SQL Server, and PostgreSQL; Oracle uses LENGTH too.

11.Add 7 days to the tDate in the Transactions table.
SELECT tDate + INTERVAL '7' DAY AS NewDate FROM Transactions;
This SQL query adds a 7-day interval to the column named tDate, but in the table Transactions. If the transaction date is "2023-11-21," it returns "2023-11-28." This can be helpful if you want to figure out when transactions are going to occur. Syntax will be slightly different from one database to another, but the idea will be the same.

12.Format transaction dates as DD-MM-YYYY.
SELECT TO_CHAR(tDate, 'DD-MM-YYYY') AS FormattedDate FROM Transactions;
Oracle regards the function TO_CHAR to format tDate column in a string format as DD-MM-YYYY. The date "2023-11-21" will be disp Displayed as "21-11-2023" in MySQL, SQL Server and PostgreSQL make use of their respective date formatting functions. DATE_FORMAT() or TO_CHAR() is used but the format is in line with all other databases.

13.Extract the username (string before @) from the Email column in the Customers table.
This function returns the portion of text before the '@' symbol in the Email column. If the email is "john.doe@gmail.com," then it returns "john.doe." Sometimes you will want to separate the username from an email address, and this will come in handy. In SQL Server, the equivalent function is CHARINDEX() instead of INSTR, but the logic is the same.

14.Make sure that each word in the Customer Name column is capitalized with the first letter.
SELECT INITCAP(Name) AS CapitalizedName FROM Customers;
The INITCAP function capitalizes the first letter of each word in the Name column. For example, "john doe" will be converted to "John Doe." This is useful when you want to standardize names in a title case format. This function works similarly across all four databases.

15.Calculate the absolute value of negative balances in the Accounts table.
SELECT ABS(Balance) AS AbsoluteBalance FROM Accounts;
ABS application The function ABS returns the absolute value of the Balance column, eliminating any negative signs if they appear. For instance, a balance of -500 will now appear as 500. It's very handy to be used in financial data analysis when you want to give importance solely to the magnitude of the value and do not pay much attention to whether it is positive or negative.

16.Use the LPAD function to pad the AccountID column with leading zeros to a total of 10 digits.
SELECT LPAD(AccountID, 10, '0') AS PaddedAccountID FROM Accounts;
The LPAD function pads the AccountID column with leading zeros so that the total length becomes 10 digits. For example, an AccountID of "12345" will print as "0000012345". This comes in handy when you want to standardize the format of numeric values, especially IDs or account numbers.

17.Remove leading and trailing spaces from customer names in the Customers table.
SELECT TRIM(Name) AS TrimmedName FROM Customers;
The TRIM function eliminates any leading and trailing spaces for that Name column. For instance, if the name is " John Doe ", it will be returned as "John Doe". This is very helpful when you need to clean up your data particularly when working with user-inputted information likely to produce unwanted spaces.

18.Print the current date and time using a function.
MySQL
SELECT NOW() AS CurrentDateTime;
The NOW() function returns the current date and time. For example, if the current date and time are "2023-11-21 12:30:00," then this function will return that timestamp. All four database systems have similar functions to get the current date and time, including SYSDATE in Oracle and CURRENT_TIMESTAMP in SQL Server and PostgreSQL.

19.Check if a customer’s email contains the domain @gmail.com.
SELECT CASE WHEN Email LIKE '%@gmail.com' THEN 'Gmail' ELSE 'Other' END AS EmailType FROM Customers;
This CASE statement checks whether the Email column contains the domain @gmail.com. If the email contains the domain, it will return 'Gmail' otherwise, it will return 'Other.' This is useful for categorizing email addresses based on the domain and works across all mentioned databases.

20.Extract last four digits of the phone number of a customer.
SQL Server, MySQL, and PostgreSQL.
SELECT RIGHT(Phone, 4) AS Last4Digits FROM Customers;
The RIGHT function displays the last four characters of the Phone number. For example, if you input the phone number "9876543210," the SQL will return "3210." This is very useful when you would like to show the last few digits of the phone number for verification or security purposes. The syntax does not differ between database types.
ORACLE
SELECT SUBSTR(Phone, -4) AS Last4Digits FROM Customers;

21.Show today date using a one-row function.
SELECT CURRENT_DATE AS CurrentDate;
The CURRENT_DATE function returns the current system date without the time part. In case today's date is "2023-11-21," the function will return that date. It helps to get the reports or filter data based on the current date in all databases.

22. Convert the Balance column of the Accounts table to a string.
SELECT CAST(Balance AS CHAR) AS BalanceAsString FROM Accounts;
Explanation
The CAST function is used to convert the Balance column from numeric data to a string. Therefore, for example a balance of 1500.50 becomes the string "1500.50" This is helpful in running string operations on numeric data such as formatting or concatenation.

23. Generate a random number between 1 and 1000.
SELECT FLOOR(RAND() * 1000) AS RandomNumber;
Explanation
The RAND() function generates a random number between 0 and 1 which the FLOOR function then rounds down to the nearest integer. Multiplying the result by 1000 causes the query to generate a random number between 1 and 1000-this can be useful for generating test data or random sampling.

24.Concatenate AccountType and Balance in to one formatted string of text.
SELECT CONCAT(AccountType,' ',Balance) AS AccountDetails FROM Accounts;
Explanation
CONCAT concatenates the AccountType and Balance columns into a single string with a space between them. If the values in AccountType were "Savings," and Balance is "1000," then the result would be "Savings 1000." This allows more than one item of information to appear together in one output field.

25. Create a bitmap index on the column AccountType in the table Accounts.
CREATE BITMAP INDEX idx_accounttype_bitmap ON Accounts (AccountType); Explanation It will create a bitmap index called idx_accounttype_bitmap on the column AccountType in Accounts. The bitmap index should be used on columns which have relatively low cardinality. Cardinality is by definition the number of unique values. In an account-type column, there would most likely be only 'Savings' and 'Checking'. On general practicality even if there are other types usage on multiple conditions or filters will be very efficient. Bitmap indexes employ bitmaps for representing data to take the advantage of fast logical operations. Extremely useful in warehousing data and analytical queries.

25.List the customers with your name and balance formatted.
SELECT CONCAT(Name, ' owes ', Balance) AS CustomerDebt FROM Customers INNER JOIN Accounts ON Customers.CustomerID = Accounts.CustomerID;
Explanation
This query concatenates the customer's name with the balance he/she owes or the amount in his/her account, returning a formatted message. For instance if this is "John Doe" and the balance is "1500" it'll return "John Doe owes 1500." This works for all databases but the syntax will differ slightly-a user would use CONCAT() in MySQL and + in SQL Server to concatenate.

Learn Using Sequences in Oracle SQL.