SQL stands for (Structured Query Language) in the Vietnamese sense of Structured Query Language: is a programming language for storing and processing information in relational databases.
First of all, I would like to say hello to everyone, have a good day at work and study. In this article, I will summarize some basic SQL queries, suitable for all basic/non-IT users. SQL is very important in Tester work, so if you are about to start learning Tester, knowledge of SQL is indispensable. Let’s learn SQL for Testers!
1. What is SQL? Illustrated examples in life
SQL stands for (Structured Query Language) in the Vietnamese sense of Structured Query Language: is a programming language for storing and processing information in relational databases. By definition, you can imagine when your lover asks you to go to the supermarket to buy fish sauce, the problem that appears here is that there are too many types of fish sauce, which are Phu Quoc fish sauce, Nam Ngu fish sauce, etc. Ba Mien fish sauce…So to find the right bottle of fish sauce for your lover, what should you do? To solve this problem, let’s think of all kinds of fish sauce as a table called “Fish sauce”. This table has attributes to display such as: name, NSX, year of manufacture, HSD, capacity…. So the request of your lover, for example:
“I went to the supermarket to buy you a bottle of fish sauce.” Switch to SQL meaning:
1 2 | SELECT * FROM NUOCMAM |
“I went to the supermarket to buy you a bottle of Phu Quoc fish sauce” . Switch to SQL meaning:
1 2 3 | SELECT * FROM NUOCMAM WHERE TEN = 'PHU QUOC' |
2. SELECT
So through the above example, then SELECT in SQL is the keyword used to get data from one or more tables in the database. The syntax of SELECT is as follows:
1 2 3 4 5 6 | SELECT column1, column2, ... FROM tablename WHERE condition; |
In there:
column1 , column2 , … is the list of columns that you want to select data from.
tablename is the name of the table you want to retrieve data from.
WHERE is a condition to filter out the data to be retrieved. If there is no WHERE condition, all rows in the table are selected.
3. WHERE
WHERE in SQL is used to filter the records in the data table. It allows you to specify a condition and retrieve only records that satisfy that condition. Understanding in practice through the example of going to buy fish sauce, when you need to find a specific object or group of objects with the same conditions, I will use WHERE.
You can use WHERE with conditions such as AND means and , OR means or . For example:
“Phu Quoc fish sauce and Anchovy fish sauce”
1 2 | WHERE TEN = 'PHU QUOC' AND TEN = 'CA COM' |
“Production month July or August”
1 2 | WHERE MONTH(NGAYSANXUAT) = 7 OR MONTH(NGAYSANXUAT) = 8 |
4. Calculation functions in SQL
4.1 AVERAGE FUNCTION
The AVG function in SQL is used to calculate the average value of a numeric column in a data table. The statement and example of the AVG function are as follows:
Command: SELECT AVG(columnname) FROM tablename
Example: Suppose we have a Students table with a Score property. Ask to find the average score of the student.
1 2 3 | SELECT AVG(score) FROM students |
4.2 SUM FUNCTION
SUM in SQL is a function that sums the values of a column in a data table.
Statement: SELECT SUM(column_name) FROM table_name
Example: Suppose we have a Products table with a Price property. Ask to find the total price of the products.
1 2 | SELECT SUM(price) FROM products |
4.3 MAX & MIN FUNCTION
In SQL, we can use two functions MAX and MIN to find the maximum or minimum value in a set of values.
Statement: SELECT MAX(column_name) FROM table_name
Example: Suppose we have an Employees table with a Salary attribute. Ask to find the employee with the highest salary.
1 2 | SELECT MAX(salary) FROM employees |
Statement: SELECT MIN(column_name) FROM table_name
Example: Suppose we have an Employees table with an Age attribute. Ask to find the youngest employee.
1 2 | SELECT MIN(age) FROM employees |
5. Summary
In this article, I mentioned some of the most basic things about SQL, in the next articles I will update more statements from basic to advanced and situations to illustrate. Learning SQL was quite difficult for me to begin with, but after understanding the nature and persistence, I figured out the meaning of the query imagining it with reality, I improved a lot. Through this article, I wish you good luck with your studies, and have a smooth first step.
NOTE : In SQL, it is not case sensitive
Finally, I would like to thank everyone who read the article!