Solucións ás tarefas do boletín de subconsultas

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;