Definition of SET - 2
Customer (Cust_Id, Cust_Name, Cust_Addr, Cust_City, EmailID,Contact_No)Magazine (Mag_Id, Mag_Name, Unit_Rate, Type_of_subsciption[weekly, monthly, etc.])
Subscription (Cust_Id, Mag_Id, start_date, end_date)
A)
- Create a View that displays Customer name, Magazine name along with its rate
which was subscribed during 01-Sept-2010 to 01-Feb-2011. - Find top three magazines having the highest sale during last one month of time.
- Create a function to return No. of customers in city Gandhinagar who have subscribed
the magazine ‘Outlook’ after August 2010. If no such customer exists, throw a user
defined exception with appropriate message. - Create a trigger that is fired after an INSERT statement is executed for the Customer
table. The trigger writes the new customer’s code, name and the sysdate in a table
called Customer_Log.(create the table Customer_Log)
TABLE CREATION
SQL> create table customer
2 (cust_id number(2) primary key,
3 cust_name varchar2(20),
4 cust_adrr varchar2(20),
5 cust_city varchar2(20),
6 email_id varchar2(20),
7 contactno number(10));
Table created.
SQL> create table magazine
2 (
3 mag_id number(4) primary key,
4 mag_name varchar2(20),
5 unit_rate number(2),
6 type_of_subscription varchar2(20) CHECK(type_of_subscription IN ('weekly','monthly'))
7 );
Table created.
SQL> create table subscription
2 (cust_id number(2) references customer(cust_id),
3 mag_id number(4) references magazine(mag_id),
4 start_date date,
5 end_date date
6 );
Table created.
INSERTION IN TABLES
SQL> insert into customer values(1,'jack','8, main road','brazil','jack@ymail.com',9016511111);
1 row created.
SQL> insert into customer values(2,'mikki','8, victoria road','paris','mikki@gmail.com',9016511221);
1 row created.
SQL> insert into customer values(3,'jenny','8, dream arcade','sydny','jenny@gmail.com',9016599999);
1 row created.
SQL> insert into customer values(4,'sam','18, drangon apart','shanghai','sam@gmail.com',9016522222);
1 row created.
SQL> insert into magazine values(101,'Filmfare',95,'monthly');
1 row created.
SQL> insert into magazine values(103,'HBM',85,'weekly');
1 row created.
SQL> insert into magazine values(102,'fog',35,'monthly');
1 row created.
SQL> insert into magazine values(104,'SBM',55,'weekly');
1 row created.
SQL> insert into subscription values(1,101,'12-JAN-12','11-NOV-12');
1 row created.
SQL> insert into subscription values(2,101,'16-MAR-12','11-NOV-12');
1 row created.
SQL> insert into subscription values(3,103,'12-JAN-12','11-NOV-12');
1 row created.
SQL> insert into subscription values(1,102,'12-APR-12','11-NOV-12');
1 row created.
SQL> insert into subscription values(4,103,'12-MAY-12','11-APR-13');
1 row created.
TABLES DISPLAY
SQL> select * from customer;
CUST_ID CUST_NAME CUST_ADRR CUST_CITY EMAIL_ID CONTACTNO
---------- -------------------- -------------------- -------------------- -------------------- ----------1 jack 8, main road brazil jack@ymail.com 9016511111
2 mikki 8, victoria road paris mikki@gmail.com 9016511221
3 jenny 8, dream arcade sydny jenny@gmail.com 9016599999
4 sam 18, drangon apart shanghai sam@gmail.com 9016522222
SQL> select * from magazine;
MAG_ID MAG_NAME UNIT_RATE TYPE_OF_SUBSCRIPTION
---------- -------------------- ---------- --------------------
101 Filmfare 95 monthly
103 HBM 85 weekly
102 fog 35 monthly
104 SBM 55 weekly
SQL> select * from subscription;
CUST_ID MAG_ID START_DAT END_DATE
---------- ---------- --------- ---------
1 101 12-JAN-12 11-NOV-12
2 101 16-MAR-12 11-NOV-12
3 103 12-JAN-12 11-NOV-12
1 102 12-APR-12 11-NOV-12
4 103 12-MAY-12 11-APR-13
*due to small space , output is messed up
A. [1] Create a View that displays Customer name, Magazine name along with its rate
which was subscribed during 01-Sept-2010 to 01-Feb-2011.
SQL> create view vw2 as select cust_name,mag_name,unit_rate from customer c,magazine m,subscription s
2 where c.cust_id=s.cust_id and m.mag_id=s.mag_id and start_date>to_date('01-SEP-11','DD-MON-YY') and start_date<to_date('01-FEB-12','DD-MON-YY');
View created.
SQL> select * from vw2;
CUST_NAME MAG_NAME UNIT_RATE
-------------------- -------------------- ----------
jack Filmfare 95
jenny HBM 85
A. [2] Find top three magazines having the highest sale during last one month of time.
SQL> select s.mag_id,m.mag_name from subscription s,magazine m where s.mag_id=m.mag_id and to_char(start_date,'mm') = to_char(sysdate,'mm') -1 and to_char (start_date,'yy') = to_char (sysdate,'yy') and rownum<4;
MAG_ID MAG_NAME
---------- --------------------
102 fog
B. [1] Create a function to return No. of customers in city Gandhinagar who have subscribed
the magazine ‘Outlook’ after August 2010. If no such customer exists, throw a user
defined exception with appropriate message.
SQL> create or replace function returncust return number is
2 totcust number(3);
3 begin
4 select count(cust_name) into totcust from customer c,magazine m,subscription s where
5 c.cust_id=s.cust_id and m.mag_id=s.mag_id and cust_city='paris' and mag_name='Filmfare'
6 and to_char(start_date,'mon')>'aug' and to_char(start_date,'yyyy')>2010;
7 return(totcust);
8 exception
9 when no_data_found then
10 dbms_output.put_line('no cutomer exists');
11 end;
12 /
Function created.
SQL> declare
2 num number(3);
3 begin
4 num:=returncust;
5 dbms_output.put_line('total customer : '||to_char(num));
6 end;
7 /
total customer : 1
PL/SQL procedure successfully completed.
B. [2] Create a trigger that is fired after an INSERT statement is executed for the Customer
table. The trigger writes the new customer’s code, name and the sysdate in a table
called Customer_Log.(create the table Customer_Log)
SQL> create table customer_log*due to small space , output is messed up
2 (cust_code number(2),
3 name varchar2(20),
4 date1 date);
Table created.
// Trigger
SQL> create or replace trigger logcust
2 after insert on customer
3 for each row
4 begin
5
6 insert into customer_log(cust_code,name,date1)values(:new.cust_id,:new.cust_name,sysdate);
7 end;
8 /
Trigger created.
SQL> insert into customer values(5,'sss','18, drangon apart','nokia','sss@gmail.com',9016522224);
1 row created.
SQL> select * from customer_log;
CUST_CODE NAME DATE1
---------- -------------------- ---------
5 sss 14-MAY-12
*this program is only for reference purpose, actual logic may differ from programmer to programmer
0 comments :