sql server programming: See Inline, and boy, this is a strange situation...

--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)

I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org

[quoted text, click to view]
NO, There are 132892 people who own 2 cars, and their names would appear
twice in the total .. so
1037854 - (132892 * 2) = Whatever
[quoted text, click to view]
MAKES SENSE (Except you are using the bad 904962 number which you should
correct as above)
[quoted text, click to view]

[quoted text, click to view]

Why not? How will you run the other SELECTs listed?

Is this some kind of homework assignment?


--
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--

Hello Experts!

The question that I want to have an answer to is how many rows does the
Person table have and how many rows does table Car have.
NOTE!!! I don't have the database so I can't check by using these select
statements.
select count(*) from Person and select count(*) from Car.
It should be possible by using the answer to the four select statements
below to calculate the answer to how many rows table Person have and table
Car have.

I have tried to calculate how many rows these tables have but the answer is
wrong.

Some background that you must have to be able to answer my question.
As I just mentioned we have two tables named Person and Car.
The relation is that one Person can own one or more cars and a car might not
have any owner.

The definition of the two tables create statement looks like this.
create table Person
{
PersonID int NOT NULL identity (1, 1),
PersonNr char(11) NOT NULL,
Forename varchar(50) NOT NULL,
Surname varchar(50) NOT NULL,
City varchar(50) NOT NULL,
Street varchar(50) NOT NULL
}
create table Car
{
carID int NOT NULL IDENTITY(1,1),
Plates varchar(8) NOT NULL,
PersonID int NULL
}

Below are the four select statements that I mentioned about just earlier and
the answer to each.
(select statement a)
select *
from Person P JOIN Car C ON P.PersonID = C.PersonID
(1037854 row(s) affected)

(select statement b)
select PersonNr, count(*)
from Person P JOIN Car C ON P.PersonID = C.PersonID
group by PersonNr
having count(*) > 1
(132892 row(s) affected)

(select statement c)
select PersonNr, count(*)
from Person P JOIN Car C ON P.PersonID = C.PersonID
group by PersonNr
having count(*) > 2
(0 row(s) affected)

(select statement d)
select count(distinct P.PersonID), count(distinct C.CarID)
from Person P OUTER JOIN Car C ON P.PersonID = C.PersonID
where C.CarID IS NULL OR P.PersonID IS NULL
--------------- --------------------
198898 114388
(1 row(s) affected)

I have went through each select statement and have discussed of my own
about to figure out the number of rows in the Person table and the number of
rows in the car table in the following way.

According to the select statement c no Person have more then 2 cars. Do you
agree with me?

Then according to my understanding about the select statements b 132 892
Persons have more then one car but when no person have more then 2 cars
according to select staement c this mean that 132 892 persons have exact 2
cars. Do you agree with me here?
According to select statement a 1037854 Persons have cars but here some
persons might have 2 cars. This means that if you make this calculation
1037854 - 132892 = 904962 will give you the number of Persons that have
exact one car. So here 904962 persons will have exact one car. Do you agree
with me about that?

According to select statement c no person have more then 2 cars. Do you
agree with me?

I mean that in table person exist two kinds of persons. There are persons
that have a relation to a car and persons that doesn't have a releation to a
car.

If you add the number of Persons that have a relation to a car with Persons
that doesn't
have a relation to a car you will get the right answer.
First the numer of Persons that have a car is 904962 persons. Then
according to statement d 198898 persons doesnt't have a relation to a car.
If you sum
these two numbers together you get 904962 + 198898 = 1103860 which would
mean that 1103860 persons exist in table Person.

In table Car one and the same car can't be own by two Persons. This mean
that 904962 persons own one unique car and according to statement d 114388
cars doesn't have any owner. If you sum these two numbers up you get 904962
+ 114388 = 101935 cars.

According to my calculation there would exist 1103860 rows in table Person
and 101935 cars in table Cars. But this answer is wrong.

If you look at URL www.multisoft.se you understand my querstion about the
number of rows in the two tables.When I rather good at database I thougt I
could manage this question.
It's not that I'm going to apply for a job but it would be nice to know why
my answer is wrong.

Have you any suggestion about the number of rows in table Person and Car?


//Tony

See inline

[quoted text, click to view]

Yes

[quoted text, click to view]

Yes (assuming that there have been no data changes in the mean time)

[quoted text, click to view]

No. Query a is not grouped. This means, that there are 1037854 cars
belonging to ? persons. Since you already established that 132892
persons have 2 cars, that would account for 2 * 132892 = 265784 rows in
query a. You also established that no person has more than 2 cars. We
can deduce from that that there must be 1037854 - 265784 = 772070
persons with 1 car.

[quoted text, click to view]

No, see above. There will be 904962 persons with one or more cars.

[quoted text, click to view]

Asked and answered.

[quoted text, click to view]

Statement d is not a valid statement. I assume you meant "full outer
join" or "full join" instead of "outer join".

[quoted text, click to view]

Right.

[quoted text, click to view]

No. There are 772070 persons with one car and 132892 persons with two
cars, and apparently 114388 cars without owner, totallying 772070*1 +
132892*2 + 114388 = 1152242

HTH,
Gert-Jan

[quoted text, click to view]