create schema dinning;


create table Staff (
EmId int not null auto_increment,
Name varchar(100) not null,
Salary decimal(8,2),
constraint PK_Staff primary key (EmId)
);

create table skills (
SkillId int not null auto_increment,
Name varchar(100) not null,
constraint PK_Skills primary key (SkillId)
);

create table StafSkills (
EmId int not null,
SkillId int not null,
constraint PK_StaffSkills primary key (EmId , SkillId),
constraint FK_StaffSkills_Staff foreign key (EmID)
references staff(EmId),
constraint FK_StaffSkills_Skills foreign key (SkillID)
references skills(SkillId)


);

create table Menus (
MenuId int not null auto_increment,
Description varchar(100) not null,
Type Varchar(100) not null,
constraint PK_Menus primary key (MenuId)
);

create table Dish (
DishId int not null auto_increment,
name Varchar(50)  not null,
Preptime int,
constraint PK_Dish primary key(DishId)
);

create table ingredients (
IngId int not null auto_increment,
name varchar(50) not null,
constraint PK_IngId primary key(IngId)
);

create table Dishingredients (
DishId int not null,
IngId int not null,
constraint PK_Dishingredients primary key(DishId, IngId),
constraint FK_Dishingredients_Dish foreign key (DishId) references Dish(DishId),
constraint Fk_Dishingredients_ingredients foreign key (IngId) references ingredients(IngId)
on delete cascade
on update restrict
);


create table menudish1 (
menuId int not null,
DishId int not null,
constraint PK_menudish1 primary key (menuId, DishId),
constraint FK_menudish1_menus foreign key (menuId)
references menus(menuId),
constraint FK_menuDish1_Dish foreign key(DishId)
references dish(DishId)
);


insert into dish(Name, Preptime)
Values('Taco',15),
('Southwesteren Corn', 30),
('Kabsah',120),
('Koppah',80),
('Kushri',80);

Insert into ingredients(name)
Values ('rice'),('chicken'),('onion'),('oil'),('macroni'),('Tomatoes'),('Spices'),('Cheese'),('bread'),('ground beef');

insert into dishingredients(DishId, IngId) values
(1 , 8), (1,9),(1,3),(1,10),(2,1),(2,7),(2,2),(2,5),(2,6),
(3,10),(3,3),(3,5),(3,6),(3,7),
(5,1),(5,2),(5,3),(5,4),(5,5),(5,6),(5,7);

insert into menus(Descreprtion,type) Values
('Spice taco sandwitch' , 'Dinner'),
('Red Rice Witch Chicken', 'lunch'),
('Ground beef grilled with borgol', 'All'),
('White ric with chicken' , 'Launch');

insert into menudish(menuId , DishId) values
(1,1),(1,2),
(2,3),
(3,4),(3,2),
(4,5);

select * from Staff;

alter table Staff
add column DOB date,
add column supervisorId int,
add constraint FK_staff_supervisor foreign key(supervisorId)
references staff(EmId);

update Staff set dob = '1988-01-22' where EmId = 1;
update staff set salary = 100000 where EmId = 4;

select staff.name, skills.name from staff
join StafSkills on StafSkills.EmId = staff.EmId
join Skills on StafSkills.SkillId = skills.SkillId
where skills.name = 'Customer Relationship';





===================================

select * from staff;
insert into staff(name, Salary)

Values('Adeeb Al Shakhs', 100.50),
('Mohammed hisham',6000),
('Thamer Jawish',8000),
('Hamza Mohamad', 7000);

insert into skills(name)
Values('Cheif'),
('Customer Relationship'),
('Desert expert');
select* from skills;

insert into StafSkills(EmId, SkillId)
Values(1,1), (1,2), (1,3),
(2,1),(2,2), (3,2),(3,3),(4,1),(4,2);

select * from StafSkills;


==========================




-- are there any products that cost more than $500?
select * from product_t where ProductStandardPrice > 500;

-- what is the total quantity that was sold in orderline_t?
select sum(OrderedQuantity) as 'total' from orderline_t;

-- how many times product 8 was sold?
select count(OrderedQuantity) from orderline_t where ProductID= 8;

-- what is the averege of net value for the product and the price?
select avg(ProductStandardPrice) from product_t;

-- what is the value of the most expensive product?
select max(ProductStandardPrice) from product_t;

select * from product_t order by ProductStandardPrice desc limit 1;

-- whats the value of the lease expensive product?
select min(ProductStandardPrice) as 'cheapeset' from product_t;

select * from product_t order by ProductStandardPrice asc limit 1;

-- provide an invoce that includes the product id and the descriptipon,
-- name of the productline, and the quantity ordered for all products
-- purchased by contemporary casuals on october 21st 2010.



select product_t.ProductID, product_t.ProductDescription,productline_t.ProductLineName,
 orderline_t.OrderedQuantity, customer_t.CustomerName, order_t.OrderDate from customer_t
join order_t on customer_t.CustomerID = order_t.CustomerID

join orderline_t on order_t.OrderID = orderline_t.OrderID
join product_t on orderline_t.ProductID = product_t.ProductID
join productline_t on product_t.ProductLineID = productline_t.ProductLineID
where customer_t.CustomerName = 'Contemporary Casuals'
and order_t.OrderDate= '2010-10-21';