Slip22
Consider the following Entities and Relationships
Student (stud_reg_no, stud_name, class)
Competition (cno, cname, ctype)
Relation between Student and Competition is Many to Many
with rank and year as descriptive attribute.
Constraint: Primary key.
Solution:-
Create a Database in 3NF & write queries for following.
•Display students from class 'F.Y. BCA' and participated in 'E-Rangoli ' Competition.
SQL>Select sname,class from Student,Competition,sc where Student.sno=sc.sno and Competition.cno=sc.cno and class=’fybca’ and cname=’rangoli’;
•Find the number of student for programming competition.
SQL>Select count(Student.sno) from Student,Competition,sc where Student.sno=sc.sno and Competition.cno=sc.cno and cname=’programming’;
•Display the names of first three winners of each competition.
SQL>SELECT Student.sname FROM Student,Competition,sc where Student.sno=sc.sno and Competition.cno=sc.cno AND ROWNUM<=3;
•Display average number of students participating in each competition.
SQL>Select avg(Student.sno) ,cname from Student,Competition,sc where Student.sno=sc.sno and Competition.cno=sc.cno group by cname;
•Display total number of competition held in the year 2014.
SQL> select sum(Competition.cno)from Student,Competition,sc where Student.sno=sc.sno and Competition.cno=sc.cno and year=2014;
