Database Schema and Queries for Hockey League

Posted on Jan 3, 2025 in Computers

Table Structures

Equipo Table

— Table structure for table Equipo

DROP TABLE IF EXISTS Equipo;

CREATE TABLE Equipo (

team_id int(11) NOT NULL,

teamName varchar(45) DEFAULT NULL,

PRIMARY KEY (team_id)

Estadísticas_Equipo Table

— Table structure for table Estadísticas_Equipo

DROP TABLE IF EXISTS Estadísticas_Equipo;

CREATE TABLE Estadísticas_Equipo (

game_id int(11) NOT NULL,

team_id int(11) NOT NULL,

tgoals int(11) DEFAULT NULL,

tshots int(11) DEFAULT NULL,

thits int(11) DEFAULT NULL,

PRIMARY KEY (game_id,team_id)

Estadísticas_Jugador Table

— Table structure for table Estadísticas_Jugador

DROP TABLE IF EXISTS Estadísticas_Jugador;

CREATE TABLE Estadísticas_Jugador (

game_id int(11) NOT NULL,

player_id int(11) NOT NULL,

assists int(11) DEFAULT NULL,

goals int(11) DEFAULT NULL,

shots int(11) DEFAULT NULL,

PRIMARY KEY (game_id,player_id)

Jugada Table

— Table structure for table Jugada

DROP TABLE IF EXISTS Jugada;

CREATE TABLE Jugada (

play_id varchar(25) NOT NULL,

team_id_for int(11) DEFAULT NULL,

team_id_against int(11) DEFAULT NULL,

event varchar(25) DEFAULT NULL,

game_id int(11) DEFAULT NULL,

PRIMARY KEY (play_id)

Jugador Table

— Table structure for table Jugador

DROP TABLE IF EXISTS Jugador;

CREATE TABLE Jugador (

player_id int(11) NOT NULL,

firstName varchar(45) DEFAULT NULL,

lastName varchar(45) DEFAULT NULL,

nationality varchar(45) DEFAULT NULL,

PRIMARY KEY (player_id)

Partido Table

— Table structure for table Partido

DROP TABLE IF EXISTS Partido;

SET character_set_client = utf8mb4 ;

CREATE TABLE Partido (

game_id int(11) NOT NULL,

date_time datetime DEFAULT NULL,

home_team_id int(11) DEFAULT NULL,

away_team_id int(11) DEFAULT NULL,

home_goals int(11) DEFAULT NULL,

away_goals int(11) DEFAULT NULL,

PRIMARY KEY (game_id)

Temporada Table

— Table structure for table Temporada

DROP TABLE IF EXISTS Temporada;

SET character_set_client = utf8mb4 ;

CREATE TABLE Temporada (

date_time datetime NOT NULL,

season int(11) DEFAULT NULL,

PRIMARY KEY (date_time)

primeratabla Table

— Table structure for table primeratabla

DROP TABLE IF EXISTS primeratabla;

CREATE TABLE primeratabla (

game_id int(11) DEFAULT NULL,

season int(11) DEFAULT NULL,

date_time datetime DEFAULT NULL,

home_team_id int(11) DEFAULT NULL,

away_team_id int(11) DEFAULT NULL,

home_goals int(11) DEFAULT NULL,

away_goals int(11) DEFAULT NULL,

player_id int(11) DEFAULT NULL,

firstName varchar(25) DEFAULT NULL,

lastName varchar(25) DEFAULT NULL,

nationality varchar(25) DEFAULT NULL,

assists int(11) DEFAULT NULL,

goals int(11) DEFAULT NULL,

shots int(11) DEFAULT NULL,

play_id varchar(25) DEFAULT NULL,

team_id_for int(11) DEFAULT NULL,

team_id_against int(11) DEFAULT NULL,

event varchar(25) DEFAULT NULL

segundatabla Table

— Table structure for table segundatabla

DROP TABLE IF EXISTS segundatabla;

CREATE TABLE segundatabla (

game_id int(11) DEFAULT NULL,

season int(11) DEFAULT NULL,

date_time datetime DEFAULT NULL,

home_team_id int(11) DEFAULT NULL,

away_team_id int(11) DEFAULT NULL,

home_goals int(11) DEFAULT NULL,

away_goals int(11) DEFAULT NULL,

team_id int(11) DEFAULT NULL,

teamName varchar(25) DEFAULT NULL,

tgoals int(11) DEFAULT NULL,

tshots int(11) DEFAULT NULL,

thits int(11) DEFAULT NULL

alter table primeratabla add primary key (player_id, play_id);HL

alter table segundatabla add primary key (game_id, team_id); AS

SQL Queries

Queries on segundatabla

A. SELECT count(game_id ) FROM segundatabla WHERE YEAR(date_time)=2013 AND teamName = “Blackhawks”;

Resultado 2 filas.

B. SELECT teamName FROM segundatabla WHERE YEAR(date_time) = 2013 AND MONTH(date_time) = 01 AND home_team_id = team_id;

Resultado 19 filas

C. SELECT team_id , teamName, sum(tgoals), sum(tshots), sum(thits) FROM segundatabla WHERE YEAR(date_time) = 2013 AND team_id = home_team_id group by (teamName);

Resultado 18 filas.

D. SELECT s.team_id, s.teamName, p.date_time, p.event, p.play_id FROM primeratabla p inner join segundatabla s WHERE team_id_for = team_id AND p.firstName = “Adam” AND p.lastName =”McQuaid” ORDER BY p.date_time;

Resultado 272 filas.

Inserts on primeratabla and segundatabla

4a. INSERT INTO primeratabla (firstName, lastName, player_id,nationality) values (“Samuel”, “Smithson”, 5554441, “CAN”);

b. INSERT INTO primeratabla (game_id, home_team_id, away_team_id, home_goals, away_goals) values (2012030999, (SELECT team_id FROM segundatabla WHERE teamName = “Red Wings” LIMIT 1), (SELECT team_id FROM segundatabla WHERE teamName = “Penguins” LIMIT 1), 3, 2);

c. INSERT INTO segundatabla (teamName, team_id) values (“Lasters”, 99);

Updates on primeratabla and segundatabla

5- UPDATE primeratabla set home_goals = 5 , away_goals = 3 where game_id = 2012020053; 0 filas

UPDATE segundatabla set home_goals = 5 , away_goals = 3 where game_id = 2012020053;

6- UPDATE segundatabla set teamName = “Wild Side” WHERE MONTH(date_time) > 02 AND YEAR(date_time) >= 2013 AND teamName = “Wild”;

11- INSERT INTO Equipo SELECT distinct team_id, teamName FROM segundatabla; etc

Queries on Partido, Equipo, Estadísticas_Equipo, Jugador and Jugada

12- Apartado 3

A. SELECT count(game_id) FROM Partido WHERE home_team_id=(SELECT team_id FROM Equipo WHERE teamName=”Blackhawks”) OR away_team_id=(SELECT team_id FROM Equipo WHERE teamName=”Blackhawks”);

B. SELECT e.teamName FROM Partido, Equipo e WHERE YEAR(date_time) = 2013 AND MONTH(date_time) = 01 AND home_team_id = e.team_id;

C. SELECT distinct Equipo.team_id, teamName , sum(tgoals), sum(thits), sum(tshots) FROM Partido JOIN Equipo on Partido.home_team_id=Equipo.team_id LEFT JOIN Estadísticas_Equipo ON Estadísticas_Equipo.team_id=Partido.home_team_id WHERE YEAR(Partido.date_time)=2013 group by team_id;

D. SELECT e.team_id, e.teamName, p.date_time, j.event, j.play_id FROM Equipo e, Partido p, Jugada j WHERE j.game_id=p.game_id and j.team_id_for=e.team_id and e.team_id <> 0 and j.game_id= (SELECT game_id FROM Estadísticas_Jugador pj WHERE pj.player_id= (SELECT player_id FROM Jugador j WHERE j.firstName= ‘Adam’ and j.lastName=’McQuaid’)) ORDER BY p.date_time;

Inserts on Jugador, Partido and Equipo

Apartado 4

A. INSERT INTO Jugador (firstName, lastName, player_id,nationality) values (“Samuel”, “Smithson”, 5554441, “CAN”);

B. INSERT INTO Partido (game_id, home_team_id, away_team_id, home_goals, away_goals) values (2012030999, (SELECT team_id FROM Equipo WHERE teamName = “Red Wings” LIMIT 1), (SELECT team_id FROM Equipo WHERE teamName = “Penguins” LIMIT 1), 3, 2);

C. INSERT INTO Equipo (teamName, team_id) values (“Lasters”, 99);

Updates on Partido and Equipo

Apartado 5

update Partido set home_goals = 5 , away_goals = 3 where game_id = 2012020053;

Apartado 6

update Equipo set teamName = “Wild Side” WHERE MONTH(date_time) > 02 AND YEAR(date_time) >= 2013 AND teamName = “Wild”;

Questions

a. ¿Cuántos partidos ha jugado en el año 2013 el equipo de nombre Blackhawks?

b. Por cada partido registrado en el mes de enero de 2013, mostrar el nombre de los equipos locales.

c. Por cada equipo, mostrar su identificador, nombre, y estadísticas (tgoals, tshots y thits) en los partidos jugados en 2013 como equipos locales.

d. Obtener, para cada partido que haya jugado el o los equipo(s) en los que haya jugado el jugador Adam McQuaid, el identificador del equipo, el nombre del equipo, el año, mes y día del partido y el identificador de cada jugada realizada junto con su descripción (event) para cada una de las jugadas que haya realizado el equipo en cada partido (event) ordenadas por la fecha del partido