SQL for Beginners: A quick tutorial on how to use it

SQL's basic structure are tables (also known as "relationships"). Basically, a table is formed by columns and rows, very similar to the spreadsheets that you have probably already used in Microsoft Excel. Another important feature about SQL are relational databases and their ability to relate data between two or more tables, that is, create "relationships" between them. This is implemented through fields or columns with common values.

What is SQL used for?

It is through the SQL language that we can create tables, columns, indexes, assign permissions to users, and perform data queries. That is, SQL allows us to “talk” to the database.

This language is very important, as it facilitates the execution of tasks in Database Management Systems (DBMSs). In addition, your understanding is critical for anyone who wants to work in more technical areas that involve data analysis. Needless to say, in a data-driven world full of businesses that use large and relational databases, understanding SQL is a highly employable skill for everyone interested in the field. So, let's talk SQL. 

What is a query?

Queries are criteria that, when executed, create a list. Keep in mind that every query is based on criteria, for example: geographic data can be based on statistics on population. Therefore, for a list to be efficient, it needs to be dynamic, that is, change at the same speed as each new contact or information changes.

Queries are the exact representation of this evolution in the universe of database queries. Held in a database, they are intended to generate a contact list that is always updated without the need of human intervention.

Is SQL easy to learn?

Well, first you need to understand that SQL is a declarative language. This means that we have to worry less about how things are done and more about informing what we want to do, combining the set of commands provided by SQL, which are organised into sets.

This division is done according to the function of each command.

SQL command list

Now, you can see what these sets are, based on the most used examples. See the main SQL commands to use in your daily life.

Commands that deal with objects

DDL - Data Definition Language

A set of SQL language commands that deal with objects, creating databases, schemas, tables, fields, etc.

Amongst the most used, we can mention CREATE, which is intended for creating the database and tables that compose it; ALTER, which allows inserting and deleting attributes in existing tables; and the DROP command, used to drop the table definition, its data and references. Example:

CREATE TABLE students

-- Example of creating the students table
 

Commands that handle data

DML - Data Manipulation Language

Corresponds to commands that deal with data. The most common are INSERT, which inserts data into a table; the UPDATE, which has the function of updating a table; and DELETE, which can delete values. Examples:

INSERT INTO product (name, value)
 

VALUES('stove','1300')

-- This is an example of inserting values ​​into the product table

UPDATE table_name

SET column_name

WHERE condition;

-- This is an example of updating values ​​in a table

DELETE FROM table_name

WHERE condition;

-- This is an example of deleting values ​​in a table

DQL - Data Query Language

As you may have heard and will realise throughout your career, one of the most common activities in databases is the execution of queries. Therefore, understanding how to create them correctly is very important for the database and applications performance that depend on it. Execution of queries is done through a set of SELECT statements that returns values ​​from databases. Example:

SELECT name,

value FROM students

-- Example of querying students table values

The SELECT command is very functional. If used correctly, it will be able to bring the results already grouped, classified, calculated or detailed, from different data sources, or tables, in many different formats, without having to write a single line of code.

On the other hand, it can also be harmful and create serious performance problems if it is not written properly or if your database is not prepared to be able to run it the way you wrote it. Therefore, it is necessary to follow the best practices of using SELECT, so that you can use it in the creation of your queries with simplicity and guarantee the performance in its use.

New career paths with SQL 

It became clear that knowing how to make a query, understand how to use the language and even a SQL course is very important in your career, right? With it, we access and manipulate the records of a database, we can store and manipulate information so that they can be used in applications and also help in the creation of different solutions, from quick queries to simple or very complex systems.

Deepening your knowledge, you can develop autonomy and, consequently, consider new career paths in data. However, SQL can help you in other areas too, not only as a data analyst. Even if you are not a data analyst/scientist/engineer, you still have reasons to learn SQL to boost your career. Here is why:
 

1. It's intuitive and easy to learn
2. SQL is “everywhere”
3. Programming logic and SQL “go together”
4. Makes data access fast
5. Makes it easy to combine multiple data sources

With data in companies and also in the world growing exponentially, it is necessary to have professionals capable of using it. This is where SQL comes in as a bridge to significantly facilitate data access and provide actionable business insights, something that we strongly believe in.

Risks of unprotected databases

Now that we've learned a bit about SQL and its functions in databases, we need to talk about security. All the important information of an organisation is stored and organised in the database, making an effective database management system a vital operational element.

There are basically two ways to obtain the information that is stored in the database. The first is the data file that contains data and objects such as tables, indexes, views, and stored procedures. This file is stored in an operating system folder where the database was installed. The second way is to carry out information searches by directly accessing the database (technology users) or through an application (regular user). Therefore, it is necessary to protect the database in all its dimensions.

Access control

Access control is one of the main measures to maintain the security of a database and is under the responsibility of a DBA (Database Administrator). Preventing unauthorised people from gaining access to systems has become a challenge to be overcome by companies. Access to the database occurs through credentials (username and password) configured in the DBMS that can be associated with people and applications. For each of these credentials, a certain privilege is defined and, therefore, the correct definition of privilege and monitoring is essential.

Protecting your data file from ransomware attacks

Protecting the folder where the data file resides is currently one of the biggest concerns, as the criminal uses operating system vulnerabilities, configuration errors and corrupted credentials to access these files, encrypt it and subsequently demand ransom – a process known as a hacking attack. 

The most effective protection is to encrypt your database and ensure that only the database process (digitally signed) has access privileges. By the way, Vaultree is building the world’s first, strongest and fastest always-encrypted database processing toolkit. Using our technology, for instance, you'd be able to restrict access, any undue attempt is blocked and generates alerts for security teams. A Database server equipped with this technology is immune to Ransomware attacks because even if a malicious code ruins the server, access to the data file is blocked and the attempt generates security alerts and countermeasures.

Cryptographic Key Management

Most companies have several database systems and each has its own configuration, algorithms and facilities. However, the adverse characteristics are not the most serious point. Perhaps the main issue is the "segregation of roles" where each character has different responsibilities and thus avoiding unnecessary risks.

It is essential that the management of cryptographic keys is carried out outside of your system, ensuring that any user, even the most privileged, never has access to cryptographic keys. Other points of attention should be highlighted as characteristics of a centralised cryptographic key management system: compatibility with different platforms; ability to dynamically rotate cryptographic keys without the need for disruption or degradation; support operation in the organisation's facilities (Data Centre) and Cloud; low computational impact – processing, memory and storage; have strong certifications and use cases.

At Vaultree, we are developing the world’s fastest Fully Homomorphic & Searchable encryption technology. If you're interested in encrypting and processing all your data in an always-encrypted live production database environment with our SDK, book a demo.