Database Management Systems Laboratory

Database Management Systems Laboratory 

SYLLABUS

1. Introduction to SQL and installation of a database product

2. Data Types, Creating Tables, Retrieval of Rows using Select Statement, Conditional Retrieval of Rows, Alter and Drop Statements.

3.Working with Null Values, Matching a Pattern from a Table, Ordering the Result of a Query, Aggregate Functions,Grouping the Result of a Query, Update and Delete Statements.

4.Set Operators, Nested Queries, Joins, Sequences.

5.Views, Indexes, Database Security and Privileges: Grant and Revoke Commands, Commit and Rollback Commands.

6.PL/SQL Architecture, Assignments and Expressions, Writing PL/SQL Code, Referencing Non-SQL parameters.

7.Stored Procedures and Exception Handling.

8.Triggers and Cursor Management in PL/SQL.

9.Working with XML databases

10.Introduction to a data mining tool

ASSIGNMENT 1

CREATE THE TABLE:

create table client_master11

(client_no varchar2(6),

name varchar2(20),

address varchar2(30),

city varchar2(15),

state varchar2(15),

pincode varchar2(15),

balance_due number(10,2)

)

insert into client_master11 values

('&client_no','&name','&address','&city','&state','&pincode',&balance_due)

SQL> select*from client_master11;

CLIENT NAME                 ADDRESS                    CITY            STATE               PINCODE         BALANCE_DUE

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

0001   Ivan                      NULL                        bombay          maharastra         400054                15000

0002   vandana              NULL                        madras            tamil nadu          780001                    0

0003   pramada              NULL                        bombay          maharastra         400057                 5000

0004   basu                     NULL                        bombay          maharastra         400056                    0

0005   ravi                       NULL                        delhi                delhi                    100001                 2000

0006   rukmani               NULL                        bombay          maharastra         400050                    0

0007   ankit                    NULL                        ghaziabad       uttar pradesh     201010                    0

 7   rows selected

CREATE THE TABLE:

create table product_master11

(product_no varchar2(8),

description varchar2(15),

profitpercent number(6),

unitmeasure varchar2(15),

quantity number(4),

reorder number(4),

sellprice number(8),

costprice number(8)

)

insert into product_master11 values

('&product_no','&description',&profitpercent,'&unitmeasure',&quantity,&reorder,&sellprice,&costprice);

SQL> select*from product_master11;

PRODUCT   DESCRIPTION     PROFIT%       UNITMEASURE       QUANTITY    REORDER  SELLPRICE  COSTPRICE

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

p00061     1.44 floppies                  5  pieces                 100            20              525         500

p03453     monitor                          6  pieces                  10                   20           12000       11200

p06734     mouse                            5 pieces                  20             5                   1050         500

p07865     1.22 floppies                 5 pieces                 100             20                 525           500

p07868     keyboard                       2 pieces                  10               3                   3150         3050

p07885     cd drive                          3 pieces                  10               3                   5250         5100

p07965     540HDD                         4 pieces                  10               3                   8400         8000

p07975     1.44 drive                      5 pieces                  10              3                   1050          1000

p08865     1.22 drive                      5 pieces                   2             3                   1050          1000

9 rows selected

QUERIES

1. FIND THE NAME OF ALL THE CLIENTS.

SQL> select name from client_master11;

NAME

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

pramada

basu

ravi

rukmani

Ivan

vandana

ankit

7 rows selected.

1. RETRIVE THE LIST OF NAMES AND CITIES OF ALL THE CLIENTS.

SQL> select name,city from client_master11;

NAME                 CITY

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

pramada              bombay

basu                     bombay

ravi                       delhi

rukmani               bombay

Ivan                      bombay

vandana              madras

ankit                   ghaziabad

7 rows selected.

1. LIST THE VARIOUS PRODUCT AVAILABLE FROM THE PRODUCT_MASTER TABLE.

SQL> select description from product_master11;

DESCRIPTION

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

1.44 floppies

monitor

monitor

mouse

1.22 floppies

keyboard

cd drive

540HDD

1.44 drive

1.22 drive

10 rows selected.

1. List all the client who are located in Bombay.

CLIENT NAME                 ADDRESS                        CITY            STATE           PINCODE    BALANCE_DUE

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

0003   pramada              NULL                           bombay          maharastra      400057         5000

0004   basu                     NULL                           bombay          maharastra      400056             0

0006   rukmani               NULL                           bombay          maharastra      400050             0

0001   Ivan                      NULL                           bombay          maharastra      400054          15000

4 rows selected.

1. DISPLAY INFORMATION FOR CLIENT NO 0001 AND 0002.

SQL> select * from client_master11 where client_no='0001' or client_no='0002';

CLIENT NAME                 ADDRESS                    CITY            STATE            PINCODE    BALANCE_DUE

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

0001   Ivan                     NULL                           bombay          maharastra      400054        15000

0002   vandana              NULL                           madras          tamilnadu       780001               0

1. FIND THE PRODUCT WITH DESCRIPTION 1.44DRIVE AND 1.22 DRIVE.

SQL> select * from product_master11 where description='1.44 drive'or description

 ='1.22 drive';

PRODUCT_ DESCRIPTION     PROFITPERCENT UNITMEASURE       QUANTITY    REORDER  SELLPRICE  COSTPRICE

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

p07975        1.44 drive                  5                           pieces                         10                    3             1050       1000

p08865       1.22 drive                  5                              pieces                      2                     3                1050       1000

    

1. FIND ALL THE PRODUCTS WHERE SELLING PRICE IS GREATER THAN RS. 5000.

SQL> select*from product_master11 where sellprice>=5000;

PRODUCT_N    DESCRIPTION     PROFITPERCENT UNITMEASURE       QUANTITY    REORDER  SELLPRICE  COSTPRICE

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

p03453   monitor                       6 pieces                  10                 20            12000      11200

p07885    cd drive                        3 pieces                  10              3             5250       5100

p07965   540HDD                       4 pieces                  10              3               8400       8000

1. FIND ALL THE LIST OF CLIENTS WHO STAY IN CITY BOMBAY OR DELHI OR MADRAS.

SQL> select*from client_master11 where city='madras'or city='delhi'or city='bomb

ay';

CLIENT NAME                 ADDRESS                        CITY            STATE           PINCODE         BALANCE_DUE

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

0003   pramada              NULL                           bombay          maharastra      400057                 5000

0004   basu                      NULL                           bombay          maharastra      400056                    0

0005   ravi                      NULL                             delhi                      delhi           100001                 2000

0006   rukmani              NULL                            bombay          maharastra      400050                    0

0001   Ivan                     NULL                            bombay          maharastra       400054                15000

0002   vandana              NULL                            madras          tamil nadu         780001     

               

1. Find the Product whose sellingprice>2000and less than or equal to 5000.

SQL> select*from product_master11 where sellprice between 1999 and 5001;

PRODUCT_ DESCRIPTION   PROFITPERCENT UNITMEASURE    QUANTITY    REORDER  SELLPRICE   COSTPRICE

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

 p07868     keyboard                 2 pieces                  10                  3               3150           3050

1. 10.List the name city and state of client who not stay in state of maharastra.

SQL> select*from client_master11 where state not in 'maharastra';

CLIENT NAME                 ADDRESS                  CITY                STATE              PINCODE         BALANCE_DUE

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

0005   ravi                     NULL                           delhi                 delhi                     100001                 2000

0002   vandana              NULL                           madras          tamil nadu            780001                    0

0007   ankit                   NULL                           ghaziabad       uttar pradesh      201010                    0

     

ASSIGNMENT  2

USING THE TABLE  CLIENT MASTER AND PRODUCT MASTER ANSWER THE FOLLOWING QUESTIONS.

• Q1 CHANGE THE SELLING PRICE OF 1.44 FLOPPY DRIVE TO 1150.

SQL> update product_master set

           sell_price = 1150

           where description='1.44 floppies'

  

• Q2 DELETE THE RECORD WITH CLIENT 0001 FROM THE CLIENT  MASTER.

SQL> delete from client_master

           where client_no=0001;

• Q3 CHANGE THE CITY OF CLIENT_NO 0005 TO BOMBAY.

SQL> update client_master set

           city ='bombay'

           where client_no=0005;

• Q4 CHANGE THE BAL_DUE OF CLIENT_NO 0001 TO 1000.

SQL> update client_master set

            bal_due=1000

             where client_no=0002;

• Q5 FIND THE PRODUCTS WHOSE SELLING PRICE IS MORE THAN 1500 AND ALSO FIND THE NEW SELLING PRICE AS ORIGINAL SELLING PRICE *15.

SQL> select description,sell_price,(sell_price)*15 "new SP" from product_master

            where sell_price>1500

  

• Q6 FIND OUT THE CLIENT WHO STAYS IN THE CITY WHOSE SECOND LETTER IS A.

SQL> select*from client_master

           where city like '_a%'

• Q7 FIND OUT THE NAME OF THE CLIENTS HAVING A AS THE SECOND LETTER IN THEIR NAMES.

SQL> select*from client_master

           where name like '_a%'

  

• Q8 LIST THE PRODUCTS IN SORTED ORDER OF THEIR DESCRIPTION.

SQL> select description from product_master order by description asc;

• Q9 COUNT THE TOTAL NUMBER OF PRODUCTS.

SQL> SELECT count(DISTINCT description)

           FROM product_master;

• Q10 CALCULATE THE AVERAGE PRICE OF ALL THE PRODUCTS.

SQL> SELECT AVG(costprice)

            FROM product_master;

• Q11 CALCULATE THE MINIMUM PRICE OF THE PRODUCTS.

SQL> SELECT MIN(costprice)

           FROM product_master;

• Q12 DETERMINE THE MAXIMUM AND MINIMUM PRICES. RENAME THE TITLE AS ‘MAX _PRICE’AND ‘MIN_PRICE’ RESPECTIVELY.

SQL> SELECT MIN(costprice)"MIN_PRICE" ,MAX(costprice) "MAX_PRICE"

           FROM product_master;

• Q13 COUNT THE NUMBER OF PRODUCTS HAVING PRICE GREATER THAN OR EQUAL TO 1500.

SQL> SELECT COUNT(product_no)

           FROM product_master

           WHERE costprice >1499;

ASSIGNMENT 3

(RESTRICTION ON TABLE )

CREATE THE FOLLOWING TABLE WITH  THE FOLLOWING RESTRICTION

Sales_master

SQL> create table sales_master

           (  salesman_no varchar2(6)CHECK(salesman_no LIKE 's%'),

           sal_names varchar2(20)NOT NULL,

           Address varchar2(20)NOT NULL,

            city varchar2(20),

            state varchar2(20),

            pincode varchar2(6),

            sal_amount number(8,2)NOT NULL,

            tgt_to_get number(6,2)NOT NULL,

            ytd_sales number(6,2)NOT NULL,

            remarks varchar2(30),

            PRIMARY KEY(salesman_no),

            CHECK(tgt_to_get !=0),

            CHECK(sal_amount !=0),
            CHECK(ytd_sales!=0 ));

         

SQL> desc sales_master11;

SQL> select*from sales_master011;

Saless_order

SQL> create table sales_order11(

           s_order_no varchar2(6)CHECK(s_order_no  LIKE 'o%'),

           s_order_date date,

           client_no varchar2(25),

           dely_add varchar2(6),

           salesman_no varchar2(6),

           dely_type char(1) default('f'),

           billed_yn char(1),

           dely_date date,

           order_status varchar2(10),

           CHECK(s_order_date>dely_date),

           CHECK(order_status IN('in process','fulfilled','back order','canceled')),

           PRIMARY KEY(s_order_no),

           FOREIGN KEY(salesman_no)REFERENCES sales_master11,

           CHECK(dely_type IN('p','f')));

SQL> desc sales_order11

SQL> select*from sales_order011;

sales_order_details

SQL> create table sales_order_details11(

           s_order_no varchar2(6)PRIMARY KEY,

           product_no varchar2(6),

           qty_no number(8),

           qty_disp number(8),

           product_rate number(10,2),

           FOREIGN KEY(s_order_no )REFERENCES sales_order11);

SQL> desc sales_order_details11;

SQL> select* from sales_order_details011;

ASSIGNMENT 4

Q1 CREATE THE FOLLOWING TABLE WITH NAME CHALLAN HEADER

SQL> create table challanheader

          (challan_no varchar2(6) PRIMARY KEY,

           s_order_no varchar2(6),

           challan_date date NOT NULL,

           billed_yn char(1) default('f'),

           CHECK(billed_yn IN('y','n')),

           FOREIGN KEY(s_order_no)REFERENCES sales_order);

Q2 CREATE THE TABLE WITH NAME CHALLAN_DETAILS

SQL>create table challan_details

          (challan_no varchar2(6) PRIMARY KEY,

           product_no  varchar2(6),

           qty_displ number(4,2) NOT NULL)

PERFORM THE FOLLOWING

• Q1 MAKE THE PRIMARY KEY TO CLIENT_NO IN CLIENT_MASTER.

SQL> alter table

          client_master

          ADD PRIMARY KEY(client_no);

• Q2 ADD A NEW COLUMN PHONE_NO IN THE CLIENT_MASTER TABLE.

SQL> alter table

           product_master

           ADD(phone_no number(12));

• Q3 ADD THE NULL CONSTRAINT IN THE PRODUCT_MASTER  TABLE WITH THE COLUMN DESCRIPTION, PROFIT PERCENT, SELL PRICE AND COST PRICE.

SQL> alter table

           product_master

           MODIFY(sellprice number(8) NOT NULL)

           MODIFY(costprice number(8) NOT NULL)

           MODIFY(profitpercent number(6) NOT NULL)

           MODIFY(description varchar2(15) NOT NULL);

• Q4 CHANGE THE SIZE OF THE CLIENT_NO FIELD IN THE CLIENT_MASTER TABLE.

SQL> alter table

           client_master

           MODIFY(client_no varchar2(20));

• Q5 SELECT PRODUCT_NO, DESCRIPTION WHERE PROFIT PERCENT IS BETWEEN 20 AND 30 BOTH INCLUSIVE

SQL> select product_no,description from product_master

           where profitpercent between 20 and 30;

ASSIGNMENT 5

                                     IMPLEMENT THE CONCEPT OF JOINS

• FIND OUT THE PRODUCT WHICH HAS BEEN SOLD TO ‘IVAN SAYROSS.’

Select  product_no,description

From product_master11 

Where Item_sold_to=’Ivan Sayross’;

• FIND OUT THE PRODUCT AND THEIR QUANTITIES THAT WILL HAVE DO DELIVERED.

Select product_description,product_quantity

From product_master

Where status=’deliver’;

• FIND OUT THE NAMES OF CLIENTS WHO HAVE PURCHASED ‘CD DRIVE’

Select c.name

From client_master11 c, product_master11 p

Where c.product_id=p.product_id

AND p.description =’Cd drive’;

• LIST THE PRODUCT_NO AND S_ORDER_NO OF CUSTOMERS HAAVING QTY ORDERED LESS THAN 5 FROM THE ORDER DETAILS TABLE FOR THE PRODUCT “1.44 FLOPPIES”.

Select p.product_no,p.s_orderno

From sales_order11 p,sales_order_detail s

Where p.product_no=s.product_no

And s.quantity<5 AND s.description =’1.44 floppies’;

• FIND THE PRODUCTS AND THEIR QUANTITIES FOR THE ORDERS PLACED BY CLIENT_NO “ C00001” AND “C00002”

Select p.product_no,p.quantities,p.description 

From sales_order p,client_master c

Where p.product_no=c.itemorder-id

And c.client_no=’C000001’ OR ’C000002’;

ASSIGNMENT 6

CONCEPT OF GROUPING OF DATA.

• Print the description and total quantity sold for each product.

Select description,quantity_sold

From product_master11

Order by quantity sold ASC;

• Find the names of clients who have ‘CD Drive’.

Select name from client_master

Where item_bought =’CD DRIVE’

Group by name;

• Select product_no, product description and qty ordered for each product.

Select product_no,quantity_ordered,description

From sales_order_details11 

Group by product_no;

ASSIGNMENT 7

CONCEPT OF INDEXES AND VIEWS.

• CREATE A VIEW THA CONTAINS DETAILS OF EMPLOYEE IN DEPARTMENT 40.

SQL>  create view employees11

  2   AS select employee_id,last_name,salary

  3   from employees

  4   where department_id =40;

View created.

UPDATING

SQL> select * from employees11;

EMPLOYEE_ID LAST_NAME                     SALARY

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

        203 Mavris                          6500

SQL> update employees11 set salary = 6600

  2  where employee_id=203;

1 row updated.

INSERTION NOT ALLOWED

SQL> insert into employees11 values('787','Marwadi',2345);

insert into employees11 values('787','Marwadi',2345)

*

ERROR at line 1:

ORA-01400: cannot insert NULL into ("IT11"."EMPLOYEES"."EMAIL")

• CREATE A VIEW OF DEPARTMENT NAME,MINIMUM,MAXIMUM,AVERAGE SALARY FROM EMPLOYEE AND DEPARTMENT TABLE.

SQL> create view new_sal(dname,maxsal,minsal,avgsal)

  2  as select d.department_name,min(e.salary),max(e.salary),avg(e.salary)

  3  from employees e, departments d

  4  where e.department_id=d.department_id

  5  group by department_name

  6  /

View created.

SQL> select*from new_sal;

DNAME                              MAXSAL     MINSAL     AVGSAL

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

Accounting                           8300      12000      10150

Administration                    4400          4400       4400

Executive                             17000         24000 19333.3333

Finance                                 6900        12000       8600

Human Resources              6600                    6600       6600

IT                                           4200          9000       5760

Marketing                            6000        13000       9500

Public Relations                  10000      10000      10000

Purchasing                           2500          11000       4150

Sales                                      6100         14000       8955.882

Shipping                               2100         8200         3475.55556

11 rows selected.

ASSIGNMENT 8

• WAP in pl/sql for addition of two numbers.

SQL> declare

  2  a number(2);

  3  b number(2);

  4  c number(2);

  5  begin

  6  a:=&a;

  7  b:=&b;

  8  c:=a+b;

  9  dbms_output.put_line(a|| ' + '||b||' = '||c);

 10  end;

 11  /

Enter value for a: 44

old   6: a:=&a;

new   6: a:=44;

Enter value for b: 43

old   7: b:=&b;

new   7: b:=43;

44 + 43 = 87

PL/SQL procedure successfully completed.

• WAP in pl/sql for addition of 1 to 100.

SQL>  Declare

  2       i number;

  3       z number:=0;

  4   Begin

  5       for i in 1..100 loop

  6                   z:=z+i;

  7       end loop;

  8       dbms_output.put_line('TOTAL SUM IS='||z);

  9   end;

 10  /

TOTAL SUM IS=5050

PL/SQL procedure successfully completed.

• Wap in pl/sql to check whether  the given number is even or odd.

SQL> declare

  2  a number(4);

  3  begin

  4  a:=&a;

  5  if mod(a,2)=0 then

  6  dbms_output.put_line('A is an even NUMBER');

  7  else

  8  dbms_output.put_line('A is an odd NUMBER');

  9  end if;

 10  end;

 11  /

Enter value for a: 32

old   4: a:=&a;

new   4: a:=32;

A is an even NUMBER

PL/SQL procedure successfully completed.

• Wap in sql/pl to inverse a number.

SQL> declare

  2   n number := &n;

  3   s number := 0;

  4   i number;

  5   begin

  6   while(n>0)

  7  loop

  8   i := n mod 10;

  9  s :=(s*10) +i;

 10   n := floor(n/10);

 11   end loop;

 12   dbms_output.put_line(s||'is reverse of a number');

 13  end;

 14  /

Enter value for n: 456

old   2:  n number := &n;

new   2:  n number := 456;

654is reverse of a number

PL/SQL procedure successfully completed.

• TO GENERATE FIBONNACI SERIES 

SQL>  declare

  2

  3   i number(4):=0;

  4   j number(4):=1;

  5   k number(4):=0;

  6   begin

  7   dbms_output.put_line(i);

  8   dbms_output.put_line(j);

  9   for l IN 2..8 loop

 10   k:=i+j;

 11   i:=j;

 12   j:=k;

 13   dbms_output.put_line(k);

 14   end loop;

 15   end;

 16  /

0

1

1

2

3

5

8

13

21

PL/SQL procedure successfully completed.