-
Notifications
You must be signed in to change notification settings - Fork 2
/
worksheet3.sql
128 lines (94 loc) · 3.33 KB
/
worksheet3.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
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
(1)
CREATE TYPE exchanges_varray as VARRAY(3)
OF VARCHAR(40)
/
CREATE TYPE stock_type AS OBJECT
(
companyName VARCHAR(20),
currentPrice NUMBER(6,2),
exchanges exchanges_varray,
lastDivident NUMBER(4,2),
eps NUMBER(4,2)
)
/
CREATE TYPE address_type AS OBJECT
(
streetNo char(10),
streetName char(15),
suburb char(20),
state char(15),
pin char(10)
)
/
CREATE TYPE Investment_type AS OBJECT
(
company REF stock_type,
purchasePrice NUMBER(6,2),
purchaseDate DATE,
quantity NUMBER(6)
)
/
CREATE TYPE investment_nestedtb_type AS TABLE OF Investment_type
/
CREATE TYPE client_type AS OBJECT(
name Varchar(40),
address address_type,
investment investment_nestedtb_type
);
(2)
CREATE TABLE client of client_type(
CONSTRAINT client_pk PRIMARY KEY(name)
NESTED TABLE investment STORE AS investment_tab
)
/
CREATE TABLE stock of stock_type(
CONSTRAINT stock_pk PRIMARY KEY(companyName)
)
/
ALTER TABLE investment_tab
ADD SCOPE FOR (company) IS stock
/
insert into client Values(client_type('John Smith',address_type('3','East Av','Bentley','WA','6102'),
investment_nestedtb_type(investment_type('BHP',12.00,'02-10-01',1000)))
/
insert into client Values(client_type('John Smith',address_type('3','East Av','Bentley','WA','6102'),
investment_nestedtb_type(investment_type('BHP',10.50,'08-06-02',2000)))
/
insert into client Values(client_type('John Smith',address_type('3','East Av','Bentley','WA','6102'),
investment_nestedtb_type(investment_type('IBM',58.00,'12-02-00',500)))
/
insert into client Values(client_type('John Smith',address_type('3','East Av','Bentley','WA','6102'),
investment_nestedtb_type(investment_type('IBM',65.00,'10-04-01',1200)))
/
insert into client Values(client_type('John Smith',address_type('3','East Av','Bentley','WA','6102'),
investment_nestedtb_type(investment_type('INFOSYS',64.00,'11-08-01',1000)))
/
insert into client Values(client_type('Jill Brody',address_type('42','Bent St','Perath','WA','6001'),
investment_nestedtb_type(investment_type('INTEL',35.00,'30-01-00',300)))
/
insert into client Values(client_type('Jill Brody',address_type('42','Bent St','Perath','WA','6001'),
investment_nestedtb_type(investment_type('INTEL',54.00,'30-01-01',400)))
/
insert into client Values(client_type('Jill Brody',address_type('42','Bent St','Perath','WA','6001'),
investment_nestedtb_type(investment_type('INTEL',60.00,'02-10-01',200)))
/
insert into client Values(client_type('Jill Brody',address_type('42','Bent St','Perath','WA','6001'),
investment_nestedtb_type(investment_type('FORD',40.00,'05-10-99',300)))
/
insert into client Values(client_type('Jill Brody',address_type('42','Bent St','Perath','WA','6001'),
investment_nestedtb_type(investment_type('GM',55.50,'12-12-00',500)))
/
insert into stock Values(stock_type('BHP',10.50,exchanges_varray('Sydney','New York'),1.50,3.20))
/
insert into stock Values(stock_type('IBM',70.00,exchanges_varray('New York','London','Tokyo'),4.25,10.00))
/
insert into stock Values(stock_type('INTEL',76.50,exchanges_varray('New York','London),5.00,12.40))
/
insert into stock Values(stock_type('FORD',40.00,exchanges_varray('New York'),2.00,8.50))
/
insert into stock Values(stock_type('INFOSYS',45.00,exchanges_varray('New York'),3.00,7.80))
/
(3)
(a)SELECT DISTINCT c.name,i.company,s.currentPrice,s.dividend,s.eps
FROM client c,investment_tab i,stock s
WHERE