Laboratório Redes de Computadores

CREATE TABLE professor (
codigo integer primary key,
nome varchar(50)
);


CREATE TABLE turma (
codigo integer primary key,
numero integer,
nivel varchar(50),
cod_prof_responsavel integer,
CONSTRAINT fk_cod_prof FOREIGN KEY (cod_prof_responsavel)
REFERENCES professor (codigo)
);

CREATE TABLE aluno (
codigo integer primary key,
nome varchar(50),
idade integer,
cod_turma integer,
CONSTRAINT fk_cod_turma FOREIGN KEY (cod_turma)
REFERENCES turma (codigo)
);

ALTER TABLE aluno DROP COLUMN idade;

ALTER TABLE aluno ADD COLUMN dt_nascimento DATE;

ALTER TABLE professor ADD COLUMN email varchar(50);

INSERT INTO professor (codigo, nome, email)
VALUES (1, 'Caio', '[email protected]'),
(2, 'Davi', '[email protected]'),
(3, 'Renata', '[email protected]');

INSERT INTO turma (codigo, numero, nivel, cod_prof_responsavel)
VALUES (1,145, 'SEM 1',1),
(2,255, 'SEM 3',2),
(3,263, 'SEM 4',1);

ALTER TABLE turma ADD COLUMN nome_turma varchar(50);

SELECT * FROM aluno;
SELECT * FROM turma;
SELECT * FROM professor;

UPDATE turma SET nome_turma = 'Banco de dados';

UPDATE turma SET nome_turma = 'Programação de Computadores' WHERE codigo = 1;

UPDATE turma SET nome_turma = 'Programação Orientada a Objetos' WHERE cod_prof_responsavel = 2;

----------

INSERT INTO professor (codigo, nome, email)
VALUES
(4, 'João Souza', '[email protected]'),
(5, 'Mariana Silva', '[email protected]'),
(6, 'Carlos Mendes', '[email protected]');


INSERT INTO turma (codigo, numero, nivel, nome_turma, cod_prof_responsavel)
VALUES
(4, 101, 'Fundamental', 'Turma A', 4),
(5, 102, 'Médio', 'Turma B', 5);


UPDATE professor SET email = '[email protected]' WHERE nome = 'João Souza';

INSERT INTO aluno (codigo, nome, dt_nascimento, cod_turma)
VALUES
(1001, '1001', '2007-03-10', 4),
(1002, '1002', '2006-11-25', 4),
(2001, '2001', '2005-09-14', 5),
(2002, '2002', '2005-01-07', 5);

UPDATE aluno SET cod_turma = 5 WHERE codigo = 1002;

INSERT INTO turma (codigo, numero, nivel, nome_turma, cod_prof_responsavel)
VALUES
(6, 103, 'Fundamental', 'Turma C', 6);

UPDATE turma SET nome_turma = 'Turma Avançada B' WHERE numero = 102;

INSERT INTO aluno (codigo, nome, dt_nascimento, cod_turma)
VALUES
(3001, '3001', '2007-06-21', 6),
(3002, '3002', '2006-12-12', 6);

UPDATE turma SET cod_prof_responsavel = 6 WHERE nome_turma = 'Turma A';

INSERT INTO professor (codigo, nome, email)
VALUES (7, 'Ana Costa', '[email protected]');

UPDATE turma SET nome_turma = 'Turma Fundamental C Avançada', nivel = 'Fundamental Avançado' WHERE numero = 103;


SELECT * FROM aluno;
SELECT * FROM turma;
SELECT * FROM professor;

--- REMOVENDO REGISTROS DO BANCO DE DADOS

DELETE FROM turma WHERE codigo = 1;

--- removendo um registro de professor
DELETE FROM turma WHERE codigo = 3
DELETE FROM professor WHERE codigo = 1;

-- INSERIR NOVO ALUNO
INSERT INTO aluno (codigo, nome, cod_turma, dt_nascimento)
VALUES (1, 'Joao', 2, '2000-05-12');

-- REMOPVER A TURMA DE CODIGO 2
DELETE FROM turma WHERE codigo = 2




INICIO

--- CRIAR A TABELA DE FILMES

CREATE TABLE filmes (
codigo integer PRIMARY KEY,
titulo varchar(100),
duracao integer,
tipo varchar(100),
avaliacao integer
);

--- INSERIR FILMES NAS TABELAS

INSERT INTO filmes (codigo, titulo, duracao, tipo, avaliacao)
VALUES (1, 'O poderoso Chefão', 153, 'Policial', 0),
(2, 'A Lista de Schindler', 189, 'Histórico', 0),
(3, 'O Rei Leão', 78, 'Animação', 0),
(4, 'Vingadores: Utimato', 180, 'Fantasia/Ação', 0),
(5, 'De Volta para o Futuro', 96, 'Ficção Cientifica', 0);

--- EXUBE APENAS O TITULO E A DURAÇÃO

SELECT titulo, duracao FROM filmes

--- SELECIONAR OS FILMES COM DURAÇÃO MENOR QUE 100
SELECT * FROM filmes WHERE duracao < 100;

--- MOSTRE OS FILMES QUE POSSUEM CÓDIGOS DE 2 ATÉ 4
SELECT * FROM filmes WHERE codigo >= 2 and codigo <= 4;
SELECT * FROM filmes WHERE codigo BETWEEN 2 and 4

--- MOSTRE OS DADOS DO FILME "De Volta para o Futuro"
SELECT * FROM filmes WHERE titulo = 'De Volta para o Futuro';
SELECT * FROM filmes WHERE titulo LIKE 'De Volta%';

--- Ignora case sensitive
SELECT * FROM filmes WHERE tipo ILIKE 'Animação'

--- DEFINE A ORDEM EM QUE OS REGISTROS SERÃO EXIBIDOS. O PADRÃO É ASC (Ascendente)
SELECT * FROM filmes ORDER BY titulo ASC;

CREATE TABLE produtos (
codigo SERIAL PRIMARY KEY,
nome VARCHAR(50) NOT NULL,
valor NUMERIC(10,2) NOT NULL,
quantidade NUMERIC(10,2) NOT NULL
);

INSERT INTO produtos (nome, valor, quantidade)
VALUES ('borracha', 4.5, 2),
('caneta', 7.9, 3),
('caderno', 12.3, 4),
('sulfite', 24, 2),
('cola', 8.75, 1),
('régua', 6, 1);

SELECT * FROM produtos

-- CONTA O NÚMERO DE REGISTROS
SELECT COUNT (*) FROM produtos;

-- REMOVE A RESTRIÇÃO DE NOT NULL DA SOLUNA QUANTIDADE
ALTER TABLE produtos ALTER COLUMN quantidade DROP NOT NULL;

-- INSERIR MAIS UM REGISTRO
INSERT INTO produtos (nome, valor)
VALUES ('borracha', 4.5);

-- CONTA A QUANTIDADE DE DADOS DE UMA COLUNA
SELECT COUNT(nome) FROM produtos;
SELECT COUNT(quantidade) FROM produtos;

-- CONTA A QUANTIDADE DE DADOS DISTINTOS EM UMA COLUNA
SELECT COUNT(DISTINCT quantidade) FROM produtos;

-- FUNÇÃO SOMA VALORES
SELECT SUM(valor) FROM produtos;

-- EDITANDO A COLUNA DE RESULTADO / RENOMEAR UMA COLUNA
SELECT SUM(valor) as total FROM produtos;

-- função para selecionar o valor máximo de uma coluna
SELECT MAX(valor) AS valor_maximo FROM produtos;

-- função para selecionar o valor minimo de uma coluna
SELECT MIN(valor) AS valor_minimo FROM produtos;

-- FUNÇÃO PARA RETORNAR A MÉDIA DE VALORES DE UMA COLUNA
SELECT AVG(valor) AS media FROM produtos;

-- FORMATAR AS CASAS DECIMAIS USAR A FUNÇÃO ROUND
SELECT ROUND(AVG(valor), 2) AS media_formatada FROM produtos;

Script de Criação

Aqui estão os scripts SQL correspondentes às consultas solicitadas:

1. Liste todos os clientes cadastrados: 2. Liste os produtos disponíveis e seus preços: 3. Exiba todas as informações dos pedidos realizados:
4. Selecione os clientes que moram na cidade de "São Paulo": 5. Liste os produtos que custam mais de R$ 500,00:
6. Exiba os pedidos feitos após "2024-03-15": 7. Liste os clientes ordenados por nome em ordem alfabética:
8. Exiba os produtos ordenados pelo preço do mais caro para o mais barato:

9. Liste os clientes cujo nome começa com "A":
10. Selecione os clientes cujo e-mail termina com "@gmail.com":
11. Conte quantos clientes estão cadastrados:
12. Conte quantos pedidos foram realizados:

-- Atividade 1
SELECT * FROM clientes;

-- Atividade 2
SELECT nome, preco FROM produtos;

-- Atividade 3
SELECT * FROM pedidos;

-- Atividade 4
SELECT * FROM clientes WHERE cidade = 'São Paulo';

-- Atividade 5
SELECT * FROM produtos WHERE preco > 500;

-- Atividade 6
SELECT * FROM pedidos WHERE data_pedido > '2024-03-15';

-- Atividade 7
SELECT * FROM clientes ORDER BY nome;

-- Atividade 8
SELECT * FROM produtos ORDER BY preco DESC;

-- Atividade 9
SELECT * FROM clientes WHERE nome LIKE 'A%';

-- Atividade 10
SELECT * FROM clientes WHERE email LIKE '%@gmail.com';

-- Atividade 11
SELECT COUNT(*) FROM clientes;

-- Atividade 12
SELECT COUNT(*) FROM pedidos;