Diferencia entre revisiones de «Solucións ás tarefas do boletín de subconsultas»

De MediaWiki
Ir a la navegación Ir a la búsqueda
(Página creada con «== Tarefa 1.1 == Seleccionar código e peso dos artigos que teñen o peso máis pequeno có peso do artigo de código '077WN45'. == Tarefa 1.2 == Seleccionar código, no…»)
 
 
(No se muestran 2 ediciones intermedias del mismo usuario)
Línea 1: Línea 1:
 +
= Tarefa 1. Realizar consultas que utilicen os operadores relacionais combinados con subconsultas dentro dunha condición =
 +
A tarefa consiste en realizar as seguintes consultas utilizando operadores relacionais pra relacionar unha consulta principal con subconsultas dentro dunha condición.
 +
 +
As tarefas do 1.1 a 1.6 serán  sobre a base de datos tendaBD e do 1.7 a 1.12 sobre a base de datos traballadores
 +
 
== Tarefa 1.1 ==  
 
== Tarefa 1.1 ==  
 
Seleccionar código e peso dos artigos que teñen o peso máis pequeno có peso do artigo de código '077WN45'.
 
Seleccionar código e peso dos artigos que teñen o peso máis pequeno có peso do artigo de código '077WN45'.
 +
 +
=== Solución ===
 +
<syntaxhighlight lang="sql" line enclose="div" highlight="" >
 +
************************************************************************************
 +
Seleccionar código e peso dos artigos que teñen o peso máis pequeno có peso do artigo
 +
de código '077WN45'
 +
************************************************************************************/
 +
select  art_codigo Codigo,
 +
art_peso Peso
 +
from artigos
 +
where art_peso < (select art_peso from artigos where art_codigo='077WN45')
 +
order by art_peso;
 +
</syntaxhighlight>
  
 
== Tarefa 1.2 ==  
 
== Tarefa 1.2 ==  
 
Seleccionar código, nome, peso e cor dos artigos da mesma cor có artigo de código '242C283', e que teñan un peso maior ou igual co peso medio de todos os artigos.
 
Seleccionar código, nome, peso e cor dos artigos da mesma cor có artigo de código '242C283', e que teñan un peso maior ou igual co peso medio de todos os artigos.
 +
 +
=== Solución ===
 +
<syntaxhighlight lang="sql" line enclose="div" highlight="" >
 +
/************************************************************************************
 +
Seleccionar código, nome, peso e cor dos artigos da mesma cor có artigo de código
 +
'242C283', e que teñan un peso maior ou igual co peso medio de todos os artigos
 +
************************************************************************************/
 +
select  art_codigo,
 +
art_nome,
 +
art_peso,
 +
art_color
 +
from artigos
 +
where art_color = (select art_color from artigos where art_codigo = '242C283')
 +
and art_peso >= (select avg(art_peso) from artigos);
 +
</syntaxhighlight>
  
 
== Tarefa 1.3 ==
 
== Tarefa 1.3 ==
 
Seleccionar código da tenda e nome do xerente das tendas nas que se vendeu polo menos unha unidade do artigo de código ' 077WN45'.
 
Seleccionar código da tenda e nome do xerente das tendas nas que se vendeu polo menos unha unidade do artigo de código ' 077WN45'.
 +
 +
=== Solución ===
 +
<syntaxhighlight lang="sql" line enclose="div" highlight="" >
 +
/************************************************************************************
 +
Seleccionar código da tenda e nome do xerente, das tendas nas que se vendeu polo menos
 +
unha unidade do artigo de código 077WN45
 +
************************************************************************************/
 +
select te.tda_id as Codigo_tenda,
 +
em.emp_nome as Nome_xerente
 +
from  tendas as te join empregados as em on te.tda_xerente = em.emp_id
 +
where te.tda_id = any (select distinct ve.ven_tenda
 +
from vendas as ve join detalle_vendas as dv
 +
on ve.ven_id = dv.dev_venda
 +
where dv.dev_artigo  =  '077WN45');
 +
</syntaxhighlight>
  
 
== Tarefa 1.4 ==  
 
== Tarefa 1.4 ==  
Línea 12: Línea 60:
 
* Utilizando any.
 
* Utilizando any.
 
* Utilizando a función min().
 
* Utilizando a función min().
 +
 +
=== Solución ===
 +
<syntaxhighlight lang="sql" line enclose="div" highlight="" >
 +
/************************************************************************************
 +
Mostrar a lista de artigos cun prezo de venta maior có prezo de venta do artigo
 +
máis barato de cor negra, utilizando any
 +
************************************************************************************/
 +
select *
 +
from artigos
 +
where art_pv > any (select art_pv
 +
                    from artigos
 +
                    where art_color="NEGRO");
 +
/************************************************************************************
 +
Mostrar a lista de artigos cun prezo de venta maior có prezo de venta do artigo
 +
máis barato de cor negra, utilizando a función min().
 +
************************************************************************************/
 +
select *
 +
from artigos
 +
where art_pv > (select min(art_pv)
 +
                from artigos
 +
                where art_color="NEGRO");
 +
</syntaxhighlight>
  
 
== Tarefa 1.5 ==
 
== Tarefa 1.5 ==
 
Seleccionar código de artigo, descrición do artigo e código do provedor para os artigos que subministra o provedor que ten o nome SEAGATE.
 
Seleccionar código de artigo, descrición do artigo e código do provedor para os artigos que subministra o provedor que ten o nome SEAGATE.
 +
 +
=== Solución ===
 +
<syntaxhighlight lang="sql" line enclose="div" highlight="" >
 +
/************************************************************************************
 +
Seleccionar código de artigo, descrición do artigo e código do provedor para os artigos
 +
que subministra o provedor SEAGATE
 +
************************************************************************************/
 +
select art_codigo as Artigo,
 +
art_nome as Descripción,
 +
art_provedor as 'Código de provedor'
 +
from artigos
 +
where art_provedor = ( select prv_id
 +
from provedores
 +
where prv_nome = 'SEAGATE');
 +
/* outra forma, combinando as táboas */
 +
select art_codigo, art_nome, art_provedor
 +
from artigos join provedores on art_provedor=prv_id
 +
where prv_nome="SEAGATE";
 +
</syntaxhighlight>
  
 
== Tarefa 1.6 ==  
 
== Tarefa 1.6 ==  
 
Mostrar información do artigo máis caro, Seleccionando o seu código, nome, prezo de venta e nome do provedor que o subministra.  
 
Mostrar información do artigo máis caro, Seleccionando o seu código, nome, prezo de venta e nome do provedor que o subministra.  
 +
 +
=== Solución ===
 +
<syntaxhighlight lang="sql" line enclose="div" highlight="" >
 +
/************************************************************************************
 +
Mostrar información do artigo máis caro, Seleccionando o seu código, nome, prezo de
 +
venda e nome do provedor que o subministra.
 +
************************************************************************************/
 +
select ar.art_codigo as Articulo,
 +
ar.art_nome as Descipcion,
 +
ar.art_pv as Prezo_venda,
 +
pr.prv_nome as Proveedor
 +
from artigos as ar join provedores as pr on ar.art_provedor=pr.prv_id
 +
where ar.art_pv = (select max(art_pv) from artigos);
 +
</syntaxhighlight>
  
 
== Tarefa 1.7 ==
 
== Tarefa 1.7 ==
 
Mostrar nomes e salarios dos empregados que teñan salario maior có salario de tódolos empregados do departamento 122. O resultado móstrase ordenado por nome de empregado.
 
Mostrar nomes e salarios dos empregados que teñan salario maior có salario de tódolos empregados do departamento 122. O resultado móstrase ordenado por nome de empregado.
 +
 +
=== Solución ===
 +
<syntaxhighlight lang="sql" line enclose="div" highlight="" >
 +
/*Mostrar nomes e salarios dos empregados que teñan salario maior có salario de
 +
tódolos empregados do departamento 122. O resultado móstrase ordenado por nome de
 +
empregado.*/
 +
select empNome as Nome,
 +
empSalario as Salario
 +
from empregado
 +
where empSalario > (select max(empSalario)
 +
from empregado
 +
where empDepartamento=122)
 +
order by empNome;
 +
/*Outra forma*/
 +
select empNome empregadoado,
 +
empSalario as Salario
 +
from empregado
 +
where empSalario > all (select empSalario
 +
from empregado
 +
where empDepartamento=122)
 +
order by empNome;
 +
</syntaxhighlight>
  
 
== Tarefa 1.8 ==
 
== Tarefa 1.8 ==
Línea 26: Línea 151:
 
* con subconsulta.
 
* con subconsulta.
 
* sen subconsulta utilizando JOIN.
 
* sen subconsulta utilizando JOIN.
 +
 +
=== Solución ===
 +
<syntaxhighlight lang="sql" line enclose="div" highlight="" >
 +
/*Mostrar os nomes e salarios dos empregados con salarios maiores ou iguais a o
 +
de Claudia Fierro, ordenados alfabeticamente.*/
 +
#Con subconsulta
 +
select empNome as Eempregado,
 +
empSalario as Salario
 +
from empregado
 +
where empSalario >=
 +
(select empSalario
 +
from empregado
 +
where empNome = 'FIERRO, CLAUDIA')
 +
order by empNome;
 +
 +
#Sen subconsulta utilizando join para facer unha autocombinación
 +
select  emp1.empNome as Eempregado,
 +
emp1.empSalario as Salario
 +
from empregado as emp1 join empregado as emp2
 +
on emp1.empSalario >= emp2.empSalario
 +
where emp2.empNome = 'FIERRO, CLAUDIA'
 +
order by emp1.empNome;
 +
</syntaxhighlight>
  
 
== Tarefa 1.9 ==
 
== Tarefa 1.9 ==
 
Mostrar nome e presuposto dos departamentos que teñen o presuposto máis alto e o máis baixo.
 
Mostrar nome e presuposto dos departamentos que teñen o presuposto máis alto e o máis baixo.
 +
 +
=== Solución ===
 +
<syntaxhighlight lang="sql" line enclose="div" highlight="" >
 +
/*Mostrar nome e presuposto dos departamentos que teñen o presuposto máis alto e o
 +
máis baixo.*/
 +
select depNome as Departamento,
 +
depPresuposto as Presuposto
 +
from  departamento
 +
where depPresuposto = (select min(depPresuposto) from  departamento)
 +
or depPresuposto = (select max(depPresuposto) from  departamento);
 +
</syntaxhighlight>
  
 
== Tarefa 1.10 ==  
 
== Tarefa 1.10 ==  
 
Mostrar número, nome, data de ingreso na empresa e nome do departamento no que traballa o empregado ou empregados que levan máis tempo na empresa.
 
Mostrar número, nome, data de ingreso na empresa e nome do departamento no que traballa o empregado ou empregados que levan máis tempo na empresa.
 +
 +
=== Solución ===
 +
<syntaxhighlight lang="sql" line enclose="div" highlight="" >
 +
/*Mostrar número, nome, data de ingreso na empresa e nome do departamento no que
 +
traballa o empregado ou empregados que levan máis tempo na empresa.*/
 +
select em.empNumero as Numero,
 +
em.empNome as Nome,
 +
em.empDataIngreso as Data,
 +
de.depNome as Departamento
 +
from  departamento as de join empregado as em on em.empDepartamento=de.depNumero
 +
where em.empDataIngreso = (select min(empDataIngreso) from  empregado);
 +
</syntaxhighlight>
  
 
== Tarefa 1.11 ==  
 
== Tarefa 1.11 ==  
Línea 37: Línea 208:
 
* Ingresaron na empresa despois do 1-1-88.
 
* Ingresaron na empresa despois do 1-1-88.
 
* Ingresaron na empresa antes do 1-1-88 pero teñen un salario inferior ao salario medio de todos os empregados da empresa.
 
* Ingresaron na empresa antes do 1-1-88 pero teñen un salario inferior ao salario medio de todos os empregados da empresa.
 +
 +
=== Solución ===
 +
<syntaxhighlight lang="sql" line enclose="div" highlight="" >
 +
/*Mostrar nome e salario dos empregados que cumpran algunha das seguintes condicións:
 +
– Ingresaron na empresa despois do 1-1-88.
 +
– Ingresaron na empresa antes do 1-1-88 pero teñen un salario inferior ao salario medio
 +
de todos os empregados da empresa.*/
 +
select empNome as Empregado,
 +
empSalario as Salario
 +
from empregado
 +
where empDataIngreso >'1988/01/01'
 +
or ( empDataIngreso <='1988/01/01'
 +
and empSalario < (select avg(empSalario) from empregado))
 +
order by empNome;
 +
</syntaxhighlight>
  
 
== Tarefa 1.12 ==  
 
== Tarefa 1.12 ==  
 
Mostrar a extensión telefónica asignada a máis empregados indicando o número de empregados que a comparten. Se hai máis dunha, deben aparecer todas.
 
Mostrar a extensión telefónica asignada a máis empregados indicando o número de empregados que a comparten. Se hai máis dunha, deben aparecer todas.
  
−
=== Solucións ===
+
=== Solución ===
 +
<syntaxhighlight lang="sql" line enclose="div" highlight="" >
 +
/*Mostrar a extensión telefónica asignada a máis empregados indicando o número de
 +
empregados que a comparten. Se hai máis dunha, deben aparecer todas.*/
 +
select empExtension as Extension,
 +
count(*) as Empregados
 +
from empregado
 +
group by empExtension
 +
having count(*)=(select count(*)
 +
from empregado
 +
group by empExtension
 +
order by 1 desc limit 1);
 +
</syntaxhighlight>
  
 
= Tarefa 2. Realizar consultas que utilicen o predicado BETWEEN combinado con subconsultas dentro dunha condición =
 
= Tarefa 2. Realizar consultas que utilicen o predicado BETWEEN combinado con subconsultas dentro dunha condición =
Línea 49: Línea 247:
 
== Tarefa 2.1 ==
 
== Tarefa 2.1 ==
 
Mostrar nome e prezo de venda dos artigos que teñen un prezo de venda comprendido entre o prezo do artigo '0713242' e a media de prezos de todos os artigos. Os datos deben mostrarse ordenados alfabeticamente polo nome do artigo.
 
Mostrar nome e prezo de venda dos artigos que teñen un prezo de venda comprendido entre o prezo do artigo '0713242' e a media de prezos de todos os artigos. Os datos deben mostrarse ordenados alfabeticamente polo nome do artigo.
 +
 +
=== Solución ===
 +
<syntaxhighlight lang="sql" line enclose="div" highlight="" >
 +
/************************************************************************************
 +
Mostrar nome e prezo de venda dos artigos que teñen un prezo de venda comprendido
 +
entre o prezo do artigo '0713242' e a media de prezos de todos os artigos.
 +
Os datos deben mostrarse ordenados alfabeticamente polo nome do artigo.
 +
************************************************************************************/
 +
select art_nome as Artigo,
 +
art_pv as Prezo
 +
from artigos
 +
where art_pv between (select art_pv from artigos where art_codigo='0713242')
 +
and (select avg(art_pv) from artigos)
 +
order by art_nome;
 +
</syntaxhighlight>
  
 
== Tarefa 2.2 ==
 
== Tarefa 2.2 ==
 
Seleccionar o  nome e número de departamento dos empregados que pertenzan a un departamento cun presuposto comprendido entre os presupostos dos departamentos 122 e 121 (incluídos). Os datos deben mostrarse ordenados de menor a maior polo número do departamento.
 
Seleccionar o  nome e número de departamento dos empregados que pertenzan a un departamento cun presuposto comprendido entre os presupostos dos departamentos 122 e 121 (incluídos). Os datos deben mostrarse ordenados de menor a maior polo número do departamento.
 +
 +
=== Solución ===
 +
<syntaxhighlight lang="sql" line enclose="div" highlight="" >
 +
/* Seleccionar o  nome e número de departamento dos empregados que pertenzan a un
 +
departamento cun presuposto comprendido entre os presupostos dos departamentos 122
 +
e 121 (incluídos).  Os datos deben mostrarse ordenados de menor a maior polo número
 +
do departamento*/
 +
select empNome, empDepartamento
 +
from empregado
 +
where empDepartamento = any (select depNumero
 +
from departamento
 +
                          where depPresuposto between (select depPresuposto
 +
from departamento
 +
                                                      where depNUmero = 122)
 +
  and (select depPresuposto
 +
from departamento
 +
where depNUmero = 121))
 +
order by empDepartamento desc;
 +
/* Para mostrar, ademais, algunha columna da táboa departamento hai que facer un join.
 +
Exemplo: mostrando, ademais, o nome do departamento*/
 +
select empNome, empDepartamento, depNome 
 +
from empregado join departamento on empDepartamento = depNumero
 +
where depPresuposto
 +
between (select depPresuposto
 +
from departamento
 +
where depNUmero = 122)
 +
and (select depPresuposto
 +
from departamento
 +
where depNUmero = 121)
 +
order by empDepartamento desc;
 +
</syntaxhighlight>
  
 
= Tarefa 3. Realizar consultas utilizando subconsultas co predicado IN =
 
= Tarefa 3. Realizar consultas utilizando subconsultas co predicado IN =
Línea 60: Línea 304:
 
== Tarefa 3.1 ==  
 
== Tarefa 3.1 ==  
 
Obter a lista de provedores que subministran como mínimo un artigo de cor negra.
 
Obter a lista de provedores que subministran como mínimo un artigo de cor negra.
 +
 +
=== Solución ===
 +
<syntaxhighlight lang="sql" line enclose="div" highlight="" >
 +
/************************************************************************************
 +
Obter a lista de provedores que subministran como mínimo un artigo de cor negra
 +
************************************************************************************/
 +
select *
 +
from  provedores
 +
where prv_id in (select distinct art_provedor
 +
from artigos
 +
where art_color  =  "NEGRO") ;
 +
</syntaxhighlight>
  
 
== Tarefa 3.2 ==  
 
== Tarefa 3.2 ==  
 
Mostrar identificador de cliente, apelidos e nome na mesma columna separados por coma, para os clientes que só teñen unha venda. O resultado estará ordenado polo identificador do cliente.
 
Mostrar identificador de cliente, apelidos e nome na mesma columna separados por coma, para os clientes que só teñen unha venda. O resultado estará ordenado polo identificador do cliente.
 +
 +
=== Solución ===
 +
<syntaxhighlight lang="sql" line enclose="div" highlight="" >
 +
/************************************************************************************
 +
Mostrar identificador de cliente, apelidos e nome na mesma columna separados por coma,
 +
para os clientes que só teñen unha venda. O resultado estará
 +
ordenado polo identificador do cliente
 +
************************************************************************************/
 +
select clt_id as Cliente,
 +
concat(clt_apelidos,', ',clt_nome) as 'Apelidos e nome'
 +
from clientes
 +
where clt_id in (select ven_cliente
 +
from vendas
 +
group by ven_cliente
 +
having count(*) = 1)
 +
order by clt_id;
 +
/* outra forma alternativa utilizando referencias externas na subconsulta*/
 +
select clt_id as Cliente,
 +
concat(clt_apelidos,', ',clt_nome) as 'Apelidos e nome'
 +
from clientes
 +
where 1 = (select count(*)
 +
from vendas
 +
where ven_cliente = clt_id)
 +
order by clt_id;
 +
</syntaxhighlight>
  
 
== Tarefa 3.3 ==  
 
== Tarefa 3.3 ==  
 
Mostrar identificador e nome dos clientes que fixeron algunha compra despois do día en que o cliente número 6 fixo a súa última compra.   
 
Mostrar identificador e nome dos clientes que fixeron algunha compra despois do día en que o cliente número 6 fixo a súa última compra.   
 +
 +
=== Solución ===
 +
<syntaxhighlight lang="sql" line enclose="div" highlight="" >
 +
/************************************************************************************
 +
Mostrar identificador e nome dos clientes que fixeron algunha compra despois do día
 +
en que o cliente número 6 fixo a súa última compra.
 +
************************************************************************************/
 +
select clt_id, clt_nome
 +
from clientes
 +
where clt_id in ( select ven_cliente
 +
                  from vendas
 +
                  where ven_data>(select max(ven_data)
 +
                                  from vendas
 +
                                  where ven_cliente=6));
 +
/* Tamén valería a condición: 
 +
where ven_data > ALL (SELECT ven_data FROM vendas WHERE ven_cliente=6) */
 +
 +
/* Outra solución podería ser */
 +
select distinct ve.ven_cliente as Cliente,
 +
cl.clt_nome as Nome
 +
from vendas as ve join clientes as cl on ve.ven_cliente=cl.clt_id
 +
where ve.ven_data > ( select max(ven_data)
 +
                    from vendas
 +
                    where ven_cliente=6 );
 +
</syntaxhighlight>
  
 
== Tarefa 3.4 ==  
 
== Tarefa 3.4 ==  
 
Mostrar os nomes dos xerentes das tendas nas que se fixo algunha venta.  
 
Mostrar os nomes dos xerentes das tendas nas que se fixo algunha venta.  
 +
 +
=== Solución ===
 +
<syntaxhighlight lang="sql" line enclose="div" highlight="" >
 +
/************************************************************************************
 +
Mostrar os nomes dos xerentes das tendas nas que se fixo algunha venta
 +
************************************************************************************/
 +
select count(em.emp_nome) as Xerente
 +
from tendas as te join empregados as em on te.tda_xerente = em.emp_id
 +
where te.tda_id in (select distinct ven_tenda from vendas);
 +
</syntaxhighlight>
  
 
== Tarefa 3.5 ==
 
== Tarefa 3.5 ==
 
Importe total das vendas que se fixeron ao cliente LEANDRO FERREIRO BENITEZ.
 
Importe total das vendas que se fixeron ao cliente LEANDRO FERREIRO BENITEZ.
 +
 +
=== Solución ===
 +
<syntaxhighlight lang="sql" line enclose="div" highlight="" >
 +
/************************************************************************************
 +
Importe total das vendas que se fixeron ao cliente LEANDRO FERREIRO BENITEZ
 +
************************************************************************************/
 +
select sum((dv.dev_cantidade*dv.dev_prezo_unitario)*(1-dv.dev_desconto/100))
 +
as Importe_total
 +
from vendas as ve join detalle_vendas as dv on ve.ven_id = dv.dev_venda
 +
where ve.ven_cliente in (select clt_id
 +
                        from clientes
 +
                        where clt_apelidos='FERREIRO BENITEZ' and clt_nome='LEANDRO');
 +
</syntaxhighlight>
  
 
== Tarefa 3.6 ==  
 
== Tarefa 3.6 ==  
 
Seleccionar o id, apelidos e nome dos empregados que aínda non fixeron ningunha venda.             
 
Seleccionar o id, apelidos e nome dos empregados que aínda non fixeron ningunha venda.             
 +
 +
=== Solución ===
 +
<syntaxhighlight lang="sql" line enclose="div" highlight="" >
 +
/************************************************************************************
 +
Seleccionar o id, apelidos e nome dos empregados que aínda non fixeron ningunha venda.
 +
************************************************************************************/
 +
select emp_id, emp_apelidos, emp_nome
 +
from empregados
 +
where emp_id not in (select distinct ven_empregado from vendas);
 +
</syntaxhighlight>
  
 
== Tarefa 3.7 ==  
 
== Tarefa 3.7 ==  
 
Mostrar o número de departamento e o número de empregados dos departamentos que teñen un presuposto anual superior a 36000 euros.
 
Mostrar o número de departamento e o número de empregados dos departamentos que teñen un presuposto anual superior a 36000 euros.
 +
 +
=== Solución ===
 +
<syntaxhighlight lang="sql" line enclose="div" highlight="" >
 +
/*Mostrar o número de departamento e o número de empregados dos departamentos que teñen
 +
un presuposto anual superior a 36000 euros.*/
 +
select empDepartamento as Departamento,
 +
count(*) as Empregados
 +
from empregado
 +
where empDepartamento in (select depNumero
 +
from departamento
 +
where  depPresuposto>36000)
 +
group by empDepartamento;
 +
#sen utilizar subsentencias
 +
select em.empDepartamento as Departamento,
 +
count(*) as Empregados
 +
from empregado as em join departamento as de on em.empDepartamento=de.depNumero
 +
where  de.depPresuposto>36000
 +
group by em.empDepartamento;
 +
</syntaxhighlight>
  
 
== Tarefa 3.8 ==  
 
== Tarefa 3.8 ==  
 
Mostrar nome de departamento e de empregado para os empregados que traballan nalgún departamento que dependa do centro 'SEDE CENTRAL'. Os datos mostraranse ordenados por departamento e nome de empregado.
 
Mostrar nome de departamento e de empregado para os empregados que traballan nalgún departamento que dependa do centro 'SEDE CENTRAL'. Os datos mostraranse ordenados por departamento e nome de empregado.
 +
 +
=== Solución ===
 +
<syntaxhighlight lang="sql" line enclose="div" highlight="" >
 +
/*Mostrar nome de departamento e de empregado para os empregados que traballan nalgún
 +
departamento que dependa do centro 'SEDE CENTRAL'. Os datos mostraranse ordenados por
 +
nome de departamento e nome de empregado.*/
 +
select de.depNome as Departamento,
 +
em.empNome as Empregado
 +
from empregado as em join departamento as de on em.empDepartamento=de.depNumero
 +
where de.depCentro in (select cenNumero
 +
from Centro
 +
where cenNome like '%SEDE CENTRAL%')
 +
order by de.depNome, em.empNome;
 +
</syntaxhighlight>
  
 
== Tarefa 3.9 ==  
 
== Tarefa 3.9 ==  
 
Mostrar número de empregados e suma dos salarios, comisións e fillos, para os departamentos nos que existe algún empregado cun salario base mensual maior de 2000 euros.
 
Mostrar número de empregados e suma dos salarios, comisións e fillos, para os departamentos nos que existe algún empregado cun salario base mensual maior de 2000 euros.
 +
 +
=== Solución ===
 +
<syntaxhighlight lang="sql" line enclose="div" highlight="" >
 +
/*Mostrar número de departamento, número de empregados e suma dos salarios, comisións e
 +
fillos, para os departamentos nos que existe algún empregado cun salario base mensual
 +
maior de 2000 euros.*/
 +
select empDepartamento as Departamento,
 +
count(*) as Empregados,
 +
sum(empSalario) as Salarios,
 +
sum(ifnull(empComision,0)) as Comisions,
 +
sum(empFillos) as Fillos
 +
from empregado
 +
where empDepartamento in (select distinct empDepartamento
 +
from empregado
 +
where empSalario>2000)
 +
group by empDepartamento;
 +
</syntaxhighlight>
  
 
== Tarefa 3.10 ==  
 
== Tarefa 3.10 ==  
Línea 89: Línea 478:
 
* con subconsultas.
 
* con subconsultas.
 
* sen subconsultas.
 
* sen subconsultas.
 +
 +
=== Solución ===
 +
<syntaxhighlight lang="sql" line enclose="div" highlight="" >
 +
/*Mostrar o nome de todos os directores de departamento ordenados polo número de
 +
departamento.*/
 +
#Con subconsultas
 +
select  empDepartamento as Departamento,
 +
empNome as Director
 +
from  empregado
 +
where empNumero in (select depDirector
 +
  from departamento)
 +
order by empDepartamento;
 +
#Sen subconsultas
 +
select  em.empDepartamento as Departamento,
 +
em.empNome as Director
 +
from  empregado as em join departamento as de on em.empNumero=de.depDirector
 +
order by em.empDepartamento;
 +
</syntaxhighlight>
  
 
== Tarefa 3.11 ==  
 
== Tarefa 3.11 ==  
Línea 94: Línea 501:
 
* con subconsultas.
 
* con subconsultas.
 
* sen subconsultas.
 
* sen subconsultas.
 +
 +
=== Solución ===
 +
<syntaxhighlight lang="sql" line enclose="div" highlight="" >
 +
/*Mostrar os nomes dos directores de departamentos que dependen dun centro de traballo
 +
que ten un nome que empeza pola letra 'S'.*/
 +
#Con subconsultas
 +
select  empDepartamento as Departamento,
 +
empNome as Director
 +
from  empregado
 +
where empNumero in (select depDirector
 +
  from departamento
 +
  where depCentro in (select cenNumero
 +
from centro
 +
where cenNome  like 'S%'));
 +
#Sen subconsultas
 +
select em.empDepartamento as Departamento,
 +
em.empNome as Director
 +
from empregado as em join departamento as de on em.empNumero=de.depDirector
 +
join centro as ce on de.depCentro=ce.cenNumero
 +
where ce.cenNome like 'S%';
 +
</syntaxhighlight>
  
 
== Tarefa 3.12 ==  
 
== Tarefa 3.12 ==  
Línea 99: Línea 527:
 
* con subconsultas.
 
* con subconsultas.
 
* sen subconsultas.
 
* sen subconsultas.
 +
 +
=== Solución ===
 +
<syntaxhighlight lang="sql" line enclose="div" highlight="">
 +
/*A empresa decide gratificar aos directores en funcións incrementando o seu salario
 +
base un 5%. Mostrar ordenados alfabeticamente, os nomes destes empregados, o seu
 +
salario, a gratificación que lle corresponde, e o salario final que resulta de
 +
sumarlle a nova gratificación ao salario.*/
 +
#Con subconsultas
 +
select empNome as Empregado,
 +
empSalario as Salario_base,
 +
empSalario*0.05 as Gratificacion,
 +
empSalario+empSalario*0.05 as Salario_base_final
 +
from empregado
 +
where empNumero in (select depDirector
 +
  from departamento
 +
  where deptipoDirector='F')
 +
order by empNome;
 +
#Sen subconsultas
 +
select em.empNome as Empregado,
 +
em.empSalario as Salario_base,
 +
em.empSalario*0.05 as Gratificacion,
 +
em.empSalario+em.empSalario*0.05 as Salario_base_final
 +
from empregado as em join departamento as de on em.empNumero = de.depDirector
 +
where  de.deptipoDirector='F'
 +
order by em.empNome;
 +
</syntaxhighlight>
  
 
== Tarefa 3.13 ==  
 
== Tarefa 3.13 ==  
 
Mostrar nome e salario dos empregados co salario base maior cá media dos soldos dos directores que están en funcións.
 
Mostrar nome e salario dos empregados co salario base maior cá media dos soldos dos directores que están en funcións.
 +
 +
=== Solución ===
 +
<syntaxhighlight lang="sql" line enclose="div" highlight="" >
 +
/*Mostrar nome e salario dos empregados co salario base maior cá media dos soldos
 +
dos directores que están en funcións.*/
 +
select empNome as Empregado,
 +
empSalario as Salario
 +
from empregado
 +
where empSalario > (select avg(empSalario)
 +
  from empregado
 +
  where empNumero  in (select  depDirector
 +
from departamento
 +
where deptipoDirector='F'));
 +
</syntaxhighlight>
  
 
== Tarefa 3.14 ==  
 
== Tarefa 3.14 ==  
 
Mostrar nome e salario+comisións dos empregados do centro “RELACIÓN CON CLIENTES ” que gañan máis de 1500 euros entre salario e comisións, ordenados por departamento, salario+comisión e nome.
 
Mostrar nome e salario+comisións dos empregados do centro “RELACIÓN CON CLIENTES ” que gañan máis de 1500 euros entre salario e comisións, ordenados por departamento, salario+comisión e nome.
 +
 +
=== Solución ===
 +
<syntaxhighlight lang="sql" line enclose="div" highlight="" >
 +
/*Mostrar nome e salario+comisións dos empregados do centro 'RELACIÓN CON CLIENTES'
 +
que gañan máis de 1500 euros entre salario e comisións.
 +
*/
 +
select empNome as Empregado,
 +
(empSalario+ifnull(empComision,0)) as Salario
 +
from empregado join departamento on empDepartamento=depNumero
 +
where (empSalario+ifnull(empComision,0) >1500) 
 +
and depCentro = (select cenNumero
 +
  from centro
 +
  where cenNome='RELACION CON CLIENTES');
 +
</syntaxhighlight>
  
 
== Tarefa 3.15 ==  
 
== Tarefa 3.15 ==  
 
Mostrar os nomes dos empregados que traballan no mesmo departamento que Lavinia Sanz ou Cesar Pons.
 
Mostrar os nomes dos empregados que traballan no mesmo departamento que Lavinia Sanz ou Cesar Pons.
  
−
=== Solucións ===
+
=== Solución ===
 +
<syntaxhighlight lang="sql" line enclose="div" highlight="" >
 +
/*Mostrar os nomes dos empregados que traballan no mesmo departamento que Lavinia Sanz
 +
ou Cesar Pons. */
 +
select empDepartamento as Departamento,
 +
empNome as Empregado
 +
from empregado
 +
where empDepartamento in (select distinct empDepartamento
 +
from empregado
 +
where empNome in ('PONS, CESAR', 'SANZ, LAVINIA'));
 +
</syntaxhighlight>
  
 
= Tarefa 4. Realizar consultas utilizando subconsultas co predicado EXISTS =
 
= Tarefa 4. Realizar consultas utilizando subconsultas co predicado EXISTS =
Línea 119: Línea 611:
 
== Tarefa 4.1 ==
 
== Tarefa 4.1 ==
 
Mostrar a lista de artigos cun prezo de venta maior có prezo de venta do artigo máis barato de cor negra utilizando unha consulta de existencia.
 
Mostrar a lista de artigos cun prezo de venta maior có prezo de venta do artigo máis barato de cor negra utilizando unha consulta de existencia.
 +
 +
=== Solución ===
 +
<syntaxhighlight lang="sql" line enclose="div" highlight="" >
 +
/************************************************************************************
 +
Mostrar a lista de artigos cun prezo de venta maior có prezo de venta do artigo
 +
máis barato de cor negra, utilizando unha consulta de existencia
 +
************************************************************************************/
 +
select *
 +
from artigos as ar1
 +
where exists (select art_color
 +
from artigos as ar2
 +
where art_color="negro" and ar1.art_pv>ar2.art_pv);
 +
</syntaxhighlight>
  
 
== Tarefa 4.2 ==  
 
== Tarefa 4.2 ==  
Línea 124: Línea 629:
 
* sen utilizar join.
 
* sen utilizar join.
 
* utilizando join.
 
* utilizando join.
 +
 +
=== Solución ===
 +
<syntaxhighlight lang="sql" line enclose="div" highlight="" >
 +
/************************************************************************************
 +
Mostrar o nome dos artigos de cor negra que teñan algunha venta de máis de 5 unidades
 +
************************************************************************************/
 +
#Sen utilizar join
 +
select art_codigo  as Codigo,
 +
art_nome  as Articulo,
 +
art_color  as Color
 +
from artigos
 +
where art_color = "NEGRO"
 +
and exists (select dev_artigo
 +
from detalle_vendas
 +
where  dev_artigo  = art_codigo and dev_cantidade > 5);
 +
#Utilizando join
 +
select distinct ar.art_codigo as Codigo,
 +
ar.art_nome as Articulo,
 +
ar.art_color as Color
 +
from  artigos as ar join detalle_vendas as dv on dv.dev_artigo = ar.art_codigo
 +
where  ar.art_color="NEGRO" and dv.dev_cantidade > 5;
 +
</syntaxhighlight>
  
 
== Tarefa 4.3 ==  
 
== Tarefa 4.3 ==  
 
Mostrar nome e apelidos dos clientes que non fixeron ningunha compra.
 
Mostrar nome e apelidos dos clientes que non fixeron ningunha compra.
  
−
=== Solucións ===
+
=== Solución ===
 +
<syntaxhighlight lang="sql" line enclose="div" highlight="" >
 +
/************************************************************************************
 +
Mostrar nome e apelidos dos clientes que non fixeron ningunha compra.
 +
************************************************************************************/
 +
select clt_nome as Nome,
 +
  clt_apelidos as Apelidos
 +
from clientes
 +
where not exists (select * from vendas  where ven_cliente=clt_id);
 +
 
 +
/*outra forma alternativa*/
 +
select clt_nome as Nome,
 +
clt_apelidos as Apelidos
 +
from clientes
 +
where clt_id not in ( select ven_cliente from vendas );
 +
</syntaxhighlight>
  
 
= Tarefa 5. Realizar consultas utilizando subconsultas que non forman parte dunha condición =
 
= Tarefa 5. Realizar consultas utilizando subconsultas que non forman parte dunha condición =
Línea 137: Línea 679:
 
== Tarefa 5.1 ==  
 
== Tarefa 5.1 ==  
 
Mostrar as vendas diarias para cada tenda. A información que se debe mostrar é: código e poboación da tenda, data das vendas, suma dos importes das vendas na tenda nesa data con dous decimais, e acumulado dos importes das vendas feitas pola tenda ata esa data, con dous decimais.   
 
Mostrar as vendas diarias para cada tenda. A información que se debe mostrar é: código e poboación da tenda, data das vendas, suma dos importes das vendas na tenda nesa data con dous decimais, e acumulado dos importes das vendas feitas pola tenda ata esa data, con dous decimais.   
 +
 +
=== Solución ===
 +
<syntaxhighlight lang="sql" line enclose="div" highlight="" >
 +
/************************************************************************************
 +
Mostrar información resumo das vendas para cada tenda.
 +
A información que se debe mostrar é:
 +
Código da tenda, poboación da tenda, data de cada venda (só a data sen hora),
 +
suma dos importes das vendas na data, con 2 decimais
 +
(cantidade vendida por prezo unitario menos desconto) e
 +
acumulado dos importes das vendas feitas na tenda ata esa data, 2 decimais.
 +
************************************************************************************/
 +
select te.tda_id as Tenda,
 +
te.tda_poboacion as Poboacion,
 +
date(ve1.ven_data) as Data,
 +
round(sum(dv1.dev_prezo_unitario*dv1.dev_cantidade*(1-dv1.dev_desconto/100)),2)
 +
as Vendas,
 +
(select round(sum(dv2.dev_prezo_unitario*dv2.dev_cantidade*
 +
(1-dv2.dev_desconto/100)),2)
 +
from detalle_vendas as dv2 join vendas as ve2
 +
on dv2.dev_venda = ve2.ven_id
 +
where ve1.ven_tenda = ve2.ven_tenda and date(ve2.ven_data)<=date(ve1.ven_data))
 +
as Acumulado   
 +
from detalle_vendas as dv1 join vendas as ve1 on dv1.dev_venda = ve1.ven_id
 +
join tendas as te on te.tda_id = ve1.ven_tenda
 +
group by te.tda_id, date(ve1.ven_data);
 +
</syntaxhighlight>
  
 
== Tarefa 5.2 ==  
 
== Tarefa 5.2 ==  
 
Seleccionar o número de departamento, a media dos salarios do departamento, e o salario medio de todos os empregados da empresa, para os departamentos que teñen un salario medio maior que o salario medio de todos os empregados da empresa.
 
Seleccionar o número de departamento, a media dos salarios do departamento, e o salario medio de todos os empregados da empresa, para os departamentos que teñen un salario medio maior que o salario medio de todos os empregados da empresa.
 +
 +
=== Solución ===
 +
<syntaxhighlight lang="sql" line enclose="div" highlight="" >
 +
/*Seleccionar o número de departamento, a media dos salarios do departamento, e o
 +
salario medio de todos os empregados da empresa, para os departamentos que teñen un
 +
salario medio maior que o salario medio de todos os empregados da empresa. */
 +
select empDepartamento as Departamento,
 +
avg(empSalario) as Media_departamento,
 +
(select avg(empSalario)from empregado) as Media_empregados
 +
from empregado
 +
group by empDepartamento
 +
having avg(empSalario)>(select avg(empSalario)from empregado);
 +
</syntaxhighlight>
  
 
== Tarefa 5.3 ==  
 
== Tarefa 5.3 ==  
 
Mostrar o nome dos empregados que non son directores, e a diferenza do salario base respecto ao do empregado que menos cobra. Ordenar o resultado de forma descendente pola diferenza e ascendente polo nome.
 
Mostrar o nome dos empregados que non son directores, e a diferenza do salario base respecto ao do empregado que menos cobra. Ordenar o resultado de forma descendente pola diferenza e ascendente polo nome.
  
−
=== Solucións ===
+
=== Solución ===
 +
<syntaxhighlight lang="sql" line enclose="div" highlight="" >
 +
/*Mostrar o nome dos empregados que non son directores, e a diferenza do soldo respecto
 +
ao empregado que menos cobra. Ordenar o resultado de forma descendente pola diferenza e
 +
ascendente polo nome.*/
 +
 
 +
select empNome as Empregado,
 +
empSalario-(select min(empSalario) from empregado) as Diferenza
 +
from empregado
 +
where empNumero not in (select depDirector
 +
from departamento
 +
where  depDirector is not null)
 +
order by Diferenza desc, Empregado desc;
 +
</syntaxhighlight>

Revisión actual del 07:25 24 nov 2021

Sumario

Tarefa 1. Realizar consultas que utilicen os operadores relacionais combinados con subconsultas dentro dunha condición

A tarefa consiste en realizar as seguintes consultas utilizando operadores relacionais pra relacionar unha consulta principal con subconsultas dentro dunha condición.

As tarefas do 1.1 a 1.6 serán sobre a base de datos tendaBD e do 1.7 a 1.12 sobre a base de datos traballadores

Tarefa 1.1

Seleccionar código e peso dos artigos que teñen o peso máis pequeno có peso do artigo de código '077WN45'.

Solución

1 ************************************************************************************
2 Seleccionar código e peso dos artigos que teñen o peso máis pequeno có peso do artigo 
3 de código '077WN45'
4 ************************************************************************************/
5 select  art_codigo Codigo,
6 	art_peso Peso
7 from artigos
8 where art_peso < (select art_peso from artigos where art_codigo='077WN45')
9 order by art_peso;

Tarefa 1.2

Seleccionar código, nome, peso e cor dos artigos da mesma cor có artigo de código '242C283', e que teñan un peso maior ou igual co peso medio de todos os artigos.

Solución

 1 /************************************************************************************
 2 Seleccionar código, nome, peso e cor dos artigos da mesma cor có artigo de código
 3 '242C283', e que teñan un peso maior ou igual co peso medio de todos os artigos
 4 ************************************************************************************/
 5 select  art_codigo,
 6 	art_nome,
 7 	art_peso,
 8 	art_color
 9 from artigos
10 where art_color = (select art_color from artigos where art_codigo = '242C283')
11 	and art_peso >= (select avg(art_peso) from artigos);

Tarefa 1.3

Seleccionar código da tenda e nome do xerente das tendas nas que se vendeu polo menos unha unidade do artigo de código ' 077WN45'.

Solución

 1 /************************************************************************************
 2 Seleccionar código da tenda e nome do xerente, das tendas nas que se vendeu polo menos 
 3 unha unidade do artigo de código 077WN45
 4 ************************************************************************************/
 5 select te.tda_id as Codigo_tenda,
 6 em.emp_nome as Nome_xerente
 7 from  tendas as te join empregados as em on te.tda_xerente = em.emp_id
 8 where te.tda_id = any (select distinct ve.ven_tenda
 9 				from vendas as ve join detalle_vendas as dv 
10 					on ve.ven_id = dv.dev_venda
11 				where dv.dev_artigo  =  '077WN45');

Tarefa 1.4

Mostrar a lista de artigos cun prezo de venta maior có prezo de venta do artigo máis barato de cor negra. Obter o mesmo resultado nos dous casos seguintes:

  • Utilizando any.
  • Utilizando a función min().

Solución

 1 /************************************************************************************
 2 Mostrar a lista de artigos cun prezo de venta maior có prezo de venta do artigo 
 3 máis barato de cor negra, utilizando any
 4 ************************************************************************************/
 5 select *
 6 from artigos
 7 where art_pv > any (select art_pv
 8                     from artigos
 9                     where art_color="NEGRO");
10 /************************************************************************************
11 Mostrar a lista de artigos cun prezo de venta maior có prezo de venta do artigo 
12 máis barato de cor negra, utilizando a función min().
13 ************************************************************************************/
14 select *
15 from artigos
16 where art_pv > (select min(art_pv)
17                 from artigos
18                 where art_color="NEGRO");

Tarefa 1.5

Seleccionar código de artigo, descrición do artigo e código do provedor para os artigos que subministra o provedor que ten o nome SEAGATE.

Solución

 1 /************************************************************************************
 2 Seleccionar código de artigo, descrición do artigo e código do provedor para os artigos
 3 que subministra o provedor SEAGATE
 4 ************************************************************************************/
 5 select art_codigo as Artigo,
 6 	art_nome as Descripción,
 7 	art_provedor as 'Código de provedor'
 8 from artigos
 9 where art_provedor = ( select prv_id
10 				from provedores
11 				where prv_nome = 'SEAGATE');
12 /* outra forma, combinando as táboas */
13 select art_codigo, art_nome, art_provedor
14 from artigos join provedores on art_provedor=prv_id
15 where prv_nome="SEAGATE";

Tarefa 1.6

Mostrar información do artigo máis caro, Seleccionando o seu código, nome, prezo de venta e nome do provedor que o subministra.

Solución

 1 /************************************************************************************
 2 Mostrar información do artigo máis caro, Seleccionando o seu código, nome, prezo de
 3 venda e nome do provedor que o subministra.
 4 ************************************************************************************/
 5 select ar.art_codigo as Articulo,
 6 	ar.art_nome as Descipcion,
 7 	ar.art_pv as Prezo_venda,
 8 	pr.prv_nome as Proveedor
 9 from artigos as ar join provedores as pr on ar.art_provedor=pr.prv_id
10 where ar.art_pv = (select max(art_pv) from artigos);

Tarefa 1.7

Mostrar nomes e salarios dos empregados que teñan salario maior có salario de tódolos empregados do departamento 122. O resultado móstrase ordenado por nome de empregado.

Solución

 1 /*Mostrar nomes e salarios dos empregados que teñan salario maior có salario de 
 2 tódolos empregados do departamento 122. O resultado móstrase ordenado por nome de 
 3 empregado.*/
 4 select empNome as Nome,
 5 	empSalario as Salario
 6 from empregado
 7 where empSalario > (select max(empSalario)
 8 				from empregado
 9 				where empDepartamento=122)
10 order by empNome;
11 /*Outra forma*/
12 select empNome empregadoado,
13 	empSalario as Salario
14 from empregado
15 where empSalario > all (select empSalario
16 				from empregado
17 				where empDepartamento=122)
18 order by empNome;

Tarefa 1.8

Mostrar os nomes e salarios dos empregados con salarios maiores ou iguais a o de Claudia Fierro, ordenados alfabeticamente. Resolver esta consulta:

  • con subconsulta.
  • sen subconsulta utilizando JOIN.

Solución

 1 /*Mostrar os nomes e salarios dos empregados con salarios maiores ou iguais a o
 2 de Claudia Fierro, ordenados alfabeticamente.*/
 3 #Con subconsulta
 4 select empNome as Eempregado,
 5 	empSalario as Salario
 6 from empregado
 7 where empSalario >=
 8 		(select empSalario
 9 		from empregado
10 		where empNome = 'FIERRO, CLAUDIA')
11 order by empNome;
12 
13 #Sen subconsulta utilizando join para facer unha autocombinación
14 select  emp1.empNome as Eempregado,
15 	emp1.empSalario as Salario	
16 from empregado as emp1 join empregado as emp2 
17 	on emp1.empSalario >= emp2.empSalario
18 where emp2.empNome = 'FIERRO, CLAUDIA'
19 order by emp1.empNome;

Tarefa 1.9

Mostrar nome e presuposto dos departamentos que teñen o presuposto máis alto e o máis baixo.

Solución

1 /*Mostrar nome e presuposto dos departamentos que teñen o presuposto máis alto e o
2 máis baixo.*/
3 select depNome as Departamento,
4 	depPresuposto as Presuposto
5 from  departamento
6 where depPresuposto = (select min(depPresuposto) from  departamento)
7 	or depPresuposto = (select max(depPresuposto) from  departamento);

Tarefa 1.10

Mostrar número, nome, data de ingreso na empresa e nome do departamento no que traballa o empregado ou empregados que levan máis tempo na empresa.

Solución

1 /*Mostrar número, nome, data de ingreso na empresa e nome do departamento no que
2 traballa o empregado ou empregados que levan máis tempo na empresa.*/
3 select em.empNumero as Numero,
4 	em.empNome as Nome,
5 	em.empDataIngreso as Data,
6 	de.depNome as Departamento
7 from  departamento as de join empregado as em on em.empDepartamento=de.depNumero
8 where em.empDataIngreso = (select min(empDataIngreso) from  empregado);

Tarefa 1.11

Mostrar nome e salario dos empregados que cumpran algunha das seguintes condicións:

  • Ingresaron na empresa despois do 1-1-88.
  • Ingresaron na empresa antes do 1-1-88 pero teñen un salario inferior ao salario medio de todos os empregados da empresa.

Solución

 1 /*Mostrar nome e salario dos empregados que cumpran algunha das seguintes condicións:
 2 –	Ingresaron na empresa despois do 1-1-88.
 3 –	Ingresaron na empresa antes do 1-1-88 pero teñen un salario inferior ao salario medio
 4 de todos os empregados da empresa.*/
 5 select empNome as Empregado,
 6 	empSalario as Salario
 7 from empregado 
 8 where empDataIngreso >'1988/01/01' 
 9 	or ( empDataIngreso <='1988/01/01'
10 	and empSalario < (select avg(empSalario) from empregado))
11 order by empNome;

Tarefa 1.12

Mostrar a extensión telefónica asignada a máis empregados indicando o número de empregados que a comparten. Se hai máis dunha, deben aparecer todas.

Solución

 1 /*Mostrar a extensión telefónica asignada a máis empregados indicando o número de
 2 empregados que a comparten. Se hai máis dunha, deben aparecer todas.*/
 3 select empExtension as Extension,
 4 	count(*) as Empregados
 5 from empregado
 6 group by empExtension
 7 having count(*)=(select count(*)
 8 			from empregado
 9 			group by empExtension
10 			order by 1 desc limit 1);

Tarefa 2. Realizar consultas que utilicen o predicado BETWEEN combinado con subconsultas dentro dunha condición

A tarefa consiste en realizar as seguintes consultas utilizando o predicado BETWEEN para relacionar unha consulta principal con subconsultas dentro dunha condición.

As tarefas do 2.1 será sobre a base de datos tendaBD e a tarefa 2.2 sobre a base de datos traballadores

Tarefa 2.1

Mostrar nome e prezo de venda dos artigos que teñen un prezo de venda comprendido entre o prezo do artigo '0713242' e a media de prezos de todos os artigos. Os datos deben mostrarse ordenados alfabeticamente polo nome do artigo.

Solución

 1 /************************************************************************************
 2 Mostrar nome e prezo de venda dos artigos que teñen un prezo de venda comprendido 
 3 entre o prezo do artigo '0713242' e a media de prezos de todos os artigos.
 4 Os datos deben mostrarse ordenados alfabeticamente polo nome do artigo.
 5 ************************************************************************************/
 6 select art_nome as Artigo,
 7 	art_pv as Prezo
 8 from artigos
 9 where art_pv between (select art_pv from artigos where art_codigo='0713242')
10 	and (select avg(art_pv) from artigos)
11 order by art_nome;

Tarefa 2.2

Seleccionar o nome e número de departamento dos empregados que pertenzan a un departamento cun presuposto comprendido entre os presupostos dos departamentos 122 e 121 (incluídos). Os datos deben mostrarse ordenados de menor a maior polo número do departamento.

Solución

 1 /* Seleccionar o  nome e número de departamento dos empregados que pertenzan a un
 2 departamento cun presuposto comprendido entre os presupostos dos departamentos 122
 3 e 121 (incluídos).  Os datos deben mostrarse ordenados de menor a maior polo número
 4 do departamento*/
 5 select empNome, empDepartamento
 6 from empregado
 7 where empDepartamento = any (select depNumero
 8 				 	from departamento
 9                           	where depPresuposto between (select depPresuposto 
10 									from departamento 
11                                                        		where depNUmero = 122)
12 					   and (select depPresuposto 
13 						from departamento
14 						where depNUmero = 121))
15 order by empDepartamento desc;
16 /* Para mostrar, ademais, algunha columna da táboa departamento hai que facer un join.
17 Exemplo: mostrando, ademais, o nome do departamento*/
18 select empNome, empDepartamento, depNome   
19 from empregado join departamento on empDepartamento = depNumero
20 where depPresuposto 
21 	between (select depPresuposto 
22 		 from departamento 
23 		 where depNUmero = 122)
24 	and (select depPresuposto 
25 		from departamento 
26 		where depNUmero = 121)
27 order by empDepartamento desc;

Tarefa 3. Realizar consultas utilizando subconsultas co predicado IN

A tarefa consiste en realizar as seguintes consultas utilizando o predicado IN para relacionar unha consulta principal con subconsultas dentro dunha condición.

As tarefas do 3.1 a 3.6 serán sobre a base de datos tendaBD e do 3.7 a 3.15 sobre a base de datos traballadores

Tarefa 3.1

Obter a lista de provedores que subministran como mínimo un artigo de cor negra.

Solución

1 /************************************************************************************
2 Obter a lista de provedores que subministran como mínimo un artigo de cor negra
3 ************************************************************************************/
4 select *
5 from  provedores
6 where prv_id in (select distinct art_provedor
7 			from artigos
8 			where art_color  =  "NEGRO") ;

Tarefa 3.2

Mostrar identificador de cliente, apelidos e nome na mesma columna separados por coma, para os clientes que só teñen unha venda. O resultado estará ordenado polo identificador do cliente.

Solución

 1 /************************************************************************************
 2 Mostrar identificador de cliente, apelidos e nome na mesma columna separados por coma, 
 3 para os clientes que só teñen unha venda. O resultado estará 
 4 ordenado polo identificador do cliente
 5 ************************************************************************************/
 6 select clt_id as Cliente,
 7 	concat(clt_apelidos,', ',clt_nome) as 'Apelidos e nome'
 8 from clientes
 9 where clt_id in (select ven_cliente
10 			from vendas
11 			group by ven_cliente
12 			having count(*) = 1)
13 order by clt_id;
14 /* outra forma alternativa utilizando referencias externas na subconsulta*/
15 select clt_id as Cliente,
16 	concat(clt_apelidos,', ',clt_nome) as 'Apelidos e nome'
17 from clientes
18 where 1 = (select count(*)
19 		from vendas
20 		where ven_cliente = clt_id)
21 order by clt_id;

Tarefa 3.3

Mostrar identificador e nome dos clientes que fixeron algunha compra despois do día en que o cliente número 6 fixo a súa última compra.

Solución

 1 /************************************************************************************
 2 Mostrar identificador e nome dos clientes que fixeron algunha compra despois do día 
 3 en que o cliente número 6 fixo a súa última compra.
 4 ************************************************************************************/
 5 select clt_id, clt_nome
 6 from clientes
 7 where clt_id in ( select ven_cliente
 8                   from vendas
 9                   where ven_data>(select max(ven_data)
10                                   from vendas
11                                   where ven_cliente=6));
12 /* Tamén valería a condición:  
13 where ven_data > ALL (SELECT ven_data FROM vendas WHERE ven_cliente=6) */
14 
15 /* Outra solución podería ser */
16 select distinct ve.ven_cliente as Cliente, 
17 	cl.clt_nome as Nome
18 from vendas as ve join clientes as cl on ve.ven_cliente=cl.clt_id
19 where ve.ven_data > ( select max(ven_data)
20                      from vendas
21                      where ven_cliente=6 );

Tarefa 3.4

Mostrar os nomes dos xerentes das tendas nas que se fixo algunha venta.

Solución

1 /************************************************************************************
2 Mostrar os nomes dos xerentes das tendas nas que se fixo algunha venta
3 ************************************************************************************/
4 select count(em.emp_nome) as Xerente
5 from tendas as te join empregados as em on te.tda_xerente = em.emp_id
6 where te.tda_id in (select distinct ven_tenda from vendas);

Tarefa 3.5

Importe total das vendas que se fixeron ao cliente LEANDRO FERREIRO BENITEZ.

Solución

1 /************************************************************************************
2 Importe total das vendas que se fixeron ao cliente LEANDRO FERREIRO BENITEZ
3 ************************************************************************************/
4 select sum((dv.dev_cantidade*dv.dev_prezo_unitario)*(1-dv.dev_desconto/100)) 
5 	as Importe_total
6 from vendas as ve join detalle_vendas as dv on ve.ven_id = dv.dev_venda
7 where ve.ven_cliente in (select clt_id
8                         	from clientes
9                         	where clt_apelidos='FERREIRO BENITEZ' and clt_nome='LEANDRO');

Tarefa 3.6

Seleccionar o id, apelidos e nome dos empregados que aínda non fixeron ningunha venda.

Solución

1 /************************************************************************************
2 Seleccionar o id, apelidos e nome dos empregados que aínda non fixeron ningunha venda.
3 ************************************************************************************/
4 select emp_id, emp_apelidos, emp_nome
5 from empregados
6 where emp_id not in (select distinct ven_empregado from vendas);

Tarefa 3.7

Mostrar o número de departamento e o número de empregados dos departamentos que teñen un presuposto anual superior a 36000 euros.

Solución

 1 /*Mostrar o número de departamento e o número de empregados dos departamentos que teñen
 2 un presuposto anual superior a 36000 euros.*/
 3 select empDepartamento as Departamento,
 4 	count(*) as Empregados
 5 from empregado
 6 where empDepartamento in 	(select depNumero
 7 				 from departamento
 8 				 where  depPresuposto>36000)
 9 group by empDepartamento;
10 #sen utilizar subsentencias
11 select em.empDepartamento as Departamento,
12 	count(*) as Empregados
13 from empregado as em join departamento as de on em.empDepartamento=de.depNumero
14 where  de.depPresuposto>36000
15 group by em.empDepartamento;

Tarefa 3.8

Mostrar nome de departamento e de empregado para os empregados que traballan nalgún departamento que dependa do centro 'SEDE CENTRAL'. Os datos mostraranse ordenados por departamento e nome de empregado.

Solución

 1 /*Mostrar nome de departamento e de empregado para os empregados que traballan nalgún
 2 departamento que dependa do centro 'SEDE CENTRAL'. Os datos mostraranse ordenados por
 3 nome de departamento e nome de empregado.*/
 4 select de.depNome as Departamento,
 5 	em.empNome as Empregado
 6 from empregado as em join departamento as de on em.empDepartamento=de.depNumero
 7 where de.depCentro in (select cenNumero 
 8 				from Centro
 9 				where cenNome like '%SEDE CENTRAL%')
10 order by de.depNome, em.empNome;

Tarefa 3.9

Mostrar número de empregados e suma dos salarios, comisións e fillos, para os departamentos nos que existe algún empregado cun salario base mensual maior de 2000 euros.

Solución

 1 /*Mostrar número de departamento, número de empregados e suma dos salarios, comisións e
 2 fillos, para os departamentos nos que existe algún empregado cun salario base mensual
 3 maior de 2000 euros.*/
 4 select empDepartamento as Departamento,
 5 	count(*) as Empregados,
 6 	sum(empSalario) as Salarios,
 7 	sum(ifnull(empComision,0)) as Comisions,
 8 	sum(empFillos) as Fillos
 9 from empregado
10 where empDepartamento in 	(select distinct empDepartamento
11 				 from empregado
12 				where empSalario>2000)
13 group by empDepartamento;

Tarefa 3.10

Mostrar o nome de todos os directores de departamento ordenados polo número de departamento. Resolver a consulta:

  • con subconsultas.
  • sen subconsultas.

Solución

 1 /*Mostrar o nome de todos os directores de departamento ordenados polo número de
 2 departamento.*/
 3 #Con subconsultas
 4 select  empDepartamento as Departamento,
 5 	empNome as Director
 6 from  empregado
 7 where empNumero in (select depDirector 
 8 			  from departamento)
 9 order by empDepartamento;
10 #Sen subconsultas
11 select  em.empDepartamento as Departamento,
12 	em.empNome as Director
13 from  empregado as em join departamento as de on em.empNumero=de.depDirector
14 order by em.empDepartamento;

Tarefa 3.11

Mostrar os nomes dos directores de departamentos que dependen dun centro de traballo que ten un nome que empeza pola letra 'S'. Resolver a consulta:

  • con subconsultas.
  • sen subconsultas.

Solución

 1 /*Mostrar os nomes dos directores de departamentos que dependen dun centro de traballo
 2 que ten un nome que empeza pola letra 'S'.*/
 3 #Con subconsultas
 4 select  empDepartamento as Departamento,
 5 	empNome as Director
 6 from  empregado
 7 where empNumero in (select depDirector
 8 			  from departamento 
 9 			  where depCentro in (select cenNumero
10 						from centro
11 						where cenNome  like 'S%'));
12 #Sen subconsultas
13 select em.empDepartamento as Departamento,
14 	em.empNome as Director
15 from empregado as em join departamento as de on em.empNumero=de.depDirector
16 	join centro as ce on de.depCentro=ce.cenNumero
17 where ce.cenNome like 'S%';

Tarefa 3.12

A empresa decide gratificar aos directores en funcións incrementando o seu salario base un 5%. Mostrar ordenados alfabeticamente, os nomes destes empregados, o seu salario, a gratificación que lle corresponde, e o salario final que resulta de sumarlle a nova gratificación ao salario. Resolver a consulta:

  • con subconsultas.
  • sen subconsultas.

Solución

 1 /*A empresa decide gratificar aos directores en funcións incrementando o seu salario
 2 base un 5%. Mostrar ordenados alfabeticamente, os nomes destes empregados, o seu
 3 salario, a gratificación que lle corresponde, e o salario final que resulta de 
 4 sumarlle a nova gratificación ao salario.*/
 5 #Con subconsultas
 6 select empNome as Empregado,
 7 	empSalario as Salario_base,
 8 	empSalario*0.05 as Gratificacion,
 9 	empSalario+empSalario*0.05 as Salario_base_final
10 from empregado
11 where empNumero in (select depDirector
12 			   from departamento
13 			   where deptipoDirector='F')
14 order by empNome;
15 #Sen subconsultas
16 select em.empNome as Empregado,
17 	em.empSalario as Salario_base,
18 	em.empSalario*0.05 as Gratificacion,
19 	em.empSalario+em.empSalario*0.05 as Salario_base_final
20 from empregado as em join departamento as de on em.empNumero = de.depDirector
21 where  de.deptipoDirector='F'
22 order by em.empNome;

Tarefa 3.13

Mostrar nome e salario dos empregados co salario base maior cá media dos soldos dos directores que están en funcións.

Solución

 1 /*Mostrar nome e salario dos empregados co salario base maior cá media dos soldos
 2 dos directores que están en funcións.*/
 3 select empNome as Empregado,
 4 	empSalario as Salario
 5 from empregado
 6 where empSalario > (select avg(empSalario) 
 7 			  from empregado
 8 			  where empNumero  in 	(select  depDirector 
 9 						from departamento
10 						where deptipoDirector='F'));

Tarefa 3.14

Mostrar nome e salario+comisións dos empregados do centro “RELACIÓN CON CLIENTES ” que gañan máis de 1500 euros entre salario e comisións, ordenados por departamento, salario+comisión e nome.

Solución

 1 /*Mostrar nome e salario+comisións dos empregados do centro 'RELACIÓN CON CLIENTES'
 2 que gañan máis de 1500 euros entre salario e comisións.
 3 */
 4 select empNome as Empregado,
 5 	(empSalario+ifnull(empComision,0)) as Salario
 6 from empregado join departamento on empDepartamento=depNumero
 7 where (empSalario+ifnull(empComision,0) >1500)  
 8 	and depCentro = (select cenNumero
 9 			  from centro
10 			  where cenNome='RELACION CON CLIENTES');

Tarefa 3.15

Mostrar os nomes dos empregados que traballan no mesmo departamento que Lavinia Sanz ou Cesar Pons.

Solución

1 /*Mostrar os nomes dos empregados que traballan no mesmo departamento que Lavinia Sanz
2 ou Cesar Pons. */
3 select empDepartamento as Departamento,
4 	empNome as Empregado
5 from empregado
6 where empDepartamento in 	(select distinct empDepartamento
7 				 from empregado
8 				 where empNome in ('PONS, CESAR', 'SANZ, LAVINIA'));

Tarefa 4. Realizar consultas utilizando subconsultas co predicado EXISTS

A tarefa consiste en realizar as seguintes consultas utilizando o predicado EXISTS para relacionar unha consulta principal con subconsultas dentro dunha condición.

As tarefas serán sobre a base de datos tendaBD

Tarefa 4.1

Mostrar a lista de artigos cun prezo de venta maior có prezo de venta do artigo máis barato de cor negra utilizando unha consulta de existencia.

Solución

1 /************************************************************************************
2 Mostrar a lista de artigos cun prezo de venta maior có prezo de venta do artigo 
3 máis barato de cor negra, utilizando unha consulta de existencia
4 ************************************************************************************/
5 select *
6 from artigos as ar1
7 where exists (select art_color
8 			from artigos as ar2
9 			where art_color="negro" and ar1.art_pv>ar2.art_pv);

Tarefa 4.2

Mostrar o nome dos artigos de cor negra que teñan algunha venda de máis de 5 unidades. Obter o mesmo resultado nos dous casos seguintes:

  • sen utilizar join.
  • utilizando join.

Solución

 1 /************************************************************************************
 2 Mostrar o nome dos artigos de cor negra que teñan algunha venta de máis de 5 unidades
 3 ************************************************************************************/
 4 #Sen utilizar join
 5 select art_codigo  as Codigo,
 6 	art_nome   as Articulo,
 7 	art_color   as Color
 8 from artigos
 9 where art_color = "NEGRO"
10 	and exists (select dev_artigo
11 			from detalle_vendas
12 			where  dev_artigo  = art_codigo and dev_cantidade > 5);
13 #Utilizando join
14 select distinct ar.art_codigo as Codigo,
15 	ar.art_nome as Articulo,
16 	ar.art_color as Color
17 from  artigos as ar join detalle_vendas as dv on dv.dev_artigo = ar.art_codigo
18 where  ar.art_color="NEGRO" and dv.dev_cantidade > 5;

Tarefa 4.3

Mostrar nome e apelidos dos clientes que non fixeron ningunha compra.

Solución

 1 /************************************************************************************
 2 Mostrar nome e apelidos dos clientes que non fixeron ningunha compra.
 3 ************************************************************************************/
 4 select clt_nome as Nome,
 5    clt_apelidos as Apelidos
 6 from clientes
 7 where not exists (select * from vendas  where ven_cliente=clt_id);
 8 
 9 /*outra forma alternativa*/
10 select clt_nome as Nome,
11 	clt_apelidos as Apelidos
12 from clientes
13 where clt_id not in ( select ven_cliente from vendas );

Tarefa 5. Realizar consultas utilizando subconsultas que non forman parte dunha condición

A tarefa consiste en realizar as seguintes consultas utilizando subconsultas que non formen parte dunha condición.

As tarefa 5.1 será sobre a base de datos tendaBD e as tarefas 5.2 e 5.3 sobre a base de datos traballadores.

Tarefa 5.1

Mostrar as vendas diarias para cada tenda. A información que se debe mostrar é: código e poboación da tenda, data das vendas, suma dos importes das vendas na tenda nesa data con dous decimais, e acumulado dos importes das vendas feitas pola tenda ata esa data, con dous decimais.

Solución

 1 /************************************************************************************
 2 Mostrar información resumo das vendas para cada tenda. 
 3 A información que se debe mostrar é:
 4 Código da tenda, poboación da tenda, data de cada venda (só a data sen hora), 
 5 suma dos importes das vendas na data, con 2 decimais 
 6 (cantidade vendida por prezo unitario menos desconto) e
 7 acumulado dos importes das vendas feitas na tenda ata esa data, 2 decimais.
 8 ************************************************************************************/
 9 select te.tda_id as Tenda,
10 	te.tda_poboacion as Poboacion,
11 	date(ve1.ven_data) as Data,	
12 	round(sum(dv1.dev_prezo_unitario*dv1.dev_cantidade*(1-dv1.dev_desconto/100)),2)
13 		as Vendas,
14 	(select round(sum(dv2.dev_prezo_unitario*dv2.dev_cantidade*
15 			(1-dv2.dev_desconto/100)),2)
16 	 from detalle_vendas as dv2 join vendas as ve2 
17 		on dv2.dev_venda = ve2.ven_id
18 	 where ve1.ven_tenda = ve2.ven_tenda and date(ve2.ven_data)<=date(ve1.ven_data))
19 		as Acumulado    
20 from detalle_vendas as dv1 join vendas as ve1 on dv1.dev_venda = ve1.ven_id
21 	join tendas as te on te.tda_id = ve1.ven_tenda
22 group by te.tda_id, date(ve1.ven_data);

Tarefa 5.2

Seleccionar o número de departamento, a media dos salarios do departamento, e o salario medio de todos os empregados da empresa, para os departamentos que teñen un salario medio maior que o salario medio de todos os empregados da empresa.

Solución

1 /*Seleccionar o número de departamento, a media dos salarios do departamento, e o
2 salario medio de todos os empregados da empresa, para os departamentos que teñen un
3 salario medio maior que o salario medio de todos os empregados da empresa. */
4 select empDepartamento as Departamento,
5 	avg(empSalario) as Media_departamento,
6 	(select avg(empSalario)from empregado) as Media_empregados
7 from empregado
8 group by empDepartamento
9 having avg(empSalario)>(select avg(empSalario)from empregado);

Tarefa 5.3

Mostrar o nome dos empregados que non son directores, e a diferenza do salario base respecto ao do empregado que menos cobra. Ordenar o resultado de forma descendente pola diferenza e ascendente polo nome.

Solución

 1 /*Mostrar o nome dos empregados que non son directores, e a diferenza do soldo respecto
 2 ao empregado que menos cobra. Ordenar o resultado de forma descendente pola diferenza e
 3 ascendente polo nome.*/
 4 
 5 select empNome as Empregado,
 6 	empSalario-(select min(empSalario) from empregado) as Diferenza
 7 from empregado
 8 where empNumero not in (select depDirector
 9 				from departamento
10 				where  depDirector is not null)
11 order by Diferenza desc, Empregado desc;
Obtenido de «https://wiki.cifprodolfoucha.es/index.php?title=Solucións_ás_tarefas_do_boletín_de_subconsultas&oldid=17365»