Armenian Knowledge Base  

Go Back   Armenian Knowledge Base > Technical sections > Languages, Compilers, Interpreters > Algorithms
Register

Reply
 
LinkBack Thread Tools
Old 21.07.2005, 18:54   #1
Silence
 
Moonlight's Avatar
 
Join Date: 11 2004
Location: back to meta level
Posts: 3,926
Downloads: 1
Uploads: 0
Blog Entries: 3
Reputation: 202 | 3
Default sql task. sos plz!

Description of the application. The following relational schema is used to maintain stock
price information.
stocks (skey, issued)

For each stock in the database, a unique identifier (skey, data type varchar2(10)) and
the date the stock was issued (issued, data type date) is stored.

stockdev (skey, tdate, price)

This table stores the time series of the diferent stock prices. For each stock referenced
by skey and trading day (tdate, data type date) the price (price, data type number)
is stored.

Compute the covariance of the time series of the stocks 'oracle' and 'ibm'.
Hint: In order to compute the covariance of two random variables you may use the
formula cov(X; Y ) = E(X  Y )
---------------------------
i got some ideas, but noth comes out

SELECT (SUM ( x * y ) - SUM ( y ) * SUM ( x ) * SUM ( x ) / n) / n

FROM stockdev
where ....?

but duno how to tell, that x is skey "oracle'-s price & y - ibm's. price1, price2??

was hinted to put subqueries into the cov formula:

SELECT (SUM (select price from stockdev where skey='oracle' AND tdate BETWEEN issuedate AND ??

or group by tdate?

how to tell that it's time series & not 1
can one say MAX(date) meaning the last date?
no, you should sort in order to get last date
Silence (07:37 PM) :
nasty.. group by won't do?\
Reply With Quote
Old 21.07.2005, 19:00   #2
Silence
 
Moonlight's Avatar
 
Join Date: 11 2004
Location: back to meta level
Posts: 3,926
Downloads: 1
Uploads: 0
Blog Entries: 3
Reputation: 202 | 3
Default

it's not even readable. task is here: http://iwi.wiwi.hu-berlin.de/downloa..._2003s_KL2.pdf

2. (d)

table stockdev should be created smth like this:

CREATE table stockdev (
skey varchar2 (10), NOT NULL,
tdate date 'dd.mm.yyyy', NOT NULL,
price number(6,2)
);
Reply With Quote
Old 21.07.2005, 19:33   #3
Silence
 
Moonlight's Avatar
 
Join Date: 11 2004
Location: back to meta level
Posts: 3,926
Downloads: 1
Uploads: 0
Blog Entries: 3
Reputation: 202 | 3
Default

chto-t rodila

SELECT (SUM ( ibm.price * oracle.price ) - SUM ( oracle.price ) * SUM ( ibm.price ) * SUM ( ibm.price ) / n) / n
FROM stockdev
where ibm.skey='ibm' AND oracle.skey='oracle'
......... ;

mi xoskov ostaetca serialize it. sdelat' eto s issue date do current date. examen zavtra utrom, do etogo any help is appreciated
Reply With Quote
Old 21.07.2005, 20:32   #4
ЙЦУКЕН
 
Join Date: 07 2002
Location: 0x68,0x69,0x72, 0x69,0x6e,0x67, 0x20,0x6e,0x6f, 0x77
Age: 47
Posts: 3,118
Downloads: 0
Uploads: 0
Reputation: 5 | 0
Default

какой sql сервер?
Reply With Quote
Old 21.07.2005, 20:40   #5
Silence
 
Moonlight's Avatar
 
Join Date: 11 2004
Location: back to meta level
Posts: 3,926
Downloads: 1
Uploads: 0
Blog Entries: 3
Reputation: 202 | 3
Default

libo oracle libo mysql. ne vajno, doljni pisat' na bumajke ya privikla po oracle.
viju pm, thx, sha postuchus'
Reply With Quote
Old 21.07.2005, 21:38   #6
Младенец
 
Karinchik's Avatar
 
Join Date: 07 2005
Location: Massachusetts
Age: 43
Posts: 1
Downloads: 0
Uploads: 0
Reputation: 0 | 0
Default

a) the table creation is corect dont' forget to crate constrain on stockdev where skey is a foreign key on stocks
b) Select AVG(stockdev.price) from stockdev, stocks where stockdev.skey = stocks.skey and stocks.issued < '01/01/2003' on the date you might have to put the to_date format but it should work without date format as well
c) Update stockdev set stockdev.price = 0.11 where stockdev.tdate like (select Max(stocks.issued) from stocks where stocks.skey = 'sun')

esli ya voprosi konechno pravil'no ponyala. Naschet d) u menya chego to mozgi gluchat i ne mogu ponyat' chto imenno trebuetsya. item c) mojno po raznomu napisat', but this is one of the options.

Item d) es k mtatsem, vor helks glhis ga k patashanem
Reply With Quote
Old 21.07.2005, 21:44   #7
ЙЦУКЕН
 
Join Date: 07 2002
Location: 0x68,0x69,0x72, 0x69,0x6e,0x67, 0x20,0x6e,0x6f, 0x77
Age: 47
Posts: 3,118
Downloads: 0
Uploads: 0
Reputation: 5 | 0
Default

Quote:
Originally Posted by Karinchik
.......
c)
UPDATE stockdev
SET stockdev.price = 0.11
WHERE stockdev.tdate LIKE (
SELECT MAX(stocks.issued) FROM stocks WHERE stocks.skey = 'sun'
)
...............

не, не правильно
Reply With Quote
Old 21.07.2005, 22:10   #8
Silence
 
Moonlight's Avatar
 
Join Date: 11 2004
Location: back to meta level
Posts: 3,926
Downloads: 1
Uploads: 0
Blog Entries: 3
Reputation: 202 | 3
Default

Karinchik, thx for help! for a) nm had already reminded me of pk & fk-s, it's fixed
Quote:
Originally Posted by Karinchik
c) Update stockdev set stockdev.price = 0.11 where stockdev.tdate like (select Max(stocks.issued) from stocks where stocks.skey = 'sun')
i wrote it so:

INSERT INTO stockdev values ('sun', stocks.issued, '0.01');

but i'm not sure on value for date. uchitel' nm, podskajite?:P

a d toje uje reshili, k moemu last trial nado bilo dobavit' where cond, that dates r =. nm, esho raz senks viydet tak:
SELECT (SUM ( ibm.price * oracle.price ) - SUM ( oracle.price ) * SUM ( ibm.price ) * SUM ( ibm.price ) / COUNT(tdate)) / COUNT(tdate)
FROM stockdev ibm, stockdev oracle
where ibm.skey='ibm' AND oracle.skey='oracle' AND ibm.tdate=oracle.tdate;

ili esho ludshe:
SELECT ( avg( ibm.price * o.price)-( avg(ibm.price) * avg(o.price) ) )
FROM stockdev ibm, stockdev o
WHERE ibm.skey='ibm' AND o.skey='oracle' AND ibm.tdate=o.tdate;
Reply With Quote
Old 21.07.2005, 22:40   #9
Профессор
 
Nikita's Avatar
 
Join Date: 01 2005
Location: Perm
Age: 38
Posts: 2,142
Downloads: 0
Uploads: 0
Reputation: 0 | 0
Default

Ты теперь художник/поэт или SQL-ом занимешся ?
Reply With Quote
Old 21.07.2005, 22:48   #10
Silence
 
Moonlight's Avatar
 
Join Date: 11 2004
Location: back to meta level
Posts: 3,926
Downloads: 1
Uploads: 0
Blog Entries: 3
Reputation: 202 | 3
Default

kajetca ya nepravil'no ponela task c), trebuet ne dobavlyat' esho 1 tuple into table, a update uje sushestvuyushiy. u Karinchik bolee blizko, poprobuyu escho:

UPDATE stockdev
set price = 0.11
where skey = 'sun' AND tdate=issued;

no issued tut iz drugovo tablea, so doljni select data from both tables.. stocks.issued itd, no u menya uje boshka bum bum, utro vechera mudrenee vsem pasibki i spoki nochi
Reply With Quote
Old 21.07.2005, 22:52   #11
Silence
 
Moonlight's Avatar
 
Join Date: 11 2004
Location: back to meta level
Posts: 3,926
Downloads: 1
Uploads: 0
Blog Entries: 3
Reputation: 202 | 3
Default

Quote:
Originally Posted by accemic26
Ты теперь художник/поэт или SQL-ом занимешся ?
ya teper' studentka, u kogo 4 majora, vibiray po svoemu vkusu a mojet ludshe zadachi poreshish? u menya ix esho mnogo.. php geeks r also welcome
Reply With Quote
Old 22.07.2005, 07:49   #12
Профессор
 
Nikita's Avatar
 
Join Date: 01 2005
Location: Perm
Age: 38
Posts: 2,142
Downloads: 0
Uploads: 0
Reputation: 0 | 0
Default

что есть студентка у которой 4-е мажера ?
Reply With Quote
Old 22.07.2005, 08:42   #13
Профессор
 
Nikita's Avatar
 
Join Date: 01 2005
Location: Perm
Age: 38
Posts: 2,142
Downloads: 0
Uploads: 0
Reputation: 0 | 0
Default

пиши на мэйл ... может помогу . Тут не буду.
Reply With Quote
Old 22.07.2005, 08:51   #14
Silence
 
Moonlight's Avatar
 
Join Date: 11 2004
Location: back to meta level
Posts: 3,926
Downloads: 1
Uploads: 0
Blog Entries: 3
Reputation: 202 | 3
Default

vremeni na mails netu, ex exams r here, we train on them
http://iwi.wiwi.hu-berlin.de/(lpzab5...x?id=2&m_id=25

course db management, esli smojesh pomoch s paru php tasks from SS 2002, stuchis' v pm, skinu asku
Reply With Quote
Old 22.07.2005, 14:39   #15
панаехавший
 
Obelix's Avatar
 
Join Date: 06 2003
Location: форпост
Age: 30
Posts: 4,007
Downloads: 0
Uploads: 0
Reputation: 0 | 0
Default

кудай пхп, посмотрим )
Reply With Quote
Sponsored Links
Reply

Thread Tools


На правах рекламы:
реклама

All times are GMT. The time now is 20:37.


Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.