1 1. Criar o banco de dados Loja_CD CREATE

TADS – Exercício Laboratório I
Analise o modelo de banco de dados abaixo:
MUSICA_AUTOR
AUTOR
Codigo_Musica: INTEGER (FK)
Codigo_Autor: INTEGER (FK)
MUSICA
Codigo_Autor: INTEGER
Nome_Autor: VARCHAR(60)
Codigo_Musica: INTEGER
Nome_Musica: VARCHAR(60)
Duracao: NUMERIC(6,2)
CD
Codigo_CD: INTEGER
FAIXA
Codigo_Musica: INTEGER (FK)
Codigo_CD: INTEGER (FK)
Numero_Faixa: INTEGER
Codigo_Gravadora: INTEGER (FK)
Nome_CD: VARCHAR(60)
Preco_Venda: NUMERIC(14,2)
Data_Lancamento: DATETIME
CD_Indicado: INTEGER (FK)
GRAVADORA
Codigo_Gravadora: INTEGER
Nome_Gravadora: VARCHAR(60)
Endereco: VARCHAR(60)
Telefone: VARCHAR(20)
Contato: VARCHAR(20)
URL: VARCHAR(80)
Figura 1 - Modelo Físico - Sistema de Loja de CD
1.
Criar o banco de dados Loja_CD
CREATE DATABASE LOJA_CD
2. Excluir o banco de dados Loja_CD
DROP DATABASE LOJA_CD
3. Criar as tabelas do banco de dados
CREATE TABLE MUSICA (
Cod_Musica int not null primary key,
nome_Musica varchar (60) null,
Duracao numeric(6,2))
CREATE TABLE AUTOR (
cod_Autor int not null primary key,
nome_Autor varchar (60)
)
CREATE TABLE GRAVADORA (
Cod_Gravadora int not null primary key,
nome_gravadora varchar (60) null,
1
end_gravadora varchar (60) null,
tel_gravadora varchar (20) null,
contato varchar(20) null,
url_gravadora varchar (80))
CREATE TABLE CD (
cod_cd int not null primary key,
cod_gravadora int not null,
nome_CD varchar (60) null,
preco_venda numeric(14, 2),
data_lancamento datetime,
cd_indicado int,
foreign key (cod_gravadora) references gravadora (cod_gravadora),
foreign key (cd_indicado) references CD (cod_CD)
ON DELETE NO ACTION
ON UPDATE NO ACTION
)
CREATE TABLE FAIXA (
Cod_Musica int not null,
cod_cd int not null,
num_faixa int,
primary key (cod_musica, cod_cd),
foreign key (cod_musica) references musica (cod_musica),
foreign key (cod_cd) references CD (cod_cd)
)
/*Observações
SET NULL: atribui null para o campo da tabela filha quando excluido/alterado um registro
da tabela mãe.
No entanto, se for um campo que não aceito null será emitido um erro.
SET DEFAULT
CASCADE: quando ocorre o delete de um registro numa tabela pai os registros referentes
são excluídos das tabelas filhas
com o CASCADE.
2
NO ACTION: sem ação
RESTRICT: não permite a exclusão */
CREATE TABLE MUSICA_AUTOR (
Cod_Musica int not null,
cod_Autor int not null DEFAULT 29, -- O VALOR COLOCADO COMO DEFAULT DEVE EXISTIR
NA TABELA AUTOR
Primary key (cod_Musica, cod_Autor),
FOREIGN KEY (cod_Musica) REFERENCES Musica (cod_Musica)
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY (cod_Autor) REFERENCES Autor (cod_Autor)
ON DELETE SET DEFAULT
ON UPDATE SET DEFAULT
)
4. Exclua a tabela de Musica
DROP TABLE Musica;
5. Alterar a chave estrangeira
ALTER TABLE MUSICA_AUTOR
ADD CONSTRAINT FK_MUSICAAUTOR
FOREIGN KEY (cod_Musica) REFERENCES MUSICA (cod_Musica) ON DELETE SET NULL;
6. Alterar a estrutura da tabela de Musica_Autor
-- adiciona uma coluna na tabela MUSICA_AUTOR
--POSTGRESQL
ALTER TABLE MUSICA_AUTOR ADD COLUMN Cod_Status char(1) DEFAULT 'A';
--SQL SERVER
ALTER TABLE MUSICA_AUTOR ADD Cod_Status char(1) DEFAULT 'A';
7. Excluir uma coluna da tabela
ALTER TABLE MUSICA_AUTOR DROP COLUMN cod_status
8. Altera a estrutura de uma coluna
--Postgresql
ALTER TABLE MUSICA_AUTOR ALTER COLUMN cod_status SET NOT NULL;
3
--SQL Server
ALTER TABLE MUSICA_AUTOR ALTER COLUMN cod_status char(1) not NULL
9. Exclui restricao do campo da tabela
--postGreSQL (nao se aplica ao SQL Server)
ALTER TABLE MUSICA_AUTOR ALTER COLUMN cod_status DROP NOT NULL;
ALTER TABLE MUSICA_AUTOR ALTER COLUMN cod_status DROP DEFAULT;
10. Adiciona uma restricao de integridade (CHECK):
ALTER TABLE Musica_Autor ADD CONSTRAINT c1 CHECK (cod_status in ('A', 'I') );
11. Insere dados na tabela de musica/autor/musica_autor
INSERT INTO Musica (Cod_Musica, nome_Musica, Duracao) VALUES (1, 'Pense em mim', 1)
INSERT INTO Autor (cod_Autor, nome_autor) VALUES (10, 'Jorge e Mateus')
INSERT INTO Musica_Autor (cod_musica, cod_autor, cod_status) VALUES (1, 10, 'S')
INSERT INTO Musica_Autor (cod_musica, cod_autor, cod_status) VALUES (1, 10, 'A')
12. Excluir a restricao (CHECK)
ALTER TABLE Musica_Autor DROP CONSTRAINT c1;
13. Excluir coluna da tabela
--PostGreSQL
ALTER TABLE MUSICA_AUTOR ALTER COLUMN cod_status DROP COLUMN NOT NULL;
--SQL Server
ALTER TABLE MUSICA_AUTOR DROP COLUMN cod_status;
14. Altera o tipo de dado
--PostGreSQL
ALTER TABLE MUSICA_AUTOR ALTER COLUMN cod_status TYPE numeric(10,2);
--SQL Server
ALTER TABLE MUSICA_AUTOR ALTER COLUMN cod_status numeric(10,2)
15. Renomear coluna
--postgresql
ALTER TABLE MUSICA_AUTOR RENAME COLUMN cod_status TO cod_status_musica;
4
--SQL Server
Renomeia Coluna utilizando a stored procedure do sistema (SP_rename)
sp_rename 'MUSICA_AUTOR.cod_status', 'cod_status_novo', 'COLUMN'
16. Renomear tabela
--postgresql
ALTER TABLE MUSICA_AUTOR RENAME TO Musica_Autor_Teste
--SQL Server
--Renomeia Tabela utilizando a stored procedure do sistema (SP_rename)
sp_rename 'MUSICA_AUTOR', 'MUSICA_AUTOR_TAB'
17. Exclui/adiciona chave primaria
ALTER TABLE Musica_Autor DROP CONSTRAINT PK__MUSICA_AUTOR__1FCDBCEB -- ver o
nome da chave no BD
ALTER TABLE Musica_Autor ADD PRIMARY KEY (cod_musica,cod_autor);
18. Criando uma tabela a partir de dados de outra tabela
CREATE TABLE GRAVADORA_BKP (
Cod_Gravadora int not null primary key,
nome_gravadora varchar (60) null,
end_gravadora varchar (60) null,
tel_gravadora varchar (20) null,
contato varchar(20) null,
url_gravadora varchar (80))
19. Insere dados na gravadora
INSERT INTO gravadora (Cod_Gravadora, nome_gravadora, end_gravadora,
tel_gravadora,contato, url_gravadora)
VALUES (100, 'EMI', 'Rua do patriota, 260', '994252125', 'Mauro Dias', 'www.emi.com.br')
20. Consulta dados
Select * From gravadora
21. Insere dados da gravadora para gravadora_BKP
INSERT INTO GRAVADORA_BKP
5
SELECT Cod_Gravadora, nome_gravadora, end_gravadora, tel_gravadora, contato,
url_gravadora FROM GRAVADORA
22. Criar indice
CREATE INDEX IDXmusica
ON musica (nome_musica);
23. Exclui indice
DROP INDEX musica.IDXmusica;
24. Trabalhando com o comando SELECT
SELECT nome_musica, cod_musica
FROM Musica
WHERE duracao > 10
ORDER BY nome_musica;
SELECT * FROM autor;
SELECT DISTINCT nome_musica FROM musica;
SELECT DISTINCT cod_musica, nome_musica
FROM musica;
SELECT MAX (cod_gravadora) FROM gravadora
SELECT COUNT (cod_gravadora) from gravadora
SELECT MIN (cod_gravadora) from gravadora
SELECT AVG (cod_gravadora) from gravadora
SELECT SUM (cod_gravadora) from gravadora
25. Conectores logicos and, or, not
SELECT cod_CD
FROM CD where (cod_cd >100 and cod_cd <= 200)
26. Operador BETWEEN
6
SELECT Cod_CD
FROM CD
WHERE cod_CD BETWEEN 10 AND 50
27. Operadores IN, NOT IN
SELECT Cod_CD
FROM CD
WHERE cod_Cd IN (60,90,70)
28. Utilizando o comando LIKE
--nomes que começam com a letra A
SELECT nome_musica
FROM musica
WHERE nome_musica LIKE 'A%'
-- obtem o nome de cada musica que termina com a letra n
SELECT nome_musica
FROM musica
WHERE nome_musica LIKE '%N'
-- obtem o nome de cada musica em que o caractere e a penultima letra
SELECT nome_musica
FROM musica
WHERE nome_musica LIKE '%e_'
29. Obtem o numero de caracteres
--postgresql
select char_length ('Maria Sylvia')
--sql server
select len('Maria Sylvia')
30. upper (cadeia1) - retorna o argumento com letras maiusculas
SELECT UPPER(nome_musica)
FROM musica
31. lower (cadeia1) - retorna o argumento com letras minusculas
SELECT LOWER (nome_musica)
FROM musica
32. substring (cadeia1, start, comp) - extrai uma subcadeia da cadeia1, começando no start e
indo ate o comp.
select substring ('Ola Mundo!', 4, 7)
7
33. trim - remove espacos em branco
--postgresql
select trim(' professora ')
--sql server
select ltrim(rtrim(' professora '))
34. concatenacao
--postgresql
select cod_autor || ' ' || nome_autor
from autor
--sql server
select cast (cod_autor as varchar(5)) + ' ' + nome_autor
from autor
35. Retorna o resto de uma divisão
--postgresql
select mod (8,3)
--sql server
select 8%3
36. Conversão de tipos de dados
--postgresql
select cast(cod_autor as varchar(5)) || ' autor'
from autor
--sql server
select cast(cod_autor as varchar(5)) + ' autor'
from autor
37. IS NULL/IS NOT NULL
select * From gravadora
where contato is null
-- not null
select * From gravadora
where contato is not null
8