sql.txt (8176B)
1 CREATE TABLE Publishers ( 2 PublisherID INT primary key, 3 Name VARCHAR(50) NOT NULL, 4 Revenue DECIMAL(10,2) NOT NULL 5 ); 6 CREATE TABLE Games ( 7 Title VARCHAR(50), 8 Price DECIMAL(10, 2) NOT NULL, 9 ReleaseYear SMALLINT, 10 Constraint CompositeKey Primary Key (Title, ReleaseYear) 11 ); 12 CREATE TABLE Publishers_Games ( 13 PublisherID INT, 14 Title VARCHAR(50) NOT NULL, 15 ReleaseYear SMALLINT NOT NULL, 16 FOREIGN KEY (Title, ReleaseYear) REFERENCES Games (Title, ReleaseYear), 17 FOREIGN KEY (PublisherID) REFERENCES Publishers (PublisherID) 18 ); 19 CREATE TABLE Genres ( 20 GenreID INT PRIMARY KEY , 21 GenreName VARCHAR(50) NOT NULL 22 ); 23 CREATE TABLE Genres_Games ( 24 Title VARCHAR(50) NOT NULL, 25 ReleaseYear SMALLINT NOT NULL, 26 GenreID INT NOT NULL, 27 FOREIGN KEY (Title, ReleaseYear) REFERENCES Games (Title, ReleaseYear), 28 FOREIGN KEY (GenreID) REFERENCES Genres(GenreID) 29 ); 30 CREATE TABLE Accounts ( 31 AccountID INT PRIMARY KEY, 32 Email VARCHAR(50) NOT NULL, 33 AccPassword VARCHAR(255) NOT NULL, 34 Bio TEXT(255) 35 ); 36 37 38 CREATE TABLE PlayerAccounts ( 39 AccountID INT, 40 PlayerID INT PRIMARY KEY, 41 FOREIGN KEY (AccountID) REFERENCES Accounts (AccountID) 42 ); 43 CREATE TABLE Items ( 44 ItemID INT PRIMARY KEY , 45 AccountID INT, 46 PlayerID INT, 47 FOREIGN KEY (AccountID) REFERENCES Accounts (AccountID), 48 FOREIGN KEY (PlayerID) REFERENCES PlayerAccounts(PlayerID) 49 ); 50 CREATE TABLE Players_Games ( 51 Title VARCHAR(50), 52 ReleaseYear SMALLINT, 53 PlayerID INT, 54 FOREIGN KEY (Title, ReleaseYear) REFERENCES Games (Title, ReleaseYear), 55 FOREIGN KEY (PlayerID) REFERENCES PlayerAccounts(PlayerID) 56 ); 57 CREATE TABLE BusinessAccounts ( 58 AccountID INT, 59 BusinessID VARCHAR(50) PRIMARY KEY, 60 PublisherID INT, 61 FOREIGN KEY (AccountID) REFERENCES Accounts (AccountID), 62 FOREIGN KEY (PublisherID) REFERENCES Publishers (PublisherID) 63 ); 64 -- Publishers 65 -- Normally, we would use auto-increment for the ID, but since we are not starting from zero on our tables I just decided to manually insert them. 66 INSERT INTO Publishers values(1234, "Cheeseball Games", 230.4); 67 INSERT INTO Publishers values(1235, "UrMother Industries", 173.6); 68 INSERT INTO Publishers values(1236, "Masterful Fishing Games", 426.9); 69 INSERT INTO Publishers values(2138, "Mothership Farms", 309); 70 -- Games 71 INSERT INTO Games values("Rulers of the Under World", 14.99, 2019); 72 INSERT INTO Games values("Rulers of the Under World", 100.99, 2015); 73 INSERT INTO Games values("Papa's Love Acedamy", 4.99, 2015); 74 INSERT INTO Games values("A to Z: Annals of Zombies", 9.99, 2020); 75 INSERT INTO Games values("Saga of the Titans", 24.99, 2020); 76 INSERT INTO Games values ("Street Fighter II", 69.99, 1991); 77 INSERT INTO Games values ("Goat Simulator", 9.99, 2019); 78 -- Publishers_Games 79 INSERT INTO Publishers_Games values (1234, "Rulers of the Under World", 2019); 80 INSERT INTO Publishers_Games values (1234, "Saga of the Titans", 2020); 81 INSERT INTO Publishers_Games values (1235, "Papa's Love Acedamy", 2015); 82 INSERT INTO Publishers_Games values (2138, "A to Z: Annals of Zombies", 2020); 83 INSERT INTO Publishers_Games values (2138, "Street Fighter II", 1991); 84 85 -- Genres 86 INSERT INTO Genres values(1, "Action"); 87 INSERT INTO Genres values(2, "Fighting"); 88 INSERT INTO Genres values(3, "Platform"); 89 INSERT INTO Genres values(4, "Shooter"); 90 INSERT INTO Genres values(5, "Survival"); 91 INSERT INTO Genres values(6, "Battle Royale"); 92 INSERT INTO Genres values(7, "Stealth"); 93 INSERT INTO Genres values(8, "Horror"); 94 INSERT INTO Genres values(9, "Massively Multiplayer Online"); 95 INSERT INTO Genres values(10, "Role-playing"); 96 INSERT INTO Genres values(11, "Sci-Fi"); 97 INSERT INTO Genres values(12, "Simulation"); 98 INSERT INTO Genres values(13, "Strategy"); 99 INSERT INTO Genres values(14, "Romance"); 100 -- Genres_Games 101 INSERT INTO Genres_Games values("Rulers of the Under World", 2019, 1); 102 INSERT INTO Genres_Games values("Goat Simulator", 2019, 12); 103 INSERT INTO Genres_Games values("Papa's Love Acedamy", 2015, 14); 104 INSERT INTO Genres_Games values("Street Fighter II", 1991, 2); 105 INSERT INTO Genres_Games values("Saga of the Titans", 2020, 2); 106 -- Accounts 107 INSERT INTO Accounts values(202, "yeahmama1@hotmail.com", "!!@ve6K1ds", "I am a mother of six"); 108 INSERT INTO Accounts values(204, "hotcheeto33@yahoo.com", "password", "lorem ipsum"); 109 INSERT INTO Accounts values(220, "bussinessemail46", "g!veme@nswer", "This is a business"); 110 INSERT INTO Accounts values(300, "icecreamlover36@gmail.com", "freedom123", "I like ice cream"); 111 112 113 114 -- BusinessAccounts 115 INSERT INTO BusinessAccounts values(202, "Mrs.Worthington", 1234); 116 INSERT INTO BusinessAccounts values(204, "Brandon", 1235); 117 INSERT INTO BusinessAccounts values(220, "Elizabeth", 1236); 118 INSERT INTO BusinessAccounts values(300, "Kun", 2138); 119 -- PlayerAccounts 120 INSERT INTO PlayerAccounts values(202, 701); 121 INSERT INTO PlayerAccounts values(204, 702); 122 INSERT INTO PlayerAccounts values(220, 703); 123 INSERT INTO PlayerAccounts values(300, 704); 124 125 -- Items 126 INSERT INTO Items values(500, 202, 701); 127 INSERT INTO Items values(501, 204, 702); 128 INSERT INTO Items values(502, 220, 703); 129 INSERT INTO Items values(503, 300, 704); 130 INSERT INTO Items values(901, 220, 703); 131 INSERT INTO Items values(1001, 220, 703); 132 INSERT INTO Items values(5555, 220, 703); 133 INSERT INTO Items values(2333, 220, 703); 134 135 -- Players_Games 136 INSERT INTO Players_Games values("Papa's Love Acedamy", 2015, 701); 137 INSERT INTO Players_Games values("Saga of the Titans", 2020, 704); 138 INSERT INTO Players_Games values("Street Fighter II", 1991, 704); 139 INSERT INTO Players_Games values("Rulers of the Under World", 2019, 702); 140 INSERT INTO Players_Games values("A to Z: Annals of Zombies", 2020, 702); 141 INSERT INTO Players_Games values("Papa's Love Acedamy", 2015, 702); 142 143 144 145 -- What is the bio of the account that bought games produced by the publisher (Cheeseball Games) and owns the item with the ItemID of 503? 146 -- Answer: (“I like ice cream”) 147 SELECT Bio 148 FROM Accounts NATURAL JOIN PlayerAccounts NATURAL JOIN Items NATURAL JOIN Players_Games NATURAL JOIN Publishers_Games NATURAL JOIN Publishers 149 WHERE Name = "Cheeseball Games" AND ItemID = 503; 150 151 152 -- What is the email of the account that has an item itemID of 501? 153 -- Answer: ("hotcheeto33@yahoo.com") 154 SELECT Email 155 FROM Accounts NATURAL JOIN PlayerAccounts NATURAL JOIN Items 156 WHERE ItemID = 501; 157 158 159 -- What are the names of the publishers that are in the fighting genre? 160 -- Answer: ("Cheeseball Games", "Mothership Farm") 161 SELECT Name 162 FROM Publishers NATURAL JOIN Publishers_Games NATURAL JOIN Genres_Games NATURAL JOIN Genres 163 WHERE GenreName = "Fighting"; 164 165 166 -- What is Mrs. Worthington's password? 167 -- Answer: (“!l@ve6K1ds”) 168 SELECT AccPassword 169 FROM Accounts NATURAL JOIN BusinessAccounts 170 WHERE BusinessID = "Mrs. Worthington"; 171 172 173 -- What fighting games titles does Kun own? 174 -- Answer: (“Rulers From the Under Ground”) 175 SELECT Title 176 FROM Players_Games NATURAL JOIN BusinessAccounts 177 WHERE businessID = "Kun" AND Title IN (SELECT Title 178 FROM Genres_Games NATURAL JOIN Genres 179 WHERE GenreName = "Action"); 180 181 -- How many games did Brandon pay at least $15 or less for? 182 -- Answer: (3) 183 184 select count(Title) 185 from Players_Games natural join PlayerAccounts natural join Games 186 where AccountID = (select AccountID 187 from BusinessAccounts 188 where BusinessID = "Brandon") and Price <= 15; 189 190 -- What are the ItemIDs Elizabeth owns? 191 -- Answer: (502, 901, 1001, 5555, 2333) 192 select ItemID 193 from Items natural join BusinessAccounts 194 where BusinessID = "Elizabeth"; 195 196 -- What is the title of the game in the romance genre? 197 -- Answer: (“Papa's Love Acedamy”) 198 select Title 199 from Genres_Games natural join 200 Genres where GenreName = "Romance"; 201 -- What is the name of the publisher that has a game called “Papa's Love Acedamy”? 202 -- Answer: (“UrMother Industries”) 203 select Name 204 from Publishers natural join Publishers_Games 205 where title = "Papa's Love Acedamy"; 206 207 -- What is the genre name of Goat Simulator? 208 -- Answer: (“Simulation”) 209 Select GenreName 210 from Genres natural join Genres_Games 211 where title = "Goat Simulator";