Query-1 Table Name: Client_Master Column Name Client_no Name Address1 Address2 City State Pincode Bal_due Data

Type Varchar2 Varchar2 Varchar2 Varchar2 Varchar2 Varchar2 Number Number Size 10 20 20 20 20 20 10 10,2 Atrribute Primary key and first letter should starts with c Not null

Not null

Table Name: Sales_order Column Name Orderno Clientno Orderdate Delyaddr Salesman_no Delytype Delydate Orderstatus Data Type Varchar2 Varchar2 Date Varchar2 Varchar2 Char Date Varchar2 Size 6 10 25 10 1 10 Attribute Primary key and first char is O Foreign key

Dellivery: Part(P)/Full(F) and default F Values in In process,Fulfilled,Backorder,Cancelled

1. Listing of the names of all clients having small letter 'a' as second letter in their names.
2. Display the order info and client number of client_no c01 and c02.

3. Count the total no of orders. 4. Write a PL/SQL program to swap two numbers without using third variable.

Query-2 Table Name: Client_Master Column Name Client_no Name Address1 Address2 City State Pincode Bal_due Data Type Varchar2 Varchar2 Varchar2 Varchar2 Varchar2 Varchar2 Number Number Size 10 20 20 20 20 20 10 10,2 Not null Atrribute Primary key and first letter should starts with c Not null

Table Name: Product_master Column Name Product_no Description Profit_percent Unit_measure Qty_on_hand Record_lvl Sell_price Cost_price Data Type Varchar2 Varchar2 Number Varchar2 Number Number Number Number Size 6 20 2,2 20 10 10 10,2 10,2 Not null, cant be 0 Not null, cant be 0 Attribute Primary Key and first letter should starts with P Not null Not null

1. List all clients whose balance due is greater than 10,000. 2. Listing of products whose selling price is more than 500 with the new selling price calculate as original selling price plus 15%. 3. .Change the city of client no 5 to bangalore. 4. Write a PL/SQL program to generate multiplication tables for 2,3,4 Query-3 Table Name: salesman_master

Column Name Salesman_id Name Address1 Address2 City State Pincode Sal_amt Targer_amt Remarks

Data Type Varchar2 Varchar2 Varchar2 Varchar2 Varchar2 Varchar2 Number Number Number Varchar2

Size 10 20 20 20 20 20 6 8,2

Attribute Primary key and first letter should starts with S Not null

Not null Should not null and zero

10,2 Should not null and zero 20

Table Name: Product_master Column Name Product_no Description Profit_percent Unit_measure Qty_on_hand Record_lvl Sell_price Cost_price Data Type Varchar2 Varchar2 Number Varchar2 Number Number Number Number Size 6 20 2,2 20 10 10 10,2 10,2 Not null, cant be 0 Not null, cant be 0 Attribute Primary Key and first letter should starts with P Not null Not null

1. Find all the products whose quality on hand is less than record level. 2. Find the names of the salesman who have salary=3000. 3. Delete all products from Product_Master where the qty_on_hand=100. 4. Write a PL/SQL program to check the given number is Palindrome or Not. Query- 4 Table Name: salesman_master Data Type Size Attribute

Column Name

Salesman_id Name Address1 Address2 City State Pincode Sal_amt Targer_amt Remarks

Varchar2 Varchar2 Varchar2 Varchar2 Varchar2 Varchar2 Number Number Number Varchar2

10 20 20 20 20 20 6 8,2

Primary key and first letter should starts with S Not null

Not null Should not null and zero

10,2 Should not null and zero 20

Table Name: Sales_order Column Name Orderno Clientno Orderdate Delyaddr Salesman_no Delytype Delydate Orderstatus Data Type Varchar2 Varchar2 Date Varchar2 Varchar2 Char Date Varchar2 10 Values in In process,Fulfilled,Backorder,Cancelled 25 10 1 Foreign key Dellivery: Part(P)/Full(F) and default F Size 6 10 Attribute Primary key and first char is O Foreign key

1. Print the information from sales order table for orders placed in the month of june. 2. Display the sales order no and day on which clients place their order. 3. Display the month and date when the order must be delivered. 4. Write a PL/SQL program to check the given string is palindrome or not. Query -5 Table Name: Product_master Column Name Data Type Size Attribute

Product_no Description Profit_percent Unit_measure Qty_on_hand Record_lvl Sell_price Cost_price

Varchar2 Varchar2 Number Varchar2 Number Number Number Number

6 20 2,2 20 10 10 10,2 10,2

Primary Key and first letter should starts with P Not null Not null

Not null, cant be 0 Not null, cant be 0

Table Name: sales_order_details Column Name Orderno Productno Qtyordered Qtydisp Productrate Data type Varchar2 Varchar2 Number Number Number Size 6 10 8 8 10,2 Attribute Primary key, Primary key, foreign key references as product_masterm table

1. Print the description and total quality sold for each product. 2. Finding the non-moving products i.e., products not been sold. 3. Change the size of sell_price column in product master to (12,2).
4.

Write a PL/SQL program to display sum of even and sum of odd numbers

Query -6

Table S(Sno varchar2(20) primary key check Sno like 'S %',Sname varchar2(20),City varchar2(20),Status number(10))

Table P(Pno varchar2(20) primary key check Pno like 'P%' ,Pname varchar2(20),Color varchar2(20),City varchar2(20),Weight number(10,2),Cost number(10,2)) Table SP(Sno varchar2(20) references S(Sno), Pno varchar2(20) references P(Pno),Qty number(10), primary key(Sno,Pno)

1. Get suppliers names from suppliers who supply at least one red part. 2. Get suppliers names for suppliers who do not supply part 'P2'. 3. Get supplier numbers for suppliers with status value less than the current max status value. 4. Write a PL/SQL program to evaluate whether the student is pass or fail.

Query-7

Table P(Pno varchar2(20) primary key check Pno like 'P%' ,Pname varchar2(20),Color varchar2(20),City varchar2(20),Weight number(10,2),Cost number(10,2))

Table SP(Sno varchar2(20) references S(Sno), Pno varchar2(20) references P(Pno),Qty number(10), primary key(Sno,Pno) 1. Using group by with having clause,get the part numbers for all the parts supplied by more than one supplier. 2. Get the total quantity of the part 'p2' supplied.
3. 4.

Get the part numbers ,whose parts are not in Red color,from London. What is a sequence? Write its syntax?Write a program for creating a sequence?

Query-8

Table S(Sno varchar2(20) primary key check Sno like 'S %',Sname varchar2(20),City varchar2(20),Status number(10)) Table P(Pno varchar2(20) primary key check Pno like 'P%' ,Pname varchar2(20),Color varchar2(20),City varchar2(20),Weight number(10,2),Cost number(10,2)) Table SP(Sno varchar2(20) references S(Sno), Pno varchar2(20) references P(Pno),Qty number(10), primary key(Sno,Pno)

1. Find the pnames of parts supplied by london supplier and by no one else. 2. Find the sno of suppliers who supply only red parts. 3. Get the part color,supplied by the supplier 's1'.
4.

Write a PL/SQL program to display sum of even and sum of odd numbers

Query-9

Table DEPT(Deptno varchar2(10) primary key,Dname varchar2(20),Loc varchar2(20)) Table EMP(Empno varchar2(10) primary key, Ename varchar2(20), Job varchar2(20), Mgr varchar2(20), Hiredate date, Sal number(10,2), Comm number(10,2), Deptno varchar2(10) references dept)

1.

What is a stored procedure? explain with syntax? Write a Procedure to increase the salary for all the employees in the EMP table