exercicios Banco de dados

Esta é uma pré-visualização de arquivo. Entre para ver o arquivo original

exercicios/Cria_BD_Empresa.docx
CREATE TABLE EMPREGADO
	(
	 NOME		VARCHAR(15)	NOT NULL,
	 INICIAISDOMEIO	CHAR,
	 SOBRENOME	VARCHAR(15)	NOT NULL,
	 CIC_E		VARCHAR(9),
	 DATANASC	DATE,
 	ENDERECO	VARCHAR(50),
	 SEXO		CHAR,
	 SALARIO	NUMERIC(10,2),
	 CIC_SUPER	VARCHAR(9),
	 NUD		INTEGER		NOT NULL,
	PRIMARY KEY (CIC_E) );
	
CREATE TABLE DEPARTAMENTO
	(
	 NOMED		VARCHAR(15),
	 NUMERODEP	INTEGER		NOT NULL,
	 CIC_GER	VARCHAR(9),
	 DATAINICIOGER	DATE,
	PRIMARY KEY (NUMERODEP),
	UNIQUE (NOMED) ); 
CREATE TABLE PROJETO
	(
	 NOMEP		VARCHAR(15)	NOT NULL,
	 NUMEROP	INTEGER		NOT NULL,
	 LOCALIZACAOP	VARCHAR(15),
	 NUMD		INTEGER		NOT NULL,
	PRIMARY KEY (NUMEROP) );
	 
 
CREATE TABLE TRABALHA_EM
	(
	 CIC_E	VARCHAR(9),
	 NUP	INTEGER		NOT NULL,
	 HORAS	NUMERIC(3,1),
	PRIMARY KEY (CIC_E,NUP) );
CREATE TABLE LOCALIzACOES_DEP
	(
	 NUMERODEP	INTEGER		NOT NULL,
	 LOCALIZACAOD	VARCHAR(15)	NOT NULL,
	PRIMARY KEY (NUMERODEP, LOCALIZACAOD));
CREATE TABLE DEPENDENTE
	(
	 CIC_E	 		VARCHAR(9)	NOT NULL,
	 NOME_DEPENDENTE	VARCHAR(15)	NOT NULL,
	 SEXO			CHAR,
	 DATANASC		DATE,
	 PARENTESCO		VARCHAR(8),
	PRIMARY KEY (CIC_E,NOME_DEPENDENTE));
--Popula tabela empregado
insert into empregado values ('James',		'E',	'Borg',		'888665555',	'11/10/1937',	'450 Stone, Houston, TX',	'M',	55000,	 null,		1);
insert into empregado values ('Jeniffer',	'S',	'Wallace',	'987654321',	'20/06/1941',	'291 Berry, Bellaire',		'F',	43000,	'888665555',	4);
insert into empregado values ('Franklin',	'T',	'Wong',		'333445555',	'12/08/1955',	'638 Voss, Houston, TX',	'M',	40000,	'888665555',	5);
insert into empregado values ('Ahmad',		'V',	'Jabbar',	'987987987',	'29/03/1969',	'980 Dallas, Houston, TX',	'M',	25000,	'987654321',	4);
insert into empregado values ('Alicia',	'J',	'Zelaya',	'999887777',	'19/07/1968',	'3321 Castle, Spring, TX',	'F',	25000,	'987654321',	4);
insert into empregado values ('Ramesh',	'K',	'Narayan',	'666884444',	'15/09/1962',	'975 Fire Oak, Humble, Tx',	'M',	38000,	'333445555',	5);
insert into empregado values ('Joyce',		'A',	'English',	'453453453',	'31/07/1972',	'5631 Rice, Houston, TX',	'F',	25000,	'333445555',	5);
insert into empregado values ('John',		'B',	'Smith',	'123456789',	'01/09/1965',	'731, Fondren, Houston, Tx',	'M',	30000,	'333445555',	5);
--Popula tabela departamento
insert into departamento values ('Pesquisa',		5,	'333445555',	'02/05/1988');
insert into departamento values ('Administracao',	4,	'999887777',	'01/01/1995');
insert into departamento values ('Matriz',	1,	'888665555',	'19/06/1981');
--Popula tabela projeto
insert into projeto values ('ProdutoX',	1,	'Bellaire',	5);
insert into projeto values ('ProdutoX',	2,	'Sugarland',	5);
insert into projeto values ('ProdutoX',	3,	'Houston',	5);
insert into projeto values ('Informatização',	10,	'Stafford',	4);
insert into projeto values ('Reorganização',	20,	'Houston',	1);
insert into projeto values ('NovosBenefícios',	30,	'Stafford',	4);
--Popula tabela trabalha_em
insert into trabalha_em values ('123456789',	1,	32.5);
insert into trabalha_em values ('123456789',	2,	7.5);
insert into trabalha_em values ('666884444',	3,	40.0);
insert into trabalha_em values ('453453453',	1,	20.0);
insert into trabalha_em values ('453453453',	2,	20.0);
insert into trabalha_em values ('333445555',	2,	10.0);
insert into trabalha_em values ('333445555',	3,	10.0);
insert into trabalha_em values ('333445555',	10,	10.0);
insert into trabalha_em values ('333445555',	20,	10.0);
insert into trabalha_em values ('999887777',	30,	30.0);
insert into trabalha_em values ('999887777',	10,	10.0);
insert into trabalha_em values ('987987987',	10,	35.0);
insert into trabalha_em values ('987987987',	30,	5.0);
insert into trabalha_em values ('987654321',	30,	20.0);
insert into trabalha_em values ('987654321',	20,	15.0);
insert into trabalha_em values ('888665555',	20,	null);
--Popula tabela LOCALIZACOES_DEP
insert into LOCALIZACOES_DEP values ( 1,'Houston');
insert into LOCALIZACOES_DEP values ( 4,'Stafford');
insert into LOCALIZACOES_DEP values ( 5,'Bellaire');
insert into LOCALIZACOES_DEP values ( 5,'Sugarland');
insert into LOCALIZACOES_DEP values ( 5,'Houston');
--Popula tabela DEPENDENTE
insert into DEPENDENTE values ('333445555','Alice',	'F','04/05/1986','Filha');
insert into DEPENDENTE values ('333445555','Theodore',	'M','25/01/1983','Filho');
insert into DEPENDENTE values ('333445555','Joy',	'F','03/05/1958','Esposa');
insert into DEPENDENTE values ('987654321','Abner',	'M','28/02/1942','Esposa');
insert into DEPENDENTE values ('123456789','Michael',	'M','04/01/1988','Filho');
insert into DEPENDENTE values ('123456789','Alice',	'F','30/12/1988','Filha');
insert into DEPENDENTE values ('123456789','Elizabeth','F','05/05/1967','Esposa');
Cria chave estrangeira CIC_SUPER na tabela EMPREGADO( REFERENCIA EMPREGADO(CIC_E) )
ALTER TABLE EMPREGADO
	ADD CONSTRAINT FK_CIC_SUPER_EMPREGADO	FOREIGN KEY (CIC_SUPER) REFERENCES EMPREGADO(CIC_E)
	ON DELETE CASCADE
	ON UPDATE CASCADE;
	
Cria chave estrangeira NUD na tabela EMPREGADO ( REFERENCIA DEPARTAMENTO(NUMERODEP) )
ALTER TABLE EMPREGADO
	ADD CONSTRAINT FK_NUD_DEPARTAMENTO
	FOREIGN KEY (NUD) REFERENCES DEPARTAMENTO(NUMERODEP)
	ON DELETE CASCADE
	ON UPDATE CASCADE;
Cria chave estrangeira CIC_GER na tabela DEPARTAMENTO ( REFERENCIA EMPREGADO(CIC_E) )
ALTER TABLE DEPARTAMENTO
	ADD CONSTRAINT FK_CIC_GER_EMPREGADO
	FOREIGN KEY (CIC_GER) REFERENCES EMPREGADO(CIC_E)
	ON DELETE CASCADE
	ON UPDATE CASCADE;
Cria chave estrangeira NUMD na tabela PROJETO( REFERENCIA DEPARTAMENTO (NUMERODEP) )
ALTER TABLE PROJETO
	ADD CONSTRAINT FK_NUMD_DEPARTAMENTO		
	FOREIGN KEY (NUMD) REFERENCES DEPARTAMENTO (NUMERODEP)
	ON DELETE CASCADE
	ON UPDATE CASCADE;
Cria chave estrangeira CIC_E na tabela TRABALHA_EM ( REFERENCIA EMPREGADO(CIC_E) )
ALTER TABLE TRABALHA_EM
	ADD CONSTRAINT FK_CIC_E_EMPREGADO
	FOREIGN KEY (CIC_E) REFERENCES EMPREGADO(CIC_E)
	ON DELETE CASCADE
	ON UPDATE CASCADE;
--Cria chave estrangeira NUP na tabela TRABALHA_EM ( REFERENCIA EMPREGADO(CIC_E) )
ALTER TABLE TRABALHA_EM
	ADD CONSTRAINT FK_NUP_PROJETO
	FOREIGN KEY (NUP) REFERENCES PROJETO(NUMEROP)	
	ON DELETE CASCADE
	ON UPDATE CASCADE;
--Cria chave estrangeira CIC_E na tabela DEPENDENTE ( REFERENCIA EMPREGADO(CIC_E) )
ALTER TABLE DEPENDENTE
	ADD CONSTRAINT FK_CIC_E_EMPREGADO
	FOREIGN KEY (CIC_E) REFERENCES EMPREGADO(CIC_E)
	ON DELETE CASCADE
	ON UPDATE CASCADE;
--Cria chave estrangeira NUMERODEP na tabela LOCALICACOES_DEP ( REFERENCIA DEPARTAMENTO(NUMERODEP) )
ALTER TABLE LOCALIZACOES_DEP
	ADD CONSTRAINT FK_NUMERODEP_DEPARTAMENTO
	FOREIGN KEY (NUMERODEP) REFERENCES DEPARTAMENTO(NUMERODEP)
	ON DELETE CASCADE
	ON UPDATE CASCADE;
exercicios/Exercicios BD_Empresa.doc
Exercícios de SQL / Banco de Dados de uma Empresa
Mostre os CICs de todos os empregados da empresa
Mostre os registros completos de todos os empregados do departamento cujo número é 5.
Mostre os salários de todos os empregados.
Mostre os salários de todos os empregados, sem repetição
Mostre os nomes e sobrenomes de todos os empregados que morem em Houston, Texas.
Mostre os nomes e sobrenomes de todos os empregados que nasceram na década de 50.
Mostre os registros completos de todos os empregados do departamento
5 cujos salários estão entre $300,00 e $400,00.
Mostre os nomes de todos os empregados que não possuem supervisores.
Mostre a data de nascimento e o endereço dos empregados cujo nome é ‘John B.Smith’
Faça uma lista dos CICs de todos os empregados, e os nomes de seus respectivos departamentos.
Mostre os registros completos de todos os empregados do departamento de Pesquisa.
Mostre, para cada empregado que trabalha no projeto ‘ProdutoX’, seu nome, seu salário atual e o salário resultante se for dado a ele um aumento de 10 por cento.
Mostre o nome e o endereço de todos os empregados que trabalham para o departamento cujo nome é ‘Pesquisa’ .
Para todo projeto localizado em ‘Stafford’, liste o número do projeto, o número do departamento controlador e o sobrenome, endereço e data de nascimento do gerente do departamento.
Para cada empregado, mostre o nome e o sobrenome do empregado e o 	nome e o sobrenome do seu supervisor imediato.
Mostre os sobrenomes dos empregados e respectivos supervisores, exibindo o resultado com os nomes das colunas ‘SOBRENOME_EMPREGADO’ e ‘SOBRENOME_SUPERVISOR’.
Mostre uma lista dos empregados, dos seus departamentos e dos projetos nos quais eles estejam trabalhando, ordenados por departamento e, dentro de cada departamento, ordenados alfabeticamente pelo sobrenome e nome. Cada linha da lista deve ter os seguintes elementos nesta ordem:
Nome do departamento, sobrenome e nome do empregado e nome do projeto
Mostre, para todos os empregados, a soma dos salários, o salário máximo, o salário mínimo e o salário médio.
Mostre a soma dos salários de todos os empregados do departamento ‘Pesquisa’, assim como o salário máximo, o salário mínimo e o salário médio deste mesmo departamento.
Mostre o número total de empregados na empresa.
 
Mostre o número de empregados no departamento ‘Pesquisa’
Exiba o número de diferentes salários existentes no banco de dados da empresa.
Para cada departamento, mostre o numero do departamento, o número de empregados no departamento e a média de seus salários.
Para cada projeto, mostre número do projeto, o nome do projeto e o número de empregados que trabalham no projeto.
Para cada projeto no qual trabalham mais do que dois empregados, mostre o número do projeto, o nome do projeto e o número de empregados que trabalham no projeto.
Para cada projeto, mostre o número do projeto, o nome do projeto e o número de empregados do departamento 5 que trabalham no projeto.
O departamento ‘Administracao’ teve um excelente desempenho. Os seus funcionários tiveram um aumento de 25%. Execute um comando que programe este aumento no banco de dados.
O projeto denominado ‘ProdutoX’ terminou, e precisa ser eliminado do banco de dados. Que comandos deveriam ser utilizados para eliminar este projeto? Em que ordem devem ser executados? Justifique a sua resposta. Dica: Verifique as chaves estrangeiras envolvidas.