Tutorial consultas SQL
Exportar y cargar base de datos
Una vez que ya tenemos listo nuestro diagrama (ocuparemos como referencia el ejemplo de aviones del Auxiliar 2). A continuación el modelo ER:
Debemos exportarlo usando Database → Forward Engineering
En lo primero que se abre, al centro (en Parameters), vamos a Password y elegimos Store in Vault. Ahí debemos ingresar nuestra contraseña. Luego damos a Siguiente dejando todo por defecto. Cuando llegamos al script (en la sección Review SQL Script), en todas las líneas en que aparezca el comando VISIBLE debemos borrarlo (siempre aparece cuando se declara un INDEX). Ejemplo:
CREATE TABLE IF NOT EXISTS `Fabrica`.`Avion` (
...
INDEX `fk_Avion_Modelo_idx` (`Modelo_codigo` ASC) VISIBLE,
UNIQUE INDEX `codigo_UNIQUE` (`codigo` ASC) VISIBLE,
...Luego se debe crear nuestra base de datos.
Código para crear la base de datos:
-- MySQL Workbench Forward Engineering
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
-- -----------------------------------------------------
-- Schema Fabrica
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema Fabrica
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `Fabrica` DEFAULT CHARACTER SET utf8 ;
USE `Fabrica` ;
-- -----------------------------------------------------
-- Table `Fabrica`.`Modelo`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Fabrica`.`Modelo` (
`codigo` VARCHAR(45) NOT NULL,
`cap_min` INT NOT NULL,
`cap_max` INT NOT NULL,
PRIMARY KEY (`codigo`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `Fabrica`.`Avion`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Fabrica`.`Avion` (
`codigo` INT NOT NULL,
`Modelo_codigo` VARCHAR(45) NOT NULL,
`capacidad` INT NOT NULL,
`precio` INT NOT NULL,
PRIMARY KEY (`codigo`),
INDEX `fk_Avion_Modelo_idx` (`Modelo_codigo` ASC),
UNIQUE INDEX `codigo_UNIQUE` (`codigo` ASC),
CONSTRAINT `fk_Avion_Modelo`
FOREIGN KEY (`Modelo_codigo`)
REFERENCES `Fabrica`.`Modelo` (`codigo`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `Fabrica`.`Cliente`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Fabrica`.`Cliente` (
`id` INT NOT NULL,
`contacto` VARCHAR(45) NOT NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `Fabrica`.`Venta`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Fabrica`.`Venta` (
`Avion_codigo` INT NOT NULL,
`Cliente_id` INT NOT NULL,
`fecha entrega` VARCHAR(45) NOT NULL,
PRIMARY KEY (`Avion_codigo`, `Cliente_id`),
INDEX `fk_Avion_has_Cliente_Cliente1_idx` (`Cliente_id` ASC),
INDEX `fk_Avion_has_Cliente_Avion1_idx` (`Avion_codigo` ASC),
CONSTRAINT `fk_Avion_has_Cliente_Avion1`
FOREIGN KEY (`Avion_codigo`)
REFERENCES `Fabrica`.`Avion` (`codigo`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Avion_has_Cliente_Cliente1`
FOREIGN KEY (`Cliente_id`)
REFERENCES `Fabrica`.`Cliente` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;Poblar base de datos
Con el comando INSERT INTO se empieza a poblar la base de datos. Abriremos MySQL Command Line Client. Nos pedirá la contraseña y a continuación podremos acceder a la CLI.
Con el comando SHOW DATABASES; (no olvidar el “;”) listamos todas las bases de datos de nuestro servidor local:
Elegimos la db fábrica (usted pueden ponerle otro nombre cuando crean el schema en el paso anterior). Luego listamos todas las tablas con SHOW TABLES;
Ahora podemos comenzar a hacer consultas SQL. Sin embargo, la base no está poblada con datos así que no retornará nada.
Insertaremos los siguientes datos para evitar poder realizar consultas:
#Populate Modelo
INSERT INTO Modelo
VALUES ('A10', 50, 80),
('A22', 150, 250),
('A33', 250, 440),
('A33-0', 250, 440);
# Populate avion
INSERT INTO avion (codigo, Modelo_codigo, capacidad, precio)
VALUES
(1000, 'A33-0', 320, 1500000),
(1001, 'A33-0', 320, 1500000),
(1002, 'A33', 350, 1800000),
(1003, 'A22', 215, 1300000),
(1004, 'A10', 70, 1000000),
(1005, 'A10', 70, 1000000),
(1006, 'A10', 70, 1000000);
# Populate Clients
INSERT INTO Cliente
VALUES (1, 56982457620), (2, 56955554444), (3, 56912345678);
# Populate ventas
INSERT INTO Venta
VALUES (1002, 2, '10/4/2022'),
(1001, 2, '10/4/2022'),
(1003, 1, '25/6/2022'),
(1005, 3, '7/7/2022');Con el comando INSERT INTO tabla VALUES (value1), (value2), (value3); podemos insertar (en este ejemplo) 3 filas a la tabla “tabla”.
Una vez insertados los datos, podemos hacer una consulta simple. Pedirle todas las filas a una tabla, en este caso a Avion.
Consultas
Aplicando las consultas del Aux 2 obtenemoslos siguientes resultados:
a) Los nombres de los modelos de aviones con un precio mayor que 1500000.
SELECT codigo
FROM Avion
WHERE precio>1500000;
b) Los precios de los aviones que tienen una capacidad menor que la capacidad mínima de su modelo o mayor que la capacidad máxima de su modelo.
SELECT precio
FROM Avion A, Modelo M
WHERE A.codigo=M.codigo AND (capacidad > cap_max OR capacidad < cap_min);
c) Debe devolver el id del cliente y el precio del avión más caro que compra, ordenado de mayor a menor.
SELECT Cliente_id, MAX(A.precio)
FROM Cliente C, Venta V, Avion A
WHERE A.codigo=V.Avion_codigo
GROUP BY Cliente_id
ORDER BY precio DESC;
d) Para cada Modelo, el conteo de aviones asociados con ese modelo. Debe devolver el nombre del modelo y el conteo. Además, debe ordenar los resultados por conteo (descendente).
SELECT M.codigo, COUNT(M.codigo) as Conteo
FROM Modelo M LEFT JOIN Avion ON M.codigo = Avion.Modelo_codigo
GROUP BY Modelo_codigo
ORDER BY Conteo DESC;