MBX Task #2

/* The request code for task 2 is below. Used PostgreSQL */

SELECT p.ProductName AS ProductName, c.CategoryName AS CategoryName
FROM Product p
LEFT JOIN ProductCategory pc ON pc.ProductId = p.ProductId
LEFT JOIN Category c ON c.CategoryId = pc.CategoryId;
/* Below is the code for creating and filling the database: */

create table Product
(
    ProductId int primary key,
    ProductName varchar(128) not null
)

insert into Product(ProductId, ProductName) values (1, N'Acer K222HQL')
insert into Product(ProductId, ProductName) values (2, N'Intel Core i5-10400')
insert into Product(ProductId, ProductName) values (3, N'Samsung M378A1K43EB2-CWE')
insert into Product(ProductId, ProductName) values (4, N'Logitech M590')

create table Category
(
    CategoryId int primary key,
    CategoryName varchar(64) not null
)

insert into Category(CategoryId, CategoryName) values(1, N'CPU')
insert into Category(CategoryId, CategoryName) values(2, N'RAM')
insert into Category(CategoryId, CategoryName) values(3, N'Monitor')

create table ProductCategory
(
    CategoryId int references Category(CategoryId),
    ProductId int references Product(ProductId),
    primary key(CategoryId, ProductId)
)

insert into ProductCategory(CategoryId, ProductId) values (3, 1)
insert into ProductCategory(CategoryId, ProductId) values (1, 2)
insert into ProductCategory(CategoryId, ProductId) values (2, 3)