ejercicio3.sql

use aiep_inicio

--Devuelve una lista con el nombre del producto, precio y nombre de fabricante de todos los productos de la base de datos.

select producto.nombre, producto.precio, fabricante.nombre from producto, fabricante where fabricante.id_fabricante = producto.fabricante

select producto.nombre, producto.precio, fabricante.nombre from producto join fabricante on producto.fabricante = fabricante.id_fabricante

--Devuelve una lista con el nombre del producto, precio y nombre de fabricante de todos los productos de la base de datos. Ordene el resultado por el nombre del fabricante, por orden alfabético.

select producto.nombre, producto.precio, fabricante.nombre from producto, fabricante where fabricante.id_fabricante = producto.fabricante order by fabricante.nombre asc

select producto.nombre, producto.precio, fabricante.nombre from producto join fabricante on fabricante.id_fabricante = producto.fabricante order by fabricante.nombre

--Devuelve una lista con el identificador del producto, nombre del producto, identificador del fabricante y nombre del fabricante, de todos los productos de la base de datos.

select producto.id_producto, producto.nombre, producto.fabricante, fabricante.nombre from producto, fabricante where fabricante.id_fabricante = producto.fabricante

select producto.id_producto, producto.nombre, producto.fabricante, fabricante.nombre from producto join fabricante on fabricante.id_fabricante = producto.fabricante

--Devuelve el nombre del producto, su precio y el nombre de su fabricante, del producto más barato.

select top 1 producto.nombre, producto.precio, fabricante.nombre from producto, fabricante where producto.fabricante = fabricante.id_fabricante order by producto.precio asc

select top 1 producto.nombre, producto.precio, fabricante.nombre from producto join fabricante on fabricante.id_fabricante = producto.fabricante order by producto.precio asc 

--Devuelve el nombre del producto, su precio y el nombre de su fabricante, del producto más caro.
select top 1 producto.nombre, producto.precio, fabricante.nombre from producto, fabricante where producto.fabricante = fabricante.id_fabricante order by producto.precio desc

select top 1 producto.nombre, producto.precio, fabricante.nombre from producto join fabricante on fabricante.id_fabricante = producto.fabricante order by producto.precio desc

--Devuelve una lista de todos los productos del fabricante Lenovo.

select * from producto, fabricante where producto.fabricante = fabricante.id_fabricante and fabricante.nombre = 'Lenovo'

select * from producto join fabricante on fabricante.id_fabricante = producto.fabricante where fabricante.nombre = 'Lenovo'

--Devuelve una lista de todos los productos del fabricante Crucial que tengan un precio mayor que 200€.

select * from producto, fabricante where producto.fabricante = fabricante.id_fabricante and fabricante.nombre = 'Crucial' and producto.precio > 200

select * from producto join fabricante on fabricante.id_fabricante = producto.fabricante where fabricante.nombre = 'Crucial' and producto.precio > 200
--Devuelve un listado con todos los productos de los fabricantes Asus, Hewlett-Packardy Seagate. Sin utilizar el operador IN.
select * from producto, fabricante where producto.fabricante = fabricante.id_fabricante and( fabricante.nombre = 'Asus' or fabricante.nombre = 'Hewlett-Packard' or fabricante.nombre = 'Seagate')
select * from producto join fabricante on producto.fabricante = fabricante.id_fabricante where fabricante.nombre = 'Asus' or fabricante.nombre = 'Hewlett-Packard' or fabricante.nombre = 'Seagate'
--Devuelve un listado con todos los productos de los fabricantes Asus, Hewlett-Packardy Seagate. Utilizando el operador IN.
select * from producto, fabricante where producto.fabricante = fabricante.id_fabricante and fabricante.nombre in('Asus', 'Hewlett-Packard','Seagate')
select * from producto join fabricante on producto.fabricante = fabricante.id_fabricante where fabricante.nombre in('Asus', 'Hewlett-Packard','Seagate')
--Devuelve un listado con el nombre y el precio de todos los productos de los fabricantes cuyo nombre termine por la vocal e.
select producto.nombre, producto.precio from producto, fabricante where producto.fabricante = fabricante.id_fabricante and right(fabricante.nombre,1) = 'e'

select producto.nombre, producto.precio from producto join fabricante on fabricante.id_fabricante = producto.fabricante and RIGHT(fabricante.nombre,1) = 'e'

--Devuelve un listado con el nombre y el precio de todos los productos cuyo nombre de fabricante contenga el carácter w en su nombre.
select producto.nombre, producto.precio from producto, fabricante where producto.fabricante = fabricante.id_fabricante and fabricante.nombre like '%w%'

select producto.nombre, producto.precio from producto join fabricante on producto.fabricante = fabricante.id_fabricante where fabricante.nombre like '%w%'

--Devuelve un listado con el nombre de producto, precio y nombre de fabricante, de todos los productos que tengan un precio mayor o igual a 180€. Ordene el resultado en primer lugar por el precio (en orden descendente) y en segundo lugar por el nombre (en orden ascendente)
select producto.nombre, producto.precio, fabricante.nombre from producto, fabricante where producto.fabricante = fabricante.id_fabricante and producto.precio > 180 order by producto.precio desc, producto.nombre asc

select producto.nombre, producto.precio, fabricante.nombre from producto join fabricante on producto.fabricante = fabricante.id_fabricante where producto.precio>180 order by producto.precio desc, producto.nombre asc

--Devuelve un listado con el identificador y el nombre de fabricante, solamente de aquellos fabricantes que tienen productos asociados en la base de datos.
select fabricante.id_fabricante, fabricante.nombre from producto, fabricante where fabricante.id_fabricante = producto.fabricante  

select fabricante.id_fabricante, fabricante.nombre from fabricante join producto on producto.fabricante = fabricante.id_fabricante

--Consultas multitabla (Composición externa)
--Resuelva todas las consultas utilizando las cláusulas LEFT JOIN y RIGHT JOIN.

--Devuelve un listado de todos los fabricantes que existen en la base de datos, junto con los productos que tiene cada uno de ellos. El listado deberá mostrar también aquellos fabricantes que no tienen productos asociados.

select * from fabricante left join producto on producto.fabricante = fabricante.id_fabricante

--Devuelve un listado donde sólo aparezcan aquellos fabricantes que no tienen ningún producto asociado.

select * from fabricante left join producto on producto.fabricante = fabricante.id_fabricante where producto.fabricante is null

--Consultas resumen
--Calcula el número total de productos que hay en la tabla productos.
select count(*) from producto
--Calcula el número total de fabricantes que hay en la tabla fabricante.
select count(*) from fabricante
--Calcula el número de valores distintos de identificador de fabricante aparecen en la tabla productos.
select count(distinct fabricante) from producto
--Calcula la media del precio de todos los productos.
select AVG(producto.precio) from producto
--Calcula el precio más barato de todos los productos.
select min(producto.precio) from producto

--Calcula el precio más caro de todos los productos.
select max(producto.precio) from producto

--Lista el nombre y el precio del producto más barato.
select producto.nombre, producto.precio from producto where precio = (select min(precio) from producto)
--Lista el nombre y el precio del producto más caro.
select producto.nombre, producto.precio from producto where precio = (select max(precio) from producto)

--Calcula la suma de los precios de todos los productos.
select sum(precio) as sumaProductos from producto
--Calcula el número de productos que tiene el fabricante Asus.
select COUNT(*) as cantidad from producto, fabricante where fabricante.id_fabricante = producto.fabricante and fabricante.nombre = 'Asus'
--Calcula la media del precio de todos los productos del fabricante Asus.
select avg(producto.precio) as promedio from producto, fabricante where fabricante.id_fabricante = producto.fabricante and fabricante.nombre = 'Asus'
--Calcula el precio más barato de todos los productos del fabricante Asus.
select min(producto.precio) as precioMinimo from producto, fabricante where fabricante.id_fabricante = producto.fabricante and fabricante.nombre = 'Asus'
--Calcula el precio más caro de todos los productos del fabricante Asus.
select max(producto.precio) as precioMaximo from producto, fabricante where fabricante.id_fabricante = producto.fabricante and fabricante.nombre = 'Asus'
--Calcula la suma de todos los productos del fabricante Asus.
select sum(producto.precio) as Suma from producto, fabricante where fabricante.id_fabricante = producto.fabricante and fabricante.nombre = 'Asus'
--Muestra el precio máximo, precio mínimo, precio medio y el número total de productos que tiene el fabricante Crucial.
select max(producto.precio) as PrecioMaximo, min(producto.precio) as PrecioMinimo, count(*) as CantidadProductos from producto, fabricante where fabricante.id_fabricante = producto.fabricante and fabricante.nombre = 'Crucial'

select max(producto.precio), MIN(producto.precio), count(*) from producto join fabricante on fabricante.id_fabricante = producto.fabricante where fabricante.nombre = 'Crucial'
--Muestra el número total de productos que tiene cada uno de los fabricantes. El listado también debe incluir los fabricantes que no tienen ningún producto. El resultado mostrará dos columnas, una con el nombre del fabricante y otra con el número de productos que tiene. Ordene el resultado descendentemente por el número de productos.

select fabricante.nombre, COUNT(producto.id_producto) as cantidad from fabricante left join producto on producto.fabricante = fabricante.id_fabricante group by fabricante.nombre order by cantidad

--Muestra el precio máximo, precio mínimo y precio medio de los productos de cada uno de los fabricantes. El resultado mostrará el nombre del fabricante junto con los datos que se solicitan.

select fabricante.nombre, max(producto.precio), min(producto.precio), avg(producto.precio) from producto right join fabricante on fabricante.id_fabricante = producto.fabricante group by (fabricante.nombre)

--Muestra el precio máximo, precio mínimo, precio medio y el número total de productos de los fabricantes que tienen un precio medio superior a 200€. No es necesario mostrar el nombre del fabricante, con el identificador del fabricante es suficiente.
select producto.fabricante, max(producto.precio), min(producto.precio), avg(producto.precio), count(producto.id_producto) from producto group by producto.fabricante having avg(producto.precio) > 200

--Muestra el nombre de cada fabricante, junto con el precio máximo, precio mínimo, precio medio y el número total de productos de los fabricantes que tienen un precio medio superior a 200€. Es necesario mostrar el nombre del fabricante.
select fabricante.nombre, max(producto.precio), min(producto.precio), avg(producto.precio), count (producto.precio) from producto join fabricante on fabricante.id_fabricante = producto.fabricante group by fabricante.nombre having avg(producto.precio) > 200

--Calcula el número de productos que tienen un precio mayor o igual a 180€.

select count(*) from producto where precio > 180

--Calcula el número de productos que tiene cada fabricante con un precio mayor o igual a 180€.

select fabricante.nombre, count(*) from producto join fabricante on fabricante.id_fabricante = producto.fabricante where precio > 180 group by fabricante.nombre
--Lista el precio medio los productos de cada fabricante, mostrando solamente el identificador del fabricante.
select fabricante.id_fabricante, avg(producto.precio) from fabricante left join producto on fabricante.id_fabricante = producto.fabricante group by fabricante.id_fabricante
--Lista el precio medio los productos de cada fabricante, mostrando solamente el nombre del fabricante.
select fabricante.nombre, avg(producto.precio) from fabricante left join producto on fabricante.id_fabricante = producto.fabricante group by fabricante.nombre
--Lista los nombres de los fabricantes cuyos productos tienen un precio medio mayor o igual a 150€.
select fabricante.nombre from fabricante right join producto on fabricante.id_fabricante = producto.fabricante  group by fabricante.nombre having avg(producto.precio) > 150
--Devuelve un listado con los nombres de los fabricantes que tienen 2 o más productos.
select fabricante.nombre from fabricante right join producto on fabricante.id_fabricante = producto.fabricante group by fabricante.nombre having count (producto.precio) >= 2
--Devuelve un listado con los nombres de los fabricantes y el número de productos que tiene cada uno con un precio superior o igual a 220 €. No es necesario mostrar el nombre de los fabricantes que no tienen productos que cumplan la condición.
select fabricante.nombre, count (producto.nombre) from fabricante right join producto on fabricante.id_fabricante = producto.fabricante where producto.precio > 200 group by fabricante.nombre