I need help generating these queries in SQL, Thank you. 1. List by last name and

Question

I need help generating these queries in SQL, Thank you.

1. List by last name and first name all writers who are also editors.

2. List the article title of all unpublished articles over 2000 words (Hint: articles not listed in the Published table).

3. List the article types that are not associated with any articles.

4. Show the average number of articles written by a writer.

5. Display the title, type and length of any business articles with an article length greater than average article length for all business articles. Sort the results in descending order by title.

6. Show the percentage for each type of article in the articles tables. Sort the results in ascending order by article type.

7. For each article type display the article title, type and length of the shortest article. Sort the results in ascending order by article length.

8. For the magazine Total Sport provide the yearly number and the cumulative number of articles published.

9. Show to how many distinct writers have written business article.

10. Show the two most recent articles published by each magazine. Sort the results first by magazine then by most recent issue date.

ArticleTypes AT_Description ART_ID ART Title ART_Type Published ART_ID MAG_ID Issue WRT-ID WRT ID MAG Name Writers WRT_ID WRT_LastName WRT_FirstName WRT_State WRT_Phone WRT LastContactDate WRT Freelancer WorksFor ED_ID MAG ID Editors ED_ID ED LastName ED FirstName ED_Phone

Explanation / Answer

1.

If there is a relationship between Writers, Editors, then the below query will work

SELECT WRT_LastName,WRT_FirstName FROM Writers, Editors

WHERE Writers.WRT_ID=Editors.ED_ID;

                                                        or

SELECT WRT_LastName,WRT_FirstName from Writers where WRT_ID in

(SELECT WRT_ID FROM Articles where ART_ID in

(SELECT ART_ID FROM Published where MAG_ID in

(SELECT MAG_ID FROM Magazines where MAG_ID in

(SELECT MAG_ID FROM WorksFor where ED_ID in

(SELECT ED_ID FROM Editors);

2.

SELECT ART_TITLE FROM Articles where ART_ID not in (select ART_ID from Published) and ART_Length>2000;

3.

SELECT AT_TYPE from Article Types where AT_TYPE not in (select DISTINCT ART_TYPE FROM Articles);

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.