0

The following relational tables/schemes are given:

In bold is the primary key, in Italics is foreign key:

-City(Name,Country,Population)

-Venue(VID,Capacity,Name)

-Concert(KID,ID,DID,duration)

-Performer(ID,KID,age,name)

-Ticket(TID,KID,price,type)

Now my assignment is to find all the concerts where there were more VIP tickets than other tickets. VIP is of the attribute type, in tickets. I honestly have thought about this problem a while. My main idea what to group KID,Type,count(*) in (tickets) and somehow add the tickets of type != VIP and select where that is less than VIP tickets, but I just don;t know how to do that formally..

Jerry West
  • 151
  • 5
  • There are multiple versions of "relational algebra". Please give a reference to the one you are supposed to use. – philipxy Feb 08 '16 at 08:21
  • Please show what you have tried for partial solutions. See [this answer](http://stackoverflow.com/a/24425914/3404097). – philipxy Feb 08 '16 at 08:45

1 Answers1

0

Something like this could work:

  SELECT CONCERT.NAME FROM CONCERT 
      INNER JOIN TICKET ON CONCERT.KID = TICKET.KID
  WHERE 
      (SELECT COUNT(CONCERT.KID) FROM CONCERT 
          INNER JOIN TICKET ON CONCERT.KID = TICKET.KID WHERE TYPE = 'VIP') >
      (SELECT COUNT(CONCERT.KID) FROM CONCERT 
          INNER JOIN TICKET ON CONCERT.KID = TICKET.KID WHERE TYPE != 'VIP')
BArms
  • 21
  • 4