SQL> selet pokedexid from pokemon where name='Pikachu';
SP2-0734: unknown command beginning "selet poke..." - rest of line ignored.
SQL> select pokedexid from pokemon where name='Pikachu';
POKEDEXID
----------
25
SQL> select * from pokemon where pokedexid = 25;
POKEDEXID NAME
---------- --------------------
25 Pikachu
SQL> select * from attributes where pokedexid = 25'
2 ;
ERROR:
ORA-01756: quoted string not properly terminated
SQL> select * from attributes where pokedexid = 25;
POKEDEXID HEIGHT WEIGHT BASEEXPERIENCE
---------- ---------- ---------- --------------
25 4 60 82
SQL> select * from attributes where (heigth, weight) = (4,60);
select * from attributes where (heigth, weight) = (4,60)
*
ERROR at line 1:
ORA-00920: invalid relational operator
SQL> select * from attributes where (height, weight) = (4,60);
select * from attributes where (height, weight) = (4,60)
*
ERROR at line 1:
ORA-00920: invalid relational operator
SQL> select distinct height from pk;
select distinct height from pk
*
ERROR at line 1:
ORA-00904: "HEIGHT": invalid identifier
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
CONTINENTS
LIST
MODELS
CAMPUSES
CARMAKERS
COUNTRIES
MAKES
CARDATA
ST
SHARKS
AIRPORTS
TABLE_NAME
------------------------------
MARATHON
FACULTY
DISCENR
DISCIPLINES
ENROLLMENTS
DEGREES
FEES
FLIGHTS
AIRLINES
TEACHERS
LEAGUES
TABLE_NAME
------------------------------
ATTENDANCE
T
ST01
NOPKEY
PK
ROOMS
CUSTOMERS
MINEFIELD
RECEIPTS
ITEMS
FOO1
TABLE_NAME
------------------------------
BEERS
MYBOOKS
BEERS1
FOO2
FOO3
FOO4
FOO5
BARS
DRINKS
BEERS2
LIKETEST
TABLE_NAME
------------------------------
TIMES
CHECKIN
FOOBAR
ATT2007
SEARCH
KODIE
CNTR
ATTN
LOCATIONS
STUFF
VECTORPAIRS
TABLE_NAME
------------------------------
VECTORS
FST
AGGS
PTS
MYLIST
ALLELES
MYVECTORS
POK
PNEXT
CC
SPECIES
TABLE_NAME
------------------------------
ATTRIBUTES
TYPES
ANEW
POKEMON
EVOLUTION
REACTORS
PFIRST
PPK
EARTHQUAKES
75 rows selected.
SQL> select distinct height from ppk;
HEIGHT
----------
6
11
20
5
4
17
35
8
7
3
10
HEIGHT
----------
16
15
12
14 rows selected.
SQL> list
1* select distinct height from ppk
SQL> select * from ppk;
POKEDEXID NAME HEIGHT WEIGHT BASEEXPERIENCE
---------- -------------------- ---------- ---------- --------------
GENUS
--------------------
1 Bulbasaur 7 69 64
Seed
2 Ivysaur 10 130 141
Seed
3 Venusaur 20 1000 208
Seed
POKEDEXID NAME HEIGHT WEIGHT BASEEXPERIENCE
---------- -------------------- ---------- ---------- --------------
GENUS
--------------------
4 Charmander 6 85 65
Lizard
5 Charmeleon 11 190 142
Flame
6 Charizard 17 905 209
Flame
POKEDEXID NAME HEIGHT WEIGHT BASEEXPERIENCE
---------- -------------------- ---------- ---------- --------------
GENUS
--------------------
7 Squirtle 5 90 66
Tiny Turtle
8 Wartortle 10 225 143
Turtle
9 Blastoise 16 855 210
Shellfish
POKEDEXID NAME HEIGHT WEIGHT BASEEXPERIENCE
---------- -------------------- ---------- ---------- --------------
GENUS
--------------------
10 Caterpie 3 29 53
Worm
11 Metapod 7 99 72
Cocoon
12 Butterfree 11 320 160
Butterfly
POKEDEXID NAME HEIGHT WEIGHT BASEEXPERIENCE
---------- -------------------- ---------- ---------- --------------
GENUS
--------------------
13 Weedle 3 32 52
Hairy Bug
14 Kakuna 6 100 71
Cocoon
15 Beedrill 10 295 159
Poison Bee
POKEDEXID NAME HEIGHT WEIGHT BASEEXPERIENCE
---------- -------------------- ---------- ---------- --------------
GENUS
--------------------
16 Pidgey 3 18 55
Tiny Bird
17 Pidgeotto 11 300 113
Bird
18 Pidgeot 15 395 172
Bird
POKEDEXID NAME HEIGHT WEIGHT BASEEXPERIENCE
---------- -------------------- ---------- ---------- --------------
GENUS
--------------------
19 Rattata 3 35 57
Mouse
20 Raticate 7 185 116
Mouse
21 Spearow 3 20 58
Tiny Bird
POKEDEXID NAME HEIGHT WEIGHT BASEEXPERIENCE
---------- -------------------- ---------- ---------- --------------
GENUS
--------------------
22 Fearow 12 380 162
Beak
23 Ekans 20 69 62
Snake
24 Arbok 35 650 147
Cobra
POKEDEXID NAME HEIGHT WEIGHT BASEEXPERIENCE
---------- -------------------- ---------- ---------- --------------
GENUS
--------------------
25 Pikachu 4 60 82
Mouse
26 Raichu 8 300 122
Mouse
27 Sandshrew 6 120 93
Mouse
POKEDEXID NAME HEIGHT WEIGHT BASEEXPERIENCE
---------- -------------------- ---------- ---------- --------------
GENUS
--------------------
28 Sandslash 10 295 163
Mouse
29 Nidoran??? 4 70 59
Poison Pin
30 Nidorina 8 200 117
Poison Pin
30 rows selected.
SQL> set linesize 200
SQL> set pagesize 40
SQL> select pokedexid from pokemon where name = 'Pikachu';
POKEDEXID
----------
25
SQL> select * from attributes where pokedexid = 25;
POKEDEXID HEIGHT WEIGHT BASEEXPERIENCE
---------- ---------- ---------- --------------
25 4 60 82
SQL> select a.pokedexid, a.height, a.weight, a.baseexperience
2 from attributes a, pokemon p
3 where p.name = 'Pickachu' and p.pokedexid = a.pokedexid
4 ;
no rows selected
SQL> select a.pokedexid, a.height, a.weight, a.baseexperience
2 from attributes a, pokemon p
3 where p.name = 'Pikachu' and p.pokedexid = a.pokedexid
4 ;
POKEDEXID HEIGHT WEIGHT BASEEXPERIENCE
---------- ---------- ---------- --------------
25 4 60 82
SQL> select * from attributes
2 where pokedexid = (select pokedexid from pokemon where name = 'Pikachu')
3 ;
POKEDEXID HEIGHT WEIGHT BASEEXPERIENCE
---------- ---------- ---------- --------------
25 4 60 82
SQL> create table a as (select pokedexid from pokemon wher name = 'Pikachu');
create table a as (select pokedexid from pokemon wher name = 'Pikachu')
*
ERROR at line 1:
ORA-00907: missing right parenthesis
SQL> create table a as (select pokedexid from pokemon where name = 'Pikachu');
Table created.
SQL> select * from a;
POKEDEXID
----------
25
SQL> select * from attributes where pokedexid = (select * from a);
POKEDEXID HEIGHT WEIGHT BASEEXPERIENCE
---------- ---------- ---------- --------------
25 4 60 82
SQL>
SQL> from pokemon
SP2-0734: unknown command beginning "from pokem..." - rest of line ignored.
SQL> where name = 'Pikachu' or
SP2-0734: unknown command beginning "where name..." - rest of line ignored.
SQL> name = 'Bulbasaur;
SP2-0734: unknown command beginning "name = 'Bu..." - rest of line ignored.
SQL> select pokedexid
2 from pokemon
3 where name = 'Pikachu' or
4 name = 'Bulbasaur;
ERROR:
ORA-01756: quoted string not properly terminated
SQL> select pokedexid
2 from pokemon
3 where name = 'Pikachu' or
4 name = 'Bulbasaur';
POKEDEXID
----------
1
25
SQL> select * from attributes
2 where pokedexid = (select pokedexid
3 from pokemon
4 where name = 'Pikachu' or
5 name = 'Bulbasaur')
6 ;
where pokedexid = (select pokedexid
*
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row
SQL> select * from attributes
2 where pokedexid IN (select pokedexid
3 from pokemon
4 where name = 'Pikachu' or
5 name = 'Bulbasaur')
6 ;
POKEDEXID HEIGHT WEIGHT BASEEXPERIENCE
---------- ---------- ---------- --------------
1 7 69 64
25 4 60 82
SQL> select * from ppk;
POKEDEXID NAME HEIGHT WEIGHT BASEEXPERIENCE GENUS
---------- -------------------- ---------- ---------- -------------- --------------------
1 Bulbasaur 7 69 64 Seed
2 Ivysaur 10 130 141 Seed
3 Venusaur 20 1000 208 Seed
4 Charmander 6 85 65 Lizard
5 Charmeleon 11 190 142 Flame
6 Charizard 17 905 209 Flame
7 Squirtle 5 90 66 Tiny Turtle
8 Wartortle 10 225 143 Turtle
9 Blastoise 16 855 210 Shellfish
10 Caterpie 3 29 53 Worm
11 Metapod 7 99 72 Cocoon
12 Butterfree 11 320 160 Butterfly
13 Weedle 3 32 52 Hairy Bug
14 Kakuna 6 100 71 Cocoon
15 Beedrill 10 295 159 Poison Bee
16 Pidgey 3 18 55 Tiny Bird
17 Pidgeotto 11 300 113 Bird
18 Pidgeot 15 395 172 Bird
19 Rattata 3 35 57 Mouse
20 Raticate 7 185 116 Mouse
21 Spearow 3 20 58 Tiny Bird
22 Fearow 12 380 162 Beak
23 Ekans 20 69 62 Snake
24 Arbok 35 650 147 Cobra
25 Pikachu 4 60 82 Mouse
26 Raichu 8 300 122 Mouse
27 Sandshrew 6 120 93 Mouse
28 Sandslash 10 295 163 Mouse
29 Nidoran??? 4 70 59 Poison Pin
30 Nidorina 8 200 117 Poison Pin
30 rows selected.
SQL> select * from ppk where genus =
2 'Mouse';
POKEDEXID NAME HEIGHT WEIGHT BASEEXPERIENCE GENUS
---------- -------------------- ---------- ---------- -------------- --------------------
19 Rattata 3 35 57 Mouse
20 Raticate 7 185 116 Mouse
25 Pikachu 4 60 82 Mouse
26 Raichu 8 300 122 Mouse
27 Sandshrew 6 120 93 Mouse
28 Sandslash 10 295 163 Mouse
6 rows selected.
SQL> select * from attributes where
2 baseexperience > ALL (select baseexperience from ppk where genus='Mouse')
3 ;
POKEDEXID HEIGHT WEIGHT BASEEXPERIENCE
---------- ---------- ---------- --------------
3 20 1000 208
6 17 905 209
9 16 855 210
18 15 395 172
31 13 600 194
34 14 620 195
38 11 199 178
42 16 550 171
45 12 186 184
55 17 766 174
59 19 1550 213
62 13 540 185
65 15 480 186
68 16 1300 193
71 17 155 191
73 16 550 205
76 14 3000 177
78 17 950 192
80 16 785 164
87 17 1200 176
91 15 1325 203
94 15 405 190
97 16 756 165
99 13 600 206
103 20 1200 212
110 12 95 173
112 19 1200 204
113 11 346 255
114 10 350 166
115 22 800 175
119 13 390 170
121 11 800 207
123 15 560 187
126 13 445 167
127 15 550 200
128 14 884 211
130 65 2350 214
POKEDEXID HEIGHT WEIGHT BASEEXPERIENCE
---------- ---------- ---------- --------------
131 25 2200 219
134 10 290 196
135 8 245 197
136 9 250 198
139 10 350 199
141 13 405 199
142 18 590 202
144 17 554 215
145 16 526 216
146 20 600 217
149 22 2100 218
150 20 1220 220
154 18 1005 208
157 17 795 209
160 23 888 210
169 18 750 204
178 15 150 171
181 14 615 194
182 4 58 184
186 11 339 185
189 8 30 176
196 9 265 197
197 10 270 197
199 20 795 164
202 13 285 177
208 92 4000 196
210 14 487 178
212 18 1180 200
214 15 540 200
217 18 1258 189
224 9 285 164
225 9 160 183
226 21 2200 168
227 17 505 168
229 14 350 204
230 18 1520 207
232 11 1200 189
POKEDEXID HEIGHT WEIGHT BASEEXPERIENCE
---------- ---------- ---------- --------------
233 6 325 180
234 14 712 165
241 12 755 200
242 15 468 255
243 19 1780 216
244 21 1980 217
245 20 1870 215
248 20 2020 218
249 52 2160 220
250 38 1990 220
254 17 522 208
257 19 520 209
260 15 819 210
272 15 550 181
275 13 596 181
279 12 280 164
282 16 484 208
286 12 392 165
289 20 1305 210
295 15 840 184
297 23 2538 184
306 21 3600 205
310 15 402 168
317 17 800 168
319 18 888 175
321 145 3980 206
323 19 2200 175
326 9 715 164
330 20 820 197
332 13 774 177
334 11 206 188
335 13 403 165
336 27 525 165
344 15 1080 189
346 15 604 199
348 15 682 199
350 62 1620 213
POKEDEXID HEIGHT WEIGHT BASEEXPERIENCE
---------- ---------- ---------- --------------
354 11 125 179
356 16 306 179
357 20 1000 169
359 12 470 174
362 15 2565 187
365 14 1506 192
367 17 270 178
368 18 226 178
369 10 234 198
373 15 1026 218
376 16 5500 210
377 17 2300 217
378 18 1750 216
379 19 2050 215
380 14 400 211
381 20 600 211
382 45 3520 218
383 35 9500 218
384 70 2065 220
385 3 11 215
386 17 608 215
389 22 3100 208
392 12 550 209
395 17 845 210
398 12 249 172
405 14 420 194
407 9 145 204
409 16 1025 199
411 13 1495 199
416 12 385 188
419 11 335 178
423 9 299 176
424 12 203 186
426 12 150 204
428 12 333 178
429 9 44 187
430 9 273 187
POKEDEXID HEIGHT WEIGHT BASEEXPERIENCE
---------- ---------- ---------- --------------
432 10 438 183
435 10 380 209
437 13 1870 188
440 6 244 255
442 10 1080 168
445 19 950 218
448 12 540 204
450 20 3000 198
452 13 615 204
454 13 444 181
455 14 270 164
460 22 1355 214
461 11 340 199
462 12 1800 211
463 17 1400 193
464 24 2828 217
465 20 1286 211
466 18 1386 199
467 16 680 199
468 15 380 220
469 19 515 198
470 10 255 196
471 8 259 196
472 20 425 192
473 25 2910 207
474 9 340 185
475 16 520 208
476 14 3400 198
477 22 1066 210
478 13 266 187
480 3 3 210
481 3 3 210
482 3 3 210
483 54 6830 220
484 42 3360 220
485 17 4300 215
486 37 4200 220
POKEDEXID HEIGHT WEIGHT BASEEXPERIENCE
---------- ---------- ---------- --------------
487 45 7500 220
488 15 856 210
489 4 31 165
490 3 14 215
491 15 505 210
493 32 3200 255
494 4 40 270
497 33 630 238
500 16 1500 238
503 15 946 238
508 12 610 221
512 11 305 174
514 10 280 174
516 10 290 174
518 11 605 170
521 12 290 215
523 16 795 174
526 17 2600 227
530 7 404 178
531 11 310 390
534 14 870 227
537 15 620 225
542 12 205 221
545 25 2005 214
547 7 66 168
549 11 163 168
553 15 963 229
555 13 929 168
558 14 2000 166
560 11 300 171
561 14 140 172
563 17 765 169
565 12 810 173
567 14 320 177
569 19 1073 166
571 16 811 179
573 5 75 165
POKEDEXID HEIGHT WEIGHT BASEEXPERIENCE
---------- ---------- ---------- --------------
576 15 440 221
579 10 201 221
581 13 242 166
584 13 575 241
586 19 925 166
589 10 330 173
593 22 1350 168
594 12 316 165
596 8 143 165
598 10 1100 171
601 6 810 234
604 21 805 232
606 10 345 170
609 10 343 234
612 18 1055 243
614 26 2600 170
615 11 1480 170
617 8 253 173
618 7 110 165
620 14 355 179
621 16 1390 170
623 28 3300 169
625 16 700 172
626 16 946 172
628 15 410 179
630 12 395 179
631 14 580 169
632 3 330 169
635 18 1600 270
637 16 460 248
638 21 2500 261
639 19 2600 261
640 20 2000 261
641 15 630 261
642 15 610 261
643 32 3300 306
644 29 3450 306
POKEDEXID HEIGHT WEIGHT BASEEXPERIENCE
---------- ---------- ---------- --------------
645 15 680 270
646 30 3250 297
647 14 485 261
648 6 65 270
649 15 825 270
264 rows selected.
SQL> list
1 select * from attributes where
2 baseexperience > ALL (select baseexperience from ppk where genus='Mouse')
3*
SQL>
SQL> select * from ppk where
2 2 baseexperience > ALL (select baseexperience from ppk where genus='Mouse')
3 ;
2 baseexperience > ALL (select baseexperience from ppk where genus='Mouse')
*
ERROR at line 2:
ORA-00920: invalid relational operator
SQL>
SQL> select * from ppk where
2 baseexperience > ALL (select baseexperience
3 from ppk where genus='Mouse')
4 ;
POKEDEXID NAME HEIGHT WEIGHT BASEEXPERIENCE GENUS
---------- -------------------- ---------- ---------- -------------- --------------------
3 Venusaur 20 1000 208 Seed
6 Charizard 17 905 209 Flame
9 Blastoise 16 855 210 Shellfish
18 Pidgeot 15 395 172 Bird
SQL>
SQL> select * from ppk where
2 baseexperience > ANY (select baseexperience
3 from ppk where genus='Mouse')
4 ;
POKEDEXID NAME HEIGHT WEIGHT BASEEXPERIENCE GENUS
---------- -------------------- ---------- ---------- -------------- --------------------
9 Blastoise 16 855 210 Shellfish
6 Charizard 17 905 209 Flame
3 Venusaur 20 1000 208 Seed
18 Pidgeot 15 395 172 Bird
28 Sandslash 10 295 163 Mouse
22 Fearow 12 380 162 Beak
12 Butterfree 11 320 160 Butterfly
15 Beedrill 10 295 159 Poison Bee
24 Arbok 35 650 147 Cobra
8 Wartortle 10 225 143 Turtle
5 Charmeleon 11 190 142 Flame
2 Ivysaur 10 130 141 Seed
26 Raichu 8 300 122 Mouse
30 Nidorina 8 200 117 Poison Pin
20 Raticate 7 185 116 Mouse
17 Pidgeotto 11 300 113 Bird
27 Sandshrew 6 120 93 Mouse
25 Pikachu 4 60 82 Mouse
11 Metapod 7 99 72 Cocoon
14 Kakuna 6 100 71 Cocoon
7 Squirtle 5 90 66 Tiny Turtle
4 Charmander 6 85 65 Lizard
1 Bulbasaur 7 69 64 Seed
23 Ekans 20 69 62 Snake
29 Nidoran??? 4 70 59 Poison Pin
21 Spearow 3 20 58 Tiny Bird
26 rows selected.
SQL> select * from ppk where genus = 'Mouse';
POKEDEXID NAME HEIGHT WEIGHT BASEEXPERIENCE GENUS
---------- -------------------- ---------- ---------- -------------- --------------------
19 Rattata 3 35 57 Mouse
20 Raticate 7 185 116 Mouse
25 Pikachu 4 60 82 Mouse
26 Raichu 8 300 122 Mouse
27 Sandshrew 6 120 93 Mouse
28 Sandslash 10 295 163 Mouse
6 rows selected.
SQL> select * from ppk where pokedex = (select * from ppk where name = 'Pikachu');
select * from ppk where pokedex = (select * from ppk where name = 'Pikachu')
*
ERROR at line 1:
ORA-00904: "POKEDEX": invalid identifier
SQL> select * from ppk where pokedex = (select * from ppk where name = 'Pikachu');
select * from ppk where pokedex = (select * from ppk where name = 'Pikachu')
*
ERROR at line 1:
ORA-00904: "POKEDEX": invalid identifier
SQL> select max(baseexperience)
2 from ppk;
MAX(BASEEXPERIENCE)
-------------------
210
SQL> select * from ppk where baseexperience = 210;
POKEDEXID NAME HEIGHT WEIGHT BASEEXPERIENCE GENUS
---------- -------------------- ---------- ---------- -------------- --------------------
9 Blastoise 16 855 210 Shellfish
SQL> select *
2 from ppk where baseexperience = (select max(baseexperience) from ppk)
3 ;
POKEDEXID NAME HEIGHT WEIGHT BASEEXPERIENCE GENUS
---------- -------------------- ---------- ---------- -------------- --------------------
9 Blastoise 16 855 210 Shellfish
SQL> select * from
2
SQL>
SQL> select p.name, a.weight, s.genus
2 from pokemon p, attributes a, species s
3 where p.pokedexid = a.pokedexid and s.pokedexid = p.pokedexid and
4 a.weight = (select max(weight) from attributes)
5 ;
NAME WEIGHT GENUS
-------------------- ---------- --------------------
Groudon 9500 Continent
SQL>
SQL> select p.pokedexid, p.name, a.weight, s.genus
2 from pokemon p, attributes a, species s
3 where p.pokedexid = a.pokedexid and s.pokedexid = p.pokedexid and
4 a.weight = (select max(weight) from attributes)
5 ;
POKEDEXID NAME WEIGHT GENUS
---------- -------------------- ---------- --------------------
383 Groudon 9500 Continent
SQL> describe attributes
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
POKEDEXID NOT NULL NUMBER(38)
HEIGHT NUMBER(38)
WEIGHT NUMBER(38)
BASEEXPERIENCE NUMBER(38)
SQL>
SQL> select s.genus
2 from pokemon p, attributes a, species s
3 where p.pokedexid = a.pokedexid and s.pokedexid = p.pokedexid and
4 a.weight = (select max(weight) from attributes)
5 ;
GENUS
--------------------
Continent
SQL> select genus, count(*)
2 from ppk
3 group by genus;
GENUS COUNT(*)
-------------------- ----------
Lizard 1
Tiny Turtle 1
Turtle 1
Seed 3
Flame 2
Poison Bee 1
Cocoon 2
Snake 1
Poison Pin 2
Bird 2
Mouse 6
Shellfish 1
Worm 1
Hairy Bug 1
Tiny Bird 2
Cobra 1
Butterfly 1
Beak 1
18 rows selected.
SQL> select *
2 from (select genus, count(*) as num
3 from ppk
4 group by genus
5 ) g
6 ;
GENUS NUM
-------------------- ----------
Lizard 1
Tiny Turtle 1
Turtle 1
Seed 3
Flame 2
Poison Bee 1
Cocoon 2
Snake 1
Poison Pin 2
Bird 2
Mouse 6
Shellfish 1
Worm 1
Hairy Bug 1
Tiny Bird 2
Cobra 1
Butterfly 1
Beak 1
18 rows selected.
SQL> select *
2 from (select genus, count(*) as num
3 from ppk
4 group by genus
5 ) g
6 ;
GENUS NUM
-------------------- ----------
Lizard 1
Tiny Turtle 1
Turtle 1
Seed 3
Flame 2
Poison Bee 1
Cocoon 2
Snake 1
Poison Pin 2
Bird 2
Mouse 6
Shellfish 1
Worm 1
Hairy Bug 1
Tiny Bird 2
Cobra 1
Butterfly 1
Beak 1
18 rows selected.
SQL> clear scr
SQL> select *
2 from (select genus, count(*) as num
3 from ppk
4 group by genus
5 ) g
6 ;
GENUS NUM
-------------------- ----------
Lizard 1
Tiny Turtle 1
Turtle 1
Seed 3
Flame 2
Poison Bee 1
Cocoon 2
Snake 1
Poison Pin 2
Bird 2
Mouse 6
Shellfish 1
Worm 1
Hairy Bug 1
Tiny Bird 2
Cobra 1
Butterfly 1
Beak 1
18 rows selected.
SQL> select *
2 from (select genus, count(*) as num
3 from ppk
4 group by genus
5 ) g
6 where g.count > 2
7 ;
where g.count > 2
*
ERROR at line 6:
ORA-00904: "G"."COUNT": invalid identifier
SQL>
SQL> select *
2 from (select genus, count(*) as num
3 from ppk
4 group by genus
5 ) g
6 where g.num > 2
7 ;
GENUS NUM
-------------------- ----------
Seed 3
Mouse 6
SQL> select * from ppk;
POKEDEXID NAME HEIGHT WEIGHT BASEEXPERIENCE GENUS
---------- -------------------- ---------- ---------- -------------- --------------------
1 Bulbasaur 7 69 64 Seed
2 Ivysaur 10 130 141 Seed
3 Venusaur 20 1000 208 Seed
4 Charmander 6 85 65 Lizard
5 Charmeleon 11 190 142 Flame
6 Charizard 17 905 209 Flame
7 Squirtle 5 90 66 Tiny Turtle
8 Wartortle 10 225 143 Turtle
9 Blastoise 16 855 210 Shellfish
10 Caterpie 3 29 53 Worm
11 Metapod 7 99 72 Cocoon
12 Butterfree 11 320 160 Butterfly
13 Weedle 3 32 52 Hairy Bug
14 Kakuna 6 100 71 Cocoon
15 Beedrill 10 295 159 Poison Bee
16 Pidgey 3 18 55 Tiny Bird
17 Pidgeotto 11 300 113 Bird
18 Pidgeot 15 395 172 Bird
19 Rattata 3 35 57 Mouse
20 Raticate 7 185 116 Mouse
21 Spearow 3 20 58 Tiny Bird
22 Fearow 12 380 162 Beak
23 Ekans 20 69 62 Snake
24 Arbok 35 650 147 Cobra
25 Pikachu 4 60 82 Mouse
26 Raichu 8 300 122 Mouse
27 Sandshrew 6 120 93 Mouse
28 Sandslash 10 295 163 Mouse
29 Nidoran??? 4 70 59 Poison Pin
30 Nidorina 8 200 117 Poison Pin
30 rows selected.
SQL> select genus, name, height
2 from ppk
3 where weight > 100;
GENUS NAME HEIGHT
-------------------- -------------------- ----------
Seed Ivysaur 10
Seed Venusaur 20
Flame Charmeleon 11
Flame Charizard 17
Turtle Wartortle 10
Shellfish Blastoise 16
Butterfly Butterfree 11
Poison Bee Beedrill 10
Bird Pidgeotto 11
Bird Pidgeot 15
Mouse Raticate 7
Beak Fearow 12
Cobra Arbok 35
Mouse Raichu 8
Mouse Sandshrew 6
Mouse Sandslash 10
Poison Pin Nidorina 8
17 rows selected.
SQL> select h.name
2 from (select genus, count(*) as num
3 from ppk
4 group by genus
5 ) g,
6 (select genus, name, height
7 from ppk
8 where weight > 100
9 ) h
10 where g.num > 2 and h.genus = g.genus
11 ;
NAME
--------------------
Ivysaur
Venusaur
Raticate
Raichu
Sandshrew
Sandslash
6 rows selected.
SQL>
SQL> select h.name, h.genus
2 from (select genus, count(*) as num
3 from ppk
4 group by genus
5 ) g,
6 (select genus, name, height
7 from ppk
8 where weight > 100
9 ) h
10 where g.num > 2 and h.genus = g.genus
11 ;
NAME GENUS
-------------------- --------------------
Ivysaur Seed
Venusaur Seed
Raticate Mouse
Raichu Mouse
Sandshrew Mouse
Sandslash Mouse
6 rows selected.
SQL> select genus, count(*) as num
2 from ppk
3 group by genus;
GENUS NUM
-------------------- ----------
Lizard 1
Tiny Turtle 1
Turtle 1
Seed 3
Flame 2
Poison Bee 1
Cocoon 2
Snake 1
Poison Pin 2
Bird 2
Mouse 6
Shellfish 1
Worm 1
Hairy Bug 1
Tiny Bird 2
Cobra 1
Butterfly 1
Beak 1
18 rows selected.
SQL> create table g as (select genus, count(*) as num
2 from ppk
3 group by genus
4 );
Table created.
SQL>
SQL> select * from g
2 where g.num = (SELECT MAX(num) from g)
3 ;
GENUS NUM
-------------------- ----------
Mouse 6
SQL> select *
2 from (select genus, count(*) as num
3 from ppk
4 group by genus
5 ) g
6 where g.num = (SELECT MAX(num) from g)
7 ;
GENUS NUM
-------------------- ----------
Mouse 6
SQL> clear scr;
SQL> select *
2 from (select genus, count(*) as num
3 from ppk
4 group by genus
5 ) g
6 where g.num = (SELECT MAX(num) from g)
7 ;
GENUS NUM
-------------------- ----------
Mouse 6
SQL> drop table g;
Table dropped.
SQL>
SQL> select *
2 from (select genus, count(*) as num
3 from ppk
4 group by genus
5 ) g
6 where g.num = (SELECT MAX(num) from g)
7 ;
where g.num = (SELECT MAX(num) from g)
*
ERROR at line 6:
ORA-00942: table or view does not exist
SQL>
SQL> select *
2 from (select genus, count(*) as num
3 from ppk
4 group by genus
5 ) g
6 where g.num = (SELECT MAX(num) from
7 (select genus, count(*) as num
8 from ppk
9 group by genus
10 )
11 )
12 ;
GENUS NUM
-------------------- ----------
Mouse 6
SQL>
SQL> exit