[GTU] SQL & PL/SQL SET - 1

Definition of SET - 1

Competition (Comp_code, Comp_name (Dancing, Painting, GK, etc.) )
Participants (Part_no, Part_name, DOB, Address, EmailID, Contact_number )
Scorecard (Part_no, Comp_code, Judge_no , Marks)Implement the following:
A)

1. Find those participants who have registered both for ‘Dancing’ and ‘Painting’ (Note:
   Use set operator).
2. Find the average score, scored in each competition event.

B)

1. Create a PL/ SQL block to prepare report in following format.
   Display the score card in the following format, for the Participant whose
   ID/ Name should be provided by the user.
   
   
   Talent Winner 2011 ::: <Participant’s Name>
   Competition name Judge1 Judge2 Judge3
   --------------------------------------------------------------------------------------------
   1. Painting
   2. Dancing
   --------------------------------------------------------------------------------------------
   Total Marks: _______
   --------------------------------------------------------------------------------------------
   
   
   

TABLE CREATION

SQL> create table competition
  2  (
  3  comp_code varchar2(4) primary key,
  4  comp_name varchar2(20)
  5  );

Table created.

SQL> create table participants
  2   (
  3   part_no number(2) primary key,
  4   part_name varchar2(10),
  5   dob date DEFAULT sysdate,
  6   address varchar2(20),
  7   email varchar2(20) UNIQUE,
  8   contact_number number(10) CHECK(LENGTH(contact_number)=10)
  9   );

Table created.

SQL> create table score_card
  2  (
  3  part_no number(2) REFERENCES participants(part_no) ON DELETE CASCADE,
  4  comp_code varchar2(4) REFERENCES competition(comp_code) ON DELETE CASCADE,
  5  judge_no number(4),
  6   marks number(3)
  7  );

Table created.

INSERTION IN TABLES

SQL> insert into competition values('C001','dancing');

1 row created.

SQL> insert into competition values('C002','painting');

1 row created.

SQL> insert into competition values('C003','gk');

1 row created.

SQL> insert into competition  values('C004','singing');

1 row created.


SQL> insert into participants values(11,'chetan','12-MAR-1990','kalol','chetan@yahoo.com',9871234567);

1 row created.

SQL> insert into participants values(12,'ramesh','18-JUN-1985','baroda','ramesh@myself.com',8001253455);

1 row created.

SQL> insert into participants  values(13,'suresh','18-MAR-1986','puna','suri@mail.com',9842042042);

1 row created.

SQL> insert into participants   values(14,'johny','29-FEB-1992','delhi','jihny@hotmail.com',9876543210);

1 row created.


SQL> insert into score_card   values(11,'C001',1,88);

1 row created.

SQL> insert into score_card   values(11,'C001',2,78);

1 row created.

SQL> insert into score_card   values(11,'C001',3,68);

1 row created.

SQL> insert into score_card   values(11,'C002',1,58);

1 row created.

SQL> insert into score_card   values(11,'C002',2,58);

1 row created.

SQL> insert into score_card   values(11,'C002',3,68);

1 row created.

SQL> insert into score_card values(13,'C004',24,75);

1 row created.

SQL> insert into score_card  values(12,'C003',23,65);

1 row created.

TABLES DISPLAY

SQL> select * from competition;

COMP COMP_NAME
---- --------------------
C001 dancing
C002 painting
C003 gk
C004 singing


SQL>  select * from participants;

   PART_NO PART_NAME  DOB       ADDRESS              EMAIL                CONTACT_NUMBER
---------- ---------- --------- -------------------- -------------------- --------------
        11 chetan     12-MAR-90 kalol                chetan@yahoo.com         9871234567
        12 ramesh     18-JUN-85 baroda               ramesh@myself.com        8001253455
        13 suresh     18-MAR-86 puna                 suri@mail.com            9842042042
        14 johny      29-FEB-92 delhi                jihny@hotmail.com        9876543210

SQL> select * from score_card;

   PART_NO COMP   JUGDE_NO      MARKS
---------- ---- ---------- ----------
        11 C001          1         88
        11 C001          2         78
        11 C001          3         68
        11 C002          1         58
        11 C002          2         58
        11 C002          3         68
        13 C004         24         75
        12 C003         23         65

8 rows selected.

*due to small space , output is messed up

A. [1]    Find those participants who have registered both for ‘Dancing’ and ‘Painting’ (Note:
Use set operator).

SQL> select distinct part_name from participants p,score_card s,competition c where p.part_no=s.part_no and
  2  s.comp_code=c.comp_code and comp_name='dancing'
  3  union
  4  select distinct part_name from participants p,score_card s,competition c where p.part_no=s.part_no and
  5  s.comp_code=c.comp_code and comp_name='painting';

PART_NAME
----------
chetan

A. [2]    Find the average score, scored in each competition event.

SQL> select comp_code , avg(marks) from score_card group by comp_code;

COMP AVG(MARKS)
---- ----------
C001         78
C002 61.3333333
C003         65
C004         75

B. [1]     Create a PL/ SQL block to prepare report in following format.
Display the score card in the following format, for the Participant whose
ID/ Name should be provided by the user.

SQL> declare
  2   cursor c1 IS SELECT * from competition where comp_name = 'dancing' or comp_name = 'painting';
  3
  4   pid number(2);
  5   pname varchar2(10);
  6
  7   dj1 number(3);
  8   dj2 number(3);
  9   dj3 number(3);
 10   pj1 number(3);
 11   pj2 number(3);
 12   pj3 number(3);
 13
 14   total number(3) := 0;
 15  begin
 16   pid := &pid;
 17   select part_name into pname from participants where pid = part_no;
 18 
 19   for c in c1
 20
 21   LOOP
 22 
 23    if ( c.comp_name  = 'painting') then
 24     select marks into pj1 from score_card where comp_code  = c.comp_code and judge_no = 1 and part_no= pid;
 25     select marks into pj2 from score_card where comp_code  = c.comp_code and judge_no = 2 and part_no= pid;
 26     select marks into pj3 from score_card where comp_code  = c.comp_code and judge_no = 3 and part_no= pid;
 27    elsif ( c.comp_name  = 'dancing') then
 28     select marks into dj1 from score_card where comp_code  = c.comp_code and judge_no = 1 and part_no= pid;
 29     select marks into dj2 from score_card where comp_code  = c.comp_code and judge_no = 2 and part_no= pid;
 30     select marks into dj3 from score_card where comp_code  = c.comp_code and judge_no = 3 and part_no= pid;
 31    end if;
 32
 33   END LOOP;
 34
 35
 36   total := pj1 + pj2 + pj3 + dj1 + dj2 +dj3 ;
 37
 38   dbms_output.put_line('Telent Winner 2011 ::: '||pname);
 39            dbms_output.put_line('------------------------------------------------------------------------');
 40   dbms_output.put_line('Competition name'||chr(9)||'judge1'||chr(9)||'judge2'||chr(9)||'judge3');
 41   dbms_output.put_line('------------------------------------------------------------------------');
 42 
 43   dbms_output.put_line('painting '||chr(9)||pj1 ||chr(9)||pj2 ||chr(9)||pj3 );
 44   dbms_output.put_line('dancing '||chr(9)||dj1 ||chr(9)||dj2 ||chr(9)||dj3 );
 45 
 46   dbms_output.put_line('------------------------------------------------------------------------');
 47
 48   dbms_output.put_line('TOTAL MARKS : '|| total);
 49  
 50   dbms_output.put_line('------------------------------------------------------------------------');
 51
 52
 53
 54  end;
 55  /
Enter value for pid: 11
old  16:  pid := &pid;
new  16:  pid := 11;
Telent Winner 2011 ::: chetan
------------------------------------------------------------------------
Competition name        judge1  judge2  judge3
------------------------------------------------------------------------
painting        58      58      68
dancing         88      78      68
------------------------------------------------------------------------
TOTAL MARKS : 418
------------------------------------------------------------------------

PL/SQL procedure successfully completed.

*due to small space , output is messed up

---

*this program is only for reference purpose, actual logic may differ from programmer to programmer