Diseño de base de datos y consultas

Hemos estado trabajando un poco con Access para familiarizarnos con las bases de datos y el lenguaje de consultas SQL. A continuación voy a mostrar mi solución al problema planteado en este apartado del curso.

En primer lugar, se debe diseñar la estructura de tablas más adecuada a las necesidades de nuestro proyecto.

A continuación, se crean las consultas necesarias sobre la estructura de tablas creada previamente para obtener los datos requeridos por los usuarios.

BÚSQUEDAS:
1.- Cliente por nombre comercial, nombre fiscal, CIF/NIF, población, provincia, tipo de IVA, código postal y teléfono
-SELECT NombreFiscal, NombreComercial FROM CLIENTE WHERE NombreComercial LIKE “*”&[Nombre]&”*”
-SELECT NombreFiscal, NombreComercial FROM CLIENTE WHERE NombreFiscal LIKE “*”&[Nombre]&”*”
-SELECT NombreFiscal, NombreComercial FROM CLIENTE WHERE CIF_NIF = [:Nombre]
-SELECT NombreFiscal, NombreComercial FROM CLIENTE WHERE Poblacion=[:Nombre]
-SELECT NombreFiscal, NombreComercial FROM CLIENTE WHERE Provincia= [:Nombre]
-SELECT NombreFiscal, NombreComercial FROM CLIENTE WHERE CIF_NIF=[:CIF_NIF]
-SELECT NombreFiscal, NombreComercial FROM CLIENTE WHERE tipo_recargo_fk=[:TipoIVA]
-SELECT NombreFiscal, NombreComercial FROM CLIENTE WHERE codigo_postal=[:CodigoPostal]
-SELECT NombreFiscal, NombreComercial FROM CLIENTE WHERE Id IN (SELECT id_cliente_fk FROM TELEFONOS_CLIENTE WHERE teléfono=[:Telefono])

2.- Proveedor por nombre comercial, nombre fiscal, CIF/NIF, población, provincia, código postal y teléfono
-SELECT NombreFiscal, NombreComercial FROM PROVEEDOR WHERE NombreComercial LIKE “*”&[Nombre]&”*”
SELECT NombreFiscal, NombreComercial FROM PROVEEDOR WHERE NombreFiscal LIKE “*”&[Nombre]&”*”
SELECT NombreFiscal, NombreComercial FROM PROVEEDOR WHERE CIF_NIF =[:Nombre]
SELECT NombreFiscal, NombreComercial FROM PROVEEDOR WHERE Poblacion==[:Nombre]
SELECT NombreFiscal, NombreComercial FROM PROVEEDOR WHERE Provincia= =[:Nombre]
SELECT NombreFiscal, NombreComercial FROM PROVEEDOR WHERE CIF_NIF=[:CIF_NIF]
SELECT NombreFiscal, NombreComercial FROM PROVEEDOR WHERE codigo_postal=[:CodigoPostal]
SELECT NombreFiscal, NombreComercial FROM PROVEEDOR WHERE Id IN (SELECT id_proveedor_fk FROM TELEFONOS_ PROVEEDOR WHERE teléfono=[:Telefono])

3.- Artículos por código y descripción
SELECT * FROM ARTICULO WHERE id=[:Código]
SELECT * FROM ARTICULO WHERE descripcion LIKE “*”&[descripción]&”*”

LISTADOS:
a)Clientes que han realizado compras de artículos de un proveedor.
SELECT DISTINCT C.nombre_comercial FROM ((CLIENTE C INNER JOIN FACTURA_VENTA FV ON C.Id=FV.id_cliente_fk) INNER JOIN DETALLE_FACTURA_VENTA DFV ON FV.Id=DFV.id_factura_venta_fk) INNER JOIN ARTICULO A ON DFV.id_articulo_fk=A.Id WHERE A.id_proveedor_fk = [:IdProveedor]
b)Clientes a los que se les ha vendido un artículo.
SELECT DISTINCT C.nombre_comercial FROM (CLIENTE C INNER JOIN FACTURA_VENTA FV ON C.Id=FV.id_cliente_fk) INNER JOIN DETALLE_FACTURA_VENTA DFV ON FV.Id=DFV.id_factura_venta_fk WHERE DFV.id_articulo_fk= [:IdArticulo]
c)Proveedores de una factura de venta.
SELECT DISTINCT P.nombre_comercial AS NombreProveedor FROM ((FACTURA_VENTA FV INNER JOIN DETALLE_FACTURA_VENTA DFV ON FV.Id=DFV.id_factura_venta_fk) INNER JOIN ARTICULO A ON DFV.id_articulo_fk=A.Id) INNER JOIN PROVEEDOR P ON A.id_proveedor_fk=P.Id WHERE FV.Id = [:IdFacturaVenta]
d)Listado de facturas (total, por cliente, conteniendo un artículo).
-SELECT Id AS NumeroFactura, id_cliente_fk AS CodigoCliente, fecha FROM FACTURA_VENTA
-SELECT Id AS NumeroFactura, id_cliente_fk AS CodigoCliente, fecha FROM FACTURA_VENTA WHERE id_cliente_fk = [:IdCliente]
-SELECT FV.Id AS NumeroFactura, FV.id_cliente_fk AS CodigoCliente, FV.fecha AS Fecha FROM FACTURA_VENTA FV INNER JOIN DETALLE_FACTURA_VENTA DFV ON FV.Id=DFV.id_factura_venta_fk WHERE DFV.id_articulo_fk = [:IdArticulo]
e)Listado de stock (todos, solo existencias).
- select articulo, sum(CantidadTotal) as Stock from(
select a.descripcion as articulo, sum(dfc.cantidad) as CantidadTotal from detalle_factura_compra dfc inner join articulo a on dfc.id_articulo_fk=a.id group by a.descripcion
union
select a.descripcion as articulo, -sum(dfv.cantidad) as CantidadTotal from detalle_factura_venta dfv inner join articulo a on dfv.id_articulo_fk=a.id group by a.descripcion)
group by articulo

- select articulo, sum(CantidadTotal) as Stock from(
select a.descripcion as articulo, sum(dfc.cantidad) as CantidadTotal from detalle_factura_compra dfc inner join articulo a on dfc.id_articulo_fk=a.id group by a.descripcion
union
select a.descripcion as articulo, -sum(dfv.cantidad) as CantidadTotal from detalle_factura_venta dfv inner join articulo a on dfv.id_articulo_fk=a.id group by a.descripcion)
group by articulo having sum(CantidadTotal)>0
f)Listado de artículos (total y vendidos a un cliente entre fechas con precio de venta).
-SELECT descripcion, precio FROM ARTICULO
-SELECT A.descripción AS Articulo, DFV.precio_unidad AS PrecioVenta, DFV.cantidad AS Cantidad FROM (FACTURA_VENTA FV INNER JOIN DETALLE_FACTURA_VENTA DFV ON FV.Id=DFV.id_factura_venta_fk) INNER JOIN ARTICULO A ON DFV.id_articulo_fk=A.Id WHERE FV.id_cliente_fk = [:IdCliente] AND FV.fecha BETWEEN [:FechaInicial] AND [:FechaFinal]
g)Formato 347 para clientes y proveedores (ventas o compras anuales por importe total superior a 3.005,06€).
- SELECT C.nombre_fiscal, SUM((1+DFV.IVA_aplicable)*DFV.cantidad*DFV.precio_unidad) AS Total from (DETALLE_FACTURA_VENTA DFV INNER JOIN FACTURA_VENTA FV ON DFV.id_factura_venta_fk=FV.id) INNER JOIN Cliente C ON FV.id_cliente_fk=C.Id GROUP BY C.nombre_fiscal HAVING SUM((1+DFV.IVA_aplicable)*DFV.cantidad*DFV.precio_unidad)>3005.6
- SELECT P.nombre_fiscal, SUM((1+DFC.IVA_aplicable)*DFC.cantidad*DFC.precio_unidad) AS Total from (DETALLE_FACTURA_COMPRA DFC INNER JOIN FACTURA_COMPRA FC ON DFC.id_factura_compra_fk=FC.id) INNER JOIN Proveedor P ON FC.id_proveedor_fk=P.Id GROUP BY P.nombre_fiscal HAVING SUM((1+DFC.IVA_aplicable)*DFC.cantidad*DFC.precio_unidad)>3005.6
h)Listado de ventas entre fechas.
SELECT A.descripcion AS Descripcion, DFV.cantidad AS Unidades, DFV.precio_unidad AS Precio, DFV.IVA_aplicable AS IVA FROM (FACTURA_VENTA FV INNER JOIN DETALLE_FACTURA_VENTA DFV ON FV.Id=DFV.id_factura_venta_fk) INNER JOIN ARTICULO A ON A.Id=DFV.id_articulo_fk WHERE FV.fecha BETWEEN [:fechaInicio] AND [:fechaFin]

i)Listado de compras entre fechas (artículos comprados entre fechas).
SELECT A.descripcion AS Descripcion, DFC.cantidad AS Unidades, DFC.precio_unidad AS Precio, DFC.IVA_aplicable AS IVA FROM (FACTURA_COMPRA FC INNER JOIN DETALLE_FACTURA_COMPRA DFC ON FC.Id=DFC.id_factura_compra_fk) INNER JOIN ARTICULO A ON A.Id=DFC.id_articulo_fk WHERE FC.fecha BETWEEN [:fechaInicio] AND [:fechaFin]
j)Listado de clientes con email.
SELECT * FROM CLIENTE WHERE email IS NOT NULL

Nota: Estas consultas han sido probadas sobre la estructura de base de datos mostrada al inicio, sobre MS Access 2007 y con unos pocos datos.