![]() |
![]() | #1 |
Silence Join Date: 11 2004 Location: back to meta level
Posts: 3,926
Blog Entries: 3 Downloads: 1 Uploads: 0
Reputation: 202 | 4 | ![]()
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?\ |
![]() |
![]() | #2 |
Silence Join Date: 11 2004 Location: back to meta level
Posts: 3,926
Blog Entries: 3 Downloads: 1 Uploads: 0
Reputation: 202 | 4 | ![]()
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) ); |
![]() |
![]() | #3 |
Silence Join Date: 11 2004 Location: back to meta level
Posts: 3,926
Blog Entries: 3 Downloads: 1 Uploads: 0
Reputation: 202 | 4 | ![]()
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 ![]() |
![]() |
![]() | #6 |
Младенец Join Date: 07 2005 Location: Massachusetts Age: 46
Posts: 1
Downloads: 0 Uploads: 0
Reputation: 0 | 0 | ![]()
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 ![]() |
![]() |
![]() | #7 | |
ЙЦУКЕН Join Date: 07 2002 Location: 0x68,0x69,0x72, 0x69,0x6e,0x67, 0x20,0x6e,0x6f, 0x77 Age: 51
Posts: 3,118
Downloads: 0 Uploads: 0
Reputation: 5 | 0 | ![]() Quote:
не, не правильно ![]() | |
![]() |
![]() | #8 | |
Silence Join Date: 11 2004 Location: back to meta level
Posts: 3,926
Blog Entries: 3 Downloads: 1 Uploads: 0
Reputation: 202 | 4 | ![]()
Karinchik, thx for help! for a) nm had already reminded me of pk & fk-s, it's fixed ![]() Quote:
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 ![]() 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; | |
![]() |
![]() | #10 |
Silence Join Date: 11 2004 Location: back to meta level
Posts: 3,926
Blog Entries: 3 Downloads: 1 Uploads: 0
Reputation: 202 | 4 | ![]()
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 ![]() ![]() |
![]() |
![]() | #11 | |
Silence Join Date: 11 2004 Location: back to meta level
Posts: 3,926
Blog Entries: 3 Downloads: 1 Uploads: 0
Reputation: 202 | 4 | ![]() Quote:
![]() | |
![]() |
![]() | #14 |
Silence Join Date: 11 2004 Location: back to meta level
Posts: 3,926
Blog Entries: 3 Downloads: 1 Uploads: 0
Reputation: 202 | 4 | ![]()
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 |
![]() |