ASSIGNMENT:- 2
Create the below table orders with the following attributes i) Id, ii) Cust_name, iii) Product, iv) Quantity,
v) Price
QUARY
1. Create and insert at least 5 records into the table.
2. Find out the total number of price of all product.
3. Find out the order details of the customer whose name is ‘soham’.
4. Find out the name of the product which have lowest price.
5. Find out the name of the product which have highest price.
6. Delete the order details of the order id=103.
7. Update the quantity from 2-5 of the order id=101.
8. Find out the total number of records of the table.
9. Find out the name of the customer hose name start with ‘s’.
10. Drop the table.
SQL> create table orders(order_id number(10), cust_name char(20), product varchar2(30), quantity number(10), prize
decimal(10,5));
Table created.
SQL> insert into orders values(&order_id,'&cust_name','&product',&quantity,&prize); Enter
value for order_id: 101
Enter value for cust_name: Maman Kar Enter
value for product: Laptop
Enter value for quantity: 9 Enter
value for prize: 800.98
old 1: insert into orders values(&order_id,'&cust_name','&product',&quantity,&prize) new
1: insert into orders values(101,'Maman Kar','Laptop',9,800.98)
1 row created. SQL> /
Enter value for order_id: 102
Enter value for cust_name: Soham Sarkar Enter
value for product: Mouse
Enter value for quantity: 7 Enter
value for prize: 300.45
old 1: insert into orders values(&order_id,'&cust_name','&product',&quantity,&prize) new
1: insert into orders values(102,'Soham Sarkar','Mouse',7,300.45)
1 row created. SQL> /
Enter value for order_id: 103
Enter value for cust_name: DM. Ghosh Enter
value for product: Desk
Enter value for quantity: 3 Enter
value for prize: 400.97
old 1: insert into orders values(&order_id,'&cust_name','&product',&quantity,&prize) new
1: insert into orders values(103,'DM. Ghosh','Desk',3,400.97)
1 row created. SQL> /
Enter value for order_id: 104
Enter value for cust_name: Ani Shinha Enter
value for product: Mobile
Enter value for quantity: 2 Enter
value for prize: 400.90
old 1: insert into orders values(&order_id,'&cust_name','&product',&quantity,&prize) new
1: insert into orders values(104,'Ani Shinha','Mobile',2,400.90)
1 row created. SQL> /
Enter value for order_id: 105
Enter value for cust_name: MR. Basu Enter
value for product: Keyboard Enter
value for quantity: 5
Enter value for prize: 450.87
old 1: insert into orders values(&order_id,'&cust_name','&product',&quantity,&prize)new 1: insert into orders values(105,'MR.
Basu','Keyboard',5,450.87)
1 row created.
SQL> select * from orders;
ORDER_ID CUST_NAME PRODUCT QUANTITY PRIZE
101 Maman Kar Laptop 9 800.98
102 Soham Sarkar Mouse 7 300.45
103 DM. Ghosh Desk 3 400.97
104 Ani Shinha Mobile 2 400.9
105 MR. Basu Keyboard 5 450.87
SQL> select sum(prize) from orders;
SUM(PRIZE)
2354.17
SQL> select * from orders where cust_name='Soham Sarkar';
ORDER_ID CUST_NAME PRODUCT QUANTITY PRIZE
102 Soham Sarkar Mouse 7 300.45
SQL> select min(price)as lowest from orders;
MIN(PRICE)
300.45
SQL> select max(price)as lowest from orders;
MAX(PRICE)
450.87
SQL> delete from orders where order_id=103;
1 row deleted.
SQL> select * from orders;
ORDER_ID CUST_NAME PRODUCT QUANTITY PRIZE
101 Maman Kar Laptop 9 800.98
102 Soham Sarkar Mouse 7 300.45
104 Ani Sinha Mobile 2 400.9
105 MR. Basu Keyboard 5 450.87
SQL> update orders set quantity =4 where order_id=101;
1 row updated.
SQL> select * from orders;
ORDER_ID CUST_NAME PRODUCT QUANTITY PRIZE
101 Maman Kar Laptop 4 800.98
102 Soham Sarkar Mouse 7 300.45
104 Ani Sinha Mobile 2 400.9
105 MR. Basu Keyboard 5 450.87
SQL> select count(*) from orders;
COUNT(*)
4
SQL> select cust_name from orders where cust_name like 'S%';
CUST_NAME
Soham Sarkar
SQL> commit;
Commit complete.
ASSIGNMENT:- 3
Create the below table using SQL
QUARY:-
1. Show all employee name and department.
2. List employee with salary more than 50000.
3. List employee with salary > 48000 and salary<56000.
4. Find the employee whose joindate after 1st January 2021.
5. Count the total number of employee.
6. Find the average salary of all employee.
7. Find the minimum and maximum salary .
8. List the employee who work on HR department.
9. Show department wise total salary.
10. Find department wise employee.
11. Find the employee whose name start with ‘D’ or ‘E’.
12. Find the name of the employee with there salary in descending order.
SQL> create table employees(emp_id number(10),name char(20),department varchar2(10),salary number(20),joindate
varchar2(30));
Table created.
insert into employees values(&emp_id number,'&name','&department',&salary,'&joindate'); SQL>
insert into employees values(&emp_id,'&name','&department',&salary,'&joindate'); Enter
value for emp_id: 101
Enter value for name: Alice Enter
value for department: HR Enter
value for salary: 50000
Enter value for joindate: 2020-01-15
old 1: insert into employees values(&emp_id,'&name','&department',&salary,'&joindate') new
1: insert into employees values( 101,'Alice','HR', 50000,' 2020-01-15')
1 row created. SQL> /
Enter value for emp_id: 102 Enter
value for name: Bob Enter
value for department: IT Enter
value for salary: 60000
Enter value for joindate: 2019-03-15
old 1: insert into employees values(&emp_id,'&name','&department',&salary,'&joindate') new
1: insert into employees values(102,'Bob','IT',60000,'2019-03-15')
1 row created. SQL> /
Enter value for emp_id: 103 Enter
value for name: Charlie Enter
value for department: IT Enter
value for salary: 55000
Enter value for joindate: 2021-07-12
old 1: insert into employees values(&emp_id,'&name','&department',&salary,'&joindate') new
1: insert into employees values(103,'Charlie','IT',55000,'2021-07-12')
1 row created. SQL> /
Enter value for emp_id: 104
Enter value for name: David Enter
value for department: Sales Enter
value for salary: 45000
Enter value for joindate: 2020-11-30
old 1: insert into employees values(&emp_id,'&name','&department',&salary,'&joindate') new
1: insert into employees values(104,'David','Sales',45000,'2020-11-30')
1 row created. SQL> /
Enter value for emp_id: 105 Enter
value for name: Eva Enter
value for department: HR Enter
value for salary: 52000
Enter value for joindate: 2022-05-10
old 1: insert into employees values(&emp_id,'&name','&department',&salary,'&joindate') new
1: insert into employees values(105,'Eva','HR',52000,'2022-05-10')
1 row created.
SQL> select * from employees;
EMP_ID NAME DEPARTMENT SALARY JOINDATE
101 Alice HR 50000 2020-01-15
102 Bob IT 60000 2019-03-20
103 Charlie IT 55000 2021-07-12
104 David Sales 45000 2020-11-30
105 Eva HR 52000 2022-05-10
SQL> select name,department from employees;
NAME DEPARTMENT
Alice HR
Bob IT
Charlie IT
David Sales
Eva HR
SQL> select * from employees where salary>50000;
EMP_ID NAME DEPARTMENT SALARY JOINDATE
102 Bob IT 60000 2019-03-20
103 Charlie IT 55000 2021-07-12
105 Eva HR 52000 2022-05-10
SQL> select * from employees where salary>48000 and
salary<56000; EMP_ID NAME DEPARTMENT
SALARY JOINDATE
101 Alice HR 50000 2020-01-15
103 Charlie IT 55000 2021-07-12
105 Eva HR 52000 2022-05-10
SQL>Select * from employees whose joindate < 2021-01-01;
EMP_ID NAME DEPARTMENT SALARY JOINDATE
103 Charlie IT 55000 2021-07-12
105 Eva HR 52000 2022-05-10
SQL> select count(*) from employees;
COUNT(*)
5
SQL> select avg(salary) from employees;
AVG(SALARY)
52400
SQL> select min(salary),max(salary) from employees;
MIN(SALARY) MAX(SALARY)
45000 60000
SQL> select * from employees where department='HR';
EMP_ID NAME DEPARTMENT SALARY JOINDATE
101 Alice HR 50000 2020-01-15
105 Eva HR 52000 2022-05-10
SQL> select department, sum(salary) from employees group by
department; DEPARTMENT SUM(SALARY)
IT 115000
HR 102000
Sales 45000
SQL> select department,count(*) from employees group by department;
DEPARTMENT COUNT(*)
IT 2
HR 2
Sales 1
SQL> select * from employees where name like 'D%' or name like 'E%';
EMP_ID NAME DEPARTMENT SALARY JOINDATE
104 David Sales 45000 2020-11-30
105 Eva HR 52000 2022-05-10
SQL> select name,salary from employees order by salary desc;
NAME SALARY
Bob 60000
Charlie 55000
Eva 52000
Alice 50000
David 45000
SQL> commit;
Commit complete.
ASSIGNMENT:- 4
Create the below table using SQL
QUARY:-
1. List all the product with category and price.
2. Show the product with price > 10000
3. Count the total number of product.
4. Count the category wise total number of product.
5. Find the most expensive product.
6. List product with stock < 10.
7. Calculate average price of electronics category.
8. Show category wise total stock.
9. Display product name and price in descending order.
10. Find all products whose name start with L.
11. Display the product with second highest price.
SQL> create table products(product_id number(10),product_name varchar2(30),category varchar2(30),price
decimal(10,4),stock number(20));
Table created.
SQL> insert into products values(&product_id, '&product_name', '&category', &price, &stock); Enter
value for product_id: 201
Enter value for product_name: Laptop Enter
value for category: Electronics Enter
value for price: 55000.00
Enter value for stock: 10
old 1: insert into products values(&product_id, '&product_name', '&category', &price, &stock) new
1: insert into products values(201, 'Laptop', 'Electronics', 55000.00, 10)
1 row created. SQL> /
Enter value for product_id: 202
Enter value for product_name: Smartphone Enter
value for category: Electronics
Enter value for price: 25000.00 Enter
value for stock: 30
old 1: insert into products values(&product_id, '&product_name', '&category', &price, &stock) new
1: insert into products values(202, 'Smartphone', 'Electronics', 25000.00, 30)
1 row created. SQL> /
Enter value for product_id: 203
Enter value for product_name: Desk Chair Enter
value for category: Furniture
Enter value for price: 4000.00 Enter
value for stock: 15
old 1: insert into products values(&product_id, '&product_name', '&category', &price, &stock) new
1: insert into products values(203, 'Desk Chair', 'Furniture', 4000.00, 15)
1 row created. SQL> /
Enter value for product_id: 204
Enter value for product_name: Notebook
Enter value for category: Stationery
Enter value for price: 50.00
Enter value for stock: 200
old 1: insert into products values(&product_id, '&product_name', '&category', &price, &stock) new
1: insert into products values(204, 'Notebook', 'Stationery', 50.00, 200)
1 row created. SQL> /
Enter value for product_id: 205
Enter value for product_name: LED TV Enter
value for category: Electronics Enter
value for price: 40000.00
Enter value for stock: 5
old 1: insert into products values(&product_id, '&product_name', '&category', &price, &stock) new
1: insert into products values(205, 'LED TV', 'Electronics', 40000.00, 5)
1 row created. SQL> /
Enter value for product_id: 206 Enter
value for product_name: Pen Enter
value for category: Stationary Enter
value for price: 10.00
Enter value for stock: 500
old 1: insert into products values(&product_id, '&product_name', '&category', &price, &stock)
new 1: insert into products values(206, 'Pen', 'Stationary', 10.00, 500)
1 row created.
SQL> /
Enter value for product_id: 207
Enter value for product_name:
Table Enter value for category:
Furniture Enter value for price:
7000.00
Enter value for stock: 7
old 1: insert into products values(&product_id, '&product_name', '&category', &price, &stock)
new 1: insert into products values(207, 'Table', 'Furniture', 7000.00, 7)
1 row created.
SQL> /
Enter value for product_id: 208
Enter value for product_name:
Headphone Enter value for category:
Electronics Enter value for price:
3000.00
Enter value for stock: 25
old 1: insert into products values(&product_id, '&product_name', '&category', &price, &stock)
new 1: insert into products values(208, 'Headphone', 'Electronics', 3000.00, 25)
1 row created.
SQL> /
Enter value for product_id: 209
Enter value for product_name: Bookshelf
Enter value for category: Furniture
Enter value for price: 6500.00
Enter value for stock: 8
old 1: insert into products values(&product_id, '&product_name', '&category', &price, &stock)
new 1: insert into products values(209, 'Bookshelf', 'Furniture', 6500.00, 8)
1 row created.
SQL> /
Enter value for product_id: 210
Enter value for product_name: Marker
Enter value for category: Stationary
Enter value for price: 20.00
Enter value for stock: 350
old 1: insert into products values(&product_id, '&product_name', '&category', &price, &stock)
new 1: insert into products values(210, 'Marker', 'Stationary', 20.00, 350)
1 row created.
SQL> select * from products;
PRODUCT_ID PRODUCT_NAME CATEGORY PRICE STOCK
201 Laptop Electronics 55000 10
202 Smartphone Electronics 25000 30
203 Desk Chair Furniture 4000 15
204 Notebook Stationery 50 200
205 LED TV Electronics 40000 5
206 Pen Stationary 10 500
207 Table Furniture 7000 7
208 Headphone Electronics 3000 25
209 Bookshelf Furniture 6500 8
210 Marker Stationary 20 350
SQL> select * from products where price > 10000;
PRODUCT_ID PRODUCT_NAME CATEGORY PRICE STOCK
201 Laptop Electronics 55000 10
202 Smartphone Electronics 25000 30
205 LED TV Electronics 40000 5
SQL> select count(*) from products;
COUNT(*)
10
SQL> select category, count(*) from products group by category;
CATEGORY COUNT(*)
Stationery 1
Stationary 2
Electronics 4
Furniture 3
SQL> select * from products where price =(select max(price) from products);
PRODUCT_ID PRODUCT_NAME CATEGORY PRICE STOCK
201 Laptop Electronics 55000 10
SQL> select * from products where stock < 10 ;
PRODUCT_ID PRODUCT_NAME CATEGORY PRICE STOCK
205 LED TV Electronics 40000 5
207 Table Furniture 7000 7
209 Bookshelf Furniture 6500 8
SQL> select avg(price) from products where category ='Electronics';
AVG(PRICE)
30750
SQL> select category, sum(stock) from products group by category;
CATEGORY SUM(STOCK)
Stationery 200
Stationary 850
Electronics 70
Furniture 30
SQL> select product_name,price from products order by price desc;
PRODUCT_NAME PRICE
Laptop 55000
LED TV 40000
Smartphone 25000
Table 7000
Bookshelf 6500
Desk Chair 4000
Headphone 3000
Notebook 50
Marker 20
Pen 10
SQL> select * from products where product_name like 'L%' or product_name like 'B%';
PRODUCT_ID PRODUCT_NAME CATEGORY PRICE STOCK
201 Laptop Electronics 55000 10
205 LED TV Electronics 40000 5
209 Bookshelf Furniture 6500 8
SQL> select * from products where price=(select max(price) from products where price <(select max(price) from
products));
PRODUCT_ID PRODUCT_NAME CATEGORY PRICE STOCK
205 LED TV Electronics 40000 5
SQL> commit;
Commit complete.
