SQL - Create Table & Query - Correct?

Question

** 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

In Scores you only have EventId and Score, so you can only have one score per event. I think many dogs will attent an event, and each dog will have it's individual score. — HoneyBadger
– HoneyBadger, Commented Mar 20, 2019 at 17:48
It's the dog that got a score not the event and also a dog participates in many events and I assume an event is performed by many dogs. Probably best to have a table for the competition as well. — Joakim Danielson
– Joakim Danielson, Commented Mar 20, 2019 at 17:48
Also, I'd use LEFT JOIN as a dog doesn't have to get a score (didn't finish, for example). — HoneyBadger
– HoneyBadger, Commented Mar 20, 2019 at 17:50
It feels like this question is to broad or a borderline code review and I think you need to give your design another go and maybe come back with a more specific question because too much is missing right now. — Joakim Danielson
– Joakim Danielson, Commented Mar 20, 2019 at 17:54
There is no datatype "string" in standard SQL. So with regards to "using regular SQL" the answer is: no, this is not correct. — user330315
– user330315, Commented Mar 20, 2019 at 18:45

Aris Mist · Accepted Answer · 2019-03-20 18:39:17Z

2

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!

answered Mar 20, 2019 at 18:39

Aris Mist

894 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Abd Alrahman · Accepted Answer · 2019-03-20 22:26:22Z

1

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.

edited Mar 20, 2019 at 22:26

answered Mar 20, 2019 at 17:57

Abd Alrahman

637 bronze badges

2 Comments

mph85 Over a year ago

appreciate the help. so you're saying that this answer would not be enough to answer the question? I'd have to add another table called competition?

Abd Alrahman Over a year ago

Yes, but I edited the answer to include the table and the needed changes to the query.

Michael Muryn · Accepted Answer · 2019-03-20 18:03:34Z

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;

Collectives™ on Stack Overflow

SQL - Create Table & Query - Correct?

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related