There are several ways to format a number as a percentage in SQL, depending on the DBMS being used.
Here are examples of adding a percent sign to a number in some of the most popular DBMSs.
Continue readingCategory: DBMS
Database Management Systems
How to Change the Locale When Formatting Numbers in PostgreSQL
If you’re formatting a number in PostgreSQL, and you want to change the locale so that the correct group and decimal separators are used, here’s how you can do that.
Continue readingCOUNT() Function in MariaDB
In MariaDB, the COUNT() aggregate function returns a count of the number of non-NULL values of an expression in the rows retrieved by a SELECT statement.
Oracle DROP TABLE IF EXISTS Alternatives
The very useful DROP TABLE IF EXISTS syntax was finally introduced in Oracle Database – Oracle Database 23c to be precise. This syntax allows us to run a DROP TABLE statement without getting an error if the table doesn’t exist.
Earlier versions of Oracle don’t support the IF EXISTS clause. Therefore, if we want to avoid any nasty errors resulting from trying to drop a non-existent table, we need to do a bit of extra work.
Below are three options for dropping a table if it exists in Oracle.
Continue readingAVG() Function in MariaDB
In MariaDB, the AVG() function returns the average value of the given expression.
The DISTINCT option can be used to return the average of the distinct values (i.e. remove duplicates before calculating the average).
NULL values are ignored.
How to Add an Option to sql_mode in MySQL Without Losing Existing Settings
Here are two options we can use to add an option to our sql_mode without wiping all existing options.
SUM() Function in MariaDB
In MariaDB, the SUM() aggregate function returns the sum of a given expression.
It can also be used to return the sum of all distinct (unique) values in an expression.
Continue readingHow to Prettify JSON Formatted Query Results in SQLcl (Oracle)
If you use SQLcl to query Oracle Database, you might be aware of the SET SQLFORMAT json option, that makes query results come back as JSON documents.
But there’s also a json-formatted option, which returns the results in a more human readable format.
HAS_DBACCESS() – Discover if a User Can Access a Database in SQL Server
SQL Server has a HAS_DBACCESS() function that returns information about whether the user has access to a specified database.
How to List All Stored Procedures in Oracle Database
There are several data dictionary views that we can use to return a list of stored procedures in Oracle Database.
Continue reading