Part 3: Here's how to unleash your database power with queries

• A database query is a request for data from a database.
• It's a way for a user or a program to retrieve data stored in a database by asking questions about the data.
• But how do you do that?

In part 1 of this series, we covered the basics of databases, and in part 2, we looked at how to build one. But what more can you do with the database?

Let us introduce you to something called "queries." Hold on tight; things are about to get wild.

What is a database query?

In a database query, you tell the computer what columns and rows of data to get and what conditions must be met in order for the data to be in the results.

Queries are expressed using a specialized language called Structured Query Language (SQL). SQL provides a syntax for asking questions about data stored in a database and retrieving the matching data.

For example, suppose you have a database of customers and their orders, and you want to retrieve the names of customers who have placed orders with a value greater than $100. You would write an SQL query that specifies the columns to retrieve (e.g., the customer name), the conditions to be met (e.g., the total order value must be greater than $100), and the table or tables where the data is stored (e.g., the customer and order tables). The database management system (DBMS) would then execute the query, retrieve the data that matches the conditions, and return the results to you.

In other words, a database query asks questions about data stored in a database and retrieves the results. This is, by far, one of the most powerful functions of a database, allowing you to interrogate data sets and perform some pretty cool data manipulations.

What kind of things can you do with database queries?

Database queries are fundamental for interacting with databases and have many uses.

Some of the most common things you'll want to use them for include, but are not limited to, the following:

• Data Retrieval: One of the primary uses of database queries is to retrieve data stored in a database. This could be used, for example, to display a list of products for sale on a website, to show a user their account information, or to generate reports based on data stored in a database.
• Data Filtering: Another use of database queries is to filter data based on specific criteria. For example, you might use a query to retrieve information on all customers who have placed orders in the past month or to show all products that are out of stock.
• Data Sorting: Queries can also be used to sort data stored in a database. For example, you might use a query to sort a list of customers by their last name or a list of products by price.
• Data Aggregation: Queries can be used to aggregate data stored in a database, such as calculating the total sales for a given period or finding the average price of products in a specific category.
• Data Modification: Database queries can also modify data stored in a database. For example, you might use a query to update a specific customer's information or delete a product that is no longer available.
• Data Joining: Queries can combine data from multiple tables in a database, such as joining information about customers and their orders to show a complete picture of a customer's activity.

These are just a few examples of the many uses of database queries. Queries are essential for working with databases and retrieving the information you need.

So, time for some examples.

What are the most helpful database queries?

So, you've got your database set up, populated with data, and are a happy bunny.

But what can you do with it now? Let's look into the beautiful world of queries.

But, before we begin, please take note of the following.

• A special note before we continue, since MySQL is based on the coding language SQL, pay particular attention to the provided code, especially the use of the ";" character. This is very important, as missing it will often be the main reason an SQL is not actioned. It is a straightforward thing to miss and incredibly frustrating to try and spot when debugging your code if it isn't working.
• Relevant commands/SQL code is included between the ===== breaks throughout the text.
• Also, some web hosting MySQL DBMSs may require additional formatting like a "`" encapsulating the database name, column names, etc. So, instead of "SELECT * FROM users" you'll need to type "SELECT * FROM `users`"
• Whenever you plan on running a query on a database, especially one that modifies the data, it is VERY IMPORTANT that you either back up your data first or "test" run the query first, or both. Most database management systems will provide a virtual environment to show you the likely outcome of a query, or pick up any potential problems with your query. Terms used for this will vary, but in most cases, you should see a "simulate query" button or similar on your DBMS. However, this is usually over the top for SELECT queries (as this doesn't modify data).

Ok, let's get into it.

1. The SELECT query is probably the one you'll use most often

The SELECT query in SQL is used to retrieve data from one or more tables in a database. It's the most commonly used type of SQL query. It is used to retrieve specific columns and rows from a database based on a set of conditions. As highlighted earlier, it is an example of a data retrieval query.

The SELECT query in SQL is an essential tool for working with databases and is used in various applications.

Here is the basic syntax for a SELECT query in SQL:

=================================================================

SELECT column1, column2, ...

FROM table_name

WHERE condition;

=================================================================

The SELECT clause specifies the columns you want to retrieve from the database. The "FROM" clause specifies the table or tables where the data is stored. The "WHERE" clause is optional and is used to set conditions that must be met for data to be included in the results.

For example, suppose you have a table named "customers" that contains information about customers, and you want to retrieve the names and addresses of customers who live in a specific city. You could write the following "SELECT" query to retrieve this data:

=================================================================

SELECT name, address

FROM customers

WHERE city = 'London';

=================================================================

This query would retrieve the "name" and "address" columns from the "customers" table for all customers who live in the city of London. The query would then run and display a table containing the matching data.

Pretty neat, huh?

2. The UPDATE query is another handy one

An UPDATE query in SQL is used to modify the data in one or more database table rows. The basic syntax for an update query is as follows:

=================================================================

UPDATE table_name

SET column1 = value1, column2 = value2, ...

WHERE some_column = some_value;

=================================================================

In the above, "table_name" is the name of the table that you want to update, "column1," "column2," etc., are the names of the columns that you want to modify, and "value1," "value2," etc., are the new values that you want to assign to the columns.

The "WHERE" clause specifies which rows to update by defining a condition the rows must satisfy. Only the rows that meet this condition will be modified. Using the "WHERE" clause in an update query is essential because, without it, all rows in the table will be updated, which could have unintended consequences.

Trust us when we say that you'll only ever run an "UPDATE" query without a "WHERE" condition once in your life. You'll never forget the feeling of horror when running it accidentally on a large data set!

So, avoid all the feelings of despair and panic by never doing it in the first place. Also, test run the query first!

You are welcome.

3. The DELETE query is a great way to keep your data sets clear of rubbish

A DELETE query in SQL is used to delete records from a table in a database. The syntax for a DELETE query is as follows:

=================================================================

DELETE FROM table_name WHERE some_column = some_value;

=================================================================

In the above, "table_name" is the name of the table from which you want to delete records, and "WHERE some_column = some_value" is the condition that specifies which records to delete. The condition specifies which rows will be deleted based on the value in the specified column.

For example, if you want to delete all records from the table "students" where the "age" column is equal to 20, the "DELETE" query would look like this:

=================================================================

DELETE FROM students WHERE age = 20;

=================================================================

It's important to use the "WHERE" clause in a "DELETE" query to avoid accidentally deleting all records in a table. If you omit the "WHERE" clause, all records in the table will be deleted.

4. Other handy queries you'll want to know

Other than the ones listed above, some other beneficial queries include the following:

• CREATE: The CREATE statement creates new tables, views, indexes, and other database objects.
• ALTER: The ALTER statement is used to modify the structure of a table or other database objects.
• DROP: The DROP statement removes tables, views, indexes, and other database objects.

• JOIN: The JOIN clause combines rows from two or more tables based on a related column.
• GROUP BY: The GROUP BY clause groups rows with the same values into summary rows.
• HAVING: The HAVING clause filters groups based on a specified condition.

And that's your lot for today.

So, by now, you should have a good grasp of a database, how to build one, and, most importantly, how to query it. With all that in mind, we'll look at what you can do with all this new knowledge in the next and final episode.