Hunter Johnson for Educative

Posted on Nov 1, 2022 • Edited on Nov 4, 2022

What is a database query? SQL and NoSQL queries explained

Databases are crucial to building applications. They store data that make our applications work as they should. A database query is a request for a database's data so we can retrieve or manipulate it. But when should we query a database, and what exactly are we doing?

In this article, we'll introduce you to database queries with multiple common uses, including SQL, NoSQL, MongoDB, PostgreSQL, and more. You'll learn when to use a database query, along with best practices. Let's get started!

In this article, we'll learn:

What is a database query?
What is Query By Example?
Languages for database querying
Query using PostgreSQL
Query using MongoDB
Query using Mongoose

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 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 as 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 relationship 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_table
WHERE [condition] // use LIKE, CONTAINS, <, >, etc. here

Update query:

UPDATE my_table
SET column1 = value1, column2 = value2, columnN = valueN
WHERE [condition];

Delete query:

DELETE FROM my_table
WHERE [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 its own syntax for certain operations.

RDBMS using PostgreSQL

Use documentation to create a file that sets up a configuration and connection to Postgres.
Create tables
Run function that creates your tables
Create simple server using Express
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,
        });
      }
    });
  });
});

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

Use documentation to create a file that sets up a configuration and connection to MongoDB.
Create a schema
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);

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

Use documentation to create a file(s) that sets up a configuration and connection to Mongoose and Express.
Create a schema
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”);
      }
 });

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”);
          }
});

What to learn next

Congrats! In this article, we went over the various ways you can encounter queries. Relational Databases, NoSQL databases, search engines, SQL, and QBE are only some of the various ways you might encounter queries and query language.

There is still a lot to learn when it comes to databases and querying. The next things you'll want to learn are:

Data modeling
Entity relationship models
Functional dependencies
SQL operators

To get started with database design and to process with these topics, check out Educative's course Database Design Fundamentals for Software Engineers. You'll learn about the fundamental concepts of databases, why and when they're used, what relational databases are, and entity-relationship diagrams.

Happy learning!

Continue reading about databases and SQL on Educative

Start a discussion

What would you like to learn about databases next? Was this article helpful? Let us know in the comments below!

DEV Community