Last active
March 23, 2020 16:07
-
-
Save riccardosacco/fd4b2f3d2562c19e473cbcb03eba6187 to your computer and use it in GitHub Desktop.
SQL Advanced
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| CREATE TABLE `Kingdom` ( | |
| `id` int(11) NOT NULL AUTO_INCREMENT, | |
| `name` varchar(90) NOT NULL, | |
| PRIMARY KEY (`id`) | |
| ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8; | |
| INSERT INTO `Kingdom` VALUES (1,'Logre'),(2,'Caledonie'),(3,'Carmelide'),(4,'Vannes'),(5,'Galles'),(6,'Aquitaine'); | |
| CREATE TABLE `Role` ( | |
| `id` int(11) NOT NULL AUTO_INCREMENT, | |
| `role` varchar(80) NOT NULL, | |
| PRIMARY KEY (`id`) | |
| ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; | |
| INSERT INTO `Role` VALUES (1,'roi'),(2,'chevalier'),(3,'magicien'); | |
| CREATE TABLE `Person` ( | |
| `id` int(11) NOT NULL AUTO_INCREMENT, | |
| `firstname` varchar(80) NOT NULL, | |
| `lastname` varchar(80) DEFAULT NULL, | |
| `age` int(11) NOT NULL, | |
| `role_id` int(11) DEFAULT NULL, | |
| `kingdom_id` int(11) DEFAULT NULL, | |
| PRIMARY KEY (`id`), | |
| KEY `fk_Person_Kingdom_idx` (`kingdom_id`), | |
| KEY `fk_Person_Role_idx` (`role_id`), | |
| CONSTRAINT `fk_Person_kingdom` FOREIGN KEY (`kingdom_id`) REFERENCES `Kingdom` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, | |
| CONSTRAINT `fk_Person_role` FOREIGN KEY (`role_id`) REFERENCES `Role` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION | |
| ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8; | |
| INSERT INTO `Person` VALUES (1,'Arthur','Pendragon',35,1,1),(2,'Guenièvre',NULL,30,NULL,3),(3,'Merlin',NULL,850,3,NULL),(4,'Perceval',NULL,36,2,5),(5,'Caradoc',NULL,32,2,4),(6,'Calogrenant',NULL,44,1,2),(7,'Leodagan',NULL,47,1,3),(8,'Lancelot','Du Lac',33,2,NULL),(9,'Elias','De Kelliwic\'h',52,3,NULL),(10,'Mevanwi','',28,NULL,4),(11,'Yvain','',23,2,3); |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- The first name, last name and age of the characters | |
| SELECT `firstname`,`lastname`,`age` FROM `Person` | |
| -- The first name, last name of the characters and their kingdom, only for those connected to a kingdom | |
| SELECT `firstname`,`lastname`,`kingdom_id` FROM `Person` WHERE `kingdom_id` IS NOT NULL | |
| -- The same as above, including all of the characters (LEFT JOIN) | |
| SELECT `Person`.`firstname`,`Person`.`lastname`,`Kingdom`.`name` AS kingdom_name FROM `Person` LEFT JOIN `Kingdom` ON `Person`.`kingdom_id` = `Kingdom`.`id` | |
| -- The average age of the characters | |
| SELECT AVG(`age`) FROM `Person` | |
| -- The number of characters per kingdom (include kingdoms with no characters) (RIGHT JOIN) | |
| SELECT COUNT(*),`Kingdom`.`name` FROM `Person` RIGHT JOIN `Kingdom` ON `Person`.`kingdom_id` = `Kingdom`.`id` GROUP BY `Kingdom`.`name` | |
| -- The average age by role | |
| SELECT AVG(`age`),`Role`.`role` FROM `Person` RIGHT JOIN `Role` ON `Person`.`role_id` = `Role`.`id` GROUP BY `Role`.`role` | |
| -- Retrieve the average of all the characters which are not magicians | |
| SELECT AVG(`age`) FROM `Person` LEFT JOIN `Role` ON `Person`.`role_id` = `Role`.`id` WHERE NOT `Role`.`role` = "magicien" |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment