-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathStocks.sql
executable file
·96 lines (87 loc) · 4.21 KB
/
Stocks.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
CREATE DATABASE StockMarket;
USE StockMarket;
create table Stocks (
Symbol VARCHAR(6),
Market VARCHAR(8),
Price DECIMAL(7,2),
OpenPrice DECIMAL(7,2),
DailyHigh DECIMAL(7,2),
DailyLow DECIMAL(7,2),
PRIMARY KEY(Symbol,Market),
PointChangeToday DECIMAL(5,2) as (Price-OpenPrice) VIRTUAL,
PercentChangeToday DECIMAL(2,2) as (PointChangeToday/OpenPrice) VIRTUAL
);
create table StocksByPriceOverTime (
Symbol VARCHAR(6),
Market VARCHAR(8),
PriceDayMinus1 DECIMAL(7,2),
PriceDayMinus2 DECIMAL(7,2),
PriceDayMinus3 DECIMAL(7,2),
PriceDayMinus4 DECIMAL(7,2),
PriceDayMinus5 DECIMAL(7,2),
PriceDayMinus6 DECIMAL(7,2),
PriceDayMinus7 DECIMAL(7,2),
PriceDayMinus8 DECIMAL(7,2),
PriceDayMinus9 DECIMAL(7,2),
PriceDayMinus10 DECIMAL(7,2),
PriceDayMinus11 DECIMAL(7,2),
PriceDayMinus12 DECIMAL(7,2),
PriceDayMinus13 DECIMAL(7,2),
PriceDayMinus14 DECIMAL(7,2),
PriceDayMinus15 DECIMAL(7,2),
PRIMARY KEY(Symbol, Market)
);
INSERT Stocks (Symbol, Market, Price, OpenPrice, DailyHigh, DailyLow) VALUE
('AGTK','NASDAQ',1119.20,1139.82,1112.65,780.61)
,('BIDU','CPH',121.45,134.80,115.76,312.14)
,('CCIH','NASDAQ',174.32,174.91,172.92,816.45)
,('GDDY','NASDAQ',174.32,174.91,172.92,816.45)
,('GEZI','NASDAQ',1119.20,1139.82,1112.65,780.61)
,('GOOGL','NASDAQ',1119.20,1139.82,1112.65,780.61)
,('JCOM','NASDAQ',1119.20,1139.82,1112.65,780.61)
,('SIM','CPH',121.45,134.80,115.76,312.14)
,('VNET','NASDAQ',174.32,174.91,172.92,816.45);
INSERT StocksByPriceOverTime (Symbol, Market,PriceDayMinus1, PriceDayMinus2, PriceDayMinus3, PriceDayMinus4, PriceDayMinus5, PriceDayMinus6, PriceDayMinus7, PriceDayMinus8, PriceDayMinus9, PriceDayMinus10, PriceDayMinus11, PriceDayMinus12, PriceDayMinus13, PriceDayMinus14, PriceDayMinus15) VALUE
('AGTK','NASDAQ',333,221,341,109,98,268,133,263,349,197,105,82,104,306,259)
,('BIDU','CPH',277,149,170,108,221,261,164,84,129,309,256,222,329,253,280)
,('CCIH','NASDAQ',151,121,177,310,142,213,85,243,86,174,161,183,113,109,339)
,('GDDY','NASDAQ',100,94,275,266,258,305,234,308,265,133,293,157,136,236,89)
,('GEZI','NASDAQ',131,236,187,218,175,251,245,178,151,279,320,288,189,192,80)
,('GOOGL','NASDAQ',272,292,189,254,112,275,334,206,266,176,312,168,341,155,97)
,('JCOM','NASDAQ',211,307,292,191,183,135,273,107,342,155,232,284,178,177,234)
,('SIM','CPH',103,302,136,160,126,81,253,321,185,214,184,194,145,328,98)
,('VNET','NASDAQ',103,80,202,256,200,337,213,305,175,240,319,286,91,329,135);
drop TABLE DB_Data;
CREATE TABLE IF NOT EXISTS DB_Data (
Symbol VARCHAR(4),
Type VARCHAR(5),
Price INT,
QC VARCHAR(3),
Total_QTY INT,
Total_Price INT,
Maturity_Date VARCHAR(10),
Dirty_Value_QC INT,
Dirty_Value_PC INT,
Dirty_Value_RC INT
);
INSERT INTO DB_Data VALUES
('SUQJ','Bond',3903,'DKK',146,569838,'2038-05-04',21058,13718,4470),
('IIUZ','Bond',920,'DKK',469,431480,'2024-01-02',34739,14062,26288),
('GESZ','Fund',2020,'CAD',545,1100900,'2026-01-25',71822,11597,29567),
('HJET','Stock',2259,'USD',741,1673919,'2023-10-31',10092,140473,158568),
('OPBA','Fund',3057,'DKK',861,2632077,'2029-06-01',24557,150000,237603),
('ETVD','Stock',1931,'CAD',392,756952,'2033-07-14',49208,33133,2),
('NHXS','Stock',4397,'DKK',339,1490583,'2028-06-08',129866,105397,47647),
('FRVD','Stock',3025,'CAD',254,768350,'2022-05-09',64344,6976,53054),
('WWYK','Bond',2597,'USD',261,677817,'2023-08-14',48237,12476,57787),
('DTTG','Fund',745,'DKK',700,521500,'2020-10-20',29312,40032,34726),
('MTFX','Bond',1076,'USD',605,650980,'2021-01-06',4957,30619,1641),
('KATS','Bond',4016,'CAD',574,2305184,'2031-10-09',54622,11674,178255),
('AXJO','Bond',4325,'USD',210,908250,'2020-08-12',80567,81840,228),
('PBGF','Stock',695,'DKK',899,624805,'2028-11-06',29131,42041,43992),
('VMUO','Stock',4058,'USD',176,714208,'2022-06-19',5097,65563,64962),
('FEUB','Stock',3579,'USD',609,2179611,'2038-09-06',56857,19475,116205),
('OZEY','Fund',2638,'EUR',974,2569412,'2020-02-09',173021,208551,155831),
('NEAC','Bond',501,'DKK',377,188877,'2037-03-04',5371,7595,62),
('OPHW','Bond',3832,'EUR',79,302728,'2027-02-25',17046,25609,9701),
('KWQK','Bond',3310,'DKK',220,728200,'2023-04-07',48013,10841,70704);