DBA – SQL update – Exercise 1

This it the first DBA (DataBase Administrator) exercise of a long serie, in my opinion to be a better programmer, you need to practice regularly and the same affirmation stands for a DBA so I decided to introduce a new training exercise each week.

Before I implement a fully working interface to make you benefit of the training I will simply follow this schema :

1. New problem (Question & context)
2. the solution of the problem will be posted the next day
3. Another post with a new question will appear the same day

The solution will be detailed with some analysis and I will be looking forward to hearing from specialists in order to improve it overtime.

Context

You have 3 tables in your Database. I assume you have access to a SQL Database and you can issue queries. Otherwise, run a docker image of an SQL server (ie: https://hub.docker.com/_/mariadb).

We will rely on temporary tables for minimum impact. I will use Postgresql as my SQL server, but you may use your favorite engine.

CREATE TEMPORARY TABLE store (
    id serial PRIMARY KEY,
    name varchar(50) NOT NULL
);

CREATE TEMPORARY TABLE category (
    id serial PRIMARY KEY,
    name varchar(50) NOT NULL,
    store_id integer not null references store(id)
);

CREATE TEMPORARY TABLE book (
    id serial PRIMARY KEY,
    name varchar(50) NOT NULL,
    price_coefficient float not null default 1,
    category_id integer NOT NULL references category(id)
);

INSERT INTO store (name) VALUES ('Store 1'), ('Store 2'), ('Store 3'), ('Store 4'), ('Store 5');
INSERT INTO category (name, store_id) VALUES ('Fiction', 1), ('Fantasy', 1), ('Romantic', 2), ('Manga', 2);
INSERT INTO book (name, category_id) VALUES ('Ants', 1), ('Final Fantasy', 2), ('One Piece', 4), ('Millenium', 1);

Just run the above scripts in the order provided. You will have a few books belonging to a category which are referenced in a store.

The book has a coefficient which will alter the price (this is just fictitious).

Question

Build a query to update all price coefficient to 1.2 for the books which are in the store named : Store 2.