SQL> select * from pokemon where pokedexid < 20;
POKEDEXID NAME
---------- --------------------
1 Bulbasaur
2 Ivysaur
3 Venusaur
4 Charmander
5 Charmeleon
6 Charizard
7 Squirtle
8 Wartortle
9 Blastoise
10 Caterpie
11 Metapod
POKEDEXID NAME
---------- --------------------
12 Butterfree
13 Weedle
14 Kakuna
15 Beedrill
16 Pidgey
17 Pidgeotto
18 Pidgeot
19 Rattata
19 rows selected.
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
73 rows selected.
SQL> select * from pfirst;
POKEMON HEIGHT WEIGHT RATIO
-------------------- ---------- ---------- ----------
Bulbasaur 7 69 9.85714286
Ivysaur 10 130 13
Venusaur 20 1000 50
Charmander 6 85 14.1666667
Charmeleon 11 190 17.2727273
Charizard 17 905 53.2352941
Squirtle 5 90 18
Wartortle 10 225 22.5
Blastoise 16 855 53.4375
Caterpie 3 29 9.66666667
10 rows selected.
SQL> set linesize 120
SQL> set pagesize 30
SQL> select MAX(HEIGHT) from pfirst;
MAX(HEIGHT)
-----------
20
SQL> select MIN(HEIGHT) from pfirst;
MIN(HEIGHT)
-----------
3
SQL> select AVG(HEIGHT) from pfirst;
AVG(HEIGHT)
-----------
10.5
SQL> select SUM(HEIGHT) from pfirst;
SUM(HEIGHT)
-----------
105
SQL> select * from pfirst;
POKEMON HEIGHT WEIGHT RATIO
-------------------- ---------- ---------- ----------
Bulbasaur 7 69 9.85714286
Ivysaur 10 130 13
Venusaur 20 1000 50
Charmander 6 85 14.1666667
Charmeleon 11 190 17.2727273
Charizard 17 905 53.2352941
Squirtle 5 90 18
Wartortle 10 225 22.5
Blastoise 16 855 53.4375
Caterpie 3 29 9.66666667
10 rows selected.
SQL> select MAX(HEIGHT)
2 from pfirst
3 where weight < 200
4 ;
MAX(HEIGHT)
-----------
11
SQL> select * from pfirst where weight < 200;
POKEMON HEIGHT WEIGHT RATIO
-------------------- ---------- ---------- ----------
Bulbasaur 7 69 9.85714286
Ivysaur 10 130 13
Charmander 6 85 14.1666667
Charmeleon 11 190 17.2727273
Squirtle 5 90 18
Caterpie 3 29 9.66666667
6 rows selected.
SQL> select AVG(WEIGHT)
2 from pfirst
3 where height > 10.5
4 ;
AVG(WEIGHT)
-----------
737.5
SQL> select MIN(HEIGHT), AVG(HEIGHT), MAX(HEIGHT)
2 from pfirst
3 ;
MIN(HEIGHT) AVG(HEIGHT) MAX(HEIGHT)
----------- ----------- -----------
3 10.5 20
SQL> select MIN(HEIGHT) AS smallest
2 from pfirst;
SMALLEST
----------
3
SQL> select * from pfirst;
POKEMON HEIGHT WEIGHT RATIO
-------------------- ---------- ---------- ----------
Bulbasaur 7 69 9.85714286
Ivysaur 10 130 13
Venusaur 20 1000 50
Charmander 6 85 14.1666667
Charmeleon 11 190 17.2727273
Charizard 17 905 53.2352941
Squirtle 5 90 18
Wartortle 10 225 22.5
Blastoise 16 855 53.4375
Caterpie 3 29 9.66666667
10 rows selected.
SQL> select MAX(HEIGHT + WEIGHT) from pfirst;
MAX(HEIGHT+WEIGHT)
------------------
1020
SQL> select AVG(HEIGHT* RATIO) from pfirst;
AVG(HEIGHT*RATIO)
-----------------
357.8
SQL> select MAX(HEIGHT*RATIO) from pfirst;
MAX(HEIGHT*RATIO)
-----------------
1000
SQL> select MAX(10*HEIGHT + WEIGHT) from pfirst;
MAX(10*HEIGHT+WEIGHT)
---------------------
1200
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
CONTINENTS
LIST
MODELS
CAMPUSES
CARMAKERS
COUNTRIES
MAKES
CARDATA
ST
SHARKS
AIRPORTS
MARATHON
FACULTY
DISCENR
DISCIPLINES
ENROLLMENTS
DEGREES
FEES
FLIGHTS
AIRLINES
TEACHERS
LEAGUES
ATTENDANCE
T
ST01
NOPKEY
PK
TABLE_NAME
------------------------------
ROOMS
CUSTOMERS
MINEFIELD
RECEIPTS
ITEMS
FOO1
BEERS
MYBOOKS
BEERS1
FOO2
FOO3
FOO4
FOO5
BARS
DRINKS
BEERS2
LIKETEST
TIMES
CHECKIN
FOOBAR
ATT2007
SEARCH
KODIE
CNTR
ATTN
LOCATIONS
STUFF
TABLE_NAME
------------------------------
VECTORPAIRS
VECTORS
FST
AGGS
PTS
MYLIST
ALLELES
MYVECTORS
POK
PNEXT
CC
SPECIES
ATTRIBUTES
TYPES
ANEW
POKEMON
EVOLUTION
REACTORS
PFIRST
73 rows selected.
SQL> CREATE TABLE POK AS
2 (SELECT p.pokedexid, p.name, a.height, a.weight, a.baseexperience, s.genus
3 from pokemon p, attributes a, species s
4 where p.pokedexid <= 30 and p.pokedexid = a.pokedexid and p.pokedexid = s.pokedexid)
5 ;
CREATE TABLE POK AS
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL>
SQL> CREATE TABLE PPK AS
2 (SELECT p.pokedexid, p.name, a.height, a.weight, a.baseexperience, s.genus
3 from pokemon p, attributes a, species s
4 where p.pokedexid <= 30 and p.pokedexid = a.pokedexid and p.pokedexid = s.pokedexid)
5 ;
Table 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 MAX(BASEEXPERIENCE) from ppk where genus = 'Seed';
MAX(BASEEXPERIENCE)
-------------------
208
SQL> select * from ppk
2 where MAX(baseexperience);
where MAX(baseexperience)
*
ERROR at line 2:
ORA-00934: group function is not allowed here
SQL> select *
2 from ppk
3 where baseexperience = MAX(baseexperience)
4 ;
where baseexperience = MAX(baseexperience)
*
ERROR at line 3:
ORA-00934: group function is not allowed here
SQL> select AVG(baseexperience)
2 from ppk
3 where genus = 'Mouse';
AVG(BASEEXPERIENCE)
-------------------
105.5
SQL> select AVG(baseexperience)
2 from ppk where genus = 'Seed';
AVG(BASEEXPERIENCE)
-------------------
137.666667
SQL> select genus, AVG(baseexperience)
2 from ppk
3 ;
select genus, AVG(baseexperience)
*
ERROR at line 1:
ORA-00937: not a single-group group function
SQL> select COUNT(*) from ppk;
COUNT(*)
----------
30
SQL> select count(genus) from ppk;
COUNT(GENUS)
------------
30
SQL> select count(pokedexid) from ppk;
COUNT(POKEDEXID)
----------------
30
SQL> insert into ppk(pokedexid, name, genus) values(41, 'Bob', 'Human');
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
41 Bob Human
31 rows selected.
SQL> set NULL NULL
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
41 Bob NULL NULL NULL Human
31 rows selected.
SQL> select count(*) from ppk;
COUNT(*)
----------
31
SQL> select count(genus) from ppk;
COUNT(GENUS)
------------
31
SQL> select count(height) from ppk;
COUNT(HEIGHT)
-------------
30
SQL> select count(distinct genus) from ppk;
COUNT(DISTINCTGENUS)
--------------------
19
SQL> select count(*) from ppk where genus = 'Seed' OR genus =
2
SQL> select count(*) from ppk where genus = 'Seed' OR genus = 'Mouse';
COUNT(*)
----------
9
SQL> select count(*) from pokemon;
COUNT(*)
----------
647
SQL> select count(*) from species where genus ='Seed';
COUNT(*)
----------
4
SQL> select count(distinct genus) from species;
COUNT(DISTINCTGENUS)
--------------------
474
SQL> select MAX(baseexp)
2 from species s, attributes a
3 where s.pokedexid = a.pokedexid
4 and species = 'Turtle';
and species = 'Turtle'
*
ERROR at line 4:
ORA-00904: "SPECIES": invalid identifier
SQL> select table_name from user
2
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
CONTINENTS
LIST
MODELS
CAMPUSES
CARMAKERS
COUNTRIES
MAKES
CARDATA
ST
SHARKS
AIRPORTS
MARATHON
FACULTY
DISCENR
DISCIPLINES
ENROLLMENTS
DEGREES
FEES
FLIGHTS
AIRLINES
TEACHERS
LEAGUES
ATTENDANCE
T
ST01
NOPKEY
PK
TABLE_NAME
------------------------------
ROOMS
CUSTOMERS
MINEFIELD
RECEIPTS
ITEMS
FOO1
BEERS
MYBOOKS
BEERS1
FOO2
FOO3
FOO4
FOO5
BARS
DRINKS
BEERS2
LIKETEST
TIMES
CHECKIN
FOOBAR
ATT2007
SEARCH
KODIE
CNTR
ATTN
LOCATIONS
STUFF
TABLE_NAME
------------------------------
VECTORPAIRS
VECTORS
FST
AGGS
PTS
MYLIST
ALLELES
MYVECTORS
POK
PNEXT
CC
SPECIES
ATTRIBUTES
TYPES
ANEW
POKEMON
EVOLUTION
REACTORS