1- all the pets and all their attributes. SQL> select * from pet; NAME OWNER SPECIES S BIRTH DEATH WEIGHT CUTENESS ------------ ------------ ------------ - --------- --------- ---------- ---------- Fluffy Harold cat f 04-FEB-93 10 78 Claws Gwen cat m 17-MAR-94 6 3 Buffy Harold dog f 13-MAY-89 23 72 Fang Benny dog m 27-AUG-90 40 38 Bowser Diane dog m 31-AUG-79 29-JUL-95 30 65 Chirpy Gwen bird f 11-SEP-98 2 41 Whistler Gwen bird 09-DEC-97 1 15 Slim Benny snake m 29-APR-96 3 9 8 rows selected. 2- Benny's pets. SQL> select * from pet where owner='Benny'; NAME OWNER SPECIES S BIRTH DEATH WEIGHT CUTENESS ------------ ------------ ------------ - --------- --------- ---------- ---------- Fang Benny dog m 27-AUG-90 40 38 Slim Benny snake m 29-APR-96 3 9 3- non-cats SQL> select * from pet where species!='cat'; NAME OWNER SPECIES S BIRTH DEATH WEIGHT CUTENESS ------------ ------------ ------------ - --------- --------- ---------- ---------- Buffy Harold dog f 13-MAY-89 23 72 Fang Benny dog m 27-AUG-90 40 38 Bowser Diane dog m 31-AUG-79 29-JUL-95 30 65 Chirpy Gwen bird f 11-SEP-98 2 41 Whistler Gwen bird 09-DEC-97 1 15 Slim Benny snake m 29-APR-96 3 9 6 rows selected. 4- the name, species, and DOB of all the pets. SQL> select name,species,birth from pet; NAME SPECIES BIRTH ------------ ------------ --------- Fluffy cat 04-FEB-93 Claws cat 17-MAR-94 Buffy dog 13-MAY-89 Fang dog 27-AUG-90 Bowser dog 31-AUG-79 Chirpy bird 11-SEP-98 Whistler bird 09-DEC-97 Slim snake 29-APR-96 8 rows selected. 5- the name, species, and DOB of the female pets. SQL> select name,species,birth from pet where sex='f'; NAME SPECIES BIRTH ------------ ------------ --------- Fluffy cat 04-FEB-93 Buffy dog 13-MAY-89 Chirpy bird 11-SEP-98 6- pets born before 1995 SQL> select * from pet where birth<'1-jan-1995'; NAME OWNER SPECIES S BIRTH DEATH WEIGHT CUTENESS ------------ ------------ ------------ - --------- --------- ---------- ---------- Fluffy Harold cat f 04-FEB-93 10 78 Claws Gwen cat m 17-MAR-94 6 3 Buffy Harold dog f 13-MAY-89 23 72 Fang Benny dog m 27-AUG-90 40 38 Bowser Diane dog m 31-AUG-79 29-JUL-95 30 65 7- four-legged pets SQL> select * from pet where species='cat' or species='dog'; NAME OWNER SPECIES S BIRTH DEATH WEIGHT CUTENESS ------------ ------------ ------------ - --------- --------- ---------- ---------- Fluffy Harold cat f 04-FEB-93 10 78 Claws Gwen cat m 17-MAR-94 6 3 Buffy Harold dog f 13-MAY-89 23 72 Fang Benny dog m 27-AUG-90 40 38 Bowser Diane dog m 31-AUG-79 29-JUL-95 30 65 8- non-four-legged pets SQL> select * from pet where species!='cat' and species!='dog'; NAME OWNER SPECIES S BIRTH DEATH WEIGHT CUTENESS ------------ ------------ ------------ - --------- --------- ---------- ---------- Chirpy Gwen bird f 11-SEP-98 2 41 Whistler Gwen bird 09-DEC-97 1 15 Slim Benny snake m 29-APR-96 3 9 9- female four-legged pets SQL> select * from pet where (species='cat' or species='dog') and sex='f'; NAME OWNER SPECIES S BIRTH DEATH WEIGHT CUTENESS ------------ ------------ ------------ - --------- --------- ---------- ---------- Fluffy Harold cat f 04-FEB-93 10 78 Buffy Harold dog f 13-MAY-89 23 72 10- the species of pets (no repeats!) SQL> select distinct species from pet; SPECIES ------------ cat dog bird snake 11- the distinct owners of female four-legged pets SQL> select distinct owner from pet where (species='cat' or species='dog') and sex='f'; OWNER ------------ Harold 12- all the pets listed in owner order SQL> select * from pet order by owner; NAME OWNER SPECIES S BIRTH DEATH WEIGHT CUTENESS ------------ ------------ ------------ - --------- --------- ---------- ---------- Fang Benny dog m 27-AUG-90 40 38 Slim Benny snake m 29-APR-96 3 9 Bowser Diane dog m 31-AUG-79 29-JUL-95 30 65 Whistler Gwen bird 09-DEC-97 1 15 Chirpy Gwen bird f 11-SEP-98 2 41 Claws Gwen cat m 17-MAR-94 6 3 Buffy Harold dog f 13-MAY-89 23 72 Fluffy Harold cat f 04-FEB-93 10 78 8 rows selected. 13- all the pets listed in owner order by name order (owner as primary sort, name as secondary sort) SQL> select * from pet order by owner,name; NAME OWNER SPECIES S BIRTH DEATH WEIGHT CUTENESS ------------ ------------ ------------ - --------- --------- ---------- ---------- Fang Benny dog m 27-AUG-90 40 38 Slim Benny snake m 29-APR-96 3 9 Bowser Diane dog m 31-AUG-79 29-JUL-95 30 65 Chirpy Gwen bird f 11-SEP-98 2 41 Claws Gwen cat m 17-MAR-94 6 3 Whistler Gwen bird 09-DEC-97 1 15 Buffy Harold dog f 13-MAY-89 23 72 Fluffy Harold cat f 04-FEB-93 10 78 8 rows selected. 14- all the pets listed in age order, youngest first SQL> select * from pet order by birth desc; NAME OWNER SPECIES S BIRTH DEATH WEIGHT CUTENESS ------------ ------------ ------------ - --------- --------- ---------- ---------- Chirpy Gwen bird f 11-SEP-98 2 41 Whistler Gwen bird 09-DEC-97 1 15 Slim Benny snake m 29-APR-96 3 9 Claws Gwen cat m 17-MAR-94 6 3 Fluffy Harold cat f 04-FEB-93 10 78 Fang Benny dog m 27-AUG-90 40 38 Buffy Harold dog f 13-MAY-89 23 72 Bowser Diane dog m 31-AUG-79 29-JUL-95 30 65 8 rows selected. 17- the DOB and name (in that column order) of all the pets listed in age order, oldest first, SQL> select birth,name from pet order by birth; BIRTH NAME --------- ------------ 31-AUG-79 Bowser 13-MAY-89 Buffy 27-AUG-90 Fang 04-FEB-93 Fluffy 17-MAR-94 Claws 29-APR-96 Slim 09-DEC-97 Whistler 11-SEP-98 Chirpy 8 rows selected. 18- the name,owner,DOB of all the pets listed in name order SQL> select name,owner,birth from pet order by name; NAME OWNER BIRTH ------------ ------------ --------- Bowser Diane 31-AUG-79 Buffy Harold 13-MAY-89 Chirpy Gwen 11-SEP-98 Claws Gwen 17-MAR-94 Fang Benny 27-AUG-90 Fluffy Harold 04-FEB-93 Slim Benny 29-APR-96 Whistler Gwen 09-DEC-97 8 rows selected. 20- pets of unknown sex SQL> select * from pet where sex is null; NAME OWNER SPECIES S BIRTH DEATH WEIGHT CUTENESS ------------ ------------ ------------ - --------- --------- ---------- ---------- Whistler Gwen bird 09-DEC-97 1 15 21- pets who have died SQL> select * from pet where death is not null; NAME OWNER SPECIES S BIRTH DEATH WEIGHT CUTENESS ------------ ------------ ------------ - --------- --------- ---------- ---------- Bowser Diane dog m 31-AUG-79 29-JUL-95 30 65 22- pets whose names start with a 'C' SQL> select * from pet where name like 'C%'; NAME OWNER SPECIES S BIRTH DEATH WEIGHT CUTENESS ------------ ------------ ------------ - --------- --------- ---------- ---------- Claws Gwen cat m 17-MAR-94 6 3 Chirpy Gwen bird f 11-SEP-98 2 41 23- pets whose owners have 5 character names SQL> select * from pet where owner like '_____'; NAME OWNER SPECIES S BIRTH DEATH WEIGHT CUTENESS ------------ ------------ ------------ - --------- --------- ---------- ---------- Fang Benny dog m 27-AUG-90 40 38 Bowser Diane dog m 31-AUG-79 29-JUL-95 30 65 Slim Benny snake m 29-APR-96 3 9 24- pets whose owners have at least 5 character names SQL> select * from pet where owner like '_____%'; NAME OWNER SPECIES S BIRTH DEATH WEIGHT CUTENESS ------------ ------------ ------------ - --------- --------- ---------- ---------- Fluffy Harold cat f 04-FEB-93 10 78 Buffy Harold dog f 13-MAY-89 23 72 Fang Benny dog m 27-AUG-90 40 38 Bowser Diane dog m 31-AUG-79 29-JUL-95 30 65 Slim Benny snake m 29-APR-96 3 9 25- pets whose names do not contain a 'y' SQL> select * from pet where owner not like '%y%'; NAME OWNER SPECIES S BIRTH DEATH WEIGHT CUTENESS ------------ ------------ ------------ - --------- --------- ---------- ---------- Fluffy Harold cat f 04-FEB-93 10 78 Claws Gwen cat m 17-MAR-94 6 3 Buffy Harold dog f 13-MAY-89 23 72 Bowser Diane dog m 31-AUG-79 29-JUL-95 30 65 Chirpy Gwen bird f 11-SEP-98 2 41 Whistler Gwen bird 09-DEC-97 1 15 6 rows selected. 26- the number of records SQL> select count(*) from pet; COUNT(*) ---------- 8 27- number of cats SQL> select count(*) from pet where species='cat'; COUNT(*) ---------- 2 28- number of species SQL> select count(distinct species) from pet; COUNT(DISTINCTSPECIES) ---------------------- 4 29- number of non-four-legged pets SQL> select count(*) as "#non-4legged pets" from pet where species!='cat' and species!='dog'; #non-4legged pets ----------------- 3 - the AVG(), MIN(), MAX(), and SUM() aggreagate functions are designed to work with numeric columns but can be applied in some cases to string columns: 30- the min name SQL> select min(name) from pet; MIN(NAME) ------------ Bowser 31- the DOB of the youngest pet SQL> select max(birth) from pet; MAX(BIRTH --------- 11-SEP-98 32- number of pets per species SQL> select species,count(*) from pet group by species; SPECIES COUNT(*) ------------ ---------- cat 2 dog 3 bird 2 snake 1 32a- sum of the weights of pets per owner (i.e. if owner has to carry all his pets, how much would that be) SQL> select owner,sum(weight) from pet group by owner; OWNER SUM(WEIGHT) ------------ ----------- Harold 33 Benny 43 Gwen 9 Diane 30 32b -which sex is cuter, on average SQL> select sex,avg(cuteness) from pet group by sex; S AVG(CUTENESS) - ------------- 15 f 63.6666667 m 28.75 33- number of pets per owner, per species SQL> select owner,species,count(*) from pet group by owner,species; OWNER SPECIES COUNT(*) ------------ ------------ ---------- Gwen cat 1 Benny dog 1 Harold cat 1 Harold dog 1 Gwen bird 2 Diane dog 1 Benny snake 1 7 rows selected. 34- name,species,weight,cuteness and cuteness per kilogram of each pet SQL> select name,species,weight,cuteness,cuteness/weight from pet; NAME SPECIES WEIGHT CUTENESS CUTENESS/WEIGHT ------------ ------------ ---------- ---------- --------------- Fluffy cat 10 78 7.8 Claws cat 6 3 .5 Buffy dog 23 72 3.13043478 Fang dog 40 38 .95 Bowser dog 30 65 2.16666667 Chirpy bird 2 41 20.5 Whistler bird 1 15 15 Slim snake 3 9 3 8 rows selected. 34a- "cuticality" is the square root of the cuteness per kilogram. use the SQRT() function. name, species,cuticality of each pet. SQL> select name,species,sqrt(cuteness/weight) as cuticality from pet; NAME SPECIES CUTICALITY ------------ ------------ ---------- Fluffy cat 2.79284801 Claws cat .707106781 Buffy dog 1.76930347 Fang dog .974679434 Bowser dog 1.47196014 Chirpy bird 4.52769257 Whistler bird 3.87298335 Slim snake 1.73205081 8 rows selected. 34b- smallest cuticality value SQL> select min(sqrt(cuteness/weight)) from pet; MIN(SQRT(CUTENESS/WEIGHT)) -------------------------- .707106781