SQL for management

Many times we have been in complicated situations in which we need information from our database of urgent data and we do not have the necessary support from the technicians and analysts of the computing. Generally, company executives and management have very precarious knowledge about the SQL language, which leaves them at a disadvantage in these situations. In this section we will try to give a brief explanation about this language and how it works and can be used for data management.

The structured query language (SQL) is a Normalized database language composed of commands, clauses, operators, and aggregate functions. These elements are combined in the instructions for creating, updating, and manipulating the databases. The types of commands that interest us for the purpose of this post is the DML that allow generating queries to sort, filter and extract data from the database.

DML commands:

• SELECT: Used to query records from the database that satisfy a certain criterion.
• INSERT: Used to load batches of data into the database in a single operation.
• UPDATE: Used to load batches of data into the database in a single operation.
• DELETE: Used to delete records from a table in a database.

Of these commands the most important is the SELECT since it will allow us to obtain the information that is required from the database. The other three commands are critical as misuse of them can cause serious database results. For example with UPDATE Y DELETE an important record for the business such as a sale can be eliminated.

Apart from the commands DML we have the clauses that are modification conditions used to define the data that you want to select or manipulate.

Clauses:

• DESDE: Used to specify the table from which the records are to be selected
• WHERE: Used to specify the conditions that the records to be selected must meet.
• GROUP BY: Used to separate selected records into specific groups
• HAVING: Used to express the condition that each group must satisfy
• ORDER BY: Used to sort the selected records according to a specific order

Logical operators:

• AND: It is the logical "and". Evaluates two conditions and returns a true value only if both are true.
• OR: It is the logical "o". Evaluates two conditions and returns a value of true if either is true.
• NOT: Logical negation. Returns the opposite value of the expression.

Comparison Operators:

• <: Smaller than
• >: Greater than
• <>: Other than
• <=: Less than or Same as
• >=: Greater than or equal
• =: Like
• BETWEEN: Used to specify a range of values.
• LIKE: Used when comparing a model
• IN: Used to specify records from a database

Aggregate functions:

• AVG: Used to calculate the average of the values ​​of a given field
• COUNT: Used to return the record number of the selection.
• SUM: Used to return the sum of the values ​​of a field
• MAX: Used to return the highest value of a specified field.
• MIN: Used to return the lowest value of a specified field.

This has been just a glimpse of everything we can do with SQL. To delve into the topics see our other related posts.