LAB Manual

Sub Code: 15055

Lab exercise:

1. Execute and test all the SQL Editor commands.
2. Work with the following conditions: Column formatting, alias and Column ordering.
3. Use indexing on a table –simple and compound indexes.
4. Work with the different categories of functions such as Math, Date, Aggregate and Conversion functions.
5. a) Create a table to show the salary details of the employees.
   b) Grant select and update privileges on above table to other users.
   c) Grant all the privileges to some other users.
   d) Revoke all the above granted permissions.
6. a) Create a table student_master with the following field’s name, Regno, dept and year with suitable data types. Use Select command to do the following.
   b) Select the student's name column
   c) Display the unique rows
   d) Sort the table in alphabetical order
   c) Display the rows of the table in the sales order date wise,
   e) Select all the students of a particular department.
7. a) Create a table sales_order with s_order_no and product_no as primary key. Set other fields to store client number, delivery address, delivery date, order status.
   b) Add a new column for storing salesman number using ALTER command.
   c) Set the s_order_no as foreign key as column constraint
   d) Set the s_order_no as foreign key as table constraint
8. Develop a Visual Basic Application with suitable Labels & TextBoxes for the columns of a Table . Add Command Buttons to perform the following: Display Records, Previous, Next, First and Last. Use the ODBC driver to connect the Application with the oracle
9. Develop a Visual Basic Form with suitable Labels & TextBoxes for the columns of a Table . Add Command Buttons to perform the following: Insert record, Delete record, Update record, Locate Record
10. a) Create a table student with their elective paper as one field.
    b) create another table staff with the subject names they have handled and number of times handled.
    c) Construct a English sentence to display the rows in the staff table.
    d) Use sub-queries to display the information about student's name and staff names where both have same subject names.
    e) Display the subject name, staff names where same subject is handled by more than one staff.
    f) Display the student, subject name where the subject is not at all handled any staff in the staff table.
11. a) Create a sales_order table and client_master table with suitable fields.
    b) Find the total number of quantity ordered for a particular product.
    c) Display the rows of the table in the sales order date wise,
    d) Join the two tables and display the product number, product name, where the order_no in the sales_order table and order_no in client_master are equal.
    e) Join the sales_order table to itself and display the order number, client number and salesman number where client has been serviced by more than one salesman.
12. a) Create table sales_order_details with the s_order_no as primary key and with the profit_percent, sell_price, supplier_name
    b) Select each row and compute sell_price*.50 and sell_price *1.50 for each row selected.
    d) Select product_no, profit_percent, sell_price where profit_per is not between 10 and 20 both inclusive.
    e) Select product_no, description. profit_percent, sell_price where profit_percent is not between 20 and 30.
    f) Select the supplier_name and product_no where suppliemame has 'r' or 'h' as second character.
13. a) Create a table client_master with the following fields client_no, name, address 1 , address2,city, state, pincode, remarks, bal_due with suitable data types. Create another table supplier_table from client_master. Select all the fields andrename client_no with supplier_no and name with supplier_name.
    b) Insert data into client_master.
    c) Insert data into supplier_master from client_master.
    d) Delete the selected row in the client_master.
14. Write a PL/SQL code to display the employee details for an employee.
15. Write a PL/SQL code to calculate EB bill for the given units using if statement.
16.  Write a PL/SQL block to handle built-in exception like No_DATA_FOUND,TOO_MANY_ROWS.
17. Write a PL/SQL block to create and handle user defined exception.
18. Write a PL/SQL block to use procedure and function and get the result.
19. Write a PL/SQL trigger to update the records while deleting the one record in another table.
20. Create a table to store the salary details of the employees in a company. Declare the cursor id to contain employee ber, employee name and net salary. Use cursor to num update the employee salaries.

HARDWARE REQUIRED

1. A Server with Oracle 8i or higher (Oracle 10g is preferred)
2. 36 computers networked and configured with TCP/IP.

SOFTWARE REQUIRED

1. SQL *PLUS
2. Microsoft Visual Basic 6.0