HLD HLD - 2 months ago 5
MySQL Question

What is the SQL statement for the following task:

List those publishers who publish paperbacks and the number of paperbacks published by each.
I'm having a difficult time with the counting the paperbacks for each publisher. Any help will be much appreciated. Thanks!

This is what I have:

SELECT publisherName, COUNT(paperback) AS "numPaperback"

FROM publisher, book

WHERE paperback = "Yes";

**Henry Books Table Schema**

**author**
authorNum INT PRIMARY KEY
authorLast VARCHAR(12),
authorFirst VARCHAR(10)

**publisher**
publisherCode CHAR(3) PRIMARY KEY
publisherName VARCHAR(25)
city VARCHAR(20)

**book**
bookCode CHAR(4) PRIMARY KEY
title VARCHAR(40)
publisherCode CHAR(3)
bookType CHAR(3)
paperback ENUM('No', 'Yes')

CONSTRAINT book_fk_publisher
FOREIGN KEY (publisherCode)
REFERENCES publisher(publisherCode)

**branch**
branchNum INT
branchName VARCHAR(50)
branchLocation VARCHAR(50)

**copy**
bookCode CHAR(4)
branchNum INT
copyNum INT PRIMARY KEY
quality ENUM('Excellent', 'Fair', 'Good', 'Poor')
price DECIMAL(8,2)

CONSTRAINT copy_pk
PRIMARY KEY (bookCode, branchNum, copyNum
CONSTRAINT copy_fk_book
FOREIGN KEY (bookCode)
REFERENCES book(bookCode),
CONSTRAINT copy_fk_branch
FOREIGN KEY (branchNum)
REFERENCES branch(branchNum)

**wrote**
bookCode CHAR(4)
authorNum INT
sequence INT

PRIMARY KEY (BookCode, AuthorNum),
CONSTRAINT wrote_fk_book
FOREIGN KEY (bookCode)
REFERENCES book(bookCode),
CONSTRAINT wrote_fk_author
FOREIGN KEY (authorNum)
REFERENCES author(authorNum)

Answer

Seems like you are new. So I will try to walk you through this...

  1. Join the publisher table to the book table. They share a publisher code which appears to link them together.

  2. Filter the joined table by book type using a where clause. You want paperbacks.

  3. Perform a group by on publisher code and name, and then count the book column

Does this help get you started?

Comments