Database Query: Definition & Tools

Instructor: Martin Gibbs

Martin has 16 years experience in Human Resources Information Systems and has a PhD in Information Technology Management. He is an adjunct professor of computer science and computer programming.

This lesson will cover the concept of a database query. Database queries will be defined, and some basic examples will be explained. An example of query design using the Microsoft Access tool is described.

Database Query

We query data every day -- from Google searches to asking Siri for a funny joke. Queries are simply questions against a set of data. They can become very complex, involving multiple tables and millions of records; however, the basic concept is straightforward and not very complex.

A database query is a request for data from a database. Usually the request is to retrieve data; however, data can also be manipulated using queries. The data can come from one or more tables, or even other queries.

Example

As mentioned above, when we ask Siri for a joke, we are posing a question/query to the application. While the query behind the scenes is very complex and will search against many sources, the concept can be made quite simple.

A query starts with the keyword SELECT, no matter the system. This tells the database to go pick something; the details are after the SELECT statement. If we want all jokes, we use the asterisk (*), which tells the database to retrieve everything:


SELECT * FROM tblJokes;


A Word About Semicolons

The SQL statement ends with a semicolon (;). This is REQUIRED to end all statements with this character. It tells the database engine to process everything before the semicolon; it indicates the end of processing. You will see the semicolon used as the last character in SQL statements.

Let's get back to our query. Note that the name of the table is tblJokes. It is common practice to prefix the names of database objects for easy identification. A report could be labeled rptJokes, a query qryJokes, and so on. If everything is named the same, it creates confusion.

If the jokes table is huge, the resulting data display will be unmanageable. But, what if we only want clean jokes? If there is a field in the table for the flag clean_joke, we can add that filter to our query. That is done via the WHERE statement. This tells the database to perform a filter and only return records that meet the conditions after the WHERE statement. In this case, a joke is clean if the flag is set to 1.


SELECT * FROM tblJokes WHERE clean_joke = 1;


An additional piece of information that could be added is the ORDER BY clause: perhaps we want to see the jokes ordered by length, or name, or author. The following SQL statement retrieves clean jokes and orders by the joke name in ascending alphabetical order:


SELECT * FROM tblJokes WHERE clean_joke = 1
ORDER BY author ASC;


There is also a GROUP BY clause to further order data into groups; this is useful in advanced reporting to show totals and sub-totals.

Other operations

A query can do more than retrieve data. It can also insert, update, create, or delete data/tables. The basic structure of each query is similar: The first keyword (e.g., SELECT) tells the database which operation to carry out; the following statements refine this statement into specific instructions. These include the table(s) to adjust/query, the types/values of records to work on, or how to group the data.

  • Insert new data into a table:

The following inserts a new record into the table tblArtist:


INSERT INTO tblArtist(artistName, genre, countryCode, notes)
VALUES('Journey', 'Rock', 'US', 'missing Raised on Radio');


  • Update an existing record in a table:

The following looks for a record with an artistID of 1 and updates the name of the artist.


UPDATE tblArtist SET artistName = 'Anthem UK'
WHERE artistID = 1;


  • Delete records

To unlock this lesson you must be a Study.com Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Try it risk-free for 30 days

Earning College Credit

Did you know… We have over 200 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it risk-free for 30 days!
Create an account
Support