-
Notifications
You must be signed in to change notification settings - Fork 0
/
lab 8.txt
238 lines (135 loc) · 4.7 KB
/
lab 8.txt
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
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
SQL*Plus: Release 11.2.0.2.0 Production on Thu Apr 21 05:37:24 2022
Copyright (c) 1982, 2010, Oracle. All rights reserved.
SQL> connect system
Enter password:
Connected.
SQL> create table sailor(sid int primary key, sname varchar(20),rating int,age i
nt);
Table created.
SQL> create table boats(bid int primary key, bname varchar(20),color varchar(20)
);
Table created.
SQL> create table reserves(sid int, bid int, day date, foreign key(sid) referenc
es sailor(sid), foreign key(bid) references boats(bid));
Table created.
SQL>
SQL> insert into sailor values(22,'Dustin',7,45.0);
1 row created.
SQL> insert into sailor values(29,'Brutus',1,33.0);
1 row created.
SQL> insert into sailor values(31,'Lubber',8,55.5);
1 row created.
SQL> insert into sailor values(32,'Andy',8,25.5);
1 row created.
SQL> insert into sailor values(58,'Rusty',10,35.5);
1 row created.
SQL> insert into sailor values(64,'Horatio',7,35.5);
1 row created.
SQL> insert into sailor values(71,'Zorba',10,16.0);
1 row created.
SQL> insert into sailor values(74,'Horatio',9,35.0);
1 row created.
SQL> insert into sailor values(85,'Art',3,25.5);
1 row created.
SQL> insert into sailor values(95,'Bob',3,63.5);
1 row created.
SQL> select * from sailor;
SID SNAME RATING AGE
---------- -------------------- ---------- ----------
22 Dustin 7 45
29 Brutus 1 33
31 Lubber 8 55.5
32 Andy 8 25.5
58 Rusty 10 35.5
64 Horatio 7 35.5
71 Zorba 10 16
74 Horatio 9 35
85 Art 3 25.5
95 Bob 3 63.5
10 rows selected.
SQL> insert into boats values(101,'Interlake','blue');
1 row created.
SQL> insert into boats values(102,'Interlake','red');
1 row created.
SQL> insert into boats values(103,'Clipper','green');
1 row created.
SQL> insert into boats values(104,'Marine','red');
1 row created.
SQL> insert into reserves values(22,101,date '1998-10-10');
1 row created.
SQL> insert into reserves values(22,102,date '1998-10-10');
1 row created.
SQL> insert into reserves values(22,103,date '1998-08-10');
1 row created.
SQL> insert into reserves values(22,104,date '1998-07-10');
1 row created.
SQL> insert into reserves values(31,102,date '1998-10-11');
1 row created.
SQL> insert into reserves values(31,103,date '1998-06-11');
1 row created.
SQL> insert into reserves values(31,104,date '1998-12-11');
1 row created.
SQL> insert into reserves values(64,101,date '1998-5-9');
1 row created.
SQL> insert into reserves values(64,102,date '1998-8-9');
1 row created.
SQL> insert into reserves values(74,103,date '1998-8-9');
1 row created.
SQL> select * from reserves
2 select * from reserves;
select * from reserves
*
ERROR at line 2:
ORA-00933: SQL command not properly ended
SQL> select * from reserves;
SID BID DAY
---------- ---------- ---------
22 101 10-OCT-98
22 102 10-OCT-98
22 103 10-AUG-98
22 104 10-JUL-98
31 102 11-OCT-98
31 103 11-JUN-98
31 104 11-DEC-98
64 101 09-MAY-98
64 102 09-AUG-98
74 103 09-AUG-98
10 rows selected.
SQL>
SQL> select distinct(sname) from sailor;
SNAME
--------------------
Rusty
Lubber
Brutus
Andy
Art
Bob
Dustin
Zorba
Horatio
9 rows selected.
SQL> select * from sailor where sid in(select sid from reserve where bid=101);
SID SNAME RATING AGE
---------- -------------------- ---------- ----------
22 Dustin 7 45
64 Horatio 7 35.5
SQL> select sid,sname from sailor where sid in(select sid from reserve);
SID SNAME
---------- --------------------
22 Dustin
31 Lubber
64 Horatio
74 Horatio
SQL> select sname from sailor where sid in(select sid from reserve where bid in(
select bid from boats where color='red'))order by age;
SNAME
--------------------
Horatio
Dustin
Lubber
SQL> select sid,sname from sailor where sid in(select sid from reserve having co
unt(*)>1 group by day,sid);
SID SNAME
---------- --------------------
22 Dustin