Problem 2: Given a Relational Database (designed to store data about students co

Question

Problem 2: Given a Relational Database (designed to store data about students competing in the programing contests) that consists of the following relations 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) Use SQL statements to create the relations. b) Populate the relations with using SQL statements with the given data posted on the class website c) Implement the SQL queries for the following 1. Display all the data you store in the database to verify that you have populated the relations correctly. 2. Display the names of students from 'University of Oklahoma' and their graduation year 3. List the name of every problem and the contest they were featured at (if problem was not featured in any contest don't list it) 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) Find the name of the single hardest problem (largest max score) 'Rachel Moran' ever authored. Find the names of problems for which more than 2 students attempted (scored any points, zero included) List the names of contests and total compensation paid to the authors for the problems used in those contests. 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. 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. 4. 5. 6. 7. 8. 9. 10. Delete students graduating in 2018. Display the updated students table. You will need to create a SQL file to store your SQL statements. This SQL file must have sql as its extension. You nmst also use Azure Potal or SQL Operations Studio to collect cropped screenshots of your query outputs and compile them into a single PDF ile.

Explanation / Answer

--As per chegg rules we can only solve 5-6 sub question please post other questions in another posr

--1
SELECT * FROM Student;
Select * From CONStest;
SELECT * From Paricipated;
SELECT * From Author;
SELECT * From Problem;
SELECT * From Scored;
SELECT * From CONStest_Problem;
--2
SELECT sname FROM Student WHERE university ='University of Oklahoma';
--3
SELECT pname FROM problem INNER JOIN CONStest_Problem ON problem.pid=CONStest_Problem.pid;
--4
SELECT st.login,SUM(s.scored) FROM Student st INNER JOIN Scored s ON st.login=s.login
GROUP by st.login;
--5
SELECT pname FROM Problem p INNER JOIN Author a ON p.aid=a.aid
WHERE a.aname='Rachel Moran' and max_score= (SELECT max(max_score)
FROM Author WHERE aname='Rachel Moran' )
--6
SELECT p.pname FROM Student st LEFT JOIN Scored s ON st.login=s.login
INNER JOIN Problem p ON s.pid=p.pid
GROUP by p.pname having count(p.pid)>2;

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

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