Part 4 - Single Table Queries

Question

1.

[SQL Week 1 Question 100-001] SELECT just the Name and Gender columns from litcharacters.

Order ascending by idLitCharacters (you may display the idLitCharacters if you like but it is not

required. You need to sort by that field.)

SELECT Name, Gender FROM litcharacters ORDER BY idLitCharacters;

2. [SQL Week 1 Question 100-002] List the different creature types are there in the litcharacters

database? For example, if you had three humans and one animal, that would be two types -

human and animal.

SELECT DISTINCT Creature from litcharacters:

3. [SQL Week 1 Question 100-003] Make output which includes only the Name, Creature Type, and

Cre SubType for all the Animals. Order by ascending idLitCharacters (you may display the

idLitCharacters if you like but it is not required. Whether or not you display the idLitCharacters

field, you need to sort by it.)

SELECT Name, CreatureIvoe. CreatureSubType FROM Jitcharacters Order By idLitCharacters

ASC;

4. [SQL Week 1 Question 100-004] Your editor wants a list of the Name, Creature Type, Creature

SubType and Gender for all new creatures - those who have not made an appearance yet (so

their Appearance total is 0.) Order by ascending idLitCharacters (you may display the

idLitCharacters if you like but it is not required. Whether or not you display the idLitCharacters

field, you need to sort by it.)

SELECT Name, creatureType, CreatureSubtype, Gender FROM Litcharacters WHERE 'appearance-

O' ORDER BY idLitCharacters ASC;

5. [SQL Week 1 Question 100-005]

Now your editor wants a list of the Name, Creature Type, Creature SubType and Gender for all

well-worn creatures - those who have made 5 or more appearances. Include those who have

made exactly 5 appearances. Order by ascending idLitCharacters.

SELECT Name, creatureType, CreatureSubtype, Gender FROM litcharacters WHERE appearances

>= 5 ORDER BY jdLitCharacters ASC;

6. [SQL Week 1 Question 100-006] They want to know the mid-popular ones as well. Create a list

of the Name, Creature Type, Creature SubType and Gender for all mid-popular creatures - those

who have made between 3 and 5 appearances. Include those who have made exactly 3 and

exactly 5 appearances. Order by ascending idLitCharacters.