Find the number of certifications held by people grouped by planet. this should have two columns the first, "name" will be the names of planets that have at least one certification. the second column should be "certcount" an

The tables are the following:

CREATE TABLE `bsg_cert` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `title` varchar(255) NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB

CREATE TABLE `bsg_cert_people` (

  `cid` int(11) NOT NULL DEFAULT '0',

  `pid` int(11) NOT NULL DEFAULT '0',

  PRIMARY KEY (`cid`,`pid`),

  KEY `pid` (`pid`),

  CONSTRAINT `bsg_cert_people_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `bsg_cert` (`id`),

  CONSTRAINT `bsg_cert_people_ibfk_2` FOREIGN KEY (`pid`) REFERENCES `bsg_people` (`id`)

) ENGINE=InnoDB

CREATE TABLE `bsg_people` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `fname` varchar(255) NOT NULL,

  `lname` varchar(255) DEFAULT NULL,

  `homeworld` int(11) DEFAULT NULL,

  `age` int(11) DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `homeworld` (`homeworld`),

  CONSTRAINT `bsg_people_ibfk_1` FOREIGN KEY (`homeworld`) REFERENCES `bsg_planets` (`id`) ON DELETE SET NULL ON UPDATE CASCADE

) ENGINE=InnoDB

CREATE TABLE `bsg_planets` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(255) NOT NULL,

  `population` bigint(20) DEFAULT NULL,

  `language` varchar(255) DEFAULT NULL,

  `capital` varchar(255) DEFAULT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `name` (`name`)

) ENGINE=InnoDB

Joining them all up doing a count with a group by should do the trick:

SELECT  planet.name ,

    COUNT(*) AS cert_count

FROM    bsg_cert_people people_cert

    JOIN bsg_people people ON people.id = people_cert.pid

    JOIN bsg_planet planet ON people.homeworld = planet.id

GROUP BY planet.name

Or we can also use this syntax to get the same result:

SELECT pl.name, count(cert) AS "CertCount"

FROM bsg_planets pl

JOIN bsg_people pe ON pl.id = pe.homeworld

JOIN bsg_cert_people cp ON cp.pid = pe.id

GROUP BY pl.id

d

explanation:

v=uf/(u-f)

= 5.10/(5-10) convex lens has positive focal length

=50/-5

=-10 cm (formed at other side)