SQL(Oracle)


* Create the table CUST with the fields (custno, cname, state, phone);


create table cust
(custno varchar2(5) primary key,  
 cname varchar2(20) unique,
 state varchar2(10),
 phone number(10),
constraint  st check(state in ('Gujarat','Rajasthan','MP','UP','Maharastra')));

Insert the records into the table CUST:

insert into cust values (&custno,'&cname','&state',&phone);

View all the records of table CUST

select * from cust;

Output:

CUSTNO       CNAME         STATE           PHONE         
------------        ----------         ----------------   ---------------- 
C0001           Jignesh           Gujarat           9999988888  
C0002           Dhaval            Maharashtra   9898989898  
C0003           Dipen              MP                  9895989598           
C0004           Hardik             UP                  9998989899               
C0005           Jigar                Rajasthan      9898999598  

       
*  TABLES
            
-> CUSTOMER TABLE
*****************

create table cust(custno varchar2(4) primary key constraint chk_custno
    ,cname varchar2(30) not null,state varchar2(20),
    phone number(10) );
   
-------------
-> ITEM TABLE
*************

create table item(itemno varchar2(4) primary key constraint chk_itemno
    ,itemname varchar2(30) not null,itemprice
    number(10,2) check(itemprice>0),qty_hand
    number(5) not null);

----------------
-> INVOICE TABLE
****************

create table invoice(invno varchar2(4) primary key constraint chk_invno
    ,invdate date,custno varchar2(4) references
    cust(custno));

----------------
-> INVITEM TABLE
****************

create table invitem(invno varchar2(4),itemno varchar2(4),qty number(5),
    primary key(invno,itemno),foreign key(invno) references invoice
    (invno),foreign key(itemno) references item(itemno));

**********************************************************************************************

            INSERT
               --------

-----------------
-> CUSTOMER TABLE
*****************

insert into cust values('&custno','&cname','&state','&phone');

-------------
-> ITEM TABLE
*************

insert into item values('&itemno','&itemname',&itemprice,&qty_hand);

----------------
-> INVOICE TABLE
****************

insert into invoice values('&invno','&invdate','&custno');

----------------
-> INVITEM TABLE
****************

insert into invitem values('&invno','&itemno',&qty);

**********************************************************************************************

            RECORDS OF THE TABLE
               ----------------------

-----------------
-> CUSTOMER TABLE
*****************

CUSTNO CNAME                STATE           PHONE
------ -------------------- --------------- --------
   211 Prit Patel           gujarat         73789
   212 Hima Patel           gujarat         57897
   225 Tulsi                MP              83432
   239 Amish Patel          MP             289316
   285 Sugeet Patel         UP             264585

-------------
-> ITEM TABLE
*************

ITEMNO ITEMNAME    ITEMPRICE   QTY_HAND ITEMCOLOR
------ ---------- ---------- ---------- ----------
1      Screw            2.25         50 GREY
2      Bullet            225        110 GREY
3      Bolt              390         75 GOLDEN
4      Hammer           9.99        125 BROWN
5      Washer           5.99        100 BLACK
6      Nail              .99        300 BLUE
7      Gear              100        250 RED

----------------
-> INVOICE TABLE
****************

INVNO INVDATE       CUSTNO
----- ------------- ------
    1 23-FEB-89        211
    2 30-MAY-90        212
    3 05-MAR-06        225
    4 07-SEP-08        239
    5 10-DEC-05        285

----------------
-> INVITEM TABLE
****************

INVNO ITEMNO            QTY
----- ----------------- ---
    1 4                  10
    2 3                   4
    3 6                   1
    4 5                   6
    5 7                  20

**********************************************************************************************

            QUERIES
             -------------

1) Add a column to the Item table, which will allow us to store Item color field.

--> alter table item add(itemcolor varchar2(15));
~~~~~~~
OUTPUT
~~~~~~~

Table altered.

=====================================================================

2) Display Itemname, Price in sentence from using concatenation.

--> select itemname ||' sold worth Rs.'|| itemprice from item;
~~~~~~~
OUTPUT
~~~~~~~

ITEMNAME ||'SOLDWORTHRS.'|| ITEMPRICE
-----------------------------------------------------------------
Screw sold worth Rs.2.25
Bullet sold worth Rs.225
Bolt sold worth Rs.390
Hammer sold worth Rs.9.99
Washer sold worth Rs.5.99
Nail sold worth Rs..99
Gear sold worth Rs.100

======================================================================

3) Find total value of each item based on quantity on hand.

--> select itemname,itemprice*qty_hand "Total value of each Item" from item;

~~~~~~~
OUTPUT
~~~~~~~

ITEMNAME   Total value of each Item
---------- ------------------------
Screw                         112.5
Bullet                        24750
Bolt                          29250
Hammer                      1248.75
Washer                          599
Nail                            297
Gear                          25000

=======================================================================

4) Find customer who are from state of Gujarat.

--> select cname from cust where state like 'Gujarat';
~~~~~~~
OUTPUT
~~~~~~~

CNAME
--------------------
Prit Patel
Hima Patel

=======================================================================

5) Display items with unit price of at least Rs. 100

--> select itemname,itemprice from item where itemprice>=100;

~~~~~~~
OUTPUT
~~~~~~~

ITEMNAME    ITEMPRICE
---------- ----------
Bullet            225
Bolt              390
Gear              100

=======================================================================

6) List items where range lies between Rs. 200 and Rs. 500

--> select itemname from item where itemprice between 200 and 500;

~~~~~~~
OUTPUT
~~~~~~~

ITEMNAME
---------
Bullet
Bolt

======================================================================

7) Which customers are from lalbaug area of ahmedabad, Baroda and Patan.

--> select cname from cust where city in('ahmedabad','Baroda','Patan') and area like 'lalbaug';

~~~~~~~
OUTPUT
~~~~~~~

CNAME
----------
Prit Patel
Hima patel

======================================================================

8) Find all customers whose name start with Letter ‘P’

--> select cname from cust where cname like 'P%';

~~~~~~~
OUTPUT
~~~~~~~

CNAME
--------------------
Prit Patel

======================================================================

9) Find name of Items with ‘W’ in their name.

--> select itemname from item where itemname like '%w%';

~~~~~~~
OUTPUT
~~~~~~~

ITEMNAME
----------
Screw

======================================================================

10) Sort all customers alphabetically.

--> select cname from cust order by cname;

~~~~~~~
OUTPUT
~~~~~~~

CNAME
--------------------
Amish Patel
Hima Patel
Prit Patel
Sugeet Patel
Tulsi

======================================================================

11) Sort all Items in descending order by their prices.

--> select itemname from item order by itemprice desc;

~~~~~~~
OUTPUT
~~~~~~~

ITEMNAME
----------
Bolt
Bullet
Gear
Hammer
Washer
Screw
Nail

======================================================================

12) Display all customers from M.P. alphabetically.

--> select cname from cust where state='MP' order by cname;

~~~~~~~
OUTPUT
~~~~~~~

CNAME
--------------------
Amish Patel
Tulsi

======================================================================

13) Display invoices dates in ‘September 05, 2007’ format.

--> select TO_CHAR(invdate,'MONTH DD, YYYY') from invoice;

~~~~~~~
OUTPUT
~~~~~~~

TO_CHAR(INVDATE,'M
------------------
FEBRUARY  23, 1989
MAY       30, 1990
MARCH     05, 2006
SEPTEMBER 07, 2008
DECEMBER  10, 2005

=====================================================================

14) Find total, average, highest and lowest unit price.

--> select sum(itemprice) "Total",avg(itemprice) "Average",max(itemprice) "Highest",
    min(itemprice)" Lowest" from item;

~~~~~~~
OUTPUT
~~~~~~~

Total    Average       Highest     Lowest
-------- -----------  -------- ----------
734.22   104.888571        390        .99

======================================================================

15) Count number of items ordered in each invoice.

--> select invno,count(qty) "number of item" from invitem group by invno;

~~~~~~~
OUTPUT
~~~~~~~

INVNO    number of item
------- ---------------
    1                 1
    2                 1
    3                 1
    4                 1
    5                 1

======================================================================

16) Find invoice which three or more items are ordered.

--> select distinct invno from invitem where qty>=3;

~~~~~~~
OUTPUT
~~~~~~~

INVNO
------
    1
    2
    4
    5

======================================================================

17) Find all possible combination of customers and items (use Certesian product)

--> select c.cname, i.itemname from cust c cross join item I;

~~~~~~~
OUTPUT
~~~~~~~

CNAME                ITEMNAME
-------------------- ----------
Prit Patel           Screw
Hima Patel           Screw
Tulsi                Screw
Amish Patel          Screw
Sugeet Patel         Screw
Prit Patel           Bullet
Hima Patel           Bullet
Tulsi                Bullet
Amish Patel          Bullet
Sugeet Patel         Bullet
Prit Patel           Bolt

CNAME                ITEMNAME
-------------------- ----------
Hima Patel           Bolt
Tulsi                Bolt
Amish Patel          Bolt
Sugeet Patel         Bolt
Prit Patel           Hammer
Hima Patel           Hammer
Tulsi                Hammer
Amish Patel          Hammer
Sugeet Patel         Hammer
Prit Patel           Washer
Hima Patel           Washer

CNAME                ITEMNAME
-------------------- ----------
Tulsi                Washer
Amish Patel          Washer
Sugeet Patel         Washer
Prit Patel           Nail
Hima Patel           Nail
Tulsi                Nail
Amish Patel          Nail
Sugeet Patel         Nail
Prit Patel           Gear
Hima Patel           Gear
Tulsi                Gear

CNAME                ITEMNAME
-------------------- ----------
Amish Patel          Gear
Sugeet Patel         Gear

======================================================================

18) Display all item quantity and item price for invoices ( natural join )

--> select invno,sum(qty),sum(qty*itemprice) "Item price" from invitem,item where
    invitem.itemno=item.itemno group by invno;

~~~~~~~
OUTPUT
~~~~~~~

INVNO   SUM(QTY)  Item price
------- --------- -----------
    1         10        99.9
    2          4        1560
    3          1         .99
    4          6       35.94
    5         20        2000

======================================================================

19) Find total price amount for each invoice.

--> select invno,sum(qty*itemprice) "Total price" from invitem,item where invitem.itemno=
    item.itemno group by invno;

~~~~~~~
OUTPUT
~~~~~~~

INVNO Total price
----- -----------
    1        99.9
    2        1560
    3         .99
    4       35.94
    5        2000

======================================================================

20) Use outer join to display items ordered as well as not ordered so far.

--> select invitem.itemno "Order",item.itemno "not order" from invitem,item where
    invitem.itemno(+)=item.itemno;

~~~~~~~
OUTPUT
~~~~~~~

Order      not or
---------- ------
            1
            2
 3          3
 4          4
 5          5
 6          6
 7          7

======================================================================

21) Find invoices with ‘Gear’ in their item name.


--> select invno from invitem,item where invitem.itemno=item.itemno and item.itemname='Gear';

~~~~~~~
OUTPUT
~~~~~~~

     INVNO
----------
         5

======================================================================

22) Display name of items orderd in invoice number 1001

--> select itemname from invitem,item where invitem.itemno=item.itemno and invno='in01';

~~~~~~~
OUTPUT
~~~~~~~

ITEMNAME
----------
Hammer

======================================================================

23) Find the items that are cheaper than ‘Bullet’

--> select itemname from item where itemprice<(select itemprice from item where itemname like
    'Bullet');

~~~~~~~
OUTPUT
~~~~~~~

ITEMNAME
----------
Screw
Hammer
Washer
Nail
Gear

======================================================================

24) Create a table ( namely guj_cust ) for all Gujarat customer based on existing customer table

--> create table guj_cust as select * from cust where state='Gujarat';

~~~~~~~
OUTPUT
~~~~~~~

Table created.

======================================================================

25) Copy all M.P. customers to the table with Gujarat Customers.

--> insert into guj_cust select * from cust where state='MP';

~~~~~~~
OUTPUT
~~~~~~~

2 rows created.

=====================================================================

26) Rename Guj_cust table to MP_cust table.
  
--> rename guj_cust to mp_cust

~~~~~~~
OUTPUT
~~~~~~~

Table renamed.

=======================================================================
27) Find the customers who are not in Gujarat or M.P.

-->select cname from cust where state not in('Gujarat','MP');

~~~~~~~
OUTPUT
~~~~~~~

CNAME
--------------------
Prit Patel
Hima Patel
Sugeet Patel

======================================================================

28) Find the Items with top three prices.

--> select itemname from(select itemname from item order by itemprice desc) where rownum<=3;

~~~~~~~
OUTPUT
~~~~~~~

ITEMNAME
----------
Bolt
Bullet
Gear

======================================================================

29) Find two items with lowest quantity on hand.
  
--> select itemname from(select itemname from item order by qty_hand) where rownum<=2;

~~~~~~~
OUTPUT
~~~~~~~

ITEMNAME
----------
Screw
Bolt

======================================================================

30) Create a simple view with item names and item price only.

--> create view v_dd as select itemname, itemprice from item;

~~~~~~~
OUTPUT
~~~~~~~

View created.

======================================================================

31) Create a sequence that can be used to enter new items into item table.

--> create sequence seq_14ee increment by 1 start with 011 minvalue 011 maxvalue 999;

~~~~~~~
OUTPUT
~~~~~~~

Sequence created.

======================================================================

32) Add a new Item into Item table with sequence just created.

--> insert into item values('i'||seq_14ee.nextval,'Switch',23,121,'gray');

~~~~~~~
OUTPUT
~~~~~~~

1 row created.

=====================================================================

33) Create a Index file to speed up a search based on customer table

--> create index index_14gg on cust(cname);

~~~~~~~
OUTPUT
~~~~~~~

Index created.
 
----------------------------------------------------------------------------------------------------------------------------
* TABLES
           
1) SCREEN
**************

create table screen(screen_id varchar2(3) primary key,
    location varchar2(20),
    seating_capacity number(3),constraint sid check(screen_id
    like ('S%')),constraint loc check(location in('FF','SF','TF')));

--------
2) MOVIE
********

create table movie(movie_id varchar2(3) primary key,
    movie_name varchar2(20),
    date_of_release date);

-----------
3) CURRENT1
***********

create table current1(screen_id varchar2(3) references screen(screen_id),
     movie_id varchar2(3) references movie(movie_id),
     date_of_arrival date,date_of_closure date,
     constraint dtc check(date_of_arrival<date_of_closure));

**********************************************************************************************

            INSERT
               --------                  

1) SCREEN
*********

insert into screen values('&screen_id','&location',&seating_capacity);

--------
2) MOVIE
********

insert into movie values('&movie_id','&movie_name','&date_of_release');

-----------
3) CURRENT1
***********

insert into current1 values('&screen_id','&movie_id','&date_of_arrival','&date_of_closure');

**********************************************************************************************

            RECORDS OF THE TABLE
               ----------------------

---------
1) SCREEN
*********

SCR LOCATION             SEATING_CAPACITY
--- -------------------- ----------------
S1  SF                                400
S2  TF                                350
S3  TF                                250
S4  SF                                300
S5  TF                                170

--------
2) MOVIE
********

MOV MOVIE_NAME           DATE_OF_R
--- -------------------- ---------
M01 Star Wars III        11-SEP-09
M02 DON                  10-JUL-09
M03 DDLJ                 18-FEB-05
M04 Aan                  27-SEP-02
M05 300                  25-OCT-05

-----------
3) CURRENT1
***********

SCR MOV  DATE_OF_A  DATE_OF_C
--- ---  ---------  ---------
S1  M01  13-JUL-09  26-AUG-09
S2  M03  25-APR-04  03-MAY-04
S3  M02  05-JAN-09  25-FEB-09
S4  M04  16-MAR-04  24-nov-04
S5  M05  03-MAY-05  09-JUL-05

**********************************************************************************************

            QUERIES
               ---------

1) Get the name of movie which has run the longest in the multiplex so far.

--> select movie_name from movie where movie_id in(select movie_id from current1
    where (date_of_closure - date_of_arrival) in
    (select max(date_of_closure-date_of_arrival) from current1));

~~~~~~~
OUTPUT
~~~~~~~

MOVIE_NAME
--------------------
300

======================================================================

2) Get the average duration of a movie on screen number 's4';

--> select avg(c.date_of_closure-c.date_of_arrival) "avg" from screen s,current1 c
    where c.screen_id=s.screen_id and c.screen_id='s04';

~~~~~~~
OUTPUT
~~~~~~~

       avg
----------
        35

======================================================================

3) Get the details of movie that closed on date 24-november-2004.

--> Select * from movie where movie_id in (select movie_id from current1
    where date_of_closure ='24-nov-04');

~~~~~~~
OUTPUT
~~~~~~~

MOV MOVIE_NAME           DATE_OF_R
--- -------------------- ---------
M04 Aan                  27-SEP-02

======================================================================

4) Movie 'star wars III' was released in the 7th week of 205.find out the date of its release
   considering that a movie releases only on friday.

--> Select date_of_release from movie where to_char(date_of_release,'iw')=7 and
    to_char(date_of_release,'d')=6;

~~~~~~~
OUTPUT
~~~~~~~

DATE_OF_R
---------
18-FEB-05

======================================================================

5) Get the full outer join of the relations screen and current.

--> select s.*,c.* from screen s,current1 c where s.screen_id=c.screen_id;

~~~~~~~
OUTPUT
~~~~~~~

SCR LOCATION             SEATING_CAPACITY SCR MOV  DATE_OF_A  DATE_OF_C
--- -------------------- ---------------- --- ---  --------- ---------
S1  SF                                400 S1  M01  13-JUL-09  26-AUG-09
S2  TF                                350 S2  M03  25-APR-04  03-MAY-04
S3  TF                                250 S3  M02  05-JAN-09  25-FEB-09
S4  SF                                300 S4  M04  16-MAR-04  24-NOV-04
S5  TF                                170 S5  M05  03-MAY-05  09-JUL-05

-----------------------------------------------------------------------------------------------------------------------------       
*  TABLES
           
1) WORKER
*********

create table worker(worker_id varchar2(5) primary key,
    name varchar2(20),
        wage_per_hr varchar2(5),
        specialised_in char(10),
        manager_id varchar2(5));

------
2) JOB
******

create table job(job_id char(5) primary key,
    type_of_job varchar2(10),
        status char(10));

----------------
3) JOB_ASSIGNED
****************

create table job_assigned(worker_id varchar2(5) references worker(worker_id),
    job_id char(5) references job(job_id),
        starting_date date,no_of_days int,
        primary key(worker_id,job_id));

**********************************************************************************************

            INSERT
            --------
---------
1) WORKER
*********

insertintoworker values('&worker_id','&name','&wage_per_hr','&specialised_in','&manager_id');

------
2) JOB
******

insert into job values('&job_id','&type_of_job','&status');

----------------
3) JOB_ASSIGNED
****************

insert into job_assigned values('&worker_id','&job_id','&starting_date','&no_of_days');

**********************************************************************************************

            RECORDS OF THE TABLE
               ----------------------

---------
1) WORKER
*********

WORKE NAME                 WAGE_ SPECIALISE MANAG
----- -------------------- ----- ---------- -----
w1    Mr.Cacophonix        50    fitting    m1
w2    ridham               40    marketing  m2
w3    jaimin               35    polishing  m3
w4    parth                30    polishing  m4
w5    nirdesh              55    fitting    m5

------
2) JOB
******

JOB_I TYPE_OF_JO STATUS
----- ---------- ----------
j1    packing    complete
j2    molding    incomplete
j3    editing    complete
j4    printing   incomplete
j5    accounting incomplete

----------------
3) JOB_ASSIGNED
****************

WORKE JOB_I STARTING_ NO_OF_DAYS
----- ----- --------- ----------
w1    j1    15-SEP-09         35
w2    j1    20-SEP-08         33
w3    j2    12-OCT-09         39
w2    j3    06-NOV-09         45
w4    j5    19-OCT-09         10
w5    j4    12-SEP-08         25

**********************************************************************************************
           
            QUERIES
               ---------

1) Display the date on which each worker is going to end his presently assigned job.

--> Select starting_date + no_of_days "End Date of Assigned Job" from job_assigned where           
    (starting_date + no_of_days)>=sysdate;
   
~~~~~~~
OUTPUT
~~~~~~~

End Date
---------
20-NOV-09
21-DEC-09

=====================================================================

2)Display how many days remain for each worker to finish his job.

--> select worker_id, job_id,(starting_date+ no_of_days) - Round (sysdate) "Day Remain for
    Worker” from job_assigned where (starting_date + no_of_days) >=sysdate;

~~~~~~~
OUTPUT
~~~~~~~

WORKE JOB_I Day Remain for Worker
----- ----- ---------------------
w3    j2                        2
w2    j3                       33

=====================================================================
  
3) Display the STARTING_DATE in the following format - 'The fifth day of month of October,
   2004'.

--> select to_char(starting_date,'"The" ddspth " day of month of" month "," yyyy"') from
    job_assigned;

~~~~~~~
OUTPUT
~~~~~~~

TO_CHAR(STARTING_DATE,'"THE"DDSPTH"DAYOFMONTHOF"MONTH","YYYY"')
--------------------------------------------------------------------
The fifteenth  day of month of september , 2009
The twentieth  day of month of september , 2008
The sixth  day of month of november  , 2009
The nineteenth  day of month of october   , 2009
The twelfth  day of month of september , 2008

======================================================================

4) Change the status to 'Complete' for all those jobs, which started in year 2008.

--> Update job set status = 'complete ' where job_id in (select job_id from job_assigned        
    where to_char(starting_date,'yyyy')='2008');

~~~~~~~
OUTPUT
~~~~~~~

2 rows updated.

select * from job;

JOB_I TYPE_OF_JO STATUS
----- ---------- ----------
j1    packing    complete
j2    molding    incomplete
j3    editing    complete
j4    printing   complete
j5    accounting incomplete

======================================================================

5) Display job details of all those jobs where at least 25 workers are working.

--> Select * from job where job_id in (select job_id from job_assigned group by job_id    
    having count (*)>=25);

~~~~~~~
OUTPUT
~~~~~~~

no rows selected

======================================================================

6) Display all those jobs that are completed.

--> select job_id,type_of_job from job where status='complete';

~~~~~~~
OUTPUT
~~~~~~~

JOB_I TYPE_OF_JO
----- ----------
j1    packing
j3    editing
j4    printing

======================================================================

7) Find all the jobs, which begin within the next two weeks.

--> Select * from job where job_id in (select job_id from job_assigned where starting_date
    <= (sysdate+15));

~~~~~~~
OUTPUT
~~~~~~~

JOB_I TYPE_OF_JO STATUS
----- ---------- ----------
j1    packing    complete
j2    molding    incomplete
j3    editing    complete
j5    accounting incomplete
j4    printing   complete

======================================================================

8) List all workers who have their wage per hour ten times greater than the wage of their
   managers.

--> Select name from worker w, job j, job_assigned a where w.worker_id=a. worker_id and
    j.job_id =a.job_id and j.type_of_job='Polishing';

~~~~~~~
OUTPUT
~~~~~~~

no rows selected

=====================================================================
  
9) List the no.of workers who have been assigned the job of molding.

--> select w.worker_id,w.name from worker w,job j,job_assigned js where w.worker_id=
    js.worker_id and j.job_id=js.job_id and j.type_of_job='molding';
   
~~~~~~~
OUTPUT
~~~~~~~

WORKE NAME
----- ---------------
w3    jaimin

======================================================================

10) What is the total no.of days allocated for packaging the goods for all the workers        
    together.

--> select sum(js.no_of_days) from job_assigned js ,job j where j.job_id=js.job_id and
    j.type_of_job='packing';

~~~~~~~
OUTPUT
~~~~~~~   

SUM(JS.NO_OF_DAYS)
------------------
               68

======================================================================

11) which workers receive higher than average wage per hour.

--> Select * from worker where wage_per_hr > (select avg(wage_per_hr) from worker);

~~~~~~~
OUTPUT
~~~~~~~

WORKE NAME                 WAGE_ SPECIALISE MANAG
----- -------------------- ----- ---------- -----
w1    Mr.Cacophonix        50    fitting    m1
w5    nirdesh              55    fitting    m5

======================================================================

12) Display details of workers who are working on more than one job.

--> Select * from worker where worker_id in (select worker_id from job_assigned group by
    worker_id having count (*)>1);
   
~~~~~~~
OUTPUT
~~~~~~~

WORKE NAME                 WAGE_ SPECIALISE MANAG
----- -------------------- ----- ---------- -----
w2    ridham               40    marketing  m2

======================================================================

13) Which workers having specialization in polishing start their job in Octomber?

--> Select a.worker_id, a.name from worker a, job_assigned b where a.specialised_in=
    'Polishing' and to_char (b.starting_date,'mon') ='oct';

~~~~~~~
OUTPUT
~~~~~~~

WOR NAME
-----------
w3    jaimin             
w4    parth

======================================================================

14) Display details of workers who are speciazed in the same field as that of mr.cacophonix
    or have a wage per hor more than any of the workers.

--> Select * from worker where specialised_in in (select specialised_in from worker where
    name= ‘Mr.Cacophonix’) or wage_per_hr> (select max (wage_per_hr) from worker);

~~~~~~~
OUTPUT
~~~~~~~

WORKE NAME                 WAGE_ SPECIALISE MANAG
----- -------------------- ----- ---------- -----
w1    Mr.Cacophonix        50    fitting    m1
w5    nirdesh              55    fitting    m5

=====================================================================

----------------------------------------------------------------------------------------------------------------------------

* write a PL/SQL block to check given no is odd or even.

 
   declare
   no number(5);
   begin
   no:=&number;
   if mod(no,2)=0 then
   dbms_output.put_line('even no='|| no);
   else
   dbms_output.put_line('odd no='|| no);
   end if;
   end;

* pl/sql block to list prime no from 1 to 100.


   declare
   k number(5);
   p number(5);
   begin
   for i in 2..100 loop
   p:=i;
   for j in 2..i loop
   k:=j;
   if(mod(i,j)=0) then
   goto abc;
   end if;
   end loop;
   <<abc>>
   if p=k then
   dbms_output.put_line(p);
   end if;
   end loop;
   end;

 * Write a PL/SQL block to print all even numbers from 1 to 100 using loop.

    declare
   no number(5);
   begin
   for no in 2..100 loop
   if(mod(no,2)=0) then
   dbms_output.put_line(no);
   end if;
   end loop;
   end;


* Write a PL/SQL block to take student id as an input and display information for that student.


    create table mca
    (
    sno number(5),
    sname varchar(20),
    city varchar(20) 
    );

    insert into mca
    values(&sno,'&sname','&city');

     select * from mca;

       SNO SNAME      CITY
---------- ---------- ----------
              1       jignesh     rajkot
           2   ketan        amreli
         3  dalpat        sure
         4  dinesh       jamna
         5  haresh       surat
         
    6 rows selected.


 
   declare
   s_id mca.sno%type;
   name mca.sname%type;
   address mca.city%type;
   begin
   s_id:=&srno;
   select sno,sname,address into s_id,name,address
   from mca where sno=s_id;
   dbms_output.put_line('student id:' || s_id);
   dbms_output.put_line('student name:' || name);
   dbms_output.put_line('student address:' || address);
   end;

* write a pl/sql block to reverse the given string.



   declare
   string varchar(20);
   len number(5);
   revstr varchar(20);
   begin
   string :='string';
   len :=length(string);
   dbms_output.put_line('length =' || len);
   for i in reverse 1..len
   loop
   revstr := revstr || substr(string,i,1);
   end loop;
   dbms_output.put_line('string is =' || string);
   dbms_output.put_line('reverse string is =' || revstr);
   end;

* write a pl/sql block to reverse the givan no.



   declare
   no number(5);
   s number(5);
   r number(5);
   begin
   no:=&number;
   s:=0;
   while no>0 loop
   r:=mod(no,10);
   s:=(s*10)+r;
   no:=no/10;
   end loop;
   dbms_output.put_line(s);
   end;


* write a pl/sql block to check no is palindrom or not.



   declare
   no number(5);
   s number(5);
   r number(5);
   t number(5);
   begin
   no:=&number;
   t:=no;
   s:=0;
   while no>0 loop
   r:=mod(no,10);
   s:=(s*10)+r;
   no:=no/10;
   end loop;
   if(t=s) then
   dbms_output.put_line('no is palindrm');
   else
   dbms_output.put_line('no is not palindrm');
   end if;
   end;


* write a pl/sql block to count no of records in table.


   declare
   no number(5);
   begin
   select count(*) into no from screen;
   dbms_output.put_line('records in table are' || no ||'.');
   end;

* write a PL/SQL block to display 1/1! + 2/2! + 3/3! +.......


          declare
          n number;
          a number;
          b number;
          c number:=(5,4);
          begin
          n:=&number;
          for i in 1..n loop
          a:=i;
          b:=1;
          for j in 1..i loop
          b:=b*j;       
          end loop;
          c:=c+a/b;
          dbms_output.put(a);
          dbms_output.put('/');
          dbms_output.put(a||'!');
          dbms_output.put('+');
          end loop;
          dbms_output.put('........=');
          dbms_output.put_line(c||' ');
          end;  



* Write a PL/SQL program to display the following format .

                            1

                            22

                            333

                            4444

                            55555




   declare
   no number(5);
   begin
   no:=&number;
   for i in 1.. no loop
   for j in 1..i loop
   dbms_output.put(i);
   end loop;
   dbms_output.put_line(' ');
   end loop;
   end;


* Write a PL/SQL block to delete a record from student database which is inputted by user.


    declare
    s_id mca.sno%type;
    begin
    s_id:=&sno;
    delete from mca where sno=s_id;
    dbms_output.put_line('the '||SQL%rowcount || 'row is deleted.');
    end;


* Create a procedure for computing department wise sum of salaries of
 employees for given department.

                              Emp(No, Name, Deptno, Salary)
                              Dept(Deptno, Name)


create or replace procedure sum_salary (deptno in dept.dept_no%type) is
     totalsalary number(6);
   begin
     select sum(salary) into totalsalary from emp where dept_no=deptno;
     dbms_output.put_line('Total salary  of '||deptno||'  is  Rs '||totalsalary);
   end;

 Run...
SQL> exec sum_salary(1);

* Write a function or procedure to check the given number is odd or even.

create or replace function evenodd(a in number)return number is
   begin       
          if (mod(a,2)=0) then
                   return '0';
          else
                   return '1';
          end if;
   end;

------------------------------------------------------------------------------------------------------------

* CREATE A PACKAGE TO CHECK THAT A NUMERIC VALUE IS GREATER THAN ZERO, AND A DATE IS LESS THAN OR EQUAL  TO   SYSDATE.


 
   CREATING OF PACKAGE:
   =======================

   CREATE OR REPLACE PACKAGE PK1
    AS

   PROCEDURE P1(PNO NUMBER);

   FUNCTION F1(PDATE DATE)
   RETURN DATE;

   END PK1;

 
   CREATE PACKAGE BODY:
   ====================

   CREATE OR REPLACE PACKAGE BODY PK1 AS
   PROCEDURE P1(PNO NUMBER) AS

   BEGIN
          IF PNO > 0 THEN
                   DBMS_OUTPUT.PUT_LINE('NUMBER IS GREATER ');
          ELSIF PNO < 0 THEN
                   DBMS_OUTPUT.PUT_LINE('NUMBER IS LESSER ');
          ELSE
                   DBMS_OUTPUT.PUT_LINE('NUMBER IS EQUAL');
          END IF;
   END;


FUNCTION F1(PDATE DATE) RETURN DATE
   AS F_DATE DATE;

   BEGIN
          F_DATE := SYSDATE;
          SELECT SYSDATE INTO F_DATE FROM DUAL;  
          IF PDATE > F_DATE THEN
                   DBMS_OUTPUT.PUT_LINE('DATE IS GREATER ');
          ELSIF PDATE < F_DATE THEN
                   DBMS_OUTPUT.PUT_LINE('DATE IS LESSER ');
          ELSE
                   DBMS_OUTPUT.PUT_LINE('DATE IS EQUAL');
          END IF;

   RETURN F_DATE;
   END;
   END PK1;

   CALL OF PACKAGE:
   ================
 DECLARE
          V_NUM NUMBER(5);
          V_DATE DATE;
          V_TEMP DATE;
   BEGIN
          V_NUM := &PNO;
          V_DATE := '&PDATE';
         
          PK1.P1(V_NUM);
          V_TEMP := PK1.F1(V_DATE);
END;


* Insert a record into Emp table and generate appropriate massage using pragma exception in it.

 
declare
         
          eno emp.no%type;
          ename emp.name%type;
          sal emp.salry%type;

          NULL_DATA exception;
          pragma exception_init(NULL_DATA,-1400);    

   begin

          insert into emp(no,name,salary)
          values(&eno,'&ename',&sal);
         
   exception

          when NULL_DATA then
          dbms_output.put_line('Data does not exists');
   end;

 
* Use user defined exception condition while deleting the record from table which is not exist.


declare
          no studevt.sid%type;
          s number(3);
          c number(2);
          record_not_found exception;
   begin
          no:=&no;
          select count(no) into c from student where sid=no;
          if c=0 then
                   raise record_not_found;
          else
                   delete from student where sid=no;
                   dbms_output.put_line('record is deleted');
          end if;
          exception
                   when record_not_found then
                   dbms_output.put_line('record not found');
   end;

 
* Write a PL/SQL program to display all the records from table using cursor.

declare
      no student.rollno%type;
      sname student.name%type;
      address student.city%type;
    
      cursor c_stud is
      select * from student;
    begin
       open c_stud;
       loop
         fetch cstud into no,sname,address;
         exit when c_stud%notfound;
         dbms_output.put('no is ' || no ||' ');
         dbms_output.put('name is ' || sname ||' ');
         dbms_output.put('address is ' ||address ||' ');
         dbms_output.put_line(' ');
    end loop;
      close c_stud;
    end;
 
Write a PL/SQL program using explicit cursor for computing categorywise total no. of books in library.


declare
          cat book_detail.category%type;
          v_count number;

        cursor c_bookdetail is
        select category,count(*) from book_detail group by category;
   begin
          open c_bookdetail;
          loop
          fetch c_bookdetail into cat,v_count;
          exit when c_bookdetail%notfound;
          dbms_output.put('category is '|| cat||'        ');
          dbms_output.put('No of books'||v_count ||'    ');
          dbms_output.put_line('  ');
          end loop;   
   close c_bookdetail;
   end;

* Write a trigger to test the entry of employee should not be more than 4 per particular department. Display appropriate message if user try to insert.

 
create or replace trigger test_check before insert on emp for each row
  
   declare
     cnt number;
   begin
      select count(deptno) into cnt from emp where deptno= :new.deptno;
      if cnt > 3 then
        raise_application_error(-20002,'INSERTION IS NOT POSSIBLE');
     end if;       
    end;

 
* Write a trigger for update employee table for the wages and update the wages only for the employee belonging to the department 34 the wages should be less than original wages.

   create or replace trigger check_salary before update on emp1 for each row

   begin
     if :new.dept_no=2 then
        if :new.salary>:old.salary then
           raise_application_error(18000,'updation is not possible');
        end if;
     else
         raise_application_error(18000,'updation is not possible');
     end if;
   end;

* Write a trigger that will check the pincod is exactly 6 digits.


create or replace trigger pincodecheck before insert on emp for each row
  
   declare
    new_pincode number(6);
  
   begin
   
    new_pincode:=:new.pincode;
     if length(new_pincode)<6 then
        raise_application_error(-20002,'Pincode must be of 6 digit');
     end if;
   end;


* Write a trigger that will stop the user to enter negative value in salary field of table employee.


    create or replace trigger checksalary before insert  on employee for each row

    declare
      new_sal number(7);
    begin
      new_sal:=:new.sal;
       if new_sal<=0 then
          raise_application_error(-20001,'Salary must not be negative or zero');
       end if;
   end;

No comments:

Post a Comment

Subscribe to: Posts (Atom)