Modelo de Dados XPTO: Alterações e Consultas em SQL

Modelo de Dados da Empresa XPTO

Create table if not exists FUNC(

numero_func Smallint not null,

Nome_func CHAR(40) not null,

CPF_func NUMERIC(11) not null,

Data_admissao_func DATE not null,

Data_saida_func DATE not null,

Situacao_func CHAR(1) not null default = 'A',

Codigo_carg CHAR(3) not null,

Salario_base_func DECIMAL(7,2) not null default = '0,00',

Primary key (numero_func))

Alter table FUNC (add constraint FK_func1 foreign key (codigo_carg) references carg(codigo_carg))

b) Inserir o campo fone_depto

Inserir o campo fone_depto, alfanumérico de 15 posições na tabela DPTO.

Alter table dpto (add Fone_dpto varchar (15))

c) Aumentar o campo nome_carg

Aumentar o campo nome_carg para 40 posições.

Alter table carg (alter nome_carg char(40) not null)

d) Restrições de chave estrangeira para a tabela ALOC

Implementar as restrições de chave estrangeira para a tabela ALOC.

Alter table Aloc (add constraint FK_aloc1 foreign key (codigo_dpto) references dpto(codigo_dpto))

Alter table Aloc (add constraint FK_aloc2 foreign key (numero_func) references FUNC(numero_func))

e) Índice único para o campo cpf_func

Criar um índice único para o campo cpf_func.

Alter table FUNC (alter cpf_func numeric (11) not null unique index)

f) Índice para o campo nome_func

Criar um índice para o campo nome_func.

Alter table FUNC (alter nome_func char(40) not null index)

g) Alterar nome_func de CHAR para VARCHAR

Alterar o nome_func de CHAR para VARCHAR mantendo o tamanho original.

Alter table func (alter nome_func varchar (40) not null)

h) Valor padrão do campo ind_comissao_carg

Alterar o campo ind_comissao_carg para que o valor padrão seja 'N'.

Alter table carg (alter ind_comissao_carg char(1) not null default 'N')

i) Remover o valor padrão do campo situacao_func

Remover o valor padrão do campo situacao_func.

Alter table func (alter situacao_func char (1) not null)

j) Chave primária da tabela PGTO

Criar a chave primária da tabela PGTO.

Alter table PGTO (add constraint PK_pg1 primary key (ano_ref_pgto))

Alter table PGTO (add constraint PK_pg2 primary key (mes_ref_pgto))

Consultas em SQL sobre o Modelo de Dados da Empresa XPTO

Considerando ainda o Modelo de Dados da empresa XPTO, resolva as consultas abaixo através de linguagem SQL.

a) Funcionários cumprindo aviso prévio com cargo de formação técnica

Exibir o nome e a data de admissão dos funcionários que estão cumprindo aviso prévio cujo cargo exija formação técnica.

Select nome_func, data_admissao_func

From Func inner join carg on func.codigo_carg = carg.codigo_carg

Where situacao_func = 'P'

And classe_carg = 'T'

b) Salário líquido de janeiro de 2013 dos mensalistas

Exibir o nome e o salário líquido recebido em janeiro de 2013 de cada funcionário (não prestador de serviços) mensalista.

Select nome_func, (sal_base_pgto - total_desc_pgto) as salario_liquido

From Func inner join pgto on func.codigo_func = pgto.codigo_func

Inner join carg on Func.codigo_carg = carg.codigo_carg

Where ano_ref_pgto = '2013'

And mes_ref_pgto = '1'

And tipo_contrato_carg = 'M'

c) Cargos sem funcionários ativos

Listar os nomes dos cargos que não possuam nenhum funcionário ativo cadastrado com estes códigos de cargo.

Select nome_carg, situacao_func

From carg inner join func on carg.codigo_carg = func.codigo_carg

Where situacao_func in ('I', 'L', 'M', 'P')

d) Empregados do departamento TI com cargos comissionados

Exibir os nomes dos empregados do departamento cujo código é 'TI' que tenham cargos comissionados.

Select nome_func

From FUNC inner join carg on func.codigo_carg = carg.codigo_carg

Where carg.codigo_carg = 'TI'

And ind_comissao_carg = 'S'