Doubt SQL Server

Asked

Viewed 95 times

-1

Guys I’m having a really hard time with this question Can someone 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'))
)

http://prntscr.com/d268fa http://prntscr.com/d2684x

sql-server
  • 1

    This "tutorial" video shows how to do this and even with the same example, speaking of employee and dependent/manager and employee https://www.youtube.com/watch?v=jl24CvKkOyw

  • What part do you have problems with? Any mistakes with the code you have? Can you explain what you want to do? (so the question becomes clearer)

2 answers

2

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;

The 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 manner. This combination must have one condition. In this case the condition is that the cod_func of the dependent table is the same as the cod_func of the working table.

In the case of left Join what it does is to display all the records of the table on the left (functio) even if the condition of Join is not satisteite. Therefore name_dep appears as NULL if John has no dependents.

2

“Write a command that shows the name of each employee and the name of each dependant that each employee you have depended on”

The above statement seemed a little ambiguous. What I understood is that, among employees who have dependents, list the name of the employee and the name of the dependent(s)(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 goal is to list the name of all employees as well as the name(s) of the respective(s) dependent(s), here is 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;

Browser other questions tagged sql-server

You are not signed in. Login or sign up in order to post.