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