Database Management Systems
In MySQL, the GROUPING() function allows us to identity which rows have been generated by the WITH ROLLUP modifier of the GROUP BY clause.
By default, WITH ROLLUP outputs NULL as the value that represents all aggregate values. By this, I mean it doesn’t provide us with a nice easy to read label. It simply outputs NULL. This makes it more difficult for us to distinguish between normal rows and super aggregate rows that were generated by WITH ROLLUP.
MySQL can operate in different SQL modes. These can be set at server startup or at runtime.
DBAs can set the global SQL mode to match site server operating requirements, and each application can set its session SQL mode to its own requirements. Users can also set their own SQL mode at the session level via the @@sql_mode system variable.
By default, the sql_mode system variable contains a list of SQL modes that are applied.
As a user, we can modify our sql_mode system variable as required at runtime. This article demonstrates how we can remove a SQL mode from our sql_mode system variable.
NOT NULL constraints are an important tool for maintaining data integrity in SQL databases. However, we may occasionally find that a NOT NULL constraint gets in the way of a legitimate operation that we want to perform, and it prevents us from entering data. In such cases, we may need to remove the NOT NULL constraint in order to proceed.
To remove a NOT NULL constraint in MySQL, we use the ALTER TABLE statement to redefine the relevant column without the NOT NULL constraint.
In MySQL, list_add() is a system function that adds a value to a comma-separated list of values and returns the result.
So it’s a handy function that allows us to append a value to a list.
Continue readingIn MySQL, list_drop() is a system function that removes a value from a comma-separated list of values and returns the result.
In MySQL, ANY_VALUE() is an aggregate function that allows us to include nonaggregated columns in the SELECT list when using the GROUP BY clause.
The ANY_VALUE() function is commonly used to resolve issues that arise when a query includes columns that are not part of the GROUP BY clause or aggregate functions. It can be useful in scenarios where we want to include descriptive columns in a grouped result set without affecting the grouping behaviour.
If you’re getting an error that reads “LIMIT is only supported in combination with either BYSCORE or BYLEX” it’s probably because you’re trying to use the LIMIT clause without using the BYSCORE or BYLEX arguments.
This can happen when using the VRANGE command without either of the BYSCORE or BYLEX arguments.
To fix this issue, use either the BYSCORE or BYLEX argument when using the command. This obviously means that we need to adjust our query so that it’s querying by score or lexicographically.
When we create a sequence in PostgreSQL, we have the option of making it a non-repeating sequence or repeating sequence.
By “repeating” sequence, I mean that, once it reaches its maximum value (or minimum if it’s a descending sequence), it starts all over again, re-using numbers previously generated.
Continue readingMySQL provides us with a handy WITH ROLLUP modifier that allows us to get totals and subtotals when using the GROUP BY clause.
Below is an example of how the MySQL WITH ROLLUP modifier works.
If you’re getting error msg 6358 which reads something like 300 is not a valid style number when converting to XML, it’s probably because you’re trying to convert a value to XML, but the style that you’re specifying isn’t supported for conversions to that data type.
It’s not that the conversion can’t happen, it’s just that it can’t happen using the style that you’re specifying.
Continue reading