SQL queries design checklist (8*day)

[plopezgit]

Base de datos Tienda

Tenemos las tablas producto y fabricante, cada una con los siguientes campos:

• _producto (codigo, número, precio, codigofabricante)

• fabricante (codigo, número) _El campo 'codigofabricante' de la entidad producto se relaciona con el campo 'código' de la entidad fabricante. Por favor, efectúa las siguientes consultas:

• [x] Lista el nombre de todos los productos que hay en la mesa producto. select nombre from tienda.producto;

• [x] Lista los nombres y los precios de todos los productos de la mesa producto. select nombre, precio from tienda.producto;

• [x] Lista todas las columnas de la tabla producto. select * from tienda.producto;

• [x] Lista el nombre de los productos, el precio en euros y el precio en dólares estadounidenses (USD). select nombre, precio as 'Precio en Euro', precio*1.05 as 'Precio en Dolar' from tienda.producto;

• [x] Lista el nombre de los productos, el precio en euros y el precio en dólares estadounidenses (USD). Utiliza los siguientes sobrenombre para las columnas: nombre de producto, euros, dólares. select nombre, precio as 'euro', precio*1.05 as 'dolares' from tienda.producto;

• [x] Lista los nombres y los precios de todos los productos de la mesa producto, convirtiendo los nombres a mayúscula. select upper(nombre), precio from tienda.producto;

• [x] Lista los nombres y los precios de todos los productos de la mesa producto, convirtiendo los nombres a minúscula. select lower(nombre), precio from tienda.producto;

• [x] Lista el nombre de todos los fabricantes en una columna, y en otra columna obtenga en mayúsculas los dos primeros caracteres del nombre del fabricante. select nombre, substring(upper(nombre), 1, 2) as 'Extract of name' from tienda.fabricante;

• [x] Lista los nombres y los precios de todos los productos de la mesa producto, redondeando el valor del precio. select nombre, format(precio, 2) from tienda.producto;

• [x] Lista los nombres y precios de todos los productos de la tabla producto, truncando el valor del precio para mostrarlo sin ninguna cifra decimal. select nombre, format(precio, 0) from tienda.producto;

• [x] Lista el código de los fabricantes que tienen productos en la mesa producto. select codigo_fabricante from tienda.producto;

• [x] Lista el código de los fabricantes que tienen productos en la mesa producto, eliminando los códigos que aparecen repetidos. select distinct codigo_fabricante from tienda.producto;

• [x] Lista los nombres de los fabricantes ordenados de forma ascendente. select nombre from tienda.fabricante order by nombre asc;

• [x] Lista los nombres de los fabricantes ordenados de forma descendente. select nombre from tienda.fabricante order by nombre desc;

• [x] Lista los nombres de los productos ordenados, en primer lugar, por el nombre de forma ascendente y, en segundo lugar, por el precio de forma descendente. select nombre from tienda.producto order by nombre asc, precio desc;

• [x] Devuelve una lista con las 5 primeras filas de la mesa fabricante. select * from tienda.fabricante limit 5;

• [x] Devuelve una lista con 2 filas a partir de la cuarta fila de la mesa fabricante. La cuarta fila también debe incluirse en la respuesta. select * from tienda.fabricante limit 3, 2;

• [x] Lista el nombre y precio del producto más barato. (Utiliza solo las cláusulas ORDER BY y LIMIT). NOTA: Aquí no podría usar MIN(precio), necesitaría GROUP BY. select nombre, precio from tienda.producto order by precio limit 1;

• [x] Lista el nombre y precio del producto más caro. (Utiliza solo las cláusulas ORDER BY y LIMIT). NOTA: Aquí no podría usar MAX(precio), necesitaría GROUP BY. select nombre, precio from tienda.producto order by precio desc limit 1;

• [x] Lista el nombre de todos los productos del fabricante cuyo código de fabricante es igual a 2. select nombre, codigo_fabricante from tienda.producto where codigo_fabricante=2;

• [x] Devuelve una lista con el nombre del producto, precio y nombre de fabricante de todos los productos de la base de datos. select p.nombre, p.precio, f.nombre from tienda.producto p join tienda.fabricante f where p.codigo_fabricante = f.codigo;

• [x] Devuelve una lista con el nombre del producto, precio y nombre de fabricante de todos los productos de la base de datos. Ordena el resultado por el nombre del fabricante, por orden alfabético. select p.nombre, p.precio, f.nombre from tienda.producto p join tienda.fabricante f where p.codigo_fabricante = f.codigo order by f.nombre asc;

• [x] Devuelve una lista con el código del producto, nombre del producto, código del fabricante y nombre del fabricante, de todos los productos de la base de datos. select p.nombre, p.precio, p.codigo_fabricante, f.nombre from tienda.producto p join tienda.fabricante f where p.codigo_fabricante = f.codigo;

• [x] Devuelve el nombre del producto, su precio y el nombre de su fabricante, del producto más barato. select p.nombre, p.precio, f.nombre from tienda.producto p join tienda.fabricante f where p.codigo_fabricante = f.codigo order by precio limit 1;

• [x] Devuelve el nombre del producto, su precio y el nombre de su fabricante, del producto más caro. select p.nombre, p.precio, f.nombre from tienda.producto p join tienda.fabricante f where p.codigo_fabricante = f.codigo order by precio desc limit 1;

• [x] Devuelve una lista de todos los productos del fabricante Lenovo. select p.nombre from tienda.producto p join tienda.fabricante f where p.codigo_fabricante = f.codigo and f.nombre = 'lenovo';

• [x] Devuelve una lista de todos los productos del fabricante Crucial que tengan un precio mayor que 200€. select p.nombre from tienda.producto p join tienda.fabricante f where p.codigo_fabricante = f.codigo and f.nombre = 'crucial' and p.precio >200;

• [x] Devuelve un listado con todos los productos de los fabricantes Asus, Hewlett-Packardy Seagate. Sin utilizar el operador IN. select distinct p.* from tienda.producto p join tienda.fabricante f where p.codigo_fabricante = f.codigo or f.nombre = 'Asus' or f.nombre = 'Hewlett-Packard' or f.nombre = 'Seagate';

• [x] Devuelve un listado con todos los productos de los fabricantes Asus, Hewlett-Packardy Seagate. Utilizando el operador IN. select * from tienda.producto p join tienda.fabricante f where p.codigo_fabricante = f.codigo and f.nombre in('Asus','Hewlett-Packard', 'Seagate');

• [x] Devuelve un listado con el nombre y el precio de todos los productos de los fabricantes cuyo nombre acabe por la vocal e. select p.nombre, p.precio from tienda.producto p join tienda.fabricante f where p.codigo_fabricante = f.codigo and f.nombre like '%e';

• [x] Devuelve un listado con el nombre y precio de todos los productos cuyo nombre de fabricante contenga el carácter w en su nombre. select p.nombre, p.precio from tienda.producto p join tienda.fabricante f where p.codigo_fabricante = f.codigo and f.nombre like '%w%';

• [x] 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 €. Ordena el resultado, en primer lugar, por el precio (en orden descendente) y, en segundo lugar, por el nombre (en orden ascendente). select p.nombre, p.precio, f.nombre from tienda.producto p join tienda.fabricante f where p.codigo_fabricante = f.codigo and p.precio >=180 order by p.precio desc, p.nombre;

• [x] Devuelve un listado con el código y el nombre de fabricante, sólo de aquellos fabricantes que tienen productos asociados en la base de datos. select distinct f.codigo, f.nombre from tienda.fabricante f join tienda.producto p where f.codigo = p.codigo_fabricante;

• [x] 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 a aquellos fabricantes que no tienen productos asociados. select p.nombre, f.nombre from tienda.producto p left join tienda.fabricante f on p.codigo_fabricante = f.codigo union select p.nombre, f.nombre from tienda.producto p right join tienda.fabricante f on p.codigo_fabricante = f.codigo;

• [x] Devuelve un listado en el que sólo aparezcan aquellos fabricantes que no tienen ningún producto asociado. select f.nombre from tienda.fabricante f left join tienda.producto p on f.codigo = p.codigo_fabricante where p.codigo_fabricante is null;

• [x] Devuelve todos los productos del fabricante Lenovo. (Sin utilizar INNER JOIN). select * from tienda.producto p join tienda.fabricante f on p.codigo_fabricante = f.codigo where f.nombre like 'lenovo';

• [x] Devuelve todos los datos de los productos que tienen el mismo precio que el producto más caro del fabricante Lenovo. (Sin usar INNER JOIN). select * from tienda.producto where precio = (select max(p.precio) from tienda.producto p join tienda.fabricante f on p.codigo_fabricante = f.codigo where f.nombre like 'lenovo');

• [x] Lista el nombre del producto más caro del fabricante Lenovo. select nombre from tienda.producto where precio = (select max(p.precio) from tienda.producto p join tienda.fabricante f on p.codigo_fabricante = f.codigo where f.nombre like 'lenovo');

• [x] Lista el nombre del producto más barato del fabricante Hewlett-Packard. select nombre from tienda.producto where precio = (select min(p.precio) from tienda.producto p join tienda.fabricante f on p.codigo_fabricante = f.codigo where f.nombre like 'Hewlett-Packard');

• [x] Devuelve todos los productos de la base de datos que tienen un precio mayor o igual al producto más caro del fabricante Lenovo. select nombre from tienda.producto where precio >= (select max(p.precio) from tienda.producto p join tienda.fabricante f on p.codigo_fabricante = f.codigo where f.nombre like 'lenovo');

• [x] Lista todos los productos del fabricante Asus que tienen un precio superior al precio medio de todos sus productos. select nombre from tienda.producto where precio > (select avg(p.precio) from tienda.producto p join tienda.fabricante f on p.codigo_fabricante = f.codigo where f.nombre like 'Asus');

Base de datos Universidad

_Por favor, descarga la base de datos del archivo schemauniversidad.sql, visualiza el diagrama E-R en un editor y efectúa las siguientes consultas:

• [x] Devuelve un listado con el primer apellido, segundo apellido y el nombre de todos los alumnos/as. El listado deberá estar ordenado alfabéticamente de menor a mayor por el primer apellido, segundo apellido y nombre. select apellido1, apellido2, nombre from universidad.persona where tipo = 'alumno' order by apellido1, apellido2, nombre;
• [x] Halla el nombre y los dos apellidos de los alumnos que no han dado de alta su número de teléfono en la base de datos. select nombre, apellido1, apellido2 from universidad.persona where tipo = 'alumno' and telefono is null;
• [x] Devuelve el listado de los alumnos que nacieron en 1999. select * from universidad.persona where tipo = 'alumno' and fecha_nacimiento like '1999%';
• [x] Devuelve el listado de profesores/as que no han dado de alta su número de teléfono en la base de datos y además su NIF termina en K. select * from universidad.persona where tipo = 'profesor' and telefono is null and nif like '%k';
• [x] Devuelve el listado de las asignaturas que se imparten en el primer cuatrimestre, en el tercer curso del grado que tiene el identificador 7. select * from universidad.asignatura where cuatrimestre = 1 and curso = 3 and id_grado = 7;
• [x] Devuelve un listado de los profesores/as junto con el nombre del departamento al que están vinculados. El listado debe devolver cuatro columnas, primer apellido, segundo apellido, nombre y nombre del departamento. El resultado estará ordenado alfabéticamente de menor a mayor por apellidos y nombre. select p.apellido1, p.apellido2, p.nombre, dep.nombre from universidad.persona p join universidad.profesor prof on prof.id_profesor = p.id join universidad.departamento dep on dep.id = prof.id_departamento;
• [x] Devuelve un listado con el nombre de las asignaturas, año de inicio y año de fin del curso escolar del alumno/a con NIF 26902806M. select p.nombre, a.nombre, ce.anyo_inicio, ce.anyo_fin from universidad.persona p join universidad.alumno_se_matricula_asignatura asma on p.id = asma.id_alumno join universidad.asignatura a on asma.id_asignatura = a.id join universidad.curso_escolar ce on asma.id_curso_escolar = ce.id where nif = '26902806M';
• [x] Devuelve un listado con el nombre de todos los departamentos que tienen profesores/as que imparten alguna asignatura en el Grado en Ingeniería Informática (Plan 2015). select distinct d.nombre from universidad.departamento d join universidad.profesor pro on pro.id_departamento = d.id join universidad.asignatura a on a.id_profesor = pro.id_profesor join universidad.grado g on a.id_grado = g.id where g.nombre = 'Grado en Ingeniería Informática (Plan 2015)';
• [x] Devuelve un listado con todos los alumnos que se han matriculado en alguna asignatura durante el curso escolar 2018/2019. select distinct p.nombre from universidad.persona p join universidad.alumno_se_matricula_asignatura asma on p.id = asma.id_alumno join universidad.curso_escolar ce on asma.id_curso_escolar = ce.id where ce.anyo_inicio between '2018%' and '2019%';

Resuelve las 6 siguientes consultas utilizando las cláusulas LEFT JOIN y RIGHT JOIN.

• [x] Devuelve un listado con los nombres de todos los profesores/as y los departamentos que tienen vinculados. El listado también debe mostrar aquellos profesores/as que no tienen ningún departamento asociado. El listado debe devolver cuatro columnas, nombre del departamento, primer apellido, segundo apellido y nombre del profesor/a. El resultado estará ordenado alfabéticamente de menor a mayor por el nombre del departamento, apellidos y nombre. select d.nombre, p.apellido1, p.apellido2, p.nombre from universidad.persona p left join universidad.profesor pro on p.id = pro.id_profesor left join universidad.departamento d on pro.id_departamento = d.id order by d.nombre, p.apellido1, p.apellido2, p.nombre;
• [x] Devuelve un listado con los profesores/as que no están asociados a un departamento. select p.apellido1, p.apellido2, p.nombre from universidad.persona p left join universidad.profesor pro on p.id = pro.id_profesor left join universidad.departamento d on pro.id_departamento = d.id where d.nombre is null;
• [x] Devuelve un listado con los departamentos que no tienen profesores asociados. select d.* from universidad.departamento d left join universidad.profesor pro on d.id = pro.id_departamento where d.id not in (select id_departamento from universidad.profesor);
• [x] Devuelve un listado con los profesores/as que no imparten ninguna asignatura. select p.* from universidad.persona p join universidad.profesor pro on p.id = pro.id_profesor left join universidad.asignatura a on pro.id_profesor = a.id_profesor where a.id_profesor is null;
• [x] Devuelve un listado con las asignaturas que no tienen un profesor/a asignado. select a.* from universidad.asignatura a left join universidad.profesor pro on a.id_profesor = pro.id_profesor where a.id_profesor is null;
• [x] Devuelve un listado con todos los departamentos que no han impartido asignaturas en ningún curso escolar. select d.* from universidad.departamento d right join universidad.profesor pro on d.id = pro.id_departamento right join universidad.asignatura a on pro.id_profesor = a.id_profesor where a.curso is null; select distinct d.* from universidad.departamento d left join universidad.profesor pro on d.id = pro.id_departamento left join universidad.asignatura a on pro.id_profesor = a.id_profesor where a.curso is null;

Consultas resumen:

• [x] Devuelve el número total de alumnos existentes. select count(id) as 'Total alumnos' from universidad.persona where tipo = 'alumno';
• [x] Calcula cuántos alumnos nacieron en 1999. select count(id) as 'Total nacidos en 1999' from universidad.persona where fecha_nacimiento like '1999%';
• [x] Calcula cuántos profesores/as hay en cada departamento. El resultado sólo debe mostrar dos columnas, una con el nombre del departamento y otra con el número de profesores/as que hay en ese departamento. El resultado sólo debe incluir los departamentos que tienen profesores/as asociados y tendrá que estar ordenado de mayor a menor por el número de profesores/as. select d.nombre, count(p.id) as Total_profesores from universidad.persona p join universidad.profesor pro on p.id = pro.id_profesor join universidad.departamento d on pro.id_departamento = d.id group by d.nombre order by Total_profesores desc;
• [x] Devuelve un listado con todos los departamentos y el número de profesores/as que hay en cada uno de ellos. Tenga en cuenta que pueden existir departamentos que no tienen profesores asociados. Estos departamentos también deben aparecer en el listado. select d.nombre, count(p.id) as Total_profesores from universidad.persona p right join universidad.profesor pro on p.id = pro.id_profesor right join universidad.departamento d on pro.id_departamento = d.id group by d.nombre order by Total_profesores desc;
• [x] Devuelve un listado con el nombre de todos los grados existentes en la base de datos y el número de asignaturas que tiene cada uno. Ten en cuenta que pueden existir grados que carecen de asignaturas asociadas. Estos grados también deben aparecer en el listado. El resultado deberá estar ordenado de mayor a menor por el número de asignaturas. select g.nombre, count(a.id) as Total_asignaturas from universidad.grado g left join universidad.asignatura a on g.id = a.id_grado group by g.nombre order by Total_asignaturas desc;
• [x] Devuelve un listado con el nombre de todos los grados existentes en la base de datos y el número de asignaturas que tiene cada uno, de los grados que tengan más de 40 asignaturas asociadas. select g.nombre, count(a.id) as Total_asignaturas from universidad.grado g left join universidad.asignatura a on g.id = a.id_grado group by g.nombre having Total_asignaturas >40;
• [x] Devuelve un listado que muestre el nombre de los grados y la suma del número total de créditos existentes para cada tipo de asignatura. El resultado debe tener tres columnas: nombre del grado, tipo de asignatura y la suma de los créditos de todas las asignaturas que existen de este tipo select g.nombre, a.tipo, sum(a.creditos) as Total_creditos from universidad.grado g join universidad.asignatura a on g.id = a.id_grado group by a.tipo, g.nombre;
• [x] Devuelve un listado que muestre cuántos alumnos se han matriculado de alguna asignatura en cada uno de los cursos escolares. El resultado tendrá que mostrar dos columnas, una columna con el año de inicio del curso escolar y otra con el número de alumnos matriculados. select ce.anyo_inicio, count(asma.id_alumno) as Total_alumnos_matriculados from universidad.alumno_se_matricula_asignatura asma right join universidad.curso_escolar ce on asma.id_curso_escolar = ce.id group by ce.anyo_inicio;
• [x] Devuelve un listado con el número de asignaturas que imparte cada profesor/a. El listado debe tener en cuenta a aquellos profesores/as que no imparten ninguna asignatura. El resultado mostrará cinco columnas: id, nombre, primer apellido, segundo apellido y número de asignaturas. El resultado estará ordenado de mayor a menor por el número de asignaturas. select p.id, p.nombre, p.apellido1, p.apellido2, count(a.id) as Total_asignaturas from universidad.persona p join universidad.profesor pro on p.id = pro.id_profesor join universidad.asignatura a on pro.id_profesor = a.id_profesor group by p.id, p.nombre, p.apellido1, p.apellido2 order by Total_asignaturas desc;
• [x] Devuelve todos los datos del alumno/a más joven. select * from universidad.persona where fecha_nacimiento = (select max(fecha_nacimiento) from universidad.persona);
• [x] Devuelve un listado con los profesores/as que tienen un departamento asociado y que no imparten ninguna asignatura. select p.id, p.nombre from universidad.persona p left join universidad.profesor pro on p.id = pro.id_profesor left join universidad.asignatura a on pro.id_profesor = a.id_profesor where pro.id_departamento is not null and a.id_profesor is null;

[plopezgit]

[plopezgit]

[plopezgit]

[plopezgit]

[plopezgit]

[plopezgit]

[plopezgit]

[plopezgit]

[plopezgit]

[plopezgit]

[plopezgit]

[plopezgit]

[plopezgit]

[plopezgit]

[plopezgit]

[plopezgit]

[plopezgit]

[plopezgit]

[plopezgit]

[plopezgit]

[plopezgit]

[plopezgit]

[plopezgit]

[plopezgit]

[plopezgit]

[plopezgit]

[plopezgit]

[plopezgit]

[plopezgit]

[plopezgit]

[plopezgit]