Question

A. Consider the following relations and relationship:

JobOpening (jobNo, positon, salary, requirement, contactPerson)

NewsPaper(newsPaperNo, name, street, city, zipCode, phoneNo)

       Posting(jobNo, newsPaperNo, date, cost)

1. List all the positions that are posted on January 01, 2016

2. List all the positions that have never been posted

3. List the names of all the newspapers where the positions “database administrator has been posted

Answer 1

1. Select position from JobOpening J, Posting P where J.jobNo=P.jobNo and P.date='2016-01-01';

To display the positions with respect to date, we need to join JobOpening and Posting table on jobNo column.

Then in addition to that we need to mention the given specific date as shown in the above answer query.

2. Select position from JobOpening J, Posting P where J.jobNo=P.jobNo and P.date Is Null;

to display postings that have never been posted, again we need to join JobOpening and Posting table on jobNo column. Since we are concerned with not posted postings i.e., date is null, we used Is Null constraint on date column.

3. Select name from NewsPaper N, JobOpening J, Posting P where (N.newsPaperNo= P.newsPaperNo) and (J.jobNo=P.jobNo) and (J.position=' database administrator') and p.date Is Not Null;

Since we are concerned with names of all the newspapers where the positions “database administrator has been posted, we need to join all the three different tables on relevant columns and with specific mention of position and date being not null in the constraint using where clause with multiple and clause.

date is not null as it is the date on which Person posted as database administrator.