Sistema Academico

sys_academico.sql

-- Banco de Dados Academico
-- version 1.0
-- Autor: Rodrigo
-- Site: Github.com/rodrigo54

-- --------------------------------------------------------
-- Database: 'Escola'
-- Crie o Banco com o seguinte comando
-- CREATE Database escola DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
-- --------------------------------------------------------

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

-- --------------------------------------------------------
-- Estrutura da tabela 'uf'
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS uf (
	ufid int(11) NOT NULL AUTO_INCREMENT,
	uf varchar(50) NOT NULL,
	PRIMARY KEY (ufid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------
-- Estrutura da tabela 'cidade'
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS cidade (
	idCidade int(11) NOT NULL AUTO_INCREMENT,
	cidade varchar(80) NOT NULL,
	ufid int(11) DEFAULT NULL,
	PRIMARY KEY (idCidade),
	FOREIGN KEY (ufid) REFERENCES uf(ufid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------
-- Estrutura da tabela 'escolas'
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS escolas (
	idEscola int(11) NOT NULL AUTO_INCREMENT,
	nome varchar(50) NOT NULL,
	idCidade int(11) DEFAULT NULL,
	PRIMARY KEY (idEscola),
	FOREIGN KEY (idCidade) REFERENCES cidade(idCidade)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------
-- Estrutura da tabela 'aluno'
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS aluno (
	idAluno int(11) NOT NULL AUTO_INCREMENT,
	nome varchar(50) NOT NULL,
	idEscola int(11) NOT NULL,
	PRIMARY KEY (idAluno),
	FOREIGN KEY (idEscola) REFERENCES escolas(idEscola)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;


-- --------------------------------------------------------
-- Estrutura da tabela 'professores'
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS professores (
	idProfessor int(11) NOT NULL AUTO_INCREMENT,
	nome varchar(50) NOT NULL,
	materia varchar(20) NOT NULL,
	PRIMARY KEY (idProfessor)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------
-- Estrutura da tabela 'boletim'
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS boletim (
	idBoletim int(11) NOT NULL AUTO_INCREMENT,
	idAluno int(11) NOT NULL,
	idProfessor int(11) NOT NULL,
	nota  dec(4,2) NOT NULL default 0,
	PRIMARY KEY (idBoletim),
	FOREIGN KEY (idAluno) REFERENCES aluno(idAluno),
	FOREIGN KEY (idProfessor) REFERENCES professores(idProfessor)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------
-- Dados da tabela 'uf'
-- --------------------------------------------------------
INSERT INTO uf (ufid, uf)
	VALUES 
		('1','Maranhão'),
		('2','Piaui'),
		('3','Ceara'),
		('4','São Paulo'),
		('5','Amazonas');

-- --------------------------------------------------------
-- Dados da tabela 'cidade'
-- --------------------------------------------------------
INSERT INTO cidade (idCidade, cidade, ufid)
	VALUES 
		('1','São Luis','1'),
		('2','Teresina','2'),
		('3','Fortaleza','3'),
		('4','Raposa','1'),
		('5','Manaus','5');

-- --------------------------------------------------------
-- Dados da tabela 'uf'
-- --------------------------------------------------------
INSERT INTO escolas (idEscola, nome, idCidade)
	VALUES 
		('1','Pitágoras','1'),
		('2','Pitágoras','2'),
		('3','Pitágoras','3'),
		('4','Pitágoras','4'),
		('5','Pitágoras','5');

-- --------------------------------------------------------
-- Dados da tabela 'uf'
-- --------------------------------------------------------
INSERT INTO aluno (idAluno, nome, idEscola)
	VALUES 
		('1','Rodrigo','1'),
		('2','Geanne','1'),
		('3','Justo','1'),
		('4','Jessica','1'),
		('5','Wallison','1');

-- --------------------------------------------------------
-- Dados da tabela 'uf'
-- --------------------------------------------------------
INSERT INTO professores (idProfessor, nome, materia)
	VALUES 
		('1','Thiago','Banco de Dados'),
		('2','Arivaldo','Eletricidade'),
		('3','Servulo','Algoritmos'),
		('4','Arlison','Estrutura de Dados'),
		('5','Michel','Calculo 3');

-- --------------------------------------------------------
-- Dados da tabela 'uf'
-- --------------------------------------------------------
INSERT INTO boletim (idBoletim, idAluno, idProfessor, nota)
	VALUES 
		('1', '1', '1', '10.00'),
		('2', '2', '1', '7.50'),
		('3', '5', '5', '8.75'),
		('4', '4', '3', '5.25'),
		('5', '3', '4', '3.10');

-- --------------------------------------------------------
-- Select das notas dos alunos com a materia
-- --------------------------------------------------------
SELECT aluno.nome, boletim.nota, professores.materia 
	FROM aluno 
		INNER JOIN boletim 
			ON boletim.idAluno = aluno.idAluno
		INNER JOIN professores 
			ON professores.idProfessor = boletim.idProfessor 
	ORDER BY nota DESC