SQL – nested and complex queries
Part 1
Deliverable : warcraft/mission5.pdf
- Display the classes that have no members.
- Display the races that have no members.
- Display the number of members by faction thanks to the race table (join).
- Display the number of members by race. We will only display the races that have more than 3 members.
- Display the mounts which are not assigned to members.
- 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
- Propose a data model to add the bosses and zones.
- 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.