SQL Exercise Script

Dog Walking

Dog Walking SQL Script

A database for a Dog Walking company having information about dog breeds, owners and their dogs and employees. The database also has information about walks performed by employees. Each walk occurs in a certain date, for a certain amount of time (in minutes) and is performed by an employee bringing along one dog only.

CREATE TABLE breed (
  b_name VARCHAR PRIMARY KEY -- breed name
);

CREATE TABLE owner (
  o_id SERIAL PRIMARY KEY, -- owner id
  o_name VARCHAR NOT NULL, -- owner name
  o_city TEXT NOT NULL     -- owner city
);

CREATE TABLE dog (
  d_id SERIAL PRIMARY KEY,               -- dog id
  d_name VARCHAR NOT NULL,               -- dog name
  b_name VARCHAR REFERENCES breed,       -- dog breed (NULL if a mutt)
  o_id INTEGER NOT NULL REFERENCES owner -- owner of this dog
);

CREATE TABLE employee (
  e_id SERIAL PRIMARY KEY, -- employee id
  e_name VARCHAR NOT NULL  -- employee name
);

CREATE TABLE walk (
  w_id SERIAL PRIMARY KEY,                   -- id of the walk
  w_date DATE NOT NULL,                      -- the date of this walk
  w_time INTEGER NOT NULL,                   -- time the dog walked in minutes
  d_id INTEGER NOT NULL REFERENCES dog,      -- the dog that was walked
  e_id INTEGER NOT NULL REFERENCES employee  -- the employee that walked the dog
);

INSERT INTO breed VALUES ('Labrador Retriever');
INSERT INTO breed VALUES ('Bulldog');
INSERT INTO breed VALUES ('Poodle');
INSERT INTO breed VALUES ('Beagle');
INSERT INTO breed VALUES ('Pug');
INSERT INTO breed VALUES ('Border Collie');

INSERT INTO owner VALUES (DEFAULT, 'John', 'Porto');
INSERT INTO owner VALUES (DEFAULT, 'Mary', 'Matosinhos');
INSERT INTO owner VALUES (DEFAULT, 'Carl', 'Gaia');
INSERT INTO owner VALUES (DEFAULT, 'Louise', 'Matosinhos');
INSERT INTO owner VALUES (DEFAULT, 'Margareth', 'Gaia');
INSERT INTO owner VALUES (DEFAULT, 'Giselle', 'Porto');

INSERT INTO dog VALUES (DEFAULT, 'Iris', 'Border Collie', 1);
INSERT INTO dog VALUES (DEFAULT, 'Daisy', NULL, 1);
INSERT INTO dog VALUES (DEFAULT, 'Max', NULL, 1);
INSERT INTO dog VALUES (DEFAULT, 'Lola', 'Poodle', 2);
INSERT INTO dog VALUES (DEFAULT, 'Sadie', 'Poodle', 2);
INSERT INTO dog VALUES (DEFAULT, 'Bear', 'Bulldog', 3);
INSERT INTO dog VALUES (DEFAULT, 'Oliver', 'Labrador Retriever', 4);
INSERT INTO dog VALUES (DEFAULT, 'Luna', 'Beagle', 4);
INSERT INTO dog VALUES (DEFAULT, 'Rocky', 'Bulldog', 4);
INSERT INTO dog VALUES (DEFAULT, 'Tucker', 'Beagle', 5);
INSERT INTO dog VALUES (DEFAULT, 'Frank', 'Poodle', 6);

INSERT INTO employee VALUES (DEFAULT, 'Eric');
INSERT INTO employee VALUES (DEFAULT, 'Evan');
INSERT INTO employee VALUES (DEFAULT, 'Edith');
INSERT INTO employee VALUES (DEFAULT, 'Eloise');

INSERT INTO walk VALUES (DEFAULT, '2018-01-07', 20, 1,  1);
INSERT INTO walk VALUES (DEFAULT, '2018-01-07', 30, 4,  1);
INSERT INTO walk VALUES (DEFAULT, '2018-01-07', 15, 10, 1);
INSERT INTO walk VALUES (DEFAULT, '2018-01-07', 40, 8,  2);
INSERT INTO walk VALUES (DEFAULT, '2018-01-07', 25, 2,  3);

INSERT INTO walk VALUES (DEFAULT, '2018-01-08', 20, 7,  2);
INSERT INTO walk VALUES (DEFAULT, '2018-01-08', 5,  6,  2);
INSERT INTO walk VALUES (DEFAULT, '2018-01-08', 15, 1,  3);

INSERT INTO walk VALUES (DEFAULT, '2018-01-09', 5,  8,  1);
INSERT INTO walk VALUES (DEFAULT, '2018-01-09', 30, 4,  1);
INSERT INTO walk VALUES (DEFAULT, '2018-01-09', 5,  2,  2);
INSERT INTO walk VALUES (DEFAULT, '2018-01-09', 15, 5,  2);

INSERT INTO walk VALUES (DEFAULT, '2018-01-10', 30, 2,  2);
INSERT INTO walk VALUES (DEFAULT, '2018-01-10', 25, 6,  3);

INSERT INTO walk VALUES (DEFAULT, '2018-01-10', 40, 3,  1);
INSERT INTO walk VALUES (DEFAULT, '2018-01-10', 15, 10, 3);