O material de estudo referente a estas aulas encontra-se no vosso livro (Ullman-Widom),
Devem também consultar documentação online sobre o Postgres,
Os exemplos que se seguem são baseados na base de dados de filmes do vosso livro de texto, "A First Course in Database Systems", e grande parte deles foram ilustrados nas aulas teóricas.
CREATE TABLE Estudios(
nome VARCHAR(30),
morada VARCHAR(70),
PRIMARY KEY (nome)
);
CREATE TABLE Realizadores(
nome VARCHAR(50),
categoria CHAR, -- vamos assumir que é A, B, ou C.
PRIMARY KEY (nome)
);
CREATE TABLE Filmes(
nome VARCHAR(50),
ano INTEGER,
duracao INTEGER,
aCores BOOLEAN,
nomeEstudio VARCHAR(30),
nomeRealizador VARCHAR(50),
PRIMARY KEY (nome,ano),
FOREIGN KEY (nomeEstudio) REFERENCES Estudios(nome),
FOREIGN KEY (nomeRealizador) REFERENCES Realizadores(nome)
);
CREATE TABLE Actores(
nome VARCHAR(50),
morada VARCHAR(70),
sexo CHAR,
dataNascimento DATE,
PRIMARY KEY (nome)
);
CREATE TABLE Participa(
nomeFilme VARCHAR(50),
anoFilme INTEGER,
nomeActor VARCHAR(50),
PRIMARY KEY (nomeFilme,anoFilme,nomeActor),
FOREIGN KEY (nomeActor) REFERENCES Actores(nome),
FOREIGN KEY (nomeFilme,anoFilme) REFERENCES Filmes(nome,ano)
);
INSERT INTO Estudios VALUES ('Fox' , '101 Embarcadero, Los Angeles');
INSERT INTO Estudios VALUES ('Disney' , '56 Yankee Boulevard, Los Angeles');
INSERT INTO Estudios VALUES ('Paramount' , '44 Pine St., Los Angeles');
INSERT INTO Estudios VALUES ('Universal' , '23 Universal Studies Dr., Los Angeles');
INSERT INTO Estudios VALUES ('Warner Brothers' , '71 Parkway Dr., San Diego');
INSERT INTO Realizadores VALUES ('George Lucas' , 'A');
INSERT INTO Realizadores VALUES ('Steven Spielberg' , 'A');
INSERT INTO Realizadores VALUES ('Kevin Costner' , 'B');
INSERT INTO Realizadores VALUES ('Irvin Kershner' , 'B');
INSERT INTO Realizadores VALUES ('Richard Marquand' , 'A');
INSERT INTO Realizadores VALUES ('Stephen Herek' , 'A');
INSERT INTO Realizadores VALUES ('Penelope Spheeris' , 'A');
INSERT INTO Realizadores VALUES ('Roger Allers' , 'A');
INSERT INTO Realizadores VALUES ('Mike Gabriel' , 'A');
INSERT INTO Realizadores VALUES ('Clint Eastwood' , 'A');
INSERT INTO Realizadores VALUES ('Barry Sonnenfeld' , 'A');
INSERT INTO Realizadores VALUES ('Merian Cooper' , 'A');
INSERT INTO Realizadores VALUES ('John Guillermin' , 'A');
INSERT INTO Realizadores VALUES ('Victor Fleming' , 'C');
INSERT INTO Realizadores VALUES ('Paul Verhoeven' , 'B');
INSERT INTO Realizadores VALUES ('Andrew Davies' , 'B');
INSERT INTO Realizadores VALUES ('Tim Burton' , 'A');
INSERT INTO Realizadores VALUES ('Tony Scott' , 'A');
INSERT INTO Realizadores VALUES ('Stanley Kubrik' , 'A');
INSERT INTO Realizadores VALUES ('Baz Luhrmann' , 'A');
INSERT INTO Realizadores VALUES ('Oliver Stone' , 'A');
INSERT INTO Filmes VALUES ('Star Wars' , 1977, 124, true, 'Fox' , 'George Lucas');
INSERT INTO Filmes VALUES ('Empire Strikes Back' , 1980, 143, true, 'Fox' , 'Irvin Kershner');
INSERT INTO Filmes VALUES ('Return of the Jedi' , 1983, 165, true, 'Fox' , 'Richard Marquand');
INSERT INTO Filmes VALUES ('Mighty Ducks' , 1991, 104, true, 'Disney' , 'Stephen Herek');
INSERT INTO Filmes VALUES ('Wayne''s World' , 1992, 95, true, 'Paramount' , 'Penelope Spheeris');
INSERT INTO Filmes VALUES ('Lion King' , 1994, 122, true, 'Disney' , 'Roger Allers');
INSERT INTO Filmes VALUES ('Pocahontas' , 1995, 115, true, 'Disney' , 'Mike Gabriel');
INSERT INTO Filmes VALUES ('Addams Family' , 1991, 102, true, 'Paramount' , 'Barry Sonnenfeld');
INSERT INTO Filmes VALUES ('King Kong' , 1933, NULL, false, 'Universal' , 'Merian Cooper');
INSERT INTO Filmes VALUES ('King Kong' , 1976, 130, true, 'Universal' , 'John Guillermin');
INSERT INTO Filmes VALUES ('Gone With the Wind' , 1939, 181, false, 'Paramount' , 'Victor Fleming');
INSERT INTO Filmes VALUES ('Basic Instinct' , 1986, 100, true, 'Paramount' , 'Paul Verhoeven');
INSERT INTO Filmes VALUES ('Total Recall' , 1990, 110, true, 'Fox' , 'Paul Verhoeven');
INSERT INTO Filmes VALUES ('Indiana Jones' , 1981, 130, true, 'Universal' , 'Steven Spielberg');
INSERT INTO Filmes VALUES ('The Fugitive' , 1993, 120, true, 'Universal' , 'Andrew Davies');
INSERT INTO Filmes VALUES ('Bridges of Madison County' , 1995, 147, true, 'Universal' , 'Clint Eastwood');
INSERT INTO Filmes VALUES ('Batman Returns' , 1992, 122, true, 'Warner Brothers', 'Tim Burton');
INSERT INTO Filmes VALUES ('Top Gun' , 1986, 145, true, 'Fox', 'Tony Scott');
INSERT INTO Filmes VALUES ('Eyes Wide Shut' , 1999, 148, true, 'Paramount', 'Stanley Kubrik');
INSERT INTO Filmes VALUES ('Moulin Rouge' , 2001, 124, true, 'Fox', 'Baz Luhrmann');
INSERT INTO Filmes VALUES ('JFK' , 1996, 130, true, 'Fox', 'Oliver Stone' );
INSERT INTO Filmes VALUES ('Dances with Wolves' , 1990, 132, true, 'Fox', 'Kevin Costner' );
INSERT INTO Actores VALUES ('Carrie Fisher' , '123 Maple St., Hollywood' ,'f', '1955-11-07');
INSERT INTO Actores VALUES ('Mark Hamill' , '456 Oak Rd., Brentwood' ,'m', '1950-02-23');
INSERT INTO Actores VALUES ('Harrison Ford' , '789 Palm Dr., Beverly Hills' ,'m', '1944-07-13');
INSERT INTO Actores VALUES ('Emilio Estevez' , '201 California St., Mountain View' ,'m', '1960-03-22');
INSERT INTO Actores VALUES ('Dana Carvey' , '700 El Camino, Beverly Hills' ,'f', '1944-04-04');
INSERT INTO Actores VALUES ('Mike Meyers' , '880 Elm St., Santa Barbara' ,'m', '1935-06-04');
INSERT INTO Actores VALUES ('Sharon Stone' , '550 Elm St., Santa Barbara' ,'f', '1970-05-14');
INSERT INTO Actores VALUES ('Tom Cruise' , '26 Palm Dr., Hollywood' ,'m', '1967-08-20');
INSERT INTO Actores VALUES ('Arnold Schwarzenegger' , '83 Lincoln Av., Las Vegas' ,'m', '1967-08-19');
INSERT INTO Actores VALUES ('Kim Basinger' , '101 Sprinfield, Santa Monica' ,'f', '1967-08-12');
INSERT INTO Actores VALUES ('Alec Baldwin' , '101 Sprinfield, Santa Monica' ,'m', '1964-01-26');
INSERT INTO Actores VALUES ('Michael Keaton' , '32 Palm Beach, Santa Monica' ,'m', '1958-03-02');
INSERT INTO Actores VALUES ('Danny DeVito' , '18 Rodeo Dr., Beverly Hills' ,'m', '1946-04-08');
INSERT INTO Actores VALUES ('Michelle Pfeiffer' , 'Via Venetto 4, Hollywood' ,'f', '1969-11-14');
INSERT INTO Actores VALUES ('Nicole Kidman' , '26 Palm Dr., Hollywood' ,'f', '1969-11-14');
INSERT INTO Actores VALUES ('Meryl Streep' , 'Meryl House, Beverly Hills' ,'f', '1961-06-21');
INSERT INTO Actores VALUES ('Kevin Costner' , '88 Palm Dr., Hollywood' ,'m', '1965-01-30');
INSERT INTO Actores VALUES ('Clint Eastwood' , '12 Presley Dr., Beverly Hills' ,'m', '1946-01-25');
INSERT INTO Participa VALUES ('Star Wars', 1977, 'Carrie Fisher');
INSERT INTO Participa VALUES ('Star Wars', 1977, 'Mark Hamill');
INSERT INTO Participa VALUES ('Star Wars', 1977, 'Harrison Ford');
INSERT INTO Participa VALUES ('Mighty Ducks', 1991, 'Emilio Estevez');
INSERT INTO Participa VALUES ('Wayne''s World', 1992, 'Dana Carvey');
INSERT INTO Participa VALUES ('Wayne''s World', 1992, 'Mike Meyers');
INSERT INTO Participa VALUES ('Empire Strikes Back', 1980, 'Carrie Fisher');
INSERT INTO Participa VALUES ('Return of the Jedi', 1983, 'Carrie Fisher');
INSERT INTO Participa VALUES ('Basic Instinct', 1986, 'Sharon Stone');
INSERT INTO Participa VALUES ('Total Recall', 1990, 'Arnold Schwarzenegger');
INSERT INTO Participa VALUES ('Total Recall', 1990, 'Sharon Stone');
INSERT INTO Participa VALUES ('The Fugitive', 1993, 'Harrison Ford');
INSERT INTO Participa VALUES ('Indiana Jones', 1981, 'Harrison Ford');
INSERT INTO Participa VALUES ('Batman Returns', 1992, 'Michael Keaton');
INSERT INTO Participa VALUES ('Batman Returns', 1992, 'Danny DeVito');
INSERT INTO Participa VALUES ('Batman Returns', 1992, 'Michelle Pfeiffer');
INSERT INTO Participa VALUES ('Eyes Wide Shut', 1999, 'Nicole Kidman');
INSERT INTO Participa VALUES ('Eyes Wide Shut', 1999, 'Tom Cruise');
INSERT INTO Participa VALUES ('Top Gun', 1986, 'Tom Cruise');
INSERT INTO Participa VALUES ('Moulin Rouge', 2001, 'Nicole Kidman');
INSERT INTO Participa VALUES ('Dances with Wolves', 1990, 'Kevin Costner');
INSERT INTO Participa VALUES ('JFK', 1996, 'Kevin Costner');
INSERT INTO Participa VALUES ('Bridges of Madison County', 1995, 'Clint Eastwood');
INSERT INTO Participa VALUES ('Bridges of Madison County', 1995, 'Meryl Streep');
-- SELECT-FROM-WHERE
-- SELECT é equivalente ao operador 'Projecção' da Álgebra Relacional
-- WHERE é equivalente ao operador 'Selecção' da Álgebra Relacional
SELECT nome
FROM Filmes
WHERE ano = 1992;
-- Operadores: = <> < > <= >=
-- Strings aparecem entre plicas
SELECT nome, ano, duracao
FROM Filmes
WHERE nomeEstudio<>'Disney';
-- WHERE pode ter operadores lógicos (AND,OR,NOT)
SELECT nome,ano
FROM Filmes
WHERE nomeEstudio = 'Disney' AND ano > 1995;
-- WHERE é opcional
SELECT nome, ano
FROM Filmes;
-- pode-se atribuir nomes temporários a elementos da cláusula SELECT
SELECT nome AS "nome do filme"
FROM Filmes
WHERE nomeEstudio = 'Disney';
-- pode-se usar expressões com atributos
SELECT nome, ano, duracao/60.0 AS "duracao em horas"
FROM Filmes
WHERE nomeEstudio = 'Disney';
-- NOTA: acho que em SQL standard, "duracao em horas" deveria
-- aparecer entre plicas e não entre aspas, mas parece
-- que o PostgreSQL nao engole as plicas.
-- * significa todos os atributos
SELECT *
FROM Filmes
WHERE nomeEstudio = 'Disney';
-- LIKE faz pattern matching em strings
-- caracter % significa 0 ou mais caracteres
-- caracter _ significa 1 caracter
-- Quais os actores que moram em Hollywood?
SELECT nome
FROM Actores
WHERE morada LIKE '%Hollywood%';
-- Também se pode usar NOT LIKE
-- Quais os actorers que não moram em Hollywood?
SELECT nome
FROM Actores
WHERE morada NOT LIKE '%Hollywood%';
-- Ordenação do output
-- ORDER BY <lista de atributos>
-- Filmes da Disney ordenados por ano, e dentro de cada ano,
-- ordenados por nome.
SELECT *
FROM Filmes
WHERE nomeEstudio='Disney'
ORDER BY ano, nome;
-- Por defeito, a ordenação é por ordem crescente
-- Se se quiser por ordem decrescente, tem de se colocar DESC.
-- A ordenação de strings é feita pela ordem lexicográfica.
-- Filmes da Disney ordenados por ordem decrescente de duração,
-- e em caso de empate, ordenados por ordem crescente de nome.
SELECT *
FROM Filmes
WHERE nomeEstudio='Disney'
ORDER BY duracao DESC, nome;
-- Qual o nome, ano, e duração de todos os filmes do Harrison Ford?
SELECT nome, ano, duracao
FROM Filmes, Participa
WHERE nome = nomeFilme
AND ano = anoFilme
AND nomeActor = 'Harrison Ford';
-- Se houver conflito nos nomes dos atributos, deve usar-se
-- NomeTabela.NomeAtributo.
-- Por vezes, necessitamos de fazer um join com a própria tabela
-- Exemplo: Quais os actores que têm a mesma morada?
SELECT a1.nome, a2.nome
FROM Actores AS a1, Actores AS a2
WHERE a1.morada = a2.morada
AND a1.nome < a2.nome;
-- Podem pensar em a1 e a2 como se fossem cópias da tabela de Actores
-- (embora na prática não é isso que o SGBD faz). a1 e a2 costumam
-- ser designadas por "tuple variables" ou por "range variables".
-- União, Intersecção, e Diferença de queries
-- UNION, INTERSECT, EXCEPT
-- Quais os filmes do Tom Cruise que foram produzidos pelo estúdio Fox?
(
SELECT nome, ano
FROM Filmes
WHERE nomeEstudio = 'Fox'
)
INTERSECT
(
SELECT nomeFilme, anoFilme
FROM Participa
WHERE nomeActor = 'Tom Cruise'
);
-- O resultado de UNION, INTERSECT, e EXCEPT, é sempre um conjunto (não há repetidos).
-- Se quisermos ter repetidos temos de utilizar UNION ALL, INTERSECT ALL, EXCEPT ALL.
-- As outras operações em SQL utilizam o 'bag-model' (pode haver repetidos).
-- Porquê? Apenas por questões de eficiência de implementação.
-- Pode-se forçar o 'set-model' usando SELECT DISTINCT
-- Quais os estúdios que produzem filmes a cores? (bag-model)
SELECT nomeEstudio
FROM Filmes
WHERE aCores = true;
-- Quais os estúdios que produzem filmes a cores? (set-model)
SELECT DISTINCT nomeEstudio
FROM Filmes
WHERE aCores = true;
-- Valor NULL: valor desconhecido ou inaplicável
-- Dá origem a um comportamento estranho em SQL.
-- Lógica a 3 valores: True, False, e Unknown.
-- NULL + qualquer coisa = NULL (o mesme para '-', '*', '/').
-- NULL comparado com qualquer coisa tem o valor lógico UNKNOWN.
-- True=1 False=0 Unknown=1/2
-- a AND b = min(a,b)
-- a OR b = max(a,b)
-- NOT a = 1-a
--
-- Os problemas que derivam por causa dos valores NULL provocam alguma
-- polémica entre diversos autores. Alguns deles são da opinião de que
-- se deve evitar ao máximo a utilização dos valores NULL. Tal pode ser
-- feito usando restrições (ver mais matéria mais adiante).
SELECT *
FROM Filmes
WHERE duracao <= 120 OR duracao > 120;
-- A query acima não devolve todos os filmes. Porquê?
-- Para verificar se x tem o valor NULL, deve-se utilizar: x IS NULL
SELECT *
FROM Filmes
WHERE duracao IS NULL;
-- Também se pode usar IS NOT NULL
SELECT *
FROM Filmes
WHERE duracao IS NOT NULL;
-- Podemos sempre fazer joins usando a forma SELECT-FROM-WHERE
-- mas também se pode fazer utilizando expressões de JOIN explicitas.
-- Produto cartesiano
SELECT *
FROM Filmes, Estudios;
-- mas pode ser feito assim:
SELECT *
FROM Filmes CROSS JOIN Estudios;
-- Theta Join
-- pode ser feito assim:
SELECT *
FROM Filmes, Participa
WHERE nome = nomeFilme
AND ano = anoFilme;
-- mas pode ser feito assim:
SELECT *
FROM Filmes JOIN Participa ON
nome = nomeFilme AND ano = anoFilme;
-- Natural Join
SELECT *
FROM Actores NATURAL JOIN Realizadores;
-- Outer Joins
-- adiciona ao output os tuplos que não conseguem fazer "join"
-- Esses tuplos ficam com NULL nos restantes atributos.
SELECT *
FROM Actores NATURAL FULL OUTER JOIN Realizadores;
SELECT *
FROM Filmes FULL OUTER JOIN Participa ON
nome = nomeFilme AND ano = anoFilme;
-- além de FULL, pode ter-se LEFT ou RIGHT.
-- LEFT junta apenas os tuplos da primeira tabela,
-- RIGHT junta apenas os tuplos da segunda tabela.
-- Exemplos:
SELECT *
FROM Actores NATURAL LEFT OUTER JOIN Realizadores;
SELECT *
FROM Actores NATURAL RIGHT OUTER JOIN Realizadores;
SELECT *
FROM Filmes LEFT OUTER JOIN Participa ON
nome = nomeFilme AND ano = anoFilme;
SELECT *
FROM Filmes RIGHT OUTER JOIN Participa ON
nome = nomeFilme AND ano = anoFilme;
-- SUM, MIN, MAX, AVG aplicam-se a atributos.
-- COUNT aplica-se atributos e tuplos.
-- Qual a duração média dos filmes da Disney ?
SELECT AVG(duracao)
FROM Filmes
WHERE nomeEstudio = 'Disney';
-- Quantos actores existem na tabela de Actores?
SELECT COUNT(*)
FROM Actores;
-- Agrupar
-- Quantos filmes é que cada estúdio produz?
SELECT nomeEstudio AS "estudio", COUNT(*) AS "num filmes"
FROM Filmes
GROUP BY nomeEstudio;
-- Quantos filmes é que cada actriz fez?
SELECT nomeActor AS "actor", COUNT(*) AS "filmes"
FROM Participa, Actores
WHERE nomeActor = nome
AND sexo='f'
GROUP BY nomeActor;
-- Quais as actrizes que fizeram mais do que 1 filme?
SELECT nomeActor AS "actor", COUNT(*) AS "filmes"
FROM Participa, Actores
WHERE nomeActor = nome
AND sexo='f'
GROUP BY nomeActor
HAVING COUNT(*) > 1;
-- Para cada estúdio, qual é a duração máxima e mínima dos seus filmes?
SELECT nomeEstudio AS estudio, MAX(duracao), MIN(duracao)
FROM Filmes
GROUP BY nomeEstudio;
-- Pode-se ter SELECT's dentro da clausula WHERE, FROM, e HAVING.
-- Qual a morada do estúdio que fez o filme do Star Wars de 1977?
SELECT morada
FROM Estudios
WHERE nome = ( SELECT nomeEstudio
FROM Filmes
WHERE nome = 'Star Wars'
AND ano = 1977
);
-- no exemplo acima, repara na "scoping rule" de 'nome',
-- o primeiro 'nome' é o nome do estudio, o segundo 'nome' é o nome do filme.
-- subquerie (SELECT nomeEstudio ...) produz como output 1 só tuplo.
-- Porquê? porque (nome,ano) é chave de Filmes.
-- O resultado da subquerie é uma relação. Mas neste caso temos a certeza
-- de que é uma relação/tabela com 1 só linha e 1 só coluna. Por isso, podemos
-- usar o seu resultado na comparação tal e qual como se fosse um valor constante.
-- A query acima também podia ter sido feita sem subquerie.
SELECT morada
FROM Estudios, Filmes
WHERE Estudios.nome = Filmes.nomeEstudio
AND Filmes.nome = 'Star Wars'
AND Filmes.ano = 1977;
-- e se o resultado da subquerie tiver mais do que 1 tuplo?
-- SQL tem 4 operadores: IN, EXISTS e as palavras reservadas ALL, ANY
-- combinadas com os operadores relacionais '=', '>', '>=', ...
-- Qual a morada dos estúdios que já produziram filmes a preto e branco
SELECT morada
FROM Estudios
WHERE nome IN
(SELECT nomeEstudio
FROM Filmes
WHERE aCores=FALSE
);
-- IN é equivalente a 'pertence'. Além de poder ser aplicado a escalares, o
-- operador IN também pode ser aplicado a tuplos. Ex:
-- Quais são os estúdios que já produziram filmes do Harrison Ford?
-- Primeiro vemos os filmes do Harrison Ford:
-- SELECT nomeFilme, anoFilme
-- FROM Participa
-- WHERE nomeActor = 'Harrison Ford'
-- O resultado é uma relação com 2 atributos e poderá ter vários tuplos.
-- Depois temos de ver os estúdios que produziram esses filmes.
SELECT nomeEstudio
FROM Filmes
WHERE (nome,ano) IN
(SELECT nomeFilme, anoFilme
FROM Participa
WHERE nomeActor = 'Harrison Ford'
);
-- também se pode utilizar NOT IN ('não pertence').
-- ALL --> todos
-- ANY --> pelo menos um
-- Qual o filme com a maior duração?
-- (NOTA: há que ter cuidado com os NULLs. Porquê?
-- porquê? porque NULL comparado com qualquer coisa dá UNKNOWN)
SELECT nome, ano
FROM Filmes
WHERE duracao >= ALL
(SELECT duracao
FROM Filmes
WHERE duracao IS NOT NULL
);
-- Quais os filmes que não têm a maior duração?
SELECT nome, ano
FROM Filmes
WHERE NOT duracao >= ALL
(SELECT duracao
FROM Filmes
WHERE duracao IS NOT NULL
);
-- ou ...
SELECT nome, ano
FROM Filmes
WHERE duracao < ANY
(SELECT duracao
FROM Filmes
WHERE duracao IS NOT NULL
);
-- EXISTS(subquery) é true sse o resultado da subquery for uma
-- relação não for vazia.
-- Exemplo: Quais os estúdios que produziram mais do que 1 filme ?
SELECT DISTINCT nomeEstudio
FROM Filmes AS f
WHERE EXISTS
(SELECT *
FROM Filmes
WHERE nomeEstudio = f.nomeEstudio
AND NOT (ano = f.ano AND nome = f.nome)
);
-- a query acima é uma "correlated subquery". O operador EXISTS
-- e quase sempre usado com uma correlated subquery.
-- Neste exemplo concreto teria sido mais facil usar o GROUP BY em
-- vez da subquery. Ficaria assim:
SELECT nomeEstudio AS "estudio"
FROM Filmes
GROUP BY nomeEstudio
HAVING COUNT(*) > 1;
-- Qual o actor que fez mais filmes?
SELECT nomeActor
FROM Participa
GROUP BY nomeActor
HAVING COUNT(*) >= ALL
(SELECT COUNT(*)
FROM Participa
GROUP BY nomeActor
);
-- Inserçao de dados
-- INSERT INTO <tabela> VALUES (...);
INSERT INTO Actores VALUES ('Jack Nicholson', '28 Elm St, Beverly Hills', 'm','1940-8-10');
-- Por defeito, a ordem dos atributos é igual à ordem especificada durante
-- a criação da tabela, mas também podemos especificar os nomes dos atributos.
INSERT INTO Actores(nome,morada,sexo,dataNascimento) VALUES ('Jack Nicholson', '28 Elm St, Beverly Hills', 'm','1940-8-10');
-- Podemos não introduzir todos os atributos. Nesse caso, o valor fica
-- NULL (ou outro valor que esteja especificado como defeito).
-- No exemplo que se segue, morada e dataNascimento ficam com NULL.
INSERT INTO Actores(nome,sexo) VALUES ('Jack Nicholson', 'm');
-- Podemos inserir vários tuplos de uma vez usando uma subquery.
-- Exemplo: Introduzir na tabela de Estudios, todos os estúdios que
-- são mencionados na tabela de Filmes.
INSERT INTO Estudios(nome)
SELECT DISTINCT nomeEstudio
FROM Filmes
WHERE nomeEstudio NOT IN
(SELECT nome
FROM Estudios
);
-- NOTA: no exemplo acima, não insere nenhum tuplo porque 'nomeEstudio'
-- é uma foreign key (ver restrições mais adiante). Este 'insert'
-- só teria utilidade se não tivessemos especificado 'nomeEstudio'
-- como sendo uma foreign key. (de qualquer modo, coloquei este
-- exemplo apenas para ilustrar a inserção de tuplos usando
-- subqueries.
-- Updates
-- UPDATE <tabela> SET <atribuição de novos valores> WHERE <condição>;
-- Exemplo: Passar todos os filmes anteriores a 1970 para preto e branco
UPDATE Filmes
SET aCores = FALSE
WHERE ano < 1970;
-- Deletes
-- DELETE FROM <tabela> WHERE <condição>;
-- Exemplo: Apagar todos os filmes da Disney
DELETE FROM Filmes
WHERE nomeEstudio = 'Disney';
-- DELETE FROM <tabela>;
-- Apaga todos os tuplos da tabela, mas não apaga a estrutura.
-- A tabela continua a existir mas terá zero tuplos. Se se
-- quiser apagar a tabela toda deve usar-se DROP TABLE <tabela>
DROP TABLE Filmes;
-- CREATE TABLE ( ... )
-- Exemplo: Tabela de filmes.
CREATE TABLE Filmes(
nome varchar(50),
ano integer,
duracao integer,
aCores boolean,
nomeEstudio varchar(30)
);
-- Pode-se adicionar/remover colunas utilizando o comando
-- ALTER TABLE <tabela> [ADD|DROP] <coluna>
-- Adicionar o atributo 'realizador' à tabela de filmes.
ALTER TABLE Filmes ADD realizador VARCHAR(30);
-- O valor do atributo realizador para os tuplos já existentes
-- fica com o valor NULL (ou outro valor por defeito que seja
-- especificado --- ver mais adiante)
-- Eliminar a coluna 'aCores' (NOTA: ainda não está implementado pelo PostgreSQL).
ALTER TABLE Filmes DROP aCores;
-- Para apagar a tabela toda utiliza-se DROP TABLE <tabela>
DROP TABLE Filmes;
-- Valores por defeito.
-- Podemos especificar valores por defeito (em vez de NULL).
-- Exemplo: queremos que por defeito os filmes sejam a cores.
CREATE TABLE Filmes(
nome varchar(50),
ano integer DEFAULT 1900,
duracao integer DEFAULT -1,
aCores boolean DEFAULT TRUE,
nomeEstudio varchar(30) DEFAULT 'estudio desconhecido'
);
-- Domínios
-- NOTA: não está implementado em PostgreSQL (mas vai estar
na versão 7.2)
-- CREATE DOMAIN <nome> AS <descrição de um tipo de dados>;
CREATE DOMAIN DominioSexo CHAR
CHECK (VALUE IN ('F','M'));
Depois pode-se definir atributos do tipo 'DominioSexo'.
CREATE TABLE Actores(
...
sexo DominioSexo,
...
);
-- Cada objecto da base de dados tem um dono (que é o seu criador).
-- Por exemplo, se o utilizador a22222 criar a tabela de filmes,
-- apenas o a22222 pode aceder e modificar os dados dessa
-- tabela. De modo a poder dar permissões a outros utilizadores,
-- o SQL oferece um esquema de permissões. Para tal é necessário
-- utilizar a palavra reservada GRANT. Exemplos:
--
-- Dar permissão ao utilizador a33333 para fazer SELECTs na tabela
-- de filmes.
GRANT SELECT ON Filmes TO a33333;
-- Dar permissão ao utilizador a33333 para fazer SELECTs, e UPDATEs
-- à tabela de filmes.
GRANT SELECT,UPDATE ON Filmes TO a33333;
-- Dar todas as permissões (SELECT, DELETE, e UPDATE) ao utilizador
-- a33333 para aceder/modificar a tabela de filmes.
GRANT ALL ON Filmes TO a44444;
-- Também se pode dar permissões a vários utilizadores.
GRANT SELECT ON Filmes TO a33333, a44444, a55555;
-- Pode-se dar permissões a um grupo de utilizadores.
-- Supondo que existe um grupo na base de dados chamado grupo8,
-- poderiamos fazer o seguinte:
GRANT SELECT ON Filmes TO grupo8;
-- Também se pode dar permissões a todos os utilizadores. Nesse
-- caso usa-se a palavra PUBLIC.
GRANT SELECT ON Filmes TO PUBLIC;
-- Chave primária (PRIMARY KEY)
-- * Um conjunto de atributos cujo valor tem de ser único.
-- * Uma chave primária não pode ter valores NULL.
-- UNIQUE
-- * outra maneira de especificar chaves.
-- Diferenças para PRIMARY KEY:
-- (1) UNIQUE permite ter valores NULL.
-- (2) Uma tabela só pode ter uma PRIMARY KEY, mas pode
-- ter várias restrições UNIQUE.
CREATE TABLE Alunos (
numero INTEGER PRIMARY KEY,
BI VARCHAR(10) UNIQUE,
morada VARCHAR(100)
nome VARCHAR(50)
);
-- também se pode escrever:
CREATE TABLE Alunos (
numero INTEGER,
BI VARCHAR(10),
nome VARCHAR(50),
morada VARCHAR(100),
PRIMARY KEY(numero),
UNIQUE(BI)
);
-- NOT NULL
-- Podemos forçar um atributo a não poder ter o valor NULL
-- (é boa prática fazer isso).
CREATE TABLE Alunos (
numero INTEGER,
BI VARCHAR(10),
nome VARCHAR(50) NOT NULL,
morada VARCHAR(100),
PRIMARY KEY(numero),
UNIQUE(BI)
);
-- Integridade Referencial --> chaves estrangeiras.
-- Utiliza-se a palavra chave FOREIGN KEY.
CREATE TABLE Participa(
nomeFilme VARCHAR(50),
anoFilme INTEGER,
nomeActor VARCHAR(50),
PRIMARY KEY (nomeFilme,anoFilme,nomeActor),
FOREIGN KEY (nomeActor) REFERENCES Actores(nome),
FOREIGN KEY (nomeFilme,anoFilme) REFERENCES Filmes(nome,ano)
);
-- Uma chave estrangeira (foreign key) é um conjunto de atributos
-- que faz referência a um conjunto de atributos de uma outra tabela
-- (eventualmente até poderia ser a própria tabela). Os atributos
-- referenciados têm de ser uma chave primária da outra tabela,
-- (daí o nome "chave estrangeira").
--
-- no exemplo acima, {nomeActor} é uma chave estrangeira porque
-- se refere ao atributo 'nome', que é chave primaria da tabela de Actores.
-- Do mesmo modo, os atributos {nomeFilme,anoFilme} também são
-- uma chave estrangeira porque se referem aos atributos {nome,ano}
-- da tabela de Filmes.
-- As modificações à base de dados poderão violar estas restrições.
-- O que é que o SGBD faz nessa situação?
-- Existem 3 alternativas possiveis:
-- (1) não permite modificações (alternativa por defeito em SQL)
-- (2) SET NULL
-- (3) CASCADE --> propaga as alterações
-- As alternativas 2 e 3 ficam associadas a eventos de DELETE ou UPDATE.
CREATE TABLE Participa(
nomeFilme VARCHAR(50),
anoFilme INTEGER,
nomeActor VARCHAR(50),
PRIMARY KEY (nomeFilme,anoFilme,nomeActor),
FOREIGN KEY (nomeActor) REFERENCES Actores(nome)
ON UPDATE CASCADE
ON DELETE SET NULL,
FOREIGN KEY (nomeFilme,anoFilme) REFERENCES Filmes(nome,ano)
);
-- * Se actualizarmos o nome de um actor na tabela de 'Actores', essa
-- modificação é propagada para todos os tuplos da tabela 'Participa'
-- que se referenciam a esse actor.
-- * Se apagarmos um actor na tabela de 'Actores', o atributo 'nomeActor'
-- da tabela 'Participa' irá ficar com o valor NULL em todos os tuplos
-- que faziam referência a esse actor.
-- Checks em atributos.
-- o check apenas é verificado quando existir algum insert ou update
-- no atributo respectivo.
--
-- Exemplo: sexo tem de ser 'F' ou 'M'.
CREATE TABLE Actores(
...
sexo CHAR CHECK (sexo IN ('F','M')),
...
);
-- Checks em tuplos
-- são verificados cada vez que há um insert ou um update
-- de um tuplo. Se a condição do check for falsa, o insert
-- ou update do respectivo tuplo é rejeitado.
--
-- Exemplo: os filmes anteriores a 1939 não podem ser a cores.
CREATE TABLE Filmes(
...
CHECK (NOT (duracao<1939 AND aCores=TRUE))
);
-- Asserções (não estão implementadas no Postgres)
-- são restrições mais gerais e que se podem aplicar a várias tabelas.
--
-- Exemplo: não pode haver realizadores que também sejam actores
-- (este exemplo é obviamente ficticio).
CREATE ASSERTION NaoHaActoresRealizadores CHECK
(NOT EXISTS
(SELECT nome FROM Actores
INTERSECT
SELECT nome FROM Realizadores
)
);
-- Outro exemplo:
-- A duração média de todos os filmes de um determinado estudio
-- não pode exceder 200 minutos.
CREATE ASSERTION DuracaoMedia CHECK
(200 >= ALL
(SELECT AVG(duracao)
FROM Filmes
GROUP BY nomeEstudio
)
);
-- Neste último exemplo, se fizessemos apenas um CHECK ao nível da
-- tabela de filmes (e não uma asserção), a restrição poderia falhar
-- quando houvesse deletes, visto que as restrições ao nível da tabela
-- apenas são testadas em inserts e updates.
-- Indíces
--
-- Os indíces são estruturas de dados (e.g. b-trees)
-- que são utilizadas para acelerar as pesquisas.
-- Por defeito, o PostgreSQL cria indices para os atributos chave.
-- Os índices aceleram as pesquisas mas tornam mais lentos os inserts,
-- deletes, e updates (porque para além de modificar tabelas, também
-- têm de se modificar as estruturas de dados).
--
-- Vamos supor que têm de procurar muitas vezes por filmes cuja duração
-- seja maior que x minutos. Se não tiverem um índice, o SGBD terá de
-- fazer uma pesquisa sequencial por todos os tuplos da tabela.
-- Pelo contrário, se tiverem um índice para o atributo 'duracao',
-- o SGBD poderá utilizar um método de pesquisa mais sofisticado.
-- Para se criar um índice no atributo 'duracao' da tabela 'Filmes'
-- devem fazer:
CREATE INDEX IndexDuracao ON Filmes(duracao);
-- Para apagar o índice:
DROP INDEX IndexDuracao;
-- Views
-- =====
--
-- Uma View representa uma tabela que não existe fisicamente na
-- base de dados. Por isso, também se costuma designar por tabela
-- virtual.
-- CREATE VIEW <nome> AS <query>;
-- Uma view que dá o nome, ano, e duracao de todos os filmes da Disney.
CREATE VIEW filmes_disney (nome,ano,realizador) AS
SELECT nome, ano, nomeRealizador
FROM Filmes
WHERE nomeEstudio = 'Disney';
-- Agora, pode-se fazer queries à view 'filmes_disney' tal e qual
-- como se filmes_disney fosse uma tabela.
-- Quais os filmes da Disney feitos na década de 90?
SELECT *
FROM filmes_disney
WHERE ano>=1990 AND ano<=2000;
-- Outro exemplo:
CREATE VIEW filmes_actores (filme,ano,estudio,realizador,actor) AS
SELECT F.nome, F.ano, F.nomeEstudio, F.nomeRealizador, P.nomeActor
FROM Filmes AS F, Participa AS P
WHERE F.nome = P.nomeFilme
AND F.ano = P.anoFilme;
-- Quais os estúdios que já trabalharam com a Nicole Kidman?
SELECT estudio
FROM filmes_actores
WHERE actor = 'Nicole Kidman';
-- As rules (regras em português) não faz parte do SQL standard, mas são
-- implementadas pelo Postgres e dão muito jeito (não sei se já fazem
-- parte do standard SQL3).
--
-- As "rules" permitem especificar uma acção alternativa
-- que é executada em selects, inserts, updates, ou deletes.
--
-- sintaxe
--
-- CREATE RULE rule_name AS ON event
-- TO object [WHERE rule_qualification]
-- DO [INSTEAD] [action | (actions) | NOTHING];
--
-- Quando o evento e um insert, update, ou delete, podemos fazer
-- referência a duas pseudo-tabelas, NEW e OLD, que se referem aos
-- novos tuplos (no caso de inserts e updates) e aos velhos tuplos
-- (no cado de deletes e updates).
--
-- Exemplo: a seguinte regra não permite apagar filmes da Disney.
CREATE RULE nao_apaga_filmes_disney AS ON DELETE
TO Filmes
WHERE OLD.nomeEstudio = 'Disney'
DO INSTEAD NOTHING;
-- em postgres, as views são implementadas com rules.
-- em postgres, as views são read-only, mas podemos simular a "escrita"
-- em views utilizando rules.
--
-- Exemplo: criar uma regra de modo a podermos inserir tuplos na view
-- filmes_disney.
CREATE RULE insere_filmes_disney AS ON INSERT
TO filmes_disney
DO INSTEAD
INSERT INTO Filmes (nome,ano,nomeRealizador,nomeEstudio)
VALUES (NEW.nome, NEW.ano, NEW.realizador, 'Disney');
-- agora já podemos inserir dados na view filmes_disney
INSERT INTO filmes_disney
VALUES ('The Huntchback of Notre Dame', 1995, 'Steven Spielberg');
-- Mais um exemplo: inserir dados na view 'filmes_actores'.
-- A definição desta view é,
CREATE VIEW filmes_actores (filme,ano,estudio,realizador,actor) AS
SELECT F.nome, F.ano, F.nomeEstudio, F.nomeRealizador, P.nomeActor
FROM Filmes AS F, Participa AS P
WHERE F.nome = P.nomeFilme
AND F.ano = P.anoFilme;
-- Vamos criar uma regra para podermos inserir dados nesta view.
-- A inserção de um tuplo na view, pode fazer com que tenhamos de inserir
-- tuplos na tabela 'Filmes', 'Estudio', 'Realizadores', 'Actores', e
-- 'Participa'.
CREATE RULE insere_em_filmes_actores AS ON INSERT
TO filmes_actores
DO INSTEAD
(INSERT INTO Filmes (nome,ano,nomeEstudio,nomeRealizador)
VALUES (NEW.filme, NEW.ano, NEW.estudio, NEW.realizador);
INSERT INTO Actores (nome) VALUES (NEW.actor);
INSERT INTO Estudios (nome) VALUES (NEW.estudio);
INSERT INTO Realizadores (nome) VALUES (NEW.realizador);
INSERT INTO Participa (nomeFilme,anoFilme,nomeActor)
VALUES (NEW.filme, NEW.ano, NEW.actor);
);
-- agora podemos inserir dados na view 'filmes_actores'. Por exemplo,
INSERT INTO filmes_actores VALUES ('The Apartment', 1950, 'Universal',
'Billy Wilder', 'Shirley MacLain');
INSERT INTO filmes_actores VALUES ('Taxi Driver', 1978, 'Universal',
'Martin Scorcese', 'Roxana Arquette');
-- Os inserts acima podem dar erro porque podem violar restrições
-- que hajam nas tabelas. Por exemplo, se o estudio 'Universal' já
-- existir na tabela de 'Estudios', o insert falha o nome do estúdio
-- está definido com chave primária. A solução para este problema é
-- criar regras adicionais de modo a evitar este tipo de problemas.
-- Por exemplo:
CREATE RULE nao_insere_filmes_duplicados AS ON INSERT
TO Filmes
WHERE( EXISTS( SELECT * FROM Filmes WHERE nome=NEW.nome AND ano=NEW.ano) )
DO INSTEAD NOTHING;
CREATE RULE nao_insere_actores_duplicados AS ON INSERT
TO Actores
WHERE( EXISTS( SELECT * FROM Actores WHERE nome=NEW.nome) )
DO INSTEAD NOTHING;
CREATE RULE nao_insere_estudios_duplicados AS ON INSERT
TO Estudios
WHERE( EXISTS( SELECT * FROM Estudios WHERE nome=NEW.nome) )
DO INSTEAD NOTHING;
CREATE RULE nao_insere_realizadores_duplicados AS ON INSERT
TO Actores
WHERE( EXISTS( SELECT * FROM Realizadores WHERE nome=NEW.nome) )
DO INSTEAD NOTHING;
-- Uma vez tendo estas regras, a inserção de dados nas views já corre
-- sem problemas. Repara que a inserção de dados na view "esconde" a
-- a inserção efectiva dos dados nas tabela base. Repara também que
-- ao inserir dados na view ficamos impossibilitados de inserir todos
-- os atributos das tabelas base. Esses atributos ficam com o valor
-- NULL (ou com o valor que estiver especificado como DEFAULT).
-- Podem consultar mais coisas na documentação online
-- (Programmer's Guide --> Server Programming --> The Postgres Rule System)
-- Os triggers não fazem parte do standard SQL-92, mas penso que
-- fazem parte do SQL3. Penso que a implementação dos triggers em
-- Postgres não é exactamente igual à que é proposta no standard,
-- mas é muito parecido. No que se segue, apresento os triggers
-- "a la PostgreSQL"
--
-- Os triggers são parecidos com as rules. A ideia é que podemos accionar
-- um procedimento aquando da ocorrência de determinados eventos.
-- Os eventos podem ser INSERT, UPDATE, DELETE, e o trigger pode
-- ser accionado imediatamente antes (BEFORE) ou imediatamente depois
-- (AFTER) de cada evento.
--
-- O procedimento pode ser programado numa linguagem qualquer
-- desde que devidamente instalada no servidor do SGBD.
-- No exemplo que se segue, utilizo PL/pgSQL, uma linguagem
-- procedimental muito parecida com a linguagem PL/SQL da ORACLE.
--
-- Exemplo: Pretende-se apagar um estúdio se não houver nenhum filme
-- que lhe faça referência. Para tal, devemos associar um trigger à
-- tabela de filmes. O trigger será accionado logo após um delete ou
-- update à tabela de filmes. Ao ser disparado, o trigger irá executar
-- um procedimento chamado 'apaga_estudio_sem_ref()'.
CREATE TRIGGER estudio_sem_ref
AFTER DELETE OR UPDATE ON Filmes
FOR EACH ROW EXECUTE PROCEDURE apaga_estudio_sem_ref();
-- O procedimento 'apaga_estudio_sem_ref()' está escrito em PL/pgSQL
-- (mas podia estar escrito noutra linguagem). Aquilo que o procedimento
-- faz é verificar se existe algum filme produzido pelo estúdio que acabou
-- de ser apagado (ou actualizado). Em caso negativo, o procedimento
-- apaga esse estúdio da tabela de estúdios.
CREATE FUNCTION apaga_estudio_sem_ref () RETURNS OPAQUE AS '
DECLARE
estudio VARCHAR(40);
BEGIN
estudio := OLD.nomeEstudio;
IF NOT EXISTS (SELECT * FROM Filmes
WHERE nomeEstudio = estudio)
THEN
DELETE FROM Estudios WHERE nome = estudio;
RAISE NOTICE ''Estudio % foi apagado.'', estudio;
END IF;
RETURN NULL;
END;
' LANGUAGE 'plpgsql';
-- Nota: Para funcionar como deve ser, devem definir o procedimento
-- antes de definir o trigger. Consultem a documentação online
-- do Postgres (Programmer's Guide --> Procedural Languages)