Question

The schema for the Academics database is as follows. Understanding this schema is necessary to answer the questions in Part B.

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) 

The semantics of most attributes are self-explanatory. For each relation, the primary key is underlined and any foreign keys are denoted by an asterisk (*). Some additional information for relations is given below:

• DEPARTMENT: Each academic department (deptnum) belongs to an institution (instname).  
• ACADEMIC: Each academic (acnum) belongs to one department (deptnum).  

• AUTHOR: A paper (panum) may be written by more than one academic (acnum).  
• FIELD: The relation describes research areas, where id is a classification code like B.1.1.  
• INTEREST: The relation describes that an academic may have research interests in several fields (fieldnum), and provide description (descrip) of his/her research.

Question1 SQL

calculate the number of academics that have more than 10 papers?

Question 2 SQL

find the departments where at most 10 academics have more than 2 research fields, and display in alphabetical order the names of institutions and names of departments?

Answer 1

Schema of table:-

CREATE TABLE DEPARTMENT(deptnum int primary key, descrip text, instname varchar(30), deptname varchar(20), state varchar(15), postcode number);

CREATE TABLE ACADEMIC(acnum int primary key, deptnum int not null, famname varchar(15), givename varchar(15), initials text, title varchar(15),FOREIGN KEY (deptnum) REFERENCES DEPARTMENT(deptnum));

CREATE TABLE PAPER(panum int primary key , title varchar(15));

CREATE TABLE AUTHOR(panum int not null, acnum int not null,FOREIGN KEY (panum) REFERENCES PAPER(panum),FOREIGN KEY (acnum) REFERENCES ACADEMIC(acnum));

CREATE TABLE FIELD(fieldnum int primary key, id int not null, title text);

CREATE TABLE INTEREST(fieldnum int not null, acnum int not null, descrip text,FOREIGN KEY (fieldnum) REFERENCES FIELD(fieldnum),FOREIGN KEY (acnum) REFERENCES ACADEMIC(acnum));

Insert sample value as we required to show sample output:-

insert into ACADEMIC values(101,8,null,null,null,null);
insert into AUTHOR values(11,101);

insert into INTEREST values(5,101,null);
insert into DEPARTMENT values(8,null,"IIT","Chemical",null,null);

Question1:- calculate the number of academics that have more than 10 papers?

Answer: - select * from ACADEMIC as A,AUTHOR as B where A.acnum==B.acnum and panum>10;

Question2: - find the departments where at most 10 academics have more than 2 research fields, and display in alphabetical order the names of institutions and names of departments?

Answer: - select instname,deptname,fieldnum from DEPARTMENT as D,ACADEMIC as A,INTEREST as I where D.deptnum==A.deptnum and A.acnum==I.acnum and fieldnum>2 order by instname asc limit 10;