Hi, locking for the answer to these questions:
1. How many rows contained in table PERSON
2. How many rows contained in teble CAR
Following table contain info about persons. Table do contain a lot of
rows!
CREATE TABLE Person
{
PersonID int NOT NULL IDENTITY(1,1)
PersNumber char(11) NOT NULL,
Name1 varchar(50) NOT NULL,
Name2 varchar(50) NOT NULL,
ShoeSize int NOT NULL,
Address varchar(50) NOT NULL,
Zip varchar(10) NOT NULL,
City varchar(50) NOT NULL
}
Further more, this table containg cars connected to persons in table
above.
CREATE TABLE Car
{
CarID int NOT NULL IDENTITY(1,1),
RegNr varchar(8) NOT NULL,
PersonID int NULL
}
Following SELECT statements are executed:
SELECT *
FROM Person P JOIN Car C ON P.PersonID = C.PersonID
(1037854 rows is affected)
SELECT PersNumber, COUNT(*)
FROM Person P JOIN Car C ON P.PersonID = C.PersonID
GROUP BY PersNumber
HAVING COUNT(*) > 1
(132892 rows are affected)
SELECT PersNumber, COUNT(*)
FROM Person P JOIN Car C ON P.PersonID = C.PersonID
GROUP BY PersNumber
HAVING COUNT(*) > 2
(0 rows are affected)
SELECT COUNT(DISTINCT P. PersonID), COUNT (DISTINCT C.CarID)
FROM Person P FULL OUTER JOIN Car C ON P.PersonID = C.PersonID
WHERE C.CarID IS NULL OR P.PersonID IS NULL
-- --
198898 114388
(1 rows are affected)
Now...the answers to this!
1. How many rows contained in table PERSON
2. How many rows contained in teble CAR
Thanks to all gurus taking time solving this. Please, if you know this
- try to explain your solution!
Thanks again!!
/Markselect count(*) from Car
go
select count(*) from person
--
current location: alicante (es)
"zekevarg" wrote:
> Hi, locking for the answer to these questions:
> 1. How many rows contained in table PERSON
> 2. How many rows contained in teble CAR
> Following table contain info about persons. Table do contain a lot of
> rows!
> CREATE TABLE Person
> {
> PersonID int NOT NULL IDENTITY(1,1)
> PersNumber char(11) NOT NULL,
> Name1 varchar(50) NOT NULL,
> Name2 varchar(50) NOT NULL,
> ShoeSize int NOT NULL,
> Address varchar(50) NOT NULL,
> Zip varchar(10) NOT NULL,
> City varchar(50) NOT NULL
> }
> Further more, this table containg cars connected to persons in table
> above.
> CREATE TABLE Car
> {
> CarID int NOT NULL IDENTITY(1,1),
> RegNr varchar(8) NOT NULL,
> PersonID int NULL
> }
> Following SELECT statements are executed:
> SELECT *
> FROM Person P JOIN Car C ON P.PersonID = C.PersonID
> (1037854 rows is affected)
> SELECT PersNumber, COUNT(*)
> FROM Person P JOIN Car C ON P.PersonID = C.PersonID
> GROUP BY PersNumber
> HAVING COUNT(*) > 1
> (132892 rows are affected)
> SELECT PersNumber, COUNT(*)
> FROM Person P JOIN Car C ON P.PersonID = C.PersonID
> GROUP BY PersNumber
> HAVING COUNT(*) > 2
> (0 rows are affected)
> SELECT COUNT(DISTINCT P. PersonID), COUNT (DISTINCT C.CarID)
> FROM Person P FULL OUTER JOIN Car C ON P.PersonID = C.PersonID
> WHERE C.CarID IS NULL OR P.PersonID IS NULL
> -- --
> 198898 114388
> (1 rows are affected)
>
> Now...the answers to this!
> 1. How many rows contained in table PERSON
> 2. How many rows contained in teble CAR
>
> Thanks to all gurus taking time solving this. Please, if you know this
> - try to explain your solution!
> Thanks again!!
> /Mark
>|||Ok, that answer would have been a bright one only when having
connection to stated tables. In my case i dont. It should be able to
answer only with information above.
Thats the tricky part!
Thanks anyway! :)|||If you want to impress you teacher, tell him/her that answers cannot be
given from the information provided. There are no constraints on these
tables so no assumptions can be made about cardinality. I believe that
primary key, foreign key and unique constraints would all be needed in order
to answer the questions based on query results.
Hope this helps.
Dan Guzman
SQL Server MVP
"zekevarg" <markussteen@.chello.se> wrote in message
news:1142331724.403236.70490@.j52g2000cwj.googlegroups.com...
> Hi, locking for the answer to these questions:
> 1. How many rows contained in table PERSON
> 2. How many rows contained in teble CAR
> Following table contain info about persons. Table do contain a lot of
> rows!
> CREATE TABLE Person
> {
> PersonID int NOT NULL IDENTITY(1,1)
> PersNumber char(11) NOT NULL,
> Name1 varchar(50) NOT NULL,
> Name2 varchar(50) NOT NULL,
> ShoeSize int NOT NULL,
> Address varchar(50) NOT NULL,
> Zip varchar(10) NOT NULL,
> City varchar(50) NOT NULL
> }
> Further more, this table containg cars connected to persons in table
> above.
> CREATE TABLE Car
> {
> CarID int NOT NULL IDENTITY(1,1),
> RegNr varchar(8) NOT NULL,
> PersonID int NULL
> }
> Following SELECT statements are executed:
> SELECT *
> FROM Person P JOIN Car C ON P.PersonID = C.PersonID
> (1037854 rows is affected)
> SELECT PersNumber, COUNT(*)
> FROM Person P JOIN Car C ON P.PersonID = C.PersonID
> GROUP BY PersNumber
> HAVING COUNT(*) > 1
> (132892 rows are affected)
> SELECT PersNumber, COUNT(*)
> FROM Person P JOIN Car C ON P.PersonID = C.PersonID
> GROUP BY PersNumber
> HAVING COUNT(*) > 2
> (0 rows are affected)
> SELECT COUNT(DISTINCT P. PersonID), COUNT (DISTINCT C.CarID)
> FROM Person P FULL OUTER JOIN Car C ON P.PersonID = C.PersonID
> WHERE C.CarID IS NULL OR P.PersonID IS NULL
> -- --
> 198898 114388
> (1 rows are affected)
>
> Now...the answers to this!
> 1. How many rows contained in table PERSON
> 2. How many rows contained in teble CAR
>
> Thanks to all gurus taking time solving this. Please, if you know this
> - try to explain your solution!
> Thanks again!!
> /Mark
>|||Actually, we should have more than enough information here to determine how
many rows are in each table. Table persons has an implied unique
constraint, and we don't need to know the constraints on table car in order
to answer the question.
The data tells us how many:
Cars are owned by a person
Persons have more than one car
Persons have more than two cars
Cars are not owned by a person
Persons do not own a car
All you need to do is add or subtract those values in order to arrive at the
answer.
Not that I am going to outright give the answer, there is somethign to be
said for actually doing your own homework.
This should be enough help to get you in the right direction.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uBfCkv2RGHA.4608@.tk2msftngp13.phx.gbl...
> If you want to impress you teacher, tell him/her that answers cannot be
> given from the information provided. There are no constraints on these
> tables so no assumptions can be made about cardinality. I believe that
> primary key, foreign key and unique constraints would all be needed in
order
> to answer the questions based on query results.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "zekevarg" <markussteen@.chello.se> wrote in message
> news:1142331724.403236.70490@.j52g2000cwj.googlegroups.com...
>|||Wrong, sorry. It's possible to answer only with given info.
Use affected rows as hint.|||Jim Underwood wrote:
> Actually, we should have more than enough information here to determine ho
w
> many rows are in each table. Table persons has an implied unique
> constraint, and we don't need to know the constraints on table car in orde
r
> to answer the question.
There are no constraints. The tables have IDENTITY columns but that
doesn't mean they have keys. Dan is right. On the information given
there is no way to be sure how many rows in each table.
In particular if PersonID isn't unique then the first two queries may
contain duplicates and so we can't be sure of the number of rows in the
base tables. The FULL JOIN query on the other hand only tells us the
number of distinct values, not the number of rows.
An good example of why keys are important.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||PersonID is an IDENTITY field, therefore it is unique.
I agree that identity does not qualify as a constraint/PK by general DBMS
terms, however we know that in SQL Server IDENTITY is always unique. This
is why I referred to it a an IMPLIED unique constraint.
The example is an academic one, a test of logic and DBMS knowledge, not an
ansi standards test.
If the PersonID was not an IDENTITY field then you would be correct, but by
definition IDENTITY is unique, no matter how much you or I may disapprove of
its use here. I would much prefer to see unique constraints explicitly
defined, but that does not change the fact that one has been implicitly
created by SQL Server, even if it is proprietary.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1142349407.116675.165460@.v46g2000cwv.googlegroups.com...
> Jim Underwood wrote:
>
how
order
> There are no constraints. The tables have IDENTITY columns but that
> doesn't mean they have keys. Dan is right. On the information given
> there is no way to be sure how many rows in each table.
> In particular if PersonID isn't unique then the first two queries may
> contain duplicates and so we can't be sure of the number of rows in the
> base tables. The FULL JOIN query on the other hand only tells us the
> number of distinct values, not the number of rows.
> An good example of why keys are important.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Jim Underwood wrote:
> PersonID is an IDENTITY field, therefore it is unique.
> I agree that identity does not qualify as a constraint/PK by general DBMS
> terms, however we know that in SQL Server IDENTITY is always unique. This
> is why I referred to it a an IMPLIED unique constraint.
Rubbish!
CREATE TABLE T1 (x INT IDENTITY);
SET IDENTITY_INSERT T1 ON;
INSERT INTO T1 (x) VALUES (1);
INSERT INTO T1 (x) VALUES (1);
SET IDENTITY_INSERT T1 ON;
GO
CREATE TABLE T2 (x INT IDENTITY);
INSERT INTO T2 DEFAULT VALUES;
DBCC CHECKIDENT (T2,RESEED,0);
INSERT INTO T2 DEFAULT VALUES;
GO
SELECT x FROM T1;
SELECT x FROM T2;
Result:
x
--
1
1
(2 row(s) affected)
x
--
1
1
(2 row(s) affected)
As for testing DBMS knowledge, I'll bet that plenty of people reading
this can testify to experience of non-unique IDENTITY columns.
Certainly I have known of real examples.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||My humble apologies.
I have indeed shown my ignorance in this regard.
Thank you for setting me straight.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1142351997.595277.210040@.p10g2000cwp.googlegroups.com...
> Jim Underwood wrote:
DBMS
This
> Rubbish!
> CREATE TABLE T1 (x INT IDENTITY);
> SET IDENTITY_INSERT T1 ON;
> INSERT INTO T1 (x) VALUES (1);
> INSERT INTO T1 (x) VALUES (1);
> SET IDENTITY_INSERT T1 ON;
> GO
> CREATE TABLE T2 (x INT IDENTITY);
> INSERT INTO T2 DEFAULT VALUES;
> DBCC CHECKIDENT (T2,RESEED,0);
> INSERT INTO T2 DEFAULT VALUES;
> GO
> SELECT x FROM T1;
> SELECT x FROM T2;
> Result:
> x
> --
> 1
> 1
> (2 row(s) affected)
> x
> --
> 1
> 1
> (2 row(s) affected)
> As for testing DBMS knowledge, I'll bet that plenty of people reading
> this can testify to experience of non-unique IDENTITY columns.
> Certainly I have known of real examples.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
in class.