INTRODUCTION TO SQL

#sql #mysql #postgres #database

Hello there 👋 ,

This is my DAY 1 creating blogs about sql and my journey in the world of data field. without further let's get started.

NOTE: All the topics that im gonna post here is base on what i've understanding and i've learned if you guys thinking that i'm lacking on some explanation in a certain topic feel free to comment down below.

Lesson content for today are:

What is SQL
SQL Coding Standards and Best Practices
Order of Execution of SQL

Now let's go with the first one.

WHAT IS SQL
SQL is a Structured Query Language. It's a programming language designed for manipulating relational database. So its totally different on NOSQL. it's also allow users to interact with databases by performing various operation such as retrieving data, save, edit, delete, in other term CRUD.

It's also good choice to choose this language when you are working with data role and you want to manipulate the data.

The SQL have many flavors or version but we are gonna discuss that in the other day.

Now that we know what is sql let's make an example how to write an sql.

Example
SELECT column_name FROM table_name

That's the example query of SQL
column_name: in this part where we gonna put the column name of the table that we want to select.
table_name: in this part is where we gonna call what's the table name we want to query.

It's like
SELECT id, name FROM Product

if you want to put more than 1 column name you need to put comma after the table name that you want to select.

Now that we are good to go and have some background knowledge what is sql. Were gonna discuss now the SQL Coding Standards and Best Practices

SQL Coding Standards and Best Practices
Now let's talk about SQL standards And Best Practices

Here's some best practices and standards

*Use consistent indention for readability *- meaning that we need to use indention when we're creating a SQL query to make more readable not one liner that if the query is long its hard to read it right?.

Example without indention:
select id, name, address, username, password, grade from user_info where status = 1;

Example with indention

select id
       name
       address
       username
       address
       password
       grade 
from user_info
where status = 1;

As you can see above it's good to use indention to make more the sql query easy to read and understand at glance compare to the first one without proper indention.

Capitalize SQL keywords for clarity - meaning that we need to capitalize the sql keywords to make it more clarity that that's a keyword and not a column name or what.

Example without Capitalize keywords:

select id
       name
       address
       username
       address
       password
       grade 
from user_info
where status = 1

Example with Capitalize keywords

SELECT id
       name
       address
       username
       address
       password
       grade 
FROM user_info
WHERE status = 1;

See the difference its good to use capitalization with combination of indention to make more the sql query looks readable.

Comments and Documentation - add comments in the sql query can make more the query understandable and well documented.

Example

SELECT id
       name  -- Name of the person
       address
       username
       address
       password
       grade 
FROM user_info
WHERE status = 1;  -- only fetch the user info with status of 1 or active

the -- is the comment for the sql language.

That are the some sample of best practices and standard coding when you are wring an SQL query if you what to know more or explore much deeper than this you can visit it in the SQL documentation.

Now that we know some of the standard and best practices in writing an sql query lets proceed now in the last topic which is:

Order of Execution of SQL

Order of execution refers to the sequence in which different part of an SQL query are processed by the database engine. Understanding this sequence can be crucial when writing efficient queries and comprehending how the database processes and returns results.

SELECT age, 
       COUNT(*) AS total_user
FROM user_account
WHERE status = 1
GROUP BY age,
ORDER BY total_user;

FROM: The query starts by identifying the table "user_account" from which data will be retrieved.
WHERE: The WHERE clause filters the rows from the "user_account" table where the status is equal to 1.
GROUP BY: The query then groups the filtered rows by the "age" column. This means that the rows with the same age will be aggregated together.
SELECT: Next, the SELECT clause is executed to select the "age" column and the count of rows for each age group. The COUNT(*) function calculates the total number of rows in each age group and aliases it as "total_user".
ORDER BY: Finally, the result set is sorted based on the "total_user" column in ascending order. This means that age groups with fewer users will appear first, and those with more users will appear later in the result set.

Now that you know all of this and have a glimpse of it you can more explore it on the documentation and try to apply it in your end.

If you have feedback or suggestion feel free to comment it below. see you in the next blog. THANKS