Learning basic SQL with me:
Tags: sql, beginners
Published: 2023-09-12 01:25:08

_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.



![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5ad0ispfrba6i95cwnxx.png)


---

## 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] (https://www.db-fiddle.com/). 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:
```sql
CREATE TABLE table_name ( 
columns_name data type
)
```
For the Student table example, it would be:

```sql
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:

```sql
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.

```sql
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] (https://www.db-fiddle.com/)
should be like this:



![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4w1szv3s7fjhe4wky6be.png)



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:

```sql

-- 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:


```sql

 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:


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/y1mqhku8f0334i75mjzb.png)


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

```sql

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](https://github.com/jnx257) :crying: and know more about me at [my X/twitter](https://twitter.com/kozu8uwu). Thanks a lot!


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

sources:

* https://www.sqltutorial.org/
* https://www.w3schools.com/sql/
and some other sources, sorry I don't remember D: