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