Data is a core part of many businesses both big and small. For example, Facebook stores each user's profile information, including data about their friends and posts within a database system. SQL (short for Structured Query Language) is the programming language that enables developers and database administrators to work with that data.

There are a few frequently used SQL commands you should be familiar with for database work. Not including Data Definition Languages (DDL) or Data Manipulation Language (DML) statements, SQL commands include those to fetch data from tables and views using the SELECT statement. Today's blog will present a couple of the most important queries to know, along with some examples using Navicat Premium as the database client.

Determining the Lowest/Highest Value for a Column

The Sakila sample database contains a number of tables themed around the film industry that cover everything from actors and film studios to video rental stores. The queries that we'll be building here today will run against it, so you may want to refer to the Generating Reports on MySQL Data tutorial for instructions on downloading and installing the Sakila database.

One of the central tables in the Sakila database is the film table. It contains details about every film that our fictional video rental store owns. It includes information such as the film titles, release year, as well as the rental price:

Suppose that we wanted to know what the price range was - that is to say, the lowest and highest rental rates? We could easily find out using the MIN() and MAX() aggregate functions. An aggregate function performs a calculation on a set of values and returns a single value result. There are many aggregate functions, including AVG, COUNT, SUM, MIN, MAX, etc. Here's a query that applies MIN() and MAX() to the rental_rate field of the film table:

SELECT MIN(f.rental_rate) as lowest_price,
       MAX(f.rental_rate) as highest_price
FROM film f;

As expected, each function returns a single value:

Grouping Results by Category

One of the most powerful clauses in SQL is GROUP BY. It group rows that have the same values into summary rows. As such, the GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result set by one or more columns.

We can use the GROUP BY clause to list the minimum and maximum rental cost for each movie rating - i.e. General, PG, PG-13, etc. All we need to do is add the rating field to the column list and append the GROUP BY clause to the end of our existing query:

SELECT f.rating,
       MIN(f.rental_rate) as lowest_price,
       MAX(f.rental_rate) as highest_price
FROM film f
GROUP BY f.rating;

Our results show that each movie rating has films that range in price from $0.99 to $4.99:

Conclusion

Today's blog presented a couple of the most important queries to know, along with some examples using Navicat Premium as the database client. Navicat helps you code fast with Code Completion and customizable Code Snippets by getting suggestions for keywords and stripping the repetition from coding. You can try it for 14 days completely free of charge for evaluation purposes.