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.
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.
[…] Let’s answer the first exercise you have been working on (DBA SQL Update exercise). […]
[…] the exercise 1 on sql update, today we introduce a new problem to train yourself on a select […]