SQLize Online / PHPize Online  /  SQLtest Online

-- Alguns nomes foram alterados por necessidade de implementação -- Ex: User,Events pra User_cm e Events_cm. CREATE TABLE User_cm (username VARCHAR(100) NOT NULL PRIMARY KEY, email VARCHAR(50) NOT NULL, password_u VARCHAR(100) NOT NULL); CREATE TABLE Promotions (pname VARCHAR(100) NOT NULL PRIMARY KEY, localization VARCHAR(100) NOT NULL UNIQUE, avg_rating decimal(10,2) CHECK (avg_rating >=0.0 AND avg_rating <=10) , abbreviation VARCHAR(100)); CREATE TABLE Wrestlers (gimmick_name VARCHAR(100) NOT NULL PRIMARY KEY, real_name VARCHAR(100), avg_rating decimal(10,2) CHECK (avg_rating >=0.0 AND avg_rating <=10.00) , birthday VARCHAR(10), birthplace VARCHAR(100), age smallint CHECK (age >=0), weight VARCHAR(20), height VARCHAR(25)); CREATE TABLE Events_cm (ename VARCHAR(100) NOT NULL PRIMARY KEY, type VARCHAR(100) NOT NULL, avg_rating decimal(10,2) CHECK (avg_rating >=0.0 AND avg_rating <=10.00) , date_e VARCHAR(10) NOT NULL UNIQUE, arena VARCHAR(50), attendance int CHECK (attendance >=0), localization VARCHAR(100)); CREATE TABLE Matches (fixture VARCHAR(100) NOT NULL PRIMARY KEY, type VARCHAR(50) NOT NULL, avg_rating decimal(10,2) CHECK (avg_rating >=0.0 AND avg_rating <=10.00), date_e VARCHAR(10) NOT NULL UNIQUE, ename VARCHAR(100) NOT NULL UNIQUE, FOREIGN KEY (ename) REFERENCES Events_cm(ename), FOREIGN KEY (date_e) REFERENCES Events_cm(date_e)); CREATE TABLE Titles (tname VARCHAR(100) NOT NULL PRIMARY KEY, avg_rating decimal(10,2) CHECK (avg_rating >=0.0 AND avg_rating <=10.00), status_t VARCHAR(20) NOT NULL); CREATE TABLE Tournaments (toname VARCHAR(100) NOT NULL PRIMARY KEY, avg_rating decimal(10,2) CHECK (avg_rating >=0.0 AND avg_rating <=10.00), type VARCHAR(50) NOT NULL, timeframe VARCHAR(30) NOT NULL); CREATE TABLE Awards (name VARCHAR(100) NOT NULL PRIMARY KEY, year_a int NOT NULL CHECK(year_a >= 0), category VARCHAR(50) NOT NULL); CREATE TABLE Videogames (title VARCHAR(100) NOT NULL PRIMARY KEY, developer VARCHAR(100) NOT NULL UNIQUE , avg_rating decimal(10,2) CHECK (avg_rating >=0.0 AND avg_rating <=10.00), date_v VARCHAR(10) NOT NULL UNIQUE, systems VARCHAR(100) NOT NULL); CREATE TABLE Ratings (rating_value smallint CHECK (rating_value <=10 AND rating_value >=0), username VARCHAR(100) NOT NULL, date_r VARCHAR(10) NOT NULL, hour_r VARCHAR(5) NOT NULL, commentary VARCHAR(3000) CHECK ( LENGTH(commentary) > 100 AND LENGTH(commentary) <300), FOREIGN KEY (username) REFERENCES User_cm(username), pname VARCHAR(100) UNIQUE, gimmick_name VARCHAR(100), ename VARCHAR(100), fixture VARCHAR(100), tname VARCHAR(100), toname VARCHAR(100), title VARCHAR(100), FOREIGN KEY (pname) REFERENCES Promotions(pname), FOREIGN KEY (gimmick_name) REFERENCES Wrestlers(gimmick_name), FOREIGN KEY (ename) REFERENCES Events_cm(ename), FOREIGN KEY (fixture) REFERENCES Matches(fixture), FOREIGN KEY (tname) REFERENCES Titles(tname), FOREIGN KEY (toname) REFERENCES Tournaments(toname), FOREIGN KEY (title) REFERENCES Videogames(title) ); -- inserção de dados na tabela User_cm INSERT INTO User_cm VALUES ('WalterWhite','walter.white@aol.com','ilovemyfamily'); INSERT INTO User_cm VALUES ('NoelGallagher', 'gallaghernoel1@gmail.com','wonderwall'); INSERT INTO User_cm VALUES ('Cellbit','rafael.lange@gmail.com','umineko'); -- inserção de dados na tabela Promotions INSERT INTO Promotions VALUES ('World Wrestling Entertainment','Stamford, Connecticut, USA ',7.84, 'WWE'); INSERT INTO Promotions VALUES ('All Elite Wrestling','Jacksonville, Florida, USA',8.79, 'AEW'); INSERT INTO Promotions VALUES ('New Japan Pro Wrestling','Tokyo,Japan',9.37, 'NJPW'); -- inserção de dados na tabela Wrestlers INSERT INTO Wrestlers VALUES ('Kota Ibushi','Ibushi Kota',9.42,'21.05.1982','Kagoshima,Japan',40,'205 lbs (93 kg)','5´ 11´´ (181 cm)'); INSERT INTO Wrestlers VALUES ('Hiroshi Tanahashi','Tanahashi Hiroshi',9.68,'13.11.1976','Ogaki,Gifu,Japan',46,'222 lbs (101 kg)','5´ 11´´ (181 cm)'); INSERT INTO Wrestlers VALUES ('AJ Styles','Alan Neal Jones',9.49,'02.06.1977','Jacksonville,North Carolina,USA',45,'218 lbs (99 kg)','5´ 11´´ (180 cm)'); INSERT INTO Wrestlers VALUES ('Jesse Pinkman','Jesse Pinkman',NULL,NULL,'United States'); -- inserção de dados na tabela Events_cm INSERT INTO Events_cm VALUES ('WCW Bash at the Beach 1995','Pay Per View',3.49,'16.07.1995','The Beach',9500,'Huntington Beach,California,USA'); INSERT INTO Events_cm VALUES ('WWE Money in the Bank 2011','Pay Per View',9.54,'17.07.2011','Allstate Arena',14815,'Chicago,Illinois,USA'); INSERT INTO Events_cm VALUES ('NJPW Wrestle Kingdom 17','TV Show',8.52,'04.01.2023','Tokyo Dome',26085,'Tokyo,Japan'); -- inserção de dados na tabela Matches INSERT INTO Matches VALUES ('Kazuchika Okada vs Jay White','Singles',8.26,'04.01.2023','NJPW Wrestle Kingdom 17'); INSERT INTO Matches VALUES ('John Cena vs CM Punk','Singles',9.64,'17.07.2011','WWE Money in the Bank 2011'); INSERT INTO Matches VALUES ('The Renegade vs Paul Orndoff','Singles',1.94,'16.07.1995','WCW Bash at the Beach 1995'); -- inserção de dados na tabela Titles INSERT INTO Titles VALUES ('WWE World Heavyweight Championship',8.94,'active'); INSERT INTO Titles VALUES ('AEW World Championship',9.35,'active'); INSERT INTO Titles VALUES ('PWG World Championship',9.14,'active'); -- inserção de dados na tabela Tournaments INSERT INTO Tournaments VALUES ('PWG Battle of Los Angeles 2015',9.76,'KO-System','28.08.2015 - 30.08.2015'); INSERT INTO Tournaments VALUES ('wXw 16 Carat Gold Tournament 2007',9.83,'KO-System','04.05.2007 - 06.05.2007'); INSERT INTO Tournaments VALUES ('Cruiserweight Classic',9.55,'KO-System','23.06.2016 - 14.09.2016'); -- inserção de dados na tabela Awards INSERT INTO Awards VALUES ('WWE Hall of Fame 2016',2016,'Hall of Fame'); INSERT INTO Awards VALUES ('Best Enemy Award',2020,'Ice Ribbon Year End Awards'); INSERT INTO Awards VALUES ('PWI 500',1992,'Pro Wrestling Illustrated'); -- inserção de dados na tabela Ratings INSERT INTO Ratings VALUES (NULL,'WalterWhite','07.02.2023','21:43',NULL,'World Wrestling Entertainment'); INSERT INTO Ratings VALUES (9,'Cellbit','07.02.2023','21:45',NULL,NULL,'Kota Ibushi'); INSERT INTO Ratings VALUES (2,'NoelGallagher','07.02.2023','21:46',NULL,NULL,NULL,'WCW Bash at the Beach 1995'); INSERT INTO Ratings VALUES (5,'WalterWhite','07.02.2023','21:52',NULL,'All Elite Wrestling');