What is a database query?

Let’s start by answering the main question: what is a database query? A query is used to extract information from a database. Allows you to manipulate data: add, delete and change. This is how we will use this word.

However, you don’t just have to write a random “request”. You must write your query based on a set of predefined codes, so that your database can understand the statement. We refer to this code as the query language.

The standard language for database management is Structured Query Language (SQL, Structured Query Language). While it is true that SQL is the most popular choice among database programs, it is definitely not the only one. Other options are AQL, datalogging Y DMX.

Important! Remember that: the first is the query language, the second is the software you use that language.

This article will explain how a query works, sample queries, and cover the steps to write them to the database.

What is a database query?

It consists of a request for information to a database. The request must come in a database table or a combination of tables using code known as a query language. In this way, the system can understand and process the query in question.

How does a query work?

Let’s say you want to order an American Coffee at a coffee shop. You make a request saying “An Americano please?”. The manager will understand the meaning of your request and will give you the requested product.

A query works the same way; it makes sense of the code used in any query language. Whether SQL or any other, both the user and the database can exchange information at any time, as long as they “speak” the same language.

Similarly, a well-designed database stores data in multiple tables. They are made up of columns that contain the attributes of the data, together with rows or records of information. A query helps to retrieve the data from the different tables, sort it and display it according to the commands.

A query can be a select, an action, or a combination of both. Select queries can retrieve information from data sources, and action queries are used to manipulate data, such as adding, modifying, or deleting it.

Advanced users can also use query commands to perform a variety of programming tasks, from creating MySQL users and granting permissions to modifying WordPress URLs in MySQL databases.

Below are some of the most common query commands along with their functions:

• SELECT – Get data from the database. It is one of the most popular commands, since all requests start with a select query.
• AND – combines data from one or more tables.
• CREATE TABLE – builds different tables and specifies the name of each column it contains.
• ORDER BY – sort the data results numerically or alphabetically.
• SUM – summarizes the data of a specific column.
• UPDATE – modify existing rows in a table.
• INSERT – add new data or rows to an existing table.
• WHERE – filter data and get its value based on a set condition.

For more variations, combine some of the above commands. For example, combine the query SELECT with other commands like AND either SUM to aggregate data or combine results.

In addition to using the query language to request information from a database, there are other methods:

• Using the available parameters: By default, the database software has lists of parameters that users can define according to their needs. These parameters deal with the exchange of information between user-defined functions (UDFs) and stored procedures (SPs).
• Query by example (QBE-Query by Example): Relational databases use a graphical query language. The system will show you a set of codes with some blank areas, in which you can write and specify the fields and values ​​of your data. Thus, instead of typing complete SQL statements, the user can fill in the blank areas.
• Installation of database plugins. An ideal solution for beginners: plugins allow users to perform various database tasks, including queries, with just a few clicks. Also, some plugins come with optimization features to ensure the best performance.

In addition to databases, search engines can also query and retrieve information. However, the term query in these two technologies differs.

The web search query refers to the keywords that users type into the search engine, while the database query it is a concrete action to make a request for information.

Now that you understand the basics of queries, let’s look at several standard terms you might encounter when querying a database:

• query string – part of the URL to pass the web requests to the database.
• Query Parameters – elements that are attached to the end of a URL to specify a specific query in the database.
• query folding – refers to a process in which the is enabled to transform complex calculations to optimize the query.
• query containment – occurs when one query is contained in another, if it is independent of the stored data values.

query languages

As we have already seen, the choice of database and its language is crucial when working with queries. In addition to , there is another type of database called NoSQL (Unstructured Query Language). The main difference between the two is the data structure.

SQL databases are relational and use predefined schemas that require you to specify their data structure. On the other hand, NoSQL databases are non-relational and have dynamic schemas for unstructured data.

In any case, both SQL and NoSQL offer applicable options. An SQL database is a great option for a data structure. Conversely, if you have unstructured documents, key values, or charts, a NoSQL database may be an ideal option.

Query Examples

Before we get into the examples, here are the main benefits of using a query:

• Review data from multiple tables simultaneously.
• Filter records that only contain certain fields and certain criteria.
• Automate data management tasks and perform calculations.

Now suppose you have collected some data from a survey. Below is a snippet of your data. Note that for this example we will be using an SQL database.

Data source: Participants (Name of the table)

IDNameSexAgeOccupation1JuanMale17Student2PedroMale26Unemployed3SaraFemale34Teacher4MariaFemale34Unemployed

Select only the “Name” and “Occupation” columns from the “Participant” table

This example shows how to create a select query that only returns the value of Name Y Occupation. The SQL statement should be something like this:

SELECT Name, Occupation FROM Participants

The above statement filters specific data from the table. This will generate the following result table:

NameOccupationJuanStudentPedroUnemployedSaraTeacherMariaUnemployed

To select other data types from the table, change the variables accordingly.

Remove data from unemployed respondents

the query DELETE It is used to delete existing records from certain tables. In this example, we are going to delete the records of Unemployed using the following statement:

DELETE FROM Participants WHERE Occupation = ‘Unemployed’

Hit enter, and this will delete the respective records and show this output:

IDNameSexAgeOccupation1JuanMale17Student3SaraFemale34Teacher

Insert a new row containing a participant named Mario

In a broader scenario, the query INSERT INTO insert data into MySQL database via MySQLi and of PHP data object. However, this example will show how to use the query to add a new row to a database table.

There are two different ways to incorporate this SQL statement:

1. If you are adding new values ​​and fields, specify all elements. So the declaration will look like this:

INSERT INTO table_name (column1,column2,column3, …) VALUES (value1, value2, value3, …);