What is a Query? Database Query Explained

In standard English, a query means a request for information. In computer programming, it refers to the same thing, except the information is retrieved from a database.

However, writing a query requires a set of pre-defined code to make the database understand the instruction. This concept is also known as the query language.

While the standard language for database management is Structured Query Language (SQL), other query languages to make database communication easy include AQL, Datalog, and DMX.

Note that SQL is different from MySQL – the former is the query language, while the latter is the software that uses the language.

This article will explain how a query works, examples of queries, and cover the steps of writing them in the database.

What Is a Query?

A database is a request for data from a database. The request should come in a database table or a combination of tables using a code known as the query language. This way, the system can understand and process the query accordingly.

How Does Query Work?

Let’s say that you want to order an Americano at a coffee shop. You make a request by saying, “Can I have an Americano?”. The barista will understand the meaning of your request and give you the ordered item.

A query works the same way – it adds meaning to the code, allowing the system to understand and execute actions accordingly. Be it SQL or any other query language, both the user and the database can exchange information as long as they use the same language.

Meanwhile, a well-designed database stores data in multiple tables. They consist of columns that hold the data’s attributes, along with rows or records of information. A query then helps retrieve data from different tables, arrange them, and display them according to the commands.

A query can either be a select, an action, or a combination of both. Select queries can retrieve information from data sources, and action queries work for data manipulation, for example, to add, change or delete data.

Advanced users can also use query commands to perform various programming tasks, from creating MySQL users and granting permissions to changing WordPress URLs in MySQL databases.

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

• SELECT – fetch data from the database. It’s one of the most popular commands, as every request begins with a select query.
• AND – combine data from one or more tables.
• CREATE TABLE – build different tables and specify the name of each column within.
• ORDER BY – sort data results either numerically or alphabetically.
• SUM – summarize data from 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 commands above. For example, pair the SELECT query with other commands like AND or SUM to aggregate data or combine results.

Besides using query language to request information from a database, other methods include:

• Using available parameters. By default, the database software has lists of parameters that users can define as they need. These parameters deal with exchanging information between user-defined functions (UDF) and stored procedures (SP).
• Query by example (QBE). Relational databases use a graphical query language. The system will show a code template in which you can write and specify the fields and values of your data. So instead of writing complete SQL statements, a user can fill the blank areas.
• Installing database plugins. An ideal solution for beginners – the plugins let users perform various database tasks, including queries, with only a few clicks. Additionally, 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.

Web search query refers to keywords that users type in the search engine, while database query is a particular action to make a request for information.

Now that you understand the basic fundamentals of queries, let’s study several standard terms you might come across when querying a database:

• Query string – a portion of URL to pass requests from the web to the database.
• Query parameters – elements attached to the end of an URL to specify a particular query on the database.
• Query folding – refers to a process where the Power Query is enabled to transform complex calculations for query optimization.
• Query containment – happens when one query is contained in another, if it is independent of the stored data values.

Query Languages

As mentioned before, choosing the database and its language is crucial when working with queries. In addition to SQL, there is another type of database called NoSQL (Not Only Structured 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 your data structure. On the other hand, NoSQL databases are non-relational and have dynamic schemas for unstructured data.

Regardless, both SQL and NoSQL provide applicable options. An SQL database is a great choice for an ACID-compliant data structure. Conversely, if you have unstructured documents, key-values, or graphs, a NoSQL database might be an ideal choice.

Query Examples

Before we delve into the examples, below are the main benefits of using a query:

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

Now, let’s suppose you have collected some data from a survey. Below is a snippet of your data. Note that for this example, we will use an SQL database.

Data source: Participant (Table Name)

ID

Name

Sex

Age

Occupation

1

John

Male

17

Student

2

Peter

Male

26

Unemployed

3

Margareth

Female

34

Teacher

4

Lea

Female

34

Unemployed

Selecting Only the “Name” and “Occupation” Columns From the “Participant” Table

This example shows you how to create a select query that only returns the value for Name and Occupation. The SQL statement should look something like this:

SELECT Name, Occupation FROM Participant

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

Name

Occupation

John

Student

Peter

Unemployed

Margareth

Teacher

Lea

Unemployed

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

Deleting Data From the Unemployed Respondents

The DELETE query works to remove existing records from particular tables. In this example, we are going to delete the Unemployed records using the following statement:

DELETE FROM Participant WHERE Occupation = ‘Unemployed’

Hit enter, and this will remove the respective records and return this output:

ID

Name

Sex

Age

Occupation

1

John

Male

17

Student

3

Margareth

Female

34

Teacher

Inserting a New Row Containing a Participant Called Mario

In a broader scene, the INSERT INTO query inserts data into the MySQL database via MySQLi and 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’re adding new values and fields, specify all the elements. Hence, the statement will look like this:

INSERT INTO <em>table_name</em> (<em>column1</em>,<em> column2</em>,<em> column3</em>, ...)

VALUES (<em>value1</em>,<em> value2</em>,<em> value3</em>, ...);

1. If you’re only adding new values to all existing columns, use the statement below:

INSERT INTO <em>table_name</em>

VALUES (<em>value1</em>,<em> value2</em>,<em> value3</em>, ...);

Changing Margareth’s Occupation to “Headmaster”

To modify existing records in a table, use the UPDATE query. Meanwhile, to specify which rows to update, use the WHERE query.

In this case, we’re going to edit Margareth’s occupation to Headmaster. Thus, the SQL statement will be:

UPDATE Participant SET Occupation = ‘Headmaster’ WHERE ID = ‘3’

The query runs to update row 3 into the specified value and shows the following output:

ID

Name

Sex

Age

Occupation

1

John

Male

17

Student

2

Peter

Male

26

Unemployed

3

Margareth

Female

34

Headmaster

4

Lea

Female

34

Unemployed

Wrapping Up

A query can either be a select or action query – select queries pick parts of your data, while action queries manipulate retrieved data.

A query can also work with the combination of both actions to perform more varied tasks, for example, to review, insert, modify, or delete data, as well as calculate and combine data from multiple tables.

Database queries show that manipulating data doesn’t have to be complicated. Most query languages are intuitive and are easy to learn once you understand some basic rules. For those who don’t feel comfortable coding, you can use database plugins or Query by example as alternatives.

We hope this article has shed more light on database query and how it works. Don’t hesitate to leave a comment below if you are still experiencing problems with database queries.

What is a database query?

At a very high level, a query is a question. When we talk about queries in relation to other people, we expect some sort of answer in return. This is no different for computers when we perform database queries.

A database query is a similar action that is most closely associated with some sort of CRUD (create, read, update, delete) function. A database query is a request to access data from a database to manipulate it or retrieve it.

This allows us to perform logic with the information we get in response to the query. There are several different approaches to queries, from using query strings, to writing with a query language, or using a QBE like GraphQL or REST.

With GraphQL, users can query for and receive only the specific data they’re looking for; not more, not less.

Note: GraphQL allows you to request specific data, giving clients more control over what information is sent.

This is more difficult with the alternative architecture, called REST, because the backend defines what data is available for each resource on a URL.

Query Parameters

Query Parameters are put on the end of a URL as part of a query string. This is how search engines grab search results for parameters a user inputs in a search bar. You can also add query parameters to the end of an endpoint to aid in pagination.

Note: When using query parameters, there is no need to know or use an actual query language for the most part.

What is Query By Example?

Formulated by a computer scientist at IBM in the 1970s, Query By Example (QBE) is a filtering or search system for databases where there was no need to use a query language.

It is done under the hood for you. The timeline for QBE occurred alongside the development of the structured query language (SQL), which we’ll go over in the next section.

More than likely there is a graphical user interface that a user fills out. Once submitted, the query is built under the hood. This prevents missing input bugs as the query only gets built from the information that it’s given as opposed to a prebuilt query that is expecting specific information.

Let’s look at an example.

Title: Jurassic Park Director: Steven Spielberg     Year:  Language:   Release:

The resulting SQL that is created:

SELECT * FROM Movies WHERE Title=’Jurassic Park’ AND Director='Steven Spielberg';

This is a very basic sampling of the type of QBE form that can be used to generate SQL. Other forms will use drop-downs to add other SQL keywords such as LIKE, CONTAINS, etc.

QBE paved the way for end-user development, allowing those who are not professional software developers or programmers to extend a piece of software to suit their needs. It is currently used in relational and some object-oriented databases.

Languages for database querying

Query language is what allows us to actually take action on databases. It allows us to create, read, update and delete items on our database, as well as more advanced queries like filtering and counting.

Structured Query Language (SQL) is the most famous of the query languages. SQL grew up alongside the Query By Example (QBE) system developed by IBM in the 1970s. It serves the basis of relational databases.

With SQL, we can store, retrieve, and manipulate data using simple code snippets, called queries, in an RDBMS (relational database management system).

The data is stored in the RDBMS in a structured way, where there are relations between the different entities and variables in the data.

These relations are defined by the database schema, which specifies the relation between various entities and the organization of data for the entities.

Purposes of SQL

Define Data

• Create a database with CREATE DATABASE my_database;
• Create a table with:

CREATE TABLE my_table(   column1 datatype,   column2 datatype,   column3 datatype,   columnN datatype,   PRIMARY KEY( columnName ));

• Drop a database with DROP DATABASE my_database;
• Drop a table with DROP TABLE my_table;

Manipulate Data

• Use a database with USE my_database;
• Insert data into a database with:

INSERT INTO my_table (column1, column2, column3,columnN)  VALUES (value1, value2, value3,valueN);

• Select data with: SELECT column1, column2, columnN FROM my_table;
• Select data with a specific condition:

SELECT column1, column2, columnN FROM my_tableWHERE [condition] // use LIKE, CONTAINS, <, >, etc. here

• Update query:

UPDATE my_tableSET column1 = value1, column2 = value2, columnN = valueNWHERE [condition]; 

• Delete query:

DELETE FROM my_tableWHERE [condition];

Control Data

• Transactions:

COMMIT;ROLLBACK;

SQL is the base for creating structured queries for your relational databases. There are many other “flavors” of SQL that each use SQL in their own way. Different versions of SQL include Oracle PL/SQL, PostgreSQL, and Microsoft Transact-SQL. At a high-level, all are very similar, but each might have their own syntax for certain operations.

Keep the learning going.

Maser databases and SQL without scrubbing through videos or documentation. Educative’s text-based courses are easy to skim and feature live coding environments, making learning quick and efficient.

RDBMS using PostgreSQL

1. Use documentation to create a file that sets up a configuration and connection to Postgres.
2. Create tables
3. Run function that creates your tables
4. Create simple server using Express
5. GET request to a sample API:

app.get('/info, (req, res) => {pool.connect((err, client, done) => {    const query = 'SELECT * FROM my_table;    client.query(query, (error, result) => {      done();      if (error) {        res.status(400).json({error})      }       if(result.rows < '1') {        res.status(404).send({        status: 'Failed',        message: 'No information found',        });      } else {        res.status(200).send({        status: 'Successful',        message: ‘Information retrieved',        results: result.rows,        });      }    });  });});

1. POST request to sample API:

app.post('/info, (req, res) => {  const data = {    name : req.body.name,    age : req.body.age,    address : req.body.address,    city : req.body.city,    state : req.body.state,  }  pool.connect((err, client, done) => {    const query = 'INSERT INTO my_table(name,age, address, city, state) VALUES($1,$2,$3,$4,$5) RETURNING *';    const values = [data.name, data.age, data.address, data.city, data.state];    client.query(query, values, (error, result) => {      done();      if (error) {        res.status(400).json({error});      }      res.status(202).send({        status: 'Successful',        result: result.rows[0],      });    });  });});

NoSQL using MongoDB

1. Use documentation to create a file that sets up a configuration and connection to MongoDB.
2. Create schema
3. GET request to sample API:

const client = new MongoClient(uri);async function runMongo() {  try {    await client.connect();    const database = client.db("my_database");    const collection = database.collection("movies");    // Query for a movie that has the title 'Jurassic Park'    const query = { title: "Jurassic Park" };    const options = {      projection: { _id: 0, title: 1, imdb: 1 },    };    const movie = await collection.findOne(query, options);    console.log(movie);  } finally {    await client.close();  }}runMongo().catch(console.dir);

1. POST request to sample API:

const client = new MongoClient(uri);async function runMongo() {  try {    await client.connect();    const database = client.db("my_database");    const collection = database.collection("movies");    // create a document to be inserted    const doc = { title: "Jurassic World", imdb: {rating: 4.0, votes: 32333, id: 241567}};    const result = await collection.insertOne(doc);    console.log(      `${result.insertedCount} documents were inserted with the _id: ${result.insertedId}`,    );  } finally {    await client.close();  }}runMongo().catch(console.dir);

NoSQL Using Mongoose – framework for MongoDB

1. Use documentation to create a file(s) that sets up a configuration and connection to Mongoose and Express.
2. Create schema
3. GET Request to Sample API:

app.get('/info, async (req, res) => {      let movies = await collection.find() {      if(movies){          res.status(200).json(movies);      } else {          res.status(500).json(“movies not found”);      } });

1. POST Request to Sample API:

app.post('/info, async (req, res) => {      const { title, imdb } = req.body;      let movie = new Movie({title, imdb});      Movies.save((err, movie)=> {          if(movie){                 res.status(201).json(movie);          } else {          res.status(500).json(“movies not posted”);          }});