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;[D
5
SQL> list
1 select genus, AVG(baseexperience)
2 from ppk
3 group by genus
4* having count(*) > 1;[D
SQL> run
1 select genus, AVG(baseexperience)
2 from ppk
3 group by genus
4* having count(*) > 1;[D
having count(*) > 1;[D
*
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