List of experiments-Database management system

D.b.m.s

DATA BASE MANAGEMENT SYSTEM (ETCS-357)

Exercise 1 (Data Creation)

a)

The table given in the book by “Ivan Bayross”.

a)

Client_master(client no. , name , address1 , address2 , city , pincode , state , bal_due)

b)

Product_master (product_no.,description , profit percent , unit_measure, qty_on_hand, reorder_lvl ,sell_price,cost_price)

c)

Salesman_master (salesman_no.,salesman_name , address1 , address2 , city , pincode, state , sal_amt ,tgt_to_get,ytd_sales,remarks )

d)

Sales_order (order_no.,order_date,client_no.,dely_addr , salesman_no.,dely_type , billed_yn , dely_date, order_status)

e)

Sales_order_details(order_no.,product_no. ,qty_ordered , qty_disp ,product_rate )

b)

Library tables

1)

IT_book (IT_book_code , IT_book_title , IT_book_author ,  IT_book_publ,IT_book_price, IT_book_eddi )

2)

IT_stud (IT_stud_code , IT_stud_name , IT_stud_branch , IT_stud_sem, IT_stud_card_no. , IT_stud_card_type)

3)

IT_issu_retu (IT_issu_doi , IT_issu_a_dor , IT_issu_fine ,  IT_book_code,IT_stud_code )

Exercise 2 (Data Retrieval)

)

Queries on the table given in the book by “Ivan Bayross”.

a)

Display all the fields from “client_master”.

b)

Display the order_no. and day on which the clients placed their orders.

c)

Display the months(in alphabets) and date when the order must be delivered.

d)

Display the order date in the format “DD-MM-YY”

e)

Find the number of days after today’s date.

f)

Find the no.of days elapsed between today’s date and the delivery date of the order placed by the clients.

)

Queries on “Library “table

a)

Display all the fields from the table “book”.

b)

Display the name of the students and date on which they have issued the books.

c)

Display the book title, author and publisher in the order of book title.

d)

Display the date of issue and the actual date of return in the format “DD-MM-YY.

e)

Display student name and fine respectively.

f)

Display the name of the students who have issued book having title “Database Management System” by any author.

Exercise 3 (Data Retrieval)

A) Queries on the table given in the book by “Ivan Bayross”.

a) Display all information about suppliers whose name begins with the letter ‘ja’.

b) Display specified client information for the clients who are not in ‘bombay’ or  ‘delhi’.

c) Display the product number and the total quantity ordered for the products ‘P0001’

   from sales_order_details table.

d) Display the information like order_no. , client_no. , order_date for all the orders placed by clients in the ascending order of date.The order_date should be displayed in DD-MM-YY format.

e) Retrieve the product_no. and the total quantity ordered for products’P0001’ ,‘P0004’ from sale_order_details table.

B) Queries on “Library “table

a) Display all the student’s details in the order of branch and library membership code.

b) Display publication,author and title in the descending order of publication , author and ascending order of their title.

c) Display the book title of those books whose price is not given.

d) Display the date of issue of those books that are not returned for last 6 months.

e) Display name of those students who have always returned books on time.

f) Display the books from ‘Tannenbaum’ which arepublished by ‘TMH’.

g) Display the details of those books which are moderately priced (if price between 1000 and 4000).

Exercise 4 (Data Retrieval)

A) Queries on the table given in the book by “Ivan Bayross”.

a)

Find out the products which have been sold to IVAN BAYROSS.

b)

Find out the products and their quantity that will haveto be delivered in the current month.

c)

Find out the product_no. and description of constantly sold i.e. rapidly moving product.

d)

Find the name of clients who have purchased CD Drive.

e)

List the product_no.,Order_no. of customers having qty_ordered less than 5 from sales_order_details table from the product ‘1.44 floppies’.

f)

Find the product and their quantity for the orders placed by ‘IVAN BAYROSS” and ‘VANDANA SAITWAL”

g)

Find the product and quantities for the orders placed by client_no. ‘C001’ and ‘C002’.

B) Queries on “Library “table.

a)

Find the book title issued to ‘Gaurav’.

b)

Find out the book title of those books which are having at least 20 copies and not a single issued.

c)

Find the details of the book that is issued maximum times.

d) Find the details of students who have taken books that are published by

   ‘TMH’.

e) Find name of the students and book title that are issued last month.

f) Find the title and price of those books that are scheduled for deposit this

    week.

g) Find publisher name for the book with the same title and different publications.

Exercise 5 (Sub Queries)

)

Create table according to the information given in the book by Ivan Bayross”.

       

a)

Find the product no. and description of non moving products i.e. not being sold.

b)

Find the customer name , address1, address2,city and pincode for the client who has placed order no. ‘1900’.

c)

Find the client name that has placed orders before the month of may 96.

d)

Find out if the product ‘1.55 Drive’ has been ordered by any client and print the client no., name to whom it was sold.

e)

Find the name of the client who has placed order worth Rs. 1000 or more.

B) Queries on “Library “table

a)  Find the book title and author whose price is above average price.

b)

Find the name of the student who has taken the book whose price is highest.

c)

Find the name of the students of ‘EC’ who has issued a book whose price is highest among the books issued by EC students.

d)

Find name of all students who have the costliest book issued in every branch.

e)

Find the author name, book title and price for the cheapest book of every author.

f)

Find the name of student who is from IT branch and has taken the costliest book by any publication.

Exercise 6 (Construction of Sentences)

A) Create table according to the information given in the book by “Ivan Bayross”.

a)  Print the information from product master,sales_order_details table in the

    following format for all the records

    | description | worth rs | total sales for the product |was sold

b)  Print the information from product master, sales_order _details table in the    

     following format for all the records

  | description | worth Rs | total sales for the product| was ordered in the month of     |order_date in month format.

c) Print the information from client_master , product master, sales order table in the following format for all the records.

Cust_name | has placed order | order_no. |order_date.

Exercise 7 (Queries on VIEWS)

a)

Create view of students containing student name, registration no., date of issue and fine.

b)

Create view a_detail contains all the details of IT students.

c)

Display the total fine for eacd IT student from view a_detail.

d)

Insert five records (2 of IT and 3 of CS in the view)

e)

Display the details of CS students which we have inserted today.

f)

Change the branch of the students who has got maximum fine from IT to MA in the view a_detail.

g)

Create 2 views cs_detail and ec_detail containing details of cs and ec students.

h)

Create a view CS_EC containing the details of CS and EC students whose name is ‘Gaurav’.

Exercise 8  (Queries on Sequences)

a)

Create a sequence inv_seq which will generate the numbers from 1 to 9999 in ascending order.

b)

Use the sequence inve_seq to generate values for inv_no column in the invoice _hdr table. Note that the invoice number must start with ‘I’.

Table name: invoice_hdr

Description: used to store the invoice information

Column name​Data type​Size​Remarks

Inv_no​Number​4​Primary key

Inv_dt​Date

Clientname​Varchar​20  

c)

Give the syntax to create a view vw_prod on the product_master table. Give example for inserting, updating and deleting record/s using the view vw_prod.

     Table name: prod_master

Description : used to store product information

Column name​Data type​Size​Attributes

Product_no​varchar2​6​Primary key/first letter must start with ‘P’

Description​Varchar2​5​Not null​

Profit_percent ​null​

Sell _price​Number​8,2​

Exercise 9  (Queries on PL/SQL)

a)

What are the components of PL/SQL code block.

b)

What is the maximum value that can be stored in a variable bound to a table column?

c)

Write a PL/SQL code block that will accept a number from the user and debit an amount of Rs.2000 from the account that has a minimum balance of 500 after the amount is debited. The process is to find account table.

Table name: accounts

Account_id​NAME​BAL

AC001​Anju​5000

AC002​Robert​10,000

AC003​Mita​5000​

AC004​Sunita​15,000​

AC005​Melba​10,000

d)

Write a PL/SQL code block to calculate the area of a circle for a value of radius varying from 3 to 7. Store the radius and the corresponding values of calculated area in a table.

Table Name: area

Radius​Area

e)

Write a PL/SQL block of code for inverting a number ‘102345’ and a string ‘MSIT’

f)

Write a PL/SQL block of code that first inserts a record in an ‘emp’ table. Update the salary of Blake and Clark by Rs.2500 and Rs.3000. Then check to see that the total salary doesn’t exceed 25000. If the total salary is greater than 25000 then undo the update made to the salaries of Blake and Clark.

Table name: emp

EMP_NO​EMP_NAME​SALARY

E001​Anju​5000​

E002​Harmy​10000

E003​Blake​5000​

E004​Jack​15000

E005​Clark​1000

Exercise 10  (Queries on Cursor)

Exercise on Implicit Cursor:

a) The HRD manager has decided to raise the salary of employee by 25%. Write a PL/SQL block code to acceptthe employee number and update the salary of that employee. Display appropriate message based on the existence of the record in the employee table.

o

By using SQL%FOUND

Exercise on Implicit Cursor :

The HRD Manager has decided to raise the salary for all employees in department no.20 by 25%. Whenever any such raise is given to employees , a record for the same is maintained in the emp_raise table. It includes the employee no. ,the date when the raise was given and the actual raise. Write a PL/SQL block to update the salary of each employee and insert a record in the emp_ raise table.

o

By using %ISOPEN

o

By using %FOUND

o

By using %NOTFOUND

o

Write a PL/SQL block code that will display the name,department and  salary of first five employees getting highest salary.(Use %ROWCOUNT)

o

By using FOR LOOP in cursors

[Syntax: FOR memory variable IN  cursorname]

Table:

Employee(emp_code varchar2(10) primary key, ename varchar2(20), deptno number(5), job varchar2(20), salary number(8,2))

Emp_raise(emp_code varchar2(10), raise_data date, raise_amt number(8,2));

Exercise 11  (Queries on Triggers)

Tables given in the book by “Ivan Bayross”

b)

Create a trigger product_i which should ensure that the product_no given at the time of insertion, in sales_order_master should be present in product_master.

c)

Create a trigger sales_u which should ensure that if client_no or order_no is modified in the sales_order table it should do the corresponding changes in the other table.

d)

Create a trigger product_d which should ensure that if a product is deleted from product master table all corresponding entries should be removed from the issued table.

e)

Create a trigger insert _s_order which should do the corresponding changes in product_master and salesman_master table at the time of insertion in sales_order table.

f)

Create a trigger client _u which should update the bal_due in client_master immediately when any bill of that client is cleared.

Library Tables:

a)

Create a trigger book_p which should ensure that the book_code present in the issued table should be present in book table.

b)

Create a trigger book_u which should ensure that the book_code or student_code is modified in the issue table it should do the corresponding changes in the other tables

c)

Create a trigger student_u which should ensure that the student _code  is modified in the student table then it should not allow it and display an error message.

d)

Create a trigger book_d which should ensure that if a book is deleted from book table all corresponding entries should be removed from issued table.

e)

Create a trigger insert_mess which should display the information about the organization and the status of library at the time of insertion in any table.