Connect with us

Hi, what are you looking for?

Data Science

Most Used SQL Queries For Beginner

Most Used SQL Queries For Beginner
Most Used SQL Queries For Beginner

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
Thanks For Reading, Happy Coding!
Click to comment

Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like

Programming

Computers can solve an event in different ways, we call these ways algorithms, each algorithm works differently from the others and has different processing...

Data Science

SQL (Structured Query Language) is one of the most used tools by data scientists, data analysts, and data engineers. Almost all companies use a...

Data Science

Data science is a field of study that requires working with many tools. In this article, I have compiled the tools that will enable...

Programming

This article is for those who want to learn about databases, learn to manipulate data, get a quick introduction to SQL. What is Database?...