Aulas de banco de dados

aula23.sql

-- a) Criar as tabelas
CREATE TABLE Material (
	CodMat	INTEGER PRIMARY KEY,
	Nacionalidade VARCHAR(20),
	Nome VARCHAR(20)
);

CREATE TABLE Medico (
	CRM INTEGER PRIMARY KEY,
	CPF INTEGER,
	Telefone_Res VARCHAR(15),
	Telefone_Celular VARCHAR(15),
	Telefone_Consultorio VARCHAR(15),
	Nome VARCHAR(40),
	Salario FLOAT,
	Rua VARCHAR(30),
	Numero INTEGER,
	CEP INTEGER,
	Cidade VARCHAR(20),
	Estado VARCHAR(2)
);

CREATE TABLE Setor (
	Sigla VARCHAR(5) PRIMARY KEY,
	Nome VARCHAR(40),
	CRM INTEGER,
	Data_Inicio DATE,
	CONSTRAINT medfk FOREIGN KEY(CRM) REFERENCES Medico(CRM)
);

CREATE TABLE Trabalha (
	Sigla VARCHAR(5),
	CRM INTEGER,
	Especialidade VARCHAR(20),
	CONSTRAINT siglafk FOREIGN KEY(Sigla) REFERENCES Setor(Sigla),
	CONSTRAINT crmfk FOREIGN KEY(CRM) REFERENCES Medico(CRM),
	CONSTRAINT pk PRIMARY KEY(Sigla, CRM)
);

CREATE TABLE Procedimento (
	CodProc INTEGER PRIMARY KEY,
	Nome VARCHAR(40),
	Custo FLOAT,
	Sigla VARCHAR(5),
	CONSTRAINT siglafk FOREIGN KEY(Sigla) REFERENCES Setor(Sigla)
);

CREATE TABLE Utiliza (
	CodProc INTEGER,
	CodMat INTEGER,
	CONSTRAINT procfk FOREIGN KEY(CodProc) REFERENCES Procedimento(CodProc),
	CONSTRAINT matfk FOREIGN KEY(CodMat) REFERENCES Material(CodMat),
	CONSTRAINT pkutiliza PRIMARY KEY(CodProc, CodMat)
);

-- b) Explore os comandos de alteração da definição de tabelas vistos em aula usando a tabela MEDICO
ALTER TABLE Medico ALTER COLUMN Nome SET NOT NULL;
ALTER TABLE Medico ALTER COLUMN Estado SET DEFAULT 'MG';
ALTER TABLE Medico ALTER COLUMN Cidade SET DEFAULT 'UBERLANDIA';

-- c) Inserir pelo menos 5 tuplas em cada uma das tabelas
INSERT INTO Medico VALUES(1, 11, '3210-3910', '9120-2910', '3250-2931', 'ARNALDO', 140.5, 'ANTIGA 14', 123, 1234, 'UBERLANDIA', 'MG');
INSERT INTO Medico (CRM, Nome, Salario, CPF, Rua) VALUES(2, 'BERNALDO', 51.0, 22, 'VELHA RUA');
INSERT INTO Medico (CRM, Nome, Salario, CPF, Cidade) VALUES(3, 'CERNALDO', 348.0, 33, 'BELO HORIZONTE');
INSERT INTO Medico (CRM, Nome, Salario, CPF) VALUES(4, 'DERNALDO', 451.0, 44);
INSERT INTO Medico (CRM, Nome, Salario, CPF) VALUES(5, 'ERNALDO', 221.0, 55);
INSERT INTO Setor VALUES('FEMEC', 'Eng. Mecanica', 1, NULL);
INSERT INTO Setor (Sigla, Nome, CRM) VALUES('FEELT', 'Eng. Eletrica', 2);
INSERT INTO Setor (Sigla, Nome, CRM) VALUES('FAMAT', 'Matematica', 3);
INSERT INTO Setor (Sigla, Nome, CRM) VALUES('INFIS', 'Fisica', 1);
INSERT INTO Setor (Sigla, Nome, CRM) VALUES('FACOM', 'Fac. de Computacao', 2);
INSERT INTO Trabalha VALUES('FEMEC', 2, 'Prof');
INSERT INTO Trabalha VALUES('FAMAT', 1, 'Prof');
INSERT INTO Trabalha VALUES('FACOM', 5, 'Prof');
INSERT INTO Trabalha VALUES('FACOM', 4, 'Prof');
INSERT INTO Trabalha VALUES('INFIS', 3, 'Aluno');
INSERT INTO Procedimento VALUES(1, 'Usinagem', 125.0, 'FEMEC');
INSERT INTO Procedimento VALUES(2, 'Contas', 15.0, 'FAMAT');
INSERT INTO Procedimento VALUES(3, 'Experiencia', 150.0, 'INFIS');
INSERT INTO Procedimento VALUES(4, 'Pograma', 2.50, 'FACOM');
INSERT INTO Procedimento VALUES(5, 'Choque', 523.99, 'FEELT');
INSERT INTO Material VALUES(1, 'BR HUE', 'ARROZ');
INSERT INTO Material VALUES(2, 'HUE', 'FEIJAO');
INSERT INTO Material VALUES(3, 'XING LING', 'SUSHI');
INSERT INTO Material VALUES(4, 'ZEZZ', 'OIEEE');
INSERT INTO Material VALUES(5, '2015', 'PRATO');
INSERT INTO Utiliza VALUES(1, 3);
INSERT INTO Utiliza VALUES(2, 1);
INSERT INTO Utiliza VALUES(3, 4);
INSERT INTO Utiliza VALUES(4, 2);
INSERT INTO Utiliza VALUES(5, 5);

-- d) Alterar o salário de um determinado médico. Observe o resultado analisando os
--    dados da tabela MEDICO antes e após a execução da alteração
SELECT Nome, Salario FROM Medico WHERE CRM = 4;
UPDATE Medico SET Salario = 50.99 WHERE CRM = 4;
SELECT Nome, Salario FROM Medico WHERE CRM = 4;

-- e) Listar os nomes dos materiais que não foram utilizados em nenhum procedimento
SELECT Nome FROM Material
WHERE CodMat != ALL(SELECT CodMat FROM Utiliza);

-- f) Total de mádicos
SELECT COUNT(*) FROM Medico;

-- g) Listar o nome do setor e o nome do médico responsável
SELECT S.Nome, M.Nome
FROM Setor S, Medico M
WHERE S.CRM = M.CRM;

-- h) Listar o nome dos procedimentos e quantos materiais cada um utiliza
SELECT P.Nome, COUNT(*)
FROM Procedimento P, Utiliza U
WHERE P.CodProc = U.CodProc
GROUP BY Nome;

-- i) Listar o nome dos setores que não são responsáveis por nenhum procedimento
SELECT Nome
FROM Setor
WHERE Sigla != ALL(SELECT Sigla FROM Procedimento);

-- j) Listar a média de salário por setor
SELECT S.Nome, AVG(M.Salario)
FROM Setor S, Medico M, Trabalha T
WHERE T.CRM = M.CRM AND T.Sigla = S.Sigla
GROUP BY S.Nome;

-- k) Listar o nome e o custo do procedimento mais barato
SELECT P.Nome, P.Custo
FROM Procedimento P, (SELECT MIN(Custo) FROM Procedimento) C
WHERE C.min = P.Custo;

-- l) Listar o nome do médico com o maior salário
SELECT M.Nome
FROM Medico M, (SELECT MAX(Salario) FROM Medico) S
WHERE M.Salario = S.max;

-- m)

consultas_aula21_22.sql

-- a) Todos os cursos da universidade.
SELECT Nome FROM Curso;

-- b) Quais os nomes e telefones de alunos da cidade de São Carlos - SP em ordem DESC de nome.
SELECT Nome, Telefone FROM Aluno
WHERE Cidade = 'SAO CARLOS - SP'
ORDER BY Nome DESC;

-- c) Quais os nomes de professores que foram contratados antes de 01/jan/1993. 
SELECT Nome FROM Professor
WHERE Admissao < '1993-01-01';

-- d) Quais os nomes de alunos que iniciam com a letra 'J'.
SELECT Nome FROM Aluno WHERE Nome ~ '^J.*';

-- e) Quais os nomes das disciplinas do curso de Ciência da Computação.
SELECT D.Nome
FROM Disciplina D, DisciplinaCurso DC
WHERE DC.NumCurso = 2143 AND DC.NumDisp=D.NumDisp;

-- f) Quais os nomes dos cursos que possuem no curriculum a disciplina Cálculo Numérico.
SELECT C.Nome
FROM Curso C, DisciplinaCurso DC
WHERE DC.NumDisp = 4 AND DC.NumCurso = C.NumCurso;

-- g) Quais os nomes das disciplina que o aluno Marcos João Casanova cursou no 1º semestre de 1998.
SELECT D.Nome
FROM Aluno E, Aula A, Disciplina D
WHERE	E.Nome ~ 'JOAO CASANOVA'
	AND A.NumAluno=E.NumAluno
	AND A.NumDisp = D.NumDisp
	AND A.Semestre = '01/1998';

-- h) Quais os nomes das disciplinas que o aluno Ailton Castro foi reprovado.
SELECT D.Nome
FROM Aluno E, Aula A, Disciplina D
WHERE	E.Nome ~ 'AILTON CASTRO'
	AND A.NumAluno = E.NumAluno
	AND A.NumDisp = D.NumDisp
	AND A.Nota < 7;

-- i) Quais os nomes de alunos reprovados na disciplina de Cálculo Numérico no 1º semestre de 1998.
SELECT E.Nome
FROM Aluno E, Aula A, Disciplina D
WHERE	A.NumAluno = E.NumAluno
	AND A.NumDisp = D.NumDisp
	AND D.Nome = 'CALCULO NUMERICO'
	AND A.Nota < 7;

-- j) Quais os nomes das disciplinas ministradas pelo prof. Ramon Travanti.
SELECT DISTINCT D.Nome AS Disciplina
FROM Disciplina D, Professor P, Aula A
WHERE	P.NumFunc = A.NumFunc
	AND D.NumDisp = A.NumDisp
	AND P.Nome ~ 'RAMON TRAVANTI';

-- k) Quais os nomes dos professores que já ministraram aula de Banco de Dados.
SELECT DISTINCT P.Nome AS Professor
FROM Disciplina D, Aula A, Professor P
WHERE	A.NumFunc = P.NumFunc
	AND D.NumDisp = A.NumDisp
	AND D.Nome ~* 'Banco de Dados';

-- l) Quais os nomes de alunos que cursaram o 1º semestre de 1998 em ordem alfabética, em
--   cada disciplina oferecida nesse semestre (listar também os nomes das disciplinas e os nomes
--   dos professores que ministraram cada disciplina).
SELECT E.Nome AS Aluno, D.Nome AS Disciplina, P.Nome as Professor
FROM Aluno E, Aula A, Disciplina D, Professor P
WHERE	A.NumAluno = E.NumAluno
	AND A.NumFunc = P.NumFunc
	AND A.NumDisp = D.NumDisp
	AND A.Semestre = '01/1998'
ORDER BY E.Nome;

-- m) Quais nomes de alunos, nomes de disciplinas e notas do 1º semestre de 1998 no curso de
--   Ciência da Computação.
SELECT E.Nome AS Aluno, D.Nome AS Disciplina, A.Nota
FROM Aluno E, Aula A, Curso C, Disciplina D
WHERE	A.NumAluno = E.NuMAluno
	AND E.NumCurso = C.NumCurso
	AND A.NumDisp = D.NumDisp
	AND A.Semestre = '01/1998'
	AND C.Nome ~* 'Ciencia da Computacao';

-- CRA
SELECT C.Nome Curso, E.Nome Aluno,
	SUM(A.Nota*D.QuantCreditos)/SUM(D.QuantCreditos) AS CRA
FROM Aluno E, Aula A, Curso C, Disciplina D
WHERE	E.NumAluno = A.NumAluno
	AND E.NumCurso = C.NumCurso
	AND A.NumDisp = D.NumDisp
GROUP BY Curso, Aluno
ORDER BY CRA DESC;

------- AULA 22 -------

-- a) Qual a maior e a menor nota na disciplina de Cálculo Numérico no 1º semestre de 1998
SELECT MIN(A.nota), MAX(A.nota)
FROM Aula A, Disciplina D
WHERE A.NumDisp = D.NumDisp AND A.Semestre = '01/1998';

-- b) Qual o nome do aluno que obteve maior nota na disciplina de Engenharia de Software no 1º
--    semestre de 1998. Deve retornar o nome e a nota do aluno. 
SELECT E.nome, A.nota FROM (SELECT MAX(A.NOta)
FROM Aula A, Disciplina D, Aluno E
WHERE A.NumAluno = E.NumAluno AND D.NumDisp = A.NumDisp
	AND D.Nome = 'ENGENHARIA DE SOFTWARE'
	AND A.Semestre = '01/1998') AS M,
	Aula A, Disciplina D, Aluno E
WHERE A.NumAluno = E.NumAluno AND D.NumDisp = A.NumDisp
	AND D.Nome = 'ENGENHARIA DE SOFTWARE'
	AND A.Semestre = '01/1998'
	AND A.nota = M.max;

-- c) Qual a média de notas do professor Marcos Salvador
SELECT avg(A.nota)
FROM Aula A, Professor P
WHERE A.NumFunc = P.NumFunc
	AND P.Nome = 'MARCOS SALVADOR';

-- d) Quais os alunos que tiveram nota entre 5.0 e 7.0 em ordem alfabetica de disciplina. Deve
--    retornar o nome do aluno, o nome da disciplina e nota referente a disciplina. 
SELECT E.nome, D.Nome Disciplina, A.nota
FROM Aluno E, Disciplina D, Aula A
WHERE A.NumAluno = E.NumAluno
	AND A.NumDisp = D.NumDisp
	AND A.Nota >= 5.0
	AND A.Nota <= 7.0
ORDER BY D.Nome;

-- e) Quantos alunos o professor Abgair teve no 1º semestre de 1998
SELECT COUNT(*)
FROM (SELECT DISTINCT A.NumAluno
FROM Aula A, Professor P
WHERE A.NumFunc = P.NumFunc
	AND A.Semestre = '01/1998'
	AND P.Nome ~ 'ABGAIR') E;

-- f) Qual a média de notas e a quantidade de disciplinas cursadas pelo aluno Edvaldo Carlos Silva
SELECT avg(A.Nota), COUNT(DISTINCT A.NumDisp)
FROM Aluno E, Aula A
WHERE E.NumAluno = A.NumAluno
	AND E.Nome ~ 'EDVALDO'

-- g) Quais as médias das notas, por nome de disciplina, de todos os cursos do 1º semestre de 1998 em ordem alfabética de disciplina
SELECT D.Nome, AVG(A.Nota)
FROM Aula A, Disciplina D
WHERE A.NumDisp = D.NumDisp
	AND A.Semestre = '01/1998'
GROUP BY D.Nome
ORDER BY D.Nome;

-- h) Quais as médias das notas, por nome de professor, no 1º semestre de 1998
SELECT AVG(A.Nota), P.Nome
FROM Aula A, Professor P
WHERE A.NumFunc = P.NumFunc
	AND A.Semestre = '01/1998'
GROUP BY P.Nome;

-- i) Qual a média das notas, por disciplina, no 1º semestre de 1998 do curso de Ciência da Computação
SELECT D.Nome, AVG(A.Nota)
FROM Aula A, Disciplina D, Curso C, DisciplinaCurso DC
WHERE A.NumDisp = D.NumDisp
	AND C.NumCurso = DC.NumCurso
	AND DC.NumDisp = D.NumDisp
	AND A.Semestre = '01/1998'
	AND C.Nome = 'CIENCIA DA COMPUTACAO'
GROUP BY D.Nome;

-- j) Qual foi a quantidade de créditos concluídos (considerar somente as disciplinas aprovadas) do aluno Edvaldo Carlos Silva
SELECT SUM(D.QuantCreditos)
FROM Aluno E, Aula A, Disciplina D
WHERE A.Nota >= 7.0
	AND E.Nome = 'EDVALDO CARLOS SILVA'
	AND A.NumAluno = E.NumAluno
	AND D.NumDisp = A.NumDisp;

-- k) Quais os nomes de alunos que já completaram 70 créditos (considerar somente os
--    aprovados na disciplina). Deve retornar os nomes dos alunos e a quantidade de créditos.
SELECT E.Nome, SUM(D.QuantCreditos) cred
FROM Aluno E, Aula A, Disciplina D
WHERE A.Nota >= 7.0
	AND A.NumAluno = E.NumAluno
	AND D.NumDisp = A.NumDisp
GROUP BY E.Nome
HAVING SUM(D.QuantCreditos) > 70;

-- l) Quais os nomes de alunos que cursaram o 1º semestre de 1998, pertencem ao curso de
--    Ciência da Computação e possuem nota superior à 8.0. Deve retornar os nomes dos alunos, os
--    nomes da disciplina e os nomes dos professores.
SELECT E.Nome Aluno, D.Nome Disciplina, P.Nome Professor
FROM Aluno E, Aula A, Curso C, Professor P, Disciplina D
WHERE A.NumAluno = E.NumAluno
	AND A.Semestre = '01/1998'
	AND E.NumCurso = C.NumCurso
	AND A.Nota > 8.0
	AND P.NumFunc = A.NumFunc
	AND A.NumDisp = D.NumDisp;