Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1868 > unrolled thread

Sql question

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  Sql question "Tony Johansson" <johansson.andersson@telia.com> - 2015-01-30 12:29 +0100
    Re: Sql question Lennart Jonsson <erik.lennart.jonsson@gmail.com> - 2015-01-30 20:14 +0100
      Re: Sql question rja.carnegie@gmail.com - 2015-02-01 22:16 -0800
        Re: Sql question Lennart Jonsson <erik.lennart.jonsson@gmail.com> - 2015-02-02 08:45 +0100
        Re: Sql question "Tony Johansson" <johansson.andersson@telia.com> - 2015-02-03 01:06 +0100
          Re: Sql question rja.carnegie@gmail.com - 2015-02-03 09:06 -0800

#1868 — Sql question

I practiceing sql question by using Kihlman's SQL webbsite
pk = primary key
fk = forign key

I have these tables
Firm(FirKey, FirName) pk is FirKey
Salary(SalWages, SalTaxYear, FirKey, EmpId) pk is (FirKey, EmpId, 
SalTaxYear)   fk is FirKey and EmpId
Employee(EmpId, EmpFirstName, EmpLastName, EmpPartner,TaxId, EmpLastTaxRaid) 
pk is EmpId    fk is TaxId, EmpPartner
TaxDistrict(TaxId, TaxName, TaxPercentage) pk is TaxId

The relation is many to many between Firm and Employee so we get an extra 
relation table in between that we have named Salary
We have relation one to many from TaxDistrict to Employee
We have also a self join in table Employee for column EmpPartner so 
EmpPartner will become a forign key here I suppose

Firm (1) ........(N)Salary(N) .............. (1) Employee(N) 
................(1)TaxDistrict

Here we have a self join
Employee.EmpId (1) .................(N) Employee.EmpPartner

I want to write a SQL question that will give a result that satisfy this 
question
The tax authority suspect person with id number 3 to be a bigamist! Find all 
information in the Employee table about all his wifes.

I tries with this
SELECT * FROM Employee a1 Employee a2 WHERE a1.EmpId = a2.EmpPartner AND 
a2.EmpId = 3;

I get this error when I try the SQL question above.
Tables are wrong: The number of tables is correct but at least one table is 
not correct
Conditions are wrong: Conditions are expected but too many have been written

I also mean that according to the relation straucture an Employee can't have 
two EmpPartner because that would mean
that we have two EmpId with the same primary key

So can somebody tell me how I should write the SQL question to satisfy this. 
"The tax authority suspect person with id number 3 to be a bigamist! Find 
all information in the Employee table about all his wifes".
Obviously my SQL question is not correct according to how Kihlman's SQL want 
to have it.


//Tony

[toc] | [next] | [standalone]

#1869

On 01/30/2015 12:29 PM, Tony Johansson wrote:
> I practiceing sql question by using Kihlman's SQL webbsite
> pk = primary key
> fk = forign key
>
> I have these tables
> Firm(FirKey, FirName) pk is FirKey
> Salary(SalWages, SalTaxYear, FirKey, EmpId) pk is (FirKey, EmpId,
> SalTaxYear)   fk is FirKey and EmpId
> Employee(EmpId, EmpFirstName, EmpLastName, EmpPartner,TaxId, EmpLastTaxRaid)
> pk is EmpId    fk is TaxId, EmpPartner
> TaxDistrict(TaxId, TaxName, TaxPercentage) pk is TaxId
>
> The relation is many to many between Firm and Employee so we get an extra
> relation table in between that we have named Salary
> We have relation one to many from TaxDistrict to Employee
> We have also a self join in table Employee for column EmpPartner so
> EmpPartner will become a forign key here I suppose
>
> Firm (1) ........(N)Salary(N) .............. (1) Employee(N)
> ................(1)TaxDistrict
>
> Here we have a self join
> Employee.EmpId (1) .................(N) Employee.EmpPartner
>
> I want to write a SQL question that will give a result that satisfy this
> question
> The tax authority suspect person with id number 3 to be a bigamist! Find all
> information in the Employee table about all his wifes.
>
> I tries with this
> SELECT * FROM Employee a1 Employee a2 WHERE a1.EmpId = a2.EmpPartner AND
> a2.EmpId = 3;
>

I suspect you mean:

SELECT * FROM Employee a1 , Employee a2 WHERE a1.EmpId = a2.EmpPartner
                          ^^^
AND a2.EmpId = 3;

However I would suggest you use an explicit join:

SELECT a1.*  -- I assume you are only interested in a1
FROM Employee a1
JOIN Employee a2
     ON a1.EmpId = a2.EmpPartner
WHERE a2.EmpId = 3;

/Lennart

[...]

[toc] | [prev] | [next] | [standalone]

#1871

On Friday, 30 January 2015 19:14:38 UTC, Lennart Jonsson  wrote:
> On 01/30/2015 12:29 PM, Tony Johansson wrote:
> > I practiceing sql question by using Kihlman's SQL webbsite
> > pk = primary key
> > fk = forign key
> >
> > I have these tables
> > Firm(FirKey, FirName) pk is FirKey
> > Salary(SalWages, SalTaxYear, FirKey, EmpId) pk is (FirKey, EmpId,
> > SalTaxYear)   fk is FirKey and EmpId
> > Employee(EmpId, EmpFirstName, EmpLastName, EmpPartner,TaxId, EmpLastTaxRaid)
> > pk is EmpId    fk is TaxId, EmpPartner
> > TaxDistrict(TaxId, TaxName, TaxPercentage) pk is TaxId
> >
> > The relation is many to many between Firm and Employee so we get an extra
> > relation table in between that we have named Salary
> > We have relation one to many from TaxDistrict to Employee
> > We have also a self join in table Employee for column EmpPartner so
> > EmpPartner will become a forign key here I suppose
> >
> > Firm (1) ........(N)Salary(N) .............. (1) Employee(N)
> > ................(1)TaxDistrict
> >
> > Here we have a self join
> > Employee.EmpId (1) .................(N) Employee.EmpPartner
> >
> > I want to write a SQL question that will give a result that satisfy this
> > question
> > The tax authority suspect person with id number 3 to be a bigamist! Find all
> > information in the Employee table about all his wifes.
> >
> > I tries with this
> > SELECT * FROM Employee a1 Employee a2 WHERE a1.EmpId = a2.EmpPartner AND
> > a2.EmpId = 3;
> >
> 
> I suspect you mean:
> 
> SELECT * FROM Employee a1 , Employee a2 WHERE a1.EmpId = a2.EmpPartner
>                           ^^^
> AND a2.EmpId = 3;
> 
> However I would suggest you use an explicit join:
> 
> SELECT a1.*  -- I assume you are only interested in a1
> FROM Employee a1
> JOIN Employee a2
>      ON a1.EmpId = a2.EmpPartner
> WHERE a2.EmpId = 3;
> 
> /Lennart

I wonder if you need to write the statement as a JOIN
given that we know we're interested in "wives" of 
employee id = 3.  Why not just:

SELECT * FROM Employee WHERE EmpPartner = 3;

This is a strange problem in many ways.  One database
seems to contain multiple firms and their employees,
and the tax authorities get to use it as well?
And it's implied that any possible wife is recorded
in the employee table?

The names - except for Frim - are more suited to 
a single company's database of employees and, 
possibly, dependents.  But the use of it is 
more like a table of IncomeTaxPayer and another
of Employer.

Since my country doesn't rely on employment 
health insurance, and practises personal data
protection, maybe I don't see how it is
natural for a company to record all of an
employee's family details and hand them to 
the government.

Anyway, Employee 3 can only claim one EmpPartner - 
if that's the code that represents marriage, 
civil partnership, and other cohabitation for
tax purpose, and if we now treat the Employee
record as analogous to a tax form - and 
you'd expect the database to be designed
to enforce integrity of the marriage relationship
...maybe.

Enforcement would meant that only one Employee - 
one wife - can have EmpPartner = 3.

I'm not sure how to write that rule.

Anyway, maybe the database designer anticipated
legal polygamy if Mitt Romney had won.

Real life is more complicated; Employee 3 may
have gotten divorced and then married in one
tax year, I guess.  I don't know what that 
looks like on the form: we also have widely 
used tax-deducted-from-salary in my country.

I also suspect it's obvious on the form 
anyway if you claim to have more than one
wife.

It's not a great fault if learn-simple-SQL
teaching materials have the connection to 
reality of life events in Sims or Monopoly
(when am I going to win a beauty contest??),
but it makes it feel less real and less 
important - and, in this case, it's harder
to translate the implausible question into
an operation on the unrealistic database.

If the question is: "Employee with id 3 
has been married more than once.  Find
all information about his wife and his 
ex-wives", I think that may actually 
be clearer - and in fact it does require
that you involve the record of employee 3
to identify his current wife.

[toc] | [prev] | [next] | [standalone]

#1872

On 2015-02-02 07:16, rja.carnegie@gmail.com wrote:
[...]
> I wonder if you need to write the statement as a JOIN
> given that we know we're interested in "wives" of
> employee id = 3.  Why not just:
>
> SELECT * FROM Employee WHERE EmpPartner = 3;
>

Yes, good observation


> This is a strange problem in many ways.  One database
> seems to contain multiple firms and their employees,
> and the tax authorities get to use it as well?
> And it's implied that any possible wife is recorded
> in the employee table?
>

Agreed


[...]

>
> Enforcement would meant that only one Employee -
> one wife - can have EmpPartner = 3.
>
> I'm not sure how to write that rule.
>

UNIQUE ( EmpPartner );


> Anyway, maybe the database designer anticipated
> legal polygamy if Mitt Romney had won.
>

:-)

[...]

[toc] | [prev] | [next] | [standalone]

#1874

Yes when I used this answer
SELECT * FROM Employee WHERE EmpPartner = 3;

then Kihlman's SDQl was happy

//Tony

<rja.carnegie@gmail.com> skrev i meddelandet 
news:1f5aad6b-a99e-48c3-adde-5f72eea5793b@googlegroups.com...
> On Friday, 30 January 2015 19:14:38 UTC, Lennart Jonsson  wrote:
>> On 01/30/2015 12:29 PM, Tony Johansson wrote:
>> > I practiceing sql question by using Kihlman's SQL webbsite
>> > pk = primary key
>> > fk = forign key
>> >
>> > I have these tables
>> > Firm(FirKey, FirName) pk is FirKey
>> > Salary(SalWages, SalTaxYear, FirKey, EmpId) pk is (FirKey, EmpId,
>> > SalTaxYear)   fk is FirKey and EmpId
>> > Employee(EmpId, EmpFirstName, EmpLastName, EmpPartner,TaxId, 
>> > EmpLastTaxRaid)
>> > pk is EmpId    fk is TaxId, EmpPartner
>> > TaxDistrict(TaxId, TaxName, TaxPercentage) pk is TaxId
>> >
>> > The relation is many to many between Firm and Employee so we get an 
>> > extra
>> > relation table in between that we have named Salary
>> > We have relation one to many from TaxDistrict to Employee
>> > We have also a self join in table Employee for column EmpPartner so
>> > EmpPartner will become a forign key here I suppose
>> >
>> > Firm (1) ........(N)Salary(N) .............. (1) Employee(N)
>> > ................(1)TaxDistrict
>> >
>> > Here we have a self join
>> > Employee.EmpId (1) .................(N) Employee.EmpPartner
>> >
>> > I want to write a SQL question that will give a result that satisfy 
>> > this
>> > question
>> > The tax authority suspect person with id number 3 to be a bigamist! 
>> > Find all
>> > information in the Employee table about all his wifes.
>> >
>> > I tries with this
>> > SELECT * FROM Employee a1 Employee a2 WHERE a1.EmpId = a2.EmpPartner 
>> > AND
>> > a2.EmpId = 3;
>> >
>>
>> I suspect you mean:
>>
>> SELECT * FROM Employee a1 , Employee a2 WHERE a1.EmpId = a2.EmpPartner
>>                           ^^^
>> AND a2.EmpId = 3;
>>
>> However I would suggest you use an explicit join:
>>
>> SELECT a1.*  -- I assume you are only interested in a1
>> FROM Employee a1
>> JOIN Employee a2
>>      ON a1.EmpId = a2.EmpPartner
>> WHERE a2.EmpId = 3;
>>
>> /Lennart
>
> I wonder if you need to write the statement as a JOIN
> given that we know we're interested in "wives" of
> employee id = 3.  Why not just:
>
> SELECT * FROM Employee WHERE EmpPartner = 3;
>
> This is a strange problem in many ways.  One database
> seems to contain multiple firms and their employees,
> and the tax authorities get to use it as well?
> And it's implied that any possible wife is recorded
> in the employee table?
>
> The names - except for Frim - are more suited to
> a single company's database of employees and,
> possibly, dependents.  But the use of it is
> more like a table of IncomeTaxPayer and another
> of Employer.
>
> Since my country doesn't rely on employment
> health insurance, and practises personal data
> protection, maybe I don't see how it is
> natural for a company to record all of an
> employee's family details and hand them to
> the government.
>
> Anyway, Employee 3 can only claim one EmpPartner -
> if that's the code that represents marriage,
> civil partnership, and other cohabitation for
> tax purpose, and if we now treat the Employee
> record as analogous to a tax form - and
> you'd expect the database to be designed
> to enforce integrity of the marriage relationship
> ...maybe.
>
> Enforcement would meant that only one Employee -
> one wife - can have EmpPartner = 3.
>
> I'm not sure how to write that rule.
>
> Anyway, maybe the database designer anticipated
> legal polygamy if Mitt Romney had won.
>
> Real life is more complicated; Employee 3 may
> have gotten divorced and then married in one
> tax year, I guess.  I don't know what that
> looks like on the form: we also have widely
> used tax-deducted-from-salary in my country.
>
> I also suspect it's obvious on the form
> anyway if you claim to have more than one
> wife.
>
> It's not a great fault if learn-simple-SQL
> teaching materials have the connection to
> reality of life events in Sims or Monopoly
> (when am I going to win a beauty contest??),
> but it makes it feel less real and less
> important - and, in this case, it's harder
> to translate the implausible question into
> an operation on the unrealistic database.
>
> If the question is: "Employee with id 3
> has been married more than once.  Find
> all information about his wife and his
> ex-wives", I think that may actually
> be clearer - and in fact it does require
> that you involve the record of employee 3
> to identify his current wife. 

[toc] | [prev] | [next] | [standalone]

#1875

On Tuesday, 3 February 2015 00:06:33 UTC, Tony Johansson  wrote:
> Yes when I used this answer
> SELECT * FROM Employee WHERE EmpPartner = 3;
> 
> then Kihlman's SDQl was happy
> 
> //Tony

Maybe the "wives" were less happy...

Having checked, I see I should have asked about Monopoly:
when am I going to win /second prize/ in a beauty contest?

Maybe when only we three have entered.  :-)

<http://www.investopedia.com/articles/basics/12/monopoly-terrible-finance-teacher.asp>

See how many criticisms of the game you don't 
actually agree with!

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web