De Manuais Referencia Departamento Informatica
Esta páxina está en probas.
- Exercicios SQL
- Os exercicios do 1 ó 51 son máis sinxelos que os do 52 ó 76.
- Resolver usando SQL os exercicios do 1 ó 76.
- As solucións ás consultas SQL deben estar nun só documento de texto co seguinte formato:
Consulta XX ------
SELECT ‘Consulta XX’, ... FROM ... WHERE ... ....
- As consultas marcadas en negriña deben estar comentadas en canto a solución aportada, problemas atopados, etc.
- O SALARIO refírese a columna SAL, mentres que o SOLDO ou SALARIO TOTAL, á suma de SAL e COM.
- A BD está creada como segue, no Analizador de Consultas de SQL Server 2000.
Create database consultas;
go
use consultas;
CREATE TABLE dept ( DEPTNO varchar (2) , DNAME varchar (12) , LOC varchar (10) , Primary Key (DEPTNO));
CREATE TABLE emp ( EMPNO varchar (4) , ENAME varchar (8) , JOB varchar (10) , MGR varchar (4) , HIREDATE datetime , SAL float , COMM real , DEPTNO varchar (2), Primary Key (EMPNO), foreign key (DEPTNO) references dept(DEPTNO));
insert into DEPT values ('10','ACCOUNTING', 'NEW YORK'); insert into DEPT values ('20','RESEARCH', 'DALLAS'); insert into DEPT values ('30','SALES', 'CHICAGO'); insert into DEPT values ('40','OPERATIONS', 'BOSTON');
insert into EMP values('7369', 'SMITH', 'CLERK', '7902', '17-12-1980', 800.0, NULL ,'20'); insert into EMP values('7499', 'ALLEN', 'SALESMAN', '7698', '20-02-1981', 1600.0, 300.0 ,'30'); insert into EMP values('7521', 'WARD', 'SALESMAN', '7698', '22-02-1981', 1250.0, 500.0 ,'30'); insert into EMP values('7566', 'JONES', 'MANAGER', '7839', '02-04-1981', 2975.0, NULL ,'20'); insert into EMP values('7654', 'MARTIN','SALESMAN', '7698', '28-10-1981', 1250.0, 1400.0 ,'30'); insert into EMP values('7698', 'BLAKE', 'MANAGER', '7839', '01-05-1981', 2850.0, NULL ,'30'); insert into EMP values('7782', 'CLARK', 'MANAGER', '7839', '09-07-1981', 2450.0, NULL ,'10'); insert into EMP values('7788', 'SCOTT', 'ANALYST', '7566', '09-11-1981', 3000.0, NULL ,'20'); insert into EMP values('7839', 'KING', 'PRESIDENT', NULL, '17-11-1981', 5000.0, NULL ,'10'); insert into EMP values('7844', 'TURNER','SALESMAN', '7698', '08-10-1981', 1500.0, 0.0 ,'30'); insert into EMP values('7876', 'ADAMS', 'CLERK', '7788', '23-10-1981', 1100.0, NULL ,'20'); insert into EMP values('7900', 'JAMES', 'CLERK', '7698', '03-12-1981', 950.0, NULL ,'30'); insert into EMP values('7902', 'FORD', 'ANALYST', '7566', '03-12-1981', 3000.0, NULL ,'20'); insert into EMP values('7934', 'MILLER','CLERK', '7782', '23-07-1982', 1300.0, NULL ,'10');
CONSULTAS
1. Obter os datos completos dos empregados.
2. E agora os dos departamentos.
3. Achar os datos dos administrativos (Clerk).
4. Idem pero ordenados polo nome.
5. O mesmo resultado que o anterior pero modificando a sentencia anterior.
6. Obtén o número, nome e salario dos empregados.
7. Achar o nome dos departamentos
8. Idem pero ordenando por nome.
9. O mesmo pero ordenando por la ciudad.
10. Achar os nomes dos departamentos ordenados pola súa cidade, pero en orden inverso.
11. Obter o nome e emprego dos empregados, ordenado por salario.
12. Idem pero ordenando agora por emprego e salario.
13. Idem pero ordenando inversamente por emprego e normalmente por salario.
14. Obtén os salarios e as comisións dos empregados do departamento 30.
15. Idem pero ordenado por comisión.
16. Obtén as comisións. Logo as distintas comisións.
17. Achar as distintas comisións e os nomes dos empregados.
18. Achar os distintos soldos e empregados.
19. Obter as comisións e os números de departamento posibles da empresa, de maneira que non se repitan.
20. Obtén os novos salarios que resultarían de sumar a os empregados do departamento 30 una gratificación de 1000$.
21. Idem pero obtendo tamén o soldo anterior.
22. Achar os empregados que teñen una comisión superior a la metade do seu soldo.
23. Achar os empregados que teñen una comisión menor ou igual que o 25% do soldo.
24. Fai que en cada fila figure ‘Nome’ e ‘Posto’ antepoñéndose ó seu respectivo valor.
25. Achar o salario e a comisión dos empregados cuxo número de empregado supera a 7500.
26. Se dividimos os empregados en dous grupos, A e B, empezando os do grupo B na letra J, obtén os nomes e empregos dos do grupo B, por orde alfabética.
27. Obter o salario, a comisión e o salario total dos empregados con comisión, ordenado por número de empregado.
28. Idem. pero para os que non teñen comisión.
29. Achar o nome dos empregados que, tendo un salario superior a 1000, teñen como director ó empregado de código 7698.
30. Achar o conxunto complementario do resultado do exercicio anterior.
31. Achar a porcentaxe que supón a comisión sobre o soldo ordenado por nome.
32. Achar os empregados do departamento 10 cuxo nome non conten a cadea LA.
33. Obtén os empregados que no dependan de ningún outro.
34. Obten os nomes dos departamentos que non sexan SALES nin RESEARCH, ordenados por localidade.
35. Desexamos coñecer o nome e departamento dos administrativos que non traballan no departamento 10 e cuxo salario é superior a 800.
36. Para os empregados que teñen comisión obtén os seus nomes por orde alfabética e o cociente entre o salario e a comisión.
37. Obtén información dos empregados cuxo nome conten exactamente cinco caracteres.
38. Idem. pero cuxos nomes conteñan ó menos cinco caracteres.
39. Acha os datos dos empregados que, o seu nome comeza por A e o seu salario é maior que 1000 ou reciben unha comisión e traballan no departamento 30.
40. Acha o nome e salario total de todos os empregados ordenado por este último e polo seu propio salario.
41. Obtén os nomes, salarios e comisións dos empregados que perciben un salario situado entre a metade da comisión e a propia comisión.
42. Acha o complementario do anterior.
43. Intenta lograr os nomes e empregos dos empregados tales que o seu emprego acaba en Man e o seu nome comeza por A.
44. Se che resultou moi fácil o anterior proba a intentalo con un predicado simple. Supón que o nome de un emprego ten ó menos cinco caracteres.
45. Acha os nomes dos empregados que teñen como máximo cinco caracteres no seu nome.
46. Supoñendo que o ano próximo a subida do total percibido por empregado é do 6% e o seguinte do 7%, achar os nomes e salarios totais actuais e futuros de todos os empregados, indicando para cada un se ten ou non comisión.
47. Acha o nome e a data de ingreso dos empregados que no son vendedores.
48. Obtén a información dispoñible dos empregados con número un dos seguintes: 7844, 7900, 7521, 7782, 7934, 7678 e 7369, pero non con un de entre: 7902, 7839, 7499 nin 7878. O predicado no debe ser moi extenso.
49. Acha os salarios totais de todos os empregados. Lembra que se coñece que hai 14 empregados.
50. Ordena os empregados polo seu departamento e logo de xeito descendente polo seu número.
A mesma consulta anterior pero ordenado polo nome de departamento.
51. Para os empregados que teñen como director a algún outro cun número maior que o seu, obter os que reciben de soldo máis de 1000 e menos de 2000, ou estean no departamento 30.
52. Obtén o salario máis alto da empresa, o total destinado a comisións e o número de empregados.
53. Acha os datos dos empregados cuxo salario é maior que o do empregado de código 7934, ordenando polo propio salario.
54. Obtén información na que se reflictan os nomes, empregos e salarios tanto dos que superan o salario de Allen como do propio Allen.
55. Acha o nome do último empregado por orde alfabética.
56. Acha o salario máis alto, o máis baixo e a diferencia entre eles.
57. Sen coñecer os resultados do exercicio anterior, ¿quen son os que reciben o salario máis alto e o máis baixo, e a canto ascenden?.
58. Acha a media dos salarios dos departamentos cuxo salario mínimo supera a 900, considerando os salarios inferiores a 5000. Ademais necesítase o código e o nome dos departamentos.
59. ¿Qué empregados traballan nas cidades que teñen máis de cinco letras?. Ordena o resultado inversamente por cidades e normalmente por nomes de empregados.
60. Acha os empregados cuxo salario supero ou coincide coa media do salario da empresa.
61. Obtén os empregados cuxo salario supera ó dos seus compañeiros de departamento.
62. ¿Cantos empregos diferentes encontramos no departamento 30?.
63. ¿Cantos empregados teñen comisión?.
64. ¿Cantos empregados ten o departamento 20?.
65. Acha os departamentos que teñen máis de tres empregados e o número de empregados dos mesmos.
66. Obtén os empregados do departamento 10 que teñen o mesmo emprego que alguén do departamento de “SALES”.
67. Acha os empregados que teñen polo menos un empregado ó seu mando, ordenados inversamente.
68. Obtén información sobre os empregados que teñen o mesmo traballo que os empregados que traballan en Chicago.
69. ¿Que empregos distintos se atopan na empresa e cantos empregados ten cada un?
70. Acha a suma dos salarios de cada departamento.
71. Obtén tódolos departamentos sen empregados.
72. Acha os empregados que non teñen a outro empregado ás súas ordenes.
73. ¿Cantos empregos hai en cada departamento e cal é a media anual do salario de cada un?
74. Acha os empregados do departamento 30 por orde descendente de comisión.
75. Obtén os empregados que traballan en Dállas ou en New York
76. Obtén un listado no que se reflictan os posibles directores de calquera empregado. Faino de maneira que obteñas resultados que atinxan a tódolos empregados.
SOLUCIÓNS
use consultas;
go
CONSULTA 01 ------
select 'consulta 01' as Consulta,* from EMP;
CONSULTA 02 ------
select 'consulta 02' as Consulta,* from DEPT;
CONSULTA 03 ------
select 'consulta 03' as Consulta,* from EMP WHERE JOB='CLERK';
CONSULTA 04 ------
select 'consulta 04' as Consulta,* from EMP WHERE JOB='CLERK' ORDER BY ENAME;
CONSULTA 05 ------
select 'consulta 05' as Consulta,* from EMP WHERE JOB='CLERK' ORDER BY 3; -- No Select, ENAME é a 3ª Columna, a 1ª é 'Consulta' e a 2ª é EMPNO
CONSULTA 06 ------
select 'consulta 06' as Consulta, EMPNO, ENAME, SAL from EMP;
CONSULTA 07 ------
select 'consulta 07' as Consulta, DNAME from DEPT; --Esta é unha consulta moi sinxela que non precisa comentario
CONSULTA 08 ------
select 'consulta 08' as Consulta, DNAME from DEPT order by DNAME; -- Esta consulta é igual a anterior só que se ordena por nome, en orde ascendente por defecto.
CONSULTA 09 ------
select 'consulta 09' as Consulta, DNAME from DEPT order by LOC;
CONSULTA 10 ------
select 'consulta 10' as Consulta, DNAME from DEPT order by LOC desc;
CONSULTA 11 ------
select 'consulta 11' as Consulta, ENAME,JOB FROM EMP ORDER BY SAL; -- Ídem que a consulta número 8.
CONSULTA 12 ------
select 'consulta 12' as Consulta, ENAME,JOB FROM EMP ORDER BY JOB, SAL;
CONSULTA 13 ------
select 'consulta 13' as Consulta, ENAME, JOB FROM EMP ORDER BY JOB desc, SAL asc;
CONSULTA 14 ------
select 'consulta 14' as Consulta, SAL, COMM FROM EMP WHERE DEPTNO='30'; -- Esta é unha consulta selectiva na que só interesa o dept 30. Como o tipo de campo de DEPTNO é -- alfanumérico este debe ir entre comiñas simples (En access en dobres)
CONSULTA 15 ------
select 'consulta 15' as Consulta, SAL, COMM FROM EMP WHERE DEPTNO='30' ORDER BY COMM;
CONSULTA 16 ------
select 'consulta 16A' as Consulta, COMM FROM EMP; -- Esta mostra as comisións teñan ou non valor, esto é, sexan ou non nulas e tamén aínda que sexan repetidas.
select distinct 'consulta 16B' as Consulta, COMM FROM EMP; -- Obsérvese que os valores repetidos só aparecen unha vez. Neste caso NULL. -- Neste caso móstranse as comisión nulas ou non pero sen repetir valores que teñan varias ocorrencias.
CONSULTA 17 ------
select distinct 'consulta 17' as Consulta, ENAME, COMM FROM EMP; -- Obsérvese que os valores non existen valores repetidos, ó mesmo tempo, de ENAME e COMM.
CONSULTA 18 ------
select 'consulta 18' as Consulta, ENAME, SAL+COMM AS SOLDO FROM EMP WHERE COMM IS NOT NULL
UNION
SELECT 'consulta 18' as Consulta, ENAME, SAL AS SOLDO FROM EMP WHERE COMM IS NULL; -- O soldo é a suma de SAL e COMM. -- Pero, primeiro hai que sumar SAL e COMM onde este último teña valores. -- Finalmente engadimos aquelas tuplas que non teñen COMM (COMM = NULL) onde o SOLDO é igual a SAL.
CONSULTA 19 ------
select distinct 'consulta 19' as Consulta, COMM, D.DEPTNO FROM (dept as D left join emp as E on (D.deptno = E.deptno))
-- Nótese que o departamento 30 aparece varias veces, pero que non se repite ningunha combinación dese dept. -- E que o departamento 40 non ten ocorrencias en EMP, por eso se fai un Left Join para obter os departamentos -- que aparecen en dept e non en emp.
CONSULTA 20 ------
select 'consulta 20' as Consulta, SAL+1000 as SALARIO_ACTUAL FROM EMP WHERE DEPTNO='30';
CONSULTA 21 ------
select 'consulta 21' as Consulta, SAL+1000 as SALARIO_ACTUAL, SAL AS SOLDO_ANTERIOR FROM EMP WHERE COMM IS NULL AND
DEPTNO='30'
UNION
select 'consulta 21' as Consulta, SAL+1000 as SALARIO_ACTUAL, SAL+COMM AS SOLDO_ANTERIOR FROM EMP WHERE COMM IS NOT NULL AND
DEPTNO='30'
-- Esta consulta faise dun xeito semellante á 18, distinguindo entre quen ten comisión a NULL e quen non a ten a NULL.
CONSULTA 22 ------
select 'consulta 22' as Consulta, ENAME FROM EMP WHERE COMM > (SAL + COMM) / 2 and
COMM IS NOT NULL;
-- Seguimos tendo en conta que a comisión non pode ser NULA para realizar operacións.
CONSULTA 23 ------
select 'consulta 23' as Consulta, ENAME FROM EMP WHERE COMM <= (0.25 * (SAL + COMM)) and
COMM is NOT NULL;
CONSULTA 24 ------
select 'consulta 24' as Consulta, 'NOME', ENAME, 'POSTO', JOB FROM EMP; -- Simplemente indicar que cando se desexa realizar unha columna con valores constantes estes deben ir entre comiñas.
CONSULTA 25 ------
select 'consulta 25' as Consulta, SAL, COMM FROM EMP WHERE EMPNO > '7500'; -- Indicar que aínda que o número de empregado é un número este campo está definido como alfanumérico, -- por iso, o valor debe ir entre comiñas.
CONSULTA 26 ------
select 'consulta 26' as Consulta, ENAME, JOB FROM EMP WHERE ENAME >= 'J' ORDER BY ENAME;
CONSULTA 27 ------
select 'consulta 27' as Consulta, ENAME, SAL, COMM, (SAL+COMM) AS SALARIO_TOTAL FROM EMP WHERE COMM IS NOT NULL ORDER BY EMPNO;
CONSULTA 28 ------
select 'consulta 28' as Consulta, ENAME, SAL, COMM, SAL AS SALARIO_TOTAL FROM EMP WHERE COMM IS NULL ORDER BY EMPNO; -- Para os empregados que non teñen comisión o salario total é igual o seu salario.
CONSULTA 29 ------
select 'consulta 29' as Consulta, ENAME FROM EMP WHERE SAL > 1000 AND
MGR = '7698';
-- Unha simple consulta cun operador AND onde se pode ver que como sal está definida como númerico -- o valor non vai entre comiñas.
CONSULTA 30 ------
select 'consulta 30' as Consulta, ENAME FROM EMP WHERE SAL <= 1000 or
MGR <> '7698';
-- O complementario do anterior é que SAL non sexa maior que 1000 nen o MGR sexa o 7698. -- Polo cal con que o SAL sexa menor ou igual que 1000 OU o MGR distinto de 7698 xa está obtido -- o complemento da consulta 29.
-- Por álxebra de BOOL, tense: -- not (A and B) = not A or not B. -- not (A or B) = not A and not B.
CONSULTA 31 ------
select 'consulta 31' as Consulta, ENAME, COMM, SAL+COMM AS SOLDO, (COMM / (SAL+COMM) * 100 ) as PORCENTAXE,'%' AS [%] FROM EMP WHERE COMM IS NOT NULL ORDER BY ENAME;
CONSULTA 32 ------
select 'consulta 32' as Consulta, ENAME FROM EMP WHERE DEPTNO='10' AND
ENAME NOT LIKE '%LA%';
CONSULTA 33 ------
select 'consulta 33' as Consulta, ENAME FROM EMP WHERE MGR IS NULL; -- Os empregados que non teñen xefes, esto é eles son os superiores, son os que teñen a columna MGR a NULL -- Esta táboa ten integrada unha recursividade que fai que o valor dunha columna, MGR, estea relacionado -- co valor de outra columna, EMPNO, da mesma táboa.
CONSULTA 34 ------
select 'consulta 34' as Consulta, DNAME FROM DEPT WHERE DNAME not in ('SALES', 'RESEARCH') ORDER BY LOC;
-- A cláusula where tamén podería ser: -- where DNAME <> 'SALES' and DNAME <> 'RESEARCH'
CONSULTA 35 ------
select 'consulta 35A' as Consulta, E.ENAME, D.DNAME FROM DEPT AS D,EMP AS E WHERE E.DEPTNO = D.DEPTNO AND
JOB = 'CLERK' AND
E.DEPTNO <> '10' AND
SAL > 800;
-- A Anterior consulta tamén podería ser da seguinte forma, usando JOIN NATURAL select 'consulta 35B' as Consulta, E.ENAME, D.DNAME FROM DEPT AS D JOIN EMP AS E ON (E.DEPTNO = D.DEPTNO) WHERE JOB = 'CLERK' AND
E.DEPTNO <> '10' AND
SAL > 800;
-- Poderíase por INNER JOIN, se non se pon nada enténdese INNER ou NATURAL JOIN
CONSULTA 36 ------
select 'consulta 36' as Consulta, ENAME, (SAL / COMM) as COCIENTE FROM EMP WHERE COMM > 0 Order by ENAME -- ollo que nunha división o divisor nunca pode ser 0; -- Non fai falla por COMM is NOT NULL, pois xa se lle está esixindo que sexa maior que 0
CONSULTA 37 ------
select 'consulta 37' as Consulta, * FROM EMP WHERE ENAME LIKE '_____';
CONSULTA 38 ------
select 'consulta 38' as Consulta, * FROM EMP WHERE ENAME LIKE '_____%';
select 'consulta 38 bis' as Consulta, * FROM EMP WHERE ENAME LIKE '%_____'; -- O comodín guión '_' serve para que substituír a calquera caracter pero só a un por cada guión. -- O comodín tanto por cen '%' serve para que substituír a calquera caracter pero a un número indeterminado deles, por cada %. -- Como nos pide que como mínimo 5 caracteres, pois póñense 5 guións e logo ou antes o signo %.
CONSULTA 39 ------
select 'consulta 39' as Consulta, * FROM EMP WHERE (ENAME LIKE 'A%' AND SAL > 1000) OR
(COMM >= 0 AND DEPTNO ='30') ;
CONSULTA 40 ------
select 'consulta 40' as Consulta, ENAME, SAL+COMM as SOLDO, SAL FROM EMP WHERE COMM IS NOT NULL
UNION
select 'consulta 40' as Consulta, ENAME, SAL as SOLDO, SAL FROM EMP WHERE COMM IS NULL
ORDER BY SOLDO, SAL;
-- As mesmas explicación que en consultas anteriores onde a Comisión non pode ser nula, pero -- Nunha UNION hai que engadir os elementos polos que se ordena na cláusula SELECT de cada subconsulta. -- Ademais o ORDER BY só se pon ó final de tódalas subconsultas, afecta a toda a UNION.
CONSULTA 41 ------
select 'consulta 41' as Consulta, ENAME, SAL, COMM FROM EMP WHERE SAL >= COMM / 2 AND
SAL <= COMM;
CONSULTA 41 Bis ------
select 'consulta 41 Bis' as Consulta, ENAME, SAL, COMM FROM EMP WHERE SAL between COMM / 2 and COMM ; -- Esta consulta ten dúas formas de realizarse unha con todo AND e outra usando o operador Between.
CONSULTA 42 ------
select 'consulta 42' as Consulta, ENAME, SAL, COMM FROM EMP WHERE COMM is NULL OR SAL < COMM / 2 OR
SAL > COMM;
CONSULTA 43 ------
select 'consulta 43' as Consulta, ENAME, JOB FROM EMP WHERE ENAME LIKE 'A%' AND JOB LIKE '%MAN'; -- Esta consulta substitúe o % por calquera número de caracteres, pero nun caso o patrón debe comezar por A, e no segundo o patrón a seguir debe rematar en MAN
CONSULTA 44 ------
select 'consulta 44' as Consulta, ENAME, JOB FROM EMP WHERE ENAME LIKE 'A%' AND JOB LIKE '%__MAN'; -- Podemos apoiarnos nas explicacións dadas na consulta 38.
CONSULTA 45 ------
select 'consulta 45' as Consulta, ENAME FROM EMP WHERE ENAME NOT LIKE '______%' -- Tamén se pode poñer Where LEN (ENAME)<=5
CONSULTA 46 ------
select 'consulta 46' as Consulta, ENAME, SAL, ((sal + comm) * 1.06) as Pasado,((sal + comm)*1.1342) as Seguinte,'SI' as Comision from EMP where COMM is not null
UNION
select 'consulta 46' as Consulta, ENAME, SAL, (sal * 1.06) as Pasado, (sal * 1.1342) as Seguinte,'NON' as Comision from EMP where COMM is null;
-- Podemos apoiarnos en tódalas consultas que se viron con anterioridade sobre COMM e NULL -- Ollo neste caso o 6% e mellor poñelo nos calculado que o calculo o xestor, -- pois así adiántase traballo. SAL + SAL *=0,06 = sal * 1,06
CONSULTA 47 ------
select 'consulta 47' as Consulta, ENAME, HIREDATE FROM EMP WHERE JOB <> 'SALESMAN';
CONSULTA 48 ------
select 'consulta 48' as Consulta, * FROM EMP WHERE EMPNO IN ('7844', '7900','7521','7782','7934','7678','7369');
CONSULTA 49 ------
select 'consulta 49' as Consulta, ENAME, SAL + COMM FROM EMP WHERE COMM IS NOT NULL
UNION
SELECT 'consulta 49' as Consulta, ENAME, SAL FROM EMP WHERE COMM IS NULL; -- O mesmo xa comentado en varias consultas anteriores sobre que non se poden realizar operacións -- con campos que poden tomar valores nulos. Hai que dividilos en varias subconsultas.
CONSULTA 50 ------
select 'consulta 50' as Consulta,* FROM EMP ORDER BY DEPTNO, EMPNO DESC;
CONSULTA 50B ------
select 'consulta 50' as Consulta,emp.* FROM EMP JOIN DEPT on (Emp.Deptno=Dept.Deptno) ORDER BY DNAME, EMPNO DESC;
-- Neste caso ordenada polo nome de departamento
CONSULTA 51 ------
select 'consulta 51' as Consulta, ENAME, MGR FROM EMP WHERE COMM IS NOT NULL AND
MGR > EMPNO AND
(SAL+COMM between 1000 AND 2000 OR DEPTNO='30');
UNION
select 'consulta 51' as Consulta, ENAME, MGR FROM EMP WHERE COMM IS NULL AND
MGR > EMPNO AND
(SAL between 1000 AND 2000 OR DEPTNO='30');
-- Ollo que a comisión non pode ser nula e
-- O director ten que ter un número superior e logo poden suceder dúas cousas
-- ou o solo está entre unhas cantidades ou o empregado pertence o dept 30.
CONSULTA 52 ------
select 'consulta 52' as Consulta, MAX(SAL) AS SALARIO_MAXIMO, SUM(COMM) AS TOTAL_COMISIONS, COUNT(EMPNO) AS NUMERO_EMPREGADOS FROM EMP;
CONSULTA 53 ------
select 'consulta 53' as Consulta, * FROM EMP WHERE SAL > (SELECT SAL
FROM EMP
WHERE EMPNO='7934')
ORDER BY SAL;
CONSULTA 54 ------
select 'consulta 54' as Consulta, ENAME, JOB, SAL FROM EMP WHERE SAL >= (SELECT SAL
FROM EMP
WHERE ENAME='ALLEN');
CONSULTA 55 ------
select 'consulta 54' as Consulta, MAX(ENAME) FROM EMP;
CONSULTA 55 bis ------
select TOP 1 ENAME, 'consulta 54 BIS' as Consulta FROM EMP ORDER BY ENAME DESC; -- É máis rápida a 54 que a 54 bis, pois leva menos carga de operacións.
CONSULTA 56 ------
select 'consulta 56' as Consulta, MAX(SAL) AS SALARIO_MAXIMO, MIN(SAL) AS SALARIO_MINIMO, MAX(SAL)-MIN(SAL) AS DIFERENCIA FROM EMP;
CONSULTA 57 ------
select 'consulta 57' as Consulta, ENAME, SAL FROM EMP WHERE SAL = (SELECT MIN(SAL)
FROM EMP)
OR
SAL = (SELECT MAX(SAL)
FROM EMP);
-- esta consulta vai mirando tupla a tupla se coincide cos valores das subconsultas.
CONSULTA 58A ------
select 'consulta 58A' as Consulta, E.DEPTNO, DNAME, Avg(SAL) FROM EMP AS E, DEPT AS D WHERE E.DEPTNO=D.DEPTNO AND
SAL < 5000 AND
900 < (SELECT MIN(SAL)
FROM EMP AS EM
WHERE E.DEPTNO=EM.DEPTNO)
GROUP BY E.DEPTNO, DNAME;
-- Selecciona o deptno da 1ª tupla, mira se se cumpren as condicións. -- d.deptno ten que ser igual a e.deptno.
CONSULTA 58B ------
select 'consulta 58B' as Consulta, D.DEPTNO, DNAME, AVG(SAL) FROM EMP AS E INNER JOIN DEPT AS D ON (E.DEPTNO=D.DEPTNO) WHERE E.SAL < 5000 AND
900 < (SELECT MIN (SAL)
FROM EMP AS EM
WHERE E.DEPTNO=EM.DEPTNO)
GROUP BY D.DEPTNO, DNAME; -- Igual á anterior pero facendo Join
CONSULTA 58C ------
select 'consulta 58C' as Consulta, E.DEPTNO, DNAME, AVG (SAL) FROM EMP AS E JOIN DEPT ON (E.DEPTNO=DEPT.DEPTNO) WHERE SAL < 5000 GROUP BY E.DEPTNO, DNAME HAVING MIN (SAL) > 900;
-- Cando hai un agrupamento pódese usar o Having que equivale o where pero só para analizar a condición -- en cada agrupa,mento que se fai.
CONSULTA 58D ------
select 'consulta 58D' as Consulta, E.DEPTNO, DNAME, AVG (SAL) FROM EMP AS E, DEPT WHERE E.DEPTNO=DEPT.DEPTNO AND
SAL < 5000
GROUP BY E.DEPTNO, DNAME HAVING MIN (SAL)>900; -- Igual á anterior pero sen JOIN
CONSULTA 59A ------
select 'consulta 59A' as Consulta, ENAME, LOC FROM DEPT AS D, EMP AS E WHERE D.DEPTNO=E.DEPTNO
AND LOC LIKE '______%'
ORDER BY LOC DESC, ENAME; -- Como se piden cidades de máis de 5 caracteres, hai que por 6 comodíns guión e un comodiín que substitue a -- calquera número deles.
CONSULTA 59B ------
select 'consulta 59B' as Consulta, ENAME, LOC FROM DEPT AS D JOIN EMP AS E ON (D.DEPTNO=E.DEPTNO) WHERE LOC LIKE '______%' ORDER BY LOC DESC, ENAME; -- Igual á anterior pero con JOIN
CONSULTA 60 ------
select 'consulta 60' as Consulta, ENAME, SAL FROM EMP WHERE SAL >= (SELECT AVG(SAL)
FROM EMP);
CONSULTA 61 ------
select 'consulta 61' as Consulta, ENAME, DEPTNO, SAL FROM EMP as e WHERE SAL = (SELECT MAX(SAL)
FROM EMP as em
where e.deptno=em.deptno );
CONSULTA 62 ------
select 'consulta 62' as Consulta, count(distinct JOB) as Numero_Empregos FROM EMP WHERE DEPTNO='30';
CONSULTA 63 ------
select 'consulta 63' as Consulta, COUNT(COMM) as Empregados_Con_Comisión FROM EMP WHERE COMM is not NULL;
CONSULTA 63B ------
select 'consulta 63B' as Consulta, COUNT(JOB) as Empregados_Con_Comisión FROM EMP WHERE COMM is not NULL;
CONSULTA 64 ------
select 'consulta 64' as Consulta, COUNT(ENAME) as Empregados_Dept_20 FROM EMP WHERE DEPTNO='20';
CONSULTA 65 ------
select 'consulta 65' as Consulta, DEPTNO, COUNT(ENAME) Numero_Empregados FROM EMP GROUP BY DEPTNO HAVING COUNT(ENAME)>3; -- O Having equivale a a where, pero só afecta o conxunto de cada un dos agrupamentos, non a toda a táboa en si.
CONSULTA 66A ------
select 'consulta 66A' as Consulta, ENAME FROM EMP WHERE DEPTNO= '10' AND
JOB IN (SELECT JOB
FROM EMP
WHERE DEPTNO = (SELECT DEPTNO
FROM DEPT
WHERE DNAME='SALES'));
CONSULTA 66B ------
select 'consulta 66B' as Consulta, ENAME FROM EMP AS E WHERE DEPTNO= '10' AND
JOB IN (SELECT JOB
FROM EMP AS EM, DEPT AS D
WHERE EM.DEPTNO = D.DEPTNO AND
DNAME = 'SALES'); -- Esta consulta é máis rápida que a 66A, pois hai unha subconsulta menos.
CONSULTA 67------
select 'consulta 67'as Consulta, ENAME FROM EMP WHERE EMPNO IN (SELECT distinct MGR –- se un empregado é xefe varios aparece unha soa vez.
FROM EMP WHERE MGR IS NOT NULL)
ORDER BY ENAME desc;
CONSULTA 68 ------
select 'consulta 68' as Consulta, * FROM emp WHERE JOB in (SELECT job FROM EMP AS E, DEPT as D
WHERE E.DEPTNO = D.DEPTNO AND
LOC = 'CHICAGO');
CONSULTA 69 ------
select 'consulta 69' as Consulta, JOB, COUNT(EMPNO) as Numero_Empregados FROM EMP GROUP BY JOB;
CONSULTA 70 ------
select 'consulta 70' as Consulta, D.DEPTNO, SUM(SAL) AS Suma_Salarios FROM EMP as E right join DEPT as D ON (E.DEPTNO=d.DEPTNO) GROUP BY D.DEPTNO; -- Unha ben simpre consulta de agrupamento na que se agrupa por número de departamento e logo se calcula suma dos salrios de cada agrupamento.
CONSULTA 71 ------
select 'consulta 71' as Consulta, DNAME FROM DEPT WHERE DEPTNO NOT IN (SELECT DEPTNO
FROM EMP);
CONSULTA 72 ------
select 'consulta 72' as Consulta, ENAME FROM EMP WHERE EMPNO NOT IN (SELECT MGR
FROM EMP
WHERE MGR IS NOT NULL);
CONSULTA 73 ------
select 'consulta 73' as Consulta, DEPTNO, COUNT(distinct JOB) as Total_Empregos, AVG(SAL)*12 as Media_Salarios FROM EMP GROUP BY DEPTNO;
CONSULTA 74 ------
select 'consulta 74' as Consulta, ENAME FROM EMP WHERE DEPTNO = '30' AND
COMM IS NOT NULL
ORDER BY COMM DESC;
CONSULTA 75A ------
select 'consulta 75A' as Consulta, ENAME FROM EMP as E, DEPT as D WHERE E.DEPTNO=D.DEPTNO AND
(LOC='DALLAS' OR LOC='NEW YORK');
CONSULTA 75B ------
select 'consulta 75B' as Consulta, ENAME FROM EMP as E, DEPT as D WHERE E.DEPTNO=D.DEPTNO AND
LOC IN ('DALLAS', 'NEW YORK');
CONSULTA 76 ------
select 'consulta 76' as Consulta, A.ENAME as Dirixe, B.ENAME as Empregado FROM EMP as A, EM as B WHERE A.JOB in ('PRESIDENT', 'MANAGER') AND A.EMPNO=B.MGR AND B.MGR IS NOT NULL;
