Definition of SET - 14
Patient (Patient_code, Patient_name, Address, City, DOB, Gender)Implement the following:
A)
- Create a Sequence that can be used to enter new Patient code into the Patient table.
Add a new record into Patient table using the created sequence using the format
‘PT1001’. - Find those patients who do not belong to ‘Ahmedabad’ or ‘Mehsana’ city. (Use set
operator)
- Create a PL/SQL block to generate the following report:
Gender (Patients in Age group)
1-20 21-40 41-60 61-80 81-100 Total
Male
Female
Total
TABLE CREATION
SQL> create table patient14
2 (
3 patient_code varchar2(10) primary key,
4 patient_name varchar2(20),
5 address varchar2(20),
6 city varchar2(10),
7 DOB date,
8 gender varchar2(1)
9 );
Table created.
INSERTION IN TABLES
*create sequence first ,see A[1] query, then insert data by using sequenceSQL> insert into patient14
2 values('PT'||to_char(sq14.nextval),'jasmin','14, red bunglow','kalol','12-FEB-88','M');
1 row created.
SQL> insert into patient14
2 values('PT'||to_char(sq14.nextval),'jas','14, red bunglow','kalol','13-MAR-88','F');
1 row created.
SQL> insert into patient14
2 values('PT'||to_char(sq14.nextval),'jasica','14, White hous','mehsana','13-MAR-88','F');
1 row created.
SQL> insert into patient14
2 values('PT'||to_char(sq14.nextval),'jonny','14, red
3 bunglow','ahmedabad','13-MAR-88','M');
1 row created.
TABLES DISPLAY
SQL> select * from patient14;
PATIENT_CO PATIENT_NAME ADDRESS CITY DOB G
---------- -------------------- -------------------- ---------- --------- -
PT1001 jasmin 14, red bunglow kalol 12-FEB-88 M
PT1002 jas 14, red bunglow kalol 13-MAR-88 F
PT1021 jasica 14, White hous mehsana 13-MAR-88 F
PT1022 jonny 14, red ahmedabad 13-MAR-88 M
A. [1] Create a Sequence that can be used to enter new Patient code into the Patient table.
Add a new record into Patient table using the created sequence using the format
‘PT1001’.
SQL> create sequence sq14
2 INCREMENT By 1
3 START WITH 1001
4 MAXVALUE 9999
5 NOCYCLE;
Sequence created.
A. [2] Find those patients who do not belong to ‘Ahmedabad’ or ‘Mehsana’ city. (Use set
operator)
*due to small space , output is messed upSQL> select * from patient14
2 MINUS
3 select * from patient14 where city IN ('ahmedabad','mehsana');
PATIENT_CO PATIENT_NAME ADDRESS CITY DOB G
---------- -------------------- -------------------- ---------- --------- -
PT1001 jasmin 14, red bunglow kalol 12-FEB-88 M
PT1002 jas 14, red bunglow kalol 13-MAR-88 F
B. [1] Create a PL/SQL block to generate the following report:
Gender (Patients in Age group)
SQL> declare
2 cursor c14 is select dob,gender from patient14;
3
4 age number(10):=0;
5 d number(10):=0;
6 s number(10):=0;
7
8 mott number(10):=0;
9 mttf number(10):=0;
10 mfts number(10):=0;
11 mste number(10):=0;
12 meth number(10):=0;
13
14 fott number(10):=0;
15 fttf number(10):=0;
16 ffts number(10):=0;
17 fste number(10):=0;
18 feth number(10):=0;
19
20 mtotal number(10):=0;
21 ftotal number(10):=0;
22
23 tott number(10):=0;
24 tttf number(10):=0;
25 tfts number(10):=0;
26 tste number(10):=0;
27 teth number(10):=0;
28 total number(10):=0;
29
30 begin
31
32 for c in c14
33 LOOP
34
35 d:=to_char(c.dob,'yyyy');
36 s:=to_char(sysdate,'yyyy');
37 age:=s-d;
38
39 if c.gender='M' then
40 if age >0 and age<21 then
41 mott:=mott+1;
42 elsif age>20 and age<41 then
43 mttf:=mttf+1;
44 elsif age>40 and age<61 then
45 mfts:=mfts+1;
46 elsif age>60 and age<81 then
47 mste:=mste+1;
48 elsif age>80 and age<101 then
49 meth:=meth+1;
50 end if;
51 end if;
52
53 if c.gender='F' then
54 if age >0 and age<21 then
55 fott:=fott+1;
56 elsif age>20 and age<41 then
57 fttf:=fttf+1;
58 elsif age>40 and age<61 then
59 ffts:=ffts+1;
60 elsif age>60 and age<81 then
61 fste:=fste+1;
62 elsif age>80 and age<101 then
63 feth:=feth+1;
64 end if;
65 end if;
66
67 mtotal:=mott+mttf+mfts+mste+meth;
68 ftotal:=fott+fttf+ffts+fste+feth;
69
70 tott:=mott+fott;
71 tttf:=mttf+fttf;
72 tfts:=mfts+ffts;
73 tste:=mste+fste;
74 teth:=meth+feth;
75 total:=mtotal+ftotal;
76
77 end loop;
78
79 dbms_output.put_line(' '||'1-20'||' '||'21-40'||' '||'41-60'||' '||'61-80'||' '||'81-100'||' '||'total');
80 dbms_output.put_line('------------------------------------------------------------------------');
81 dbms_output.put_line('male'||' '||mott ||' '||mttf||' '||mfts||' '||mste||' '||meth||' '||mtotal);
82
83 dbms_output.put_line('------------------------------------------------------------------------');
84
85 dbms_output.put_line('female'||' '||fott||' '||fttf||' '||ffts||' '||fste||' '||feth||' '||ftotal);
86
87 dbms_output.put_line('------------------------------------------------------------------------');
88
89 dbms_output.put_line('total'||' '||tott||' '||tttf||' '||tfts||' '||tste||' '||teth||' '||total);
90 commit;
91 end;
92
93 /
1-20 21-40 41-60 61-80 81-100 total
------------------------------------------------------------------------
male 0 2 0 0 0 2
------------------------------------------------------------------------
female 0 2 0 0 0 2
------------------------------------------------------------------------
total 0 4 0 0 0 4
PL/SQL procedure successfully completed.
*this program is only for reference purpose, actual logic may differ from programmer to programmer
0 comments :