commit b3caa1f757be1758d066c9b7e1c3bf37481bbf12
parent c79e86f984cc2437a4125ac086ea9a149877ac62
Author: Evan Alba <evanalba@protonmail.com>
Date: Tue, 5 Dec 2023 20:57:13 -0800
feat: Added SQL directory.
Diffstat:
| A | sql/sql.txt | | | 211 | +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ |
1 file changed, 211 insertions(+), 0 deletions(-)
diff --git a/sql/sql.txt b/sql/sql.txt
@@ -0,0 +1,211 @@
+CREATE TABLE Publishers (
+ PublisherID INT primary key,
+ Name VARCHAR(50) NOT NULL,
+ Revenue DECIMAL(10,2) NOT NULL
+);
+CREATE TABLE Games (
+ Title VARCHAR(50),
+ Price DECIMAL(10, 2) NOT NULL,
+ ReleaseYear SMALLINT,
+ Constraint CompositeKey Primary Key (Title, ReleaseYear)
+);
+CREATE TABLE Publishers_Games (
+ PublisherID INT,
+ Title VARCHAR(50) NOT NULL,
+ ReleaseYear SMALLINT NOT NULL,
+ FOREIGN KEY (Title, ReleaseYear) REFERENCES Games (Title, ReleaseYear),
+ FOREIGN KEY (PublisherID) REFERENCES Publishers (PublisherID)
+);
+CREATE TABLE Genres (
+ GenreID INT PRIMARY KEY ,
+ GenreName VARCHAR(50) NOT NULL
+);
+CREATE TABLE Genres_Games (
+ Title VARCHAR(50) NOT NULL,
+ ReleaseYear SMALLINT NOT NULL,
+GenreID INT NOT NULL,
+ FOREIGN KEY (Title, ReleaseYear) REFERENCES Games (Title, ReleaseYear),
+ FOREIGN KEY (GenreID) REFERENCES Genres(GenreID)
+);
+CREATE TABLE Accounts (
+ AccountID INT PRIMARY KEY,
+ Email VARCHAR(50) NOT NULL,
+ AccPassword VARCHAR(255) NOT NULL,
+ Bio TEXT(255)
+);
+
+
+CREATE TABLE PlayerAccounts (
+ AccountID INT,
+PlayerID INT PRIMARY KEY,
+ FOREIGN KEY (AccountID) REFERENCES Accounts (AccountID)
+);
+CREATE TABLE Items (
+ ItemID INT PRIMARY KEY ,
+ AccountID INT,
+ PlayerID INT,
+ FOREIGN KEY (AccountID) REFERENCES Accounts (AccountID),
+ FOREIGN KEY (PlayerID) REFERENCES PlayerAccounts(PlayerID)
+);
+CREATE TABLE Players_Games (
+ Title VARCHAR(50),
+ReleaseYear SMALLINT,
+PlayerID INT,
+ FOREIGN KEY (Title, ReleaseYear) REFERENCES Games (Title, ReleaseYear),
+ FOREIGN KEY (PlayerID) REFERENCES PlayerAccounts(PlayerID)
+);
+CREATE TABLE BusinessAccounts (
+ AccountID INT,
+BusinessID VARCHAR(50) PRIMARY KEY,
+ PublisherID INT,
+ FOREIGN KEY (AccountID) REFERENCES Accounts (AccountID),
+ FOREIGN KEY (PublisherID) REFERENCES Publishers (PublisherID)
+);
+-- Publishers
+-- 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.
+INSERT INTO Publishers values(1234, "Cheeseball Games", 230.4);
+INSERT INTO Publishers values(1235, "UrMother Industries", 173.6);
+INSERT INTO Publishers values(1236, "Masterful Fishing Games", 426.9);
+INSERT INTO Publishers values(2138, "Mothership Farms", 309);
+-- Games
+INSERT INTO Games values("Rulers of the Under World", 14.99, 2019);
+INSERT INTO Games values("Rulers of the Under World", 100.99, 2015);
+INSERT INTO Games values("Papa's Love Acedamy", 4.99, 2015);
+INSERT INTO Games values("A to Z: Annals of Zombies", 9.99, 2020);
+INSERT INTO Games values("Saga of the Titans", 24.99, 2020);
+INSERT INTO Games values ("Street Fighter II", 69.99, 1991);
+INSERT INTO Games values ("Goat Simulator", 9.99, 2019);
+-- Publishers_Games
+INSERT INTO Publishers_Games values (1234, "Rulers of the Under World", 2019);
+INSERT INTO Publishers_Games values (1234, "Saga of the Titans", 2020);
+INSERT INTO Publishers_Games values (1235, "Papa's Love Acedamy", 2015);
+INSERT INTO Publishers_Games values (2138, "A to Z: Annals of Zombies", 2020);
+INSERT INTO Publishers_Games values (2138, "Street Fighter II", 1991);
+
+-- Genres
+INSERT INTO Genres values(1, "Action");
+INSERT INTO Genres values(2, "Fighting");
+INSERT INTO Genres values(3, "Platform");
+INSERT INTO Genres values(4, "Shooter");
+INSERT INTO Genres values(5, "Survival");
+INSERT INTO Genres values(6, "Battle Royale");
+INSERT INTO Genres values(7, "Stealth");
+INSERT INTO Genres values(8, "Horror");
+INSERT INTO Genres values(9, "Massively Multiplayer Online");
+INSERT INTO Genres values(10, "Role-playing");
+INSERT INTO Genres values(11, "Sci-Fi");
+INSERT INTO Genres values(12, "Simulation");
+INSERT INTO Genres values(13, "Strategy");
+INSERT INTO Genres values(14, "Romance");
+-- Genres_Games
+INSERT INTO Genres_Games values("Rulers of the Under World", 2019, 1);
+INSERT INTO Genres_Games values("Goat Simulator", 2019, 12);
+INSERT INTO Genres_Games values("Papa's Love Acedamy", 2015, 14);
+INSERT INTO Genres_Games values("Street Fighter II", 1991, 2);
+INSERT INTO Genres_Games values("Saga of the Titans", 2020, 2);
+-- Accounts
+INSERT INTO Accounts values(202, "yeahmama1@hotmail.com", "!!@ve6K1ds", "I am a mother of six");
+INSERT INTO Accounts values(204, "hotcheeto33@yahoo.com", "password", "lorem ipsum");
+INSERT INTO Accounts values(220, "bussinessemail46", "g!veme@nswer", "This is a business");
+INSERT INTO Accounts values(300, "icecreamlover36@gmail.com", "freedom123", "I like ice cream");
+
+
+
+-- BusinessAccounts
+INSERT INTO BusinessAccounts values(202, "Mrs.Worthington", 1234);
+INSERT INTO BusinessAccounts values(204, "Brandon", 1235);
+INSERT INTO BusinessAccounts values(220, "Elizabeth", 1236);
+INSERT INTO BusinessAccounts values(300, "Kun", 2138);
+-- PlayerAccounts
+INSERT INTO PlayerAccounts values(202, 701);
+INSERT INTO PlayerAccounts values(204, 702);
+INSERT INTO PlayerAccounts values(220, 703);
+INSERT INTO PlayerAccounts values(300, 704);
+
+-- Items
+INSERT INTO Items values(500, 202, 701);
+INSERT INTO Items values(501, 204, 702);
+INSERT INTO Items values(502, 220, 703);
+INSERT INTO Items values(503, 300, 704);
+INSERT INTO Items values(901, 220, 703);
+INSERT INTO Items values(1001, 220, 703);
+INSERT INTO Items values(5555, 220, 703);
+INSERT INTO Items values(2333, 220, 703);
+
+-- Players_Games
+INSERT INTO Players_Games values("Papa's Love Acedamy", 2015, 701);
+INSERT INTO Players_Games values("Saga of the Titans", 2020, 704);
+INSERT INTO Players_Games values("Street Fighter II", 1991, 704);
+INSERT INTO Players_Games values("Rulers of the Under World", 2019, 702);
+INSERT INTO Players_Games values("A to Z: Annals of Zombies", 2020, 702);
+INSERT INTO Players_Games values("Papa's Love Acedamy", 2015, 702);
+
+
+
+-- 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?
+-- Answer: (“I like ice cream”)
+SELECT Bio
+FROM Accounts NATURAL JOIN PlayerAccounts NATURAL JOIN Items NATURAL JOIN Players_Games NATURAL JOIN Publishers_Games NATURAL JOIN Publishers
+WHERE Name = "Cheeseball Games" AND ItemID = 503;
+
+
+-- What is the email of the account that has an item itemID of 501?
+-- Answer: ("hotcheeto33@yahoo.com")
+SELECT Email
+FROM Accounts NATURAL JOIN PlayerAccounts NATURAL JOIN Items
+WHERE ItemID = 501;
+
+
+-- What are the names of the publishers that are in the fighting genre?
+-- Answer: ("Cheeseball Games", "Mothership Farm")
+SELECT Name
+FROM Publishers NATURAL JOIN Publishers_Games NATURAL JOIN Genres_Games NATURAL JOIN Genres
+WHERE GenreName = "Fighting";
+
+
+-- What is Mrs. Worthington's password?
+-- Answer: (“!l@ve6K1ds”)
+SELECT AccPassword
+FROM Accounts NATURAL JOIN BusinessAccounts
+WHERE BusinessID = "Mrs. Worthington";
+
+
+-- What fighting games titles does Kun own?
+-- Answer: (“Rulers From the Under Ground”)
+SELECT Title
+FROM Players_Games NATURAL JOIN BusinessAccounts
+WHERE businessID = "Kun" AND Title IN (SELECT Title
+ FROM Genres_Games NATURAL JOIN Genres
+ WHERE GenreName = "Action");
+
+-- How many games did Brandon pay at least $15 or less for?
+-- Answer: (3)
+
+select count(Title)
+from Players_Games natural join PlayerAccounts natural join Games
+where AccountID = (select AccountID
+from BusinessAccounts
+where BusinessID = "Brandon") and Price <= 15;
+
+-- What are the ItemIDs Elizabeth owns?
+-- Answer: (502, 901, 1001, 5555, 2333)
+select ItemID
+from Items natural join BusinessAccounts
+where BusinessID = "Elizabeth";
+
+-- What is the title of the game in the romance genre?
+-- Answer: (“Papa's Love Acedamy”)
+select Title
+from Genres_Games natural join
+Genres where GenreName = "Romance";
+-- What is the name of the publisher that has a game called “Papa's Love Acedamy”?
+-- Answer: (“UrMother Industries”)
+ select Name
+ from Publishers natural join Publishers_Games
+ where title = "Papa's Love Acedamy";
+
+-- What is the genre name of Goat Simulator?
+-- Answer: (“Simulation”)
+Select GenreName
+from Genres natural join Genres_Games
+where title = "Goat Simulator";
