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 30 SQL> run 1* 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 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> SQL> SELECT height, weight 2 FROM ppk 3 WHERE genus = 'Mouse' 4 ; HEIGHT WEIGHT ---------- ---------- 3 35 7 185 4 60 8 300 6 120 10 295 6 rows selected. SQL> SELECT height, weight 2 FROM attributes 3 WHERE genus = 'Mouse' 4 ; WHERE genus = 'Mouse' * ERROR at line 3: ORA-00904: "GENUS": invalid identifier SQL> SELECT height, weight 2 FROM attributes a, species s 3 WHERE genus = 'Mouse' and 4 a.pokedexId = s.pokedexId 5 ; HEIGHT WEIGHT ---------- ---------- 3 35 7 185 4 60 8 300 6 120 10 295 6 rows selected. SQL> select * from pokemon 2 where height =8 and weight =300 and 3 (height, weight) IN 4 (SELECT height, weight 5 FROM attributes a, species s 6 WHERE genus = 'Mouse' and 7 a.pokedexId = s.pokedexId 8 ) 9 ; (height, weight) IN * ERROR at line 3: ORA-00904: "WEIGHT": invalid identifier SQL> select pokedexID 2 from attributes 3 where height =8 and weight = 300 and 4 (height, weight) IN 5 (SELECT height, weight 6 FROM attributes a, species s 7 WHERE genus = 'Mouse' and 8 a.pokedexId = s.pokedexId 9 ) 10 ; POKEDEXID ---------- 26 SQL> SQL> select name 2 from pokemon 3 where pokedexID = ( 4 select pokedexID 5 from attributes 6 where height =8 and weight = 300 and 7 (height, weight) IN 8 (SELECT height, weight 9 FROM attributes a, species s 10 WHERE genus = 'Mouse' and 11 a.pokedexId = s.pokedexId 12 ) 13 ) 14 ; NAME -------------------- Raichu SQL> select height, weight 2 from attributes 3 where pokedexId = (select pokedexID from 4 pokemon 5 where name = 'Raichu' 6 ) 7 ; HEIGHT WEIGHT ---------- ---------- 8 300 SQL> insert into pokemon values (1000, 'CS365chu'); 1 row created. SQL> insert into attributes values (1000, 8, 300, 200); 1 row created. SQL> SQL> select * from attributes 2 where (height, weight) = ( 3 (select height, weight 4 from attributes 5 where pokedexId = (select pokedexID from 6 pokemon 7 where name = 'Raichu' 8 ) 9 ) 10 ) 11 ; POKEDEXID HEIGHT WEIGHT BASEEXPERIENCE ---------- ---------- ---------- -------------- 26 8 300 122 1000 8 300 200 SQL> insert into species values (1000, 'Database'); insert into species values (1000, 'Database') * ERROR at line 1: ORA-00947: not enough values SQL> describe species Name Null? Type ----------------------------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------------------- POKEDEXID NOT NULL NUMBER(38) TYPEID NUMBER(38) GENUS VARCHAR2(20) SQL> select distinct typeid from species; TYPEID ---------- 1 11 13 6 2 14 4 5 8 17 7 3 12 10 15 16 9 17 rows selected. SQL> insert into species values(1000, 18, 'Database'); insert into species values(1000, 18, 'Database') * ERROR at line 1: ORA-02291: integrity constraint (ALEX.SYS_C00427793) violated - parent key not found SQL> select * from types; TYPEID TYPE GENERATION DAMAGECLASS ---------- -------------------- ---------- ----------- 1 normal 1 2 2 fighting 1 2 3 flying 1 2 4 poison 1 2 5 ground 1 2 6 rock 1 2 7 bug 1 2 8 ghost 1 2 9 steel 2 2 10 fire 1 3 11 water 1 3 12 grass 1 3 13 electric 1 3 14 psychic 1 3 15 ice 1 3 16 dragon 1 3 17 dark 2 3 10001 unknown 2 10002 shadow 3 19 rows selected. SQL> insert into species values(1000, 14, 'Database'); 1 row created. 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 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> select name 2 from ppk 3 where baseexperience = (SELECT MAX(Baseexperience) from ppk); NAME -------------------- Blastoise SQL> select name, baseexperience 2 from ppk 3 where baseexperience >= ALL (select baseexperience from ppk) 4 ; NAME BASEEXPERIENCE -------------------- -------------- Blastoise 210 SQL> select name, baseexperience 2 from ppk 3 where baseexperience >= ALL (select baseexperience from ppk 4 where genus = 'Mouse') 5 ; NAME BASEEXPERIENCE -------------------- -------------- Venusaur 208 Charizard 209 Blastoise 210 Pidgeot 172 Sandslash 163 SQL> select genus from ppk group by genus having count(*) > 1; GENUS -------------------- Seed Flame Cocoon Poison Pin Bird Mouse Tiny Bird 7 rows selected. SQL> select MAX(baseexperience) 2 from ppk 3 where genus = 'Mouse' 4 ; MAX(BASEEXPERIENCE) ------------------- 163 SQL> SQL> select name, baseexperience, genus 2 from ppk p 3 where baseexperience = 4 (select MAX(baseexperience) 5 from ppk 6 where genus = 'Mouse' 7 ) 8 ; NAME BASEEXPERIENCE GENUS -------------------- -------------- -------------------- Sandslash 163 Mouse SQL> SQL> select name, baseexperience, genus 2 from ppk p 3 where baseexperience = 4 (select MAX(baseexperience) 5 from ppk 6 where genus = 'Turtle' 7 ) 8 ; NAME BASEEXPERIENCE GENUS -------------------- -------------- -------------------- Wartortle 143 Turtle SQL> SQL> select name, baseexperience, genus 2 from ppk p 3 where baseexperience = 4 (select MAX(baseexperience) 5 from ppk 6 where genus = p.genus 7 ) 8 ; NAME BASEEXPERIENCE GENUS -------------------- -------------- -------------------- Venusaur 208 Seed Charmander 65 Lizard Charizard 209 Flame Squirtle 66 Tiny Turtle Wartortle 143 Turtle Blastoise 210 Shellfish Caterpie 53 Worm Metapod 72 Cocoon Butterfree 160 Butterfly Weedle 52 Hairy Bug Beedrill 159 Poison Bee Pidgeot 172 Bird Spearow 58 Tiny Bird Fearow 162 Beak Ekans 62 Snake Arbok 147 Cobra Sandslash 163 Mouse Nidorina 117 Poison Pin 18 rows selected. SQL> SQL> select name, baseexperience, genus 2 from ppk p 3 where baseexperience = 4 (select MAX(baseexperience) 5 from ppk 6 where genus = p.genus 7 ) 8 order by genus 9 ; NAME BASEEXPERIENCE GENUS -------------------- -------------- -------------------- Fearow 162 Beak Pidgeot 172 Bird Butterfree 160 Butterfly Arbok 147 Cobra Metapod 72 Cocoon Charizard 209 Flame Weedle 52 Hairy Bug Charmander 65 Lizard Sandslash 163 Mouse Beedrill 159 Poison Bee Nidorina 117 Poison Pin Venusaur 208 Seed Blastoise 210 Shellfish Ekans 62 Snake Spearow 58 Tiny Bird Squirtle 66 Tiny Turtle Wartortle 143 Turtle Caterpie 53 Worm 18 rows selected. SQL> SQL> select name, baseexperience, genus 2 from ppk p 3 where baseexperience = 4 (select MAX(baseexperience) 5 from ppk 6 where genus = p.genus 7 ) and 1 < (select count(*) from ppk 8 where genus = p.genus) 9 order by genus 10 ; NAME BASEEXPERIENCE GENUS -------------------- -------------- -------------------- Pidgeot 172 Bird Metapod 72 Cocoon Charizard 209 Flame Sandslash 163 Mouse Nidorina 117 Poison Pin Venusaur 208 Seed Spearow 58 Tiny Bird 7 rows selected. SQL> select genus, AVG(baseexperience) 2 from ppk 3 group by genus 4 having count(*) > 1 5 ; GENUS AVG(BASEEXPERIENCE) -------------------- ------------------- Seed 137.666667 Flame 175.5 Cocoon 71.5 Poison Pin 88 Bird 142.5 Mouse 105.5 Tiny Bird 56.5 7 rows selected. SQL> SQL> SQL> SQL> select * 2 from (select genus, AVG(baseexperience) 3 from ppk 4 group by genus 5 having count(*) > 1 6 ) t 7 where t.baseexperience = (SELECT MAX(baseexperience) 8 from t 9 ) 10 ; where t.baseexperience = (SELECT MAX(baseexperience) * ERROR at line 7: ORA-00904: "T"."BASEEXPERIENCE": invalid identifier SQL> SQL> select * 2 from (select genus, AVG(baseexperience) as exp 3 from ppk 4 group by genus 5 having count(*) > 1 6 ) t 7 where t.exp = (SELECT MAX(baseexperience) 8 from t 9 ) 10 ; where t.exp = (SELECT MAX(baseexperience) * ERROR at line 7: ORA-00904: "BASEEXPERIENCE": invalid identifier SQL> SQL> select * 2 from (select genus, AVG(baseexperience) as exp 3 from ppk 4 group by genus 5 having count(*) > 1 6 ) t 7 where t.exp = (SELECT MAX(exp) 8 from t 9 ) 10 ; GENUS EXP -------------------- ---------- Seed 137.666667 Flame 175.5 Cocoon 71.5 Poison Pin 88 Bird 142.5 Mouse 105.5 Tiny Bird 56.5 7 rows selected. SQL> select * from t; MAKER YEAR MILAGE NUM --------------- ---------- ---------- ---------- peugeaut 1972 21 1 chrysler 1973 15.8571429 7 amc 1974 16.3333333 3 nissan 1974 31.5 2 subaru 1974 26 1 honda 1975 33 1 fiat 1976 28 1 honda 1977 31.5 1 gm 1977 21 9 renault 1977 36 1 volkswagen 1978 31.6333333 3 subaru 1980 33.8 1 gm 1981 24.775 4 toyota 1981 33.65 4 honda 1981 34.4 2 volvo 1981 30.7 1 gm 1982 29.7777778 9 ford 1982 27.4 5 mazda 1982 34 2 saab 1978 21.6 1 renault 1980 40.9 1 gm 1970 14.2857143 8 ford 1970 15.75 6 peugeaut 1970 25 1 chrysler 1971 17 4 fiat 1971 30 1 volvo 1972 18 1 MAKER YEAR MILAGE NUM --------------- ---------- ---------- ---------- toyota 1973 20 2 honda 1974 24 1 amc 1975 18 3 opel 1976 25 1 renault 1976 27 1 subaru 1977 30 1 mazda 1977 21.5 1 volkswagen 1980 38.2833333 6 nissan 1980 36.925 4 mazda 1981 32.85 2 honda 1978 32.8 2 chrysler 1979 26.95 6 daimler benz 1979 25.4 1 ford 1971 18.6666667 6 renault 1972 26 1 ford 1972 16.2 5 chrysler 1974 20 4 ford 1975 16.3333333 6 volkswagen 1975 25.6666667 3 gm 1976 20.1428571 7 honda 1976 33 1 nissan 1977 27.75 2 ford 1977 18.75 4 ford 1981 28.1666667 3 amc 1982 23 1 volkswagen 1982 40 2 nissan 1982 37 2 MAKER YEAR MILAGE NUM --------------- ---------- ---------- ---------- chrysler 1970 15.6666667 7 amc 1970 17.5 5 toyota 1971 28 2 amc 1973 15.75 4 ford 1973 15.5714286 7 volkswagen 1973 23 2 nissan 1973 22 1 ford 1976 20.1666667 6 volkswagen 1976 29.25 2 toyota 1976 23.5 2 daimler benz 1976 16.5 1 ford 1978 23.4166667 6 mazda 1980 33.8666667 3 triumph 1980 35 1 nissan 1981 31.3666667 3 chrysler 1982 32.2 5 toyota 1982 33 2 toyota 1978 24.3 2 peugeaut 1978 16.2 1 amc 1979 23.8 2 fiat 1979 37.3 1 toyota 1980 33.3666667 3 volkswagen 1970 25 2 peugeaut 1971 30 1 mazda 1973 18 1 fiat 1973 27.5 2 opel 1973 24 1 MAKER YEAR MILAGE NUM --------------- ---------- ---------- ---------- volvo 1973 19 1 ford 1974 19.25 4 toyota 1974 31.5 2 fiat 1974 27 3 toyota 1975 26.5 2 volvo 1975 22 1 saab 1975 25 1 chrysler 1976 18.3333333 6 peugeaut 1976 19 1 toyota 1977 26 1 mazda 1978 32.8 1 subaru 1981 32.3 1 renault 1981 34.5 1 saab 1981 1 honda 1982 35.3333333 3 nissan 1978 30.1666667 3 gm 1979 23.9 10 volkswagen 1979 31.9 1 amc 1980 24.3 1 daimler benz 1980 30 1 opel 1971 28 1 volkswagen 1972 22.5 2 mazda 1972 19 1 toyota 1972 24.6666667 3 opel 1974 26 1 gm 1975 18.25 8 nissan 1975 24 1 MAKER YEAR MILAGE NUM --------------- ---------- ---------- ---------- nissan 1976 32 1 volkswagen 1977 29.75 2 chrysler 1981 29.05 6 gm 1978 21.3625 8 chrysler 1978 21.6833333 6 amc 1978 18.75 2 volvo 1978 17 1 nissan 1970 27 1 gm 1971 18.1428571 7 nissan 1972 28 1 gm 1973 13.9090909 11 gm 1974 17.25 4 volkswagen 1974 27.5 2 chrysler 1975 17.6666667 3 peugeaut 1975 23 1 volvo 1976 20 1 chrysler 1977 21.8 5 bmw 1977 21.5 1 ford 1980 25 2 honda 1980 38.5 2 peugeaut 1979 27.2 1 nissan 1979 31.8 1 gm 1980 30.05 2 citroen 1970 1 toyota 1970 24 1 amc 1971 18.3333333 3 gm 1972 14.1666667 6 MAKER YEAR MILAGE NUM --------------- ---------- ---------- ---------- amc 1972 16 2 chrysler 1972 19 5 saab 1973 24 1 amc 1976 18.5 3 volkswagen 1981 33 1 peugeaut 1981 28.1 1 ford 1979 18.34 5 mazda 1979 34.1 1 chrysler 1980 23.5 2 saab 1970 25 1 bmw 1970 26 1 hi 1970 9 1 nissan 1971 31 2 volkswagen 1971 27 2 149 rows selected. SQL> drop table t; Table dropped. SQL> SQL> select * 2 from (select genus, AVG(baseexperience) as exp 3 from ppk 4 group by genus 5 having count(*) > 1 6 ) t 7 where t.exp = (SELECT MAX(exp) 8 from t 9 ) 10 ; from t * ERROR at line 8: ORA-00942: table or view does not exist SQL> SQL> select * 2 from (select genus, AVG(baseexperience) as exp 3 from ppk 4 group by genus 5 having count(*) > 1 6 ) t 7 where t.exp = (SELECT MAX(exp) 8 FROM (select genus, AVG(baseexperience) as exp 9 from ppk 10 group by genus 11 having count(*) > 1 12 ) 13 ) 14 ; GENUS EXP -------------------- ---------- Flame 175.5 SQL> select genus, AVG(baseexperience) 2 from ppk 3 group by genus 4 having count(*) > 1; 5 SQL> list 1 select genus, AVG(baseexperience) 2 from ppk 3 group by genus 4* having count(*) > 1; SQL> run 1 select genus, AVG(baseexperience) 2 from ppk 3 group by genus 4* having count(*) > 1; having count(*) > 1; * ERROR at line 4: ORA-00911: invalid character SQL> select genus, AVG(baseexperience) 2 from ppk 3 group by genus 4 having count(*) > 1; GENUS AVG(BASEEXPERIENCE) -------------------- ------------------- Seed 137.666667 Flame 175.5 Cocoon 71.5 Poison Pin 88 Bird 142.5 Mouse 105.5 Tiny Bird 56.5 7 rows selected. SQL> select * from ppk 2 where height = (select MAX(height) from ppk); POKEDEXID NAME HEIGHT WEIGHT BASEEXPERIENCE GENUS ---------- -------------------- ---------- ---------- -------------- -------------------- 24 Arbok 35 650 147 Cobra SQL> select * from ppk 2 where height >= ALL (select height from ppk) 3 ; POKEDEXID NAME HEIGHT WEIGHT BASEEXPERIENCE GENUS ---------- -------------------- ---------- ---------- -------------- -------------------- 24 Arbok 35 650 147 Cobra SQL> SQL> select * 2 from (select genus, AVG(baseexperience) as exp 3 from ppk 4 group by genus 5 having count(*) > 1 6 ) t 7 where t.exp >= ALL (SELECT AVG(baseexperience) as exp 8 from ppk 9 group by genus 10 having count(*) > 1 11 ) 12 ; GENUS EXP -------------------- ---------- Flame 175.5 SQL> SQL> select * 2 from (select genus, AVG(baseexperience) as exp 3 from ppk 4 group by genus 5 having count(*) > 1 6 ) t 7 where t.exp >= ALL (SELECT AVG(baseexperience) as exp 8 from ppk 9 group by genus 10 having count(*) > 1 11 ) 12 ; GENUS EXP -------------------- ---------- Flame 175.5 SQL> describe types; Name Null? Type ----------------------------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------------------- TYPEID NOT NULL NUMBER(38) TYPE VARCHAR2(20) GENERATION NUMBER(38) DAMAGECLASS NUMBER(38) 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 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> select * from types; TYPEID TYPE GENERATION DAMAGECLASS ---------- -------------------- ---------- ----------- 1 normal 1 2 2 fighting 1 2 3 flying 1 2 4 poison 1 2 5 ground 1 2 6 rock 1 2 7 bug 1 2 8 ghost 1 2 9 steel 2 2 10 fire 1 3 11 water 1 3 12 grass 1 3 13 electric 1 3 14 psychic 1 3 15 ice 1 3 16 dragon 1 3 17 dark 2 3 10001 unknown 2 10002 shadow 3 19 rows selected. SQL> describe species Name Null? Type ----------------------------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------------------- POKEDEXID NOT NULL NUMBER(38) TYPEID NUMBER(38) GENUS VARCHAR2(20) SQL> SQL> select p.pokedexid, p.name, p.height, p.weight, p.baseexperience, p.genus, t.type 2 from ppk p, species s, types t 3 where p.pokedexid = s.pokedexid and 4 s.typeid = t.typeid 5 ; POKEDEXID NAME HEIGHT WEIGHT BASEEXPERIENCE GENUS TYPE ---------- -------------------- ---------- ---------- -------------- -------------------- -------------------- 16 Pidgey 3 18 55 Tiny Bird normal 17 Pidgeotto 11 300 113 Bird normal 18 Pidgeot 15 395 172 Bird normal 19 Rattata 3 35 57 Mouse normal 20 Raticate 7 185 116 Mouse normal 21 Spearow 3 20 58 Tiny Bird normal 22 Fearow 12 380 162 Beak normal 23 Ekans 20 69 62 Snake poison 24 Arbok 35 650 147 Cobra poison 29 Nidoran??? 4 70 59 Poison Pin poison 30 Nidorina 8 200 117 Poison Pin poison 27 Sandshrew 6 120 93 Mouse ground 28 Sandslash 10 295 163 Mouse ground 10 Caterpie 3 29 53 Worm bug 11 Metapod 7 99 72 Cocoon bug 12 Butterfree 11 320 160 Butterfly bug 13 Weedle 3 32 52 Hairy Bug bug 14 Kakuna 6 100 71 Cocoon bug 15 Beedrill 10 295 159 Poison Bee bug 4 Charmander 6 85 65 Lizard fire 5 Charmeleon 11 190 142 Flame fire 6 Charizard 17 905 209 Flame fire 7 Squirtle 5 90 66 Tiny Turtle water 8 Wartortle 10 225 143 Turtle water 9 Blastoise 16 855 210 Shellfish water 1 Bulbasaur 7 69 64 Seed grass 2 Ivysaur 10 130 141 Seed grass POKEDEXID NAME HEIGHT WEIGHT BASEEXPERIENCE GENUS TYPE ---------- -------------------- ---------- ---------- -------------- -------------------- -------------------- 3 Venusaur 20 1000 208 Seed grass 25 Pikachu 4 60 82 Mouse electric 26 Raichu 8 300 122 Mouse electric 30 rows selected. SQL> SQL> create table pkk as ( 2 select p.pokedexid, p.name, p.weight, p.baseexperience, p.genus, t.type 3 from ppk p, species s, types t 4 where p.pokedexid = s.pokedexid and 5 s.typeid = t.typeid 6 ) 7 ; Table created. SQL> select * from pkk; POKEDEXID NAME WEIGHT BASEEXPERIENCE GENUS TYPE ---------- -------------------- ---------- -------------- -------------------- -------------------- 16 Pidgey 18 55 Tiny Bird normal 17 Pidgeotto 300 113 Bird normal 18 Pidgeot 395 172 Bird normal 19 Rattata 35 57 Mouse normal 20 Raticate 185 116 Mouse normal 21 Spearow 20 58 Tiny Bird normal 22 Fearow 380 162 Beak normal 23 Ekans 69 62 Snake poison 24 Arbok 650 147 Cobra poison 29 Nidoran??? 70 59 Poison Pin poison 30 Nidorina 200 117 Poison Pin poison 27 Sandshrew 120 93 Mouse ground 28 Sandslash 295 163 Mouse ground 10 Caterpie 29 53 Worm bug 11 Metapod 99 72 Cocoon bug 12 Butterfree 320 160 Butterfly bug 13 Weedle 32 52 Hairy Bug bug 14 Kakuna 100 71 Cocoon bug 15 Beedrill 295 159 Poison Bee bug 4 Charmander 85 65 Lizard fire 5 Charmeleon 190 142 Flame fire 6 Charizard 905 209 Flame fire 7 Squirtle 90 66 Tiny Turtle water 8 Wartortle 225 143 Turtle water 9 Blastoise 855 210 Shellfish water 1 Bulbasaur 69 64 Seed grass 2 Ivysaur 130 141 Seed grass POKEDEXID NAME WEIGHT BASEEXPERIENCE GENUS TYPE ---------- -------------------- ---------- -------------- -------------------- -------------------- 3 Venusaur 1000 208 Seed grass 25 Pikachu 60 82 Mouse electric 26 Raichu 300 122 Mouse electric 30 rows selected. SQL> select type, species, AVG(baseexperience) 2 from pkk 3 group by type, species 4 having count(*) > 1 5 ; group by type, species * ERROR at line 3: ORA-00904: "SPECIES": invalid identifier SQL> select type, genus, AVG(baseexperience) 2 from pkk 3 group by type, genus 4 having count(*) > 1 5 ; TYPE GENUS AVG(BASEEXPERIENCE) -------------------- -------------------- ------------------- ground Mouse 128 normal Bird 142.5 fire Flame 175.5 normal Mouse 86.5 bug Cocoon 71.5 grass Seed 137.666667 electric Mouse 102 poison Poison Pin 88 normal Tiny Bird 56.5 9 rows selected. SQL> SQL> select * 2 from ( 3 select type, genus, AVG(baseexperience) AS exp 4 from pkk 5 group by type, genus 6 having count(*) > 1 7 ) t 8 where exp = (SELECT MAX(exp) 9 from ( select type, genus, AVG(baseexperience) as exp 10 from pkk 11 where type = t.type 12 group by type, genus 13 ) 14 ) 15 ; where type = t.type * ERROR at line 11: ORA-00904: "T"."TYPE": invalid identifier SQL> select type , genus, AVG(baseexperience) AS exp 2 from pkk 3 group by type, genus 4 having count(*) > 1 5 ; TYPE GENUS EXP -------------------- -------------------- ---------- ground Mouse 128 normal Bird 142.5 fire Flame 175.5 normal Mouse 86.5 bug Cocoon 71.5 grass Seed 137.666667 electric Mouse 102 poison Poison Pin 88 normal Tiny Bird 56.5 9 rows selected. SQL> select * 2 from ( 3 select type , genus, AVG(baseexperience) AS exp 4 from pkk 5 group by type, genus 6 having count(*) > 1 7 ) t 8 where exp = (SELECT MAX(exp) 9 from ( select type, genus, AVG(baseexperience) as exp 10 from pkk 11 where type = t.type 12 group by type, genus 13 ) 14 ) 15 ; where type = t.type * ERROR at line 11: ORA-00904: "T"."TYPE": invalid identifier SQL> SQL> select * 2 from ( 3 select type , genus, AVG(baseexperience) AS exp 4 from pkk 5 group by type, genus 6 having count(*) > 1 7 ) t 8 where exp = (SELECT MAX(exp) 9 from ( select type, genus, AVG(baseexperience) as exp 10 from pkk p 11 where p.type = t.type 12 group by p.type, p.genus 13 ) 14 ) 15 ; where p.type = t.type * ERROR at line 11: ORA-00904: "T"."TYPE": invalid identifier SQL> SQL> select * 2 from ( 3 select type , genus, AVG(baseexperience) AS exp 4 from pkk 5 group by type, genus 6 having count(*) > 1 7 ) t 8 where exp >= ALL( select AVG(baseexperience) as exp 9 from pkk p 10 where p.type = t.type 11 group by p.type, p.genus 12 ) 13 ; TYPE GENUS EXP -------------------- -------------------- ---------- ground Mouse 128 fire Flame 175.5 grass Seed 137.666667 electric Mouse 102 SQL> select distinct type from pkk; TYPE -------------------- electric grass bug water poison ground normal fire 8 rows selected. SQL> exit