[GTU] SQL & PL/SQL SET - 2

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)

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.
2. Find top three magazines having the highest sale during last one month of time.

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.
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)
   
   
   

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
  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

*due to small space , output is messed up

---

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