MySQL: Common Table Expression (CTE)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

PS C:\Users\pretentious> mysqldump --add-drop-database --add-drop-table --add-drop-trigger --comments --compact --complete-insert --compress --create-options --default-character-set=utf8 --delete-source-logs --source-data=2 java jiff
mysqldump: [Warning] unknown option '--loose_no_such_option'.
-- CHANGE MASTER TO MASTER_LOG_FILE='cruces-bin.000051', MASTER_LOG_POS=157;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `jiff` (
  `date` date DEFAULT NULL,
  `price` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `jiff` (`date`, `price`) VALUES ('2017-01-03',100),('2017-01-03',200),('2017-01-06',50),('2017-01-08',10),('2017-01-08',20),('2017-01-08',150),('2017-01-10',5);

 

 

 

 

CREATE TABLE employees (
  id         INT PRIMARY KEY NOT NULL,
  name       VARCHAR(100) NOT NULL,
  manager_id INT NULL,
  INDEX (manager_id),
FOREIGN KEY (manager_id) REFERENCES employees (id)
);
INSERT INTO employees VALUES
(333, "Yasmina", NULL),  # Yasmina is the CEO (manager_id is NULL)
(198, "John", 333),      # John has ID 198 and reports to 333 (Yasmina)
(692, "Tarek", 333),
(29, "Pedro", 198),
(4610, "Sarah", 29),
(72, "Pierre", 29),
(123, "Adil", 692);