QUESTION 8

Question 8
APPLICANT (aid, aname, addr, abirth_dt)
ENTRANCE_TEST (etid, etname, max_score, cut_score)
ETEST_CENTRE (etcid, location, incharge, capacity)
ETEST_DETAILS (aid, etid, etcid, etest_dt, score)

CREATE TABLE

Application:

create table applicant

(

      aid varchar2(5) primary key,

      aname varchar2(20),

      addr varchar2(20),

      abirth_dt date

);

Entrance_Test

create table entrance_test

(

      etid number(10) primary key,

      etname varchar2(20),

      max_score number(10),

      cut_score number(10)

);

Etest_Centre

create table etest_centre

(

      etcid number(10) primary key,

      location varchar2(15),

      incharge varchar2(10),

      capacity number(10)

);

Etest_Details

create table etest_details

(

      aid varchar2(5) references applicant(aid),

      etid number(10) references entrance_test(etid),

      etcid number(10) references etest_centre(etcid),

      etest_dt date check(to_char(etest_dt,’d’)<>1),

      score number(10),

      primary key(aid,etid,etcid)

);

INSERT RECORDS

 -- Insert Into Application

insert into applicant values('A001','pankaj','bglore','25-jan-1988');

insert into applicant values('A002','sumit','pune','07-jun-1989');

insert into applicant values('A003','jigee','kolkata','12-apr-1990');

insert into applicant values('A004','monica','madras','02-jul-1988');

insert into applicant values('A005','parul','rajkot','01-aug-1989');

insert into applicant values('A006','geeta','mumbai','03-dec1988');

insert into applicant values('A007','sweta','surat','02-mar-1991');

insert into applicant values('A008','ami','anand','11-oct-1991');

-- Insert Into Entrace_Test

insert into entrance_test values(01,'GCET',400,150);

insert into entrance_test values(02,'AIEEE',200,100);

insert into entrance_test values(03,'NIIT',200,130);

insert into entrance_test values(04,'PMT',500,200);

insert into entrance_test values(05,'NDA',100,50);

insert into entrance_test values(06,'PABT',150,75);

-- Insert Into Etest_Centre

insert into etest_centre values(01,'JAMNAGAR','SWETAmam',120);

insert into etest_centre values(02,'PUNE','supriyamam',60);

insert into etest_centre values(03,'BGLORE','nilesh',120);

insert into etest_centre values(04,'DELHI','vikram',100);

insert into etest_centre values(05,'SURAT','aartimam',50);

insert into etest_centre values(06,'MEHSANA','vipulsir',200);

insert into etest_centre values(07,'RAJKOT','tyagisir',150);

-- Insert Into Etest_Details

insert into etest_details values('A001',02,03,'25-dec-2010',150);

insert into etest_details values('A002',03,06,'04-mar-2011',120);

insert into etest_details values('A002',02,02,'29-jan-2010',120);

insert into etest_details values('A004',04,04,'05-dec-2010',114);

insert into etest_details values('A002',05,07,'02-jan-2011',99);

insert into etest_details values('A006',04,04,'21-apr-2011',300);

insert into etest_details values('A006',06,02,'20-feb-2011',76);

(This database is for a common entrance test which is being conducted at a number of
centers and can be taken by an applicant on any day except holidays)

1. Modify the APPLICANT table so that every applicant id has an ‘A’ before its value. E.g. if value is ‘1123’, it should become ‘A1123’.
   
   alter table applicant add check (aid like 'A%');
   
2. Display test center details where no tests were conducted.
   
   select * from etest_centre where etcid not in (select etcid from etest_details group by etcid);
   
3. Display details about applicants who have the same score as that of Jaydev in ‘ORACLE FUNDAMENTALS’.
   
   select applicant.aid,applicant.aname from applicant, entrance_test,etest_detailswhere applicant.aid=etest_details.aid and score =
   (
   select score
   from etest_details ed,entrance_test et,applicant a
   where et.etname='AIEEE' and et.etid=ed.etid
   and a.aname='pankaj' and a.aid=ed.aid
   ) and entrance_test.etname='AIEEE'
   and entrance_test.etid=etest_details.etid;
   
4. Display details of applicants who appeared for all tests.
   
   select aid from etest_details where etid = all (select etid from etest_details);
   
5. Display those tests where no applicant has failed.
   
   select * from entrance_test where etid not in (select ed.etid from etest_details ed,entrance_test et
   where ed.etid=et.etid and score<cut_score);
   
6. Display details of entrance test centers which had full attendance between 1st Oct 05 and
   15th Oct 05.
   
   select etcid from etest_centre where (etcid,capacity) in (
   select etcid,count(*) from etest_details where etest_dt>'1-sep-2010' and etest_dt<'30-dec-2010' group by etcid );
   
7. Display details of the applicants who scored more than the cut score in the teststhey appeared in.
   
   select ed.etid,cut_score,score from etest_details ed, entrance_test et
   where ed.etid=et.etid and score>cut_score
   
8. Display average and maximum score test wise of tests conducted at Mumbai.
   
   select max(ed.score) "MAX", avg(ed.score) "AVG" from etest_details ed, etest_centre et where et.location='DELHI' and et.etcid=ed.etcid group by ed.etid;
   
9. Display the number of applicants who have appeared for each test, test center wise
   
   select ed.etid,ed.etcid,count(aid) from etest_details ed
   group by ed.etid,ed.etcid order by etid;
   
10. Display details about test centers where no tests have been conducted.
    
    select * from etest_centre where etcid not in (select etcid from etest_details);
    
11. For tests, which have been conducted between 2-3-04 and 23-4-04, show details of the tests as well as the test centres.
    
    select ed.etid, et.etname, et.max_score, et.cut_score, ec.etcid, ec.location, ec.incharge, ec.capacity from etest_details ed, entrance_test et, etest_centre ec
    where etest_dt>'01-dec-2010' and etest_dt<'30-dec-2010' and et.etid=ed.etid and ec.etcid=ed.etcid;
    
12. How many applicants appeared in the ‘ORACLE FUNDAMENTALS’ test at Chennai in the month of February ?
    
    select count(*) "NO"
    
    from etest_details ed, etest_centre ec, entrance_test et
    where to_char(etest_dt,'mon')='dec'
    and ec.location='DELHI' and ec.etcid=ed.etcid and et.etid=ed.etid
    and etname='PMT' group by ec.etcid;
    
13. Display details about applicants who appeared for tests in the same month as the month in which they were born.
    
    select aid,aname from applicant where aid in(
    select a.aid from applicant a,etest_details ed where to_char(a.abirth_dt,'mon')=to_char(ed.etest_dt,'mon'));
    
    
14. Display the details about APPLICANTS who have scored the highest in each test,test centre wise.
    
    select ed.etid,ed.etcid,max(score) from etest_details ed
    group by ed.etid,ed.etcid order by etid;
    
15. Design a read only view, which has details about applicants and the tests that he has appeared for.
    
    create view view1 as select a.aid,aname,et.etid,et.etname from applicant a, etest_details ed,entrance_test et where a.aid=ed.aid and et.etid=ed.etid WITH READ ONLY