Given the following SQL tables: Student (login: varchar, sname: varchar, univers

Question

Given the following SQL tables: Student (login: varchar, sname: varchar, university: varchar, grad_year: int) Contest (cname: varchar, year: int, location: varchar) Participated (login, cname) Author (aid: int, aname: varchar, compensation: int) Problem (pid: int, pname: varchar, max_score: int, aid) Scored (pid, login, score: real) Contest_Problems (cname, pid) Do the following using Azure SQL: a) Implement the SQL queries for For the authors who designed at least one problem for which all students who attempted it, on average scored between 50% and 75% of maximum score, raise the author's compensation by 10%. Display the updates Author table. NOTE: Author's compensation should be raised only ONCE, not per instance of students scoring between 50% and 75% of maximum score.

Explanation / Answer

Solution :-

SQL query for For the authors who designed at least one problem for which all students who attempted it, on average scored between 50% and 75% of maximum score, raise the author's compensation by 10% is

SELECT cname, SUM(compensations)

FROM Contest_Problems c, Problem p, Author a

WHERE c.pid = p.pid AND p.aid = a.id

GROUP BY cname;

Please upvote my answer :)

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

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