employee table.sql

show databases;
use ecom;
show tables;

create table sales(
sale_id int primary key,
product_id int,
quantity_sold int,
sale_date date,
total_price decimal(10,2)
);

desc sales;

insert into sales(sale_id, product_id, quantity_sold, sale_date, total_price)
values(1,101,5,'2024-01-01',2500.00),
(2,102,3,'2024-01-02',900.00),
(3,103,2,'2024-01-02',60.00),
(4,104,4,'2024-01-03',80.00),
(5,105,6,'2024-01-03',90.00);

select * from sales;

create table products(
product_id int primary key,
product_name varchar(100),
category varchar(50),
unit_price decimal(10,2)
);

insert into products(product_id, product_name, category, unit_price) values
(101,'Laptop','Electronics',500.00),
(102,'Smartphone','Electronics', 300.00),
(103,'Headphones','Electronics',30.00),
(104,'Keyboard','Electronics',20.00),
(105,'Mouse','Electronics',15.00);

select * from products;

select product_name, unit_price from products;

select sale_id, sale_date from sales;

select * from products 
where category = "Electronics";

select sale_id, total_price from sales 
where sale_date = '2024-01-03';

select product_id, product_name from products
where unit_price>100;

select sum(total_price) as total_revenue
from sales;

select avg(unit_price) from products;

select sum(quantity_sold) from sales;

select sale_date, count(*) 
from sales
group by sale_date
order by sale_date;

select product_name, unit_price
from Products
order by unit_price desc
limit 1;

select sale_id, product_id, total_price
from Sales
where quantity_sold>4;

select product_name, unit_price
from Products
order by unit_price desc;

select round(total_price,2) from sales ;

select sale_id, date_format(sale_date, '%Y-%m-%d') as formatted_date
from sales;

select avg(total_price) from sales;

select sum(sales.total_price) as total_revenue
from sales
join products on sales.product_id = products.product_id
where products.category = "Electronics";

select product_name, unit_price
from products
where unit_price between 20 and 600;

select product_name, category
from products
order by category;

select sum(quantity_sold) from sales
join products on sales.product_id = products.product_id
where products.category = "Electronics";

select product_name , total_price from sales ;

show databases;
use ecom;
show tables;

select * from emp;

alter table emp add address varchar(10);

select * from emp;

desc emp;

alter table emp drop address;