Beispiele und Übungsaufgaben

Zeitrechnen

WARNINGS; SET sql_mode = 'ANSI,TRADITIONAL,PIPES_AS_CONCAT';
SET div_precision_increment = 4;
DROP SCHEMA IF EXISTS S20171127092111;
CREATE SCHEMA S20171127092111;
USE S20171127092111
CREATE TABLE HMS
( H INT, M INT, S INT );
INSERT INTO HMS( H, M, S )VALUES
(  2, 52, 15 ),
(  4, 41, 58 );
SELECT * FROM HMS;
SELECT SEC_TO_TIME( SUM( TIME_TO_SEC( T )))FROM( SELECT CAST( H || ':' || M || ':' || S AS TIME ) AS T FROM HMS )AS T1;

07:34:13

SEC_TO_TIME arbeitet nicht mehr korrekt, wenn die Summe zu groß wird!

Untertypen

.-------------------------.
| PRODUCT                 |
'-------------------------'
      ^              ^
      |              |
.----------.   .----------.
| PHYSICAL |   | DATA     |
'----------'   '----------'

Untertypen

SET foreign_key_checks = 0;
DROP TABLE IF EXISTS PHYSICAL;
DROP TABLE IF EXISTS DATA;
DROP TABLE IF EXISTS PRODUCT;
DROP VIEW IF EXISTS PHYSICAL_PRODUCT;
DROP VIEW IF EXISTS ALL_PRODUCT;
DROP VIEW IF EXISTS PHYSICAL_TYPED;
DROP VIEW IF EXISTS DATA_TYPED;
SET foreign_key_checks = 1;

CREATE TABLE PRODUCT
( PRODUCT SERIAL PRIMARY KEY,
  PRICE DECIMAL ( 22, 2 ) );

CREATE TABLE PHYSICAL
( PRODUCT BIGINT UNSIGNED NOT NULL UNIQUE PRIMARY KEY,
  WEIGHT DECIMAL ( 12, 5 ),
  FOREIGN KEY ( PRODUCT ) REFERENCES PRODUCT ( PRODUCT )
  ON DELETE CASCADE );

CREATE TABLE DATA
( PRODUCT BIGINT UNSIGNED NOT NULL UNIQUE PRIMARY KEY,
  SIZE BIGINT,
  FOREIGN KEY ( PRODUCT ) REFERENCES PRODUCT ( PRODUCT )
  ON DELETE CASCADE );

CREATE VIEW PHYSICAL_PRODUCT AS 
SELECT * FROM PRODUCT NATURAL JOIN PHYSICAL;

INSERT INTO PRODUCT ( PRICE ) VALUES ( 12.00 );

INSERT INTO PHYSICAL ( PRODUCT, WEIGHT ) 
VALUES ( LAST_INSERT_ID(), 10.00 );

SELECT * FROM PHYSICAL_PRODUCT;

INSERT INTO PRODUCT ( PRICE ) VALUES ( 24.00 );

INSERT INTO DATA ( PRODUCT, SIZE ) 
VALUES ( LAST_INSERT_ID(), 10.00 );

CREATE TABLE TYPE_P AS SELECT 'P' AS TYPE;
CREATE TABLE TYPE_D AS SELECT 'D' AS TYPE;

CREATE VIEW PHYSICAL_TYPED AS
SELECT PRODUCT, TYPE, WEIGHT FROM PHYSICAL 
JOIN TYPE_P;

CREATE VIEW DATA_TYPED AS
SELECT PRODUCT, TYPE, SIZE FROM DATA 
JOIN TYPE_D;

CREATE VIEW ALL_PRODUCT AS
SELECT PRODUCT.PRODUCT, 
COALESCE( PHYSICAL_TYPED.TYPE, DATA_TYPED.TYPE ) AS TYPE, 
PRICE, WEIGHT, SIZE FROM PRODUCT 
LEFT JOIN PHYSICAL_TYPED ON PRODUCT.PRODUCT=PHYSICAL_TYPED.PRODUCT 
LEFT JOIN DATA_TYPED     ON PRODUCT.PRODUCT=DATA_TYPED.PRODUCT;

SELECT * FROM ALL_PRODUCT;

+---------+------+-------+----------+------+
| PRODUCT | TYPE | PRICE | WEIGHT   | SIZE |
+---------+------+-------+----------+------+
|       1 | P    | 12.00 | 10.00000 | NULL |
|       2 | D    | 24.00 |     NULL |   10 |
+---------+------+-------+----------+------+

Übungsaufgaben

/   Übungsaufgabe (mittel)

Struktur der Basistabelle:

DROP SCHEMA S; CREATE SCHEMA S; USE S;

CREATE TABLE base
( id INT NOT NULL AUTO_INCREMENT KEY,
  Hauptwert INT NULL,
  Linkswert INT NULL,
  Rechtswert INT NULL );

Es soll der Wert einer neuen Spalte nach folgenden Regeln ermittelt werden: Wenn ein Hauptwert angegeben ist, wird dieser verwendet, wenn Linkswert und Rechtswert angegeben sind, soll deren Mittelwert verwendet werden, sonst ist der Wert der neuen Spalte NULL.

Erstellen Sie die Basistabelle mit der passenden Struktur, füllen Sie diese mit einigen Testzeilen und erstellen Sie einen View mit einer weiteren Spalte wie oben angegeben.

Motivation In einer Newsgroup fragte jemand wie das geht, weil er dies für die Verwaltung von Chemikalien benötigte.

Verständnisfrage Was bedeutet es, daß ein Wert „angegeben“ ist?