oracle sql command for this oic gve steps
Here’s how you can approach and write Oracle SQL queries for the hands-on exercises shown in
your image. I’ll break down the steps and provide the structure for each query. Since full table
structures aren't given, I’ll use placeholder column names that are standard for such tables—
please adjust as per your schema.
Step-by-step Oracle SQL Commands
1. Table Data Computations
a. List the names of all clients having 'a' as the second letter in their names.
SELECT client_name
FROM Client_Master
WHERE SUBSTR(client_name, 2, 1) = 'a';
b. List clients who stay in a city whose first letter is 'M'.
SELECT client_name, city
FROM Client_Master
WHERE SUBSTR(city, 1, 1) = 'M';
c. All clients who stay in 'Bangalore' or 'Mangalore'.
SELECT client_name, city
FROM Client_Master
WHERE city IN ('Bangalore', 'Mangalore');
d. All clients whose BalDue is greater than 10,000.
SELECT client_name, BalDue
FROM Client_Master
WHERE BalDue > 10000;
e. All information from Sales_Order for orders in June.
SELECT *
FROM Sales_Order
WHERE TO_CHAR(order_date, 'MM') = '06';
f. Order info for ClientNo 'C00001' and 'C00002'.
SELECT *
FROM Sales_Order
WHERE ClientNo IN ('C00001', 'C00002');
g. Products with selling price >500 and <=750.
SELECT *
FROM Product_Master
WHERE selling_price > 500 AND selling_price <= 750;
h. Products with selling price >500. Calculate new selling price as original * 1.15.
SELECT *, selling_price * 1.15 AS new_price
FROM Product_Master
WHERE selling_price > 500;
i. Names, city, state of clients not in state 'Maharashtra'.
SELECT client_name, city, state
FROM Client_Master
WHERE state <> 'Maharashtra';
j. Count total number of orders.
SELECT COUNT(*) AS order_count
FROM Sales_Order;
k. Average price of all products.
SELECT AVG(selling_price) AS avg_price
FROM Product_Master;
l. Max & min product price, renamed as max_price and min_price.
SELECT MAX(selling_price) AS max_price, MIN(selling_price) AS min_price
FROM Product_Master;
m. Count of products with price <=500.
SELECT COUNT(*) AS count_products
FROM Product_Master
WHERE selling_price <= 500;
n. Products where QtyOnHand < reorder_level.
SELECT *
FROM Product_Master
WHERE QtyOnHand < reorder_level;
2. Date Manipulation
a. List order number and day (weekday) when clients placed orders.
SELECT order_no, TO_CHAR(order_date, 'DAY') AS order_day
FROM Sales_Order;
b. List the month (alphabets) and date when orders must be delivered.
SELECT order_no, TO_CHAR(delivery_date, 'Month DD') AS delivery_month_date
FROM Sales_Order;
c. OrderDate in format 'DD-Month-YY'.
SELECT TO_CHAR(order_date, 'DD-Month-YY') AS formatted_date
FROM Sales_Order;
d. Date, 15 days after today's date.
SELECT SYSDATE + 15 AS date_after_15_days
FROM dual;
General Steps to execute in Oracle SQL:
1. Connect to your Oracle database using SQL*Plus, SQL Developer, or any compatible tool.
2. Verify table and column names correspond to your actual schema; replace placeholders if
necessary.
3. Copy-paste and execute each query for its corresponding result.
If you need the queries in a table with explanations or further customization, let me know!
⁂
1. image.jpg
