Here is the schema for POP: JOURNALS(j_num, j_name, j_subject_area, chief_editor
ID: 3819802 • Letter: H
Question
Here is the schema for POP:
JOURNALS(j_num, j_name, j_subject_area, chief_editor_r_ssn[1])
chief_editor_rssn FK REF Researchers
ISSUES(j_num, i_num, i_date, i_num_pages)
j_num FK REF journals
RESEARCHERS(r_ssn, r_name, r_address, r_phone, r_institution)
ARTICLES(art_num, art_title, art_area_topic, art_abstract, j_num_submitted,date_submitted,
j_num_published, i_num_published)
j_num_submitted FK REF journals
(j_num_published,i_num) FK REF issues
AREA_EDITS(j_num, r_ssn)
j_num FK REF journals
r_ssn FK REF researchers
WRITES(r_ssn, art_num)
r_ssn FK REF researchers
art_num FK REF articles
REVIEWS(r_ssn, art_num)
r_ssn FK REF researchers
art_num FK REF articles
RESEARCH_SPECIALTIES(r_spec_name)
RRS(r_ssn, r_spec_name)
r_ssn FK REF researchers
spec_name FK REF research_specialties
Write SQL statements to answer the following queries.
a)The name of the journal with the maximum number of area-editors.
b) The number of area editors for the journal in which article number 5471 appeared.
c) The number of journals for which researcher ‘Albert Einstein’ is the chief editor.
d) The average number of articles written by researchers at TU.
e) The article area topic that has the maximum number of articles
[1] The chief_editor_r_ssn is the r_ssn of the researcher who is chief editor for the journal.
Explanation / Answer
a)The name of the journal with the maximum number of area-editors.
select J.j_name from JOURNALS J,AREA_EDITS A where max(A.r_ssn) and J.j_num=A.j_num;
b)number of area editors for the journal in which article number 5471 appeared
select count(r_ssn) from JOURNALS J,AREA_EDITS A,Articles AR where J.j_num=A.j_num and J.j_num=AR.j_num_published and art_num =5471;
c)The number of journals for which researcher ‘Albert Einstein’ is the chief editor?
select count(J.j_num) from JOURNALS J,RESEARCHERS R where J.r_ssn=R.r.ssn and R.r_name like ‘Albert Einstein’;
d)The average number of articles written by researchers at TU.
select count(W.art_num) from WRITES W,RESEARCHERS R where W.r_ssn=R.r.ssn and R.r_address like ‘TU’;
e)The article area topic that has the maximum number of articles?
select art_area_topic from ARTICLES where max(art_num);