What is SQL?

SQL (Structured Query Language) is a programming language standard used to manage relational databases. Therefore, it is important to understand what SQL is, as it is widely used in managed data, management and data analysis systems in companies around the world.

In this article, we will discuss the basic concepts of SQL, including its structure and syntax. Additionally, we will show the main commands and operators used to manage a database. We will also cover SQL data types, table creation and manipulation, advanced queries, and SQL security.

What is SQL? Starting with the basics

Very simply speaking, SQL is a language for the operator to “talk” to a database . Through the SQL language, an operator (or a system) can ask questions to the database.

For example: “select the 10 youngest employees in the company”. Or even, “insert a new employee into the database”.

Through SQL, the DBMS (database server) understands what is being requested, scans it, and returns the result to the operator.

We should not confuse SQL with MySQL. Although they are very related terms, SQL is the language, and MySQL is a database server system.

The term SQL, in addition to meaning “Structured Query Language”, also originated from the abbreviation “seek well”. Translated into Portuguese, it means “to search correctly”. – comments Gustavo Carvalho

Structure of an SQL query

The structure of an SQL query contains several parts that work together to return the desired data. The main parts of an SQL query include:

1. SELECT Command : We use the SELECT command to specify the columns that you want to return in the query results. Thus, the first part of an SQL query is followed by the FROM clause.
2. FROM Command : We use the FROM command to specify the source of data that you want to return in the query. In this sense, this could be a table, a view, or a subquery.
3. WHERE command : we use the WHERE command to filter the query results. In this way, we use it to exclude records that do not meet a certain criteria.
4. JOIN Command : We use the JOIN command to combine data from two or more tables into a single table. So this is useful when you want to get information from multiple tables in a single query.

Here is an example of a simple SQL query that uses all of these clauses:

SELECT name, age, city
FROM people
WHERE age > 18

Data types

In SQL, there are several data types used to store and manipulate information in a database. Each data type has a specific structure and properties that determine how the data should be stored and manipulated. Here are some of the most common SQL data types :

1. VARCHAR : We use VARCHAR data type to store strings or strings of characters. It can store any type of text, including uppercase and lowercase letters, semicolons, and other characters. The size of VARCHAR may vary depending on the database, but can generally store up to 8000 bytes.
2. INT : We use the INT data type to store integers. Where generally used to store codes or numbers that do not need to have decimal precision.
3. DATE : We use DATE data type to store dates and times. In this way, we generally use it to store dates of birth, dates of birth of a product, or any other specific date or time.
4. BOOLEAN : We use BOOLEAN data type to store true or false values. Thus, we generally apply it to indicate whether a condition is true or false, such as whether a user is active or not.

In addition to these data types, there are many other data types supported by SQL, such as FLOAT, REAL, DECIMAL, and BINARY. Therefore, each data type has its own specific properties and uses, and it is important to choose the appropriate data type for each column when you create a table.

Most common SQL operators

SQL operators are used to compare values ​​and perform operations in a SQL query. In this sense, they are essential for building effective queries and filtering data according to your specific requirements. Here are some of the most common SQL operators:

1. = Operator: We use the equality operator (=) to compare values ​​and determine if they are equal. For example, if you want to return all records where the “age” field equals 30, you can use the following query:

SELECT * FROM people WHERE age = 30;

2. < Operator : We use the less than operator (<) to compare values ​​and determine if one value is less than another. For example, if you want to return all records where the age is less than 30:

SELECT * FROM people WHERE age < 30;

3. > Operator : We use the greater than operator (>) to compare values ​​and determine whether one value is greater than another. For example, if you want to return all records where the age is greater than 30:

SELECT * FROM people WHERE age > 30;

4. <> Operator: The not equal to (<>) operator, also known as the inequality operator , which is used to compare values ​​and determine whether one value is different from another. For example, if you want to return all records where the age is other than 30:

SELECT * FROM people WHERE age <> 30;

5. BETWEEN Operator : The BETWEEN operator is used to specify a range of values ​​and return all records that satisfy that criteria. For example, if you want to return all records where the age is between 20 and 30:

SELECT * FROM people WHERE age BETWEEN 20 AND 30;

6. IN Operator: We use the IN operator to specify a list of values ​​and return all records that satisfy this criteria. For example, if you want to return all records where the city is “São Paulo” or “Rio de Janeiro”:

SELECT * FROM people WHERE city IN ('São Paulo', 'Rio de Janeiro');

Working with tables

To understand what SQL is, it is important to differentiate between tables and databases. Tables are the basis of all data stored in an SQL database. In this way, they are made up of columns and rows, and each column represents a category of data, such as name, age, address, etc. Thus, tables are created and managed using SQL syntax.

To create a table in SQL, you can use the following syntax:

CREATE TABLE table_name (
    column1 data_type,
    column2 data_type,
    column3 data_type,
    ...
);

For example, to create a “people” table with “name”, “age” and “address” columns, you can use the following query:

CREATE TABLE people (
    name VARCHAR(255),
    INT age,
    address VARCHAR(255)
);

In addition to creating a table, you can also manage tables using SQL. This includes defining predefined values ​​and integrity constraints.

To define predefined values, you can use the following syntax:

ALTER TABLE table_name ADD CONSTRAINT constraint_name
    CHECK (column_1 BETWEEN value_1 AND value_2);

For example, to add a pre-defined value restriction to the “age” column of the “people” table, you can use the following query:

ALTER TABLE people ADD CONSTRAINT age_restriction
    CHECK (age BETWEEN 18 AND 100);

Integrity constraints, you can use the following syntax:

ALTER TABLE table_name ADD CONSTRAINT constraint_name
    FOREIGN KEY (column) REFERENCES reference_table_name (reference_column);

For example, to add a foreign key constraint to the “city” column of the “people” table, which refers to the “name” column of the “states” table, you can use the following query:

ALTER TABLE people ADD CONSTRAINT foreign_key_city
    FOREIGN KEY (city) REFERENCES states(name);

In short, tables are the foundation of all data stored in an SQL database. They are made up of columns and rows, and each column represents a category of data. Thus, tables can be created and managed using SQL syntax, including defining predefined values ​​and integrity constraints.

Advanced queries

One of the most powerful features of SQL is the ability to perform advanced queries. Thus, these queries involve combining data from multiple tables, applying filters and sorts, and creating aggregations.

• JOIN

One of the most common advanced query techniques is the use of joins. Joins allow you to combine data from two or more tables based on a common column. Thus, there are several types of joins, such as INNER JOIN, OUTER JOIN and CROSS JOIN , each with its own purpose and use.

• Subqueries

Subqueries are queries embedded within a main query, allowing you to run a query within another query, which can make queries more complex and powerful.

• Queries with aggregations

Allow you to calculate and aggregate data into a table. In this sense, it includes creating counts, averages, standard deviations, percentages and other useful information and is widely used to create reports and data visualizations.

SQL Security

Security is a fundamental aspect of protecting data in a SQL database, it includes user authentication and authorization, which ensures that only authorized users have access to data.

• Authentication

Authentication is the process of verifying a user’s identity. SQL offers several authentication methods, such as using hashed passwords, security tokens, and certificates. Each method has its own advantages and disadvantages.

• Authorization

Authorization, on the other hand, is the process of controlling users’ access to data. Allowing database administrators to create users and set access permissions for each user, such as to read, write, update or delete data in a table, or across the entire database.

Additionally, it is important to ensure that data in an SQL database is protected from external attacks. Therefore, we use firewalls, data encryption and regular backups to ensure this protection.

Here is an example of how to configure user authentication and authorization in a SQL database using SQL Server:

-- Create user
CREATE USER [username] WITH PASSWORD = 'password';

-- Assign permissions to the user
GRANT ADMINISTER BULK OPERATIONS, CREATE SESSION, TABACCO
  TO [user_name];

-- Limit database access
ALTER ROLE db_datareader ADD MEMBER [username];
ALTER ROLE db_datawriter ADD MEMBER [username];

In this example, we create a user with the name “user_name” and a provided password. Permissions are then assigned to the user, allowing the user to perform batch operations, create a session, and take notes.

It is important to note that instructions and syntax may vary depending on the DBMS you are using. This example is for SQL Server, but other database management tools may have their own instructions. In this sense, you can learn much more with free SQL courses!