Assignment Here is a schema for POP: JOURNALS(j_num, j_name, j_subject_area, chi
ID: 3810642 • Letter: A
Question
Assignment
Here is a 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
Please formulate the following queries in SQL:
The names of all researchers from TU.
The names of all researchers from TU with an “MIS” specialty.
The names and addresses of all researchers who are chief editors.
The names of all journals with subject area = “MIS”.
The names and addresses of all researchers who have written or reviewed an article.
The titles of all articles published in “MIS Quarterly”.
The names and addresses of the area editors for “Architectural Quarterly”.
Note: We don’t have to run these in MYSQL . We should use the standard SQL conventions that we learnt in class. Please turn in on a neatly written/printed sheet of paper. The idea is to learn how to formulate queries in our heads first, without using feedback from any particular system.
[1] The chief_editor_r_ssn is the r_ssn of the researcher who is chief editor for the journal.
Explanation / Answer
The names of all researchers from TU.
Query:
Select r_name from RESEARCHERS where r_institution=”TU”
The names of all researchers from TU with an “MIS” specialty.
//Join RESEARCHERS and RRS table using r_ssn column
Query:
Select r_name from RESEARCHERS rcrs
Inner join RRS rrs
On rcrs. r_ssn = rrs. r_ssn
Where rrs.r_spec_name=”MIS”
The names and addresses of all researchers who are chief editors.
//Join RESEARCHERS and JOURNALS table using r_ssn column
Query:
Select r_name, r_address from RESEARCHERS rcrs
Inner join JOURNALS jr
On rcrs. r_ssn = jr. chief_editor_r_ssn
The names of all journals with subject area = “MIS”.
Query:
Select j_name from JOURNALS where j_subject_area=”MIS”
The names and addresses of all researchers who have written or reviewed an article.
//Join RESEARCHERS and WRITES, REVIEWS tables using r_ssn column
Query:
Select r_name, r_address from RESEARCHERS rcrs
Inner join WRITES wr
On rcrs. r_ssn = wr. r_ssn
Inner join REVIEWS jr
On rcrs. r_ssn = rs. r_ssn
The titles of all articles published in “MIS Quarterly”.
Query:
Select art_title from ARTICLES where art_area_topic= “MIS Quarterly”.
The names and addresses of the area editors for “Architectural Quarterly”.
//Join RESEARCHERS and JOURNALS table using r_ssn column
//Join ISSUES and JOURNALS
//JOIN ARTICLES and ISSUES
Query:
Select r_name, r_address from RESEARCHERS rcrs
Inner join JOURNALS jr
On rcrs. r_ssn = jr. chief_editor_r_ssn
Inner join ISSUES is
On jr. j_num=is. j_num
Inner join ARTICLES ars
On ars. i_num=is. i_num
Where art_area_topic=”Architectural Quarterly”
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.