Structured Query Language (SQL)| Full Explanation (Definition, usage, structure and example with MySQL)

Structured Query Language (SQL)| Full Explanation (Definition, usage, structure and example with MySQL)

Introduction

Hello, my name is Daniela Barazarte and I want to welcome you to this complete explanation of SQL. This explanation will be intuitive and simple as well as all the explanations that are part of #DetectaLaLogica.

If you prefer videos, here is a full tutorial made by me on YouTube, it's in Spanish but it also has subtitles: https://youtu.be/Hjs-zGEQtg8

Theory

Definition of each word

Structured Query Language (SQL) or Structured Query Language

• Language: refers to the set of rules and syntax used to write programs that a computer can understand.
• Structured: means to organize the code of a program in smaller and more logical pieces, so that it is easier to understand and maintain.
• Query: refers to a specific search for information in a database.

Full definition

SQL is a programming language used to manage and manipulate structured and relational databases using queries.

#DetectTheLogic

SQL is called Structured Query Language (Structured Query Language) because it is literally a language that allows us to query a structured database and obtain results

Use

So that

It is used to manage and manipulate relational databases, either to create, modify, delete, or view tables, objects, and records in a database.

When and where

• E-commerce websites
• Content management applications
• Social media apps
• Data analysis applications
• Project management applications
• Booking and appointment apps

As

You need a relational database management system that supports SQL (MySQL, Oracle, Microsoft SQL Server, etc.) Then, you must use the commands

Practice

Exercise

You have a web application for an online store that sells electronics. You must store and manage order information.

Method: SQL

-- Create the order table
CREATE TABLE orders (
   id INT AUTO_INCREMENT PRIMARY KEY,
   client VARCHAR(50) NOT NULL,
   date DATE NOT NULL,
   product VARCHAR(50) NOT NULL,
   amount INT NOT NULL,
   price DECIMAL(10,2) NOT NULL
);

-- Create orders
INSERT INTO orders (customer, date, product, quantity, price)
VALUES
   ('Juan Perez', '2023-06-16', 'Red shirt', 2, 20.50),
   ('Ana Garcia', '2023-06-15', 'Blue pants', 1, 35.00),
   ('Luis Gomez', '2023-06-15', 'Green dress', 2, 40.00),
   ('Maria Hernandez', '2023-06-14', 'Bob Grapefruit Socks', 12, 5.80),
   ('Carlos Rodriguez', '2023-06-14', 'Jacket for the cold', 1, 75.50),
   ('Laura Martinez', '2023-06-13', 'Blue Blouse', 3, 30.20),
   ('Pedro Sanchez', '2023-06-13', 'Cowboy Jeans', 6, 50.00),
   ('Sofia Ramirez', '2023-06-12', 'Sweatshirt for exercise', 2, 25.80),
   ('Diego Torres', '2023-06-12', 'Crocodile Cap', 1, 12.80),
   ('Fernanda Castro', '2023-06-11', 'Coat', 2, 90.00);

-- Read all orders
SELECT * FROM orders;

-- Read order by ID
SELECT * FROM orders WHERE id = 3;

-- Edit/Update Order
UPDATE orders SET quantity = 4, price = 80.00 WHERE id = 5;

-- Delete order
DELETE FROM orders WHERE id IN (2, 6, 8);

Importance

SQL has several benefits:

• It is easy to learn and use.
• It is very efficient to manipulate and access data.
• It is flexible and can handle both simple and complex queries.
• It is scalable, it works with small or very large databases.
• It is a de facto standard, widely compatible between different database systems.
• Once you master it, you can apply your skills to multiple database platforms.

Farewell

(Remember) #DetectaLaLógica: SQL is called Structured Query Language (Structured Query Language) because it is literally a language that allows us to query a structured database and obtain results

You can practice this topic in my GitHub repository (C# language code): https://github.com/danielabarazarte/DetectaLaLogica

Thank you very much for reading, if you have any questions you can comment and you can also follow me to see more posts of this style, thanks <3.