Using Conversion Functions and Conditional Expressions in SQL

6.Show Last 4 Digits of Phone Numbers.

Oracle
SELECT SUBSTR(Phone, -4) AS Last4Digits FROM Customers;

MySQL, PostgreSQL, SQL Server
SELECT RIGHT(Phone, 4) AS Last4Digits FROM Customers;

Explanation
This query retrieves the last 4 digits of the Phone column. Oracle uses SUBSTR with a negative start position, while other databases use the RIGHT function. This is often required for safe display of sensitive phone numbers as in masking other digits.

7.Convert Dates to Month-Year Format.
Oracle
SELECT TO_CHAR(tDate, 'MM-YYYY') AS MonthYear FROM Transactions;

MySQL
SELECT DATE_FORMAT(tDate, '%m-%Y') AS MonthYear FROM Transactions;

PostgreSQL
SELECT TO_CHAR(tDate, 'MM-YYYY') AS MonthYear FROM Transactions;

SQL Server
SELECT FORMAT(tDate, 'MM-yyyy') AS MonthYear FROM Transactions;

Explanation
This query displays transaction dates in "Month-Year" format. The keywords are different for each database: Oracle and PostgreSQL use TO_CHAR, MySQL uses DATE_FORMAT, and SQL Server uses FORMAT. This is necessary for this exercise to produce a monthly report or summary.

8.Replace NULL Balances with Zero.

Common for all databases
SELECT COALESCE(Balance, 0) AS AdjustedBalance FROM Accounts;

The COALESCE function replaces NULLs in the Balance column with 0. Missing values do not populate any reports or calculations. This is a universally reliable answer, because the syntax is exactly the same for Oracle, MySQL, PostgreSQL, and SQL Server.

9.Add Prefixing to Account Types.
SELECT CONCAT('Type: ', AccountType) AS PrefixedAccountType FROM Accounts;

Explanation
This query combines the string "Type: " with values in the AccountType column. The CONCAT function works the same way across all databases. That is handy for building the labels or descriptions of an account type in reports or interfaces.

10.Classify Transactions as High or Low.

SELECT Amount, CASE
WHEN Amount >= 1000 THEN 'High'
ELSE 'Low'
END AS TransactionCategory
FROM Transactions;

Explanation
This question employs a CASE expression to sort transactions as "High" if the Amount is 1000 or more; otherwise, "Low." Such classifications are useful in isolating the high transaction occurrence. The query syntax is perfectly applicable across Oracle, MySQL, PostgreSQL, and SQL Server.

11.Replace NULL Emails with 'Not Provided'

Common for all databases

SELECT COALESCE(Email, 'Not Provided') AS EmailStatus FROM Customers;

Explanation
This query replaces all NULL values in the Email column with the string "Not Provided" using the COALESCE function. It ensures that all customers receive a valid, meaningful entry in reports or views. The query performs the same on Oracle, MySQL, PostgreSQL, and SQL Server so that it's a very flexible solution across the board.

12.Extract Initials from Customer Names.

Oracle
SELECT UPPER(SUBSTR(Name, 1, 1)) || '.' || UPPER(SUBSTR(Name, INSTR(Name, ' ') + 1, 1)) AS Initials FROM Customers;

MySQL, PostgreSQL
SELECT CONCAT(UPPER(LEFT(Name, 1)), '.', UPPER(SUBSTRING_INDEX(SUBSTRING_INDEX(Name, ' ', 2), ' ', -1, 1))) AS Initials FROM Customers;

SQL Server
SELECT UPPER(LEFT(Name, 1)) + '.' + UPPER(SUBSTRING(Name, CHARINDEX(' ', Name) + 1, 1)) AS Initials FROM Customers;

Explanation
This extraction: Extract Initials from Full Name This query extracts initials from a full name. In Oracle, SUBSTR and INSTR find the characters. MySQL and PostgreSQL use LEFT and SUBSTRING_INDEX, while SQL Server uses LEFT and CHARINDEX. The initializations are combined through concatenation functions (||, CONCAT, or +) and converted to uppercase for consistency. This query showcases different string manipulation methods in databases.

13.Format Phone Numbers as '(XXX) XXX-XXXX'.

Oracle
SELECT '(' || SUBSTR(Phone, 1, 3) || ') ' || SUBSTR(Phone, 4, 3) || '-' || SUBSTR(Phone, 7, 4) AS FormattedPhone FROM Customers;

MySQL, PostgreSQL, SQL Server
SELECT CONCAT('(', SUBSTRING(Phone, 1, 3), ') ', SUBSTRING(Phone, 4, 3), '-', SUBSTRING(Phone, 7, 4)) AS FormattedPhone FROM Customers;

Explanation
This query formats phone numbers into a readable format: (XXX) XXX-XXXX. Oracle uses SUBSTR and concatenation (||), while other databases depend on SUBSTRING and CONCAT. Such formatting is very useful when printing phone numbers in friendly ways, such as contact lists or receipts.

14.Print Transactions type in Uppercase.

Universal for all databases
SELECT UPPER(TransactionType) AS UppercaseTransactionType FROM Transactions;

Explanation
This question makes all the values in the TransactionType column uppercase using the UPPER function. If the data is coming from different sources with varying capitalization, this will normalize text case. This query will run the same across all databases, so it is cross-compatible.

15.List Customers who Have No Phone Number.
Universal to all databases

SELECT CustomerID, Name FROM Customers WHERE Phone IS NULL;

Explanation
This query identifies customers who have not provided a phone number by checking for NULL in the Phone column. Only the columns CustomerID and Name are retrieved for concise results. This query is an important check of completeness, operating with uniformity in all the databases.

16.Convert Transaction Dates to Text.

Oracle
SELECT TO_CHAR(tDate, 'DD-MON-YYYY') AS TransactionDateText FROM Transactions;

MySQL
SELECT DATE_FORMAT(tDate, '%d-%b-%Y') AS TransactionDateText FROM Transactions;

PostgreSQL
SELECT TO_CHAR(tDate, 'DD-Mon-YYYY') AS TransactionDateText FROM Transactions;

SQL Server
SELECT FORMAT(tDate, 'dd-MMM-yyyy') AS TransactionDateText FROM Transactions;

This question formats tDate as text in the "DD-MON-YYYY" format. Every database has a date formatting function (TO_CHAR, DATE_FORMAT, or FORMAT) in order to produce the output that has the above. That comes in handy for reports or front ends.

17.Retrieve Accounts with Balance Greater Than a Specified Threshold.

Universal to all databases

SELECT AccountID,
CASE
WHEN Balance > 5000 THEN 'High Balance'
ELSE 'Standard Balance'
END;

This query will classify the accounts by their balance. Accounts above 5000 are classified as "High Balance," and everything else is "Standard Balance." The CASE expression allows for conditional classification and is particularly common when producing financial reports. The query syntax works the same in all databases.

18.Convert Account Balances to Euros.

Universal for all databases

SELECT Balance, Balance * 0.85 AS BalanceInEuros FROM Accounts;

This question converts account balances from the default currency to euros with a fixed conversion rate of 0.85. The question includes both the original and converted balances. These conversions are required for international reporting and financial analysis. The question syntax applies to all databases listed.

19.Display Names in Title Case ( Initial capital Letter).

Oracle
SELECT INITCAP(Name) AS TitleCaseName FROM Customers;

MySQL, PostgreSQL
SELECT CONCAT(UPPER(LEFT(Name, 1)), LOWER(SUBSTRING(Name, 2))) AS TitleCaseName FROM Customers;

SQL Server
SELECT UPPER(LEFT(Name, 1)) + LOWER(SUBSTRING(Name, 2, LEN(Name) - 1)) AS TitleCaseName FROM Customers;

Explanation
This query formats the names of customers in title case-that is, only the first letter is uppercase, and all the rest are lowercase. In Oracle INITCAP automatically does this for you. The result can also be achieved in MySQL, PostgreSQL, and SQL Server using string functions such as UPPER, LOWER, or substring methods. This is ideal to be displayed since it is more readable and presentable.

20.Write a query to Convert Balance to Words.

Oracle
SELECT TO_CHAR(TO_DATE(Balance, 'J'), 'JSP') AS BalanceInWords FROM Accounts;

MySQL, PostgreSQL, SQL Server
There is no native built-in function. It requires external libraries or application logic.

Explanation
In Oracle, the use of TO_CHAR with Julian date format ('J') converts numerical balances into their word equivalents. Other databases do not have a built-in function for doing this operation; a similar result requires external libraries or custom scripts in application layers. This can increase the readability of financial documents.

21.How to Categorize Transactions by Amount Using SQL CASE Statement?

General for all databases
SELECT TransactionID,
CASE
WHEN Amount < 100 THEN 'Low Value'
WHEN Amount BETWEEN 100 AND 1000 THEN 'Medium Value'
ELSE 'High Value'
END AS TransactionCategory
FROM Transactions;

This query uses a CASE expression to assign transactions into categories based on the Amount field. The three tiers Low Value, Medium Value and High Value are determined by ranges. This classification is essential for trend analysis or customer segmentation and works uniformly across all databases.

22. TRUNCATE Transaction Amounts to the Nearest Hundred

To use for all databases
SELECT TransactionID, ROUND(Amount, -2) AS RoundedAmount FROM Transactions;


Explanation
The query will truncate transaction amounts to the nearest hundred using the ROUND function with a negative precision of -2. This data transformation helps to reduce detail and focus on an approximate value, making the report data easier to understand. This method is fully compatible across Oracle, MySQL, PostgreSQL, and SQL Server.

23.Replace Blank Address with ‘Unknown’
General for all Databases.

SELECT COALESCE(Address, ‘Unknown’) AS AddressStatus FROM Customers;

Explanation
This statement applies COALESCE to fill in the NULL values in the Address column with "Unknown." It does not allow for any blank or undefined entries, which is helpful for guaranteeing a complete and proper dataset in user interfaces or audits.

24.How to Format SQL Dates to ISO 8601 Standard Across Databases?

Oracle
SELECT TO_CHAR(tDate, 'YYYY-MM-DD"T"HH24:MI:SS') AS ISO8601Date FROM Transactions;

MySQL
SELECT DATE_FORMAT(tDate, '%Y-%m-%dT%H:%i:%s') AS ISO8601Date FROM Transactions;

PostgreSQL
SELECT TO_CHAR(tDate, 'YYYY-MM-DD"T"HH24:MI:SS') AS ISO8601Date FROM Transactions;

SQL Server
SELECT FORMAT(tDate, 'yyyy-MM-ddTHH:mm:ss') AS ISO8601Date FROM Transactions;

This statement formats transaction dates to appear in the ISO 8601 standard, important for inter-system data exchange. Each database uses its date formatting function (TO_CHAR, DATE_FORMAT, or FORMAT) to produce the same result, ensuring consistency cross databases.

25.How to Append a Percentage Sign to SQL Query Results in Different Databases?

Oracle
SELECT TO_CHAR(Balance) || '%' AS PercentageBalance FROM Accounts;

MySQL, PostgreSQL
SELECT CONCAT(Balance, '%') AS PercentageBalance FROM Accounts;

SQL Server
SELECT CAST(Balance AS VARCHAR) + '%' AS PercentageBalance FROM Accounts;

Explanation
The query adds a percentage sign (%) to the values in the Balance column. Oracle makes use of concatenation (||), MySQL and PostgreSQL use CONCAT, while SQL Server uses + along with type conversion. These kinds of formats make the interface more understandable by humans to represent percentage values in reports or user interface applications.

Learn Using Sequences in Oracle SQL.