This it the first DBA (DataBase Administrator) exercise of a long series, 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 every week.
Before I implement a fully working interface to make you benefit of the training I will simply follow this schema :
- New problem (Question & context)
- the solution of the problem will be posted the next week
- Another post with a new question will appear during the week
The solution will be detailed with some analysis and I will be looking forward to hearing from specialists in order to improve it overtime.
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).
Build a query to update all price coefficient to 1.2 for the books which are in the store named : Store 2.