create table Books ( ID int not null primary key , Title varchar(255) not null , Author varchar(255) not null , PublisherID int not null , LanguageCode char(2) not null , Genre varchar(10) not null , DatePublished date not null , ISBN varchar (255) not null);

insert into books (ID, Title, Author, PublisherID, LanguageCode, Genre, DatePublished, ISBN) values (1, 'Death On The Nile', 'Agatha Christie', 2, 'en', 'Novel', '1937-11-01', '4215574186436');

create table Branches (ID int not null primary key , Name varchar(255) not null , City varchar(255) not null , Address varchar(255) not null);

insert into Branches(ID, Name, City, Address) values (1, 'Name1', 'London', 'Address1') , (2, 'Name2', 'Birmingham', 'Address2') , (3, 'Name3', 'Manchester', 'Address3');

create table BookCopies (ID int not null primary key , BookID int default 1 not null references books (ID) , BranchID int not null references branches (ID));

insert into BookCopies (ID, BranchID) values (1,1), (2,1), (3,2), (4,2), (5,2), (6,3);

SELECT BookID, BranchID, COUNT(*) FROM BookCopies GROUP BY BookID, BranchID;

SELECT b.ID as BookID , b.Title , b.PublisherID , b.DatePublished , b.ISBN , r.ID as BranchID , r.Name as Branch , x.CNT FROM Books AS b JOIN ( SELECT BookID, BranchID, COUNT(*) AS CNT FROM BookCopies GROUP BY BookID, BranchID ) AS x ON b.ID = x.BookID JOIN Branches AS r ON r.ID = x.BranchID ORDER BY b.ID, r.ID