"L" Scheme - Sub Code: 25256
LAB EXERCISES
PART - A
1 Create a table “Customer” with the following fields: Id, Company name, Last name, First name, Address, City, State, Pin code and Perform the following operations
- Find the customer who does not have last name.
- Change the pin code of any customer
- Insert new record into the table
- Update the field’s First name and Last name into Name.
2. Implement the following Transaction Control Statements a. Commit b. Rollback c. Save point
3. 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 and rename client no with supplier no and name with supplier name.
- Insert data into client master.
- Insert data into suppliermaster from client master.
- Delete the selected row in the client master.
4. Create a table to show the salary details of the employees and perform the following operations
- Create an updatable view to modify and display the details of the employees for the above table.
- Grant select and update privileges on above table to other users.
- Grant all the privileges to some other users.
- Revoke all the above granted permissions.
5. Create two tables “Item details” with the fields of Item id,, Order Id, Part Id, Quantity, Total and Create a another table “Order Details: with the fields of Order id, Customer id, Order Date and
Perform the following operations
- Create the view that calculates the total for all the items in each order
- Create view to join order and corresponding line item information from the above two tables.
6. Create a table student with their elective paper as one field.
- Create another table staff with the subject names they have handled and number of times handled.
- Construct a English sentence to display the rows in the staff table.
- Use sub-queries to display the information about student's name and staff names where both have same subject names.
- Display the subject name, staff names where same subject is handled by more than one staff.
- Display the student, subject name where the subject is not at all handled any staff in the staff table.
7. Create two tables ”Department” and “Staff Details”. Apply a Master Transaction relationship between them.
8. 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.
- Add a new column for storing salesman number using ALTER command.
- Set the s_order_no as foreign key as column constraint
- Set the s_order_no as foreign key as table constraint
- Enforce the integrity rules using CHECK.
PART - B
- Create a table 'master_book' to contain the information of magazine code, magazine name, publisher, Weekly/biweekly/monthly, price. Write PL/SQL block to perform insert, update, delete operations on the above table.
- Create a table to store the salary details of the employees in a company. Declare the cursor id to contain employee number, employee name and net salary. Use cursor to update the employee
- Create a table to store the salary details of the employees in a company. Declare the cursor to contain Employee number, Employee name and Net salary. Use cursor to update update the employee salaries.
- Write a PL/SQL trigger to update the records while deleting the one record in another table.
- Create a table to contain phone number, user name, address of the phone user. Write a function to search for a address using phone number.
- Create a table 'stock' to contains the itemcode, itemname, current stock, date of last purchase. Write a stored procedure to seek for an item using itemcode and delete it, if the date of last purchase is before one year from the current date. If not, update the current stock.
- Write a PL/SQL block to handle built-in exception like No_DATA_FOUND and TOO_MANY_ROWS.
- Write a PL/SQL block to create and handle any two user defined exception.
PART – C - MINI PROJECT
Maximum 4 Students in a batch
Using any front end tool develop an application involving minimum 2 – 4 tables. Include the coding and output screens in the lab record.
Sub Code: 15055
Lab exercise:
- Execute and test all the SQL Editor commands.
- Work with the following conditions: Column formatting, alias and Column ordering.
- Use indexing on a table –simple and compound indexes.
- Work with the different categories of functions such as Math, Date, Aggregate and Conversion functions.
- 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.
- 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.
- 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
- 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
- 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
- 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.
- 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.
- 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.
- 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.
- Write a PL/SQL code to display the employee details for an employee.
- Write a PL/SQL code to calculate EB bill for the given units using if statement.
- Write a PL/SQL block to handle built-in exception like No_DATA_FOUND,TOO_MANY_ROWS.
- Write a PL/SQL block to create and handle user defined exception.
- Write a PL/SQL block to use procedure and function and get the result.
- Write a PL/SQL trigger to update the records while deleting the one record in another table.
- 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
- A Server with Oracle 8i or higher (Oracle 10g is preferred)
- 36 computers networked and configured with TCP/IP.
SOFTWARE REQUIRED
- SQL *PLUS
- Microsoft Visual Basic 6.0
