PHP school: MySQL: Home task 3

1.create-database.sql

CREATE DATABASE mydb CHARACTER SET utf8;

2.create-demo-catalog.sql

CREATE TABLE categories (
  id int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf16;

CREATE TABLE category_descriptions (
  category_id int(11) NOT NULL,
  lang char(3) NOT NULL,
  name varchar(255) NOT NULL,
  description text,
  PRIMARY KEY (category_id,lang)
) ENGINE=InnoDB DEFAULT CHARSET=utf16;

CREATE TABLE products (
  id int(11) NOT NULL AUTO_INCREMENT,
  category_id int(11) NOT NULL,
  price decimal(12,2) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf16;

CREATE TABLE product_descriptions (
  product_id int(11) NOT NULL,
  lang char(3) NOT NULL,
  name varchar(255) NOT NULL,
  description text,
  PRIMARY KEY (product_id,lang)
) ENGINE=InnoDB DEFAULT CHARSET=utf16;


INSERT INTO categories (id) VALUES
(1),
(2),
(3),
(4);

INSERT INTO category_descriptions (category_id, lang, name, description) VALUES
(1, 'eng', 'Phones', 'Apple iPphone, Samsung, e.t.c'),
(1, 'rus', 'Phones', 'Apple iPphone, Samsung, e.t.c'),
(2, 'eng', 'Monitors', '2k, 4k e.t.c'),
(2, 'rus', 'Monitors', '2k, 4k e.t.c'),
(3, 'eng', 'HDD', 'HDD drives up to 2Tb'),
(3, 'rus', 'HDD', 'HDD drives up to 2Tb'),
(4, 'eng', 'Keyboards and mouses', 'Many items'),
(4, 'rus', 'Keyboards and mouses', 'Many items');

INSERT INTO products (id, category_id, price) VALUES
(1, 1, '500.00'),
(2, 1, '600.00'),
(3, 2, '300.00'),
(4, 3, '300.00'),
(5, 4, '50.00'),
(6, 1, '200.00'),
(7, 1, '400.00'),
(8, 2, '299.00'),
(9, 2, '150.00'),
(10, 2, '199.00'),
(11, 2, '99.00'),
(12, 4, '289.00');

INSERT INTO product_descriptions (product_id, lang, name, description) VALUES
(1, 'eng', 'iPhone 7', 'Amazing phone'),
(1, 'rus', 'iPhone 7', 'Amazing phone'),
(2, 'eng', 'iPhone 8', 'Amazing phone'),
(2, 'rus', 'iPhone 8', 'Amazing phone'),
(3, 'eng', 'Dell monitor', 'Awesome monitor'),
(3, 'rus', 'Dell monitor', 'Awesome monitor'),
(4, 'eng', 'Seagate 1Tb', 'Seagate 1Tb HDD drive'),
(4, 'rus', 'Seagate 1Tb', 'Seagate 1Tb HDD drive'),
(5, 'eng', 'Microsoft keyboard', 'Microsoft keyboard'),
(5, 'rus', 'Microsoft keyboard', 'Microsoft keyboard'),
(6, 'eng', 'iPhone 6s', 'Old but not bad device'),
(6, 'rus', 'iPhone 6s', 'Old but not bad device'),
(7, 'eng', 'Google Nexus 4', 'Best choice for google lovers'),
(7, 'rus', 'Google Nexus 4', 'Best choice for google lovers'),
(8, 'eng', 'Dell SE2416H', '24\" Dell SE2416H Silver-Black (210-AFZC)'),
(8, 'rus', 'Dell SE2416H', '24\" Dell SE2416H Silver-Black (210-AFZC)'),
(9, 'eng', 'Dell P2419H', '23.8\" Dell P2419H Black (210-APWU) - Сinema screen'),
(9, 'rus', 'Dell P2419H', '23.8\" Dell P2419H Black (210-APWU) - Сinema screen'),
(10, 'eng', 'Philips 223V5LSB2', '21.5\" Philips 223V5LSB2/10/62'),
(10, 'rus', 'Philips 223V5LSB2', '21.5\" Philips 223V5LSB2/10/62'),
(11, 'eng', 'LG 22MP48A-P', 'Display 21.5\" LG 22MP48A-P'),
(11, 'rus', 'LG 22MP48A-P', 'Display 21.5\" LG 22MP48A-P'),
(12, 'eng', 'Seagate Exos 7E8', 'Seagate Exos 7E8 512E 8TB 7200rpm 256MB ST8000NM0055 3.5\" SATA III'),
(12, 'rus', 'Seagate Exos 7E8', 'Seagate Exos 7E8 512E 8TB 7200rpm 256MB ST8000NM0055 3.5\" SATA III');

3.exec-query.sql

SELECT
	p.*, pd.*, cd.*
FROM products p
JOIN product_descriptions pd
	ON p.id = pd.product_id
		AND pd.lang = 'eng'
JOIN categories c
	ON p.category_id = c.id
JOIN category_descriptions cd
	ON c.id = cd.category_id
		AND cd.lang = 'eng'