Dominio de SQL para Sistemas de Control Vehicular y Gestión de Datos

Estructura y Creación de la Base de Datos

Para iniciar el proceso de gestión de información, ejecutamos los comandos para crear y utilizar el entorno de trabajo:

CREATE DATABASE REPASO;
USE REPASO;

Definición de Tablas y Restricciones

A continuación, se detalla la creación de las tablas. Es fundamental considerar que las Primary Keys y Foreign Keys se definen siguiendo la estructura lógica del esquema:

• Tabla prop: Contiene las propiedades; las llaves primarias y las foreign van de último. Id INT PRIMARY KEY NOT NULL.
• Tabla tenencias: placas VARCHAR(6) PRIMARY KEY NOT NULL (se puede quitar).
• Tabla seguros: poliza PRIMARY KEY NOT NULL. Se colocan todos los campos y, por último, definimos la relación: FOREIGN KEY (placas) REFERENCES tenencias(placas) (quita).
• Tabla autos: PRIMARY KEY NOT NULL. Se colocan todos los campos y, finalmente: FOREIGN KEY (prop) REFERENCES prop (Id) y FOREIGN KEY (placas) REFERENCES tenencias(placas) (quita).

Importación Masiva de Datos

Para optimizar la carga de información desde archivos externos, utilizamos la instrucción BULK INSERT de manera individual por cada tabla:

BULK INSERT autos FROM 'C:/' WITH (FIELDTERMINATOR = ',');

Consultas y Reportes Especializados

A continuación, se presentan los ejercicios de consulta para la extracción de información específica del sistema:

1. Propietario y Vehículo con Seguro de Menor Monto (Banamex)

Muestre el nombre completo del propietario y los datos completos del vehículo cuyo seguro sea el de menor monto con la aseguradora Banamex.

SELECT p.nombre, p.ap_pat, p.ap_mat, a.*
FROM prop p, autos a
WHERE a.placas IN (
    SELECT TOP 1 placas 
    FROM seguros 
    WHERE aseguradora LIKE 'Banamex%' 
    ORDER BY costo ASC
) 
AND a.prop = p.id;

2. Propietarios sin Adeudos en Periodos Específicos

Muestre el nombre completo de los propietarios que no deban alguna tenencia de los periodos 2003, 2005, 2011.

SELECT p.nombre, p.ap_pat, p.ap_mat 
FROM prop p, tenencias t, autos a
WHERE t.estatus NOT LIKE 'ad%'
      AND t.periodo IN (2003, 2005, 2011) 
      AND a.prop = p.id 
      AND t.placas = a.placas;

3. Cobertura de Seguros con Tenencias Pendientes

Muestre el monto y tipo de cobertura de los seguros de autos que adeudan alguna tenencia.

SELECT s.costo, s.tipo_cobertura
FROM seguros s, tenencias t
WHERE t.estatus LIKE 'ad%'
AND s.placas = t.placas;

4. Propietarios con Vehículos Asegurados en Bancomer

Muestre el nombre completo de los propietarios cuyo auto está asegurado con Bancomer.

SELECT p.nombre, p.ap_pat, p.ap_mat 
FROM prop p, autos a
WHERE a.placas IN (
    SELECT placas 
    FROM seguros 
    WHERE aseguradora LIKE 'Bancomer%'
) 
AND p.id = a.prop;

5. Propietarios con Vehículos de Alto Valor y Adeudos Superiores a 3000

Muestre el nombre del propietario cuyo auto tenga un precio mayor a 100,000 y adeude alguna tenencia con monto mayor a 3,000.

SELECT DISTINCT p.nombre, p.ap_pat, p.ap_mat
FROM prop p, tenencias t, autos a
WHERE a.precio_vehiculo > 100000 
AND t.estatus LIKE 'ad%' 
AND t.monto_tenencia > 3000
AND a.prop = p.id 
AND t.placas = a.placas;

6. Propietarios sin Vehículos Registrados

Muestre toda la información de los propietarios que no tienen algún auto.

SELECT p.*
FROM prop p 
LEFT JOIN autos a ON p.id = a.prop
WHERE a.placas IS NULL;

7. Información Completa de Autos con Tenencias Pagadas

Muestre toda la información del auto y del propietario para aquellos autos que tengan todas las tenencias pagadas.

SELECT a.*, p.*
FROM autos a, prop p, tenencias t
WHERE a.prop = p.id 
AND t.placas = a.placas /* amarre */ 
AND a.placas NOT IN (
    SELECT placas 
    FROM tenencias 
    WHERE estatus LIKE 'ad%'
);