PRACTICL -3
Create a table : Product (prod_id, prod_name, prod_price, Brand_name, Quantity)
Create Command:
CREATE TABLE prd.product (
prod_id INT PRIMARY KEY,
prod_name VARCHAR(30),
prod_price DECIMAL(10,2),
brand_name VARCHAR(30),
quantity INT
);
Insert Command:
insert into prd.product values
('101','Laptop','45000.23','Dell','10'),
('102','Laptop','35000.32','Dell','6'),
('103','Laptop','50000.34','Lenevo','20'),
('104','Head phone','4000.00','Sony','30'),
('105','Head phone','2450.60','Boat','67'),
('106','Keyboard','1000.80','Lenevo','200'),
('107','Keyboard','569.89','Dell','200'),
('108','Mouse','300.80','HP','200'),
('109','Mouse','500.35','Intex','200'),
('110','Pen Drive','400.80','Kingston','200'),
('111','Pen Drive','700.00','Sandisk','200')
;
SELECT * FROM prd.product;
Queries:
1.Print the name of product which has quantity greater than 100.
SELECT prod_name
FROM prd.product
WHERE quantity > 100;
2. Print the prices and brand name of the laptop.
SELECT prod_price, brand_name
FROM prd.product
WHERE prod_name = 'Laptop';
3. Print all the brand name and product name of product.
SELECT brand_name, prod_name
FROM prd.product;
4. Print all brand name.
SELECT DISTINCT brand_name
FROM prd.product;
5.Print the total price.
SELECT SUM(prod_price) AS total_price
FROM prd.product;
6. Print the average price.
SELECT AVG(prod_price) AS average_price
FROM prd.product;
7. Print the maximum price.
SELECT MAX(prod_price) AS max_price
FROM prd.product;
8. Print the minimum price.
SELECT MIN(prod_price) AS min_price
FROM prd.product;
9. Print the total number of records in a table.
SELECT COUNT(*) AS total_records
FROM prd.product;
10. Print the total quantity of pen drive.
SELECT SUM(quantity) AS total_pen_drive_quantity
FROM prd.product
WHERE prod_name = 'Pen Drive';
11. Print the maximum price of laptop
SELECT MAX(prod_price) AS max_laptop_price
FROM prd.product
WHERE prod_name = 'Laptop';
12. Print the average price of keyboard:
SELECT AVG(prod_price) AS average_keyboard_price
FROM prd.product
WHERE prod_name = 'Keyboard';
13. Print the name of product in ascending order of their price.
SELECT prod_name
FROM prd.product
ORDER BY prod_price ;
14. Print the name and price of product in ascending order of
their price:
SELECT prod_name, prod_price
FROM prd.product
ORDER BY prod_price ;
15. Print the name of brand in descending order.
SELECT brand_name
FROM prd.product
ORDER BY brand_name DESC;
16. Print the product count of every brand.
SELECT brand_name, COUNT(*) AS product_count
FROM prd.product
GROUP BY brand_name;
17. Print the total quantity of every type of product
separately.
SELECT prod_name, SUM(quantity) AS total_quantity
FROM prd.product
GROUP BY prod_name;
18. Print the average price and product name of the every product separately:
SELECT prod_name, AVG(prod_price) AS average_price
FROM prd.product
GROUP BY prod_name;
19. Print the name and maximum price of every product in
ascending order of name.
SELECT prod_name, MAX(prod_price) AS max_price
FROM prd.product
GROUP BY prod_name
ORDER BY prod_name ;
20. Print the count of every product separately.
SELECT prod_name, COUNT(*) AS product_count
FROM prd.product
GROUP BY prod_name;
