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.