Question

Consider the following relations and relationship

property(propertyNo, ownerID, type, rent, address)

Newspaper(newspaperNo, name, street, city, zipCode, phoenNo)

Advertisement(propertyNo, newspaperNo, date, cost)

1. List the propertyNo of all the properties that have been advertised on “Houston Chronical”

2. List the names of all the newspapers where the properties with rent greater 1000 have been posted.

Answer 1

1. Here, we need property number that are advertised on newpaper houston chronical. so, we need to join the tables with newspaper names and propertyno. As evident, joining tables Newspaper and Advertisement will help us achieve the above mentioned result.

Query to list property no. advertised on houston chronical:

Select propertyNo from Advertisement inner join Newspaper where Advertisement.newspaperNo=Newspaper.newspaperNo AND Newspaper.name="Houston Chronical";

2. Here , we need names of newspaper with advertisement of property with rent greater than 1000, so need to implement subquery using the 'IN' keyword to check value among multiple possible answers. We need to select newspaper name from table newspaper where the newspaperNo is same as in table advertisement, which is further joined with the preperty table to on propertyNo only for rent greater than 1000.

Query to list newspaper with advertisement of rent more than 1000:

Select Newspaper.name from Newspaper where Newspaper.newspaperNo IN ( Select Advertisement.newspaperNo from Advertisement inner join property where Advertisement.propertyNo=property.propertyNo AND property.rent > 1000 );