I'm new to SQL and I need some help with this task:
Database schema:
Comic(no primary key NOT NULL, title, pages, publisher)PoweredPerson(alias primary key NOT NULL, firstname NOT NULL, lastname, evilness_level)SuperHero(alias primary key?PoweredPerson NOT NULL,cape)SuperVillain(alias primary key?PoweredPerson NOT NULL)FightIn(comic primary key? Comic NOT NULL,hero primary key? SuperHero NOT NULL,villain primary key? SuperVillain NOT NULL)Now I shall declare a SQL query which gives the first and last names of those powered person(s) that fought the most enemies in just one comic.
My solution is this:
SELECT firstname,lastnameFROM fightIn f JOIN poweredperson pON f.hero = p.alias OR f.villain= p.aliasGROUP BY comic,aliasHAVING COUNT(alias)=(SELECT COUNT(alias) FROM fightIn f JOIN poweredperson p ON f.hero = p.alias OR f.villain = p.alias GROUP BY comic,alias ORDER BY COUNT(hero) DESC LIMIT 1)I want to know if my solution is correct and in case it is, whether there is a much smarter and shorter way to solve this.
Thanks in advance =)
ليست هناك تعليقات:
إرسال تعليق