ExercicioSQL

/*	Criar tabelas...	*/
CREATE TABLE empresa(
	id_empresa		INTEGER UNSIGNED		AUTO_INCREMENT		PRIMARY KEY,
	empresa			VARCHAR(64)			NOT NULL			UNIQUE KEY
	);

CREATE TABLE usuario(
	id_usuario		INTEGER UNSIGNED		AUTO_INCREMENT		PRIMARY KEY,
	email			VARCHAR(64)			NOT NULL			UNIQUE KEY,
	senha			VARCHAR(20)			NOT NULL,
	fk_empresa		INTEGER UNSIGNED,
	CONSTRAINT fk_empresa_usuario
		FOREIGN KEY(fk_empresa)
			REFERENCES empresa(id_empresa)
			ON UPDATE CASCADE
	);

CREATE TABLE ponto(
	id_ponto		INTEGER UNSIGNED		AUTO_INCREMENT			PRIMARY KEY,
	fk_usuario		INTEGER UNSIGNED		NOT NULL,
	entrada			DATETIME			NOT NULL			DEFAULT NOW(),
	saida			DATETIME,
	CONSTRAINT fk_usuario_ponto
		FOREIGN KEY(fk_usuario)
			REFERENCES usuario(id_usuario)
			ON UPDATE CASCADE
	);


/* 	Povoar tabelas com exemplos...	*/
INSERT INTO empresa
	(empresa) VALUES
	("Gmail"),
	("InJunior")
	;

INSERT INTO usuario
	(email,senha,fk_empresa) VALUES
		("kaue.maia@gmail.com","Senha",2),
		("igor.martire@injunior.com.br","OutraSenha",2),
		("nicholas.barcelos@injunior.com.br","SenhaRepetida",2),
		("edoarda@id.uff.br","SenhaRepetida", NULL),
		("MrNull@id.uff.br","Null", NULL)
	;

INSERT INTO ponto
	(fk_usuario,entrada,saida) VALUES
		(1,"2015-07-27 09:00:00","2015-07-27 16:55:12"),
		(2,"2015-07-27 09:00:00","2015-07-28 17:05:00"),
		(4,"2015-07-27 09:00:00","2015-07-27 16:55:12"),
		(1,"2015-07-28 09:07:40", NULL),
		(2,"2015-07-28 12:30:00","2015-07-28 19:05:00"),
		(3,"2015-07-28 09:00:00","2015-07-28 18:17:00")
	;


/*	Consultar cada informacao de cada usuario...	*/
SELECT
		email,
		empresa,
		entrada,
		saida
	FROM
		usuario
	LEFT JOIN empresa
		ON id_empresa=fk_empresa
	LEFT JOIN ponto
		ON id_usuario=fk_usuario
	;