Navicat Blog

The Many Flavors of the SQL Count() Function Aug 27, 2020 by Robert Gravelle

If you have worked with relational databases (RDBMS) for any length of time, you have almost certainly utilized the SQL COUNT() function. As such, you are no doubt already aware that the COUNT() function returns the number of rows or columns in a table, as filtered by the criteria specified in the WHERE clause. Its flexible syntax and widespread support makes it one of the most versatile and useful functions in SQL. In today's blog, we'll take a look at its many permutations and learn how to obtain a variety of counts.

Storing Formatted Fields in a Database Aug 20, 2020 by Robert Gravelle

When it comes to storing formatted fields in a database, the adage "store raw, display pretty", usually holds true. In most cases, raw values are the most conducive for working with in the database, allowing them to be queried, sorted, compared, and what-have-you. Yet, there are times that you may want to leave in special characters, where they are essential to formatting, such as HTML markup. In today's blog, we'll explore both options with examples using Navicat Premium.

Applying Select Distinct to One Column Only Aug 12, 2020 by Robert Gravelle

Adding the DISTINCT keyword to a SELECT query causes it to return only unique values for the specified column list so that duplicate rows are removed from the result set. Since DISTINCT operates on all of the fields in SELECT's column list, it can't be applied to an individual field that are part of a larger group. That being said, there are ways to remove duplicate values from one column, while ignoring other columns. We'll be taking a look at a couple of those here today.

Splitting Query Results into Ranges Aug 4, 2020 by Robert Gravelle

Grouping query results into buckets of equal size is a common requirement for database developers and database administrators (DBAs) alike. Examples include:

  • customers whose last names begin with A - L and M-Z
  • products prices that are between 1 - 10 dollars, 11 - 20 dollars, 21 - 20 dollars, etc...
  • quarterly sales, i.e., from Jan - Mar, Apr - Jun, Jul- Sep, Oct - Dec

Standard SQL is well suited to this task. By combining the power of the CASE statement with the GROUP BY clause, data can be broken up into whatever range we deem necessary to best interpret our data. In today's blog, we'll compose a couple of range queries in Navicat Premium's excellent Query Editor.

Using Output Parameters in Stored Procedures Jul 29, 2020 by Robert Gravelle

Output parameters are a feature of stored procedures that is seldom used, which is a shame because they are an excellent option for returning scalar data to the user. In today's blog, we'll learn some uses for Output Parameters and how to use them in your stored procedures.

Navicat Blogs
Feed Entries
Blog Archives