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)
- Find those participants who have registered both for ‘Dancing’ and ‘Painting’ (Note:
Use set operator). - Find the average score, scored in each competition event.
- 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;*due to small space , output is messed up
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.
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*due to small space , output is messed up
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.
*this program is only for reference purpose, actual logic may differ from programmer to programmer
0 comments :