
CREATE TABLE book (
	"Book_ID" INTEGER, 
	"Title" TEXT, 
	"Issues" REAL, 
	"Writer" TEXT, 
	PRIMARY KEY ("Book_ID")
)


CREATE TABLE publication (
	"Publication_ID" INTEGER, 
	"Book_ID" INTEGER, 
	"Publisher" TEXT, 
	"Publication_Date" TEXT, 
	"Price" REAL, 
	PRIMARY KEY ("Publication_ID"), 
	FOREIGN KEY("Book_ID") REFERENCES book ("Book_ID")
)

CREATE TABLE book (
	"Book_ID" INTEGER, 
	"Title" TEXT, 
	"Issues" REAL, 
	"Writer" TEXT, 
	PRIMARY KEY ("Book_ID")
)

/*
2 rows from book table:
Book_ID	Title	Issues	Writer
1	The Black Lamb	6.0	Timothy Truman
2	Bloody Mary	4.0	Garth Ennis
*/


CREATE TABLE publication (
	"Publication_ID" INTEGER, 
	"Book_ID" INTEGER, 
	"Publisher" TEXT, 
	"Publication_Date" TEXT, 
	"Price" REAL, 
	PRIMARY KEY ("Publication_ID"), 
	FOREIGN KEY("Book_ID") REFERENCES book ("Book_ID")
)

/*
2 rows from publication table:
Publication_ID	Book_ID	Publisher	Publication_Date	Price
1	1	Pearson	August 2008	15000000.0
2	3	Thomson Reuters	March 2008	6000000.0
*/
Show different publishers together with the number of publications they have.
SELECT Publisher ,  COUNT(*) FROM publication GROUP BY Publisher
[{'Publisher': 'Bertelsmann', 'COUNT(*)': 1}, {'Publisher': 'Pearson', 'COUNT(*)': 2}, {'Publisher': 'Springer Nature', 'COUNT(*)': 1}, {'Publisher': 'Thomson Reuters', 'COUNT(*)': 1}, {'Publisher': 'Wiley', 'COUNT(*)': 2}]