Tutorial SQL Componente Curricular: Informรกtica II
Pág.1 ÍNDICE Comando CREATE TABLE ........................................................................................ 3 COMANDO ALTER TABLE ........................................................................................ 3 Cláusula Constraint .................................................................................................... 4 Chave primária ou primary key................................................................................... 4 Comando CREATE INDEX ....................................................................................... 4 Comando DROP ........................................................................................................ 5 Exercício 1 (Create e Alter Table) .............................................................................. 5 Relacionamento ......................................................................................................... 6 Saída ......................................................................................................................... 7 Produtos .................................................................................................................... 7 Entrada ...................................................................................................................... 7 Exercício – Criando tudo junto - CD – Categoria ........................................................ 8 Categoria ................................................................................................................... 8 CD ............................................................................................................................. 8 Exercício – Criando tudo junto - Carro – Marca .......................................................... 8 Carro .......................................................................................................................... 8 Marca ......................................................................................................................... 8 Atualizações do Banco de Dados ............................................................................... 9 Inclusão ..................................................................................................................... 9 Alteração .................................................................................................................... 9 Delete ...................................................................................................................... 10 Exercícios utilizando atualização de dados .............................................................. 10 Exercícios utilizando atualização de dados – Gênero e Filmes ................................ 11 Comandos de Consulta ao Banco de Dados ........................................................... 11 ALUNO .................................................................................................................... 11 CURSO .................................................................................................................... 11 Instrução SELECT ................................................................................................... 12 WHERE ................................................................................................................... 12 Cláusula DISTINCT.................................................................................................. 14 Cláusula ORDER BY ............................................................................................... 14 Cláusula BETWEEN...AND ...................................................................................... 15 Cláusula IN .............................................................................................................. 15 Operador LIKE ......................................................................................................... 15 Cláusula IS NULL..................................................................................................... 16 Operadores negativos: ............................................................................................. 16 Exercício Tabela de Clientes .................................................................................... 16 Cláusula UPPER ...................................................................................................... 17 AVG(n) ..................................................................................................................... 17 MIN(expr) ................................................................................................................. 18 MAX(expr) ................................................................................................................ 18 SUM(n) .................................................................................................................... 18 Exercício Agência Bancária ..................................................................................... 18 Campo Calculado..................................................................................................... 20 Exercícios de Campo Calculado - Tabela de Produtos ............................................ 20 Group By .................................................................................................................. 21 Having ...................................................................................................................... 21 Exercício das tabelas de Clientes e Vendas............................................................. 22 Operação INNER JOIN ............................................................................................ 22 Exercícios usando Inner Join – Biblioteca ................................................................ 23 Exercício das Tabelas de Alunos e Cursos .............................................................. 24 Cláusula União ......................................................................................................... 25 Exercício Farmácia .................................................................................................. 25
Pág.2 Exercício - Vendedores – Pedidos - Clientes .......................................................... 27 BIBLIOGRAFIA
28
Pág.3
Comando CREATE TABLE Criação de uma tabela e definição de seus atributos: Sintaxe: CREATE TABLE <nome_da_tabela> ( nome_do_1º_campo tipo_do_campo [(tamanho)] [not null], nome_do_2º_campo tipo_do_campo [(tamanho)] [not null], nome_do_3º_campo tipo_do_campo...);
Exemplo de criação de tabela: 1) CREATE TABLE DEPTO ( NUMDEP INTEGER NOT NULL, NOMEDEPTO VARCHAR(20), ANDAR INTEGER )
2) CREATE TABLE EMPREGAD0 ( NUMEMP INTEGER NOT NULL, NOMEEMP VARCHAR(20), DATAADMI DATE, SALARIO NUMERIC(18,2) , COMISSAO NUMERIC(9,2), NUMDEP INTEGER) NOT NULL – Se not null for utilizado para um campo, então é necessário que os novos registros tenham dados válidos naquele campo, ou seja, obrigatoriamente esse campo deverá ser preenchido, não podendo deixa-lo sem informação. COMANDO ALTER TABLE Este comando permite alterar (inserir/eliminar) atributos nas tabelas já existentes. Sintaxe: ALTER TABLE < nome_tabela > ADD / DROP ( nome_atributo1 < tipo > [ NOT NULL ], nome_atributoN < tipo > [ NOT NULL ] ) ; Exemplo de alteração na tabela de departamento incluindo os atributos nomegerente e qtdefunc ALTER TABLE depto ADD nomegerente varchar(35) not null, ADD qtdefunc integer;
Exemplo de alteração na tabela de departamento excluindo os atributos nomegerente e qtdefunc ALTER TABLE depto DROP nomegerente, DROP qtdefunc;
Pág.4
Cláusula Constraint Você utiliza a cláusula constraint nas instruções Create Table e Alter Table para criar ou excluir restrições (Chaves primárias e relacionamentos)
Chave primária ou primary key Um ou mais campos (colunas) cujo valor ou valores identificam de modo exclusivo cada registro de uma tabela. Uma chave primária não permite valores nulos e nem repetidos.
Sintaxe para adicionar chave primária: Alter table nome_da_tabela campo que será chave)
Add Constraint
nome_da_chave Primary Key (nome do
Exemplo para adicionar chave primária: 1) Definindo o campo NUMDEP da tabela DEPTO como chave primária ALTER TABLE DEPTO ADD CONSTRAINT CHAVE_DEPTO PRIMARY KEY ( NUMDEP ) 2) Definindo o campo NUMEMP da tabela EMPREGADO como chave primária ALTER TABLE EMPREGADO ADD CONSTRAINT CHAVE_EMP PRIMARY KEY ( NUMEMP )
Sintaxe para deletar chave primária: Alter table nome_da_tabela Drop Constraint nome_da_chave Exemplo para deletar chave primária: 1) Tirando chave primária do campo NUMDEP da tabela DEPTO ALTER TABLE DEPTO DROP CONSTRAINT CHAVE_DEPTO 2) Tirando chave primária do campo NUMEMP da tabela EMPREGADO ALTER TABLE EMPREGADO DROP CONSTRAINT CHAVE_EMP
Comando CREATE INDEX Cria um novo índice em uma tabela existente. Você pode utilizar a propriedade Unique para determinar que um índice assegure que os dados no índice-chave da tabela sejam exclusivos.
Pág.5 ASC - Cria índice crescente. DESC – Cria índice decrescente – Sintaxe para criar índice CREATE UNIQUE ASC INDEX nome_do_índice ON nome_da_tabela (atributo_chave);
Exemplo de criação de índice crescente para o campo numdep da tabela Depto CREATE UNIQUE ASC INDEX idxdepto ON depto(numdep);
Exemplo de criação de índice decrescente para o campo nomedepto da tabela Depto CREATE UNIQUE DESC INDEX idxnome ON depto(nomedepto);
Comando DROP Este comando elimina a definição da tabela, seus dados e referências. Sintaxe: DROP TABLE < nome_tabela >
Exemplo de exclusão da tabela empregado DROP TABLE empregado
Exemplo de exclusão do índice da tabela de depto DROP INDEX idxdepto
Exercício 1 (Create e Alter Table)
1) Ainda no mesmo banco, criar uma tabela chamada Alunos com os atributos abaixo. Os campos RG e nome não poderão ficar nulos Nome do Campo RG Nome Sexo Media Data_Nasc
Tipo Tamanho Varchar 15 Varchar 20 Varchar 1 Numeric (9,1) Date -
Pág.6 2 - Adicionar o campo salário - numeric (10,2) . 3 - Deletar o campo Salário. 4 - Definir o campo RG como chave primária. 5 - Deletar a chave primária. 6 – Criar um índice para o campo nome em ordem crescente. 7 – Criar um índice para o campo sexo em ordem decrescente. 8 - Deletar o índice criado para o campo nome. 7 – Deletar as tabelas Relacionamento 1) Criar um banco chamado RELACAO.GDB e as tabelas de Clientes e Pedidos conforme abaixo, lembrando que os campos CodCli e CodPed não poderão ser nulos. Clientes (lado 1) Nome do Tipo Campo CodCli Integer Nome Varchar Fone Varchar
Tamanho 20 15
Pedido (lado N) Nome do Tipo Campo CodPed Integer Data Data Valor Numeric
Tamanho 20 (9,2)
2) Definir os campos CodCli e CodPed como Chave Primária 3) Adicionar o campo RG – varchar (15) na tabela de Clientes. 4) Deletar o campo Fone da tabela de Clientes. 5) Criar um índice para o campo nome da tabela de Clientes em ordem decrescente. 6) Criar um índice para o campo fone da tabela de Clientes em ordem crescente. 7) Deletar o índice do campo nome da tabela de Clientes 8) Adicionar a chave estrangeira. Lembrando a teoria de banco de dados, num relacionamento (1:N), a tabela do lado N rouba a chave primária do lado 1. Neste caso o campo CodCli deverá fazer parte da tabela de Pedido. Para isto basta adicionar o campo CodCli na tabela de Pedidos usando normalmente o comando Alter Table, conforme abaixo : ALTER TABLE Pedidos ADD CodCli integer;
Obs. Uma chave externa indica como as tabelas se relacionam. Os tipos de dados nos campos de chave externa e primária devem coincidir. Por exemplo, foi definido uma chave primária do tipo integer, então, necessariamente, a chave estrangeira tem que
Pág.7 ser do tipo integer também. Quanto aos nomes não precisam ser iguais, mas é aconselhável colocar o mesmo nome para não haver confusão. 9) Fazer o relacionamento : Sintaxe: ALTER TABLE nome_da_tabela_do_lado_N ADD CONSTRAINT nome_da_constraint FOREIGN KEY (nome_da_chave_estrangeira) REFERENCES nome_da_tabela_do_lado 1 Exemplo: ALTER TABLE Pedidos ADD CONSTRAINT Rel_Ped_Cli (pode ser qualquer nome) FOREIGN KEY (CodCli) REFERENCES Clientes 10) Deletar o relacionamento ALTER TABLE Pedidos DROP CONSTRAINT Rel_Ped_Cli – Exercício de Relacionamento 1) Criar um banco chamado ESTOQUE.GDB e as tabelas Produtos, Entrada e Saída Sabendo-se : N Entrada
1
1
N
Produto
Saída
s
Produtos @CodProduto - Integer Descrição - Varchar (20) Saldo - Integer
2) 3) 4) 5) 6) 7) 8) 9)
Entrada @CodEntrada – Integer Quant_Ent – Integer Data_Entrada - Date
Saída @CodSaida - Integer Quant_Saída – Integer Data_Saída - Date
Adicionar chaves primárias Adicionar os chaves estrangeiras Fazer os relacionamentos envolvendo as três tabelas Adicionar o campo valor na tabela produtos Deletar o relacionamento entre as tabelas de produtos e entrada Deletar o campo data da tabela de entrada Deletar o campo CodEntrada Deletar a tabela Entrada
Pág.8 Exercício – Criando tudo junto - CD – Categoria
Sabendo-se: CD
N
1
Categoria
CD @ CodCD – Integer Titulo – Varchar (30) Cantor – Varchar(20) Preço – Numeric (9,2)
Categoria @ CodCat - Integer Nome – Varchar (20)
1) Criar um banco chamado MÚSICA.GDB 2) Criar as tabelas, chaves primárias e relacionamento. Resposta: Primeiro cria-se a tabela do lado 1 – que é a tabela de Categoria, juntamente com a Chave Primária. CREATE TABLE Categoria (CodCat integer not null, nome varchar(20), CONSTRAINT chave_cat PRIMARY KEY (codcat)) Depois cria-se a tabela do lado N – que é a tabela de CD, juntamente com a Chave Primária, Chave Estrangeira e o Relacionamento. CREATE TABLE CD (CodCd integer not null, titulo varchar(30), cantor varchar(20), preco numeric (9,2), codcat integer, CONSTRAINT chave_cd PRIMARY KEY (codcd), CONSTRAINT rel_cat_cd FOREIGN KEY (codcat) REFERENCES categoria) Exercício – Criando tudo junto - Carro – Marca Sabendo-se: Marca
1
N
Carro
Marca @ CodMarca – Integer Nome – Varchar (30)
Carro @ CodCarro - Integer Modelo – Varchar (20) Placa – Varchar (10) Preco – Numeric (18,2) Data – Date
3) Criar um banco chamado GARAGEM.GDB 4) Criar as tabelas, chaves primárias e relacionamento.
Pág.9 Atualizações do Banco de Dados Inclusão Inclui dados em uma tabela. Sintaxe: INSERT INTO nome_tabela (nome_coluna, ....) VALUES (valor, ...) 1) Exemplo – Adicionando 1 registro na tabela Marca. INSERT INTO marca (CodMarca, Nome) VALUES (1, ‘Fiat’) Você deverá digitar esse comando em SQL no Interbase e depois compilar. Para ver o resultado, digite o seguinte comando : SELECT * FROM MARCA e compile novamente. Deverá aparecer na parte inferior da tela os dados inseridos na tabela. Caso não apareça clique no ícone Commit Work Observe que quando o campo for do tipo varchar, o texto deverá estar entre apóstrofe, no caso a palavra Fiat está entre apóstrofe porque quando a tabela foi criada esse campo foi definido como uma string, ou seja, varchar.
2) Exemplo – Adicionando 1 registro na tabela Carro INSERT INTO Carro (CodCarro, Modelo, Placa, Preço, Data) VALUES (1, 'Palio', ‘BKL-7878’,12320.50, '01/20/04') Neste exemplo foi inserido informações nos campos preço e data. Quando inserimos informações em campo do tipo date é necessário colocar a data entre apóstrofe e no formato mm/dd/aa, ou seja, primeiro o mês, depois o dia e por último o ano. Em campo do tipo Numeric não se coloca ponto (.) e sim a vírgula (,) para separar as casas decimais, conforme exemplo acima. Alteração Atualiza os dados de uma tabela Sintaxe: UPDATE nome_tabela SET nome_coluna=valor, ...
Pág.10 WHERE (condição de localização) 1) Exemplo – Mudar a placa e a data do carro Palio da tabela de Carro para 12600 e 08/01/04 UPDATE carro SET preco=9500 WHERE codcarro = 1 2) Exemplo – Mudar o preço do carro Palio da tabela de Carro para 9500 UPDATE carro SET placa=’DCC-2020’, ‘ 01/08/04’ WHERE codcarro = 1
Delete Remove os dados de uma tabela Sintaxe: DELETE FROM nome_tabela WHERE (condição de localização) 1) Exemplo – Apagar o registro 1 da tabela de Carro DELETE FROM carro WHERE codcarro=1 Exercícios utilizando atualização de dados 1) Criar um banco chamado PESSOAL.GDB 2) Verificar a cardinalidade das tabelas abaixo: Depto @ Cód_depto - Integer Nome_depto - Varchar (20)
Func @ Cód_func - Integer Nome_func - Varchar (20) Período - Varchar (20) Sexo - Varchar (1) Data_adm - Date Cód_depto - Integer
Depend
@ Cód_depend - Integer @ Cód_func - Integer Nome_dep - Varchar(20) Parentesco -Varchar(15) Data_nasc - Date
1- Criar as tabelas acima, adicionar chaves estrangeiras, colocar chave primária e fazer os relacionamentos. Obs. A tabela de dependente tem duas chaves primárias que são cód_depend e cód_func, quando for cria-las, basta colocar os nomes do dois campos entre parênteses, separados por vírgula. 2- Adicionar o campo salário na tabela Func. 3- Adicionar os seguintes dados na tabela Depto: Cód_Dpeto Nome_Depto
Pág.11 1 2 3 4
CPD RH CTB EXPORTAÇÃO
4- Alterar o departamento exportação para produção. 5- Deletar o departamento CTB. 6- Adicionar 5 registros na tabela FUNC através do comando Insert Into. 7- Adicionar 2 dependentes para cada funcionário usando o Insert Into. Exercícios utilizando atualização de dados – Gênero e Filmes 1) Criar um banco chamado LOCA.GDB 1
N
Gênero
Filme
@Cód_gen - Integer Nome_gen – varchar(20)
@Cód_filme - Integer Título – varchar (30) Locado – Varchar (3) Preço – Numeric(8,2)
1- Criar tabelas, adicionar chave estrangeira, definir chaves primárias e fazer relacionamento. 2- Adicionar campo data na tabela de filme. 3- Adicionar os registros abaixo na tabela de gênero: Cód_Gen Nome_Gen 1 Suspense 2 Comédia 3 Terror 4 - Deletar o registro Comédia da tabela de gênero. 5 – Alterar o registro Suspense para Drama 6 - Deletar o campo locado da tabela de filme. 7 - Deletar o campo cód_filme da tabela de filme. 8 - Deletar o relacionamento. 9 - Deletar a tabela de filme.
Comandos de Consulta ao Banco de Dados Sabendo-se: CURSO
1
N
Curso @ CodCurso – Integer Nome – Varchar (30)
ALUNO
Aluno @ RG - Integer Nome – Varchar (20) Salário – Numeric (18,2)
Pág.12 Media – Numeric (5,2) Sexo – Varchar (1) Periodo – Varchar (10) Data_Nasc – Date 1) Criar um banco chamado ESCOLA.GDB 2) Criar as tabelas, chaves primárias, chave estrangeira e relacionamento. 3) Adicionar os registros abaixo na tabela Curso pelo Interbase. Cursos CodCurso Nome 1 Informática 2 Mecânica 3 Enfermagem 4 Eletrônica 5 Química
3) Adicionar os registros abaixo na tabela Alunos pelo Interbase. Alunos RG
Nome
123 456 789 101 112 131
John Iranete Márcia Thiago Erika Cristiane
Salário 700,00 200,00 1000,00 900,00 -
Media 10 6 9 10 7
Sexo M F F M F F
Período Data Nasc. Noite 31/01/60 Manhã 10/02/62 Tarde 05/03/63 Manhã 27/11/50 Tarde 12/12/67 Tarde 01/06/70
CodCurso 1 1 5 4 3 5
Instrução SELECT Retorna as informações do banco de dados como um conjunto de registros. As instruções SELECT não alteram os dados no banco de dados. SELECT é geralmente a primeira palavra em uma instrução SQL. Ex1: Seleção de todas os campos (ou colunas) da tabela de Departamentos. SELECT * FROM Alunos; O exemplo utiliza o coringa "*" para selecionar as colunas na ordem em que foram criadas. A instrução Select, como pudemos observar seleciona um grupo de registros de uma (ou mais) tabela(s). No caso a instrução From nos indica a necessidade de pesquisarmos tais dados apenas na tabela Alunos. WHERE
Pág.13 A cláusula "where" corresponde ao operador restrição da álgebra relacional. Contém a condição que as tuplas devem obedecer a fim de serem listadas. Ela pode comparar valores em colunas, literais, expressões aritméticas ou funções. A seguir apresentamos operadores lógicos e complementares a serem utilizados nas expressões apresentadas em where. Operadores lógicos: Operadores Significado = Igual a > Maior que >= Maior que ou igual a < Menor que Nome Iranete Márcia Érika Cristiane Menor que ou igual a
<=
Ex2 Selecione os campos nome e sexo da tabela Alunos, onde o RG seja igual a 456. SELECT nome, sexo FROM alunos WHERE rg = 456 Demais Operadores: Operadores Between...and... In (...) Like Is null
Significado Entre dois valores(inclusive) Lista de valores Com um padrão de caracteres É um valor nulo
Ex1: Selecionar os alunos do sexo feminino Select nome from alunos where sexo = ‘F’ O resultado será: Ex2: Selecionar o nome, sexo, período dos alunos do sexo feminino que estudam no período da tarde: Select nome, sexo, período from alunos where sexo = ‘F’ and periodo = ‘Tarde’ Nome Márcia Érika Cristiane
Sexo F F F
Período Tarde Tarde Tarde
Pág.14 Cláusula DISTINCT Elimina duplicidades, significando que somente relações distintas serão apresentadas como resultado de uma pesquisa. Ex1: Apresente todos os períodos de estudo existentes na escola, porém omita eventuais duplicidades. SELECT DISTINCT periodo FROM alunos; Os valores que são repetidos, isto é, duplicados só irão aparecer uma vez.
Cláusula ORDER BY Modifica a ordem de apresentação do resultado da pesquisa (ascendente ou descendente). Ex1: Apresente os rg’s e nomes de cada aluno contidos na tabela Alunos, porém classificados alfabeticamente (A..Z) SELECT rg, nome FROM Alunos ORDER BY nome ASC; Obs.: A tabela de alunos foi classificada em ordem crescente pelo nome. Ex 2: Classificar a tabela pelo nome em ordem decrescente: SELECT rg, nome FROM Alunos ORDER BY nome DESC; Para classificar em ordem crescente podemos usar a cláusula ASC ou deixar sem nada, já para classificar em ordem decrescente usamos a cláusula DESC. Ex 3: Classificar os nomes dos alunos do sexo masculino e deixá-los em ordem decrescente: Select nome from alunos where sexo = ‘M’ order by nome desc Nome Thiago John Ex 4: Selecionar todos os nomes dos alunos e deixar em ordem alfabética por nome Select nome from alunos order by nome Nome Cristiane Erika Iranete John
Pág.15 Márcia Thiago Cláusula BETWEEN...AND Entre dois valores (inclusive eles). Ex1: Mostrar os nomes e salários dos alunos que ganham entre 500,00 e 1000,00. SELECT nome, salario FROM alunos WHERE salario BETWEEN 500 AND 1000;
Ex2: Selecionar os nomes dos alunos que nasceram em 1970: Select nome from alunos where data_nasc between ‘01/01/70’ and ‘12/31/70’ Nome Cristiane Não esquecer de colocar a data no sistema americano ‘MM/DD/AA’
Ex3: Selecionar o nome e média dos alunos que tiveram Média entre 8 e 10: Select nome, média from alunos where média between 8 and 10
Cláusula IN Define uma lista de valores. Ex 1: Mostre os alunos onde o rg seja o especificado na lista de valores: SELECT nome, rg FROM alunos WHERE rg IN (123, 101); A cláusula IN define uma lista de valores que será o rg = 123 e rg = 101. Operador LIKE Padrão de caracteres. Comparando uma expressão de seqüência com um padrão em uma expressão SQL.. O símbolo "%" pode ser usado para construir a pesquisa ("%" = qualquer seqüência de nenhum até vários caracteres). Você pode utilizar o operador Like para localizar valores completos (por exemplo: Like “John”) ou para encontrar um intervalo de valores ( por exemplo Like ‘Jo%’).
Pág.16
Ex 1: Selecionar os nomes dos alunos que começam com a letra T SELECT nome FROM alunos WHERE nome LIKE ' T% '; Nome Thiago
Cláusula IS NULL Valor nulo em um campo da tabela. Ex 1: Mostrar os nomes dos alunos onde o salário é nulo. SELECT nome, salário FROM alunos WHERE salario IS NULL Operadores negativos: Operadores <> Not nome_coluna = Not nome_coluna > Not between...and... Not in (...) Not like is not null
Significado diferente Diferente da coluna Não maior que Não entre dois valores(inclusive) Não existente em uma lista de valores Diferente do padrão de caracteres Não é um valor nulo
Ex 1: Selecione os nomes e salários da tabela Alunos cujos salários sejam menores que 250 ou maiores que 800. SELECT nomemp, salário FROM alunos WHERE salario NOT BETWEEN 250 AND 800; Ex 2: Apresente os nomes e salários de todos os alunos com salários menores que 500 ou maiores que 950 e que sejam do sexo feminino. SELECT nome, salário FROM alunos WHERE salario NOT BETWEEN 500 AND 950 AND sexo = ‘F’ Necessitaremos de consultas com condições múltiplas. Operadores Significado AND E OR OU Exercício Tabela de Clientes 1- Criar banco chamado COMERCIO.GDB 2- Criar a tabela de clientes, chave primária e adicionar os registros.
Pág.17
Clientes Cód_cli 1 2 3 4 5 6 7 8 9 10 11 12
Nome Luciene Luciana Thiago Fernando John Luiz Tatiane Vivian Viviana Zildo Lucilene Andréia
Cidade Dobrada Dobrada Matão Sta Ernestina Chicago Turvo Matão Araraquara Jaboticabal Matão Jaboticabal Araraquara
Salário 500,00 500,00 700,00 100,00 50,00 150,00 700,00 500,00 900,00 150,00 100,00 500,00
Pagto A vista A vista A prazo A prazo A prazo A prazo A prazo A prazo A vista A vista A prazo A prazo
Data_compra 10/08/02 20/08/02 13/08/02 10/08/02 03/07/02 13/07/02 20/08/02 20/08/02 10/06/02 10/06/02 20/08/02 10/07/02
Valor_compra 200,00 150,00 300,00 20,00 30,00 400,00 300,00 500,00 500,00 30,00 30,00 20,00
3- Selecionar o código e nome dos clientes que começam com a letra L e deixa-los em ordem alfabética. 4- Selecionar todos os nomes dos clientes e deixa-los em ordem alfabética. 5- Mostrar nome, cidade e salário dos clientes que moram em Matão. 6- Selecionar nome, valor da compra, data da compra e pagamento dos clientes que compraram a prazo e que moram em Matão. 7- Selecionar nome e salário dos clientes que compraram em agosto. 8- Mostrar o nome e salário dos clientes que ganham entre 50 e 500. 9- Mostrar as formas de pagamentos existentes eliminando duplicidades. 10- Selecione o nome, salário dos clientes que ganham menos que 300 e mais que 800. 11- Selecionar nome, código, salário dos clientes dos códigos 1, 8 e 9
Cláusula UPPER Força caracteres minúsculas aparecerem em maiúsculas. Ex 1: Apresente o nome de todos os alunos em letras maiúsculas. SELECT UPPER (nome) FROM alunos
AVG(n) Calcula a média do valor n, ignorando nulos Ex.:1 Mostrar a média dos salários dos alunos. SELECT AVG(salario) FROM alunos
Pág.18 MIN(expr) Apresenta o menor valor da expressão Ex.: 1 Mostrar o menor salário dos alunos. SELECT MIN(salario) FROM alunos MAX(expr) Apresnta o maior valor da expressão Ex.:1 Mostrar o maior salário dos alunos. SELECT
MAX(salario) FROM alunos
SUM(n) Apresenta a soma dos valores de n, ignorando nulos Ex.:1 Mostrar a somatória de todos os salários dos alunos. SELECT SUM(salario) FROM alunos
Exercício Agência Bancária 1. Criar um banco chamado AGENCIA.GDB N Clientes @Codcli - Integer Nome – varchar(20) Fone - varchar(13) Data_Admi - Date Salário – numeric (10,2)
N Agencia @Codagen - Integer nomeagen – varchar (30) cidade - varchar(20) regiao – Varchar (40)
Como o relacionamento é N:N será necessário criar uma terceira tabela, conforme abaixo: Cliagen @Codcli - Integer @Codagen - Integer
2. Criar tabela de clientes e sua respectiva chave primária. 3. Criar tabela de Agencia e sua respectiva chave primária.
Pág.19 4. Criar tabela de cliagen, sua respectivas chaves primárias e os dois relacionamentos. 5. Inserir os dados conforma informações abaixo pelo Interbase Cadastrar clientes
Cadastrar agências
Cadastrar cliagen
6. Selecione todos os clientes onde salário seja maior que 1000 7. Selecione os campos codagen e nomeagen da tabela agencia, onde codagen seja <=3 8. Selecione o nomeagen da tabela agencia, onde região seja igual a Taquaritinga 9. Omita eventuais duplicidades no campo região da tabela agencia. 10. Selecione nome e salário dos clientes e classifique em ordem crescente de nome. 11. Selecione todos os campos de clients onde data_admi esteja entre 01/01/70 à 01/01/80. 12. selecione * da tabela agencia onde codagen façam parte da lista de 1 e 4. 13. Selecione os campos codcli, nome e fone dos clients onde nome comece com a letra J. 14. Apresente todos os nomes de clientes em letras maiúsculas. 15. Apresente a media dos salarios dos clientes
Pág.20
16. Apresente a soma dos salarios dos clientes 17. Apresente o maior salarios dos clientes 18. Apresente o menor salarios dos clientes
Campo Calculado Um campo calculado é um campo que não é gravado fisicamente no banco. Através de cálculos de outros campos da tabela é que se obtém o campo calculado. Como exemplo temos a tabela abaixo, que é de Produtos, onde tem um campo chamado quantidade e outro chamado precounit. Para obter o Total não é necessário criar esse campo fisicamente na tabela, cria-se um campo calculado , que seria a multiplicação da quantidade vezes o preço unitário. Fazendo assim economizamos memória do computador. Exercícios de Campo Calculado - Tabela de Produtos
1. Criar a tabela acima, chave primária e inserir os dados. Resposta: create table produtos ( codpro integer not null, descricao varchar (20), quantidade numeric (9,1), precounit numeric(9,2), constraint chave_prod primary key (codpro)) 2. Criar um campo calculado do preço total. Mostrar a descrição, quantidade, preço unitário e total. Resposta: select descricao, quantidade, precounit, (quantidade * precounit) as Total from produtos 3. Mostrar a descrição, quantidade, preço unitário, total, desconto (considerar 10%) e o total a pagar Resposta:
select descricao, quantidade, precounit, (quantidade * precounit) as Total, (quantidade * precounit* 0.1) as Desconto, (quantidade * precounit* 0.9) as Total_Pagar from produtos Sua tela deverá ficar assim :
Pág.21
4. Mostrar a descrição, quantidade, preço unitário, total, acréscimo (considerar 10%) e o total a pagar. Deixar os produtos em ordem alfabética. Resposta: select descricao, quantidade, precounit, (quantidade * precounit) as Total, (quantidade * precounit* 0.1) as Acrescimo, (quantidade * precounit* 1.10) as Total_Pagar from produtos order by descricao Sua tela deverá ficar assim :
Group By As funções de grupo operam sobre grupos de tuplas(linhas). Retornam resultados baseados em grupos de tuplas em vez de resultados de funções por tupla individual. A claúsula "group by" do comando "select" é utilizada para dividir tuplas em grupos menores. A cláusula "GROUP BY" pode ser usada para dividir as tuplas de uma tabela em grupos menores. As funções de grupo devolvem uma informação sumarizada para cada grupo. Ex.:1 Apresente a média de salário pagos por departamento. SELECT numdep, AVG(salario) FROM empregado GROUP BY numdep; Obs.: Qualquer coluna ou expressão na lista de seleção, que não for uma função agregada, deverá constar da claúsula "group by". Portanto é errado tentar impor uma "restrição" do tipo agregada na cláusula Where. Having A cláusula "HAVING" pode ser utilizada para especificar quais grupos deverão ser exibidos, portanto restringindo-os. Ex.:1 Retome o problema anterior, porém apresente resposta apenas para departamentos com mais de 1 empregado. SELECT numdep, AVG(salario) FROM empregado GROUP BY numdep HAVING COUNT(*) > 1;
Pág.22 Obs.: A claúsula "group by" deve ser colocada antes da "having", pois os grupos são formados e as funções de grupos são calculadas antes de se resolver a cláusula "having". A cláusula "where" não pode ser utilizada para restringir grupos que deverão ser exibidos. Seqüência no comando "Select":
A "sql" fará a seguinte avaliação: a) WHERE, para estabelecer tuplas individuais candidatas (não pode conter funções de grupo) b) GROUP BY, para fixar grupos. c) HAVING, para selecionar grupos para exibição.
Exercício das tabelas de Clientes e Vendas 1) Abra o banco COMERCIO e observar que já tem uma tabela criada chamada CLIENTES, 2) Criar tabela de vendas, chave primária e relacionamento conforme abaixo: Vendas: código_venda – Integer cód_cli – Integer produto – Varchar (30) quantidade – Integer preço – Numeric (9,2) data – date Clientes
1
Valendo Nota: Montar 10 exercícios utilizando os comandos : Where, Distinct, Order By, In, Between, Like, Upper, Avg, Sum, Campo Calculado. N
Adicionar os seguintes registros na tabela de Vendas: Código_Venda Cód_Cli Produto Quantidade 10 2 Calça 2 20 7 Sapato 1 30 1 Camisa 3 40 3 Camisa 2 50 8 Vestido 1 60 3 Camiseta 2
Vendas
Preço 58 75 28 28 53 15
Data 01/12/02 01/12/02 02/12/02 02/12/02 02/12/02 02/12/02
Operação INNER JOIN Combina registros de duas tabelas sempre que houver valores correspondentes em um campo comum. Ex.:1 Selecionar o nome de todos os clientes com seus respectivos nome dos produtos comprados.
Pág.23
Select clientes.nome, vendas.produto From clientes inner join vendas On clientes.cod_cli = vendas.cod_cli Ex.:2 Selecionar o nome do cliente, o nome do produto e o preço do cliente 3. Select clientes.nome, vendas.produto, vendas.preço From clientes inner join vendas On clientes.cod_cli = vendas.cod_cli Where clientes.cod_cli = 3 Resultado: Nome Thiago Thiago
Produto Camisa Camiseta
Preço 28 15
Exercícios usando Inner Join – Biblioteca 1) Criar Banco chamado BIB.GDB
Editora
1
N
N Livros
N
1
Genero
2) Antes de criar as tabelas verifique os tipos de dados através das informações que já estão inseridas. 3) Criar as tabelas, chaves primárias, chaves estrangeiras e relacionamentos. Adicionar as informações através do Interbase. Tabela : Editora CodEditora NomeEditora 1 Érica 2 Ática 3 Atual 4 Makron 5 Vida
Cidade São Paulo Campinas Parati Ribeirão Preto São Paulo
Endereço Av. das Lagoas Av. dos Rios Rua do Mar Av. Cascata Av. Córrego
Cep 02560-555 05698-111 03560-888 20405-777 02650-888
Estado SP SP RJ SP SP
Tabela : Genero CodGen NomeGen 1 Informática 2 Literatura 3 Romance 4 Saúde 5 Esportes Tabela : Livros CodLivro
CodEditora CodGen
Titulo
Autor
1 2 3
2 2 4
Delphi Titanic Clipper
Paulo Zulu Programação 65,00 Luana Piovane Romance 34,00 Thiago Lacerda Programação 27,00
1 3 1
Assunto
Preço
Pág.24 4
5
4
5 6
1 3
5 1
7 8
1 3
5 2
Comer Bem
Ana Paula Arósio Verdão é o melhor Marcelo Antony Conheça o Front Maria Page Fernanda Verdão é imbatível Brad Pitt Romeu e Julieta Fábio Assumpção
Culinária
18,00
Esporte Internet
99,00 22,00
Esporte Romantismo
76,00 45,00
Com base nas tabelas acima, responda : 1. Selecionar as editoras do estado de SP e deixa-las em ordem alfabética 2. Selecionar o código do livro, título e autor dos livros que custam mais que R$ 50,00 3. Selecionar todos os livros que fazer parte do assunto Esporte 4. Mostrar o código da editora, código do livro e preço do código da editora 2 5. Mostrar o código da editora, titulo do livro e o nome da editora do código 2. Utilizar inner join. 6. Mostrar o código, titulo e preço da tabela livro, o código e nome da tabela gênero. Usar inner join. Fazer com o código 4. 7. Listar somente os títulos dos livros que começam com a letra C 8. Selecionar os códigos e título dos livros que forem da editora 2 e do gênero 3. 9. Mostrar os resultados. Exercício das Tabelas de Alunos e Cursos
1. Criar as tabelas acima, chaves primárias, relacionamentos e inserir os dados através do Access 2. Mostrar o nome, nota1, nota2 e a média do aluno 2. O campo média será um campo calculado. 3. Mostrar o nome do aluno e o nome do curso do código 3. Classificar pelo campo nome do curso. 4. Selecionar os nomes dos alunos onde a média for maior que 7 5. Selecionar os nomes, nota1, nota2 e média e deixar em ordem decrescente de média 6. Selecionar os nomes do cursos que começam com a letra E 7. Selecionar o nome do aluno, a nota1, nota2 e o nome do curso dos alunos que fazem PD. Organizar pelo campo nota2.
Pág.25
8. Mostrar a Média das Notas1, a Quantidade de Alunos, a Maior Nota1, a Menor Nota1, a Soma das Notas1 9. Mostrar a Média das Notas2, a Quantidade de Alunos, a Maior Nota2, a Menor Nota2, a Soma das Notas2 10. Selecionar os nomes dos alunos onde a média estiver entre 8 e 10 11. Mostrar o assunto da tabela de livros eliminando duplicidades. 12. Mostrar código e nome das editoras onde a cidade não foi cadastadas. 13. Mostre os nomes dos livros das editoras 2, 4 e 5. Usar cláusula IN 14. Mostrar nome dos gêneros em letras maiúsculas e em ordem alfabética. 15. Mostra a soma, a média, o máximo e o mínimo dos preços dos livros.
Cláusula União Podemos eventualmente unir duas linhas de consultas simplesmente utilizando a palavra reservada UNION. Ex.:1 mostre todos os empregados onde numero do empregado seja igual a 2 e numero do departamento seja igual a 2 SELECT * FROM empregado WHERE numemp=2 UNION select * FROM empregado WHERE numdep=2; Exercício Farmácia 1) Abra um banco chamado FARMACIA.GDB
2) Criar tabela de Cliente e definir chave primária. Adicionar os dados.
Pág.26
3) Criar tabela de Compra, definir chave primária, adicionar chave estrangeira e fazer relacionamento. Adicionar os dados.
4) Criar tabela de Produtos e definir chave primária. Adicionar os dados.
5) Criar tabela de Compra, definir chave primária, adicionar chave estrangeira e fazer relacionamento. Adicionar os dados.
6 – apresente a soma dos preços dos itens dos produtos 7 – apresente a soma dos preços das compras de cada cliente, apresentando pelo número da compra. select n_compra, sum (preco) from itens_compra group by n_compra 8 - apresente a soma dos preços das compras de cada cliente, mas apenas para compras com mais de 1 item. select n_compra, sum (preco) from itens_compra
Pág.27 group by n_compra having count(*)>1 9 – selecione os campos n_compra, codcli, data e nome cli e fone das tabelas cliente e compra, onde codcli seja igual a 1
10 – Mostre todos os campos onde n_compra seja 1 e codprod seja 5 select * from itens_compra where n_compra=1 union select * from itens_compra where codprod =5 11 – mostre a media da renda dos clientes 12 – selecione os campos nome, fone e emprego de clientes onde renda esteja entre 1000 e 1500. 13 – mostre todos os clientes que comecem com a letra R 14 – Classifique os clientes por Ordem alfabética crescente por nome. Exercício - Vendedores – Pedidos - Clientes
Vendedores Vendedores Cód_Vend 1 2 3 4 Pedido N_Ped 1 2 3 4 5 6 7 Clientes CodCli 1 2
1
N
Nome_Vend Ellen João Michelle Marcos
Data 10/08/00 20/08/00 10/09/00 20/09/00 30/09/00 05/10/00 30/08/00
Nome Hélica Soraia
Pedido
N
1
Clientes
Telefone 282-7060 282-6070 282-4420 282-2020
V_Total 300,00 400,00 200,00 700,00 150,00 900,00 820,00
Cidade Matão Araraquara
Prazo 5 10 5 10 5 15 15
Vendedor 3 4 2 1 3 1 2
Cliente 2 3 4 1 5 2 6
Pág.28 3 4 5 6
Selma Tony Wagner Elaine
Matão Dobrada Matão Dobrada
1. 2. 3. 4. 5. 6. 7.
Criar um banco chamado PEDIDOS.GDB Criar as tabelas, chaves primárias, chaves estrangeiras e relacionamentos Selecionar os nomes dos vendedores e deixa-los em ordem alfabética Selecionar a data, valor total e número dos pedidos que ficaram mais que 600 Selecionar a data e valor total dos pedidos feitos no mês de Setembro. Selecionar todos os dados dos vendedores, onde os nomes começam com a letra M Mostrar o código, nome e cidade dos clientes que moram em Matão. Deixar os clientes em ordem alfabética. 8. Mostrar o valor total e data dos pedidos, o código e nome de um dos vendedores. Utilizar Inner Join. 9. Mostrar o valor total e data dos pedidos, o código, nome e cidade dos clientes que fizeram pedidos no mês de agosto. Utilizar e Inner Join. 10. Selecionar todos os clientes e deixar por ordem decrescente de cidade .
Bibliografia OLIVEIRA, José de Oliveira MS-SQL Dominando seus Dados. Editora Érica, 1999. Revista PC Máster – Ano 3 nº 8 Edição 32 Revista ClubeDelphi – Ano 3 Edição 29