DBMS Practical Slips

11.Student_Competition

create table student10
(
stud_reg_no number(5)primary key,
stud_name varchar(20),
class varchar(20)
);

create table competition20
(
cno number(5)primary key,
cname varchar(20),
ctype varchar(20)
);

create table student_competition30
(
stud_reg_no number(5)references student10(stud_reg_no),
cno number(5)references  competition20(cno),
rank number(10),
year number(10)
);

insert into student10 values(101,'hitesh','fybca');
insert into student10 values(102,'ankit','sybca');
insert into student10 values(103,'rohit','tybca');
insert into student10 values(104,'laxman','fybca');

insert into competition20 values(11,'e-rangoli','culture');
insert into  competition20 values(12,'programming','knowledge');
insert into  competition20 values(13,'e-rangoli','culture');
insert into  competition20 values(14,'programming','knowledge');

insert into student_competition30 values(101,11,1,'2014');
insert into student_competition30 values(102,12,1,'2013');
insert into student_competition30 values(103,13,1,'2014');
insert into student_competition30 values(104,14,2,'2013');

1. select stud_name from  student10,competition20,student_competition30
where student10.stud_reg_no= student_competition30.stud_reg_no and
competition20.cno= student_competition30.cno and
student10.class='fybca' and
cname='e-rangoli';

STUD_NAME
--------------------
hitesh

2. select COUNT(student10.stud_reg_no) from student10,competition20, student_competition30
where student10.stud_reg_no= student_competition30.stud_reg_no and
competition20.cno= student_competition30.cno and
competition20.cname='programming';

COUNT(student10.STUD_REG_NO)
-----------------------------------------------------
                                                                    2

3. select student_competition30.rank from student10,competition20,student_competition30
where student10.stud_reg_no=student_competition30.stud_reg_no and
 competition20.cno=student_competition30.cno order by cname;

      RANK
-------------
         1
         1
         2
         1

4. select AVG( student10.stud_reg_no) from student10,competition20,student_competition30
group by cname;

AVG(STUDENT10.STUD_REG_NO)
------------------------------------------------
                                                        102.5
                                                        102.5

5. select COUNT(competition20.cno) from student10,competition20,student_competition30
where student10.stud_reg_no=student_competition30.stud_reg_no and
 competition20.cno=student_competition30.cno and
student_competition30.year='2014';

COUNT(COMPETITION20.CNO)
---------------------------------------------
                                                           2