Challenge me! -- sql server programming

Shailesh Humbad
1/26/2004 3:57:57 AM

A truck drives around Boston recording latitude and longitude every
ten seconds. The trouble is, the GPS receiver sometimes gives
innaccurate or missing readings. Suppose the innaccurate readings are
eliminated by an estimate of maximum acceleration, leaving only
missing readings. The problem remains to interpolate values for the
missing readings so as to obtain a smooth plot of the truck's movements.

I unfortunately no longer have the solution, but the interpolation
step was actually solved with a single SQL query (on Oracle). I gave
it to my database guru to figure out, and it sure made his day to get
such a challenging problem.

Tab delimited sample data available here:

http://www.somacon.com/samplelatlon.tab

Remember, I no longer have a solution. Have fun.

Shailesh

[quoted text, click to view]

David Portas
1/26/2004 7:02:59 AM

CREATE TABLE Clients (clientid INTEGER PRIMARY KEY, clientname VARCHAR(10)
NOT NULL UNIQUE)

CREATE TABLE Orders (orderid INTEGER PRIMARY KEY, clientid INTEGER NOT NULL
REFERENCES Clients (clientid), orderdate DATETIME NOT NULL)

SELECT C.clientid, C.clientname
FROM Orders AS O
JOIN Clients AS C
ON O.clientid = C.clientid
WHERE O.orderdate >= '19940101' AND O.orderdate < '20040101'
GROUP BY c.clientid, C.clientname
HAVING COUNT(DISTINCT YEAR(O.orderdate))=10

The best way to specify a problem (challenging or otherwise) is to include
DDL, sample data and show your required result.
http://www.aspfaq.com/5006

--
David Portas
SQL Server MVP
--

Joe Celko
1/26/2004 8:13:09 AM

[quoted text, click to view]
Please? <<

1) Buy a copy of my SQL PUZZLES & ANSWERS (the sales stink and I need
the money).

2) Go to the DBMS and DATABASE PROGRAMMING & DESIGN archives at the
INTELLIGENT ENTERPRISE website. I used to end my columns in those two
magazines with a SQL puzzle. A lot of them made it into the book, but a
lot did not.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***

Uri Dimant
1/26/2004 8:34:48 AM

Pierre
SELECT c.ClientID,ClientName FROM Client C LEFT JOIN Order O ON
C.ClientID=O.ClientID
WHERE OrderDate IS NOT NULL

OR

SELECT * FROM Client WHERE EXISTS
(
SELECT * FROM Order WHERE Client.ClientID=Order.ClientID AND OrderDate IS
NOT NULL
)

[quoted text, click to view]
will win... uh... the respect from all of us! But please, post me your
interesting SQL problems WITHOUT a solution - I want to give them a go!
[quoted text, click to view]
Largest Community Website: http://www.sqlJunkies.com/newsgroups/

Dandy Weyn
1/26/2004 8:35:01 AM

Challenge :

create the fastest method to
store all possible ids in a format of AAA000 upto ZZZ999 into a table
exceptions, idss starting with M can not be used,
ids that are in the additional exception list can not be used either.
then select from the id table a random value
actually these ids are belgian carplates

Regards,

Dandy Weyn
MCSE, MCSA, MCDBA, MCT

www.dandyman.net
[quoted text, click to view]
will win... uh... the respect from all of us! But please, post me your
interesting SQL problems WITHOUT a solution - I want to give them a go!
[quoted text, click to view]
Largest Community Website: http://www.sqlJunkies.com/newsgroups/

Joe Celko
1/26/2004 8:43:06 AM

[quoted text, click to view]
exceptions, idss starting with M can not be used,
ids that are in the additional exception list can not be used either. <<

That pattern is also used in the US.

CREATE TABLE Digits
(d INTEGER NOT NULL PRIMARY KEY
CHECK (d BETWEEN 0 AND 9);
-- insert zero to nine

CREATE TABLE Alpha
(a CHAR(1) NOT NULL PRIMARY KEY
CHECK (a BETWEEN 'A' AND 'Z';
--insert A thru Z

CREATE TABLE ForbiddenList
(bad_tag CHAR(6) NOT NULL PRIMARY KEY);
--insert the bad tag numbers

CREATE TABLE Tags
(tag_nbr CHAR(6) NOT NULL PRIMARY KEY
CHECK tag_nbr
LIKE '[A-Z][A-Z][A-Z][0-9][0-9][0-9]'
AND tat_nbr
NOT LIKE 'M[A-Z][A-Z][0-9][0-9][0-9]');

INSERT INTO Tags
SELECT A1.a + A2.a + A3.a + D1.d + D2.d + D3.d
FROM Alpha AS A1, Alpha AS A2, Alpha AS A3,
Digits AS D1, Digits AS D2, Digits AS D3
WHERE A1.a <> 'M'
AND A1.a + A2.a + A3.a + D1.d + D2.d + D3.d
NOT IN (SELECT bad_tag FROM ForbiddenList);

If we had full SQL-92, then we could add a predicate to Tags that would
handle the Forbidden tag list.

[quoted text, click to view]

This is not a relational operation, so it should be done in the front
end. If you add a sequential number in a column to tags, so as to make
this table into a sequential file (ugh!!), then you can use this:

SELECT S1.*
FROM Tags AS T1
WHERE CEILING((SELECT COUNT(*) FROM Tags)
* RAND())
=(SELECT COUNT(*)
FROM Tags AS T2
WHERE T1.seq <= T2.seq);

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***

Jacco Schalkwijk
1/26/2004 9:37:26 AM

From SQL Server MVP Erland Sommarskog:

http://tinyurl.com/pgoi

My solution to his challenge is included in the thread though, so you will
have to look at the first 2 posts only :-)

--
Jacco Schalkwijk
SQL Server MVP

[quoted text, click to view]
will win... uh... the respect from all of us! But please, post me your
interesting SQL problems WITHOUT a solution - I want to give them a go!
[quoted text, click to view]
Largest Community Website: http://www.sqlJunkies.com/newsgroups/

David Portas
1/26/2004 9:38:24 AM

Take a look at some of Itzik Ben-Gan's articles in SQL Server Magazine:
http://www.sqlmag.com/Articles/Index.cfm?DepartmentID=743

Also Celko's SQL Puzzles and Answers and SQL for Smarties:
http://tinyurl.com/353dw
http://tinyurl.com/2g78n

--
David Portas
SQL Server MVP
--

Delbert Glass
1/26/2004 10:39:36 AM

Write the best query you can for this post.

From: Girish (gbajaj@tietronixinc.com)
Subject: Efficient Query Design for problem
Newsgroups: microsoft.public.sqlserver.programming
Date: 2002-11-04 16:58:38 PST

without reading any of the rest of thread.

After which can you read the thread up to
but not including the posts of Nov 6.
Then improve on your query if you can.

And then finally compare your best query to the final query I posted.
(Actually I should say queries.)

Bye,
Delbert Glass

[quoted text, click to view]
will win... uh... the respect from all of us! But please, post me your
interesting SQL problems WITHOUT a solution - I want to give them a go!
[quoted text, click to view]
Largest Community Website: http://www.sqlJunkies.com/newsgroups/

Steve Kass
1/26/2004 10:55:45 AM

Pierre,

Here's some DDL and a relatively efficient solution using an indexed view:

CREATE TABLE Clients (
clientid CHAR(1) primary key
)
insert into Clients
select distinct left(CustomerID,1) from Northwind..Customers

CREATE TABLE Orders (
orderid INTEGER PRIMARY KEY NONCLUSTERED,
clientid CHAR(1) NOT NULL
REFERENCES Clients (clientid),
orderdate DATETIME NOT NULL
)
create index Orders_nci on Orders(clientid,orderdate)
insert into Orders
select
OrderID,
left(CustomerID,1),
cast('19930101' as datetime) + Freight*80
from Northwind..Orders
go

create view ClientYears with schemabinding as
select clientid, year(orderdate) as yr, count_big(*) as cnt
from dbo.Orders
group by clientid, year(orderdate)
go

create unique clustered index ClientYears_ci
on ClientYears(clientid,yr)
go

declare @yrs int
set @yrs = 10
declare @thisyear datetime
set @thisyear = 2004

select clientid
from ClientYears with (noexpand)
where yr between @thisyear-@yrs
and @thisyear-1
group by clientid
having count(*) = @yrs
go

drop view ClientYears
drop table Orders, Clients

SK
[quoted text, click to view]
will win... uh... the respect from all of us! But please, post me your
interesting SQL problems WITHOUT a solution - I want to give them a go!
[quoted text, click to view]
Largest Community Website: http://www.sqlJunkies.com/newsgroups/

Steve Kass
1/26/2004 11:49:06 AM

If Digits.d is declared as an integer, + will not concatenate.

SK

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : Challenge me!