Üks tabel

CREATE TABLE ounad(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  sort VARCHAR(30),
  diameeter FLOAT
);

INSERT INTO ounad VALUES (DEFAULT, 'Suislepp', 7);
INSERT INTO ounad VALUES (DEFAULT, 'Suislepp', 6.5);
INSERT INTO ounad VALUES (DEFAULT, 'Suislepp', 7.3);
INSERT INTO ounad VALUES (DEFAULT, 'Valge klaar', 5);
INSERT INTO ounad VALUES (DEFAULT, 'Valge klaar', 5.5);

SELECT * FROM ounad;

+----+-------------+-----------+
| id | sort        | diameeter |
+----+-------------+-----------+
|  1 | Suislepp    |         7 |
|  2 | Suislepp    |       6.5 |
|  3 | Suislepp    |       7.3 |
|  4 | Valge klaar |         5 |
|  5 | Valge klaar |       5.5 |
+----+-------------+-----------+

SELECT * FROM ounad ORDER BY diameeter;
+----+-------------+-----------+
| id | sort        | diameeter |
+----+-------------+-----------+
|  4 | Valge klaar |         5 |
|  5 | Valge klaar |       5.5 |
|  2 | Suislepp    |       6.5 |
|  1 | Suislepp    |         7 |
|  3 | Suislepp    |       7.3 |
+----+-------------+-----------+

SELECT * FROM ounad WHERE sort='Suislepp';

+----+----------+-----------+
| id | sort     | diameeter |
+----+----------+-----------+
|  1 | Suislepp |         7 |
|  2 | Suislepp |       6.5 |
|  3 | Suislepp |       7.3 |
+----+----------+-----------+

SELECT * FROM ounad WHERE sort='Suislepp' ORDER BY diameeter DESC;

+----+----------+-----------+
| id | sort     | diameeter |
+----+----------+-----------+
|  3 | Suislepp |       7.3 |
|  1 | Suislepp |         7 |
|  2 | Suislepp |       6.5 |
+----+----------+-----------+

SELECT sort, diameeter/2 AS raadius FROM ounad;
+-------------+--------------------+
| sort        | raadius            |
+-------------+--------------------+
| Suislepp    |                3.5 |
| Suislepp    |               3.25 |
| Suislepp    | 3.6500000953674316 |
| Valge klaar |                2.5 |
| Valge klaar |               2.75 |
+-------------+--------------------+

SELECT sort, ROUND(diameeter/2, 1) AS raadius FROM ounad;
+-------------+---------+
| sort        | raadius |
+-------------+---------+
| Suislepp    |     3.5 |
| Suislepp    |     3.2 |
| Suislepp    |     3.7 |
| Valge klaar |     2.5 |
| Valge klaar |     2.8 |
+-------------+---------+

SELECT * FROM ounad;
+----+-------------+-----------+
| id | sort        | diameeter |
+----+-------------+-----------+
|  1 | Suislepp    |         7 |
|  2 | Suislepp    |       6.5 |
|  3 | Suislepp    |       7.3 |
|  4 | Valge klaar |         5 |
|  5 | Valge klaar |       5.5 |
+----+-------------+-----------+

UPDATE ounad SET sort='Sügisjoonik' WHERE id=1;

mysql> SELECT * FROM ounad;
+----+--------------+-----------+
| id | sort         | diameeter |
+----+--------------+-----------+
|  1 | Sügisjoonik  |         7 |
|  2 | Suislepp     |       6.5 |
|  3 | Suislepp     |       7.3 |
|  4 | Valge klaar  |         5 |
|  5 | Valge klaar  |       5.5 |
+----+--------------+-----------+

DELETE FROM ounad WHERE id=4;

+----+--------------+-----------+
| id | sort         | diameeter |
+----+--------------+-----------+
|  1 | Sügisjoonik  |         7 |
|  2 | Suislepp     |       6.5 |
|  3 | Suislepp     |       7.3 |
|  5 | Valge klaar  |       5.5 |
+----+--------------+-----------+

ALTER TABLE ounad ADD mass FLOAT;
+----+--------------+-----------+------+
| id | sort         | diameeter | mass |
+----+--------------+-----------+------+
|  1 | Sügisjoonik  |         7 | NULL |
|  2 | Suislepp     |       6.5 | NULL |
|  3 | Suislepp     |       7.3 | NULL |
|  5 | Valge klaar  |       5.5 | NULL |
+----+--------------+-----------+------+

UPDATE ounad SET mass=(4/3)*3.14*POWER(diameeter/2, 3);
+----+--------------+-----------+---------+
| id | sort         | diameeter | mass    |
+----+--------------+-----------+---------+
|  1 | Sügisjoonik  |         7 | 179.503 |
|  2 | Suislepp     |       6.5 |  143.72 |
|  3 | Suislepp     |       7.3 | 203.586 |
|  5 | Valge klaar  |       5.5 | 87.0696 |
+----+--------------+-----------+---------+

UPDATE ounad SET mass=mass*0.95 WHERE sort='Valge klaar';

+----+--------------+-----------+---------+
| id | sort         | diameeter | mass    |
+----+--------------+-----------+---------+
|  1 | Sügisjoonik  |         7 | 179.503 |
|  2 | Suislepp     |       6.5 |  143.72 |
|  3 | Suislepp     |       7.3 | 203.586 |
|  5 | Valge klaar  |       5.5 | 82.7161 |
+----+--------------+-----------+---------+

ALTER TABLE ounad CHANGE mass massgrammides FLOAT;

+----+--------------+-----------+---------------+
| id | sort         | diameeter | massgrammides |
+----+--------------+-----------+---------------+
|  1 | Sügisjoonik  |         7 |       179.503 |
|  2 | Suislepp     |       6.5 |        143.72 |
|  3 | Suislepp     |       7.3 |       203.586 |
|  5 | Valge klaar  |       5.5 |       82.7161 |
+----+--------------+-----------+---------------+

SELECT * FROM ounad ORDER BY massgrammides DESC LIMIT 1;

+----+----------+-----------+---------------+
| id | sort     | diameeter | massgrammides |
+----+----------+-----------+---------------+
|  3 | Suislepp |       7.3 |       203.586 |
+----+----------+-----------+---------------+

SELECT MAX(massgrammides) FROM ounad;

+--------------------+
| MAX(massgrammides) |
+--------------------+
| 203.58558654785156 |
+--------------------+

-- proovi ka funktsioone MIN, AVG, COUNT

ALTER TABLE ounad DROP massgrammides;
DROP TABLE ounad;

_______

Looge tabel "porgandid", iga porgandi kohta raadius ja pikkus, lisage kümmekond väärtust
Väljasta porgandid pikkuse järgi järjestatuna
Lisa tulp "sihvakus" pikkuse ja raadiuse suhtega
Väljasta porgandid sihvakuse järjekorras
Väljasta sihvakuse järjekorras porgandid, mille raadius on vähemasti 2 cm
Kustuta porgandid, mille raadius on väiksem kui 1 cm

Leia porgandite pikkuste summa
Leia porgandite ruumalade summa



Kodust/sülearvutist sisenemine

putty lin2.tlu.ee

ÕIS-i kasutajanimi ja parool

ssh juku@greeny.cs.tlu.ee
  kas tahad ühenduda - yes
  greeny parool
  
mysql -uif16 -pifikad16 if16_juku