In a database class i need some SQL Queries help here is the the tables and rela
ID: 3740112 • Letter: I
Question
In a database class i need some SQL Queries help
here is the the tables and relations :
ANIMAL (AnimalID, NickName, Species, Sex, Age, Exhibit, Keeper)
EXHIBIT (ExhibitName, Environment)
KEEPER (KeeperID, LastName, FirstName)
Exhibit in ANIMAL must exist in ExhibitName in EXHIBIT.
Keeper in ANIMAL must exist in KeeperID in KEEPER.
The following data has already been inserted in Zoo Starter.accdb:
ANIMAL
AnimalID
NickName
Species
Sex
Age
Exhibit
Keeper
1
Biggo
Elephant
Male
37
Africa
1
2
Biggette
Elephant
Female
27
Africa
1
3
Josie
Camel
Female
17
Africa
1
4
Flipper
Porpoise
Male
7
Ocean
2
5
Patty
Porpoise
Female
5
Ocean
2
6
Sally
Seal
Female
8
Ocean
3
7
Tusky
Walrus
Male
12
Ocean
3
EXHIBIT
ExhibitName
Environment
Africa
Grassland
Ocean
Saltwater
KEEPER
KeeperID
LastName
FirstName
1
Abrams
Beth
2
Cooley
Doug
3
Evertt
Fran
Create an SQL query to display the nickname, age, exhibit, and environment of all animals under 20 years old. Create, run, and save this SQL query as SQLQuery-Test-D.
Create an SQL query to display the nickname, age, exhibit, environment, and keeper (first and last names) of all male animals at least 10 years old. Create, run, and save this SQL query as SQLQuery-Test-E.
AnimalID
NickName
Species
Sex
Age
Exhibit
Keeper
1
Biggo
Elephant
Male
37
Africa
1
2
Biggette
Elephant
Female
27
Africa
1
3
Josie
Camel
Female
17
Africa
1
4
Flipper
Porpoise
Male
7
Ocean
2
5
Patty
Porpoise
Female
5
Ocean
2
6
Sally
Seal
Female
8
Ocean
3
7
Tusky
Walrus
Male
12
Ocean
3
Explanation / Answer
Create an SQL query to display the nickname, age, exhibit, and environment of all animals under 20 years old. Create, run, and save this SQL query as SQLQuery-Test-D.
SELECT nickname,species,exhibit,environment from animal,exhibit where animal.age<12 AND animal.sex='male' AND animal.exhibit=exhibit.exhibitName
The query results in
nickname species exhibit environment
flipper porpoise ocean saltwater.
Create an SQL query to display the nickname, age, exhibit, environment, and keeper (first and last names) of all male animals at least 10 years old. Create, run, and save this SQL query as SQLQuery-Test-E.
SELECT nickname,species,exhibit,environment,firstName,lastName from animal,exhibit,keeper where animal.age>5 AND animal.sex='female'AND exhibit.environment='saltwater' AND animal.exhibit=exhibit.exhibitName AND animal.keeper=keeper.keeperId
the above query results in
nickname species exhibit environment firstName lastName
sally seal ocean saltwater fran evertt