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)

Constest (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 the following:

1. List the login of every student along with the sum of their scores for every problem ever attempted (if student didn’t attempt any problems don’t list their login).

2. Find the name of the single hardest problem (largest max_score) ‘Rachel Moran’ ever authored.

3. Find the names of problems for which more than 2 students attempted (scored any points, zero included).

4. List the names of contests and total compensation paid to the authors for the problems used in those contests.

5. List the names of contests along with the maximum total (sum) score received by the best student for attempting problems (not necessarily all of them) in that contest.

6. For the authors who designed at least one problem for which all students who attempted it on average scored between 50% to 75% of maximum score, raise their compensation by 10%. Display the updated author table.  

Explanation / Answer

If you have any doubts, please give me comment...

-- 1)

SELECT login, pid, SUM(score)

FROM Student s, Scored sc

WHERE s.login = sc.login

GROUP BY sc.login, sc.pid;

-- 2)

SELECT pname

FROM Problem p

WHERE max_score = (

SELECT MAX(max_score)

FROM Problem, Author a

WHERE p.aid = a.aid AND aname = 'Rachel Moran'

);

-- 3)

SELECT pname

FROM Problem p, Scored s

WHERE p.pid = s.pid

GROUP BY p.pid, pname

HAVING COUNT(*)>2;

-- 4)

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;

-- 5)

SELECT cname, login, MAX(SUM(score))

FROM Scored s, Contest_Problems c

WHERE s.pid = c.pid

GROUP BY cname, login;

-- 6)

UPDATE Author SET compensation = compensation + (compensation * 0.10) WHERE aid IN (

SELECT aid

FROM Problem p, Scored s

WHERE p.pid = s.pid

GROUP BY aid

HAVING (AVG(score)/MAX(score))*100 <= 50 AND (AVG(score)/MAX(score))*100 <= 70

);

SELECT * FROM Author;

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

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