SINHGAD INSTITUTE OF MANAGEMENT PUNE
ORACLE
Assignment No -5
MCA I DIV A & B
Q1. Consider the following table
Account_master (AcNo, Cust_name, Ac_type[Saving, Current], Status [A-Active, C-closed], Balance)
Account_transaction (AcNo, Ac_trn_type [W-withdrawal, D- deposit], Trn_date, trn_amount)
1. Create above tables with proper constraints.
2. Display customers, who have not done single transaction.
3. Store all customer records having Ac_type as ‘current’ into Cust_new table.
4. Display unique customer names.
5. Display customers wise total withdrawal amount.
6. Remove all customers having zero balance.
7. Display the customer name who has withdrawn maximum amount today.
8. Display the customer name that who has done more than one transaction today.
9. Display the customer name that who has not done any transaction from last six months.
10. Display the customer name who has not maintaining the minimum balance in their account. (minimum balance is 1000 Rs)
SINHGAD INSTITUTE OF MANAGEMENT PUNE
MCA I DIV A&B
Q1. Consider the following table
1. Purchase_order_header ( PONo, PO_date, Supplier_no, Total_amount)
2. Purchase_order_Detail (PONo, Item_no, Item_name,Qty,Rate)
Write SQL queries
1. Create above tables with proper constraints
2. Add column excepted_delivery_date to the table Purchase_order_Header table
3. Display total cost (qty*rate) of every purchase order.
4. Display PO_date as ‘10th March 2011, Tuesday’.
5. Write a query to count total number of suppliers.
6. Display item names having ‘M’ in them.
7. Display all purchase orders having total amount >25000.
8. Display all purchase orders whose excepted delivery date is the same month.
9. Count number of items in every purchase order.
10. Display purchase order details of items with 6 no. of characters in item name.
11. Display PoNo, PO_date, Supplier_no and item_name for all purchase orders.
SINHGAD INSTITUTE OF MANAGEMENT PUNE
ORACLE Assignment No -7
MCA I DIV A&B
Q1. Consider the following table
Salesperson (S_no, S_name, city, commission)
Customer ( cust_no, Cust_name, city, rating, S_no)
Order (O_no, O_amt, O_date, Cust_no,S_no)
1.Create above tables with proper constraints.
2.Find the average order amount.
3.List how many salespersons have booked orders in the order table.
4.List all customers serviced by Akash.
5.Find the order having minimum amount.
6.List the maximum amount of order taken by salesperson.