CREATE TABLE t6ud(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  nimetus VARCHAR(30),
  kirjeldus TEXT
);


CREATE TABLE hobused (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  hobusenimi VARCHAR(30),
  synniaeg DATE,
  t6u_id INT,
  FOREIGN KEY(t6u_id) REFERENCES t6ud(id)
);

INSERT INTO t6ud (nimetus, kirjeldus) VALUES ('Eesti raskeveohobune', 'Väljasuremise äärel');
INSERT INTO t6ud (nimetus, kirjeldus) VALUES ('Belgia raskeveohobune', 'Suured ja tugevad');
INSERT INTO t6ud (nimetus, kirjeldus) VALUES ('Ðairi hobune', 'Kaks meetrit kõrge');

INSERT INTO hobused (hobusenimi, synniaeg, t6u_id) VALUES ('Urva', '2009-02-05', 2);
INSERT INTO hobused (hobusenimi, synniaeg, t6u_id) VALUES ('Uno', '2009-03-05', 2);
INSERT INTO hobused (hobusenimi, synniaeg, t6u_id) VALUES ('Sefiir', '2009-05-02', 3);
INSERT INTO hobused (hobusenimi, synniaeg, t6u_id) VALUES ('Alta', '2010-05-05', 3);
INSERT INTO hobused (hobusenimi, synniaeg, t6u_id) VALUES ('Asso', '2010-01-05', 1);

CREATE INDEX hobusenimed ON hobused(hobusenimi);


C:\Users\opetaja\Desktop\XAMPP\mysql\bin>mysql -uroot kursusebaas


SELECT * FROM hobused;

mysql> SELECT * FROM hobused;
+----+------------+------------+--------+
| id | hobusenimi | synniaeg   | t6u_id |
+----+------------+------------+--------+
|  1 | Urva       | 2009-02-05 |      2 |
|  2 | Uno        | 2009-03-05 |      2 |
|  3 | Sefiir     | 2009-05-02 |      3 |
|  4 | Alta       | 2010-05-05 |      3 |
|  5 | Asso       | 2010-01-05 |      1 |
+----+------------+------------+--------+
5 rows in set (0.00 sec)

SELECT hobusenimi, YEAR(synniaeg) as aasta FROM hobused;

mysql> SELECT hobusenimi, YEAR(synniaeg) as aasta FROM hobused;
+------------+-------+
| hobusenimi | aasta |
+------------+-------+
| Urva       |  2009 |
| Uno        |  2009 |
| Sefiir     |  2009 |
| Alta       |  2010 |
| Asso       |  2010 |
+------------+-------+
5 rows in set (0.00 sec)

mysql> SELECT hobusenimi, synniaeg FROM hobused ORDER BY hobusenimi;
+------------+------------+
| hobusenimi | synniaeg   |
+------------+------------+
| Alta       | 2010-05-05 |
| Asso       | 2010-01-05 |
| Sefiir     | 2009-05-02 |
| Uno        | 2009-03-05 |
| Urva       | 2009-02-05 |
+------------+------------+
5 rows in set (0.00 sec)

mysql> SELECT hobusenimi, synniaeg FROM hobused WHERE YEAR(synniaeg)=2009;
+------------+------------+
| hobusenimi | synniaeg   |
+------------+------------+
| Urva       | 2009-02-05 |
| Uno        | 2009-03-05 |
| Sefiir     | 2009-05-02 |
+------------+------------+
3 rows in set (0.01 sec)

DELETE FROM hobused WHERE id=4;

SELECT * FROM hobused;

mysql> DELETE FROM hobused WHERE id=4;
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM hobused;
+----+------------+------------+--------+
| id | hobusenimi | synniaeg   | t6u_id |
+----+------------+------------+--------+
|  1 | Urva       | 2009-02-05 |      2 |
|  2 | Uno        | 2009-03-05 |      2 |
|  3 | Sefiir     | 2009-05-02 |      3 |
|  5 | Asso       | 2010-01-05 |      1 |
+----+------------+------------+--------+
4 rows in set (0.00 sec)

INSERT INTO hobused (hobusenimi, synniaeg, t6u_id) VALUES ('Alma', '2011-05-02', 3);


mysql> INSERT INTO hobused (hobusenimi, synniaeg, t6u_id) VALUES ('Alma', '2011-05-02', 3);
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM hobused;
+----+------------+------------+--------+
| id | hobusenimi | synniaeg   | t6u_id |
+----+------------+------------+--------+
|  1 | Urva       | 2009-02-05 |      2 |
|  2 | Uno        | 2009-03-05 |      2 |
|  3 | Sefiir     | 2009-05-02 |      3 |
|  5 | Asso       | 2010-01-05 |      1 |
|  6 | Alma       | 2011-05-02 |      3 |
+----+------------+------------+--------+
5 rows in set (0.00 sec)


ALTER TABLE hobused ADD mass INT DEFAULT 500;

mysql> ALTER TABLE hobused ADD mass INT DEFAULT 500;
Query OK, 5 rows affected (0.30 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM hobused;
+----+------------+------------+--------+------+
| id | hobusenimi | synniaeg   | t6u_id | mass |
+----+------------+------------+--------+------+
|  1 | Urva       | 2009-02-05 |      2 |  500 |
|  2 | Uno        | 2009-03-05 |      2 |  500 |
|  3 | Sefiir     | 2009-05-02 |      3 |  500 |
|  5 | Asso       | 2010-01-05 |      1 |  500 |
|  6 | Alma       | 2011-05-02 |      3 |  500 |
+----+------------+------------+--------+------+
5 rows in set (0.00 sec)


ALTER TABLE hobused CHANGE mass mass_kilogrammides NUMERIC(6, 2);

mysql> ALTER TABLE hobused CHANGE mass mass_kilogrammides NUMERIC(6, 2);
Query OK, 5 rows affected (0.20 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM hobused;
+----+------------+------------+--------+--------------------+
| id | hobusenimi | synniaeg   | t6u_id | mass_kilogrammides |
+----+------------+------------+--------+--------------------+
|  1 | Urva       | 2009-02-05 |      2 |             500.00 |
|  2 | Uno        | 2009-03-05 |      2 |             500.00 |
|  3 | Sefiir     | 2009-05-02 |      3 |             500.00 |
|  5 | Asso       | 2010-01-05 |      1 |             500.00 |
|  6 | Alma       | 2011-05-02 |      3 |             500.00 |
+----+------------+------------+--------+--------------------+
5 rows in set (0.00 sec)

UPDATE hobused SET mass_kilogrammides=970.2 WHERE id=3;
mysql> UPDATE hobused SET mass_kilogrammides=970.2 WHERE id=3;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM hobused;
+----+------------+------------+--------+--------------------+
| id | hobusenimi | synniaeg   | t6u_id | mass_kilogrammides |
+----+------------+------------+--------+--------------------+
|  1 | Urva       | 2009-02-05 |      2 |             500.00 |
|  2 | Uno        | 2009-03-05 |      2 |             500.00 |
|  3 | Sefiir     | 2009-05-02 |      3 |             970.20 |
|  5 | Asso       | 2010-01-05 |      1 |             500.00 |
|  6 | Alma       | 2011-05-02 |      3 |             500.00 |
+----+------------+------------+--------+--------------------+
5 rows in set (0.00 sec)

UPDATE hobused SET mass_kilogrammides=350 WHERE id=1;
UPDATE hobused SET mass_kilogrammides=720 WHERE id=5;

mysql> SELECT * FROM hobused;
+----+------------+------------+--------+--------------------+
| id | hobusenimi | synniaeg   | t6u_id | mass_kilogrammides |
+----+------------+------------+--------+--------------------+
|  1 | Urva       | 2009-02-05 |      2 |             350.00 |
|  2 | Uno        | 2009-03-05 |      2 |             500.00 |
|  3 | Sefiir     | 2009-05-02 |      3 |             970.20 |
|  5 | Asso       | 2010-01-05 |      1 |             720.00 |
|  6 | Alma       | 2011-05-02 |      3 |             500.00 |
+----+------------+------------+--------+--------------------+
5 rows in set (0.00 sec)
mysql> SELECT MAX(mass_kilogrammides) FROM hobused;
+-------------------------+
| MAX(mass_kilogrammides) |
+-------------------------+
|                  970.20 |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT MIN(mass_kilogrammides), AVG(mass_kilogrammides), COUNT(*) FROM hobused;
+-------------------------+-------------------------+----------+
| MIN(mass_kilogrammides) | AVG(mass_kilogrammides) | COUNT(*) |
+-------------------------+-------------------------+----------+
|                  350.00 |              608.040000 |        5 |
+-------------------------+-------------------------+----------+
1 row in set (0.00 sec)

mysql> SELECT GROUP_CONCAT(hobusenimi) FROM hobused;
+---------------------------+
| GROUP_CONCAT(hobusenimi)  |
+---------------------------+
| Alma,Asso,Sefiir,Uno,Urva |
+---------------------------+
1 row in set (0.00 sec)

mysql> SELECT GROUP_CONCAT(hobusenimi), AVG(mass_kilogrammides) FROM hobused WHERE mass_kilogrammides>500 ;
+--------------------------+-------------------------+
| GROUP_CONCAT(hobusenimi) | AVG(mass_kilogrammides) |
+--------------------------+-------------------------+
| Sefiir,Asso              |              845.100000 |
+--------------------------+-------------------------+
1 row in set (0.00 sec)

Alampäringud

mysql> ALTER TABLE hobused CHANGE mass_kilogrammides mass NUMERIC(6, 1);
Query OK, 5 rows affected (0.19 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT AVG(mass) FROM hobused;
+-----------+
| AVG(mass) |
+-----------+
| 608.04000 |
+-----------+
1 row in set (0.00 sec)

Alla keskmise massiga hobused

mysql> SELECT hobusenimi, mass FROM hobused WHERE mass < (SELECT AVG(mass) FROM hobused);
+------------+-------+
| hobusenimi | mass  |
+------------+-------+
| Urva       | 350.0 |
| Uno        | 500.0 |
| Alma       | 500.0 |
+------------+-------+
3 rows in set (0.00 sec)

mysql> SELECT hobusenimi, mass FROM hobused WHERE mass < 608;
+------------+-------+
| hobusenimi | mass  |
+------------+-------+
| Urva       | 350.0 |
| Uno        | 500.0 |
| Alma       | 500.0 |
+------------+-------+
3 rows in set (0.00 sec)


Leidke hobuste keskmisest massist väiksemate massidega hobuste hulgast suurima hobuse mass.

SELECT MAX(mass) FROM hobused WHERE mass<(SELECT AVG(mass) FROM hobused);

mysql> SELECT MAX(mass) FROM hobused WHERE mass<(SELECT AVG(mass) FROM hobused);

+-----------+
| MAX(mass) |
+-----------+
|     500.0 |
+-----------+
1 row in set (0.00 sec)


Leidke suurima massiga hobune / hobused, kelle mass jääb alla kõigi hobuste keskmist massi.

SELECT * FROM hobused WHERE mass=(
  SELECT MAX(mass) FROM hobused WHERE mass<
        (SELECT AVG(mass) FROM hobused)
);

mysql> SELECT * FROM hobused WHERE mass=(SELECT MAX(mass) FROM hobused WHERE mass<(SELECT AVG(mass) FROM hobused));
+----+------------+------------+--------+-------+
| id | hobusenimi | synniaeg   | t6u_id | mass  |
+----+------------+------------+--------+-------+
|  2 | Uno        | 2009-03-05 |      2 | 500.0 |
|  6 | Alma       | 2011-05-02 |      3 | 500.0 |
+----+------------+------------+--------+-------+
2 rows in set (0.01 sec)

mysql> SELECT * FROM hobused, t6ud WHERE hobused.t6u_id=t6ud.id;
+----+------------+------------+--------+-------+----+-----------------------+---------------------+
| id | hobusenimi | synniaeg   | t6u_id | mass  | id | nimetus               | kirjeldus           |
+----+------------+------------+--------+-------+----+-----------------------+---------------------+
|  1 | Urva       | 2009-02-05 |      2 | 350.0 |  2 | Belgia raskeveohobune | Suured ja tugevad   |
|  2 | Uno        | 2009-03-05 |      2 | 500.0 |  2 | Belgia raskeveohobune | Suured ja tugevad   |
|  3 | Sefiir     | 2009-05-02 |      3 | 970.2 |  3 | ªairi hobune          | Kaks meetrit k§rge  |
|  5 | Asso       | 2010-01-05 |      1 | 720.0 |  1 | Eesti raskeveohobune  | Võljasuremise õõrel |
|  6 | Alma       | 2011-05-02 |      3 | 500.0 |  3 | ªairi hobune          | Kaks meetrit k§rge  |
+----+------------+------------+--------+-------+----+-----------------------+---------------------+
5 rows in set (0.00 sec)

SELECT hobusenimi, synniaeg, nimetus, kirjeldus 
   FROM hobused, t6ud 
   WHERE hobused.t6u_id=t6ud.id;
+------------+------------+-----------------------+---------------------+
| hobusenimi | synniaeg   | nimetus               | kirjeldus           |
+------------+------------+-----------------------+---------------------+
| Urva       | 2009-02-05 | Belgia raskeveohobune | Suured ja tugevad   |
| Uno        | 2009-03-05 | Belgia raskeveohobune | Suured ja tugevad   |
| Sefiir     | 2009-05-02 | ªairi hobune          | Kaks meetrit k§rge  |
| Asso       | 2010-01-05 | Eesti raskeveohobune  | Võljasuremise õõrel |
| Alma       | 2011-05-02 | ªairi hobune          | Kaks meetrit k§rge  |
+------------+------------+-----------------------+---------------------+
5 rows in set (0.00 sec)

SELECT hobusenimi, synniaeg, nimetus, kirjeldus 
   FROM hobused INNER JOIN t6ud 
   ON hobused.t6u_id=t6ud.id;

mysql> SELECT hobusenimi, synniaeg, nimetus, kirjeldus
    ->    FROM hobused INNER JOIN t6ud
    ->    ON hobused.t6u_id=t6ud.id;
+------------+------------+-----------------------+---------------------+
| hobusenimi | synniaeg   | nimetus               | kirjeldus           |
+------------+------------+-----------------------+---------------------+
| Urva       | 2009-02-05 | Belgia raskeveohobune | Suured ja tugevad   |
| Uno        | 2009-03-05 | Belgia raskeveohobune | Suured ja tugevad   |
| Sefiir     | 2009-05-02 | ªairi hobune          | Kaks meetrit k§rge  |
| Asso       | 2010-01-05 | Eesti raskeveohobune  | Võljasuremise õõrel |
| Alma       | 2011-05-02 | ªairi hobune          | Kaks meetrit k§rge  |
+------------+------------+-----------------------+---------------------+
5 rows in set (0.00 sec)


Hobuste baasi kavandamine

Kasutajad:

  Loomaarst. Käib hobuste juures visiitidel. Mõõdab/kaalub/jälgib. Vajadusel soovitab toite, harjutusi ja ravi. Näeb iga hobusega seotud toimingute ajalugu.
  
  Hobuse omanik. Näeb, mis on vaja hobusega teha, mis on tehtud. Märgib omapoolsed toimingud (nt. jala sidumine) üles. Näeb, mis makstud, mis vaja maksta. Milliseid ravimeid osta. 
  

peremehed(id, eesnimi, perekonnanimi, isikukood, aadress, telefon)
hobused(id, hobusenimi, synniaeg, t6u_id, peremehe_id, ema_id, isa_id)
t6ud(id, nimetus, kirjeldus)
arstid(id, eesnimi, perekonnanimi, telefon)
kylastused(id, arsti_id, hobuse_id, aeg, kirjeldus)
ravimid(id, nimetus, hind)
ravimid_kylastused(id, ravimi_id, kylastuse_id, juhend)
protseduurid(id, kirjeldus, kylastuse_id, l6ppaeg)
protseduurit2itmine(id, protseduuri_id, aeg, kommentaar)
arved(id, peremehe_id, kirjeldus, baashind, aeg)
ravimid_arvel(id, ravimi_id, arve_id, kogus)