Question

1. SQL (32 marks) This question uses the Research database available in Oracle and as a build file for use in SQLite.

The ER model for the Academics database is as follows: The Relational model for the Academics database is as follows:

DEPARTMENT( deptnum , descrip, instname, deptname, state, postcode)

ACADEMIC( acnum , deptnum*, famname, givename, initials, title)

PAPER( panum , title) AUTHOR( panum*, acnum* )

FIELD( fieldnum , id, title)

INTEREST( fieldnum*, acnum* , descrip)

Write one SQL query for each question below to extract information from the database. Do not supply the output of the query. Only the SQL query is required for each question. All SQL must be presented in text format, i.e. no screenshots.

1.5. There is concern about the integrity of the data in the Academics table. Are there any academics in the database who are missing Initials or a Title? Write a query to list the academic number of any academics that do not have initials or a title. Do not use a sub-query.

1.6. There are two requirements for this question:

1. Find all academics who have an interest in the field titled “Natural Language Processing”. You must use an EXISTS sub-query. Output the initials of these academics. HINT : Don’t forget to use TRIM when testing the title.

2. Further limit this list of academics by adding a NOT EXISTS condition test so only academics who have no other interests other than “Natural Language Processing” are present in the output.

1.7.What does the following query do? Describe what its purpose is using only plain English. Avoid use of SQL keywords. You may refer to Question 1 for help regarding postcodes.

select distinct p1.title from paper P1, author A1 where P1.panum=A1.panum and acnum in (select acnum from academic where title like 'Dr%' or title like 'Prof%') and exists (select * from academic AC1 where AC1.deptnum in (select deptnum FROM department WHERE postcode >=4000 and postcode <= 4999) And AC1.acnum=A1.acnum);

1.8. Write an SQL query to create a View that displays the title and surname of each academic and how many papers they have written. If an academic have not written any papers, then a “0” should be displayed against their name.

Answer 1

1.5 ) select acnum from ACADEMIC where nvl(initials,'') = '' or nvl(title,'') = ''

1.6
1) select a.acnum , a.famname from ACADEMIC a,INTEREST i where a.acnum = i.acnum and EXISTS(select 1 from FIELD f where i.fieldnum = f.fieldnum and f.title=TRIM('Natural Language Processing'));

2)
select a.acnum , a.famname from ACADEMIC a,INTEREST i where a.acnum = i.acnum
and EXISTS(select 1 from FIELD f where i.fieldnum = f.fieldnum and f.title=TRIM('Natural Language Processing'))
and NOT EXISTS(select 1 from FIELD f where i.fieldnum = f.fieldnum and f.title = TRIM('Natural Language Processing'));
1.7
select distinct p1.title from paper P1, author A1 where P1.panum=A1.panum
and acnum in (select acnum from academic where title like 'Dr%' or title like 'Prof%') and
exists (select * from academic AC1 where AC1.deptnum in (select deptnum FROM department WHERE postcode >=4000 and postcode <= 4999)
And AC1.acnum=A1.acnum);

list all title of the paper whose auther's title start with 'Dr' or 'Prof' and department of the other resides in postal code between 4000 and 4999
1.8)
select distinct ac.title, ac.givename, (select count(1) from AUTHOR au where au.acnum=ac.acnum) as papercount from ACADEMIC ac ;