What is a database query? SQL and NoSQL queries explained
Databases are crucial to building applications. They store data that make our applications work like 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
Learn the fundamental concepts of databases#
Learn about the basics of databases, relationship diagrams, normalization, and SQL.
Database Design Fundamentals for Software Engineers
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_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 their own syntax for certain operations.
Keep the learning going.#
Master 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.
Database Design Fundamentals for Software Engineers
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 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 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
- and more
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#
Free Resources
