Bases de datos

Y todavía más SQL

El diagrama fue hecho en ERD+ con esta especificación

El diagrama fue hecho en dbdiagram con esta especificacion

Siempre pueden revisarlo en línea

SQL

Vamos a crear una base de datos nueva...

CREATE DATABASE `aseguradora_2`
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_spanish_ci;

USE DATABASE `aseguradora_2`;

SQL

Y a establecer su estructura


--
-- Estructura de tabla para la tabla `accidente`
--

CREATE TABLE `accidente` (
  `codigo` varchar(255) COLLATE utf8mb4_spanish_ci NOT NULL,
  `fecha_hora` datetime NOT NULL,
  `lugar` varchar(255) COLLATE utf8mb4_spanish_ci NOT NULL,
  `descripcion` text COLLATE utf8mb4_spanish_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `multa`
--

CREATE TABLE `multa` (
  `numero` int(12) NOT NULL,
  `fecha_hora` datetime NOT NULL,
  `lugar` varchar(255) COLLATE utf8mb4_spanish_ci DEFAULT NULL,
  `monto` decimal(10,2) NOT NULL,
  `descripcion` text COLLATE utf8mb4_spanish_ci DEFAULT NULL,
  `vehiculo_numero_serie` varchar(255) COLLATE utf8mb4_spanish_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `nacionalidad`
--

CREATE TABLE `nacionalidad` (
  `clave` varchar(4) COLLATE utf8mb4_spanish_ci NOT NULL,
  `nombre` varchar(255) COLLATE utf8mb4_spanish_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `persona`
--

CREATE TABLE `persona` (
  `numero_licencia` varchar(20) COLLATE utf8mb4_spanish_ci NOT NULL,
  `nombre` varchar(255) COLLATE utf8mb4_spanish_ci NOT NULL,
  `apellido` varchar(255) COLLATE utf8mb4_spanish_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `persona_involucrada_accidente`
--

CREATE TABLE `persona_involucrada_accidente` (
  `persona_numero_licencia` varchar(20) COLLATE utf8mb4_spanish_ci NOT NULL,
  `accidente_codigo` varchar(255) COLLATE utf8mb4_spanish_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `persona_nacionalidad`
--

CREATE TABLE `persona_nacionalidad` (
  `persona_numero_licencia` varchar(20) COLLATE utf8mb4_spanish_ci NOT NULL,
  `nacionalidad_clave` varchar(4) COLLATE utf8mb4_spanish_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `vehiculo`
--

CREATE TABLE `vehiculo` (
  `numero_serie` varchar(255) COLLATE utf8mb4_spanish_ci NOT NULL,
  `marca` enum('ford','volkswagen','seat','audi','toyota','nissan','renault') COLLATE utf8mb4_spanish_ci NOT NULL,
  `modelo` enum('ford_bronco','ford_focus','ford_ranger','volkswagen_taos','volkswagen_tiguan','volkswagen_nivus','seat_leon','seat_cordoba','seat_ibiza','audi_a1','audi_a3','audi_a7','toyota_camry','toyota_corolla','toyota_hilux','nissan_sentra','nissan_versa','nissan_march','renault_arkana','renault_clio','renault_megane') COLLATE utf8mb4_spanish_ci NOT NULL,
  `placas` varchar(8) COLLATE utf8mb4_spanish_ci DEFAULT NULL,
  `persona_numero_licencia` varchar(255) COLLATE utf8mb4_spanish_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `vehiculo_involucrado_accidente`
--

CREATE TABLE `vehiculo_involucrado_accidente` (
  `vehiculo_numero_serie` varchar(255) COLLATE utf8mb4_spanish_ci NOT NULL,
  `accidente_codigo` varchar(255) COLLATE utf8mb4_spanish_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;

--
-- Índices para tablas volcadas
--

--
-- Indices de la tabla `accidente`
--
ALTER TABLE `accidente`
  ADD PRIMARY KEY (`codigo`);

--
-- Indices de la tabla `multa`
--
ALTER TABLE `multa`
  ADD PRIMARY KEY (`numero`),
  ADD KEY `vehiculo_numero_serie` (`vehiculo_numero_serie`);

--
-- Indices de la tabla `nacionalidad`
--
ALTER TABLE `nacionalidad`
  ADD PRIMARY KEY (`clave`);

--
-- Indices de la tabla `persona`
--
ALTER TABLE `persona`
  ADD PRIMARY KEY (`numero_licencia`);

--
-- Indices de la tabla `persona_involucrada_accidente`
--
ALTER TABLE `persona_involucrada_accidente`
  ADD PRIMARY KEY (`persona_numero_licencia`,`accidente_codigo`),
  ADD KEY `accidente_codigo` (`accidente_codigo`);

--
-- Indices de la tabla `persona_nacionalidad`
--
ALTER TABLE `persona_nacionalidad`
  ADD PRIMARY KEY (`persona_numero_licencia`,`nacionalidad_clave`),
  ADD KEY `nacionalidad_clave` (`nacionalidad_clave`);

--
-- Indices de la tabla `vehiculo`
--
ALTER TABLE `vehiculo`
  ADD PRIMARY KEY (`numero_serie`),
  ADD KEY `persona_numero_licencia` (`persona_numero_licencia`);

--
-- Indices de la tabla `vehiculo_involucrado_accidente`
--
ALTER TABLE `vehiculo_involucrado_accidente`
  ADD PRIMARY KEY (`vehiculo_numero_serie`,`accidente_codigo`),
  ADD KEY `accidente_codigo` (`accidente_codigo`);

--
-- AUTO_INCREMENT de las tablas volcadas
--

--
-- AUTO_INCREMENT de la tabla `multa`
--
ALTER TABLE `multa`
  MODIFY `numero` int(12) NOT NULL AUTO_INCREMENT;

--
-- Restricciones para tablas volcadas
--

--
-- Filtros para la tabla `multa`
--
ALTER TABLE `multa`
  ADD CONSTRAINT `multa_ibfk_1` FOREIGN KEY (`vehiculo_numero_serie`) REFERENCES `vehiculo` (`numero_serie`);

--
-- Filtros para la tabla `persona_involucrada_accidente`
--
ALTER TABLE `persona_involucrada_accidente`
  ADD CONSTRAINT `persona_involucrada_accidente_ibfk_1` FOREIGN KEY (`persona_numero_licencia`) REFERENCES `persona` (`numero_licencia`),
  ADD CONSTRAINT `persona_involucrada_accidente_ibfk_2` FOREIGN KEY (`accidente_codigo`) REFERENCES `accidente` (`codigo`);

--
-- Filtros para la tabla `persona_nacionalidad`
--
ALTER TABLE `persona_nacionalidad`
  ADD CONSTRAINT `persona_nacionalidad_ibfk_1` FOREIGN KEY (`persona_numero_licencia`) REFERENCES `persona` (`numero_licencia`),
  ADD CONSTRAINT `persona_nacionalidad_ibfk_2` FOREIGN KEY (`nacionalidad_clave`) REFERENCES `nacionalidad` (`clave`);

--
-- Filtros para la tabla `vehiculo`
--
ALTER TABLE `vehiculo`
  ADD CONSTRAINT `vehiculo_ibfk_1` FOREIGN KEY (`persona_numero_licencia`) REFERENCES `persona` (`numero_licencia`);

--
-- Filtros para la tabla `vehiculo_involucrado_accidente`
--
ALTER TABLE `vehiculo_involucrado_accidente`
  ADD CONSTRAINT `vehiculo_involucrado_accidente_ibfk_1` FOREIGN KEY (`vehiculo_numero_serie`) REFERENCES `vehiculo` (`numero_serie`),
  ADD CONSTRAINT `vehiculo_involucrado_accidente_ibfk_2` FOREIGN KEY (`accidente_codigo`) REFERENCES `accidente` (`codigo`);

SQL - Comodines de texto

La cláusula LIKE selecciona valores que correspondan al registro expresado entre paréntesis

SELECT nombre, apellido FROM persona WHERE nombre LIKE '%Jos%'

SQL - IN

La cláusula IN nos permite seleccionar valores de entre un conjunto determinado de los mismos

SELECT marca, modelo, placas FROM vehiculo WHERE marca IN ('nissan', 'ford', 'toyota');

SQL - NULL

El valor NULL es un valor especial y no se pueden usar comparadores aritméticos porque, literalmente, representa una ausencia de valor

SELECT marca, modelo, placas FROM vehiculo WHERE placas IS NOT NULL;

SQL - DISTINCT

La cláusula DISTINCT remueve tuplas duplicadas en la selección de resultados

SELECT nombre, apellido FROM persona INNER JOIN vehiculo ON numero_licencia = persona_numero_licencia;

SELECT DISTINCT nombre, apellido FROM persona INNER JOIN vehiculo ON numero_licencia = persona_numero_licencia;

SQL - UNION

La cláusula UNION hace la unión de dos conjuntos de tuplas

SELECT
  persona.nombre,
  persona.apellido
FROM persona_involucrada_accidente
  INNER JOIN persona
    ON persona_involucrada_accidente.persona_numero_licencia = persona.numero_licencia
  INNER JOIN accidente
    ON persona_involucrada_accidente.accidente_codigo = accidente.codigo
UNION
SELECT
  persona.nombre,
  persona.apellido
FROM vehiculo
  INNER JOIN persona
    ON vehiculo.persona_numero_licencia = persona.numero_licencia
  INNER JOIN multa
    ON multa.vehiculo_numero_serie = vehiculo.numero_serie;

SQL - INTERSECT

La cláusula INTERSECT hace la intersección de dos conjuntos de tuplas

SELECT
  persona.nombre,
  persona.apellido
FROM persona_involucrada_accidente
  INNER JOIN persona
    ON persona_involucrada_accidente.persona_numero_licencia = persona.numero_licencia
  INNER JOIN accidente
    ON persona_involucrada_accidente.accidente_codigo = accidente.codigo
INTERSECT
SELECT
  persona.nombre,
  persona.apellido
FROM vehiculo
  INNER JOIN persona
    ON vehiculo.persona_numero_licencia = persona.numero_licencia
  INNER JOIN multa
    ON multa.vehiculo_numero_serie = vehiculo.numero_serie;

SQL - EXCEPT

La cláusula EXCEPT remueve el conjunto de la segunda operación de la primera

SELECT
  persona.nombre,
  persona.apellido
FROM persona_involucrada_accidente
  INNER JOIN persona
    ON persona_involucrada_accidente.persona_numero_licencia = persona.numero_licencia
  INNER JOIN accidente
    ON persona_involucrada_accidente.accidente_codigo = accidente.codigo
EXCEPT
SELECT
  persona.nombre,
  persona.apellido
FROM vehiculo
  INNER JOIN persona
    ON vehiculo.persona_numero_licencia = persona.numero_licencia
  INNER JOIN multa
    ON multa.vehiculo_numero_serie = vehiculo.numero_serie;

Bases de datos: Y todavía más SQL

By Gilberto 🦁

Bases de datos: Y todavía más SQL

Y todavía más SQL

5 months ago
140

Gilberto 🦁 PRO

Desarrollo visualizaciones y aplicaciones en línea, colaboro con distintos medios digitales

Bases de datos

Y todavía más SQL

SQL

SQL

SQL - Comodines de texto

SQL - IN

SQL - NULL

SQL - DISTINCT

SQL - UNION

SQL - INTERSECT

SQL - EXCEPT

Bases de datos: Y todavía más SQL

More from Gilberto 🦁