Saturday, 9 August 2014

PL/SQL queries(MG university)

These were my PL/SQL queries during my fifth semester. If you have any doubts, please let me know.


PL\SQL
1)<HAS>
DECLARE
RADIUS NUMBER(10);
AREA NUMBER(10);
BEGIN
RADIUS:=&RADIUS;
AREA:=3.14*RADIUS*RADIUS;
DBMS_OUTPUT. PUT_LINE(‘AREA: ’||AREA);
END;

2)
DECLARE
BASIC NUMBER(10,3);
GROSS NUMBER(10,3);
DA NUMBER(10,3);
HRA NUMBER(10,3);
PF NUMBER(10,3);
BEGIN
BASIC:=&BASIC;
DA:=0.4*BASIC;
HRA:=0.2*BASIC;
PF:=0.12*BASIC;
GROSS:=BASIC+DA+HRA-PF;
DBMS_OUTPUT. PUT_LINE(‘GROSS: ’||GROSS);
END;

3)
DECLARE
NUM NUMBER(10);
NUM1 NUMBER(10);
SUM1 NUMBER(10);
BEGIN
NUM:=&NUM;
SUM1:=0;
WHILE NUM<>0
LOOP
NUM1:=NUM MOD 10;
SUM1:=(SUM1 + NUM1);
NUM:=TRUNC(NUM/10);
END LOOP;
DBMS_OUTPUT.PUT_LINE (‘SUM: ‘||SUM1);
END;


4)
 CREATE TABLE ACCMASTER(ACCNO VARCHAR(10) PRIMARY KEY, NAME VARCHAR(10),BALANCE NUMBER(10,3));
CREATE TABLE ACCTRAN(ACCNO VARCHAR(10) REFERENCES ACCMASTER ON DELETE CASCADE,TRANSDATE DATE,DEB_CRED VARCHAR2(10) CHECK (DEB_CRED IN(‘DEBIT’,’CREDIT’)),AMOUNT NUMBER(10,3),PROCESSED VARCHAR2(10) CHECK(PROCESSED IN(‘YES’,’NO’)));
insert into accmaster values(‘10101A’,'NAMITHA',500.00);
insert into accmaster values(‘10201B’,'SANJANA',300.00);
insert into accmaster values(‘10302C’,'RAM',260.00);

insert into acctran values(‘10101A’,'14-JUN-13','DEBIT',700,'YES');
insert into acctran values(‘10201B’,'03-MAR-13','CREDIT',400,'NO');
insert into acctran values(‘10302C’,'1-SEP-13','DEBIT',2300,'NO');

set serveroutput on;

declare
   act acctran.accno%type;
   act1 acctran.accno%type;
    amt acctran.amount%type;
  bal accmaster.balance%type;
   begin
   select accno into act from acctran where deb_cred='DEBIT' and processed='NO';
   select amount into amt from acctran where accno=act;
  select balance into bal from accmaster where accno=act;
  update accmaster set balance=bal-amt where accno=act;
  select accno into act1 from acctran where deb_cred='CREDIT' and processed='NO';
  select amount into amt from acctran where accno=act1;
   select balance into bal from accmaster where accno=act1;
  update accmaster set balance=bal+amt where accno=act1;
 end;

SQL> select * from acctran;

ACCNO      TRANSDATE DEB_CRED       AMOUNT PROCESSED
---------- --------- ---------- ---------- ----------
10101A     14-JUN-13 DEBIT             700 YES
10201B     03-MAR-13 CREDIT            400 NO
10302C     01-SEP-13 DEBIT            2300 NO


5)
CREATE  TABLE EMPLOYEE(NAME VARCHAR(6),EMPNO NUMBER(5) PRIMARY KEY,SALARY NUMBER(5),DEPTNO NUMBER(5));
Table created.
CREATE TABLE EMP_RAISE(EMP_NO VARCHAR2(6),RAISE_DATE DATE,RAISE_AMT NUMBER(10));
Table created.

SQL> INSERT INTO EMPLOYEE VALUES('&NAME','&EMPNO','&SALARY','&DEPTNO');
Enter value for name: POOJA
Enter value for empno: 30
Enter value for salary: 1200
Enter value for deptno: 2
old   1: INSERT INTO EMPLOYEE VALUES('&NAME','&EMPNO','&SALARY','&DEPTNO')
new   1: INSERT INTO EMPLOYEE VALUES('POOJA','30','1200','2')

1 row created.

Commit complete.
SQL> /
Enter value for name: ABC
Enter value for empno: 12
Enter value for salary: 5600
Enter value for deptno: 1
old   1: INSERT INTO EMPLOYEE VALUES('&NAME','&EMPNO','&SALARY','&DEPTNO')
new   1: INSERT INTO EMPLOYEE VALUES('ABC','12','5600','1')

1 row created.

Commit complete.
SQL> /
Enter value for name: RAM
Enter value for empno: 8
Enter value for salary: 3000
Enter value for deptno: 2
old   1: INSERT INTO EMPLOYEE VALUES('&NAME','&EMPNO','&SALARY','&DEPTNO')
new   1: INSERT INTO EMPLOYEE VALUES('RAM','8','3000','2')

1 row created.

Commit complete.
SQL> /
Enter value for name: JOHN
Enter value for empno: 64
Enter value for salary: 8000
Enter value for deptno: 3
old   1: INSERT INTO EMPLOYEE VALUES('&NAME','&EMPNO','&SALARY','&DEPTNO')
new   1: INSERT INTO EMPLOYEE VALUES('JOHN','64','8000','3')

1 row created.

Commit complete.

declare
  cursor c1 is select salary,empno from employee;
 
  sal employee.salary%type;
 emp employee.empno%type;
begin                                             
 open c1;
  loop
  fetch c1 into sal ,emp;
insert  into emp_raise  values(emp,sysdate,sal*0.05);
update employee set salary=(salary +(salary*0.05)) where employee.empno=emp;
exit when c1%notfound;
end loop;
close c1;
end;
LIBRARY:
CREATE TABLE STUDENT(REGNO NUMBER(10),NAME VARCHAR(10),DEPT NUMBER(3),CLASS NUMBER(5),FINE NUMBER(10,3),NO_OF_BOOKS NUMBER(5));
CREATE TABLE LIBRARY_BOOKS(BID NUMBER(10),TITLE VARCHAR(20),AUTHOR VARCHAR(15),EDITION NUMBER(2),FINE NUMBER(10,3),NO_OF_COPIES NUMBER(5));
CREATE TABLE STATUS (REGNO NUMBER(10),BID NUMBER(10),STATUS VARCHAR(15),AUTHOR VARCHAR(10),ISSUE_DATE DATE,RETURN_DATE DATE);
INSERT INTO STUDENT VALUES(‘&REGNO’,’&NAME’,’&DEPT’,’&CLASS’,’&FINE’,’&NO_OF_BOOKS’);


SQL> INSERT INTO STUDENT VALUES('&REGNO','&NAME','&DEPT','&CLASS','&FINE','&NO_OF_BOOKS');
Enter value for regno: 3
Enter value for name: NAMITHA
Enter value for dept: 12
Enter value for class: 5
Enter value for fine:
Enter value for no_of_books: 1
old   1: INSERT INTO STUDENT VALUES('&REGNO','&NAME','&DEPT','&CLASS','&FINE','&NO_OF_BOOKS')
new   1: INSERT INTO STUDENT VALUES('3','NAMITHA','12','5','','1')

1 row created.

Commit complete.
SQL> /
Enter value for regno: 4
Enter value for name: POOJA
Enter value for dept: 10
Enter value for class: 6
Enter value for fine:
Enter value for no_of_books: 0
old   1: INSERT INTO STUDENT VALUES('&REGNO','&NAME','&DEPT','&CLASS','&FINE','&NO_OF_BOOKS')
new   1: INSERT INTO STUDENT VALUES('4','POOJA','10','6','','0')

1 row created.

Commit complete.

Enter value for regno: 6
Enter value for name: RAM
Enter value for dept: 12
Enter value for class: 6
Enter value for fine:
Enter value for no_of_books: 2
old   1: INSERT INTO STUDENT VALUES('&REGNO','&NAME','&DEPT','&CLASS','&FINE','&NO_OF_BOOKS')
new   1: INSERT INTO STUDENT VALUES('6','RAM','12','6','','2')

1 row created.

Commit complete.
SQL> /
Enter value for regno: 9
Enter value for name: NAVYA
Enter value for dept: 12
Enter value for class: 5
Enter value for fine:
Enter value for no_of_books: 1
old   1: INSERT INTO STUDENT VALUES('&REGNO','&NAME','&DEPT','&CLASS','&FINE','&NO_OF_BOOKS')
new   1: INSERT INTO STUDENT VALUES('9','NAVYA','12','5','','1')

1 row created.

Commit complete.

INSERT INTO LIBRARY_BOOKS VALUES(‘&BID’,’&TITLE’,’&AUTHOR’,’&EDITION’,’&NO_OF_COPIES’);






create or replace procedure issuebk(reg in varchar,bd in varchar,isdate in date)
 is
 aut varchar2(30);
b number(5);
cursor c1 is select  bid from status where bid=bd and regno=reg and status=’issued’;
begin
open c1;
fetch c1 into b;
if(c1%notfound) then
update student  set no_of_books=no_of_books+1 where regno=reg;
update library_books set no_of_copies=no_of_copies-1 where bid=bd;
select author into aut from library_books where bid=bd;
insert into status values(reg,bd,'issued',aut,isdate,'');
else
dbms_output.put_line(‘Book is already issued’);
end if;
close c1;              
end;

Procedure created.
 exec issuebk('3','401','02-sep-13');


SQL>  exec bissue('3','401','02-sep-13');

PL/SQL procedure successfully completed.

Commit complete.
SQL>
SQL> select * from status;

     REGNO        BID STATUS          AUTHOR     ISSUE_DAT RETURN_DA
---------- ---------- --------------- ---------- --------- ---------
         3        401 issued          DAN BROWN  02-SEP-13

return:
create or replace procedure returnbk(reg2 in varchar,bidno in varchar)
is
famt number(6);
issdate date;
begin
update student set no_of_books=no_of_books-1 where REGNO=reg2;
update library_books set no_of_copies=no_of_copies+1 where BID=bidno;
select ISSUE_DATE into issdate from status where REGNO=reg2;
if((sysdate-issdate)>10)
then
famt:=(sysdate-issdate-10)*10;
else
famt:=0;
end if;
 update student set fine=famt where regno=reg2;
update status set return_date=sysdate where regno=reg2;
update status set status=’returned’ where regno=reg2;
end;


Procedure created.

exec returnbk(‘3’,’401’);


PL/SQL procedure successfully completed.

Commit complete.
SQL> select * from status;

     REGNO        BID STATUS          AUTHOR     ISSUE_DAT RETURN_DA
---------- ---------- --------------- ---------- --------- ---------
         3        401 issued          DAN BROWN  02-SEP-13 09-OCT-13




SQL> select * from status;

     REGNO        BID STATUS          AUTHOR     ISSUE_DAT RETURN_DA
---------- ---------- --------------- ---------- --------- ---------
         3        401 returned        DAN BROWN  02-SEP-13 09-OCT-13
         2        403 issued          STEFFANI   07-OCT-13


MAXIMUM FINE

create or replace function maximum(maxm out number)
 return number
 is
 begin
 select max(fine) into maxm from student;
 dbms_output.put_line('Maximum fine is '||maxm);
 return maxm;
 end;

Function created.
set  serveroutput on;
declare
a number(5);
begin
a:=maximum(a);
end;
Maximum fine is 376

PL/SQL procedure successfully completed.

Commit complete.
TRIGGER 1:

create or replace trigger due after update of return_date on status for each row
begin
 if(:new.return_date>(:old.issue_date+10)) then
dbms_output.put_line('Returndate exceeds the due date');
end if;
end;

              Trigger created.


SQL> update status set return_date='25-oct-13' where regno=2;
Returndate exceeds the due date

1 row updated.

Commit complete.

TRIGGER 2:
              create or replace trigger more before update of no_of_books on student for each row
                 begin
                 if(:new.no_of_books>5) then
                 raise_application_error(-20000,'No of books exceeded five');
                 end if;
                 end;


Trigger created.
SQL> update student set no_of_books=7 where regno=3;
update student set no_of_books=7 where regno=3
       *
ERROR at line 1:
ORA-20000: No of books exceeded five
ORA-06512: at "U1103079.MORE", line 3
ORA-04088: error during execution of trigger 'U1103079.MORE'


Posted by at No comments:
Labels: , , , , , , , , ,

DBMS lab queries (MG university)

These were my DBMS queries in the DBMS lab, when I was studying it during my 5th semester. Since this was lying around on my computer, I decided to upload it in case you get stuck with some of the various DATABASE QUERIES. If you have any doubts please let me know.

1)

SQL> set autocommit on;
a)
SQL> CREATE TABLE CLIENT(CNO VARCHAR(6) PRIMARY KEY CHECK(CNO LIKE 'C%'),NAME VARCHAR2(20) NOT NULL,ADDRESS VARCHAR2(30),CITY VARCHAR2(15),PINCODE NUMBER(8),STATE VARCHAR2(15),BALDUE NUMBER(10,2));
Table created.

b)

SQL> CREATE TABLE PRODUCT(PNO VARCHAR2(6) PRIMARY KEY CHECK(PNO LIKE 'P%'),DESCRIPTION VARCHAR2(20) NOT NULL,PROFITPERCENT NUMBER(4,2) NOT NULL,UNITMEASURE VARCHAR2(15) NOT NULL,QTYONHAND NUMBER(8) NOT NULL,RECORDLVL VARCHAR2(15) NOT NULL,SELLPRICE NUMBER(8,2) NOT NULL CHECK(SELLPRICE<>0),COSTPRICE NUMBER(8,2) NOT NULL CHECK(COSTPRICE<>0));

Table created.
c)
SQL> CREATE TABLE SALESMAN(SNO VARCHAR2(6) PRIMARY KEY CHECK(SNO LIKE 'S%'),SNAME VARCHAR2(20) NOT NULL,CITY VARCHAR2(15) NOT NULL,STATE VARCHAR2(15),SELAMT NUMBER(6,2) NOT NULL CHECK(SELAMT<>0),TGTTOGET NUMBER(6,2) NOT NULL CHECK(TGTTOGET<>0),QTYSALE NUMBER(6,2),REMARKS VARCHAR2(60));
Table created.

d)
SQL>  CREATE TABLE SALES_ORDER(ORDERNO VARCHAR2(6) PRIMARY KEY CHECK(ORDERNO LIKE 'O%'), ORDERDATE DATE, CNO VARCHAR2(6) REFERENCES CLIENT(CNO) ON DELETE CASCADE,DELYADDR VARCHAR2(25),DELYTYPE CHAR(1) DEFAULT 'P',SNO VARCHAR2(6) REFERENCES SALESMAN(SNO) ON DELETE CASCADE,DELYDATE DATE,ORDERSTATUS VARCHAR2(10) CHECK(ORDERSTATUS IN('INPROCESS','FULFILLED','BACKORDER','CANCELLED')));

Table created.


SQL> ALTER TABLE SALES_ORDER ADD(CONSTRAINT DELYDATE CHECK(DELYDATE>ORDERDATE));

Table altered.

SQL> ALTER TABLE SALES_ORDER ADD(CONSTRAINT DELYTYPE CHECK(DELYTYPE IN ('P','F')));

Table altered.
e)
SQL> CREATE TABLE SALES_ORDER_DETAILS(ORDERNO VARCHAR2(6) REFERENCES SALES_ORDER ON DELETE CASCADE,PNO VARCHAR2(7) REFERENCES PRODUCT,QTYORDERED NUMBER(8),QTYDISP NUMBER(8),PRODUCTRATE NUMBER(10,2),PRIMARY KEY(ORDERNO,PNO));

Table created.
2)
a)
SQL> INSERT INTO CLIENT VALUES('&CNO','&NAME','&ADDRESS','&CITY',&PINCODE,'&STATE',&BALDUE);
Enter value for cno: C01
Enter value for name: AMRITHA
Enter value for address: ABCD
Enter value for city: COCHIN
Enter value for pincode: 680324
Enter value for state: KERALA
Enter value for baldue: 14000

1 row created.

Commit complete.
SQL> /
Enter value for cno: C02
Enter value for name: MANU
Enter value for address: FEG
Enter value for city: COCHIN
Enter value for pincode: 680324
Enter value for state: KERALA
Enter value for baldue: 0

1 row created.

Commit complete.
SQL> /
Enter value for cno: C03
Enter value for name: ALEN
Enter value for address: GHIJ
Enter value for city: COIMBATORE
Enter value for pincode: 680345
Enter value for state: KARNATAKA
Enter value for baldue: 5000
1 row created.

Commit complete.
SQL> /
Enter value for cno: C04
Enter value for name: AMMU
Enter value for address: KLMN
Enter value for city: PUNE
Enter value for pincode: 680423
Enter value for state: MAHARASHTRA
Enter value for baldue: 500

1 row created.

Commit complete.
SQL> /
Enter value for cno: C05
Enter value for name: MEGHA
Enter value for address: NEWR
Enter value for city: GANDIPURAM
Enter value for pincode: 680321
Enter value for state: KARNATAKA
Enter value for baldue: 0

1 row created.

Commit complete.
b)
SQL> INSERT INTO PRODUCT VALUES('&PNO','&DESCRIPTION',&PROFITPERCENT,'&UNITMEASURE',&QTYONHAND,&REOR
DERLVL,&SELLPRICE,&COSTPRICE);
Enter value for pno: P01
Enter value for description: T-SHIRTS
Enter value for profitpercent: 5
Enter value for unitmeasure: PIECE
Enter value for qtyonhand: 200
Enter value for reorderlvl: 50
Enter value for sellprice: 350
Enter value for costprice: 250

1 row created.

Commit complete.
SQL> /
Enter value for pno: P02
Enter value for description: SHIRTS
Enter value for profitpercent: 6
Enter value for unitmeasure: PIECE
Enter value for qtyonhand: 150
Enter value for reorderlvl: 50
Enter value for sellprice: 500
Enter value for costprice: 350

1 row created.

Commit complete.
SQL> /
Enter value for pno: P03
Enter value for description: COTTON JEANS
Enter value for profitpercent: 5
Enter value for unitmeasure: PIECE
Enter value for qtyonhand: 100
Enter value for reorderlvl: 20
Enter value for sellprice: 750
Enter value for costprice: 500

1 row created.

Commit complete.
SQL> /
Enter value for pno: P04
Enter value for description: TROUSERS
Enter value for profitpercent: 4
Enter value for unitmeasure: PIECE
Enter value for qtyonhand: 500
Enter value for reorderlvl: 20
Enter value for sellprice: 350
Enter value for costprice: 250

1 row created.

Commit complete.
SQL> /
Enter value for pno: P05
Enter value for description: SKIRTS
Enter value for profitpercent: 2.5
Enter value for unitmeasure: PIECE
Enter value for qtyonhand: 50
Enter value for reorderlvl: 30
Enter value for sellprice: 200
Enter value for costprice: 150

1 row created.

Commit complete.
SQL> /
Enter value for pno: P06
Enter value for description: TOPS
Enter value for profitpercent: 3
Enter value for unitmeasure: PIECE
Enter value for qtyonhand: 10
Enter value for reorderlvl: 40
Enter value for sellprice: 700
Enter value for costprice: 300

1 row created.

Commit complete.
c)
SQL> INSERT INTO SALESMAN VALUES('&SNO','&SNAME','&CITY','&STATE',&SELAMT,&TGTTOGET,&QTYSALE,'&REMAR
KS');
Enter value for sno: S01
Enter value for sname: MAHESH
Enter value for city: PUNE
Enter value for state: MAHARASHTRA
Enter value for selamt: 3000
Enter value for tgttoget: 100
Enter value for qtysale: 50
Enter value for remarks: GOOD
1 row created.

Commit complete.
SQL> /
Enter value for sno: S02
Enter value for sname: MANOJ
Enter value for city: COIMBATORE
Enter value for state: KARNATAKA
Enter value for selamt: 3000
Enter value for tgttoget: 500
Enter value for qtysale: 100
Enter value for remarks: BAD

1 row created.

Commit complete.

c)
SQL> INSERT INTO SALESMAN VALUES('&SNO','&SNAME','&CITY','&STATE',&SELAMT,&TGTTOGET,&QTYSALE,'&REMAR
KS');
Enter value for sno: S03
Enter value for sname: TRESSA
Enter value for city: COCHIN
Enter value for state: KERALA
Enter value for selamt: 3000
Enter value for tgttoget: 750
Enter value for qtysale: 100
Enter value for remarks: BAD

1 row created.

Commit complete.
SQL> /
Enter value for sno: S04
Enter value for sname: ANIL
Enter value for city: PUNE
Enter value for state: MAHARASHTRA
Enter value for selamt: 3500
Enter value for tgttoget: 350
Enter value for qtysale: 150
Enter value for remarks: GOOD

1 row created.

Commit complete.
d)
SQL> INSERT INTO SALES_ORDER VALUES('&ORDERNO','&ORDERDATE','&CNO','&DELYADDR','&DELYTYPE','&SNO','&
DELYDATE','&ORDERSTATUS');
Enter value for orderno: O1
Enter value for orderdate: 02-JUN-13
Enter value for cno: C03
Enter value for delyaddr: XXX
Enter value for delytype: F
Enter value for sno: S02
Enter value for delydate: 23-JUL-13
Enter value for orderstatus: FULFILLED

1 row created.

Commit complete.
SQL> /
Enter value for orderno: O2
Enter value for orderdate: 02-MAY-12
Enter value for cno: C02
Enter value for delyaddr: YYY
Enter value for delytype: P
Enter value for sno: S03
Enter value for delydate: 23-JUL-12
Enter value for orderstatus: CANCELLED

1 row created.

Commit complete.
SQL> /
Enter value for orderno: O3
Enter value for orderdate: 20-DEC-11
Enter value for cno: C04
Enter value for delyaddr: ZZZ
Enter value for delytype: P
Enter value for sno: S01
Enter value for delydate: 21-JAN-12
Enter value for orderstatus: FULFILLED

1 row created.

Commit complete.
SQL> /
Enter value for orderno: O4
Enter value for orderdate: 23-JUL-13
Enter value for cno: C01
Enter value for delyaddr: QQQ
Enter value for delytype: F
Enter value for sno: S04
Enter value for delydate: 30-JUL-13
Enter value for orderstatus: INPROCESS

1 row created.

Commit complete.
e)
SQL> INSERT INTO SALES_ORDER_DETAILS VALUES('&ORDERNO','&PNO',&QTYORDERED,&QTYDISP,&PRODUCTRATE);
Enter value for orderno: O3
Enter value for pno: P01
Enter value for qtyordered: 4
Enter value for qtydisp: 4
Enter value for productrate: 525

1 row created.

Commit complete.
SQL> /
Enter value for orderno: O2
Enter value for pno: P01
Enter value for qtyordered: 10
Enter value for qtydisp: 0
Enter value for productrate: 525

1 row created.

Commit complete.
SQL> /
Enter value for orderno: O1
Enter value for pno: P03
Enter value for qtyordered: 2
Enter value for qtydisp: 2
Enter value for productrate: 1050

1 row created.

Commit complete.
SQL> /
Enter value for orderno: O4
Enter value for pno: P04
Enter value for qtyordered: 2
Enter value for qtydisp: 1
Enter value for productrate: 700

1 row created.

Commit complete.
DDL
1)
SQL> ALTER TABLE CLIENT ADD(TELEPHONE NUMBER(10));

Table altered.

2)
SQL> ALTER TABLE CLIENT MODIFY(NAME VARCHAR2(10));

Table altered.
3)
SQL> ALTER TABLE PRODUCT DROP COLUMN RECORDLVL;

Table altered.
4)
SQL> ALTER TABLE CLIENT MODIFY(PINCODE NUMBER(8) NOT NULL);

Table altered.

5)
SQL> ALTER TABLE CLIENT MODIFY(PINCODE NUMBER(8));

Table altered.

DML

1)<HAS> :

SQL> select pno,description,sellprice,costprice from product where profitpercent > 4;

PNO    DESCRIPTION           SELLPRICE  COSTPRICE
------ -------------------- ---------- ----------
P01    T-shirts                    350        250
P02    shirts                      500        350
P03    Cotton Jeans                750        500
2)
SQL> SELECT * FROM SALESMAN WHERE STATE NOT LIKE 'KERALA' AND QTYSALE>50;

SNO    SNAME                CITY            STATE               SELAMT   TGTTOGET    QTYSALE REMARKS
------ -------------------- --------------- --------------- ---------- ---------- ---------- -------
S02    MANOJ                COIMBATORE      KARNATAKA             3000        500        100 BAD
S04    ANIL                 PUNE            MAHARASHTRA           3500        350        150 GOOD



3)<HAS>  :

SQL> select * from client where city like 'cochin';

CNO    NAME       ADDRESS         CITY               PINCODE  STATE               BALDUE  TELEPHONE
------ ---------- ------------------------------ --------------- ---------- --------------- ---------- ----------
C01    Amritha    Abcd                  cochin              680324   Kerala               14000
C02    Manu       feg                      cochin              680324  Kerala                   0


4)
SQL> SELECT * FROM SALESMAN WHERE SNAME LIKE 'M%' OR CITY LIKE '__H%';

SNO    SNAME                CITY            STATE               SELAMT   TGTTOGET    QTYSALE REMARKS
------ -------------------- --------------- --------------- ---------- ---------- ---------- -------
S01    MAHESH               PUNE            MAHARASHTRA           3000        100         50 GOOD
S02    MANOJ                COIMBATORE      KARNATAKA             3000        500        100 BAD

5)
SQL> SELECT * FROM CLIENT WHERE CITY IN('COCHIN','PUNE','GANDIPURAM') AND BALDUE<500;

CNO    NAME       ADDRESS                        CITY               PINCODE STATE               BALDUE  TELEPHONE
------ ---------- ------------------------------ --------------- ---------- --------------- --------
C02    MANU       FEG                            COCHIN              680324 KERALA                   0
C05    MEGHA      NEWR                           GANDIPURAM          680321 KARNATAKA                0

6)
SQL> SELECT DESCRIPTION FROM PRODUCT WHERE SELLPRICE BETWEEN 300 AND 500;

DESCRIPTION
--------------------
T-SHIRTS
SHIRTS
TROUSERS

7)
SQL> SELECT DISTINCT PNO FROM SALES_ORDER_DETAILS WHERE QTYDISP>0;

PNO

P01
P03
P04

8)
SQL> Select description,sellprice*0.15 "new_price" from product where  sellprice>500;

DESCRIPTION           new_price
-------------------- ----------
Cotton Jeans              112.5
tops                        105

9)
SQL> UPDATE PRODUCT SET DESCRIPTION='JEANS' WHERE PNO='P03';

1 row updated.

Commit complete.
SQL> SELECT * FROM PRODUCT;

PNO    DESCRIPTION          PROFITPERCENT UNITMEASURE      QTYONHAND  SELLPRICE  COSTPRICE
------ -------------------- ------------- --------------- ---------- ---------- ----------
P01    T-SHIRTS                         5 PIECE                  200       52.5        250
P02    SHIRTS                           6 PIECE                  150       75         350
P03    JEANS                            5 PIECE                  100      112.5        500
P04    TROUSERS                         4 PIECE                  500       52.5        250
P05    SKIRTS                         2.5 PIECE                   50         30        150
P06    TOPS                             3 PIECE                   10        105        300

6 rows selected.


10)

SQL> update client set baldue=100 where cno='C02';

1 row updated.

Commit complete.

SQL> select * from client;

CNO    NAME       ADDRESS                        CITY               PINCODE   STATE               BALDUE  TELEPHONE

------ ---------- ------------------------------ --------------- ----------  --------------- ---------- ----------
C01    Amritha    Abcd                           cochin              680324 Kerala               14000
C02    Manu       feg                            cochin              680324                      Kerala                 100
C03    Alen       ghij                           Coimbatore          680345 Karnataka             5000
C04    Ammu       klmn                           Pune                680423  Maharashtra            500
C05    Megha      newr                           Gandhipuram         680321        Karnataka                0

12)
SQL> SELECT AVG(SELLPRICE) "AVERAGE PRICE" FROM PRODUCT;

AVERAGE PRICE
-------------
         475

13)
SQL> SELECT MAX(COSTPRICE)"MAXIMUM PRICE",MIN(COSTPRICE)"MINIMUM PRICE" FROM PRODUCT;

MAXIMUM PRICE MINIMUM PRICE
-------------                 -------------
        500                 150

14)
SQL> SELECT COUNT(CNO) "NUMBER OF CLIENTS" FROM CLIENT WHERE STATE NOT LIKE 'KERALA';

NUMBER OF CLIENTS
-----------------
                3

SQL> SELECT PNO,SUM(QTYORDERED) "TOTAL ORDER", SUM(QTYDISP) "TOTAL DISPATCH" FROM
SALES_ORDER_DETAILS GROUP BY PNO HAVING PNO IN(SELECT PNO FROM SALES_ORDER_DETAILS);

PNO    TOTAL ORDER TOTAL DISPATCH
--- ---------------------- -------------------------
P01                   4                                4
P03                   2                                     2
P04                   1                                     2                           



16.)
SQL> select product.pno,product.description,qtydisp*productrate  “Values” from
product,sales_order_details where product.pno=sales_order_details.pno;

PNO    DESCRIPTION          Values
------ -------------------- -------------------
P01    T-shirts                            2100
P01    T-shirts                               0
P03    Jeans                               2100
P04    Trousers                             700

SUB-QUERIES
1)
SQL> SELECT DESCRIPTION FROM PRODUCT WHERE PNO=(SELECT PNO FROM SALES_ORDER_DETAILS WHERE QTYDISP>2);

DESCRIPTION
--------------------
T-SHIRTS

2)
SQL> SELECT PNO,DESCRIPTION FROM PRODUCT WHERE PNO NOT IN (SELECT PNO FROM SALES_ORDER_DETAILS);
PNO    DESCRIPTION
------ --------------------
P02    SHIRTS
P05    SKIRTS
P06    TOPS

3)
SQL>  SELECT * FROM CLIENT WHERE CNO IN(SELECT CNO FROM SALES_ORDER WHERE ORDERNO IN(SELECT ORDERNO
FROM SALES_ORDER_DETAILS WHERE PNO IN(SELECT PNO FROM PRODUCT WHERE DESCRIPTION LIKE 'T-SHIRTS')));

CNO    NAME       ADDRESS                        CITY               PINCODE STATE               BALDUE  TELEPHONE
------ ---------- ------------------------------ --------------- ---------- --------------- --------
C02    MANU       FEG                            COCHIN              680324 KERALA                   0
C04    AMMU       KLMN                           PUNE                680423 MAHARASHTRA            500


4)(wrong)

SQL> SELECT NAME,SNAME,DESCRIPTION FROM CLIENT,SALESMAN,PRODUCT WHERE (CNO IN(SELECT CNO FROM SALES_ORDER WHERE ORDERSTATUS LIKE 'FULFILLED')) AND( PNO IN(SELECT PNO FROM SALES_ORDER_DETAILS WHERE ORDERNO IN (SELECT ORDERNO FROM SALES_ORDER WHERE ORDERSTATUS='FULFILLED'))) AND (SNO IN(SELECT SNO FROM SALES_ORDER WHERE ORDERSTATUS LIKE ‘FULFILLED’));

5)
SQL> SELECT * FROM CLIENT WHERE CNO IN(SELECT CNO FROM SALES_ORDER WHERE ORDERSTATUS='CANCELLED');

CNO    NAME       ADDRESS                        CITY               PINCODE STATE               BALDUE  TELEPHONE
------ ---------- ------------------------------ --------------- ---------- --------------- --------
C02    MANU       FEG                            COCHIN              680324 KERALA                   0


UNION
SQL> SELECT NAME FROM CLIENT WHERE CITY='COCHIN' UNION SELECT SNAME FROM SALESMAN WHERE CITY ='COCHIN';

NAME
--------------------
AMRITHA
MANU
TRESSA

INTERSCTION
SQL> SELECT CNO FROM CLIENT WHERE CITY='COCHIN' INTERSECT SELECT CNO FROM CLIENT WHERE CNO IN (SELECT CNO FROM SALES_ORDER);

CNO
------
C01
C02

MINUS
SQL> SELECT CNO FROM CLIENT MINUS SELECT CNO FROM SALES_ORDER;

CNO
------
C05
JOIN
SQL>SELECT PNO,QTYORDERED FROM PRODUCT,SALES_ORDER_DETAILS WHERE PRODUCT.PNO=SALES_ORDER_DETAILS.PNO;

VIEW
SQL> create view view1 as select orderno,orderdate,orderstatus from sales_order;

View created.

SQL> create view view2 as select pno,qtyordered from sales_order_details;

View created.

INDEX

SQL>create index ind on sales_order_details(order,pno);


Index created.
Posted by at No comments:
Labels: , , , , , , ,
Subscribe to: Posts (Atom)