0

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');
Tushar
  • 3,527
  • 9
  • 27
  • 49
Brett Saguid
  • 13
  • 3

1 Answers1

0

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%'
paddyfields
  • 1,466
  • 2
  • 15
  • 25
  • SELECT COUNT(Shows) FROM Shows, Episode WHERE Shows.showid = Episode.showID AND Episode.title LIKE {'%good%'}; It is not working.. – Brett Saguid Mar 19 '15 at 15:17
  • I've updated my answer with a suggestion. Why are you using { } ? – paddyfields Mar 19 '15 at 15:18
  • Nevermind! I changed it to: SELECT COUNT(Shows.title) FROM Shows, Episode WHERE Shows.showid = Episode.showID AND Episode.title LIKE '%good%'; it worked! Thank you – Brett Saguid Mar 19 '15 at 15:20
  • Could you help with this query as well?? I'd really appreciate it! No one in my class is helping my over spring break :( Find all actors who are in the main cast of at least one show and in the recurring cast of at least one show. Display the actor's first name, last name, the title of the show in which the actor is in the main cast, the title of the show in which the actors is in the recurring cast, and the role the actor plays in each show. I am confused as to how to approach this – Brett Saguid Mar 19 '15 at 15:21