¡Practiquemos las consultas SQL con MySQL! (I de IV)

Hola amig@s, ¿qué hay de nuevo? ¡Ya sois más de 100 personas las que me seguís en Twitter! Muchas gracias por seguirme. Os cuento que, a petición de un@ de vosotr@s, en la siguiente serie de posts voy a tratar el tema de las consultas SQL con MySQL: consultas simples, consultas resumen, consultas multitabla (los JOINS), subconsultas, etc. La idea es disponer de estos posts para que cuando no recordéis exactamente cómo iba una consulta, podáis venir aquí, a la sección MySQL de programarivm.com, y copypastear dicha consulta.

Voy a tratar este tema muy prácticamente, principalmente poniendo código MySQL, pero tambíen escribiré una explicación introductoria, when necessary, para que podamos recordar cómo iba tal o cual cláusula (WHERE, FROM, HAVING, etc.). En concreto, crearemos una BBDD de ejemplo y empezaremos a lanzar diferentes consultas que se irán complicando poco a poco.

Personalmente, hacer este ejercicio me viene muy bien porque últimamente me he acostumbrado a trabajar con las bases de datos en el plano abstracto, comunicándome con la BBDD desde una capa modelo ya programada en mis aplicaciones. Pero eso es otra historia…

Bueno chic@s, allá va sin más preámbulos la base de datos de ejemplo sobre la que vamos a trabajar nuestros ejercicios MySQL. Are you ready?

– ¡No os oigo bien!, chic@s. Are you readyyyy?

– ¿Sí? ¡Pues allá va!:

CREATE DATABASE company;
 
USE company;
 
CREATE TABLE departments (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    number TINYINT UNSIGNED NOT NULL,
    name VARCHAR(32) NOT NULL,
    location VARCHAR(32) NOT NULL,
    PRIMARY KEY(id)
) ENGINE=InnoDB;
 
CREATE TABLE employees (
    id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
    number MEDIUMINT UNSIGNED NOT NULL,
    name VARCHAR(32) NOT NULL,
    job VARCHAR(32) NOT NULL,
    hire_date DATETIME NOT NULL,
    salary SMALLINT UNSIGNED NOT NULL,
    commission SMALLINT UNSIGNED NULL,
    id_employee MEDIUMINT UNSIGNED NULL,
    id_department SMALLINT UNSIGNED NOT NULL,
    PRIMARY KEY(id),
    FOREIGN KEY(id_employee) REFERENCES employees(id),
    FOREIGN KEY(id_department) REFERENCES departments(id)
) ENGINE=InnoDB;
 
INSERT INTO departments(number, name, location)
VALUES
(101, 'Cuentas', 'Madrid'),
(76, 'I+D', 'Sevilla'),
(25, 'Ventas', 'Barcelona'),
(88, 'E-commerce', 'Barcelona');
 
INSERT INTO employees(number, name, job, hire_date, salary, commission, id_employee, id_department)
VALUES
(1, 'Julián', 'Director general', '2004-11-10', 4000, NULL, NULL, 1),
(10, 'Pepa', 'Jefe de cuentas', '2004-11-10', 3500, 300, 1, 1),
(11, 'Juan', 'Director técnico', '2004-11-25', 3600, NULL, 1, 2),
(12, 'Marta', 'Jefa de ventas', '2004-11-19', 2500, 500, 1, 3),
(13, 'Ignacio', 'Director e-commerce', '2004-11-10', 2300, 700, 1, 4),
(88, 'Carlos', 'Administrativo', '2004-12-11', 1200, NULL, 2, 1),
(89, 'Silvia', 'Contable', '2004-12-11', 1300, NULL, 2, 1),
(90, 'María', 'Administrativa', '2004-12-11', 1200, NULL, 2, 1),
(91, 'Pedro', 'Contable', '2004-12-11', 1300, NULL, 2, 1),
(92, 'Teresa', 'Ingeniera', '2004-12-11', 2100, NULL, 3, 2),
(93, 'Isabel', 'Ingeniera Técnica', '2004-12-28', 1800, NULL, 3, 2),
(94, 'Javier', 'Ingeniero Técnico', '2004-12-11', 1800, NULL, 3, 2),
(95, 'Fernando', 'Ingeniero', '2004-12-11', 2100, NULL, 3, 2),
(96, 'Laura', 'Comercial', '2004-12-30', 2100, 500, 4, 3),
(97, 'Montse', 'Comercial', '2004-12-27', 1800, 600, 4, 3),
(100, 'Jorge', 'Comercial', '2004-12-30', 1800, 500, 4, 3),
(102, 'Arturo', 'Comercial', '2004-12-12', 2100, 550, 4, 3),
(105, 'Juan', 'Programador', '2004-12-11', 1800, NULL, 5, 4),
(109, 'Pepe', 'Programador', '2005-01-01', 1700, NULL, 5, 4),
(110, 'Juan', 'SEO', '2005-01-01', 2000, 200, 5, 4),
(112, 'Pepe', 'Analista', '2005-01-01', 1700, NULL, 5, 4);

CREATE DATABASE company;

USE company;

CREATE TABLE departments (

id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,

number TINYINT UNSIGNED NOT NULL,

name VARCHAR(32) NOT NULL,

location VARCHAR(32) NOT NULL,

PRIMARY KEY(id)

) ENGINE=InnoDB;

CREATE TABLE employees (

id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,

number MEDIUMINT UNSIGNED NOT NULL,

name VARCHAR(32) NOT NULL,

job VARCHAR(32) NOT NULL,

hire_date DATETIME NOT NULL,

salary SMALLINT UNSIGNED NOT NULL,

commission SMALLINT UNSIGNED NULL,

id_employee MEDIUMINT UNSIGNED NULL,

id_department SMALLINT UNSIGNED NOT NULL,

PRIMARY KEY(id),

FOREIGN KEY(id_employee) REFERENCES employees(id),

FOREIGN KEY(id_department) REFERENCES departments(id)

) ENGINE=InnoDB;

INSERT INTO departments(number, name, location)

VALUES

(101, 'Cuentas', 'Madrid'),

(76, 'I+D', 'Sevilla'),

(25, 'Ventas', 'Barcelona'),

(88, 'E-commerce', 'Barcelona');

INSERT INTO employees(number, name, job, hire_date, salary, commission, id_employee, id_department)

VALUES

(1, 'Julián', 'Director general', '2004-11-10', 4000, NULL, NULL, 1),

(10, 'Pepa', 'Jefe de cuentas', '2004-11-10', 3500, 300, 1, 1),

(11, 'Juan', 'Director técnico', '2004-11-25', 3600, NULL, 1, 2),

(12, 'Marta', 'Jefa de ventas', '2004-11-19', 2500, 500, 1, 3),

(13, 'Ignacio', 'Director e-commerce', '2004-11-10', 2300, 700, 1, 4),

(88, 'Carlos', 'Administrativo', '2004-12-11', 1200, NULL, 2, 1),

(89, 'Silvia', 'Contable', '2004-12-11', 1300, NULL, 2, 1),

(90, 'María', 'Administrativa', '2004-12-11', 1200, NULL, 2, 1),

(91, 'Pedro', 'Contable', '2004-12-11', 1300, NULL, 2, 1),

(92, 'Teresa', 'Ingeniera', '2004-12-11', 2100, NULL, 3, 2),

(93, 'Isabel', 'Ingeniera Técnica', '2004-12-28', 1800, NULL, 3, 2),

(94, 'Javier', 'Ingeniero Técnico', '2004-12-11', 1800, NULL, 3, 2),

(95, 'Fernando', 'Ingeniero', '2004-12-11', 2100, NULL, 3, 2),

(96, 'Laura', 'Comercial', '2004-12-30', 2100, 500, 4, 3),

(97, 'Montse', 'Comercial', '2004-12-27', 1800, 600, 4, 3),

(100, 'Jorge', 'Comercial', '2004-12-30', 1800, 500, 4, 3),

(102, 'Arturo', 'Comercial', '2004-12-12', 2100, 550, 4, 3),

(105, 'Juan', 'Programador', '2004-12-11', 1800, NULL, 5, 4),

(109, 'Pepe', 'Programador', '2005-01-01', 1700, NULL, 5, 4),

(110, 'Juan', 'SEO', '2005-01-01', 2000, 200, 5, 4),

(112, 'Pepe', 'Analista', '2005-01-01', 1700, NULL, 5, 4);

La información de las tablas queda así:

mysql> SELECT * FROM departments;
+----+--------+------------+-----------+
| id | number | name       | location  |
+----+--------+------------+-----------+
|  1 |    101 | Cuentas    | Madrid    |
|  2 |     76 | I+D        | Sevilla   |
|  3 |     25 | Ventas     | Barcelona |
|  4 |     88 | E-commerce | Barcelona |
+----+--------+------------+-----------+
4 rows in set (0.00 sec)
 
mysql> SELECT * FROM employees;
+----+--------+----------+---------------------+---------------------+--------+------------+-------------+---------------+
| id | number | name     | job                 | hire_date           | salary | commission | id_employee | id_department |
+----+--------+----------+---------------------+---------------------+--------+------------+-------------+---------------+
|  1 |      1 | Julián   | Director general    | 2004-11-10 00:00:00 |   4000 |       NULL |        NULL |             1 |
|  2 |     10 | Pepa     | Jefe de cuentas     | 2004-11-10 00:00:00 |   3500 |        300 |           1 |             1 |
|  3 |     11 | Juan     | Director técnico    | 2004-11-25 00:00:00 |   3600 |       NULL |           1 |             2 |
|  4 |     12 | Marta    | Jefa de ventas      | 2004-11-19 00:00:00 |   2500 |        500 |           1 |             3 |
|  5 |     13 | Ignacio  | Director e-commerce | 2004-11-10 00:00:00 |   2300 |        700 |           1 |             4 |
|  6 |     88 | Carlos   | Administrativo      | 2004-12-11 00:00:00 |   1200 |       NULL |           2 |             1 |
|  7 |     89 | Silvia   | Contable            | 2004-12-11 00:00:00 |   1300 |       NULL |           2 |             1 |
|  8 |     90 | María    | Administrativa      | 2004-12-11 00:00:00 |   1200 |       NULL |           2 |             1 |
|  9 |     91 | Pedro    | Contable            | 2004-12-11 00:00:00 |   1300 |       NULL |           2 |             1 |
| 10 |     92 | Teresa   | Ingeniera           | 2004-12-11 00:00:00 |   2100 |       NULL |           3 |             2 |
| 11 |     93 | Isabel   | Ingeniera Técnica   | 2004-12-28 00:00:00 |   1800 |       NULL |           3 |             2 |
| 12 |     94 | Javier   | Ingeniero Técnico   | 2004-12-11 00:00:00 |   1800 |       NULL |           3 |             2 |
| 13 |     95 | Fernando | Ingeniero           | 2004-12-11 00:00:00 |   2100 |       NULL |           3 |             2 |
| 14 |     96 | Laura    | Comercial           | 2004-12-30 00:00:00 |   2100 |        500 |           4 |             3 |
| 15 |     97 | Montse   | Comercial           | 2004-12-27 00:00:00 |   1800 |        600 |           4 |             3 |
| 16 |    100 | Jorge    | Comercial           | 2004-12-30 00:00:00 |   1800 |        500 |           4 |             3 |
| 17 |    102 | Arturo   | Comercial           | 2004-12-12 00:00:00 |   2100 |        550 |           4 |             3 |
| 18 |    105 | Juan     | Programador         | 2004-12-11 00:00:00 |   1800 |       NULL |           5 |             4 |
| 19 |    109 | Pepe     | Programador         | 2005-01-01 00:00:00 |   1700 |       NULL |           5 |             4 |
| 20 |    110 | Juan     | SEO                 | 2005-01-01 00:00:00 |   2000 |        200 |           5 |             4 |
| 21 |    112 | Pepe     | Analista            | 2005-01-01 00:00:00 |   1700 |       NULL |           5 |             4 |
+----+--------+----------+---------------------+---------------------+--------+------------+-------------+---------------+
21 rows in set (0.00 sec)
 
mysql>

mysql> SELECT * FROM departments;

+----+--------+------------+-----------+

+----+--------+------------+-----------+

| 1 | 101 | Cuentas | Madrid |

| 2 | 76 | I+D | Sevilla |

| 3 | 25 | Ventas | Barcelona |

| 4 | 88 | E-commerce | Barcelona |

+----+--------+------------+-----------+

4 rows in set (0.00 sec)

mysql> SELECT * FROM employees;

+----+--------+----------+---------------------+---------------------+--------+------------+-------------+---------------+

+----+--------+----------+---------------------+---------------------+--------+------------+-------------+---------------+

| 1 | 1 | Julián | Director general | 2004-11-10 00:00:00 | 4000 | NULL | NULL | 1 |

| 2 | 10 | Pepa | Jefe de cuentas | 2004-11-10 00:00:00 | 3500 | 300 | 1 | 1 |

| 3 | 11 | Juan | Director técnico | 2004-11-25 00:00:00 | 3600 | NULL | 1 | 2 |

| 4 | 12 | Marta | Jefa de ventas | 2004-11-19 00:00:00 | 2500 | 500 | 1 | 3 |

| 5 | 13 | Ignacio | Director e-commerce | 2004-11-10 00:00:00 | 2300 | 700 | 1 | 4 |

| 6 | 88 | Carlos | Administrativo | 2004-12-11 00:00:00 | 1200 | NULL | 2 | 1 |

| 7 | 89 | Silvia | Contable | 2004-12-11 00:00:00 | 1300 | NULL | 2 | 1 |

| 8 | 90 | María | Administrativa | 2004-12-11 00:00:00 | 1200 | NULL | 2 | 1 |

| 9 | 91 | Pedro | Contable | 2004-12-11 00:00:00 | 1300 | NULL | 2 | 1 |

| 10 | 92 | Teresa | Ingeniera | 2004-12-11 00:00:00 | 2100 | NULL | 3 | 2 |

| 11 | 93 | Isabel | Ingeniera Técnica | 2004-12-28 00:00:00 | 1800 | NULL | 3 | 2 |

| 12 | 94 | Javier | Ingeniero Técnico | 2004-12-11 00:00:00 | 1800 | NULL | 3 | 2 |

| 13 | 95 | Fernando | Ingeniero | 2004-12-11 00:00:00 | 2100 | NULL | 3 | 2 |

| 14 | 96 | Laura | Comercial | 2004-12-30 00:00:00 | 2100 | 500 | 4 | 3 |

| 15 | 97 | Montse | Comercial | 2004-12-27 00:00:00 | 1800 | 600 | 4 | 3 |

| 16 | 100 | Jorge | Comercial | 2004-12-30 00:00:00 | 1800 | 500 | 4 | 3 |

| 17 | 102 | Arturo | Comercial | 2004-12-12 00:00:00 | 2100 | 550 | 4 | 3 |

| 18 | 105 | Juan | Programador | 2004-12-11 00:00:00 | 1800 | NULL | 5 | 4 |

| 19 | 109 | Pepe | Programador | 2005-01-01 00:00:00 | 1700 | NULL | 5 | 4 |

| 20 | 110 | Juan | SEO | 2005-01-01 00:00:00 | 2000 | 200 | 5 | 4 |

| 21 | 112 | Pepe | Analista | 2005-01-01 00:00:00 | 1700 | NULL | 5 | 4 |

+----+--------+----------+---------------------+---------------------+--------+------------+-------------+---------------+

21 rows in set (0.00 sec)

mysql>

Categorías