SQL - Data Modeling

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:

$ mariadb –u root –p

MySQL access command:

$ mysql –u root –p

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:

MariaDB>

MySQL DBMS prompt:

MySQL>

Access to a database

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

$ mysql database_name

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:

MariaDB> USE database_name;

To exit we do:

MariaDB> quit

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}

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

# This is a comment
-- This is also a comment
/* This is a multi-line
comment */

Creating and deleting a database

The CREATE DATABASE statement creates a new database.

Example 2. Creating a database

CREATE DATABASE database_name;

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

Example 3. Deleting a database

DROP DATABASE database_name;

Creating tables

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

Syntax of the CREATE TABLE statement:

CREATE TABLE table_name (
  column1 data_type [column_options],
  column2 data_type [column_options],
  ...
  [table_options]
);

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

SELECT * FROM table_name;

To select specific columns:

SELECT column1, column2 FROM table_name;

ORDER BY

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

Example 5. Sorting

SELECT * FROM Employees ORDER BY LastName ASC;

SELECT DISTINCT

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

Example 6. Distinct values

SELECT DISTINCT City FROM Customers;

Aliases

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

Example 7. Column Alias

SELECT FirstName AS Name FROM Employees;

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

SELECT * FROM Products WHERE Price BETWEEN 10 AND 20;

Joining criteria with AND and OR

Example 9. Logic operators

SELECT * FROM Customers WHERE City='Madrid' AND PostalCode > 28000;

Combining multiple AND and OR

Use parentheses to group conditions.

Example 10. Combined logic

SELECT * FROM Employees WHERE (Job='Manager' AND Salary > 3000) OR Job='Director';

IN operator

Allows you to specify multiple values in a WHERE clause.

Example 11. Using IN

SELECT * FROM Customers WHERE Country IN ('Germany', 'France', 'UK');

The NULL value

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

Example 12. Checking NULLs

SELECT * FROM Books WHERE Price IS NULL;

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

SELECT * FROM Customers WHERE Name LIKE 'a%';

Comparing strings with LIKE and with =

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

Example 14. Binary comparison

SELECT BINARY 'à' = 'a'; -- Returns 0

SELECT statement with LIMIT clause

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

Example 15. Simple limit

SELECT * FROM Employees LIMIT 5;

Example 16. Limit with offset (skip 4, take 1)

SELECT * FROM Books ORDER BY Price DESC LIMIT 4, 1;

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

SELECT COUNT(*), Salary FROM Employees GROUP BY Salary;

Use of AVG and SUM with GROUP BY

SELECT AVG(Salary) FROM Employees GROUP BY Department;

Use of GROUP BY for more than one column

SELECT Department, Job, SUM(Salary) FROM Employees GROUP BY Department, Job;

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

SELECT COUNT(*) AS Num, Salary 
FROM Employees 
GROUP BY Salary 
HAVING Num > 1;

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

SELECT DATE_FORMAT(NOW(), "%d-%m-%Y");

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

SELECT Name FROM Products WHERE Price > (SELECT AVG(Price) FROM Products);

Subqueries and the IN operator

SELECT Name FROM Customers WHERE id IN (SELECT CustomerID FROM Orders);

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.

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

SELECT first_name, last_name 
FROM customers c 
JOIN orders o ON o.customer_id = c.id;

Multiple JOIN

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;

LEFT JOIN

SELECT c.Name, o.OrderID 
FROM Customers c 
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID;

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:

INSERT INTO table_name (col1, col2) VALUES (val1, val2);

Inserting rows with a SELECT statement

INSERT INTO Table2 SELECT * FROM Table1;

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.

SELECT MAX(Id)+1 INTO @next_id FROM MyTable;
INSERT INTO MyTable (Id, Name) VALUES (@next_id, 'Example');

UPDATE statement

Used to modify existing records.

UPDATE table_name 
SET column1 = value1 
WHERE condition;

DELETE statement

Used to delete records.

DELETE FROM table_name WHERE condition;