Root: Root moved page SQL DML to SQL - Data Modeling without leaving a redirect

2026-01-14T13:32:11Z

Root moved page SQL DML to SQL - Data Modeling without leaving a redirect

← Older revision	Revision as of 14:32, 14 January 2026
(No difference)

Root: Created page with "= Introduction = '''SQL''' (Structured Query Language): is a standard language for communication with relational databases. That is, a standardized language that allows working with the majority of relational databases. '''SQL''' can be used within other programming languages. The main characteristic of this language is its simplicity, as with little knowledge basic queries can be performed on a database, although it does not cease to be a complete language, both relati..."

2026-01-14T13:16:29Z

Created page with "= Introduction = '''SQL''' (Structured Query Language): is a standard language for communication with relational databases. That is, a standardized language that allows working with the majority of relational databases. '''SQL''' can be used within other programming languages. The main characteristic of this language is its simplicity, as with little knowledge basic queries can be performed on a database, although it does not cease to be a complete language, both relati..."

New page

= Introduction =

'''SQL''' (Structured Query Language): is a standard language for communication with relational databases. That is, a standardized language that allows working with the majority of relational databases. '''SQL''' can be used within other programming languages. The main characteristic of this language is its simplicity, as with little knowledge basic queries can be performed on a database, although it does not cease to be a complete language, both relationally and computationally.

The SQL language can be divided into three sets of instructions or statements according to their function:

* '''DDL'''-Data Definition Language: Definition statements, which allow creating, modifying, and deleting data structures.
* '''DML'''-Data Manipulation Language: Manipulation statements, which allow reading, inserting, modifying, and deleting records.
* '''DCL'''-Data Control Language: Control statements, which allow finalizing or rejecting transactions and authorizing or revoking user permissions.

Structured Query Language ('''SQL''') is a language for databases designed to manage data stored in a relational database management system. The first version of SQL was initially developed by IBM in the early 70s. In 86 it became an ANSI standard and in 87 an ISO standard. The initial version, called '''SEQUEL''' (Structured English Query Language), was designed to manipulate and extract data stored in IBM's quasi-relational database management system *System R*. Then, at the end of the 70s, the company *Relational Software Inc.*, which is now *Oracle Corporation*, introduced the first commercially available implementation of SQL: *Oracle V2* for VAX computers.

Many of the DBMS available today, such as Oracle Database, Microsoft SQL Server, MySQL, MariaDB, or PostgreSQL, use SQL.

Despite the standardization of SQL, each manufacturer uses various extensions and variants that make some adaptations necessary to move SQL code from one system to another.

In a DBMS, SQL is used to:

* Create the database and the tables: DDL
* Perform basic data management operations (addition, deletion, and modification): DML
* Perform simple or complex queries to select and order the stored data and transform them into useful information: DML

The data manipulation language of SQL (Data Manipulation Language, DML) is used to query and modify the database data. In this section, we will describe how the DML statements SELECT, INSERT, UPDATE, and DELETE are used, which are defined below:

* SELECT - to query data from the database
* INSERT - to insert data into a table
* UPDATE - to update data in a table
* DELETE - to delete data from a table

In a DML SQL statement:

* Each clause in a statement should start on a new line
* The beginning of each clause should align with the beginning of the other clauses
* If a clause has several parts, they should appear on separate lines and be indented under the beginning of the clause to show the relationship
* Uppercase letters are used to represent reserved words
* Lowercase letters are used to represent user-defined words

== Installation of software for the follow-up of the UF ==

Note: In this course, the DBMS MariaDB will be used with the WorkBench application to work with it.

=== Installation of the DBMS ===

Installation of the DBMS

* '''MariaDB''': is a branch of the MySQL database management system driven by the community, in order to maintain its free status under the GNU GPL, due to the uncertainty of the status of the MySQL license now that it is under the ownership of Oracle. Likewise, there is the intention to maintain high fidelity with MySQL, which guarantees a "drop-in" substitution capability due to the equivalence of the binary library and compatibility with the APIs and MySQL commands.

Documentation and download: [web link](https://mariadb.com/get-started-with-mariadb/)

==== Accessing and exiting MariaDB/MySQL ====

To access the DBMS: From the command line:

MariaDB access command:
<syntaxhighlight lang="bash">
$ mariadb –u root –p
</syntaxhighlight>

MySQL access command:
<syntaxhighlight lang="bash">
$ mysql –u root –p
</syntaxhighlight>

The parameter -u indicates the user, and -p indicates that it will request the password (you must have the path in the PATH, otherwise search for the BIN directory of MYSQL).

Note: If you have not modified it, the root password is empty.

If you manage to enter, it will appear as a prompt:

MariaDB DBMS prompt:
<syntaxhighlight lang="sql">
MariaDB>
</syntaxhighlight>

MySQL DBMS prompt:
<syntaxhighlight lang="sql">
MySQL>
</syntaxhighlight>

=== Access to a database ===

If we know the database we want to work with, we will do:

<syntaxhighlight lang="bash">
$ mysql database_name
</syntaxhighlight>

If instead we enter only with the mysql command, we will then have to execute the USE command to indicate which database we want to work with:

<syntaxhighlight lang="sql">
MariaDB> USE database_name;
</syntaxhighlight>

To exit we do:

<syntaxhighlight lang="sql">
MariaDB> quit
</syntaxhighlight>

=== Installation of the work tool ===

The recommended work tool is the DBeaver application, which is a universal and free database tool ([link](https://dbeaver.io/)).

==== Installation and use of the DBeaver application ====

Steps for installation:

1. Download and install DBeaver: [download link](https://dbeaver.io/download/)
2. Configure a connection:

{IMAGE_PLACEMENT}

=== Starting/Stopping the MySQL service ===

=== Possible problems accessing MariaDB ===

== Commands in MySQL/MariaDB and brief introduction to DDL (Data Definition Language) ==

=== Comments in SQL ===

Comments can be made on a single line by starting the line with the character "#" or "--" (two hyphens followed by a space).

Multi-line comments can also be made by starting with "/*" and ending with "*/".

Example 1. SQL Comments
<syntaxhighlight lang="sql">
# This is a comment
-- This is also a comment
/* This is a multi-line
comment */
</syntaxhighlight>

=== Creating databases and adding records with SQL in the MySQL/MariaDB environment ===

=== Creating and deleting a database ===

The CREATE DATABASE statement creates a new database.

Example 2. Creating a database
<syntaxhighlight lang="sql">
CREATE DATABASE database_name;
</syntaxhighlight>

The DROP DATABASE statement deletes an existing database and all its tables.

Example 3. Deleting a database
<syntaxhighlight lang="sql">
DROP DATABASE database_name;
</syntaxhighlight>

=== Creating tables ===

The CREATE TABLE statement is used to create a new table.

Syntax of the CREATE TABLE statement:
<syntaxhighlight lang="sql">
CREATE TABLE table_name (
column1 data_type [column_options],
column2 data_type [column_options],
...
[table_options]
);
</syntaxhighlight>

=== Data types ===

To define a table, we must specify the name of each column and its data type.

Common numeric types:
* TINYINT: Very small integer.
* INT: Normal size integer.
* FLOAT: Small decimal point number.
* DOUBLE: Large decimal point number.
* DECIMAL(p,s): Fixed point number.

Common string types:
* CHAR(n): Fixed length string.
* VARCHAR(n): Variable length string.
* TEXT: Large text blocks.

Common date types:
* DATE: Date (YYYY-MM-DD).
* TIME: Time (HH:MM:SS).
* DATETIME: Date and time.

=== Column options ===

Columns can have various modifiers:
* NOT NULL: The column cannot contain NULL values.
* UNIQUE: All values in the column must be different.
* PRIMARY KEY: Identifies the column as the primary key.
* AUTO_INCREMENT: The system automatically generates a unique number for each new record.
* DEFAULT value: Assigns a value if none is provided.

=== Table options ===

* ENGINE: Storage engine (e.g., InnoDB).
* DEFAULT CHARSET: Character set.
* COLLATE: Sorting rules.

= Querying data: SELECT statement =

== SELECT ==

The SELECT statement is used to select data from a database.

Example 4. Basic SELECT
<syntaxhighlight lang="sql">
SELECT * FROM table_name;
</syntaxhighlight>

To select specific columns:
<syntaxhighlight lang="sql">
SELECT column1, column2 FROM table_name;
</syntaxhighlight>

== ORDER BY ==

The ORDER BY keyword is used to sort the result-set in ascending or descending order.

Example 5. Sorting
<syntaxhighlight lang="sql">
SELECT * FROM Employees ORDER BY LastName ASC;
</syntaxhighlight>

== SELECT DISTINCT ==

The SELECT DISTINCT statement is used to return only distinct (different) values.

Example 6. Distinct values
<syntaxhighlight lang="sql">
SELECT DISTINCT City FROM Customers;
</syntaxhighlight>

== Aliases ==

SQL aliases are used to give a table, or a column in a table, a temporary name.

Example 7. Column Alias
<syntaxhighlight lang="sql">
SELECT FirstName AS Name FROM Employees;
</syntaxhighlight>

== SELECT statement with WHERE criteria ==

The WHERE clause is used to filter records.

=== Comparison operators for text strings ===

* = : Equal
* != or <> : Not equal

=== Comparison operators for numbers and dates ===

* = : Equal
* < : Less than
* > : Greater than
* <= : Less than or equal
* >= : Greater than or equal

=== Specifying ranges with BETWEEN ===

Example 8. Using BETWEEN
<syntaxhighlight lang="sql">
SELECT * FROM Products WHERE Price BETWEEN 10 AND 20;
</syntaxhighlight>

=== Joining criteria with AND and OR ===

Example 9. Logic operators
<syntaxhighlight lang="sql">
SELECT * FROM Customers WHERE City='Madrid' AND PostalCode > 28000;
</syntaxhighlight>

=== Combining multiple AND and OR ===

Use parentheses to group conditions.

Example 10. Combined logic
<syntaxhighlight lang="sql">
SELECT * FROM Employees WHERE (Job='Manager' AND Salary > 3000) OR Job='Director';
</syntaxhighlight>

=== IN operator ===

Allows you to specify multiple values in a WHERE clause.

Example 11. Using IN
<syntaxhighlight lang="sql">
SELECT * FROM Customers WHERE Country IN ('Germany', 'France', 'UK');
</syntaxhighlight>

=== The NULL value ===

IS NULL and IS NOT NULL are used to check for empty values.

Example 12. Checking NULLs
<syntaxhighlight lang="sql">
SELECT * FROM Books WHERE Price IS NULL;
</syntaxhighlight>

== Use of wildcards in the LIKE clause ==

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

* % - Represents zero, one, or multiple characters.
* _ - Represents a single character.

Example 13. Wildcards
<syntaxhighlight lang="sql">
SELECT * FROM Customers WHERE Name LIKE 'a%';
</syntaxhighlight>

=== Comparing strings with LIKE and with = ===

By default, string comparisons are case-insensitive. Use BINARY for case-sensitive matches.

Example 14. Binary comparison
<syntaxhighlight lang="sql">
SELECT BINARY 'à' = 'a'; -- Returns 0
</syntaxhighlight>

== SELECT statement with LIMIT clause ==

LIMIT is used to specify the number of records to return.

Example 15. Simple limit
<syntaxhighlight lang="sql">
SELECT * FROM Employees LIMIT 5;
</syntaxhighlight>

Example 16. Limit with offset (skip 4, take 1)
<syntaxhighlight lang="sql">
SELECT * FROM Books ORDER BY Price DESC LIMIT 4, 1;
</syntaxhighlight>

== SELECT statement with GROUP BY clause ==

The GROUP BY statement groups rows that have the same values into summary rows.

=== Use of COUNT with GROUP BY ===

<syntaxhighlight lang="sql">
SELECT COUNT(*), Salary FROM Employees GROUP BY Salary;
</syntaxhighlight>

=== Use of AVG and SUM with GROUP BY ===

<syntaxhighlight lang="sql">
SELECT AVG(Salary) FROM Employees GROUP BY Department;
</syntaxhighlight>

=== Use of GROUP BY for more than one column ===

<syntaxhighlight lang="sql">
SELECT Department, Job, SUM(Salary) FROM Employees GROUP BY Department, Job;
</syntaxhighlight>

=== Restriction on the use of GROUP BY ===

Columns in the SELECT clause must either be in the GROUP BY clause or used in an aggregate function.

== Restricting rows with HAVING ==

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

Example 17. Using HAVING
<syntaxhighlight lang="sql">
SELECT COUNT(*) AS Num, Salary
FROM Employees
GROUP BY Salary
HAVING Num > 1;
</syntaxhighlight>

= Chapter 3. Built-in functions =

== Aggregation functions ==

* COUNT(): Number of rows.
* SUM(): Total sum.
* AVG(): Average value.
* MIN(): Smallest value.
* MAX(): Largest value.

== Time and date functions ==

=== Obtaining parts of a date ===

* YEAR(), MONTH(), DAY(), HOUR(), MINUTE(), SECOND().

=== Obtaining the current time ===

* NOW(), CURDATE(), CURTIME().

=== Calculation of dates ===

* ADDDATE(), DATE_ADD(), SUBDATE(), DATE_SUB(), DATEDIFF().

=== Date format ===

<syntaxhighlight lang="sql">
SELECT DATE_FORMAT(NOW(), "%d-%m-%Y");
</syntaxhighlight>

== Mathematical functions ==

* ROUND(): Rounding.
* FLOOR(): Round down.
* POW(): Power.
* SQRT(): Square root.

== Hash and password functions ==

* MD5(): MD5 hash.
* SHA1(): SHA1 hash.

== String functions ==

* CONCAT(): Joins strings.
* CHAR_LENGTH(): String length.
* UPPER() / LOWER(): Case conversion.
* LEFT() / RIGHT(): Extracts characters.

== Execution control functions ==

* IFNULL(val, default): Returns default if val is NULL.
* COALESCE(list): Returns the first non-NULL value.

= Order of execution in a SELECT query =

The order of execution in an SQL query does not always follow the order in which the clauses appear in the written query. The typical execution order of an SQL query is as follows:

* '''FROM''': Specifies the tables from which the data is read.
* '''WHERE''': Filters the rows of the tables according to the specified condition.
* '''GROUP BY''': Groups the rows that meet the WHERE condition into categories based on one or more columns.
* '''HAVING''': Filters the categories generated by the GROUP BY according to the specified condition.
* '''SELECT''': Specifies the columns to be displayed in the query result; it can include aggregation functions such as MAX, SUM, etc.
* '''ORDER BY''': Sorts the result of the query based on one or more columns.
* '''LIMIT''': Limits the number of rows displayed in the result and, optionally, specifies the starting point from which they should be shown.

The order of execution is important because, for example, if we do not use a '''GROUP BY''' after using an aggregation function, we will not be able to use '''HAVING''', as it only acts upon the categories generated by the group.

In summary, in the '''HAVING''' clause, you can use the aliases created via aggregation functions (such as COUNT, MAX, etc.) in the '''SELECT''' clause for the filtering conditions that affect the categories generated by the '''GROUP BY'''.

= Subqueries =

A subquery is a query within another query.

== Subqueries with comparison operators ==

<syntaxhighlight lang="sql">
SELECT Name FROM Products WHERE Price > (SELECT AVG(Price) FROM Products);
</syntaxhighlight>

=== Subqueries and the IN operator ===

<syntaxhighlight lang="sql">
SELECT Name FROM Customers WHERE id IN (SELECT CustomerID FROM Orders);
</syntaxhighlight>

=== IN and LIMIT operators ===

== The ANY and ALL modifiers ==

Used to compare a value to any or all values in a set.

== Subqueries with EXISTS and NOT EXISTS ==

Tests for the existence of any record in a subquery.

== Subqueries in the SELECT clause ==

= Chapter 6. Joining tables - JOIN =

== INNER JOIN ==

Returns records that have matching values in both tables.

== OUTER JOIN ==

=== LEFT OUTER JOIN ===

Returns all records from the left table, and matched records from the right.

=== RIGHT OUTER JOIN ===

Returns all records from the right table, and matched records from the left.

=== FULL OUTER JOIN ===

Returns all records when there is a match in either left or right table.

== CROSS JOIN ==

Returns the Cartesian product of the two tables.

=== Examples of JOIN queries ===

==== JOIN ====
<syntaxhighlight lang="sql">
SELECT first_name, last_name
FROM customers c
JOIN orders o ON o.customer_id = c.id;
</syntaxhighlight>

==== Multiple JOIN ====
<syntaxhighlight lang="sql">
SELECT o.order_date, p.product_name
FROM products p
JOIN order_details od ON od.product_id = p.id
JOIN orders o ON od.order_id = o.id;
</syntaxhighlight>

==== LEFT JOIN ====
<syntaxhighlight lang="sql">
SELECT c.Name, o.OrderID
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID;
</syntaxhighlight>

== FROM of more than one table ==

Comma-separated tables in FROM clause act as a CROSS JOIN.

= Modification of data: INSERT, UPDATE and DELETE statements =

== INSERT statement ==

Used to insert new records in a table.

Syntax:
<syntaxhighlight lang="sql">
INSERT INTO table_name (col1, col2) VALUES (val1, val2);
</syntaxhighlight>

=== Inserting rows with a SELECT statement ===

<syntaxhighlight lang="sql">
INSERT INTO Table2 SELECT * FROM Table1;
</syntaxhighlight>

=== INSERT with auto-generated keys ===

Use LAST_INSERT_ID() to retrieve the ID of the last inserted row.

=== INSERT without auto-generated keys ===

Calculate the ID manually using variables.
<syntaxhighlight lang="sql">
SELECT MAX(Id)+1 INTO @next_id FROM MyTable;
INSERT INTO MyTable (Id, Name) VALUES (@next_id, 'Example');
</syntaxhighlight>

== UPDATE statement ==

Used to modify existing records.

<syntaxhighlight lang="sql">
UPDATE table_name
SET column1 = value1
WHERE condition;
</syntaxhighlight>

=== Inclusion of subqueries in an UPDATE statement ===

== DELETE statement ==

Used to delete records.

<syntaxhighlight lang="sql">
DELETE FROM table_name WHERE condition;
</syntaxhighlight>
[[Category:IT Databases]]

SQL - Data Modeling - Revision history

Root: Root moved page SQL DML to SQL - Data Modeling without leaving a redirect