Scribd
Upload
9 views3 pages

Day1 Exercise - Product

The document provides a product table with details such as product name, price, cost, and launch date, along with SQL queries to extract various information from the table. It includes queries for filtering products based on cost, profit, family, launch year, and specific naming conventions. Additionally, it describes the creation of the product table and the insertion of product records into it.

Uploaded by

Prasad Deshpande
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
9 views3 pages

Day1 Exercise - Product

The document provides a product table with details such as product name, price, cost, and launch date, along with SQL queries to extract various information from the table. It includes queries for filtering products based on cost, profit, family, launch year, and specific naming conventions. Additionally, it describes the creation of the product table and the insertion of product records into it.

Uploaded by

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

PRODUCT Table and related data

Consider the following product table. Product table contains the product name we sell, price of the
product, what it is the cost and on what date it launched. Write the SQL’s to answer the following
questions.

Prod_id P_name P_family price cost Launch_date


100 Marker Stationary 75 60 15-jan-18
101 Mouse Computer 1000 750 '16-apr-17'
102 Whiteboard Stationary 450 375 '20-aug-10'
103 Desktop Computer 20000 25000 '21-sep-10'
104 Laptop Computer 40000 45000 '21-dec-10'

1. Write the select statement which gives all the products which costs more than Rs 250.
select * from productTB1
where cost>250;

2. Write the select statement which gives product name, cost, price and profit. (profit formula is
price – cost)

select p_name,cost,price, price-cost"profit" from productTB1

3. Find the products which give more profit than product Mouse
select productTB1.*, (price-cost)"profit" from productTB1
where (price-cost)> (select (price-cost) from productTB1
where p_name='Mouse');

4. Display the products which give the profit greater than 100 Rs.
5.
select productTB1.*,(price-cost)"profit" from productTB1
where (price-cost)>100

6. Display the products which are not from Stationary and Computer family.

select * from productTB1


where p_family not in ('Stationary','Computer');

7. Display the products which give more profit than the p_id 102.

select productTB1.*, (price-cost)"profit" from productTB1


where (price-cost)>(select (price-cost) from productTB1
where p_id=102);
8. Display products which are launched in the year of 2010.

9. Display the products which name starts with either ‘S’ or ‘W’ and which belongs to Stationary
and cost more than 300 Rs

10. Display the products which are launching next month.

11. Display product name which has the maximum price in the entire product table.
select Max(price)"price" from productTB1

12. List the product name, cost, price, profit and percentage of profit we get in each product.

13. Display how many products we have in Computer family which has the price range between
2000 and 50000.

select count(*)"count",p_family from productTb1


where p_family='COMPUTER' and price between 2000 and 50000
group by p_family

create table product


(p_id number(4),
p_name varchar2(20),
p_family varchar2(20),
price number(5),
cost number(5),
launch_date date);

insert all
into product values(100,'Marker','stationary',75,60,'15-jan-18')
into product values(101,'Mouse','computer',1000,750,'16-apr-17')
into product values(102,'Whiteboard','stationary',450,375,'20-aug-10')
into product values(103,'desktop','computer',20000,25000,'21-sep-10')
into product values(104,'Laptop','computer',40000,45000,'21-dec-10')
select * from dual;

You might also like