SQL is a query language that is needed by people working in a wide variety of fields such as data scientists, database specialists, mobile developers.
It is the 3rd most used programming language today (although not a programming language). The importance of SQL is known in almost every company.
In this article, I have listed the most important and useful SQL queries for beginners. From organizing data to collecting data, all the queries a beginner will need are in this article.
Introduction to SQL
Before we move on to the queries, let’s get some brief but important introductory information about SQL.
It is a query language that allows us to perform operations such as querying and manipulating SQL databases, yes it is not a programming language.
SQL is relatively easy to use and comes with most of the features you need. In short, SQL is your best friend when you want to call and organize your data.
1 – Creating Table Query
You can create data in SQL in 2 ways, either you export it as a csv file in media such as excel, mysql, or you create it manually with the Creating Table query.
Since we’re reviewing queries, in this section we’ll look at how to manually create a table on SQL.
CREATE TABLE name_of_table ( column1 datatype, column2 datatype, ... );
You can make yourself sample tables by setting the columns (with the data type) right after your table’s name.
CREATE TABLE users ( UserName varchar(20), UserAge int, UserPassword varchar(200), UserLevel float, );
Only the table is not enough, we need to add data into it, you can use tools such as MySQL, Oracle Database, or you can add with the code below.
INSERT INTO table or column VALUES value1, value2...;
2 – Select Query
With Select, you can observe the data you want, your data will be displayed according to the table and columns you specify.
In addition to this query, FROM is also used. FROM is used to specify which table to operate on.
-- Select All Columns & Rows SELECT * FROM table_name; -- Select Designated Location SELECT column1,column2 FROM table_name; -- Select Unprecedented Data SELECT DISTINCT column1 FROM table_name;
Select Distinct does not show more than 1 row with the same data, so only 1 of them will be printed if Germany is present in 100 rows.
3 – Where Query
It is used for conditional cases. It can be likened to a kind of if function or switch-case. It provides the desired action to be linked to a condition.
SELECT column1 FROM table_name WHERE condition;
It needs other queries to make it work. It will also be used with the examples in other sections, so Where is one of the most important constructs in SQL.
SELECT * FROM table WHERE UserName = "Markie";
TABLE: Jonathen|16|password2|34 Emma|19|password3|21 Alexa|22|password4|66 Markie|12|password1|14.0 OUTPUT: Markie|12|password1|14.0
4 – Update & Delete Queries
Although Update and Delete are separate queries, they are similar to each other. You can update the data with one and delete it with the other.
DELETE FROM table WHERE condition;
UPDATE table SET x = value, y = value2 WHERE condition;
The only common feature between them is that they work according to a condition with Where. You can delete or edit the data according to the condition you give.
-- Using Delete Query DELETE FROM table WHERE UserLevel = 65; -- Using Update Query UPDATE table SET UserName = "Mark",UserLevel = 0 WHERE Age = 12;
TABLE: Jonathen|16|password2|23 Emma|19|password3|53 Alexa|22|password4|28 Markie|12|password1|65.0 DELETE: Jonathen|16|password2|23 Emma|19|password3|53 Alexa|22|password4|28 UPDATE: Jonathen|16|password2|23 Emma|19|password3|53 Alexa|22|password4|28 Mark|12|password1|0.0
5 – Mathematic Queries
In this section, we will look at the most used mathematical queries, average, small, large, sum, mod, ABS, etc.
-- Min & Max Queries SELECT MIN(column) FROM table; SELECT MAX(column) FROM table; -- Sum & Average Queries SELECT SUM(column) FROM table; SELECT AVG(column) FROM table; -- Mod & ABS Queries SELECT MOD(x , y); SELECT ABS(x); -- SQRT & Power SELECT SQRT(x); SELECT POWER(x,y);
The basic math functions here will give you more comprehensive control over your dataset. In addition, SQL is faster than Python in operations such as data retrieval and filtering.
6 – Like Query to Searching
Like query is to search a specific column according to a condition, in short, it is used to search on columns.
SELECT * FROM table WHERE column LIKE "pattern";
The like query may sound a bit complicated, so let’s examine it on a real table. After examining it you will see that the Like query is simple.
%x = Find the values ending with x x% = Find the values starting with x %x% = Find the value with x in it
The expressions above allow you to search by typing in the pattern section, in short, you can find the data you want with the coding system here.
SELECT * FROM users WHERE UserName LIKE "%a"; SELECT * FROM users WHERE UserName LIKE "%j%";
TABLE: Markie|12|password1|65.0 Jonathen|16|password2|23.0 Emma|19|password3|53.0 Alexa|22|password4|28.0 %a search: Emma|19|password3|53.0 Alexa|22|password4|28.0 %j search: Jonathen|16|password2|23.0
7 – Order By & Group By
You can organize your data with the help of the Order query, create a separate table with Group By and see the data you want to examine as a group.
SELECT column1,column2... FROM table WHERE condition ORDER BY column1,column2;
data is organized according to the columns written next to Order By, it can be used with conditions.
SELECT column1,column2... FROM table WHERE condition GROUP BY column1,column2;
Now, let’s examine these two concepts by operating on the sample database. The database below will be used.
SELECT * FROM users ORDER BY UserLevel ASC; SELECT * FROM users ORDER BY UserLevel DESC; SELECT COUNT(UserName),Country FROM users GROUP BY Country;
Table: Markie|41|password1|65.0|UK Joon Seo|32|password2|23.0|KR Midori|13|password3|53.0|JP Alexa|22|password4|28.0|USA ORDER BY (ASC): Joon Seo|32|password2|23.0|KR Alexa|22|password4|28.0|USA Midori|13|password3|53.0|JP Markie|41|password1|65.0|UK ORDER BY (DESC): Markie|41|password1|65.0|UK Midori|13|password3|53.0|JP Alexa|22|password4|28.0|USA Joon Seo|32|password2|23.0|KR GROUP BY: 1|JP 1|KR 2|USA