CODEWITHSUNDEEP
sqldatabase
mph85
mph85

Reputation: 1356

SQL - Create Table & Query - Correct?

** Just using regular SQL, would this solution be correct?**

Design a database for the following scenario: You need to track all of the dogs at an agility competition. They have different events for each competition and each dog has a score from each event. Also, write the SQL queries to return all the dogs in the competition, the events that each dog are enrolled in, and the score that they received in that event.

CREATE TABLE Dogs (
  Name STRING,
  EventId INT
)
CREATE TABLE Events (
  Name STRING,
  Id INT
)
CREATE TABLE Scores (
  Score INT,
  EventId INT
)
SELECT name
FROM Dogs 
INNER JOIN Events On Events.Id = Dogs.EventId
INNER JOIN Scores On Events.Id = Scores.Event.Id

Upvotes: 0

Views: 120

Answers (3)

Abd Alrahman
Abd Alrahman

Reputation: 63

You need to connect the two tables 'Dogs' and 'Events' using table 'Scores', then you can get the desired information. It would be something like the following:

   CREATE TABLE Dogs (
    Name varchar(45),
    Id INT
    );

CREATE TABLE Competition (
  Name varchar(45),
  Id INT
);

CREATE TABLE Events (
  Name varchar(45),
  Id INT,
  CompetitionId INT
);

CREATE TABLE Scores (
  DogId INT,
  Score INT,
  EventId INT
);

SELECT Dogs.name DogName,Competition.Name CompetitionName,Event.name EventName,Scores.Score
FROM Dogs
    INNER JOIN Scores On Dogs.Id = Scores.DogId
    INNER JOIN Events On Events.Id = Scores.EventId
    INNER JOIN Competition on Events.CompetitionId=Competition.Id;

Edited: I added the table Competition, made the required changes on the select statement and corrected the datatypes and some syntax issues. This query will get the names of the dogs with the competitions and the events they enrolled in and the score they got at that event.

Upvotes: 1

Aris Mist
Aris Mist

Reputation: 89

Try your tables to be like

CREATE TABLE Dogs ( Name STRING, DogId INT ) CREATE TABLE Events ( Name STRING, EventId INT ) CREATE TABLE Scores ( Score INT, ScoreId INT ) CREATE TABLE dog_Event ( dogid INT, ScoreId INT ) CREATE TABLE Score_Event( EventId INT, ScoreId INT )

Do inner join to all of them!

Upvotes: 2

Michael Muryn
Michael Muryn

Reputation: 91

I like singular table name. I also like to put an id in all table as a common pattern. Do like you want for this.

I am unsure for some points like if we need to track different competitions (I assumed yes since they say "for each competition" assuming there is more than one competition to track) and if the set of events can be shared among competition (I only modelized event per competition, instead of having an event list that is re-used across all competition, which would also be interesting if there are common events between competition, like "faster runner").

Here is a take for the modelization based on these assumptions:

dog
---
id
name

competition
-----------
id
date
name

competition_event
-----------------
id
competition_id
name

/* note: we could make a different event (or event_type) table 
and just use event_id (or event_type_id) in the competition_event table 
if we want to share events among competition... 
would be useful to do stat on stuff like "faster runner" across all competitions */

dog_competition_event_score
---------------------------
id
competition_event_id
dog_id
score

Then for the query it should looks something like this:

SELECT *
FROM dog d
JOIN dog_competition_event_score dces ON dces.dog_id = d.id
JOIN competition_event ce ON ce.id = dces.competition_event_id
JOIN competition c ON c.id = ce.competition_id;

Upvotes: 1

Related Questions