Nesse post você encontra uma oportunidade de treinar sua habilidade em SQL com vários exercícios usando consultas em SQL. Preste bastante atenção quanto a construção do ambiente e escreva e teste as consultas indicadas na seção 3.
Não sabe como fazer consultas usando SQL? veja aqui um tutorial básico.
Para resolver esse exercício é preciso compreender conceitos básicos de SQL, como: criação de tabelas, inserção de dados etc. Para saber mais sobre isso acesse nossos conteúdos gratuitos sobre banco de dados.
Regras do exercício:
– Siga o passo a passo descrito no corpo do exercício
– Utilize qualquer banco de dados, recomendamos o Posgres ou MySQL
– Caso queira usar programas que auxiliem na construção da busca o risco de se acomodar é seu.
Passo 01- Crie o banco de dados para o esquema abaixo:
- Professor (número_prof, profnome, profrua, profcidade)
- Aluno (número_aluno, alunome, alurua, alucidade)
- Matrícula (número_aluno,código_disc,ano)
- Disciplina (código_disc,nome_disciplina,nome_curso, quantidade de aulas)
- ProfDisc (código_disc,número_prof, ano)
CREATE TABLE professor(
numero_prof integer NOT NULL,
profnome character varying(30),
profrua character varying(30),
profcidade character varying(30),
CONSTRAINT professor_pkey PRIMARY KEY (numero_prof)
);
CREATE TABLE aluno (
numero_aluno integer NOT NULL,
alunome character varying(30),
alufrua character varying(30),
alucidade character varying(30),
CONSTRAINT aluno_pkey PRIMARY KEY (numero_aluno)
);
CREATE TABLE disciplina(
codigo_disc integer NOT NULL,
nome_disciplina character varying(30),
nome_curso character varying(30),
qtd_aulas integer,
CONSTRAINT disciplina_pkey PRIMARY KEY (codigo_disc)
);
CREATE TABLE matricula(
numero_aluno integer NOT NULL,
codigo_disc integer NOT NULL,
ano character varying(4) NOT NULL,
CONSTRAINT matricula_pkey PRIMARY KEY (numero_aluno, codigo_disc, ano),
CONSTRAINT matricula_aluno_fk FOREIGN KEY (numero_aluno)
REFERENCES aluno (numero_aluno),
CONSTRAINT matricula_disciplina_fk FOREIGN KEY (codigo_disc)
REFERENCES disciplina (codigo_disc)
);
CREATE TABLE profdisc(
codigo_disc integer NOT NULL,
numero_prof integer NOT NULL,
ano character varying(4),
CONSTRAINT prof_disc_pk PRIMARY KEY (codigo_disc, numero_prof, ano),
CONSTRAINT codigo_disc_fk FOREIGN KEY (codigo_disc)
REFERENCES disciplina (codigo_disc),
CONSTRAINT numero_prof_fk FOREIGN KEY (numero_prof)
REFERENCES professor (numero_prof)
);
Passo 02 – Insira para cada tabela os registros
INSERT INTO aluno VALUES (1, ' Tiago Mateus Junior', '210', 'Palmas');
INSERT INTO aluno VALUES (2, 'Luciana Gomes', '202', 'Palmas');
INSERT INTO aluno VALUES (3, 'João Mateus', '2300', 'Gurupi');
INSERT INTO aluno VALUES (4, 'José Silva', '1024', 'Paraíso');
INSERT INTO aluno VALUES (5, 'Juraci', '106', 'Miranorte');
INSERT INTO aluno VALUES (6, 'Paulo', '101', 'Goianorte');
INSERT INTO aluno VALUES (7, 'Adriana Calcanhoto', 'rua Alvorada', 'Paraíso');
INSERT INTO aluno VALUES (8, 'Rosane', 'rua das flores', 'Guaraí');
INSERT INTO aluno VALUES (9, 'Mara', 'rua das águas', 'Porto Nacional');
INSERT INTO aluno VALUES (10, 'Julia', 'rua de madeira', 'Paraíso');
INSERT INTO aluno VALUES (11, 'Julia', 'rua de madeira', 'Palmas');
INSERT INTO professor VALUES (1, 'João José', '103', 'Palmas');
INSERT INTO professor VALUES (2, 'Maria José', '201', 'Palmas');
INSERT INTO professor VALUES (3, 'Bruno', '305', 'Paraiso');
INSERT INTO professor VALUES (4, 'José Ricardo', '1106', 'Porto Nacional');
INSERT INTO professor VALUES (5, 'Manuela', '120', 'Araguaína');
INSERT INTO professor VALUES (6, 'Bruno', '1003', 'Porto Nacional');
INSERT INTO professor VALUES (7, 'Luciana', ' 100', 'Miracema');
INSERT INTO professor VALUES (8, 'Paulo', ' 150', 'Palmas');
INSERT INTO professor VALUES (9, 'Lual', '1006', 'Paraíso');
INSERT INTO professor VALUES (10, 'Antonio Marcos', '170', 'Araguaína');
INSERT INTO disciplina (codigo_disc, nome_disciplina, nome_curso, qtd_aulas) VALUES (1, ' Química', 'Ensino Médio', 4);
INSERT INTO disciplina (codigo_disc, nome_disciplina, nome_curso, qtd_aulas) VALUES (2, ' Biologia', 'Ensino Médio', 4);
INSERT INTO disciplina (codigo_disc, nome_disciplina, nome_curso, qtd_aulas) VALUES (3, 'História', 'Ensino Médio', 2);
INSERT INTO disciplina (codigo_disc, nome_disciplina, nome_curso, qtd_aulas) VALUES (4, ' Religião', 'Ensino Médio', 2);
INSERT INTO disciplina (codigo_disc, nome_disciplina, nome_curso, qtd_aulas) VALUES (5, ' Português', 'Ensino Pós Médio', 4);
INSERT INTO disciplina (codigo_disc, nome_disciplina, nome_curso, qtd_aulas) VALUES (6, 'Física', 'Ensino Pós Médio', 6);
INSERT INTO disciplina (codigo_disc, nome_disciplina, nome_curso, qtd_aulas) VALUES (7, 'Matemática', 'Ensino Pós Médio', 4);
INSERT INTO disciplina (codigo_disc, nome_disciplina, nome_curso, qtd_aulas) VALUES (8, 'Geografia ', 'Ensino Pós Médio', 2);
INSERT INTO disciplina (codigo_disc, nome_disciplina, nome_curso, qtd_aulas) VALUES (9, 'Informática Aplicada', 'CST Sistemas para Internet', 4);
INSERT INTO disciplina (codigo_disc, nome_disciplina, nome_curso, qtd_aulas) VALUES (10, 'Banco de dados', 'CST Sistemas para Internet', 4);
INSERT INTO disciplina (codigo_disc, nome_disciplina, nome_curso, qtd_aulas) VALUES (11, 'Programação de Banco de dados', 'Computação', 4);
INSERT INTO matricula (numero_aluno, codigo_disc, ano) VALUES (1, 10, '2018');
INSERT INTO matricula (numero_aluno, codigo_disc, ano) VALUES (1, 4, '2018');
INSERT INTO matricula (numero_aluno, codigo_disc, ano) VALUES (1, 6, '2018');
INSERT INTO matricula (numero_aluno, codigo_disc, ano) VALUES (5, 3, '2018');
INSERT INTO matricula (numero_aluno, codigo_disc, ano) VALUES (6, 1, '2018');
INSERT INTO matricula (numero_aluno, codigo_disc, ano) VALUES (2, 6, '2018');
INSERT INTO matricula (numero_aluno, codigo_disc, ano) VALUES (7, 9, '2018');
INSERT INTO matricula (numero_aluno, codigo_disc, ano) VALUES (8, 10, '2018');
INSERT INTO matricula (numero_aluno, codigo_disc, ano) VALUES (9, 1, '2018');
INSERT INTO matricula (numero_aluno, codigo_disc, ano) VALUES (2, 2, '2018');
INSERT INTO matricula (numero_aluno, codigo_disc, ano) VALUES (11, 11, '2018');
INSERT INTO profdisc (codigo_disc, numero_prof, ano) VALUES (1, 10, '2018');
INSERT INTO profdisc (codigo_disc, numero_prof, ano) VALUES (2, 5, '2018');
INSERT INTO profdisc (codigo_disc, numero_prof, ano) VALUES (3, 7, '2018');
INSERT INTO profdisc (codigo_disc, numero_prof, ano) VALUES (4, 4, '2018');
INSERT INTO profdisc (codigo_disc, numero_prof, ano) VALUES (5, 9, '2018');
INSERT INTO profdisc (codigo_disc, numero_prof, ano) VALUES (6, 2, '2018');
INSERT INTO profdisc (codigo_disc, numero_prof, ano) VALUES (7, 3, '2018');
INSERT INTO profdisc (codigo_disc, numero_prof, ano) VALUES (8, 9, '2018');
INSERT INTO profdisc (codigo_disc, numero_prof, ano) VALUES (9, 6, '2018');
INSERT INTO profdisc (codigo_disc, numero_prof, ano) VALUES (10, 3, '2018');
INSERT INTO profdisc (codigo_disc, numero_prof, ano) VALUES (11, 5, '2018');
3 – Faça as consultas usando SQL
1 – Crie uma consulta com todas as disciplinas oferecidas no ano de 2018, constando os nomes das disciplinas, nomes e cidades dos professores responsáveis e nomes dos cursos das disciplinas.
2 – Crie uma consulta que mostre todos os nomes dos professores do curso de CST Sistemas para Internet, no ano de 2018, e os nomes das disciplinas pelas quais são responsáveis.
3 – Crie uma consulta com todos os nomes de alunos matriculados no ano de 2018, os nomes das disciplinas em que estão matriculados, a cidade onde moram e os nomes dos cursos das disciplinas.
- Crie uma consulta que mostre todos os alunos de Palmas que cursam alguma disciplina de Programação de Banco de dados no ano de 2018.
- Crie uma consulta que mostre todos os professores que não moram em Palmas e que ministram alguma disciplina no curso de CST Sistemas para Internet.
- Crie uma consulta que mostre o nome dos professores e a média de aulas que ministram.
- Crie uma consulta que mostre o nome dos professores o total de aulas que ministram por curso.
- Crie uma consulta que mostre o nome do aluno, nome do curso, código e nome das disciplinas matriculadas de todos os alunos no ano de 2018.
onde fica a resposta