SQL e modelo relacional, Notas de estudo de Engenharia Informática

Baixe SQL e modelo relacional e outras Notas de estudo em PDF para Engenharia Informática, somente na Docsity!

TREINAMENTOS

SQL e Modelo Relacional

 - 30 de julho de 

• Sobre a K19 Sumário i
• Seguro Treinamento
• Termo de Uso
• Cursos
• 1 Introdução
  • 1.1 SGBD
  • 1.2 MySQL Server
  • 1.3 Bases de dados ( Databases )
  • 1.4 Criando uma base de dados no MySQL Server
  • 1.5 Tabelas
  • 1.6 Tabelas no MySQL Server
  • 1.7 CRUD
  • 1.8 Restrições
  • 1.9 Exercícios de Fixação
• 2 Consultas
  • 2.1 SELECT
  • 2.2 WHERE
  • 2.3 Exercícios de Fixação
  • 2.4 ORDER BY
  • 2.5 Exercícios de Fixação
  • 2.6 Funções de Agrupamento
  • 2.7 Exercícios de Fixação
  • 2.8 GROUP BY
  • 2.9 Exercícios de Fixação
  • 2.10 DISTINCT
  • 2.11 LIMIT
• 3 Relacionamentos SUMÁRIO ii
  • 3.1 UNIQUE
  • 3.2 Exercícios de Fixação
  • 3.3 Exercícios Complementares
  • 3.4 Chaves Primárias
  • 3.5 Chaves Estrangeiras
  • 3.6 One to One
  • 3.7 Exercícios de Fixação
  • 3.8 Exercícios Complementares
  • 3.9 One to Many ou Many to One
  • 3.10 Exercícios de Fixação
  • 3.11 Exercícios Complementares
  • 3.12 Many to Many
  • 3.13 Exercícios de Fixação
  • 3.14 Exercícios Complementares
• 4 Subqueries, Joins e Unions
  • 4.1 Subqueries
  • 4.2 Exercícios de Fixação
  • 4.3 Exercícios Complementares
  • 4.4 Joins
  • 4.5 Exercícios de Fixação
  • 4.6 Exercícios Complementares
  • 4.7 Unions
  • 4.8 Exercícios de Fixação
  • 4.9 Exercícios Complementares
• A Respostas

1 SUMÁRIO

Sobre a K

A K19 é uma empresa especializada na capacitação de desenvolvedores de software. Sua equipe

é composta por profissionais formados em Ciência da Computação pela Universidade de São Paulo

(USP) e que possuem vasta experiência em treinamento de profissionais para área de TI.

O principal objetivo da K19 é oferecer treinamentos de máxima qualidade e relacionados às prin-

cipais tecnologias utilizadas pelas empresas. Através desses treinamentos, seus alunos se tornam

capacitados para atuar no mercado de trabalho.

Visando a máxima qualidade, a K19 mantém as suas apostilas em constante renovação e melho-

ria, oferece instalações físicas apropriadas para o ensino e seus instrutores estão sempre atualizados

didática e tecnicamente.

3 SUMÁRIO

Termo de Uso

Termo de Uso

Todo o conteúdo desta apostila é propriedade da K19 Treinamentos. A apostila pode ser utilizada

livremente para estudo pessoal. Além disso, este material didático pode ser utilizado como material

de apoio em cursos de ensino superior desde que a instituição correspondente seja reconhecida pelo

MEC (Ministério da Educação) e que a K19 seja citada explicitamente como proprietária do material.

É proibida qualquer utilização desse material que não se enquadre nas condições acima sem

o prévio consentimento formal, por escrito, da K19 Treinamentos. O uso indevido está sujeito às

medidas legais cabíveis.

SUMÁRIO 4

K01- Lógica de Programação

K11 - Orientação a Objetos em Java

K12 - Desenvolvimento Web com JSF2 e JPA

K21 - Persistência com JPA2 e Hibernate

K22 - Desenvolvimento Web Avançado com JFS2, EJB3.1 e CDI

K23 - Integração de Sistemas com Webservices, JMS e EJB

K31 - C# e Orientação a Objetos

K32 - Desenvolvimento Web com ASP.NET MVC

TREINAMENTOSTREINAMENTOSTREINAMENTOS Conheça os nossos cursos

www.k19.com.br/cursos

INTRODUÇÃO 2

Suponha que os dados fossem mantidos sem nenhuma separação lógica. Implementar regras

de segurança específicas seria extremamente complexo. Tais regras criam restrições quanto ao con-

teúdo que pode ser acessado por cada usuário. Por exemplo, determinado usuário poderia ter per-

missão de acesso aos dados dos clientes do banco, mas não às informações dos produtos da loja

virtual, ou dos livros da livraria.

Para obter uma organização melhor, os dados são armazenados separadamente em um SGBD.

Daí surge o conceito de base de dados (database). Uma base de dados é um agrupamento lógico das

informações de um determinado domínio.

Criando uma base de dados no MySQL Server

Para criar uma base de dados no MySQL Server, podemos utilizar o comando CREATE DATA-

BASE.

mysql > CREATE DATABASE livraria ; Query OK , 1 row affected (0.02 sec )

Terminal 1.1: Criando uma base de dados.

Podemos utilizar o comando SHOW DATABASES para listar as bases de dados existentes.

mysql > show databases ;

• • • • • • • • • • - - - - - - - - - - -+ | Database | + - - - - - - - - - - - - - - - - - - - -+ | information_schema | | livraria | | mysql | | test | + - - - - - - - - - - - - - - - - - - - -+ 4 rows in set (0.03 sec )

Terminal 1.2: Listando as bases de dados existentes.

Repare que, além da base de dados livraria , há outras três bases. Essas bases foram criadas au-

tomaticamente pelo próprio MySQL Server para teste ou para armazenar configurações.

Quando uma base de dados não é mais necessária, ela pode ser removida através do comando

DROP DATABASE.

mysql > DROP DATABASE livraria ; Query OK , 0 rows affected (0.08 sec )

Terminal 1.3: Destruindo uma base de dados.

Tabelas

Um servidor de banco de dados é dividido em bases de dados com o intuito de separar as infor-

mações de domínios diferentes. Nessa mesma linha de raciocínio, podemos dividir os dados de uma

base a fim de agrupá-los segundo as suas correlações. Essa separação é feita através de tabelas. Por

exemplo, no sistema de um banco, é interessante separar o saldo e o limite de uma conta, do nome e

CPF de um cliente. Então, poderíamos criar uma tabela para os dados relacionados às contas e outra

para os dados relacionados aos clientes.

3 INTRODUÇÃO

Cliente

nome idade cpf

José 27 31875638735

Maria 32 30045667856

Conta

numero saldo limite

1 1000 500

2 2000 700

Tabela 1.1: Tabelas para armazenar os dados relacionados aos clientes e às contas

Uma tabela é formada por registros (linhas) e os registros são formados por campos (colunas).

Por exemplo, considere uma tabela para armazenar as informações dos clientes de um banco. Cada

registro dessa tabela armazena em seus campos os dados de um determinado cliente.

Tabelas no MySQL Server

As tabelas no MySQL Server são criadas através do comando CREATE TABLE. Na criação de uma

tabela, é necessário definir quais são os nomes e os tipos das colunas.

mysql > CREATE TABLE ‘ livraria ‘. ‘ Livro ‘ ( -> ‘ titulo ‘ VARCHAR (255) , -> ‘ preco ‘ DOUBLE -> ) -> ENGINE = MyISAM ; Query OK , 0 rows affected (0.14 sec )

Terminal 1.4: Criando uma tabela.

As tabelas de uma base de dados podem ser listadas através do comando SHOW TABLES. Antes

de utilizar esse comando, devemos selecionar uma base de dados através do comando USE.

mysql > USE livraria ; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A

Database changed mysql > SHOW TABLES ;

• • • • • • • • • • - - - - - - - - - - -+ | Tables_in_livraria | + - - - - - - - - - - - - - - - - - - - -+ | Livro | + - - - - - - - - - - - - - - - - - - - -+ 1 row in set (0.00 sec )

Terminal 1.5: Listando as tabelas de uma base de dados.

Se uma tabela não for mais desejada, ela pode ser removida através do comando DROP TABLE.

mysql > DROP TABLE Livro ; Query OK , 0 rows affected (0.00 sec )

Terminal 1.6: Destruindo uma tabela.

Também podemos alterar a estrutura de uma tabela com o comando ALTER TABLE.

mysql > ALTER TABLE Livro RENAME livros ; Query OK , 0 rows affected (0.00 sec )

Terminal 1.7: Alterando o nome da tabela.

mysql > ALTER TABLE Livro ADD paginas INTEGER ; Query OK , 0 rows affected (0.00 sec )

5 INTRODUÇÃO

Restrições

Podemos estabelecer algumas restrições sobre os valores armazenados nas tabelas para manter a

consistência dos dados. Por exemplo, é possível obrigar que um determinado campo possua sempre

um valor não nulo.

No MySQL Server, quando criamos uma tabela, podemos adicionar a restrição NOT NULL nas

colunas que são obrigatórias.

mysql > CREATE TABLE ‘ livraria ‘. ‘ Livro ‘ ( -> ‘ titulo ‘ VARCHAR (255) NOT NULL , -> ‘ preco ‘ DOUBLE NOT NULL -> ) -> ENGINE = MyISAM ; Query OK , 0 rows affected (0.14 sec )

Terminal 1.16: Aplicando o comando NOT NULL nas colunas obrigatórias.

Também podemos definir, no MySQL Server, que uma coluna não pode possuir valores repetidos

através do comando UNIQUE.

mysql > CREATE TABLE ‘ livraria ‘. ‘ Livro ‘ ( -> ‘ titulo ‘ VARCHAR (255) NOT NULL UNIQUE , -> ‘ preco ‘ DOUBLE NOT NULL -> ) -> ENGINE = MyISAM ; Query OK , 0 rows affected (0.14 sec )

Terminal 1.17: Aplicando o comando UNIQUE na coluna titulo.

Exercícios de Fixação

(^1) Abra um terminal, crie e acesse uma pasta com o seu nome.

cosen@k19 :~ $ mkdir rafael cosen@k19 :~ $ cd rafael / cosen@k19 :~/ rafael$

Terminal 1.18: Criando e acessando uma pasta com o seu nome.

(^2) Estando dentro da sua pasta, acesse o MySQL Server utilizando o usuário root e a senha root.

k19@k19 -11:~/ rafael$ mysql -u root -p Enter password :

Terminal 1.19: Logando no MySQL Server.

(^3) Caso exista uma base de dados chamada livraria , remova-a. Utilize o comando SHOW DATA-

BASES para listar as bases de dados existentes e o comando DROP DATABASE para remover a base

livraria se ela existir.

mysql > SHOW DATABASES ;

• • • • • • • • • • - - - - - - - - - - -+ | Database | + - - - - - - - - - - - - - - - - - - - -+ | information_schema | | livraria |

INTRODUÇÃO 6

| mysql | | test |

• • • • • • • • • • - - - - - - - - - - -+ 4 rows in set (0.00 sec )

mysql > DROP DATABASE livraria ; Query OK , 1 row affected (0.12 sec )

Terminal 1.20: Listando as bases de dados existentes e removendo a base livraria.

(^4) Crie uma nova base de dados chamada livraria. Utilize o comando CREATE DATABASE. Você

vai utilizar esta base nos exercícios seguintes.

mysql > CREATE DATABASE livraria ; Query OK , 1 row affected (0.00 sec )

Terminal 1.21: Criando a base livraria.

(^5) Abra um editor de texto e digite o código abaixo para criar uma tabela com o nome Editora.

Depois salve o arquivo com o nome create-table-editora.sql dentro da pasta com o seu nome.

1 USE livraria ;

2 CREATE TABLE Editora (

3 id BIGINT NOT NULL AUTO_INCREMENT ,

4 nome VARCHAR (255) NOT NULL ,

5 email VARCHAR (255) NOT NULL ,

7 ENGINE = InnoDB ;

Código SQL 1.1: Criando a tabela Editora

(^6) Dentro do terminal, use o comando source para executar o arquivo que você acabou de criar.

mysql > source create - table - editora. sql Database changed Query OK , 0 rows affected (0.08 sec )

Terminal 1.22: Executando a tabela Editora.

(^7) Abra um novo editor de texto e digite o código abaixo para criar uma tabela com o nome Livro.

Em seguida, salve o arquivo com o nome create-table-livro.sql dentro da pasta com o seu nome.

1 USE livraria ;

2 CREATE TABLE Livro (

3 id BIGINT NOT NULL AUTO_INCREMENT ,

4 titulo VARCHAR (255) NOT NULL ,

5 preco DOUBLE NOT NULL ,

7 ENGINE = InnoDB ;

Código SQL 1.2: Criando a tabela Livro

(^8) Dentro do terminal, use o comando source para executar o código do arquivo create-table-livro.sql.

mysql > source create - table - livro. sql Database changed Query OK , 0 rows affected (0.08 sec )

Terminal 1.23: Executando a tabela Livro.

(^9) Abra um novo editor de texto e digite o código abaixo para adicionar alguns registros na tabela

INTRODUÇÃO 8

| id | titulo | preco |+ - - - -+ - - - - - - - - - - - - - - - - - - - - - - -+ - - - - - - -+| 1 | Aprendendo C# ←- | 89.9 || 2 | Introdução ao JSF 2 | 122.9 || 3 | JSF 2 Avançado | 149.9 |+ - - - -+ - - - - - - - - - - - - - - - - - - - - - - -+ - - - - - - 3 rows in set (0.00 sec )

Terminal 1.27: Selecionando os livros.

(^14) Altere alguns dos registros da tabela Livro. Utilize o comando UPDATE.

mysql > UPDATE Livro SET preco =92.9 WHERE id =1; Query OK , 1 row affected (0.07 sec ) Rows matched : 1 Changed : 1 Warnings : 0

Terminal 1.28: Alterando livros.

(^15) Altere alguns dos registros da tabela Editora. Utilize o comando UPDATE.

mysql > UPDATE Editora SET nome =’ OReilly ’ WHERE id =1; Query OK , 1 row affected (0.09 sec ) Rows matched : 1 Changed : 1 Warnings : 0

Terminal 1.29: Alterando editoras.

(^16) Remova alguns registros da tabela Livro. Utilize o comando DELETE.

mysql > DELETE FROM Livro WHERE id =2; Query OK , 1 row affected (0.07 sec )

Terminal 1.30: Removendo livros.

CONSULTAS

C

A P Í T U L O 2

SELECT

Através do comando SELECT, podemos recuperar as informações armazenadas em um banco

de dados. Para utilizar o comando SELECT, devemos indicar as tabelas que serão consultadas e as

colunas que queremos recuperar.

No exemplo abaixo, as colunas nome e email são recuperadas da tabela Aluno.

1 SELECT nome , email FROM Aluno ;

Código SQL 2.1: Recuperando as colunas nome e email da tabela Aluno

Aluno

nome email

Rafael Cosentino [email protected]

Jonas Hirata [email protected]

Tabela 2.1: Resultado da consulta: SELECT nome, email FROM Aluno

Quando todas as colunas devem ser recuperadas, é mais prático utilizar o caractere “ ***** ”. Veja o

exemplo abaixo:

1 SELECT * FROM Aluno ;

Código SQL 2.2: Recuperando todas as colunas da tabela Aluno

Aluno

nome email telefone altura

Rafael Cosentino [email protected] 11 23873791 1.

Jonas Hirata [email protected] 11 23873791 1.

Tabela 2.2: Resultado da consulta: SELECT * FROM Aluno

É possível recuperar colunas de várias tabelas. Nesse caso, os registros das tabelas consultadas

são “cruzados”. Veja o exemplo abaixo:

1 SELECT * FROM Aluno , Professor ;

Código SQL 2.3: Recuperando todas as colunas das tabelas Aluno e Professor

11 CONSULTAS

Código SQL 2.6: Aplicando o comando WHERE

Aluno

nome email telefone altura

Rafael Cosentino [email protected] 11 23873791 1.

Tabela 2.6: Resultado da consulta: SELECT * FROM Aluno WHERE altura > 1.

Eis uma lista de algumas funções e operadores de comparação do MySQL Server que podem ser

utilizados com o comando WHERE:

• =

1 SELECT * FROM Aluno WHERE altura = 1.8;

• <> ou !=

1 SELECT * FROM Aluno WHERE altura <> 1.8;

2 SELECT * FROM Aluno WHERE altura != 1.8;

• <=

1 SELECT * FROM Aluno WHERE altura <= 1.8;

• <

1 SELECT * FROM Aluno WHERE altura < 1.8;

• =

1 SELECT * FROM Aluno WHERE altura >= 1.8;

1 SELECT * FROM Aluno WHERE altura > 1.8;

• IS

1 SELECT * FROM Aluno WHERE aprovado IS TRUE ;

• IS NOT

1 SELECT * FROM Aluno WHERE aprovado IS NOT TRUE ;

• IS NULL

1 SELECT * FROM Aluno WHERE nome IS NULL ;

• IS NOT NULL

1 SELECT * FROM Aluno WHERE nome IS NOT NULL ;

CONSULTAS 12

• BETWEEEN... AND...

1 SELECT * FROM Aluno WHERE altura BETWEEN 1.5 AND 1.8;

• NOT BETWEEEN... AND...

1 SELECT * FROM Aluno WHERE altura NOT BETWEEEN 1.5 AND 1.8;

• LIKE

1 SELECT * FROM Aluno WHERE nome LIKE ’ Rafael % ’;

• NOT LIKE

1 SELECT * FROM Aluno WHERE nome NOT LIKE ’ Rafael % ’;

• IN()

1 SELECT * FROM Aluno WHERE altura IN (1.5 , 1.6 , 1.7 , 1.8) ;

• NOT IN()

1 SELECT * FROM Aluno WHERE altura NOT IN (1.5 , 1.6 , 1.7 , 1.8) ;

Eis uma lista dos operadores lógicos do MySQL Server que também podem ser utilizados com o

comando WHERE.

• NOT ou!

1 SELECT * FROM Aluno WHERE NOT altura = 1.80;

2 SELECT * FROM Aluno WHERE! ( altura = 1.80) ;

• AND ou &&

1 SELECT * FROM Aluno WHERE altura < 1.8 AND nome LIKE ’ Rafael % ’;

2 SELECT * FROM Aluno WHERE altura < 1.8 && nome LIKE ’ Rafael % ’;

• OR ou ||

1 SELECT * FROM Aluno WHERE altura < 1.8 OR nome LIKE ’ Rafael % ’;

2 SELECT * FROM Aluno WHERE altura < 1.8 || nome LIKE ’ Rafael % ’;

• XOR

1 SELECT * FROM Aluno WHERE altura < 1.8 XOR nome LIKE ’ Rafael % ’;

Exercícios de Fixação

(^1) Crie uma tabela Aluno com as colunas nome (VARCHAR(255)), email (VARCHAR(255)) telefone

(VARCHAR(10)), altura (DECIMAL(3,2)) e aprovado (TINYINT(1)).

1 CREATE TABLE Aluno (