DROP SCHEMA IF EXISTS HW_COMPUTERVARIATIONADVANCED;

CREATE SCHEMA HW_COMPUTERVARIATIONADVANCED;

USE HW_COMPUTERVARIATIONADVANCED;

CREATE TABLE COMPUTER (
  ID VARCHAR(20) PRIMARY KEY,
  Model VARCHAR(40)
);

CREATE TABLE PERIPHERAL (
  ID VARCHAR(20) PRIMARY KEY,
  Model VARCHAR(40),
  TYPE ENUM ('mouse', 'keyboard', 'screen', 'printer'),
  LastConnexion DATETIME
);

CREATE TABLE CONNEXION (
  Computer VARCHAR(20),
  Peripheral VARCHAR(20),
  PRIMARY KEY (Computer, Peripheral),
  FOREIGN KEY (Computer) REFERENCES COMPUTER (ID) ON DELETE
    CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (Peripheral) REFERENCES PERIPHERAL (ID) ON
    DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TRIGGER last_connexion_update
  BEFORE INSERT ON CONNEXION
  FOR EACH ROW UPDATE PERIPHERAL
  SET LastConnexion = NOW ()
WHERE NEW.Peripheral = PERIPHERAL.ID;

INSERT INTO COMPUTER
VALUES (
  'A',
  'Apple IIc Plus'),
(
  'B',
  'Commodore SX-64');

INSERT INTO PERIPHERAL (
  ID,
  Model,
  TYPE)
VALUES (
  '12',
  'Trendcom Model',
  'printer'),
(
  '14',
  'TP-10 Thermal Matrix',
  'printer'),
(
  '15',
  'IBM Selectric',
  'keyboard');

INSERT INTO CONNEXION
VALUES (
  'A',
  '12'),
(
  'B',
  '14'),
(
  'A',
  '15');

SELECT *
FROM PERIPHERAL;

-- start snippet solution
/* code/sql/HW_ComputerVariationAdvancedSol.sql */
START TRANSACTION;

DELETE FROM CONNEXION
WHERE Computer = 'A';

SELECT *
FROM COMPUTER;

SELECT *
FROM PERIPHERAL;

SELECT *
FROM CONNEXION;

ROLLBACK;

START TRANSACTION;

DELETE FROM COMPUTER
WHERE ID = 'A';

SELECT *
FROM COMPUTER;

SELECT *
FROM PERIPHERAL;

SELECT *
FROM CONNEXION;

ROLLBACK;

START TRANSACTION;

DELETE FROM PERIPHERAL
WHERE ID = '15';

SELECT *
FROM COMPUTER;

SELECT *
FROM PERIPHERAL;

SELECT *
FROM CONNEXION;

ROLLBACK;

START TRANSACTION;

DELETE FROM CONNEXION
WHERE Computer <> 'A';

SELECT *
FROM COMPUTER;

SELECT *
FROM PERIPHERAL;

SELECT *
FROM CONNEXION;

ROLLBACK;

SELECT TYPE
FROM PERIPHERAL
WHERE ID = '12';

SELECT ID
FROM COMPUTER
WHERE Model LIKE '%Apple%';

SELECT COUNT(ID)
FROM COMPUTER;

SELECT DISTINCT (TYPE)
FROM PERIPHERAL;

SELECT CONNEXION.Computer
FROM CONNEXION,
  PERIPHERAL
WHERE PERIPHERAL.Type = 'keyboard'
  AND PERIPHERAL.ID = CONNEXION.Peripheral;

SELECT COMPUTER.Model
FROM CONNEXION,
  PERIPHERAL,
  COMPUTER
WHERE PERIPHERAL.Model = 'TP-10 Thermal Matrix'
  AND PERIPHERAL.ID = CONNEXION.Peripheral
  AND CONNEXION.Computer = COMPUTER.ID;

INSERT INTO CONNEXION
VALUES (
  'B',
  '12');

SELECT *
FROM COMPUTER;

SELECT *
FROM PERIPHERAL;

-- Note that the "LastConnexion" attribute has been updated.
SELECT *
FROM CONNEXION;

-- end snippet solution