SQL – nested and complex queries

Part 1

Deliverable : warcraft/mission5.pdf

  1. Display the classes that have no members.
  2. Display the races that have no members.
  3. Display the number of members by faction thanks to the race table (join).
  4. Display the number of members by race. We will only display the races that have more than 3 members.
  5. Display the mounts which are not assigned to members.
  6. Display the guilds which do not have all the races thanks to the previous request.

 

select distinct members.members_character_guild from members
where members.members_character_race NOT IN(
select races.races_id from races 
where races.races_id !=all(
select members.members_character_race
from members ))

 

7 – Show classes that are not used in members. Show the guilds that don’t have all the classes.
8 – Show the number of mounts by class whose faction is alliance
9 – Show the number of mounts by class which has more than 1 member.
10 – Display the members who don’t have all the mounts.
11 – Display the members whose lvl is lower than 100 and which has more than 5 mounts.

Part 2

Deliverable : warcraft/mission5.pdf et  warcraft/mission5.sql

  1. Propose a data model to add the bosses and zones.
  2. Add the bosses and zones to the database.

https://gitlab.com/cherryclass/wow7/blob/master/boss.sql
https://gitlab.com/cherryclass/wow7/blob/master/zones.sql

3 – Add a primary key to Zones and Bosses. Add the foreign key.
4 – The quality of the data in the zones table is not good. We want to correct this data by deleting the rows where “zones_boss_name” is null.
5 – Display the average health of the bosses by zone in normal mode.
6 – Display the zones which have a number of members
7 – Display the name of the bosses by zone of maximum 5 members
8 – Display the number of bosses per zone with maximum 25 members
9 – Display the average difference in health between the bosses in normal and heroic mode.
10 – Display the bosses grouped by 10 levels (level 1 to 10 : boss x,x,x,x level 11 to 20 x,x,x)
11 – Make a dump.