S UBQUERIES , J OINS E U NIONS C - SQL e Modelo Relacional

A P Í T U L O

4

Conforme trabalhamos com um banco de dados é inevitável que em algum momento seja ne- cessário gerar uma consulta que trabalhe com mais de uma tabela ao mesmo tempo. Neste capítulo iremos abordar as principais técnicas utilizadas em situações como esta e identificar em quais situ- ações devemos aplicá-las.

Subqueries

Uma subquery é uma query como qualquer outra, porém ela é executada dentro de uma outra query de SELECT, INSERT, UPDATE ou DELETE. A função da subquery é produzir um resultado que será utilizado pela query que a contém. Alguns autores se referem à subquery como query interna e a query que a contém como query externa.

Subqueries podem ser utilizadas em qualquer parte de uma query onde uma expressão é aceita. Além disso, subqueries podem ocorrer em outras subqueries e assim por diante, ou seja, em uma query podemos encontrar vários níveis de subqueries.

Vamos supor que no sistema de cadastro de alunos de uma escola tenhamos a tabelaNotana qual ficam registradas as notas dos alunos em cada turma.

1 C R E A T E T ABL E Nota ( 2 id INT NOT NULL, 3 a l u n o _ i d INT, 4 t u r m a _ i d INT, 5 nota D E C I M A L(4 ,2) , 6 P R I M A R Y KEY ( id ) , 7 F O R E I G N KEY ( a l u n o _ i d ) R E F E R E N C E S Alu no ( id ) , 8 F O R E I G N KEY ( t u r m a _ i d ) R E F E R E N C E S Tur ma ( id ) 9 ) 10 E N G I N E = I n n o D B ;

Código SQL 4.1: Tabela Nota

Se quisermos saber quais foram os alunos que tiraram uma nota maior que a média das notas de cada turma, poderíamos realizar a seguinte consulta:

1 S E L E C T * 2 FROM Nota AS n1 3 W HER E n1 . nota > ( 4 S E L E C T AVG( n2 . nota ) 5 FROM Nota AS n2 6 W HER E n2 . t u r m a _ i d = n1 . t u r m a _ i d 7 ) ;

Código SQL 4.2: Consultando notas acima da média da turma

mos o valorn1.turmaproveniente da query externa. Isso nos mostra que a subquery é dependente da query que a contém e, por isso, a chamamos de subquery correlacionada.

Uma subquery correlacionada, devido à sua dependência de um valor da query externa, pode custar muito processamento, pois cada registro encontrado pela query externa irá executar a subquery.

Quando uma subquery não necessita de nenhum valor da query externa nós as chamamos de

subquery independente. Diferentemente de uma subquery correlacionada, a subquery indepen-

dente pode ser executada apenas uma vez mesmo que a query externa retorne mais de um registro.

1 S E L E C T n1 .* , ( 2 S E L E C T MAX( n2 . nota ) 3 FROM Nota AS n2 4 W HER E n2 . t u r m a _ i d = 1 5 ) AS m a i o r _ n o t a 6 FROM Nota AS n1 7 W HER E n1 . t u r m a _ i d = 1;

Código SQL 4.3: Consultando notas de uma turma

No exemplo acima utilizamos uma subquery como um campo virtual da query externa para ob- termos a maior nota de uma determinada turma. Como o valorturma_idnão depende de um valor da query externa nossa subquery é independente.

Exercícios de Fixação

1 _{Utilizando a biblioteca}escola, crie uma nova tabela chamadaNota. Após isso, crie e adicione alguns registros para essa tabela.

2 _{Faça uma consulta que retorne apenas as notas que são maiores que a nota média da turma.}

Observe os resultados. 1 S E L E C T * 2 FROM Nota AS n1 3 W HER E n1 . nota > ( 4 S E L E C T AVG( n2 . nota ) 5 FROM Nota AS n2 6 W HER E n2 . t u r m a _ i d = n1 . t u r m a _ i d 7 ) ;

3 _{Faça uma consulta que retorne a maior nota da turma cujo}_id_{da turma seje igual a 1. Mostre}

essa nota em uma nova coluna chamadamaior_nota.

1 S E L E C T n1 .* , ( 2 S E L E C T MAX( n2 . nota ) 3 FROM Nota AS n2 4 W HER E n2 . t u r m a _ i d = 1 5 ) AS m a i o r _ n o t a 6 FROM Nota AS n1 7 W HER E n1 . t u r m a _ i d = 1;

Código SQL 4.6: Resposta do exercício

Exercícios Complementares

1 _{Crie ou altere a tabela}_Livro_{. A tabela deve conter uma coluna}_preco_{que irá armazenar o preço}

de cada livro. Crie uma consulta que devolva todas as colunas de todos os livros registrados. Além das colunas normais da tabela, crie uma coluna virtual que irá conter a média dos preços dos livros.

2 _{Ainda utilizando a tabela}Livro, crie uma consulta que devolva todas as colunas de todos os livros registrados cujos preços sejam superiores em relação aos livros mais baratos.

3 _{Na tabela}_Livro_{crie a coluna}_{autor_id}_{caso ela ainda não exista. Também crie ou altere a tabela} Autorcom, pelo menos, as colunasidenome. Crie uma consulta que devolva todos os livros escritos por autores cujos nomes começam com a letra A.

Joins

Utilizamos joins do SQL para extrairmos as informações de uma ou mais tabelas em um único conjunto de resultados baseando-se nos relacionamentos entre as colunas das tabelas envolvidas.

Até agora criamos as nossas tabelas definindo uma coluna como chave primária em cada uma delas. Não fizemos isso à toa, pois agora veremos como utilizar esse relacionamento entre colunas de tabelas diferentes em uma única consulta.

Vamos voltar ao exemplo da rede social. Quando modelamos as tabelas separamos as informa- ções do usuário em duas tabelas: Usuariocom as informações pertinentes à conta do usuário na rede social ePerfilcom as informações pessoais do mesmo.

1 C R E A T E T ABL E U s u a r i o ( 2 id INT NOT NULL,

3 n o m e _ u s u a r i o V A R C H A R(10) , 4 s enh a V A R C H A R(10) , 5 e mai l V A R C H A R( 100 ) , 6 P R I M A R Y KEY ( id ) 7 ) 8 E N G I N E = I n n o D B ; 9 10 C R E A T E T ABL E P e r f i l ( 11 id INT NOT NULL,

12 nome V A R C H A R( 255 ) , 13 sexo T I N Y I N T (1) , 14 p r o f i s s a o V A R C H A R( 255 ) , 15 o n d e _ e s t u d o u V A R C H A R( 25 5) , 16 h o b b i e s V A R C H A R( 255 ) , 17 g o s t o _ m u s i c a l V A R C H A R( 25 5) , 18 P R I M A R Y KEY ( id ) , 19 F O R E I G N KEY ( id ) R E F E R E N C E S U s u a r i o ( id ) 20 ) 21 E N G I N E = I n n o D B ;

Código SQL 4.10: TabelaUsuarioePerfil

Para trazer as informações das duas tabelas em um único conjunto de resultados utilizaremos a instruçãoJOIN.

1 S E L E C T *

2 FROM U s u a r i o AS u 3 JOIN P e r f i l AS p

Código SQL 4.11: Cruzando os dados dos usuários e seus perfis

Repare que o resultado obtido não foi o desejado, pois para cada registro da tabelaUsuariofoi feita uma relação com todos os registros da tabelaPerfil. Isso ocorreu porque não informamos qual a coluna queremos utilizar para definir o relacionamento entre as tabelas.

Para definirmos qual a coluna irá definir o relacionamento entre as tabelas devemos utilizar a instruçãoJOINjuntamente com a instruçãoON.

1 S E L E C T *

2 FROM U s u a r i o AS u 3 JOIN P e r f i l AS p 4 ON u . id = p . id ;

Código SQL 4.12: Consultando usuários e seus respectivos perfis

No exemplo acima utilizamos a instruçãoJOIN, porém o MySQL oferece outros tipos de joins. Abaixo segue uma lista com cada tipo:

• JOIN: retorna registros quando existe algum valor na coluna de relacionamento em pelo menos uma das tabelas.

• LEFT JOIN: retorna registros quando existe algum valor na coluna de relacionamento da tabela informada à esquerda na consulta.

• RIGHT JOIN: retorna registros quando existe algum valor na coluna de relacionamento da tabela informada à direita na consulta.

Exercícios de Fixação

4 _Utilizando_joins_{, crie consultas para os alunos e suas respectivas notas. Faça uma consulta sem}

o uso da instruçãoONe depois com a instrução como mostrado abaixo.

1 S E L E C T * 2 FROM A lun o AS a

3 JOIN Nota AS n ; 4 5 S E L E C T * 6 FROM A lun o AS a 7 JOIN Nota AS n 8 ON a . id = n . A l u n o _ i d ;

Código SQL 4.13: Resposta do exercício

Exercícios Complementares

4 _{Crie ou altere a tabela}Livroe faça com que ela contenha a colunaautor_idcaso esta ainda não exista. Também crie ou altere a tabelaAutorcom, pelo menos, as colunasidenome. Crie uma consulta que devolva todos os livros escritos por autores cujos nomes começam com a letra A.

5 _{Crie uma consulta que gere como resultado uma lista com todos os autores que possuam livros}

publicados. Além disso, o resultado deve conter o número de livros que cada autor publicou.

6 _{Refaça o exercício anterior ordenando decrescentemente o resultado pelo nome do autor.}

Unions

Nos exemplos anteriores conseguimos obter resultados de duas ou mais tabelas concatenando suas colunas ou criando campos virtuais.

Porém nem sempre é isso que desejamos. Às vezes queremos que duas ou mais tabelas sejam unidas aumentando o número de registros. Para atingirmos este objetivo devemos utilizar a instru- çãoUNION.

Vamos supor que, em nossa rede social, armazenamos os usuários administrativos do site em uma tabela diferente dos usuários normais. A tabela poderia ser assim:

1 C R E A T E T ABL E U s u a r i o A d m i n i s t r a t i v o ( 2 id INT NOT NULL,

3 n o m e _ u s u a r i o V A R C H A R(10) , 4 s enh a V A R C H A R(10) , 5 e mai l V A R C H A R( 100 ) , 6 g rup o INT, 7 P R I M A R Y KEY ( id ) 8 ) 9 E N G I N E = I n n o D B ;

Código SQL 4.17: Tabela UsuarioAdministrativo

Para obter uma lista com o nome e e-mail de todos os usuários, inclusive os usuários administrativos utilizaremos a instruçãoUNION.

1 S E L E C T n o m e _ u s u a r i o , e ma il 2 FROM U s u a r i o

4 S E L E C T n o m e _ u s u a r i o , e ma il 5 FROM U s u a r i o A d m i n i s t r a t i v o ;

Código SQL 4.18: Obtendo a lista de todos os usuários da rede social

Repare que no primeiro e segundoSELECTescolhemos quais colunas queríamos no resultado. A instruçãoUNIONnos obriga que cadaSELECTretorne o mesmo número de colunas. Como a tabela

UsuarioAdministradorpossui uma coluna a mais, se tivéssemos utilizado o caractere wildcard *

em ambas colunas, nossa consulta teria retornado um erro.

Por padrão a instruçãoUNIONseleciona registros distintos. Portanto, caso um usuário administra- tivo também seja um usuário normal da rede social, com nome e e-mail cadastrados com os mesmos valores nas duas tabelas, a consulta do exemplo acima nos teria retornado apenas um resultado para esse usuário.

Se quisermos que o resultado traga entradas duplicadas, devemos utilizar a instruçãoUNION ALL.

1 S E L E C T n o m e _ u s u a r i o , e ma il 2 FROM U s u a r i o

3 U NIO N ALL

4 S E L E C T n o m e _ u s u a r i o , e ma il 5 FROM U s u a r i o A d m i n i s t r a t i v o ;

Código SQL 4.19: Obtendo a lista de todos os usuários da rede social

Exercícios de Fixação

5 _{Utilize a instrução}UNION

Reproduza os exemplos anteriores utilizando a instruçãoUNIONe depoisUNION ALL. Insira alguns registros nas tabelas e observe os resultados.

1 C R E A T E T ABL E U s u a r i o A d m i n i s t r a t i v o ( 2 id INT NOT NULL,

3 n o m e _ u s u a r i o V A R C H A R(10) , 4 s enh a V A R C H A R(10) , 5 e mai l V A R C H A R( 100 ) , 6 g rup o INT, 7 P R I M A R Y KEY ( id ) 8 ) 9 E N G I N E = I n n o D B ; 10 11 S E L E C T n o m e _ u s u a r i o , e mai l 12 FROM U s u a r i o 13 U NIO N 14 S E L E C T n o m e _ u s u a r i o , e mai l 15 FROM U s u a r i o A d m i n i s t r a t i v o ; 16 17 S E L E C T n o m e _ u s u a r i o , e mai l 18 FROM U s u a r i o 19 U NIO N ALL 20 S E L E C T n o m e _ u s u a r i o , e mai l 21 FROM U s u a r i o A d m i n i s t r a t i v o ;

Exercícios Complementares

7 _{Utilizando as tabelas}UsuarioeUsuarioAdministrativodo exercício de fixação, crie uma consulta que gere uma lista com todos os usuarios (administrativos e normais). Além disso, quando um usuário não possuir um valor na colunanome_usuario, imprima no seu lugar o e-mail deste usuário.

RESPOSTAS

A

P Ê N D I C E

A

Terminal 1.31: Removendo a database k03_livraria.

Exercício Complementar 1.3

1 USE k 0 3 _ l i v r a r i a ; 2 C R E A T E T ABL E E d i t o r a (

3 id = B I G I N T NOT NULL A U T O _ I N C R E M E N T, 4 nome V A R C H A R ( 255 ) NOT NULL,

5 e mai l V A R C H A R ( 255 ) NOT NULL, 6 a n o _ f u d a c a o YEAR NOT NULL, 7 n a c i o n a l T I N Y I N T (1) NOT NULL

8 )

9 E N G I N E = I n n o D B ;

Código SQL 1.5: Criando a tabelaEditora

mysql > s o u r c e create - table - e d i t o r a . sql D a t a b a s e c h a n g e d

Qu ery OK , 0 rows a f f e c t e d ( 0.0 8 sec )

Terminal 1.32: Executando a tabelaEditora

Exercício Complementar 1.4

1 I N S E R T INTO E d i t o r a ( nome , email , a n o _ f u n d a c a o , n a c i o n a l ) V A L U E S(’ O r e i l l y ’, ’←-

o r e i l l y @ e m a i l . com ’, 1982 , 1) ;

3 I N S E R T INTO E d i t o r a ( nome , email , a n o _ f u n d a c a o , n a c i o n a l ) V A L U E S(’ Wrox ’, ’ w r o x @ e m a i l .←-

5 I N S E R T INTO E d i t o r a ( nome , email , a n o _ f u n d a c a o , n a c i o n a l ) V A L U E S(’ A p r e s s ’, ’←-

a p r e s s @ e m a i l . com ’, 1968 , 0) ;

Código SQL 1.6: adicionando registros para a tabelaEditora

mysql > s o u r c e a d i c i o n a n d o - registros - e d i t o r a . sql Qu ery OK , 1 row a f f e c t e d ( 0.0 3 sec )

Qu ery OK , 1 row a f f e c t e d ( 0.0 4 sec ) Qu ery OK , 1 row a f f e c t e d ( 0.0 4 sec )

Terminal 1.33: executando a adição de registros na tabelaEditora

Terminal 1.34: Selecionando asEditoras

Exercício Complementar 1.6

mysql > U P D A T E E d i t o r a SET nome = ’ OReilly ’ W HER E id =1; Qu ery OK , 1 row a f f e c t e d ( 0.0 9 sec )

Rows m a t c h e d : 1 C h a n g e d : 1 W a r n i n g s : 0

Terminal 1.35: Alterando asEditoras

Exercício Complementar 1.7

mysql > D E L E T E FROM E d i t o r a WH ER E id =2; Qu ery OK , 1 row a f f e c t e d ( 0.0 7 sec )

Terminal 1.36: Deletando umaEditora

Exercício Complementar 2.1

2 S E L E C T * FROM E d i t o r a W HER E id != 1; 3 S E L E C T * FROM E d i t o r a W HER E nome IS NULL; 4 S E L E C T * FROM E d i t o r a W HER E nome IS NOT NULL; 5 S E L E C T * FROM E d i t o r a W HER E id B E T W E E N 1 AND 3; 6 S E L E C T * FROM E d i t o r a W HER E nome LIKE ’ O r e i l y % ’; 7 S E L E C T * FROM E d i t o r a W HER E nome NOT LIKE ’ O r e i l y%’; 8 S E L E C T * FROM E d i t o r a W HER E id < 3 AND nome like ’ Wrox%’;

Código SQL 2.29: Consultando a tabelaEditora

Exercício Complementar 2.2

1 S E L E C T * FROM E d i t o r a O RDE R BY id DESC;

Código SQL 2.35: Resposta do complementar

Exercício Complementar 2.3

1 S E L E C T * FROM E d i t o r a W HER E id >= 2 ORD ER BY ema il DESC;

Código SQL 2.36: Resposta do complementar

Exercício Complementar 2.4

1 S E L E C T AVG( id ) FROM E d i t o r a WHE RE n a c i o n a l = 1;

Código SQL 2.46: Calculando a média dos autores com bibliografia.

Exercício Complementar 2.5

1 S E L E C T C OUN T ( nome ) FROM E d i t o r a WHE RE d a t a _ f u n d a c a o <= 1 9 8 0 / 0 1 / 0 1 ;

Código SQL 2.47: Mostrando autores nascidos antes de1980.

Exercício Complementar 2.6

1 S E L E C T C OUN T (*) AS n a c i o n a i s _ t o t a l

2 FROM E d i t o r a W HER E n a c i o n a l = 1 AND nome LIKE ’O % ’ XOR nome LIKE ’% O ’

Código SQL 2.51: total de editoras nacionais ou começando ou terminando com a letra O.

Exercício Complementar 3.1

1 C R E A T E T ABL E L ivr o (

2 t i t u l o V A R C H A R ( 255 ) NOT NULL, 3 a uto r V A R C H A R ( 255 ) NOT NULL

4 )

5 E N G I N E = I n n o D B ;

Código SQL 3.6: Resposta do exercício

Exercício Complementar 3.3

1 A LTE R T ABL E L ivr o ADD U N I Q U E IND EX( titulo , aut or )

Código SQL 3.7: Resposta do exercício

Exercício Complementar 3.5

1 C R E A T E T ABL E L ivr o ( 2 id INT NOT NULL, 3 isbn BIGINT,

4 t i t u l o V A R C H A R( 255 ) , 5 P R I M A R Y KEY ( id ) 6 )

7 E N G I N E = I n n o D B ;

Código SQL 3.13: Resposta do exercício

Exercício Complementar 3.6

1 C R E A T E T ABL E L i v r o D e t a l h e ( 2 id INT NOT NULL,

3 ano INT, 4 e d i c a o INT, 5 p rec o D E C I M A L(10 ,2) , 6 P R I M A R Y KEY ( id ) , 7 F O R E I G N KEY ( id ) R E F E R E N C E S Liv ro ( id ) 8 ) 9 E N G I N E = I n n o D B ;

Exercício Complementar 3.8

1 C R E A T E T ABL E E d i t o r a ( 2 id INT NOT NULL, 3 nome V A R C H A R( 255 ) , 4 P R I M A R Y KEY ( id ) 5 ) 6 E N G I N E = I n n o D B ; 7 8 C R E A T E T ABL E L ivr o ( 9 id INT NOT NULL, 10 t i t u l o INT UNIQUE, 11 a uto r V A R C H A R( 255 ) , 12 p rec o D E C I M A L(14 ,2) , 13 a u t o r _ i d INT, 14 P R I M A R Y KEY ( id ) , 15 F O R E I G N KEY ( a u t o r _ i d ) R E F E R E N C E S Aut or ( id ) 16 ) 17 E N G I N E = I n n o D B ;

Código SQL 3.18: Resposta do exercício

Exercício Complementar 3.10

1 C R E A T E T ABL E A uto r ( 2 id INT NOT NULL, 3 nome V A R C H A R( 255 ) , 4 P R I M A R Y KEY ( id ) 5 ) 6 E N G I N E = I n n o D B ; 7 8 C R E A T E T ABL E L ivr o ( 9 id INT NOT NULL, 10 t i t u l o V A R C H A R( 255 ) , 11 e d i c a o INT, 12 p rec o D E C I M A L(10 ,2) , 13 isbn INT, 14 P R I M A R Y KEY ( id ) 15 ) 16 E N G I N E = I n n o D B ;

Código SQL 3.23: Resposta do exercício

Exercício Complementar 3.11

1 C R E A T E T ABL E A u t o r L i v r o ( 2 a u t o r _ i d INT NOT NULL, 3 l i v r o _ i d INT NOT NULL,

4 P R I M A R Y KEY ( autor_id , l i v r o _ i d ) ,

5 F O R E I G N KEY ( a u t o r _ i d ) R E F E R E N C E S Aut or ( id ) , 6 F O R E I G N KEY ( l i v r o _ i d ) R E F E R E N C E S Liv ro ( id ) 7 )

Código SQL 3.24: Resposta do exercício Exercício Complementar 4.1 1 S E L E C T l1 .* , ( 2 S E L E C T AVG( l2 . p rec o ) 3 FROM L ivr o AS l2 4 ) AS m e d i a _ p r e c o 5 FROM L ivr o AS l1 ;

Código SQL 4.7: Resposta do exercício

Exercício Complementar 4.2 1 S E L E C T l1 .* 2 FROM L ivr o AS l1 3 W HER E l1 . p rec o > ( 4 S E L E C T MIN( l2 . p rec o ) 5 FROM L ivr o AS l2 6 ) ;

Código SQL 4.8: Resposta do exercício

Exercício Complementar 4.3 1 S E L E C T * 2 FROM L ivr o 3 W HER E L ivr o . a u t o r _ i d IN ( 4 S E L E C T id 5 FROM A uto r

6 W HER E nome LIKE ’A % ’

7 ) ;

Código SQL 4.9: Resposta do exercício

Exercício Complementar 4.4

1 S E L E C T L ivr o .* 2 FROM L ivr o 3 JOIN A uto r

4 ON L ivr o . a u t o r _ i d = A uto r . id 5 W HER E A uto r . nome LIKE ’A % ’;

Exercício Complementar 4.5

1 S E L E C T A uto r .* , C OUN T( L ivr o . id ) AS t o t a l _ l i v r o s 2 FROM A uto r JOIN L ivr o

3 ON L ivr o . a u t o r _ i d = A uto r . id 4 G ROU P BY A uto r . id ;

Código SQL 4.15: Resposta do exercício

Exercício Complementar 4.6

1 S E L E C T * FROM (

2 S E L E C T A uto r .* , C OUN T( L iv ro . id ) AS t o t a l _ l i v r o s 3 FROM A uto r JOIN L ivr o

4 ON L ivr o . a u t o r _ i d = A ut or . id 5 G ROU P BY A uto r . id

6 ) AS A

7 O RDE R BY A . nome DESC;

Código SQL 4.16: Resposta do exercício

Exercício Complementar 4.7

1 S E L E C T n o m e _ u s u a r i o , e mai l 2 FROM U s u a r i o

3 W HER E U s u a r i o . n o m e _ u s u a r i o IS NOT NULL

4 U NIO N ALL

5 S E L E C T n o m e _ u s u a r i o , e mai l 6 FROM U s u a r i o A d m i n i s t r a t i v o

7 W HER E U s u a r i o A d m i n i s t r a t i v o . n o m e _ u s u a r i o IS NOT NULL

8 U NIO N ALL 9 S E L E C T n o m e _ u s u a r i o , e mai l 10 FROM U s u a r i o 11 W HER E U s u a r i o . n o m e _ u s u a r i o IS NULL 12 U NIO N ALL 13 S E L E C T email , e mai l 14 FROM U s u a r i o A d m i n i s t r a t i v o 15 W HER E U s u a r i o A d m i n i s t r a t i v o . n o m e _ u s u a r i o IS NULL;

No documento SQL e Modelo Relacional (páginas 37-51)