sql task. sos plz!

[Moonlight]

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?\

[Moonlight]

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)
);

[Moonlight]

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

[nm]

какой sql сервер?

[Moonlight]

libo oracle libo mysql. ne vajno, doljni pisat' na bumajke ya privikla po oracle.
viju pm, thx, sha postuchus'

[Karinchik]

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

[nm]

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'
)
...............

не, не правильно

[Moonlight]

Karinchik, thx for help! for a) nm had already reminded me of pk & fk-s, it's fixed

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;

[Nikita]

Ты теперь художник/поэт или SQL-ом занимешся ?

[Moonlight]

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

[Moonlight]

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

[Nikita]

что есть студентка у которой 4-е мажера ?

[Nikita]

пиши на мэйл ... может помогу . Тут не буду.

[Moonlight]

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

[Obelix]

кудай пхп, посмотрим )