Doubts SQL Server [closed]

Question

Doubts SQL Server [closed]

Navigation

#1 by (2 votes)
#2 by (2 votes)

-1

I'm having trouble with this question, can anyone help me?

Tables:

 CREATE TABLE Funcionario
(
 Cod_Func int Identity not null,
 Nome_Func varchar(100) not null,
 Data_CadFunc smalldatetime not null Default
Getdate(),
 Sexo_Func char(01) not null Default
'F',
 Sal_Func decimal(10,2) not null Default 200,
 End_Func varchar(100) not null,
 Constraint PK_Func Primary Key(Cod_Func),
 Constraint CH_Func1 Check(Data_CadFunc>=Getdate()),

 Constraint CH_Func2 Check(Sexo_Func IN
('F','M')),
 Constraint CH_Func3 Check(Sal_Func >=0)
)

CREATE TABLE Dependente
(
 Cod_Dep int identity not null,
 Cod_Func int not null,
 Nome_Dep varchar(100) not null,
 Data_NascDep smalldatetime not null,
 Sexo_Dep char(01) not null Default
'F',
 Constraint PK_Dep Primary Key(Cod_Dep),
 Constraint FK_Dep Foreign
Key(Cod_Func)References Funcionario(Cod_Func),
 Constraint CH_Dep Check(Sexo_Dep IN ('F','M'))
)

link link

sql-server

asked by anonymous 02.11.2016 / 16:17

2 answers

Insert image database Search session user record in mysql

score 2 · Answer 1

You can use a left join for this:

select fun.nome_func, dep.nome_dep
  from funcionario fun
  left join dependente dep
    on dep.cod_func = fun.cod_func;

Return would be something like:

nome_func | nome_dep
João      | NULL
Maria     | José
Maria     | Alberto

The function of join is to display data from two different tables in a combined way. This combination must have a condition. In this case the condition is that the dependent table's cod_func is the same as the official table's cod_func.

In the case of the left join, what it does is to display all the records in the table on the left (official) even if the join condition is not satisfied. Therefore, name_dep appears as NULL if John does not have dependents.

score 2 · Answer 2

" Type a command that shows the name of each employee and the name of each dependent that each employee that has dependents

The above statement struck me as somewhat ambiguous. What I understand is that, among employees who have dependents, list the employee's name and the name of the dependent (s).

-- código #1
SELECT F.Nome_Func as Funcionário, 
       D.Nome_Dep as Dependente
  from Funcionario as F
       inner join Dependente as D on D.Cod_Func = F.Cod_Func;

But if the purpose is to list the names of all employees, as well as the name of the respective dependent (s), here's another suggestion:

-- código #2
SELECT F.Nome_Func as Funcionário, 
       IsNull(D.Nome_Dep, '--') as Dependente
  from Funcionario as F
       left join Dependente as D on D.Cod_Func = F.Cod_Func;