Learning basic SQL with me:

jenx — Tue, 12 Sep 2023 01:25:08 +0000

The main objective of this article is to learn basic SQL commands and the structure of SQL..

Okay, let's start with Wikipedia's definition of SQL:

SQL is a domain-specific language used in programming and designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS). It is particularly useful in handling structured data, i.e., data incorporating relations among entities and variables

So, basically, SQL is a language that we can use to access/manipulate databases..

RDBMS

RDBMS stands for Relational Database Management System. But what does it mean? SQL forms the foundation for all RDBMS systems, and nearly all technologies utilize SQL as their basis.

SQL continues to serve as a fundamental pattern, but now we employ various technologies that build upon SQL, each with its own distinct characteristics.

most used are:

MySQL: created by Oracle, It's a option that give us free and paid services;
PostgreSQL: most common In web, PostgreSQL is a Opensource software;
Oracle: know by your security, is most common in big companies ;
SQL Server: developed by Microsoft, got free versions and paid ones.

Consistences of SQL

So, we already know how data is managed in SQL. But it's important to understand what we can do with SQL. SQL can be divided into three main categories:

Data Definition Language (DDL): Deals with schema creation and modification. The CREATE TABLE statement allows you to create a new table in the database, and the ALTER TABLE statement changes the structure of an existing table.
Data Manipulation Language (DML): Provides constructs to query data, such as the SELECT statement, and to update data, such as INSERT, UPDATE, and DELETE statements.
Data Control Language (DCL): Consists of statements that deal with user authorization and security, such as GRANT and REVOKE statements.

Main commands in SQL:

Before we explore some commands, take a look at this website to test everything:

In this tutorial I will use DB Fiddle. DB-Fiddle supports various SQL databases, including MySQL, PostgreSQL, and SQLite, and provides a simple interface for testing SQL queries.

First thing that we will do in SQL will be a Student table with the following columns: (student_id, student_name, student_email, student_pw, student_birth)

The syntax for creating a table in SQL:

CREATE TABLE table_name ( 
columns_name data type
)

For the Student table example, it would be:

CREATE TABLE students (
student_id INT PRIMARY KEY,
/* you can divide our arguments in SQL by the column title and the data type
like in this first case we have:
 INT = integer
 PRIMARY KEY =  a constrain that says our data is unique values for each row in the table.
This ensures that no two rows can have the same value in the primary key column(s)
 */


-- btw this is a comment line in SQL, everything inside of it wont be read.
/* and this 
is a multi-line comment, I think its obvious the difference between those two.
*/ 
student_name VARCHAR(50), 
student_email VARCHAR(100),
student_password VARCHAR(255),
student_birth DATE
);

Once we have created our table in SQL, we need to add data to it to explore what's inside and manipulate this information. Let's do it:

INSERT INTO students (student_id, student_name, student_email, student_password, student_birth)
VALUES (1, 'Gabriel Galileo', 'gabriel.galileo@gmail.com', 'encrypted_password', "20-03-1997");

This is just a small example by adding one person called Gabriel Galileo to our database. You can copy this and add it to your own display on DB Fiddle.

INSERT INTO students (student_id, student_name, student_email, student_password, student_birth)
VALUES
    (1, 'Gabriel Galileo', 'gabriel.galileo@gmail.com', 'encrypted_password', '1997-03-20'),
    (2, 'Soren Kierkegaard', 'soren.kierke@gmail.com', 'another_password', '2002-05-25'),
    (3, 'Immanuel Kant', 'immanuel.kant@gmail.com', 'secure_password', '2003-03-10'),
    (4, 'Jesus Christ', 'godson@gmail.com', 'strong_password', '1970-11-25'),
    (5, 'Karl Marx', 'communist@gmail.com', 'good_password', '2008-01-25'),
    (6, 'Renato Cariani', 'renatao.monstro@gmail.com', 'password123', '1993-06-25'),
    (7, 'Aristoteles', 'aristoteles@gmail.com', 'idkmannnn', '1997-08-12');

After done previous commands the DB Fiddle
should be like this:

Now that we know how to create a table, add data to it, and query it, we have some basic SQL methods at our hands. Now, let's see how to manipulate the data we have created.

Imagine we made a mistake in the first student's name; it should be "Galileo Galilei," not "Gabriel Galilei." What should we do? Like this:


-- Basic SELECT query to retrieve all data from the students table
SELECT * FROM students;

-- Selecting specific columns
SELECT student_id, student_name FROM students;

-- Using WHERE clause to filter data
SELECT * FROM students WHERE student_birth < '2002-01-01';

After knowing how to create a table, adding data to it and querying it. We got in our hands some basic SQL Methods, but let's go some further and see how manipulate those data we have created.

Imagine we made a mistake in the first student's name; it should be "Galileo Galilei," not "Gabriel Galilei." What should we do? Like this:


 UPDATE students SET student_name = 'Galileu Galilei', student_email = "galileu.galilei@gmail.com" WHERE student_id = 1;

SELECT * FROM students WHERE student_id = 1;

and It's done, if we check in display has to be:

Crap... We added someone who isn't a student in our table, we got to delete this:


DELETE FROM students WHERE student_name = "Renato Cariani";
SELECT * FROM students;

yes, that's what I'm talking about.

Now we have covered the most basic commands in SQL. Of course, there is much more to learn, even at the beginner level. But I believe that the best way to improve your knowledge is by doing it. I will apply this knowledge in my login (API) application(which I've already apply a NoSQL DB {mongooDB}).

Please check it out in my github :crying: and know more about me at my X/twitter. Thanks a lot!

if you see any issue in this text pls text me! <3

sources: