Sqltempp

Wiki-Quelltext dieser Seite.

<language>SQL</language>
== SQL Playground

<code id="622b6f3707a78">
DROP TABLE IF EXISTS player;
CREATE table player(p_id int NOT NULL PRIMARY KEY, username text NOT NULL, level int NOT NULL, rank text NOT NULL);
INSERT INTO player(p_id,username,level,rank) values (1,"hilseduck", 101, "silver");
INSERT INTO player(p_id,username,level,rank) values (2,"chiefspy007", 34, "iron");
INSERT INTO player(p_id,username,level,rank) values (3,"randalf", 507, "gold");
INSERT INTO player(p_id,username,level,rank) values (4,"gerard_alaste", 55, "bronze");
INSERT INTO player(p_id,username,level,rank) values (5,"96TRS", 98, "silver");
INSERT INTO player(p_id,username,level,rank) values (6,"team_siko", 87, "iron");
INSERT INTO player(p_id,username,level,rank) values (7,"skell.reaver", 201, "gold");
INSERT INTO player(p_id,username,level,rank) values (8,"n0ts", 51, "bronze");
INSERT INTO player(p_id,username,level,rank) values (9,"eremois", 156, "silver");
INSERT INTO player(p_id,username,level,rank) values (10,"wildwestsr", 177, "bronze");
INSERT INTO player(p_id,username,level,rank) values (11,"teemo_reemo", 156, "silver");
INSERT INTO player(p_id,username,level,rank) values (12,"trest", 78, "silver");
INSERT INTO player(p_id,username,level,rank) values (13,"livinrat", 126, "silver");
INSERT INTO player(p_id,username,level,rank) values (14,"smurfedya", 48, "gold");
INSERT INTO player(p_id,username,level,rank) values (15,"fish_553", 156, "diamond");
INSERT INTO player(p_id,username,level,rank) values (16,"idom_ror", 48, "silver");
INSERT INTO player(p_id,username,level,rank) values (17,"wizard", 78, "diamond");
INSERT INTO player(p_id,username,level,rank) values (18,"kled_bane", 106, "silver");
INSERT INTO player(p_id,username,level,rank) values (19,"doublelift", 707, "platin");
INSERT INTO player(p_id,username,level,rank) values (20,"faker", 852, "platin");

DROP TABLE IF EXISTS game;
CREATE table game(g_id int NOT NULL PRIMARY KEY, end_time datetime NOT NULL, start_time datetime NOT NULL);
INSERT INTO game(g_id,end_time,start_time) values (1,'2022-03-18 10:27:32','2022-03-18 10:05:01');
INSERT INTO game(g_id,end_time,start_time) values (2,'2022-03-19 04:43:12','2022-03-19 04:12:53');
INSERT INTO game(g_id,end_time,start_time) values (4,'2022-03-20 15:12:59','2022-03-20 14:22:37');

DROP TABLE IF EXISTS rune;
CREATE table rune(r_id int NOT NULL PRIMARY KEY, path_name text NOT NULL);
INSERT INTO rune(r_id,path_name) values (1,"Precision");
INSERT INTO rune(r_id,path_name) values (2,"Domination");
INSERT INTO rune(r_id,path_name) values (3,"Sorcery");
INSERT INTO rune(r_id,path_name) values (4,"Resolve");
INSERT INTO rune(r_id,path_name) values (5,"Inspiration");

DROP TABLE IF EXISTS champion;
CREATE table champion(c_id int NOT NULL PRIMARY KEY, c_name text NOT NULL);
INSERT INTO champion(c_id,c_name) values (1,"Talon");
INSERT INTO champion(c_id,c_name) values (2,"Akshan");
INSERT INTO champion(c_id,c_name) values (3,"Jhin");
INSERT INTO champion(c_id,c_name) values (4,"Vi");
INSERT INTO champion(c_id,c_name) values (5,"Ahri");
INSERT INTO champion(c_id,c_name) values (6,"Malphite");
INSERT INTO champion(c_id,c_name) values (7,"Viego");
INSERT INTO champion(c_id,c_name) values (8,"Aatrox");
INSERT INTO champion(c_id,c_name) values (9,"Leona");
INSERT INTO champion(c_id,c_name) values (10,"Jinx");
INSERT INTO champion(c_id,c_name) values (11,"Rengar");
INSERT INTO champion(c_id,c_name) values (12,"Kled");
INSERT INTO champion(c_id,c_name) values (13,"Fizz");
INSERT INTO champion(c_id,c_name) values (14,"Twitch");

DROP TABLE IF EXISTS item;
CREATE table item(i_id int NOT NULL PRIMARY KEY, i_name text NOT NULL, damage int NOT NULL, attack_speed int NOT NULL, armor int NOT NULL);
INSERT INTO item(i_id, i_name, damage, attack_speed,armor) values (1,"Thornnail",5,0,20);
INSERT INTO item(i_id, i_name, damage, attack_speed,armor) values (2,"Rapid Firecannon",35,100,0);
INSERT INTO item(i_id, i_name, damage, attack_speed,armor) values (3,"Bloodthirster",135,0,0);
INSERT INTO item(i_id, i_name, damage, attack_speed,armor) values (4,"Steel-Plated Boots",0,0,15);
INSERT INTO item(i_id, i_name, damage, attack_speed,armor) values (5,"Kraken Slayer",55,85,0);
INSERT INTO item(i_id, i_name, damage, attack_speed,armor) values (6,"Everfrost",100,0,5); 
INSERT INTO item(i_id, i_name, damage, attack_speed,armor) values (7,"Galeforce",75,75,0);
INSERT INTO item(i_id, i_name, damage, attack_speed,armor) values (8,"Gargoyle Stoneplate",0,0,45);

DROP TABLE IF EXISTS game_player;
CREATE table game_player(
  g_id int NOT NULL, 
  p_id INT NOT NULL,
  c_id int NOT NULL,
  i_id int NOT NULL,
  r_id int NOT NULL,
  win_or_team int NOT NULL,
  role TEXT NOT NULL,
  FOREIGN KEY (g_id) REFERENCES game(g_id),
  FOREIGN KEY (p_id) REFERENCES player(p_id),
  FOREIGN KEY (c_id) REFERENCES champion(c_id),
  FOREIGN KEY (i_id) REFERENCES item(i_id),
  FOREIGN KEY (r_id) REFERENCES rune(r_id),
  PRIMARY KEY (g_id, p_id),
  -- CHECKS
  CHECK (win_or_team <= 1),
  CHECK (win_or_team >= 0)
  -- win_or_team darf nur 0 (für Team Verlierer) oder 1 (für Team Gewinner) sein
);

INSERT INTO game_player(g_id,p_id,c_id,i_id,r_id,win_or_team,role) values (1,1,2,5,1,1,"mid");  
INSERT INTO game_player(g_id,p_id,c_id,i_id,r_id,win_or_team,role) values (1,20,5,6,2,0,"mid");
INSERT INTO game_player(g_id,p_id,c_id,i_id,r_id,win_or_team,role) values (1,7,3,7,1,1,"adc");  
INSERT INTO game_player(g_id,p_id,c_id,i_id,r_id,win_or_team,role) values (1,3,14,2,1,0,"adc");
INSERT INTO game_player(g_id,p_id,c_id,i_id,r_id,win_or_team,role) values (1,4,9,6,5,1,"support");  
INSERT INTO game_player(g_id,p_id,c_id,i_id,r_id,win_or_team,role) values (1,9,6,8,5,0,"support");
INSERT INTO game_player(g_id,p_id,c_id,i_id,r_id,win_or_team,role) values (1,17,4,4,2,1,"jungle");  
INSERT INTO game_player(g_id,p_id,c_id,i_id,r_id,win_or_team,role) values (1,13,11,3,2,0,"jungle");
INSERT INTO game_player(g_id,p_id,c_id,i_id,r_id,win_or_team,role) values (1,2,8,1,4,1,"top");  
INSERT INTO game_player(g_id,p_id,c_id,i_id,r_id,win_or_team,role) values (1,8,12,8,3,0,"top");
</code>
<run id="622b6f370a39f">
-- Schwierigkeitsstufe 1 : welchen Rang (engl. rank) hat der Spieler mit dem username "chiefspy007"?
SELECT player.username, player.rank  FROM player WHERE username = "chiefspy007";

-- Schwierigkeitsstufe 2: Wie lange dauerte das Match mit der Match-ID 1 in Minuten?
select ROUND((JULIANDAY(end_time) - JULIANDAY(start_time)) * 1440,2) FROM game  WHERE g_id = 1;

-- Schwierigkeitsstufe 2: Welches Match dauerte am längsten, und wie lange?
select g_id, MAX(ROUND((JULIANDAY(end_time) - JULIANDAY(start_time)) * 1440,2)) FROM game;

-- Schwierigkeitsstufe 3: Überblick über das Game mit der ID 1. 
SELECT game_player.g_id,game_player.role,game.end_time,game.start_time,player.username,player.level,player.rank,champion.c_name,item.i_name,rune.path_name,win_or_team FROM game_player,
game,player,champion,item,rune WHERE game_player.g_id = game.g_id AND game_player.p_id = player.p_id AND game_player.c_id = champion.c_id AND game_player.i_id = item.i_id 
AND game_player.r_id = rune.r_id AND game_player.g_id = 1 
ORDER BY win_or_team DESC, CASE 
WHEN role='top' THEN 1 
WHEN role='jungle' THEN 2 
WHEN role='mid' THEN 3 
WHEN role='adc' THEN 4
WHEN role='support' THEN 5
ELSE role
END;

-- Schwierigkeitsstufe 3 Welchen Champion und welches Item spielte der Spieler mit dem höchsten Level zuletzt in einem Spiel, und in welcher Role?
SELECT MAX(game_player.g_id), game_player.p_id, game_player.role,game.end_time,game.start_time,player.username,MAX(player.level),player.rank,champion.c_name,item.i_name FROM game_player,game,player,champion,item WHERE game_player.g_id = game.g_id AND game_player.p_id = player.p_id 
AND game_player.c_id = champion.c_id AND game_player.i_id = item.i_id;</run>