Armenian Knowledge Base

Armenian Knowledge Base (https://forum.armkb.com/)
-   Algorithms (https://forum.armkb.com/algorithms/)
-   -   sql task. sos plz! (https://forum.armkb.com/algorithms/18518-sql-task-sos-plz.html)

Moonlight 21.07.2005 17:54

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

Moonlight 21.07.2005 18:00

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 21.07.2005 18:33

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 21.07.2005 19:32

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

Moonlight 21.07.2005 19:40

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

Karinchik 21.07.2005 20:38

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 21.07.2005 20:44

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


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

Moonlight 21.07.2005 21:10

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;

Nikita 21.07.2005 21:40

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

Moonlight 21.07.2005 21:48

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 21.07.2005 21:52

Quote:

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

ya teper' studentka, u kogo 4 majora, vibiray po svoemu vkusu:D a mojet ludshe zadachi poreshish? u menya ix esho mnogo.. php geeks r also welcome

Nikita 22.07.2005 06:49

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

Nikita 22.07.2005 07:42

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

Moonlight 22.07.2005 07:51

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 22.07.2005 13:39

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


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

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