Scribd
Upload
35 views7 pages

Dbms Exp5 Solution

The document creates tables, inserts sample data, and performs various queries on the tables. It creates three tables - sales_order, client_master, and product_master. It then inserts sample records into the tables and writes SQL queries to select, filter, aggregate and perform calculations on the data.

Uploaded by

Nishant Patil
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
35 views7 pages

Dbms Exp5 Solution

The document creates tables, inserts sample data, and performs various queries on the tables. It creates three tables - sales_order, client_master, and product_master. It then inserts sample records into the tables and writes SQL queries to select, filter, aggregate and perform calculations on the data.

Uploaded by

Nishant Patil
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 7

1.

Create following table:


Table name : sales_order
CREATE TABLE sales_order_9629(
order_no varchar(6) Primary key,
order_date date NOT NULL,
Client_no varchar(6) NOT NULL,
Dely_addr varchar(25),
Salesman_no varchar(6),
Dely_type char(5),
Billed_yn char(1),
Dely_date date,
Order_status varchar(10)
);
CREATE TABLE
client_master_9629( client_no
varchar(6), client_name varchar(20),
address varchar(30), city varchar(15),
pincode numeric(8), state1
varchar(15), due_bal numeric(10,2)
);
CREATE TABLE
product_master_9629( product_no
varchar(6), description varchar(15),
profit_percent numeric(4,2),
unit_measure varchar(10),
qty_on_hand varchar(8), reorder_level
numeric(8), sell_price numeric(8,2),
cost_price numeric(8,2)
);
2. Insert 5-6 records in table.
INSERT INTO client_master_9629 VALUES('C001','Sam','Kothrud','Pune',411077,'Maharashtra',10000.23);
INSERT INTO client_master_9629 VALUES('C002','John','Kurla','Mumbai',400087,'Maharashtra',50000.55);
INSERT INTO client_master_9629 VALUES('C003','Jack','Kishan Nagar','Gandhinagar',400055,'Gujarat',25000.93);
INSERT INTO client_master_9629 VALUES('C004','Ann','Charminar','Hydrabad',400090,'Andhra
Pradesh',77000.00);
INSERT INTO client_master_9629 VALUES('C005','Peter','Bandra','Mumbai',400045,'Maharashtra',88000.23);
INSERT INTO client_master_9629 VALUES('C006','Nick','Vasai','Mumbai',400077,'Maharashtra',10000.23);

INSERT INTO product_master_9629 VALUES('P1','Laptop',30.12,'low',1200,13000,60000.23,50000.34);


INSERT INTO product_master_9629 VALUES('P2','Refrigerator',30.92,'medium',4354,42255,42423.24,35000.55);
INSERT INTO product_master_9629 VALUES('P3','Hard Disk',10.00,'high',5678,8723,3500.00,2500.00);
INSERT INTO product_master_9629 VALUES('P4','Mixture',40.12,'low',2535,44000,10000.23,8000.34);
INSERT INTO product_master_9629 VALUES('P5','Washing Machine',35.12,'high',9000,90000,40000.99,28000.90);

insert into sales_order_9629 values('1','2022-02-11','C001','Link Road','S001','FAST','Y','2022-02-15','CONFIRMED');


insert into sales_order_9629 values('2','2022-01-13','C002','Link Road','S001','REG','Y','2022-02-14','CONFIRMED');
insert into sales_order_9629 values('3','2022-02-01','C003','Andheri','S002','FAST','N','2022-03-25','CANCELLED');
insert into sales_order_9629 values('4','2021-12-21','C004','Matunga','S004','REG','Y','2022-03-13','DELIVERY');
insert into sales_order_9629 values('5','2022-01-10','C005','Bandra','S003','REG','N','2022-04-25','CONFIRMED');
insert into sales_order_9629 values('6','2021-11-30','C006','Worli','S001','REG','Y','2022-02-15','DELIVERY');
select * from client_master_9629
select * from product_master_9629

select * from sales_order_9629

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

SELECT * FROM client_master_9629 WHERE SUBSTR(client_name, 2, 1) = 'a';

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

SELECT * FROM client_master_9629 WHERE city LIKE '_a%'

5. Find the list of all clients who stay in ‘mumbai’ ordered by their names

SELECT * FROM client_master_9629 WHERE city='Mumbai'


6. Print the list of clients whose bal_due is greater than value 10000

SELECT * FROM client_master_9629 WHERE due_bal > 10000

7. Print the information from sales_order table for orders placed in


themonth of January

SELECT * FROM sales_order_9629 WHERE TO_CHAR(order_date,'mm') = '01';

8. Display the order information for client_no C001 and C002

SELECT * FROM sales_order_9629 WHERE client_no IN('C001', 'C002');

9. Find the products whose selling price is greater than 2000 and less than
orequal to 5000

SELECT * FROM product_master_9629 WHERE sell_price > 2000 AND sell_price <= 5000;

10. Find the products whose selling price is more than 1500. Calculate
newselling price as original selling price * 1.5. Rename the new column
in the above query as new_price
SELECT product_no, sell_price * 1.5 AS new_price FROM product_master_9629 WHERE sell_price >
1500;

11. Count the total number of orders

SELECT COUNT(*) AS total_orders FROM sales_order_9629;

12. Calculate the average price of all the product

SELECT AVG(sell_price) as average_price FROM products;

13. Determine minimum and maximum product prices

SELECT MIN(sell_price) as min_price, MAX(sell_price) as max_price FROM product_master_9629;

14.Count the number of products having price greater than or equal to 1500

SELECT COUNT(*) as num_products FROM product_master_9629 WHERE sell_price >= 1500;


15. Display the order number and day on which clients placed their order

SELECT order_no , TO_CHAR(order_date , 'dd') as order_day FROM sales_order_9629;

16. Display the order_date in the format ‘dd-month-yy’


SELECT TO_CHAR(order_date, 'DD-MON-YY') as order_date_formatted FROM sales_order_9629;

17. Display the month (in alphabets) and date when the order must be
Delivered

SELECT TO_CHAR(dely_date, 'Month DD') as delivery_date_formatted FROM sales_order_9629;

18. Find the date, 15 days after today’s date


SELECT CURRENT_DATE + INTERVAL '15 days' as date_15_days_after_today;

19. Find the no. of days elapsed between today’s date and the delivery
dateof orders placed by the clients.

SELECT NOW()-dely_date AS days_elapsed FROM sales_order_9629;

You might also like