SQL查询 - 有条款

Question

Here are my database relations:

shows(showID, title, premiere_year, network, creator, category)

episode(showID, episodeID, airdate, title)

• showID is a foreign key to shows

actor(actID, fname, lname)

• main_cast(showID, actorID, role)
• showID is a foreign key to shows
• actID is a foreign key to actor

recurring_cast(showID, episodeID, actorID, role)

• showID is a foreign key to shows
• episodeID is a foreign key to episode
• actID is a foreign key to actor

customer(custID, fname, lname, email, creditcard,membersince,renewaldate, password, username)

cust_queue(custID, showID, datequeued)

• custID is a foreign key to customer
• showID is a foreign key to shows

watched(custID, showID, episodeID, datewatched) - custID is a foreign key to customer - showID is a foreign key to shows - (showID, episodeID) is a foreign key to episode - (custID, showID) is a foreign key to cust_queue

All the 'IDs' are primary keys

---

I was given some queries and I'm having problems with them. One of which involves a HAVING Clause. The query is:

Find how many shows have episodes with the word "good" in the title?

Im assuming I have to use a COUNT and a HAVING Clause. But my syntax is always off. Someone help?!! My latest attempt looks like this:

SELECT Shows, COUNT(Episode)
FROM Shows, Episode
WHERE Shows.showid = Episode.showID
GROUP BY Shows
HAVING (Episode.title = 'good');

Answer 1

You need to use 'LIKE' and then wild cards

... WHERE title LIKE '%good%'

The % at each end will find any titles with the word 'good' included.

For your query in particular, the query below should find all episodes with the word 'good' if I understand your database schematic correctly

SELECT COUNT(*) FROM Episode WHERE title LIKE '%good%'