MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- example for greek movies database
CREATE TABLE IF NOT EXISTS `mt_films` (
  `ID_films` int NOT NULL,
  `Title1` varchar(150) DEFAULT NULL,
  PRIMARY KEY (`ID_films`),
  KEY `ID_films` (`ID_films`)
) DEFAULT CHARSET=utf8;
INSERT INTO `mt_films` VALUES (12429,'BRUTALIA, DAYS OF LABOUR'),(14365,'AEROLIN');

SELECT * FROM `mt_films`;

Records: 2  Duplicates: 0  Warnings: 0




ID_films
Title1


12429
BRUTALIA, DAYS OF LABOUR

14365
AEROLIN


CREATE TABLE IF NOT EXISTS `st_br_persons` (
  `aa_st_br_person` int NOT NULL AUTO_INCREMENT,
  `ID_Films` int DEFAULT NULL,
  `ID_person` int DEFAULT NULL,
  `ID_brabeio` int DEFAULT NULL,
  `ID_Idiotita` int DEFAULT NULL,
  `etos_brp` int DEFAULT NULL,
  `ID_thesmos` int DEFAULT NULL,
  PRIMARY KEY (`aa_st_br_person`),
  KEY `MT_filmsST_br_persons` (`ID_Films`),
  KEY `T_brabeioST_br_persons` (`ID_brabeio`),
  KEY `T_idiotitaST_br_persons` (`ID_Idiotita`),
  KEY `T_peopleST_br_persons` (`ID_person`)
) DEFAULT CHARSET=utf8;
INSERT INTO `st_br_persons` VALUES (2677,12429,18238,131,28,2021,449),(2678,12429,36437,265,19,2021,449),(2702,12429,36433,40,29,2021,88),(2707,12429,18238,294,28,2021,88),(2708,12429,36437,294,19,2021,88),(3029,14365,34980,40,29,2023,449),(3030,14365,34980,38,27,2023,449),(3031,14365,43644,38,27,2023,449);

SELECT * FROM `st_br_persons`;

Records: 8  Duplicates: 0  Warnings: 0




aa_st_br_person
ID_Films
ID_person
ID_brabeio
ID_Idiotita
etos_brp
ID_thesmos


2677
12429
18238
131
28
2021
449

2678
12429
36437
265
19
2021
449

2702
12429
36433
40
29
2021
88

2707
12429
18238
294
28
2021
88

2708
12429
36437
294
19
2021
88

3029
14365
34980
40
29
2023
449

3030
14365
34980
38
27
2023
449

3031
14365
43644
38
27
2023
449


CREATE TABLE IF NOT EXISTS `st_peoplefilms` (
  `aa_st_peoplefilms` int NOT NULL AUTO_INCREMENT,
  `ID_films` int NOT NULL,
  `ID_person` int NOT NULL,
  `ID_idiotita` int DEFAULT NULL,
  PRIMARY KEY (`aa_st_peoplefilms`),
  UNIQUE KEY `aa_st_peoplefilms_UNIQUE` (`aa_st_peoplefilms`),
  KEY `ID_films` (`ID_films`),
  KEY `ID_idiotita` (`ID_idiotita`),
  KEY `ID_person` (`ID_person`)
) DEFAULT CHARSET=utf8;
INSERT INTO `st_peoplefilms` VALUES (206899,12429,36433,29),(206900,12429,36433,27),(206913,12429,18238,28),(206914,12429,36437,19),(246745,14365,34980,29),(246746,14365,43644,27),(246747,14365,34980,27),(246765,14365,41660,28),(246766,14365,81211,19);

SELECT * FROM `st_peoplefilms`;

Records: 9  Duplicates: 0  Warnings: 0




aa_st_peoplefilms
ID_films
ID_person
ID_idiotita


206899
12429
36433
29

206900
12429
36433
27

206913
12429
18238
28

206914
12429
36437
19

246745
14365
34980
29

246746
14365
43644
27

246747
14365
34980
27

246765
14365
41660
28

246766
14365
81211
19


CREATE TABLE IF NOT EXISTS `t_brab_p` (
  `ID_brab_p` int NOT NULL AUTO_INCREMENT,
  `brabeio_p` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`ID_brab_p`),
  KEY `ID_brab_p` (`ID_brab_p`)
) DEFAULT CHARSET=utf8;
INSERT INTO `t_brab_p` VALUES (38,'Screenplay Award'),(40,'Direction Award'),(131,'Scenography Award'),(265,'Costume Design Award');

SELECT * FROM `t_brab_p`;

Records: 4  Duplicates: 0  Warnings: 0




ID_brab_p
brabeio_p


38
Screenplay Award

40
Direction Award

131
Scenography Award

265
Costume Design Award


CREATE TABLE IF NOT EXISTS `t_idiotita` ( -- role
  `ID_idiotita` int NOT NULL AUTO_INCREMENT,
  `idiotita` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`ID_idiotita`),
  KEY `ID_idiotita` (`ID_idiotita`)
) DEFAULT CHARSET=utf8;
INSERT INTO `t_idiotita` VALUES (19,'Costuming'),(27,'Scenary'),(28,'Scenography'),(29,'Director');

SELECT * FROM `t_idiotita`;

Records: 4  Duplicates: 0  Warnings: 0




ID_idiotita
idiotita


19
Costuming

27
Scenary

28
Scenography

29
Director


CREATE TABLE IF NOT EXISTS `t_people` (
  `ID_person` int NOT NULL AUTO_INCREMENT,
  `person` varchar(100) NOT NULL,
  PRIMARY KEY (`ID_person`),
  UNIQUE KEY `ID_person_UNIQUE` (`ID_person`),
  KEY `ID_person` (`ID_person`)
) DEFAULT CHARSET=utf8;
INSERT INTO `t_people` VALUES (18238,'Kalogianni Dafni'),(34980,'Koukias Pantelis Alexis'),(36433,'Mavris Manolis'),(36437,'Gioulakou Eva'),(43644,'Mathioudakis Michalis');

SELECT * FROM `t_people`;

Records: 5  Duplicates: 0  Warnings: 0




ID_person
person


18238
Kalogianni Dafni

34980
Koukias Pantelis Alexis

36433
Mavris Manolis

36437
Gioulakou Eva

43644
Mathioudakis Michalis


CREATE TABLE IF NOT EXISTS `t_thesmos` ( -- institution
  `ID_thesmos` int NOT NULL AUTO_INCREMENT,
  `thesmos` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`ID_thesmos`),
  UNIQUE KEY `Name` (`thesmos`),
  KEY `id` (`ID_thesmos`)
) DEFAULT CHARSET=utf8;
INSERT INTO `t_thesmos` VALUES (12,'DISFF'),(270,'DISFF International'),(446,'DISFF International Anima'),(447,'DISFF International Competition'),(448,'DISFF International student'),(566,'DISFF National Anima'),(449,'DISFF National Competition'),(565,'DISFF National student');

SELECT * FROM `t_thesmos`;

Records: 8  Duplicates: 0  Warnings: 0




ID_thesmos
thesmos


12
DISFF

270
DISFF International

446
DISFF International Anima

447
DISFF International Competition

448
DISFF International student

566
DISFF National Anima

449
DISFF National Competition

565
DISFF National student


SELECT 
    f.ID_films,
    br_p.etos_brp AS year_award,
    b.brabeio_p AS award,
    t.thesmos AS festival,
    f.Title1 AS movie,
    GROUP_CONCAT(p.ID_person, '::', p.person SEPARATOR '||') AS awarded_people,
    (
        SELECT GROUP_CONCAT(p.ID_person, '::', p.person SEPARATOR '||')
        FROM t_people p
        JOIN st_peoplefilms pf ON p.ID_person = pf.ID_person AND pf.ID_idiotita = 29
        WHERE pf.ID_films = f.ID_films
    ) AS director
FROM st_br_persons br_p
JOIN t_people p
    ON br_p.ID_person = p.ID_person
JOIN mt_films f
    ON br_p.ID_films = f.ID_films
/* JOIN t_idiotita i
    ON br_p.ID_Idiotita = i.ID_idiotita */
JOIN t_thesmos t
    ON br_p.ID_thesmos = t.ID_thesmos
JOIN t_brab_p b
    ON br_p.ID_brabeio = b.ID_brab_p

WHERE br_p.ID_thesmos IN (12 , 270, 446, 447, 448, 449, 565, 566)
  
GROUP BY
    br_p.ID_brabeio,
    br_p.etos_brp,
    br_p.ID_Films,
    br_p.ID_thesmos
ORDER BY br_p.etos_brp DESC;


ID_films
year_award
award
festival
movie
awarded_people
director


14365
2023
Screenplay Award
DISFF National Competition
AEROLIN
34980::Koukias Pantelis Alexis||43644::Mathioudakis Michalis
34980::Koukias Pantelis Alexis

14365
2023
Direction Award
DISFF National Competition
AEROLIN
34980::Koukias Pantelis Alexis
34980::Koukias Pantelis Alexis

12429
2021
Scenography Award
DISFF National Competition
BRUTALIA, DAYS OF LABOUR
18238::Kalogianni Dafni
36433::Mavris Manolis

12429
2021
Costume Design Award
DISFF National Competition
BRUTALIA, DAYS OF LABOUR
36437::Gioulakou Eva
36433::Mavris Manolis


SELECT 
        `st_br_persons`.`aa_st_br_person` AS `aa_st_br_person`,
        `st_br_persons`.`etos_brp` AS `etos_brp`,
        `mt_films`.`ID_films` AS `ID_films`,
        `t_brab_p`.`brabeio_p` AS `brabeio`,
        `t_brab_p`.`ID_brab_p` AS `ID_price`,
        `t_thesmos`.`thesmos` AS `thesmos`,
        CONCAT(`mt_films`.`Title1`) AS `movie`,
        GROUP_CONCAT(DISTINCT `t_people`.`person`
            SEPARATOR ', ') AS `awarded`,
        GROUP_CONCAT((CASE
                WHEN (`st_peoplefilms_1`.`ID_idiotita` = 29) THEN CONCAT(`t_people_1`.`person`)
            END)
            SEPARATOR ',') AS `Director`
    FROM
        (((((((`t_people` `t_people_1`
        JOIN `st_peoplefilms` `st_peoplefilms_1` ON ((`t_people_1`.`ID_person` = `st_peoplefilms_1`.`ID_person`)))
        JOIN `mt_films` ON ((`mt_films`.`ID_films` = `st_peoplefilms_1`.`ID_films`)))
        JOIN `st_br_persons` ON ((`st_br_persons`.`ID_person` = `t_people_1`.`ID_person`)))
        JOIN `t_idiotita` ON ((`t_idiotita`.`ID_idiotita` = `st_br_persons`.`ID_Idiotita`)))
        JOIN `t_thesmos` ON ((`t_thesmos`.`ID_thesmos` = `st_br_persons`.`ID_thesmos`)))
        JOIN `t_brab_p` ON ((`t_brab_p`.`ID_brab_p` = `st_br_persons`.`ID_brabeio`)))
        JOIN `t_people` ON ((`t_people`.`ID_person` = `st_br_persons`.`ID_person`)))
    WHERE
        ((`st_br_persons`.`ID_thesmos` IN (12 , 270, 446, 447, 448, 449, 565, 566))
            AND (`st_br_persons`.`ID_Films` = `st_peoplefilms_1`.`ID_films`))
    GROUP BY `st_br_persons`.`ID_brabeio` , `st_br_persons`.`ID_Films`
    ORDER BY `st_br_persons`.`etos_brp` DESC

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'fiddle.st_br_persons.aa_st_br_person' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by


/* Bad SQL dependent on ONLY_FULL_GROUP_BY being disabled */
SELECT @@sql_mode;
SET sql_mode = sys.list_drop(@@sql_mode, 'ONLY_FULL_GROUP_BY');
SELECT @@sql_mode;


@@sql_mode


ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION




@@sql_mode


STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION


SELECT 
        `st_br_persons`.`aa_st_br_person` AS `aa_st_br_person`,
        `st_br_persons`.`etos_brp` AS `etos_brp`,
        `mt_films`.`ID_films` AS `ID_films`,
        `t_brab_p`.`brabeio_p` AS `brabeio`,
        `t_brab_p`.`ID_brab_p` AS `ID_price`,
        `t_thesmos`.`thesmos` AS `thesmos`,
        CONCAT(`mt_films`.`Title1`) AS `movie`,
        GROUP_CONCAT(DISTINCT `t_people`.`person`
            SEPARATOR ', ') AS `awarded`,
        GROUP_CONCAT((CASE
                WHEN (`st_peoplefilms_1`.`ID_idiotita` = 29) THEN CONCAT(`t_people_1`.`person`)
            END)
            SEPARATOR ',') AS `Director`
    FROM
        (((((((`t_people` `t_people_1`
        JOIN `st_peoplefilms` `st_peoplefilms_1` ON ((`t_people_1`.`ID_person` = `st_peoplefilms_1`.`ID_person`)))
        JOIN `mt_films` ON ((`mt_films`.`ID_films` = `st_peoplefilms_1`.`ID_films`)))
        JOIN `st_br_persons` ON ((`st_br_persons`.`ID_person` = `t_people_1`.`ID_person`)))
        JOIN `t_idiotita` ON ((`t_idiotita`.`ID_idiotita` = `st_br_persons`.`ID_Idiotita`)))
        JOIN `t_thesmos` ON ((`t_thesmos`.`ID_thesmos` = `st_br_persons`.`ID_thesmos`)))
        JOIN `t_brab_p` ON ((`t_brab_p`.`ID_brab_p` = `st_br_persons`.`ID_brabeio`)))
        JOIN `t_people` ON ((`t_people`.`ID_person` = `st_br_persons`.`ID_person`)))
    WHERE
        ((`st_br_persons`.`ID_thesmos` IN (12 , 270, 446, 447, 448, 449, 565, 566))
            AND (`st_br_persons`.`ID_Films` = `st_peoplefilms_1`.`ID_films`))
    GROUP BY `st_br_persons`.`ID_brabeio` , `st_br_persons`.`ID_Films`
    ORDER BY `st_br_persons`.`etos_brp` DESC


aa_st_br_person
etos_brp
ID_films
brabeio
ID_price
thesmos
movie
awarded
Director


3030
2023
14365
Screenplay Award
38
DISFF National Competition
AEROLIN
Koukias Pantelis Alexis, Mathioudakis Michalis
Koukias Pantelis Alexis

3029
2023
14365
Direction Award
40
DISFF National Competition
AEROLIN
Koukias Pantelis Alexis
Koukias Pantelis Alexis

2677
2021
12429
Scenography Award
131
DISFF National Competition
BRUTALIA, DAYS OF LABOUR
Kalogianni Dafni
null

2678
2021
12429
Costume Design Award
265
DISFF National Competition
BRUTALIA, DAYS OF LABOUR
Gioulakou Eva
null
aa_st_br_person	ID_Films	ID_person	ID_brabeio	ID_Idiotita	etos_brp	ID_thesmos
2677	12429	18238	131	28	2021	449
2678	12429	36437	265	19	2021	449
2702	12429	36433	40	29	2021	88
2707	12429	18238	294	28	2021	88
2708	12429	36437	294	19	2021	88
3029	14365	34980	40	29	2023	449
3030	14365	34980	38	27	2023	449
3031	14365	43644	38	27	2023	449
aa_st_peoplefilms	ID_films	ID_person	ID_idiotita
206899	12429	36433	29
206900	12429	36433	27
206913	12429	18238	28
206914	12429	36437	19
246745	14365	34980	29
246746	14365	43644	27
246747	14365	34980	27
246765	14365	41660	28
246766	14365	81211	19
ID_brab_p	brabeio_p
38	Screenplay Award
40	Direction Award
131	Scenography Award
265	Costume Design Award
ID_person	person
18238	Kalogianni Dafni
34980	Koukias Pantelis Alexis
36433	Mavris Manolis
36437	Gioulakou Eva
43644	Mathioudakis Michalis
ID_thesmos	thesmos
12	DISFF
270	DISFF International
446	DISFF International Anima
447	DISFF International Competition
448	DISFF International student
566	DISFF National Anima
449	DISFF National Competition
565	DISFF National student