First of all, I am new at MySQL, and I've got some problems with altering tables with query's.
Secondly, sorry for bad English, unfortunately, it's not my native language.
So yeah, I have some questions, and I will be very thankful if you will be able to help me.
I have 2 tables: Screenshot
Foreign key is 'manufact' from table Registred
So, I have few Query tasks I wanted to do, but can't figure out how to do them.
Here goes first task I did, I just want you guys to check, If I did it right.
DELETE FROM Registered Order by January Limit 1;
ALTER TABLE Registered Add Column alltotal Int;
Set alltotal = Select SUM(February + January) From Registered;
Let's break this down problem by problem. The first one is:
Delete all information about car manufacturer, that had smallest amount of cars registered in January.
The first thing we need to do is write a subquery that determines which manufacturer that is. We can do so by selecting the id of the row in
registracija with the minimum value for January. We can do that using
ORDER BY, which you caught on to:
SELECT vieglas FROM registracija ORDER BY january LIMIT 1;
Now that we have that id, we can delete from that table using the
DELETE FROM registracija WHERE vieglas = (SELECT vieglas FROM (SELECT * FROM registracija) t ORDER BY january LIMIT 1);
For information on why I included the
SELECT * FROM registracija, see this answer.
To see an SQL Fiddle of that in action, check this: http://sqlfiddle.com/#!9/c81d5/1
For the second part:
Calculate all cars registred in January and February and save it in additional field. (Should be displayed as Int, without floating point.)
We can use the addition operator along with an update command to put this total inside the
Total column like this:
UPDATE registracija SET total = (january + february);
For the SQL Fiddle of the update, see this: http://sqlfiddle.com/#!9/f5b28/1