3. Introdução à linguagem SQL

Nesta seção do capítulo, apresentamos os primeiros comandos SQL que permitem criar e utilizar uma única tabela. Em geral, apresentamos uma versão simplificada deles. Sua sintaxe completa está disponível nos guias de referência do Firebird (ver parágrafo 2.2).

Um banco de dados é utilizado por pessoas com diferentes competências:

o administrador do banco de dados é, em geral, alguém que domina a linguagem SQL e os bancos de dados. É ele quem cria as tabelas, pois essa operação geralmente é realizada apenas uma vez. Com o tempo, ele pode precisar modificar a estrutura delas. Um banco de dados é um conjunto de tabelas interligadas por relações. É o administrador do banco de dados que definirá essas relações. É também ele quem concederá direitos aos diferentes usuários do banco de dados. Assim, ele indicará que determinado usuário tem o direito de visualizar o conteúdo de uma tabela, mas não de modificá-la.
O usuário do banco de dados é quem dá vida aos dados. De acordo com os direitos concedidos pelo administrador do banco de dados, ele irá adicionar, modificar e excluir dados nas diferentes tabelas do banco. Ele também irá explorá-los para extrair informações úteis para o bom funcionamento da empresa, da administração, etc.

No parágrafo 2.6, apresentamos o editor SQL da ferramenta [IB-Expert]. É essa ferramenta que vamos utilizar. Vamos relembrar alguns pontos:

O editor SQL é acessado por meio da opção de menu [Tools/SQL Editor] ou pela tecla [F12]

Assim, é exibida uma janela [SQL Editor], na qual podemos digitar um comando SQL:

A captura de tela acima costuma ser representada pelo texto a seguir:

SQL> select * from BIBLIO

3.1. Os tipos de dados do Firebird

Ao criar uma tabela, é necessário indicar o tipo de dados que uma coluna da tabela pode conter. Apresentamos aqui os tipos de dados mais comuns do Firebird. Vale ressaltar que esses tipos de dados podem variar de um SGBD para outro.

`SMALLINT`	número inteiro no intervalo [-32768, 32767]: 4
`INTEGER`	número inteiro no domínio [–2 147 483 648, 2 147 483 647]: -100
`NUMERIC(n,m) DECIMAL(n,m)`	número real com n dígitos, dos quais m após a vírgula NUMERIC(5,2): -100,23, +027,30
`FLOAT`	número real aproximado com 7 algarismos significativos: 10,4
`DOUBLE PRECISION`	número real aproximado com 15 algarismos significativos: -100.89
`CHAR(N) CHARACTER(N)`	cadeia de exatamente N caracteres. Se a cadeia armazenada tiver menos de N caracteres, ela será preenchida com espaços. CHAR(10): 'ANGERS ' (4 espaços no final)
`VARCHAR(N) CHARACTER VARYING(N)`	cadeia de, no máximo, N caracteres VARCHAR(10): 'ANGERS'
`DATE`	uma data: '2006-01-09' (formato YYYY-MM-DD)
`TIME`	uma hora: '16:43:00' (formato HH:MM:SS)
`TIMESTAMP`	data e hora juntas: '2006-01-09 16:43:00' (formato YYYY-MM-DD HH:MM:SS)

A função CAST() permite converter de um tipo para outro quando necessário. Para converter um valor V declarado como sendo do tipo T1 para o tipo T2, escreve-se: CAST(V,T2). É possível realizar as seguintes conversões de tipo:

número para cadeia de caracteres. Essa mudança de tipo ocorre implicitamente e não requer o uso da função CAST. Assim, a operação 1 + '3' não requer a conversão do caractere '3'. Seu resultado é o número 4.
DATE, TIME, TIMESTAMP para cadeias de caracteres e vice-versa. Assim,
TIMESTAMP para TIME ou DATE e vice-versa

Em uma tabela, uma linha pode conter colunas sem valor. Diz-se que o valor da coluna é a constante NULL. É possível verificar a presença desse valor por meio dos operadores

IS NULL / IS NOT NULL

3.2. Criação de uma tabela

Para descobrir como criar uma tabela, começamos criando uma no modo [Design] com IBExpert. Para isso, seguimos o método descrito no parágrafo 2.3. Assim, criamos a seguinte tabela:

Essa tabela servirá para registrar os livros adquiridos por uma biblioteca. O significado dos campos é o seguinte:

Name	Tipo	Restrição	Significado
`ID`	`INTEGER`	`Primary Key`	`Identifiant du livre`
`TITRE`	`VARCHAR(30)`	`NOT NULL UNIQUE`	`Titre du livre`
`AUTEUR`	`VARCHAR(20)`	`NOT NULL`	`Son auteur`
`GENRE`	`VARCHAR(30)`	`NOT NULL`	`Son genre (Roman, Poésie, Policier, BD, ..)`
`ACHAT`	`DATE`	`NOT NULL`	`Date d'achat du livre`
`PRIX`	`NUMERIC6,2)`	`NOT NULL`	`Son prix`
`DISPONIBLE`	`CHAR(1)`	`NOT NULL`	`Est-il disponible ? O (oui), N (non)`

Esta tabela, que foi criada com a ferramenta IBEXPERT como assistente, poderia ter sido criada diretamente por meio das ordens SQL. Para conhecê-las, basta consultar a aba [DDL] da tabela:

O código SQL, que permitiu criar a tabela [BIBLIO], é o seguinte:

SET SQL DIALECT 3;

SET NAMES ISO8859_1;


CREATE TABLE BIBLIO (
    ID INTEGER NOT NULL,
    TITRE VARCHAR(30) NOT NULL,
    AUTEUR VARCHAR(20) NOT NULL,
   GENRE VARCHAR(30) NOT NULL,
   ACHAT DATE NOT NULL,
   PRIX NUMERIC(6,2) NOT NULL,
   DISPONIBLE  CHAR(1) NOT NULL
);

ALTER TABLE BIBLIO ADD CONSTRAINT UNQ1_BIBLIO UNIQUE (TITRE);
ALTER TABLE BIBLIO ADD CONSTRAINT PK_BIBLIO PRIMARY KEY (ID);

linha 1: proprietário Firebird — indica o nível do dialeto SQL utilizado
linha 2: proprietário Firebird — indica a família de caracteres utilizada
linhas 6 a 14: padrão SQL: cria a tabela BIBLIO, definindo o nome e o tipo de cada uma de suas colunas.
linha 16: padrão SQL: cria uma restrição indicando que a coluna TITRE não permite duplicatas
linha 17: padrão SQL: indica que a coluna [ID] é a chave primária da tabela. Isso significa que duas linhas da tabela não podem ter o mesmo valor para ID. Estamos aqui próximos da restrição [UNIQUE NOT NULL] da coluna [TITRE] e, de fato, a coluna TITRE poderia ter servido como chave primária. A tendência atual é utilizar chaves primárias que não tenham significado e que sejam geradas pelo SGBD.

A sintaxe do comando [CREATE TABLE] é a seguinte:

syntaxe

CREATE TABLE tabela (nom_colonne1 type_colonne1 contrainte_colonne1, nom_colonne2 type_colonne2 contrainte_colonne2, ..., nom_colonnen type_colonnen contrainte_colonnen, outras restrições)

action

cria a tabela table com as colunas indicadas

`nom_colonnei`	nome da coluna i a ser criada
`type_colonnei`	tipo de dados da coluna i: char(30) numeric(6,2) date timestamp ...
`contrainte_colonnei`	restrições que os dados da coluna i devem respeitar. Aqui estão algumas delas: PRIMARY KEY: a coluna é uma chave primária. Isso significa que duas linhas da tabela nunca terão o mesmo valor nessa coluna e, além disso, que é obrigatório preencher um valor nessa coluna. Uma chave primária serve principalmente para identificar uma linha de forma única. NOT NULL : não são permitidos valores nulos nessa coluna. UNIQUE : nenhum valor pode aparecer mais de uma vez na coluna. CHECK (condição): o valor da coluna deve atender à condição.
`autres contraintes`	aqui é possível definir - restrições em várias colunas: check(col1>col2) - restrições de chaves estrangeiras

A tabela [BIBLIO] também poderia ter sido criada com a seguinte ordem SQL:

CREATE TABLE BIBLIO (
    ID INTEGER NOT NULL PRIMARY KEY,
    TITRE VARCHAR(30) NOT NULL UNIQUE,
    AUTEUR VARCHAR(20) NOT NULL,
   GENRE VARCHAR(30) NOT NULL,
   ACHAT DATE NOT NULL,
   PRIX NUMERIC(6,2) NOT NULL,
   DISPONIBLE  CHAR(1) NOT NULL
);

Vamos demonstrar isso. Retomemos essa ordem em um editor SQL (F12) para criar uma tabela que chamaremos de [BIBLIO2]:

Após a execução, é necessário confirmar a transação para visualizar o resultado no banco de dados:

Feito isso, a tabela aparece no banco de dados:

Ao clicar duas vezes em seu nome, é possível acessar sua estrutura:

Podemos ver que a definição que criamos para a tabela [BIBLIO2] está correta

3.3. Exclusão de uma tabela

A ordem SQL para excluir uma tabela é a seguinte:

`syntaxe`	DROP TABLE table
`action`	Supprime [table]

Para excluir a tabela [BIBLIO2] que acabamos de criar, executamos agora o seguinte comando SQL:

e a validamos com [Commit]. A tabela [BIBLIO2] é excluída:

3.4. Preenchimento de uma tabela

Inserimos uma linha na tabela [BIBLIO] que acabamos de criar:

Validamos a adição da linha por meio de [Commit] e, em seguida, clicamos com o botão direito do mouse na linha adicionada:

e solicitemos, conforme mostrado acima, que a linha inserida seja copiada para a área de transferência na forma de um comando SQL INSERT. Em seguida, abramos qualquer editor de texto e colemos (Colar / Paste) o que acabamos de copiar. Obtemos o seguinte código SQL:

INSERT INTO BIBLIO (ID,TITRE,AUTEUR,GENRE,ACHAT,PRIX,DISPONIBLE) VALUES (1,'Candide','Voltaire','Essai','18-OCT-1985',140,'o');

A sintaxe de um comando de inserção SQL é a seguinte:

`syntaxe`	insert into table [(colonne1, colonne2, ..)] values (valor1, valor2, ....)
`action`	adiciona uma linha (valor1, valor2, ..) à tabela table. Esses valores são atribuídos às tabelas colonne1, colonne2, ... caso existam; caso contrário, às colunas da tabela na ordem em que foram definidas.

Para inserir novas linhas na tabela [BIBLIO], digite os seguintes comandos INSERT no editor SQL. Essas ordens serão executadas e validadas uma a uma. Utilize o botão para passar para a próxima ordem.

insert into biblio(id,titre,auteur,genre,achat,prix,disponible) values (2,'Les fleurs du mal','Baudelaire','Poème','01-jan-78',120,'n');
insert into biblio(id,titre,auteur,genre,achat,prix,disponible) values (3,'Tintin au Tibet','Hergé','BD','10-nov-90',70,'o');
insert into biblio(id,titre,auteur,genre,achat,prix,disponible) values (4,'Du côté de chez Swann','Proust','Roman','08-dec-78',200,'o');
insert into biblio(id,titre,auteur,genre,achat,prix,disponible) values (5,'La terre','Zola','roman','12-jun-90',50,'n');
insert into biblio(id,titre,auteur,genre,achat,prix,disponible) values (6,'Madame Bovary','Flaubert','Roman','12-mar-88',130,'o');
insert into biblio(id,titre,auteur,genre,achat,prix,disponible) values (7,'Manhattan transfer','Dos Passos','Roman','30-aug-87',320,'o');
insert into biblio(id,titre,auteur,genre,achat,prix,disponible) values (8,'Tintin en Amérique','Hergé','BD','15-may-91',70,'o');

Após validar as diferentes ordens SQL, obtemos a tabela a seguir:

3.5. Consulta de uma tabela

3.5.1. Introdução

No editor SQL, digitemos o seguinte comando:

e executá-lo. Obtemos o seguinte resultado:

O comando SELECT permite consultar o conteúdo das tabelas do banco de dados. Esse comando possui uma sintaxe bastante abrangente. Apresentamos aqui apenas a sintaxe que permite consultar uma única tabela. Abordaremos posteriormente a consulta simultânea de várias tabelas. A sintaxe do comando SQL [SELECT] é a seguinte:

syntaxe

SELECT [ALL|DISTINCT] [*|expression1 alias1, expression2 alias2, ...]

FROM table

action

exibe os valores de expressioni para todas as linhas da tabela. expressioni pode ser uma coluna ou uma expressão mais complexa. O símbolo * designa o conjunto de colunas. Por padrão, todas as linhas da tabela (ALL) são exibidas. Se DISTINCT estiver presente, as linhas idênticas selecionadas são exibidas apenas uma vez. Os valores de expressioni são exibidos em uma coluna com o título expressioni ou aliasi, caso este tenha sido utilizado.

Exemplos:

SQL > select titre, auteur from biblio

SQL> select titre,prix from biblio

SQL> select titre TITRE_DU_LIVRE, prix PRIX_ACHAT from biblio

Acima, associamos aliases (TITRE_DU_LIVRE, PRIX_ACHAT) às colunas solicitadas.

3.5.2. Exibição das linhas que atendem a uma condição

`syntaxe`	SELECT .... WHERE condition
`action`	somente as linhas que atendem à condição condition são exibidas

Exemplos

SQL> select titre,prix from biblio where prix>100

SQL> select titre,prix,genre from biblio where genre='Roman'

Um dos livros tem o gênero “romance” e não “Romance”. Utilizamos a função upper, que transforma uma sequência de caracteres em maiúsculas para obter todos os romances.

SQL> select titre,prix,genre from biblio where upper(genre)='ROMAN'

Podemos combinar condições por meio de operadores lógicos

`AND`	ET logique
`OR`	OU logique
`NOT`	Negação lógica

SQL> select titre,prix,genre from biblio where upper(genre)='ROMAN' and prix<100

SQL> select titre,genre from biblio

SQL> select titre,genre from biblio where upper(genre)='ROMAN' or upper(genre)='BD'

SQL> select titre,genre from biblio where not( upper(genre)='ROMAN' or upper(genre)='BD')

SQL> select titre,achat from biblio

SQL>select titre,achat from biblio where achat>'31-dec-1987'

SQL> select titre,prix from biblio where prix between 100 and 150

3.5.3. Exibição das linhas em uma ordem determinada

Às sintaxes anteriores, é possível adicionar uma cláusula ORDER BY indicando a ordem de exibição desejada:

`syntaxe`	SELECT .... ORDER BY expression1 [asc\|desc], expression2 [asc\|dec], ...
`action`	As linhas resultantes da seleção são exibidas na ordem de 1: ordem crescente (asc / ascending, que é o valor padrão) ou decrescente (desc / descending) de expression1 2: em caso de igualdade de expression1, a exibição é feita de acordo com os valores de expression2 etc.

Exemplos:

SQL>select titre, genre,prix,achat from biblio order by achat desc

SQL>select titre, genre,prix,achat from biblio order by prix

SQL>select titre, genre,prix,achat from biblio order by genre desc

SQL >select titre, genre,prix,achat from biblio order by genre desc, prix

SQL>select titre, genre,prix,achat from biblio order by genre desc, prix desc

3.6. Exclusão de linhas em uma tabela

`syntaxe`	DELETE FROM table [WHERE condition]
`action`	elimina as linhas de table verificando condition. Se esta última estiver ausente, todas as linhas são excluídas.

Exemplos:

SQL> select titre from biblio

Os dois comandos abaixo são executados um após o outro:

SQL> delete from biblio where titre='Candide'

SQL> select titre from biblio

3.7. Alteração do conteúdo de uma tabela

`syntaxe`	update table set coluna1 = expressão1, coluna2 = expressão2, ... [where condition]
`action`	Para as linhas de table que verificam condition (todas as linhas, se não houver condição), colonnei recebe o valor de expressioni.

Exemplos:

SQL> select genre from biblio

Colocamos todos os gêneros em maiúsculas:

SQL> update biblio set genre=upper(genre)

Verificamos:

SQL> select genre from biblio

Exibimos os preços:

SQL> select genre,prix from biblio;

O preço dos romances aumenta em 5%:

SQL> update biblio set prix=prix*1.05 where genre='ROMAN';

Verificamos:

SQL> select genre,prix from biblio

3.8. Atualização definitiva de uma tabela

Quando são feitas alterações em uma tabela, o Firebird, na verdade, as gera em uma cópia da tabela. Elas podem então ser tornadas definitivas ou canceladas pelos comandos COMMIT e ROLLBACK.

`syntaxe`	COMMIT
`action`	torna definitivas as atualizações feitas nas tabelas desde a última execução do COMMIT.

`syntaxe`	ROLLBACK
`action`	anula todas as modificações feitas nas tabelas desde a última execução do COMMIT.

Remarque

Um COMMIT é executado implicitamente nos seguintes momentos:

a) Ao desconectar-se do Firebird

b) Após cada comando que afete a estrutura das tabelas: CREATE, ALTER, DROP.

Exemplos

No editor SQL, coloca-se o banco de dados em um estado conhecido, validando todas as operações realizadas desde o último COMMIT ou ROLLBACK:

SQL> commit

Solicita-se a lista de títulos:

SQL> select titre from biblio

Exclusão de um título:

SQL> delete from biblio where titre='La terre'

Verificação:

SQL> select titre from biblio

O título foi excluído com sucesso. Agora, invalidamos todas as alterações feitas desde o último COMMIT / ROLLBACK:

SQL> rollback

Verificação:

SQL> select titre from biblio

O título excluído aparece novamente. Agora, vamos solicitar a lista de preços:

SQL> select prix from biblio

Suponhamos que todos os preços tenham sido zerados.

SQL> update biblio set prix=0

Vamos verificar os preços:

SQL> select prix from biblio

Vamos reverter as alterações feitas na base:

SQL> rollback

e vamos verificar os preços novamente:

SQL> select prix from biblio

Recuperamos os preços originais.

3.9. Adicionando linhas de uma tabela a outra

É possível adicionar linhas de uma tabela a outra quando suas estruturas são compatíveis. Para demonstrar isso, vamos começar criando uma tabela [BIBLIO2] com a mesma estrutura que a [BIBLIO].

No explorador de bancos de dados de IBExpert, cliquemos duas vezes na tabela [BIBLIO] para acessar a guia [DDL]:

Nessa guia, encontramos a lista de ordens SQL que permitem gerar a tabela [BIBLIO]. Copie todo esse código para a área de transferência (CTRL-A, CTRL-C). Em seguida, vamos chamar uma ferramenta chamada [Script Executive], que permite executar uma lista de ordens SQL:

Aparece um editor de texto, no qual podemos colar (CTRL-V) o texto que colocamos anteriormente na área de transferência:

Costuma-se chamar de script SQL uma lista de comandos SQL. O [Script Executive] nos permitirá executar esse tipo de script, enquanto o editor SQL permitia a execução de apenas um comando por vez. O script SQL atual permite criar a tabela [BIBLIO]. Vamos fazer com que ele crie uma tabela chamada [BIBLIO2]. Para isso, basta alterar [BIBLIO] para [BIBLIO2]:

SET SQL DIALECT 3;

SET NAMES ISO8859_1;

CREATE TABLE BIBLIO2 (
    ID          INTEGER NOT NULL,
    TITRE       VARCHAR(30) NOT NULL,
    AUTEUR      VARCHAR(20) NOT NULL,
    GENRE       VARCHAR(20) NOT NULL,
    ACHAT       DATE NOT NULL,
    PRIX        NUMERIC(6,2) DEFAULT 10 NOT NULL,
    DISPONIBLE  CHAR(1) NOT NULL
);

ALTER TABLE BIBLIO2 ADD CONSTRAINT UNQ1_BIBLIIO2 UNIQUE (TITRE);

ALTER TABLE BIBLIO2 ADD CONSTRAINT PK_BIBLIIO2 PRIMARY KEY (ID);

Vamos executar esse script usando o botão [Run Script] abaixo:

O script é executado:

e podemos ver a nova tabela no explorador de bancos de dados:

Se clicarmos duas vezes em [BIBLIO2] para verificar seu conteúdo, descobriremos que ela está vazia, o que é normal:

Uma variante do comando SQL INSERT permite inserir em uma tabela linhas provenientes de outra tabela:

`syntaxe`	INSERT INTO table1 [(colonne1, colonne2, ...)] SELECT colunaA, colunaB, ... FROM table2 WHERE condition
`action`	As linhas de table2 que verificam condition são adicionadas a table1. As colunas colonnea, colonneb, ... de table2 são atribuídas, nessa ordem, às colunas colonne1, colonne2, ... de table1 e, portanto, devem ser de tipo compatível.

Voltemos ao editor SQL:

e emitamos o comando SQL a seguir:

SQL> insert into BIBLIO2 select * from BIBLIO where upper(genre)='ROMAN'

que insere no [BIBLIO2] todas as linhas do [BIBLIO] correspondentes a um romance. Após a execução da ordem SQL, vamos validá-la com um [Commit]:

SQL> commit

Feito isso, consultemos os dados da tabela [BIBLIO2]:

SQL> select * from BIBLIO2

3.10. Exclusão de uma tabela

`syntaxe`	DROP TABLE table
`action`	supprime table

Exemplo: exclui-se a tabela BIBLIO2

SQL> drop table BIBLIO2

Confirmamos a alteração:

SQL> commit

No explorador de bancos de dados, atualiza-se a exibição das tabelas:

Verifica-se que a tabela [BIBLIO2] foi excluída:

3.11. Alteração da estrutura de uma tabela

`syntaxe`	ALTER TABLE table [ ADD nom_colonne1 type_colonne1 contrainte_colonne1] [ALTER nom_colonne2 TYPE type_colonne2] [DROP nom_colonne3] [ADD contrainte] [DROP CONSTRAINT nom_contrainte]
`action`	permite adicionar (ADD), modificar (ALTER) e excluir (DROP) colunas da tabela. A sintaxe nom_colonnei type_colonnei contrainte_colonnei é a mesma do CREATE TABLE. Também é possível adicionar/excluir restrições de tabela.

Exemplo: Executemos sucessivamente os dois comandos SQL a seguir no editor SQL

SQL > alter table biblio add nb_pages numeric(4), alter genre type varchar(30)

SQL> commit

No explorador de bancos de dados, vamos verificar a estrutura da tabela [BIBLIO]:

As alterações foram aplicadas. Vamos ver como o conteúdo da tabela mudou:

SQL> select * from biblio

A nova coluna [NB_PAGES] foi criada, mas não possui nenhum valor. Vamos excluir essa coluna:

SQL> alter table biblio drop nb_pages

SQL> commit

Vamos verificar a nova estrutura da tabela [BIBLIO]:

A coluna [NB_PAGES] foi removida corretamente.

3.12. As visões

É possível ter uma visão parcial de uma tabela ou de várias tabelas. Uma visão funciona como uma tabela, mas não contém dados. Seus dados são extraídos de outras tabelas ou visões. Uma visão apresenta várias vantagens:

Um usuário pode estar interessado apenas em determinadas colunas e linhas de uma determinada tabela. A visão permite que ele veja apenas essas linhas e colunas.
O proprietário de uma tabela pode desejar autorizar apenas um acesso limitado a outros usuários. A visão permite que ele faça isso. Os usuários que ele autorizar terão acesso apenas à visão que ele definiu.

3.12.1. Criação de uma vista

`syntaxe`	CREATE VIEW nom_vue AS SELECT coluna1, coluna2, ... FROM table WHERE condition [ WITH CHECK OPTION ]
`action`	cria a visualização nom_vue. Essa é uma tabela cuja estrutura é composta pelas colunas coluna1, coluna2, ... de table e, como linhas, as linhas de table que atendem à condição de condition (todas as linhas, caso não haja condição)
`WITH CHECK OPTION`	Esta cláusula opcional indica que as inserções e atualizações na visão não devem criar linhas que a visão não possa selecionar.

Observação A sintaxe de CREATE VIEW é, na verdade, mais complexa do que a apresentada acima e permite, em particular, criar uma visualização a partir de várias tabelas. Para isso, basta que a consulta SELECT abranja várias tabelas (ver capítulo seguinte).

Exemplos

A partir da tabela biblio, cria-se uma visualização que inclua apenas os romances (seleção de linhas) e apenas as colunas título, autor e preço (seleção de colunas):

SQL> create view romans as select titre,auteur,prix from biblio where upper(genre)='ROMAN';

SQL> commit

No explorador de bancos de dados, atualizamos a visualização (F5). Aparece uma visualização:

É possível identificar a ordem SQL associada à visualização. Para isso, clique duas vezes na visualização [ROMANS]:

Uma visão é como uma tabela. Ela possui uma estrutura:

e um conteúdo:

Uma visualização é usada como uma tabela. É possível fazer consultas SQL nela. Aqui estão alguns exemplos para testar no editor SQL:

SQL> select * from romans

SQL> insert into biblio values (10,'Le père Goriot','Balzac','Roman','01-sep-91',200,'o')

O novo romance está visível na visualização [ROMANS]?

SQL> select * from romans

Vamos adicionar algo além de um romance à tabela [BIBLIO]:

SQL> insert into biblio(id,titre,auteur,genre,achat,prix,disponible) values (11,'Poèmes saturniens','Verlaine','Poème','02-sep-92',200,'o');

Vamos verificar a tabela [BIBLIO]:

SQL> select titre, auteur from BIBLIO

Vamos verificar a visualização [ROMANS]:

SQL> select titre, auteur from ROMANS

O livro adicionado não está na visualização [ROMANS] porque não possuía upper(gênero)='ROMAN'.

3.12.2. Atualização de uma visão

É possível atualizar uma visão da mesma forma que se faz com uma tabela. Todas as tabelas das quais os dados da visão são extraídos são afetadas por essa atualização. Aqui estão alguns exemplos:

SQL> insert into biblio(id,titre,auteur,genre,achat,prix,disponible) values (13,'Le Rouge et le Noir','Stendhal','Roman','03-oct-92',110,'o')

SQL> select * from romans

SQL> select titre, auteur from biblio

Elimina-se uma linha da visualização [ROMANS]:

SQL> delete from ROMANS where titre='Le Rouge et le Noir'

SQL> select * from romans

SQL> select auteur, titre from BIBLIO

A linha excluída da visão [ROMANS] também foi excluída da tabela [BIBLIO]. Agora, aumentamos o preço dos livros da visão [ROMANS]:

SQL> update romans set prix=prix*1.05

Verificamos na [ROMANS]:

SQL> select * from romans

Qual foi o impacto na tabela [BIBLIO]?

SQL> select titre, auteur, prix from biblio

Os romances também tiveram um aumento de 5% na tabela [BIBLIO].

3.12.3. Excluir uma visualização

`syntaxe`	DROP VIEW nom_vue
`action`	exclui a visualização chamada

Exemplo

SQL> drop view romans

SQL> commit

No explorador de bancos de dados, é possível atualizar a visualização (F5) para verificar que a visualização [ROMANS] desapareceu:

3.13. Uso de funções de grupos

Existem funções que, em vez de atuarem em cada linha de uma tabela, atuam em grupos de linhas. Trata-se, essencialmente, de funções estatísticas que nos permitem calcular a média, o desvio-padrão etc. dos dados de uma coluna.

`syntaxe1`	SELECT f1, f2, .., fn FROM table [ WHERE condition ]
`action`	calcula as funções estatísticas fi em todas as linhas da tabela, verificando a eventual condition.

syntaxe2

SELECT f1, f2, .., fn FROM table

[ WHERE condition ]

[ GROUP BY expr1, expr2, ..]

action

A palavra-chave GROUP BY tem como efeito dividir as linhas da tabela em grupos. Cada grupo contém as linhas para as quais as expressões expr1, expr2, ... têm o mesmo valor.

Exemplo: GROUP BY gênero coloca em um mesmo grupo os livros com o mesmo gênero. A cláusula GROUP BY autor,gênero colocaria no mesmo grupo os livros com o mesmo autor e o mesmo gênero. A cláusula WHERE condição elimina primeiro da tabela as linhas que não atendem à condição. Em seguida, os grupos são formados pela cláusula GROUP BY. As funções fi são então calculadas para cada grupo de linhas.

`syntaxe3`	SELECT f1, f2, .., fn FROM table [ WHERE condition ] [ GROUP BY expression] [ HAVING condition_de_groupe]
`action`	A cláusula HAVING filtra os grupos formados pela cláusula GROUP BY. Portanto, ela está sempre vinculada à presença dessas cláusulas: GROUP e BY. Exemplo: GROUP BY gênero HAVING gênero!='ROMAN'

As funções estatísticas fi disponíveis são as seguintes:

`AVG(expression)`	média da expressão
`COUNT(expression)`	número de linhas nas quais a expressão tem um valor
`COUNT(*)`	número total de linhas na tabela
`MAX(expression)`	valor máximo da expressão
`MIN(expression)`	mínimo de expressão
`SUM(expression)`	soma da expressão

Exemplos

SQL> select prix from biblio

Preço médio? Preço máximo? Preço mínimo?

SQL> select avg(prix), max(prix), min (prix) from biblio

SQL> select titre, prix,genre from biblio

Preço médio de um romance? Preço máximo?

SQL> select avg(prix) moyenne, max(prix) prix_maxi from biblio where upper(genre)='ROMAN'

Quantos BD?

SQL> select count(*) from biblio where upper(genre)='BD'

Quantos romances custam menos de 100 F?

SQL> select count(*) from biblio where upper(genre)='ROMAN' and prix<100

SQL> select genre, prix from biblio

Quantos livros e qual é o preço médio por livro para livros do mesmo gênero?

SQL> select upper(genre) GENRE,avg(prix) PRIX_MOYEN,count(*) NOMBRE from biblio group by upper(genre)

A mesma pergunta, mas apenas para livros que não sejam romances:

SQL>
select upper(genre) GENRE,avg(prix) PRIX_MOYEN,count(*) NOMBRE
from biblio
group by upper(genre)
having upper(GENRE)!='ROMAN'

A mesma pergunta, mas apenas para livros com preço inferior a 150 F:

SQL> 
select upper(genre) GENRE,avg(prix) PRIX_MOYEN,count(*) NOMBRE
from biblio
where prix<150
group by upper(genre)
having upper(GENRE)!='ROMAN'

A mesma questão, mas consideramos apenas os grupos com preço médio por livro >100 F

SQL> 
select upper(genre) GENRE, avg(prix) PRIX_MOYEN,count(*) NOMBRE
from biblio
group by upper(genre)
having avg(prix)>100

3.14. Criar o script SQL “ ” a partir de uma tabela

A linguagem SQL é uma linguagem padrão que pode ser utilizada com diversos SGBD. Para poder passar de um SGBD para outro, é recomendável exportar um banco de dados ou apenas alguns elementos dele na forma de um script SQL que, quando executado em outro SGBD, será capaz de recriar os elementos exportados no script.

Vamos, neste caso, exportar a tabela [BIBLIO]. Vamos escolher a opção [Extract Metadata]:

Observe-se acima que é necessário estar na base da qual se deseja exportar os elementos. A opção inicia um assistente:

`1`	onde gerar o script SQL: em um arquivo (File) na área de transferência (Clipboard) na ferramenta Script Executive
`2`	nome do arquivo se a opção [File] for selecionada
`3`	o que exportar
`4`	botões para selecionar (->) ou desmarcar (<-) os objetos a serem exportados

Se quiséssemos exportar toda a base de dados, marcaríamos a opção [Extract All] acima. Queremos apenas exportar a tabela BIBLIO. Para isso, com [4], selecionamos a tabela [BIBLIO] e, com [2], indicamos um arquivo:

Se pararmos por aqui, apenas a estrutura da tabela [BIBLIO] será exportada. Para exportar seu conteúdo, precisamos usar a aba [Data Tables]:

Vamos usar [1] para selecionar a tabela [BIBLIO]:

Usemos [2] para gerar o script SQL:

Aceitemos a oferta. Isso nos permite ver o script que foi gerado no arquivo [biblio.sql]:

/******************************************************************************/
/****          Gerado por IBExpert 17/06/2004 22/01/2006 15:        06:13 ****/
/******************************************************************************/

SET SQL DIALECT 3;

SET NAMES ISO8859_1;

CREATE DATABASE 'D:\data\serge\travail\2005-2006\polys\sql\DBBIBLIO.GDB'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 16384
DEFAULT CHARACTER SET ISO8859_1;



/******************************************************************************/
/****                                                                 Tabelas ****/
/******************************************************************************/



CREATE TABLE BIBLIO (
ID          INTEGER NOT NULL,
TITRE       VARCHAR(30) NOT NULL,
AUTEUR      VARCHAR(20) NOT NULL,
GENRE       VARCHAR(30) NOT NULL,
ACHAT       DATE NOT NULL,
PRIX        NUMERIC(6,2) DEFAULT 10 NOT NULL,
DISPONIBLE  CHAR(1) NOT NULL
);

INSERT INTO BIBLIO (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (2, 'Les fleurs du mal', 'Baudelaire', 'POèME', '1978-01-01', 120, 'n');
INSERT INTO BIBLIO (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (3, 'Tintin au Tibet', 'Hergé', 'BD', '1990-11-10', 70, 'o');
INSERT INTO BIBLIO (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (4, 'Du côté de chez Swann', 'Proust', 'ROMAN', '1978-12-08', 220.5, 'o');
INSERT INTO BIBLIO (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (5, 'La terre', 'Zola', 'ROMAN', '1990-06-12', 55.13, 'n');
INSERT INTO BIBLIO (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (6, 'Madame Bovary', 'Flaubert', 'ROMAN', '1988-03-12', 143.33, 'o');
INSERT INTO BIBLIO (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (7, 'Manhattan transfer', 'Dos Passos', 'ROMAN', '1987-08-30', 352.8, 'o');
INSERT INTO BIBLIO (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (8, 'Tintin en Amérique', 'Hergé', 'BD', '1991-05-15', 70, 'o');
INSERT INTO BIBLIO (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (10, 'Le père Goriot', 'Balzac', 'Roman', '1991-09-01', 210, 'o');
INSERT INTO BIBLIO (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (11, 'Poèmes saturniens', 'Verlaine', 'Poème', '1992-09-02', 200, 'o');

COMMIT WORK;



/******************************************************************************/
/****                                                     Restrições de exclusividade ****/
/******************************************************************************/

ALTER TABLE BIBLIO ADD CONSTRAINT UNQ1_BIBLIO UNIQUE (TITRE);


/******************************************************************************/
/****                                                           Chaves primárias ****/
/******************************************************************************/

ALTER TABLE BIBLIO ADD CONSTRAINT PK_BIBLIO PRIMARY KEY (ID);

as linhas 1 a 3 são comentários
as linhas 5 a 12 são do SQL, de propriedade do Firebird
as demais linhas são do SQL padrão, que devem poder ser executadas novamente em um SGBD que teria os tipos de dados declarados na tabela BIBLIO.

Vamos executar novamente esse script no Firebird para criar uma tabela BIBLIO2, que será um clone da tabela BIBLIO. Para isso, vamos usar o [Script Executive] (Ctrl-F12):

Vamos carregar o script [biblio.sql] que acabamos de gerar:

Vamos modificá-lo para manter apenas a parte referente à criação da tabela e à inserção de linhas. A tabela é renomeada como [BIBLIO2]:

CREATE TABLE BIBLIO2 (
    ID          INTEGER NOT NULL,
    TITRE       VARCHAR(30) NOT NULL,
    AUTEUR      VARCHAR(20) NOT NULL,
    GENRE       VARCHAR(30) NOT NULL,
    ACHAT       DATE NOT NULL,
    PRIX        NUMERIC(6,2) DEFAULT 10 NOT NULL,
    DISPONIBLE  CHAR(1) NOT NULL
);

INSERT INTO BIBLIO2 (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (2, 'Les fleurs du mal', 'Baudelaire', 'POèME', '1978-01-01', 120, 'n');
...

COMMIT WORK;

Vamos executar este script:

Podemos verificar no explorador de bancos de dados se a tabela [BIBLIO2] foi realmente criada e se possui a estrutura e o conteúdo esperados: