In SQL I was asked to do the following: Write a query that brings back a list of
ID: 3713436 • Letter: I
Question
In SQL I was asked to do the following:
Write a query that brings back a list of monsters, including the following information – MonsterKey, starting grade, monster class, monster element, monster name, and a count of how many of that monster are currently in circulation. This view needs to return all monsters (even those with none in circulation).
This is what I have so far:
SELECT MonsterKey, G.GradeName AS Starting_Grade, MonsterClass, MonsterElement, MonsterName,
SUM(CASE M.Active WHEN 1 then 0 end)
FROM Monsters M INNER JOIN MonsterClasses MC
ON M.MonsterClassKey = MC.MonsterClassKey
INNER JOIN MonsterElements ME
ON M.MonsterElementKey = ME.MonsterElementKey
INNER JOIN Grades G
ON M.StartingGradeKey = G.GradeKey
GROUP BY MonsterKey, G.GradeName, MonsterClass, MonsterElement, MonsterName, M.Active
ORDER BY MonsterName
I can't figure out what to count or how to do it. I need you to fix my query so it does what the instructions ask for. The database script follows.
CREATE DATABASE MonsterGame;
CREATE TABLE Grades
(
GradeKey int IDENTITY(1,1) PRIMARY KEY NOT NULL,
GradeName varchar(50) NOT NULL,
MaxLevel int NOT NULL
)
SET IDENTITY_INSERT Grades ON
INSERT Grades (GradeKey, GradeName, MaxLevel)
VALUES
(1, '1 Star', 15),
(2, '2 Star', 20),
(3, '3 Star', 25),
(4, '4 Star', 30),
(5, '5 Star', 35),
(6, '6 Star', 40)
SET IDENTITY_INSERT Grades OFF
CREATE TABLE MonsterElements
(
MonsterElementKey int IDENTITY(1,1) PRIMARY KEY NOT NULL,
MonsterElement varchar(50) NOT NULL
)
SET IDENTITY_INSERT MonsterElements ON
INSERT MonsterElements (MonsterElementKey, MonsterElement)
VALUES
(1, 'Fire'),
(2, 'Water'),
(3, 'Wind'),
(4, 'Light'),
(5, 'Dark')
SET IDENTITY_INSERT MonsterElements OFF
CREATE TABLE MonsterClasses
(
MonsterClassKey int IDENTITY(1,1) PRIMARY KEY NOT NULL,
MonsterClass varchar(50) NOT NULL,
CreateDate smalldatetime DEFAULT(GETDATE()) NOT NULL
)
SET IDENTITY_INSERT MonsterClasses ON
INSERT MonsterClasses (MonsterClassKey, MonsterClass)
VALUES
(1, 'Paladin'),
(2, 'Unicorn'),
(3, 'Pirate Captain'),
(4, 'Barbaric King'),
(5, 'Kung Fu Girl'),
(6, 'Epikion Priest'),
(7, 'Frankenstein'),
(8, 'Mummy'),
(9, 'Penguin Knight'),
(10, 'Drunken Master'),
(11, 'Living Armor')
SET IDENTITY_INSERT MonsterClasses OFF
CREATE TABLE Monsters
(
MonsterKey int IDENTITY(1,1) PRIMARY KEY NOT NULL,
StartingGradeKey int NOT NULL,
MonsterClassKey int NOT NULL,
MonsterElementKey int NOT NULL,
MonsterName varchar(100) NOT NULL,
CreateDate smalldatetime DEFAULT(GETDATE()) NOT NULL,
Active bit DEFAULT(1) NOT NULL
)
SET IDENTITY_INSERT Monsters ON
INSERT Monsters (MonsterKey, StartingGradeKey, MonsterClassKey, MonsterElementKey, MonsterName) VALUES
(1,5,1,1,'Ophilia'),
(2,5,1,2,'Josephine'),
(3,5,1,3,'Louise'),
(4,5,1,4,'Jeanne'),
(5,5,1,5,'Leona'),
(6,5,2,1,'Alexandra'),
(7,5,2,2,'Eleanor'),
(8,5,2,3,'Diane'),
(9,5,2,4,'Amelia'),
(10,5,2,5,'Helena'),
(11,4,3,1,'Carrack'),
(12,4,3,2,'Galleon'),
(13,4,3,3,'Barque'),
(14,4,3,4,'Brig'),
(15,4,3,5,'Frigate'),
(16,4,4,1,'Surtr'),
(17,4,4,2,'Aegir'),
(18,4,4,3,'Hraesvelg'),
(19,4,4,4,'Mimirr'),
(20,4,4,5,'Hrungnir'),
(21,4,6,1,'Chloe'),
(22,3,6,2,'Rina'),
(23,3,6,3,'Michelle'),
(24,4,6,4,'Iona'),
(25,3,6,5,'Rasheed'),
(26,4,5,1,'Hong Hua'),
(27,4,5,2,'Xiao Lin'),
(28,4,5,3,'Ling Ling'),
(29,4,5,4,'Liu Mei'),
(30,4,5,5,'Fei'),
(31,3,7,1,'Bulldozer'),
(32,3,7,2,'Tractor'),
(33,3,7,3,'Crane'),
(34,3,7,4,'Driller'),
(35,3,7,5,'Crawler'),
(36,3,8,1,'Sonora'),
(37,3,8,2,'Nubia'),
(38,3,8,3,'Namib'),
(39,3,8,4,'Sahara'),
(40,3,8,5,'Karakum'),
(41,3,9,1,'Naki'),
(42,3,9,2,'Toma'),
(43,3,9,3,'Mav'),
(44,3,9,4,'Dona'),
(45,3,9,5,'Kuna'),
(46,3,10,1,'Xiao Chun'),
(47,3,10,2,'Mao'),
(48,3,10,3,'Huan'),
(49,3,10,4,'Tien Qin'),
(50,3,10,5,'Wei Shin'),
(51,3,11,1,'Iron'),
(52,3,11,2,'Nickel'),
(53,3,11,3,'Copper'),
(54,3,11,4,'Silver'),
(55,3,11,5,'Zinc')
SET IDENTITY_INSERT Monsters OFF
CREATE TABLE Gamers
(
GamerKey int IDENTITY(1,1) NOT NULL PRIMARY KEY,
FirstName varchar(50) NOT NULL,
LastName varchar(50) NOT NULL,
CreateDate smalldatetime NOT NULL DEFAULT (GETDATE()),
Active bit NOT NULL DEFAULT (1)
)
SET IDENTITY_INSERT Gamers ON
INSERT Gamers (GamerKey, FirstName, LastName) VALUES
(1, 'Wade', 'Watts'),
(2, 'James', 'Halliday'),
(3, 'Samantha', 'Cook')
SET IDENTITY_INSERT Gamers OFF
CREATE TABLE GamerMonsters
(
GamerMonstersKey int IDENTITY(1,1) NOT NULL PRIMARY KEY,
MonsterKey int NOT NULL,
GamerKey int NOT NULL,
CurrentLevel int NOT NULL,
CurrentGrade int NOT NULL,
Active bit DEFAULT(1) NOT NULL
)
INSERT GamerMonsters (MonsterKey, GamerKey, CurrentLevel, CurrentGrade) VALUES
(1, 1, 33, 5),
(12, 1, 40, 6),
(21, 1, 30, 4),
(8, 2, 35, 5),
(16, 2, 30, 4),
(31, 2, 25, 3),
(31, 2, 35, 5),
(43, 2, 40, 6),
(34, 2, 1, 4),
(33, 2, 1, 4),
(33, 2, 1, 4),
(34, 2, 1, 3),
(10, 3, 40, 6),
(18, 3, 35, 5),
(24, 3, 40, 6),
(54, 3, 25, 3),
(55, 3, 25, 3),
(53, 3, 1, 3),
(55, 3, 1, 3),
(50, 3, 1, 3)
ALTER TABLE Monsters
ADD CONSTRAINT fk_MonsterGrade
FOREIGN KEY (StartingGradeKey) REFERENCES Grades(GradeKey)
ALTER TABLE Monsters
ADD CONSTRAINT fk_MonsterClass
FOREIGN KEY (MonsterClassKey) REFERENCES MonsterClasses(MonsterClassKey)
ALTER TABLE Monsters
ADD CONSTRAINT fk_MonsterElement
FOREIGN KEY (MonsterElementKey) REFERENCES MonsterElements(MonsterElementKey)
ALTER TABLE GamerMonsters
ADD CONSTRAINT fk_GamerMonsterKey
FOREIGN KEY (MonsterKey) REFERENCES Monsters(MonsterKey)
ALTER TABLE GamerMonsters
ADD CONSTRAINT fk_GamerKey
FOREIGN KEY (GamerKey) REFERENCES Gamers(GamerKey)
ALTER TABLE GamerMonsters
ADD CONSTRAINT fk_GamerCurrentGrade
FOREIGN KEY (CurrentGrade) REFERENCES Grades(GradeKey)
/* Simple Random Number Generation */
DECLARE @myRandomNumber decimal (4,4)
SET @myRandomNumber = RAND()
PRINT @myRandomNumber
/* Random Number between two values 10 and 20 with balanced distribution */
DECLARE @Counter int = 1
DECLARE @Lowest int = 10
DECLARE @Highest int = 20
WHILE (@Counter < 1000)
BEGIN
DECLARE @myRandomNumberBetween int
SET @myRandomNumberBetween = @Lowest + FLOOR((@Highest - @Lowest + 1) * RAND())
PRINT @myRandomNumberBetween
SET @Counter = @Counter + 1
END
Explanation / Answer
/*Write a query that brings back a list of monsters,
including the following information – MonsterKey, starting grade, monster class, monster element,
monster name, and a count of how many of that monster are currently in circulation. */
/*Hi Please observer question clearly. U missed below sentense*/
/*count of how many of that monster are currently in circulation.*/
/*The above sentense states, you need to count particular monster in circulation*/
/*GamerMonsters table 33 (monster key) occures 2 time. You need to count occurense of each monster in GamerMonsters table */
SELECT M.MonsterKey, G.GradeName AS Starting_Grade, MC.MonsterClass, ME.MonsterElement, M.MonsterName,COUNT(gm.MonsterKey)
FROM Monsters M LEFT JOIN MonsterClasses MC
ON M.MonsterClassKey = MC.MonsterClassKey
LEFT JOIN MonsterElements ME
ON M.MonsterElementKey = ME.MonsterElementKey
LEFT JOIN Grades G
ON M.StartingGradeKey = G.GradeKey
LEFT JOIN GamerMonsters gm
ON M.MonsterKey=gm.MonsterKey
GROUP BY M.MonsterName
ORDER BY M.MonsterName
/*Please comments incase of problem*/
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.