In regular English, a query means a request for information. What is a query in computer programming then? Simple, it’s the same – except the information is retrieved from a database. This is handy for data manipulation – adding, removing, and changing data. That’s how we’ll use this word in the article.
However, you don’t just type in a random ‘request.’ You write your query based on a set of pre-defined code, so your database understands the instruction. We refer to this code as the query language.
The standard for database management is Structured Query Language (SQL). Remember, SQL is different from MySQL: the former is the query language, the latter is the software which uses the language. While it is true that SQL is the most popular choice among database software, it is definitely not the only one. Several others are AQL, Datalog, and DMX.
Regardless, these languages make database communication easy.
How Does Query Work?
Let’s say that you want to order an Americano at Starbucks. 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 gives meaning to the code used in any query language. Be it SQL or anything else, both the user and the database can always exchange information as long as they ‘speak’ the same language.
You may now think that placing a query is the only way to request data. In fact, quite a few database software options let you use other methods. The most popular ones are:
- Using available parameters
The software, by default, has lists of parameters on their menu. Users can choose one, and the system will then guide you to produce the desired output. It’s easy, but not flexible and offers limited operations.
- Query by example
The system will show you a set of code with some blank areas, in which you can write and specify the fields and values of your data.
- Query language
This is what we’ve been talking about. You have to write the queries from scratch whenever you want to manipulate data. This method requires understanding the query language used by your database software. Although it is complex, it gives you full control over your data.
Examples of A Query
Now that we know quite a bit about queries, why not try writing some? Please be aware that for this exercise we use SQL.
Data source: Participant (Table Name)
Let’s say that you collected some data from a survey. Below is a snippet of your data.
With SQL, you can write either SQL SELECT or SQL ACTION queries. As the name suggests, SQL SELECT lets you select parts of your data, and SQL ACTION enables you to perform action-oriented operations like deleting and inserting data, updating values, and creating new tables. Well, examples speak louder than words, let’s have a look:
- Selecting only the “Name” and “Occupation” columns from the “Participant” table.
SELECT Name, Occupation FROM Participant
Name Occupation John Student Peter Unemployed Margareth Teacher Lea Unemployed
- Deleting data from the unemployed respondents.
DELETE FROM Participant WHERE Occupation = ‘Unemployed’
ID Name Sex Age Occupation 1 John Male 17 Student 3 Margareth Female 34 Teacher
- Inserting a new row containing a participant called Mario.
INSERT INTO Participant (ID, Name, Sex, Age, Occupation) VALUES (‘5’, ‘Mario’, ‘Male’, ‘67’, ‘Retired’)
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 5 Mario Male 67 Retired
- Changing Margareth’s occupation to “Headmaster”.
UPDATE Participant SET Occupation = ‘Headmaster’ WHERE ID = ‘1’
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
Some SQL statements, like the ones above, are a solid testament to what a query language can do. It lets you deal with your data more effectively. Imagine if you have thousands of data rows. Manipulating them doesn’t have to be hard. Besides, most query languages are intuitive, meaning that they are easy to learn once you understand some basic rules. Let’s get our hands dirty with database queries and deal with our data like pros!