SQL

Raw

database.md

SQL

Structured Query Language

It is NOT a programming language, but a database query language.

How to pronounce it?

Both are widely accepted:

Sequel
- MySQL
- Ms SQL Server
Ess-Cue-Ell
- Oracle
- PostgreSQL

Note

Even though there is a standard for SQL: ANSI SQL

ANSI/ISO/IEC 9075:2003 "Database Language SQL"

each database engine implements its own dialect of SQL

Software to be used for the training

Demo database overview

Tables and relations

·-----------·    ·-------------·    ·--------·    ·-----------·
| TRAININGS |---<| ENROLLMENTS |>---| PEOPLE |>---| COMPANIES |
·-----------·    ·-------------·    ·--------·    ·-----------·

Data

People

select * from People;

Id	FirstName	LastName	IDNumber	Email	CompanyId
1	Iván	Osuna Ayuste	23000	ivan.osuna.ayuste@gmail.com
2	Mario	Novi Rod	296666	novirod@gmail.com	7
3	Juan	Garcia Garcia	2346800	Juan.Garcia@gmail.com
4	Mateusz Aleksander	Duda	3694485	mateusz.aleksander@gmail.com	2
5	Michael	Wilson	7777777	mcantos@gmail.com	1
6	Marta	Pérez Arribas	5844692	marta.perez.arribas@gmail.com
7	Vicenç	Côdina Soler	30000	vcs@gmail.com	3
8	Pedro	Oviedo Miedo	665444	Oviedo.Miedo@pepe.es	1
9	John	Spearman	77771177	mcantos@gmail.com	1
10	Marcelo	Buonera	2300120	primeragl@gmail.com	5
11	Mateo	Libra	71620135	bikes@gmail.com	2

Enrollments

select * from Enrollments;

TrainingId	PersonId	Date	Passed
1	1	2021-11-21	1
1	3	2022-12-30	1
1	4	2022-04-20	1
1	5	2021-10-23	1
1	6	2021-12-05	0
1	7	2022-04-23	1
1	11	2022-04-05	0
2	2	2021-10-30	1
4	4	2022-04-24	0
8	1	2021-12-31	1
10	2	2021-11-12	1
11	11	2021-05-12	1

Trainings

select * from Trainings;

Id	Name	Hours	Price
1	SQL Introduction	20	1000.05
2	Programming webapps	17	800.05
3	Python for dummies	60	1353.5
4	Advanced Python	50	8500.99
5	Advanced SQL	20	1000.05
6	Oracle SQL	50	995
7	PHP for dummies	35	2000.05
8	MS Access	20	500
9	Cobol	80	1000.05
10	SQL for data analytics	20	1000.05
11	MS Excel	30	3000

Companies

select * from Companies;

Id	Name	VATNumber
1	Womanpower	A12345678
2	Flower Power	B765098777
3	IBN	Y8968960
4	MatSoft	A69435871
5	Oracol	A12345678
6	Kyndryl	A6532653
7	Solero	B89101112

Database creation and population scripts

Database creation script

CREATE TABLE Trainings (
    Id SMALLINT NOT NULL PRIMARY KEY, 
    Name VARCHAR(100) NOT NULL,
    Hours SMALLINT NOT NULL,
    Price DOUBLE (6,2) NOT NULL
) ENGINE INNODB; 

CREATE TABLE Companies (
    Id SMALLINT NOT NULL PRIMARY KEY, 
    Name VARCHAR(100) NOT NULL,
    VATNumber CHAR(10) NOT NULL
) ENGINE INNODB;

CREATE TABLE People (
    Id INT NOT NULL PRIMARY KEY,
    FirstName VARCHAR(100) NOT NULL,
    LastName VARCHAR(100) NOT NULL,
    IDNumber INT NOT NULL UNIQUE,
    Email VARCHAR(100),
    CompanyId SMALLINT,
    FOREIGN KEY (CompanyId) REFERENCES Companies(Id)
) ENGINE INNODB;

CREATE TABLE Enrollments (
    TrainingId SMALLINT NOT NULL,
    PersonId INT NOT NULL,
    Date DATE NOT NULL,
    Passed BOOLEAN,
    PRIMARY KEY (TrainingId, PersonId),
    FOREIGN KEY (TrainingId) REFERENCES Trainings(Id),
    FOREIGN KEY (PersonId) REFERENCES People(Id)
) ENGINE INNODB;

CREATE INDEX IDX_Names ON People(FirstName);

Database population script

INSERT INTO Trainings (Id,Name,Hours,Price) 
    VALUES ( 1, 'SQL Introduction', 20, '1000.05');
INSERT INTO Trainings (Id,Name,Hours, Price) 
    VALUES ( 2, 'Programming webapps', 17, 800.05);
INSERT INTO Trainings (Id,Name,Hours, Price) 
    VALUES ( 3, 'Python for dummies', 60, 1353.50);
INSERT INTO Trainings (Id,Name,Hours, Price) 
    VALUES ( 4, 'Advanced Python', 50, 8500.99);
INSERT INTO Trainings (Id,Name,Hours, Price) 
    VALUES ( 5, 'Advanced SQL', 20, 1000.05);
INSERT INTO Trainings (Id,Name,Hours, Price) 
    VALUES ( 6, 'Oracle SQL', 50, 995);
INSERT INTO Trainings (Id,Name,Hours, Price) 
    VALUES ( 7, 'PHP for dummies', 35, 2000.05);
INSERT INTO Trainings (Id,Name,Hours, Price)
    VALUES ( 8 , 'MS Access' , 20 , 500.00 );
INSERT INTO Trainings (Id,Name,Hours, Price) 
    VALUES ( 9, 'Cobol', 80, 1000.05);
INSERT INTO Trainings (Id, Name,Hours, Price) 
    VALUES ( 10, 'SQL for data analytics', 20, 1000.05);
INSERT INTO Trainings (Id,Name,Hours, Price) 
    VALUES ( 11, 'MS Excel', 30, 3000.00);

INSERT INTO Companies (Id,Name, VATNumber) 
    VALUES ( 1, 'Womanpower', 'A12345678');
INSERT INTO Companies (Id,Name, VATNumber) 
    VALUES ( 2, 'Flower Power', 'B765098777');
INSERT INTO Companies (Id,Name, VATNumber) 
    VALUES ( 3, 'IBN', 'Y8968960');
INSERT INTO Companies (Id,Name, VATNumber) 
    VALUES ( 4, 'MatSoft', 'A69435871');
INSERT INTO Companies (Id,Name, VATNumber) 
    VALUES ( 5, 'Oracol', 'A12345678');
INSERT INTO Companies (Id,Name, VATNumber) 
    VALUES ( 6, 'Kyndryl', 'A6532653');
INSERT INTO Companies (Id,Name, VATNumber) 
    VALUES ( 7, 'Solero', 'B89101112');

INSERT INTO People (Id,FirstName, LastName, IDNumber, Email, CompanyId) 
    VALUES ( 1, 'Iván', 'Osuna Ayuste', 23000, 'ivan.osuna.ayuste@gmail.com', NULL );
INSERT INTO People (Id,FirstName, LastName, IDNumber, Email, CompanyId) 
    VALUES ( 2, 'Mario', 'Novi Rod', 00296666, 'novirod@gmail.com', 7);
INSERT INTO People (Id,FirstName, LastName, IDNumber, Email) 
    VALUES ( 3, 'Juan', 'Garcia Garcia', 2346800, 'Juan.Garcia@gmail.com');
INSERT INTO People (Id,FirstName, LastName, IDNumber, Email, CompanyId) 
    VALUES ( 4, 'Mateusz Aleksander', 'Duda', 3694485, 'mateusz.aleksander@gmail.com', 2);
INSERT INTO People (Id,FirstName, LastName, IDNumber, Email, CompanyId) 
    VALUES ( 5, 'Michael', 'Wilson', 7777777, 'mcantos@gmail.com', 1);
INSERT INTO People (Id,FirstName, LastName, IDNumber, Email, CompanyId) 
    VALUES ( 6, 'Marta', 'Pérez Arribas', 05844692, 'marta.perez.arribas@gmail.com', NULL);
INSERT INTO People (Id,FirstName, LastName, IDNumber, Email, CompanyId) 
    VALUES ( 7, 'Vicenç', 'Côdina Soler', 30000, 'vcs@gmail.com', 3);
INSERT INTO People (Id,FirstName, LastName, IDNumber, Email, CompanyId)
    VALUES ( 8 , 'Pedro' , 'Oviedo Miedo' , 665444, 'Oviedo.Miedo@pepe.es' , 1 );
INSERT INTO People (Id, FirstName, LastName, IDNumber, Email, CompanyId) 
    VALUES ( 9, 'John', 'Spearman', 77771177, 'mcantos@gmail.com', 1);
INSERT INTO People (Id, FirstName, LastName, IDNumber, Email, CompanyId) 
    VALUES ( 10, 'Marcelo', 'Buonera', 2300120, 'primeragl@gmail.com', 5);
INSERT INTO People (Id,FirstName, LastName, IDNumber, Email, CompanyId) 
    VALUES ( 11, 'Mateo', 'Libra', 71620135, 'bikes@gmail.com', 2);

INSERT INTO Enrollments (TrainingId, PersonId, Date, Passed) 
    VALUES (1, 1, '2021-11-21', 1);
INSERT INTO Enrollments (TrainingId, PersonId, Date, Passed) 
    VALUES (2, 2, '2021-10-30', 1);
INSERT INTO Enrollments (TrainingId, PersonId, Date, Passed) 
    VALUES (1, 3, '2022-12-30', 1);
INSERT INTO Enrollments (TrainingId, PersonId, Date, Passed) 
    VALUES (1, 4, '2022-04-20', 1);
INSERT INTO Enrollments (TrainingId, PersonId, Date, Passed) 
    VALUES (4, 4, '2022-04-24', 0);
INSERT INTO Enrollments (TrainingId, PersonId, Date, Passed) 
    VALUES (1, 5, '2021-10-23', 1);
INSERT INTO Enrollments (TrainingId, PersonId, Date, Passed) 
    VALUES (1, 6, '2021-12-05', 0);
INSERT INTO Enrollments (TrainingId, PersonId, Date, Passed) 
    VALUES (1, 7, '2022-04-23', 1);
INSERT INTO Enrollments (TrainingId, PersonId, Date, Passed)
    VALUES (8 , 1 , '2021-12-31' , 1 );
INSERT INTO Enrollments (TrainingId, PersonId, Date, Passed) 
    VALUES (10, 2, '2021-11-12', 1);
INSERT INTO Enrollments (TrainingId, PersonId, Date, Passed) 
    VALUES (11, 11, '2021-05-12', 1);
INSERT INTO Enrollments (TrainingId, PersonId, Date, Passed) 
    VALUES (1, 11, '2022-04-05', 0);

Raw

queriesI.md

Queries

Simple data query

SELECT FirstName, LastName FROM People;

SELECT Name FROM Trainings;

SELECT 
  People.FirstName
FROM
  People
;

Qualifing column names

SELECT 
 Students.FirstName
FROM
 People AS Students
;

Using alias

SELECT 
 Name AS Company,
 VATNumber AS TaxesId
FROM 
 Companies;

Wildcards

SELECT * FROM Companies;

Filtering data

SELECT 
 PersonId,
 Passed
FROM 
 Enrollments
WHERE 
 -- Passed IS TRUE 
 -- Passed IS NOT TRUE 
 Passed IS FALSE 
;

More filters

SELECT 
 Id, 
 FirstName,
 LastName, 
 CompanyId
FROM 
 People
WHERE
 CompanyId IS NOT NULL -- Value cannot be empty
 -- CompanyId IS NULL -- Value must be empty
 -- Name NOT IN ("Ivan", "Manuel") -- None of them       
 -- OR Name IN ("Ivan", "Manuel") -- Alternative condition. Any of them
 -- AND Name IN ("Ivan", "Manuel") -- Additional condition
 -- Email = "mateusz.aleksander@gmail.com"  -- Equals
 -- Email != "mateusz.aleksander@gmail.com" -- Not equals / Different
 -- Email <> "mateusz.aleksander@gmail.com" -- ot equals / Different
 -- Name > "Ivan" -- Alpha order > < >= <=
 -- ID > 7 -- Numeric comparison
 -- ID BETWEEN 3 AND 9
 -- ID NOT BETWEEN 3 AND 9 
;

Joining tables

SELECT
  People.FirstName AS Employee,
  Companies.Name AS Company
FROM 
  People INNER JOIN Companies 
           ON People.CompanyId = Companies.Id;

SELECT
  People.FirstName AS Employee,
  Companies.Name AS Company
FROM 
  People,
  Companies
WHERE
  People.CompanyId = Companies.Id;

Right outer Join

Additionally, include companies with no employees

SELECT
  People.FirstName AS Employee,
  Companies.Name AS Company
FROM 
  People RIGHT JOIN Companies 
           ON People.CompanyId = Companies.Id;

Left outer Join

Additionally, include people not assigned to any company

SELECT
  People.FirstName AS Employee,
  Companies.Name AS Company
FROM 
  People LEFT JOIN Companies 
           ON People.CompanyId = Companies.Id;

Raw

queriesII.md

Query:

Then name (fN+lN) of our students
Then name of the training they performed or will perform

Tables involved?

People
Enrollment
Trainings

Type of JOIN:

Inner Join -> 2 different syntax: SAME THING
Outer Join

SELECT 
	People.FirstName,
	People.LastName,
	Trainings.Name
FROM
	People,
    Enrollments,
    Trainings
WHERE 
	People.Id = Enrollments.PersonId AND
    Trainings.Id = Enrollments.TrainingId
;

SELECT
  Trainings.Name,
  People.FirstName
FROM
  Trainings 
  INNER JOIN Enrollments ON Enrollments.TrainingId = Trainings.Id
  INNER JOIN People ON Enrollments.PersonId = People.Id
;

Query:

In addition, the name of the trainings with no enrollments

SELECT
  Trainings.Name,
  People.FirstName
FROM
  Trainings LEFT OUTER JOIN Enrollments 
      ON Enrollments.TrainingId = Trainings.Id
  LEFT JOIN People ON Enrollments.PersonId = People.Id
;

Query:

Trainings without enrollments

SELECT
  Trainings.Name
FROM
  Trainings 
  LEFT OUTER JOIN Enrollments 
      ON Enrollments.TrainingId = Trainings.Id
WHERE 
  Enrollments.PersonId IS NULL
;

QUERY USING FUNCTIONS: CASE | IFNULL | CONCAT

Students, their trainings and their companies (if any)

-- List :
-- - Name of the students
-- - Name of the training enrolled
-- - Their company's name (only if employees.. if not, that's ok, null)

SELECT 
   -- Companies.Name AS Company,

   CASE
   	WHEN Companies.Name IS NULL THEN "None"
       ELSE Companies.Name
   END AS Company,
   
   IFNULL(Companies.Name, "None") as Company2,
   CONCAT(People.FirstName," ",People.LastName) AS Student,
   Trainings.Name AS Training,
   CASE 
   	WHEN Trainings.Price < 1000 THEN "Category 1"
   	WHEN Trainings.Price < 2000 THEN "Category 2"
       ELSE "Category 3"
   END AS TrainingCategory
   
FROM
   Companies
   RIGHT OUTER JOIN People ON Companies.Id = People.CompanyId
   INNER JOIN Enrollments ON Enrollments.PersonId = People.Id
   -- Do we need enrollments with no person? That's weird NO ...
   -- Do we need people wothout enrollments... NO
   INNER JOIN Trainings ON Trainings.Id = Enrollments.TrainingId
   -- Do we need enrollments without training? That's weird NO ...
   -- Do we need traings without enrollments... NO    
;

QUERY DISTINCT

Name of students with enrollments

SELECT DISTINCT
   CONCAT( People.FirstName, " ", People.LastName ) AS Student
FROM
   People,
   Enrollments
WHERE 
   People.Id = Enrollments.PersonId
;

QUERY GROUP BY | SUMMARY

Name of students with enrollments

SELECT
   CONCAT( People.FirstName, " ", People.LastName ) AS Student,
   COUNT(*) AS Trainings
FROM
   People,
   Enrollments
WHERE 
   People.Id = Enrollments.PersonId
GROUP BY 
   Student
;
-- MAX, MIN, COUNT, AVG

The name our customers (companies)
And the number of trainings (including companies with no enrollments)

-- The name our companies (clients)
-- And the number of trainings (including companies with no enrollments)

-- Tables
-- - Companies
--     LEFT OUTER JOIN
-- - People
--     LEFT OUTER JOIN
-- - Enrollments

SELECT
   Companies.Name,
--    People.Id,
   Count(Enrollments.TrainingId) as NumberOfTrainings
--    Enrollments.TrainingId
FROM
   Companies
   LEFT OUTER JOIN (
     -- People having enrollments
     People INNER JOIN Enrollments ON People.Id = Enrollments.PersonId
   ) ON Companies.Id = People.CompanyId
GROUP BY 
   Companies.Name;

QUERY GROUP BY | HAVING

Name of students with enrollments

SELECT
   CONCAT( People.FirstName, " ", People.LastName ) AS Student,
   COUNT(*) AS Trainings
FROM
   People,
   Enrollments
WHERE 
   People.Id = Enrollments.PersonId
GROUP BY 
   Student
HAVING 
   COUNT(*) > 1
;

QUERY Text functions

SELECT 
 TRIM(People.FirstName), -- Remove whitespaces
 LTRIM(People.FirstName), 
 RTRIM(People.FirstName), 
 LENGTH(People.FirstName),
 SUBSTRING(People.FirstName,2,4),
 CONCAT("Hola ", People.FirstName) 
FROM 
 People
WHERE
 -- UPPER(People.FirstName) LIKE UPPER('I%')
 -- LOWER(People.FirstName) LIKE LOWER('I%')
 People.FirstName LIKE "I_a%"

QUERY Date functions

SELECT 
 DATE_FORMAT(Enrollments.Date ,"%d/%m/%Y"),
 DATE_FORMAT(Enrollments.Date ,"%d"), -- TEXT
 DAY(Enrollments.Date), -- NUMBER
 MONTH(Enrollments.Date),
 YEAR(Enrollments.Date),
 HOUR(Enrollments.Date),
 MINUTE(Enrollments.Date),
 SECOND(Enrollments.Date),
 NOW(),
 SYSDATE()
FROM 
 Enrollments
WHERE 
 Enrollments.Date >= STR_TO_DATE("01-01-2022","%d-%m-%Y")
;

-- %d day of month
-- %m month
-- %y year 2 digits
-- %Y year 4 digits
-- %h 0-12
-- %p AM PM
-- %H 0-23
-- %i minutos %M
-- %s segundos

SELECT 
	People.FirstName,
	People.LastName,
    Trainings.Name,
    Enrollments.Date
FROM
	Trainings
	INNER JOIN Enrollments ON Trainings.Id = Enrollments.TrainingId
	INNER JOIN People ON People.Id = Enrollments.PersonId
WHERE 
 	MONTH(Enrollments.Date) = MONTH(NOW())
    AND YEAR(Enrollments.Date) = YEAR(NOW())
ORDER BY 
	Enrollments.Date DESC, Trainings.Name ASC

IvanciniGT/database.md

Select an option

No results found

Select an option

No results found

SQL

How to pronounce it?

Note

Software to be used for the training

Demo database overview

Tables and relations

Data

People

Enrollments

Trainings

Companies

Database creation and population scripts

Queries

Simple data query

Qualifing column names

Using alias

Wildcards

Filtering data

More filters

Joining tables

Right outer Join

Left outer Join

Query:

Query:

Query:

QUERY USING FUNCTIONS: CASE | IFNULL | CONCAT

QUERY DISTINCT

QUERY GROUP BY | SUMMARY

QUERY GROUP BY | HAVING

QUERY Text functions

QUERY Date functions