CODEWITHSUNDEEP
sqldatabase
mph85
mph85

Reputation: 1356

SQL - Creating Database and Querying it

I need to answer the following instructions and am wondering if I'm on the right path as far as answering it.

Instructions

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 DATABASE Competition (
  Dogs CHAR(30) NOT NULL,
  Score INT NOT NULL,
  Event CHAR(30) NOT NULL,
)

SELECT * 
FROM Competition

This seems to be a little too simple, but would this satisfy all the instructions that's given?

EDIT

Would this solution work?

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: 1

Views: 89

Answers (2)

SQLRaptor
SQLRaptor

Reputation: 681

You have your entities mixed up in the syntax. The database is the logical container, within it you create your tables. For example, if you are using T-SQL:

CREATE DATABASE DogCompetition;
USE DogCompetition;
CREATE TABLE Dogs 
(Dog VARCHAR(30) PRIMARY KEY);
CREATE TABLE Events 
(Event VARCHAR(30) PRIMARY KEY);
CREATE TABLE DogEventScores 
(Dog VARCHAR(30) REFERENCES Dogs(Dog), 
Event VARCHAR(30) REFERENCES Events (Event),
Score INT NOT NULL,
PRIMARY KEY (Dog, Event)
);
INSERT INTO Dogs (Dog) VALUES ('Charlie');
INSERT INTO Events (Event) VALUES ('Running');
INSERT INTO DogEventScores (Dog, Event, Score) VALUES ('Charlie', 'Running', 8);
SELECT * FROM DogEventScores;

HTH

Upvotes: 2

Hien Nguyen
Hien Nguyen

Reputation: 18975

You should design your table like below.

Dog and Competition has many to many relationship and Event is the mediator table.

Dog (DogId, DogName)

Event(DogId, CompetitionId, EventName, Score)

Competition(CompetitionId, Name)

Queries to return all the dogs in the competition // return all dog for competition id equal 1

select * from Dog inner join Event on Dog.DogId = Event.DogId where Event.CompetitionId = 1

The events that each dog are enrolled in, and the score that they received in that event.

   select Event.EventName, sum(Score) from Dog inner join Event on Dog.DogId = Event.DogId 
where Dog.DogName = 'Misa' group by Event.EventName

Upvotes: 1

Related Questions