22)Consider the following Entities and Relationships [30 Marks]
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.
Create a Database in 3NF & write queries for following.
• Display students from class 'F.Y. BCA' and participated in 'E-Rangoli ' Competition.
• Find the number of student for programming competition.
• Display the names of first three winners of each competition.
• Display average number of students participating in each competition.
• Display total number of competition held in the year 2014.
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;
/
• 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;
Search
Recent Posts
Mail Management System
Posted on 2019-07-18Online food ordering system
Posted on 2019-07-18Library Management System
Posted on 2019-07-17Health center system project
Posted on 2019-07-17Gym Management System
Posted on 2019-07-17furniture management system
Posted on 2019-07-17Electronic shop management system
Posted on 2019-07-17Automobile Workshop Management
Posted on 2019-07-17Online Visa Processing System
Posted on 2019-07-17Inventory management System
Posted on 2019-07-17petrol-management system
Posted on 2019-07-17Cloths management system
Posted on 2019-07-17Society Management system
Posted on 2019-07-17Mall management system
Posted on 2019-07-17school management system
Posted on 2019-07-17Sales Order Processing System
Posted on 2019-07-17Retail sales management
Posted on 2019-07-17Raw Materials Management
Posted on 2019-07-17railway reservation system
Posted on 2019-07-17purchase and sales management system
Posted on 2019-07-17Placement Management System
Posted on 2019-07-17Pet Shop Management System
Posted on 2019-07-17petrol pump management system
Posted on 2019-07-17Patient Information System
Posted on 2019-07-17news agency system
Posted on 2019-07-17Cinema Booking System
Posted on 2019-07-17Medical Store System
Posted on 2019-07-17leave management System
Posted on 2019-07-17Laboratory Information Management System
Posted on 2019-07-17content management system
Posted on 2019-07-17Inventory management System
Posted on 2019-07-17Institute Management System
Posted on 2019-07-17Hotel management System
Posted on 2019-07-17Gym Management System
Posted on 2019-07-17Garage Management System
Posted on 2019-07-17Furniture shop management system
Posted on 2019-07-17Fisheries management System
Posted on 2019-07-17Fertilizer scheduling system
Posted on 2019-07-17online eye care system
Posted on 2019-07-17Dental Clinic Management System
Posted on 2019-07-17Cyber Café Management
Posted on 2019-07-17Milk Billing System
Posted on 2019-07-17Colddrink management system
Posted on 2019-07-17Cable management System
Posted on 2019-07-17Beauty parlor management system
Posted on 2019-07-17Facebook Clone
Posted on 2019-05-28Dance Class Management System
Posted on 2019-05-24Library Management System
Posted on 2019-05-24Cab Management System
Posted on 2019-05-23Blood Bank Management system
Posted on 2019-05-23Beauty Parlour Management System
Posted on 2019-05-23vissa proccesing system
Posted on 2019-05-23Toll Plazza
Posted on 2019-05-23BILLING APPLICATION
Posted on 2019-05-23FLORICULTURE MANAGEMENT SYSTEM
Posted on 2019-05-23Car On Rent
Posted on 2019-05-23E-commers Shop
Posted on 2019-05-23