Oracle Assignments List with All  Solved Queries

 

1.  Create table Client_Master with following details:

Client_no varchar2(6)

Name Varchar2(20),

Address1 Varchar2(30),

Address2 Varchar(30),

City Varchar2(150,

State Varchar2(15),

Pincode Number(6),

Remarks Varchar2(60),

Bal_due Number(10,2)

2.   2.     Create Product_Master Table with following details:

Product_Number Varchar2(6),

Description Varchar2(25),

Profit_Percent Number(2,2),

Unit_Measure Varchar2(10),

Qty_on_hand Number(8),

Reorder_lvl Number(8),

Drll_Price Number(8,2),

Cost_Price Number(6,2)

3.     3.    Create Table Client_Master with Not Null Constraint on all Columns

        4. Create table Client_Master with Primary Key on Client_No column

5.          5.  Create table Sales_order_Details with following details:

S_Order_No Varchar2(6) Primary Key

          Product_NO Varchar2(6) Primary Key

          Qty_Ordered Number(8),

          Qty_Disp Number(8),

          Product_Rate Number(8,2)

6.     6. Create Client_Master table with Unique Constraint on Client_No Column.

7.    7.  Create Client_Master table with Check constraint on Client_No

Check Client No start with C alphabet,

Check Name in Upper case,

Check City in ‘New Delhi’,’Mumbai’, ‘Pune’, ‘Satara’, ‘Nagpur’,

8.     8. Create Sales__Order table with following details:

S_Order_No Varchar2(6) Primary Key

Client_No Varchar2(6) Foreign Key References Client_Master

Order_Date Date,

Total_amount Number(8,2),

Payment_Status Varchar2(20),

Shipping_Address Varchar2(30)

9.       9. Create Table Sales_Order_Details with

S_Order_No Primary Key, Foreign Key References S_Order_No from Sales_Order_Details,

Product_No Foreign Key on Product_No From Product_Master table

1    10.Insert 10 rows in each of above table

11  11.Find all the names of all the clients

1     12.Print entire client_master table

C        13.Retrieve the list of names and cities of all the clients

1      14.List all the products available from Product_Master table

        15.Find the names of all the clients having ‘a’ as second letter in their names

     16. Find out the clients who stay in a city whose second letter is ‘a’

        17.Find out the clients who stay in city ‘Mumbai’, or ‘Delhi’, Or ‘Madras’

        18.List all the clients who are located in ‘Mumbai’

        19.List all the clients whose bal_due are greater than 10000

        20. Print the information of sales_order table of orders placed in the month of January

       21. Find the products where selling price is greater than 2000 and less than or equals to 5000

      22. Find the products whose selling price is more than 1500 and also find new selling price as original price * 15.

       23. Rename the new column in above query as New_Price

       24. List all the products whose cost price is less than 1500

       25. List the products in sorted order of their description

       26. List all the orders that were placed in month January

        Set Function and concatenation

       27.  Count the total number of orders

        28. Calculate the average price of all the products

        29. Calculate minimum price of products

         Joining

       30. 

            PLSQL

        1.    

        

Views Examples

   1. Creating Books_View on Books Table

    2.    Creating View by Using Joins to Store Data from more than One Table

          

      Cursor Examples

2.    1.    Cursor Program to Retrieve Data from Books Table and Display the Result

 2.    Cursor to Display Total Amount of EVS Book in Books Table

   3.    Demonstration of Cursor in Oracle to Print Average Marks of Students 

    4.    Cursor in Oracle to Calculate Total Marks of Students 

2