Asukohad

* Koosta tabel asukohad tulpadega
  id, toon, x, y
  
Tabeli kustutamiseks
DROP TABLE asukohad;

CREATE TABLE asukohad(
   id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   toon VARCHAR(20),
   x INT,
   y INT
);

INSERT INTO asukohad (toon, x, y) VALUES ('roheline', 3, 5);
INSERT INTO asukohad (toon, x, y) VALUES ('sinine', 3, 6);
INSERT INTO asukohad (toon, x, y) VALUES ('roheline', 6, 5);
INSERT INTO asukohad (toon, x, y) VALUES ('sinine', 4, 8);
INSERT INTO asukohad (toon, x, y) VALUES ('kollane', 6, 2);
INSERT INTO asukohad (toon, x, y) VALUES ('roheline', 7, 3);
INSERT INTO asukohad (toon, x, y) VALUES ('kollane', 6, 4);
INSERT INTO asukohad (toon, x, y) VALUES ('roheline', 7, 5);

SELECT * FROM asukohad WHERE toon='roheline';
SELECT * FROM asukohad WHERE x>3;
SELECT * FROM asukohad WHERE x>3 AND y<5;
SELECT * FROM asukohad ORDER BY x;
SELECT * FROM asukohad WHERE toon='roheline' ORDER BY x;

DELETE FROM asukohad WHERE id=4;
INSERT INTO asukohad (toon, x, y) VALUES ('sinine', 4, 8);
UPDATE asukohad SET x=4 WHERE id=2;
UPDATE asukohad SET x=x+1, y=0 WHERE id IN (1, 2, 5);

-- Nuppudel, millel x on suurem kui 5 vähendage y-d kahe võrra.

SELECT toon, CONCAT('(', x,', ',y,')') as asukoht FROM asukohad;
SELECT SQRT(25);
Arvutage ja väljastage iga nupu kaugus nullpunktist

SELECT id, x*x FROM asukohad;
SELECT id, x, y, SQRT(x*x+y*y) FROM asukohad;
SELECT id, x, y, ROUND(SQRT(x*x+y*y), 2) as kaugus FROM asukohad;

SELECT COUNT(*) FROM asukohad;
SELECT COUNT(*) FROM asukohad WHERE toon='roheline';
-- Loe kokku, mitme nupu asukoht on nullpunktist kaugemal kui 6 ühikut
SELECT COUNT(*) FROM asukohad WHERE SQRT(x*x+y*y)>6;
SELECT MAX(x) FROM asukohad;
SELECT MIN(x) FROM asukohad;
SELECT AVG(x) FROM asukohad;

ALTER TABLE asukohad ADD z INT DEFAULT 1;
ALTER TABLE asukohad CHANGE z korgus FLOAT;
UPDATE asukohad SET korgus=0.75 WHERE id=3;
ALTER TABLE asukohad DROP korgus;
mysql> SELECT * FROM asukohad;
+----+----------+------+------+
| id | toon     | x    | y    |
+----+----------+------+------+
|  1 | roheline |    4 |    0 |
|  2 | sinine   |    5 |    0 |
|  3 | roheline |    6 |    5 |
|  5 | kollane  |    7 |    0 |
|  6 | roheline |    7 |    3 |
|  7 | kollane  |    6 |    4 |
|  8 | roheline |    7 |    5 |
|  9 | sinine   |    4 |    8 |
+----+----------+------+------+
8 rows in set (0.00 sec)


jaagup@tlu.ee
56 221 789