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.

2 responses to “DBA – SQL update – Exercise 1”

  1. […] Let’s answer the first exercise you have been working on (DBA SQL Update exercise). […]

  2. […] the exercise 1 on sql update, today we introduce a new problem to train yourself on a select […]

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Want more information?

Related links will be displayed within articles for you to pick up another good spot to get more details about software development, deployment & monitoring.

Stay tuned by following us on Youtube.

%d bloggers like this: