Assignment2_solution.sql

use omila405;
#Q1:
select * from jbemployee;
#Q2:
select name from jbdept order by name;
#Q3:
SELECT * FROM jbitem where qoh=0;
#Q4:
select * from jbemployee where salary between 9000 and 10000;
#Q5:
select name,startyear-birthyear AS 'Age'from jbemployee;

#Q6:
select * from jbemployee where name regexp 'son,';
#Q7:
select * from jbitem where supplier = (select id from jbsupplier where name='Fisher-Price');
#Q8:
select jbitem.*, jbsupplier.name as supplierName  from jbitem 
join jbsupplier on jbitem.supplier=jbsupplier.id
where jbsupplier.name="Fisher-Price";
#Q9:
select city.id,city.name,sup.city from jbsupplier sup left join jbcity city on city.id=sup.city;
#Q10:
select name,color from jbparts where weight >(select weight from jbparts where name='card reader');
#Q11:
select p1.name,p1.color,p1.weight from jbparts p1 join jbparts p2 on p2.name='card reader' where p1.weight>p2.weight;
#Q12:
select AVG(weight) from jbparts where color='black';
#Q13:
select p.name as part_name,(sup.quan*p.weight) as total_weight,c.state from jbsupply sup left join jbparts p on sup.part=p.id 
	 join jbsupplier on jbsupplier.id=sup.supplier
     join jbcity c on c.id=jbsupplier.city
    where c.state='Mass';



#Q14:
create table item_copy as
	select * from jbitem where price < (select AVG(price) from jbitem) ;

#Q15:
create view item_copy_view as
	select * from jbitem where price < (select AVG(price) from jbitem) ;
#Q16:view is an virtial table and saves in views. It doesn't have rows(tuples) and columns(attributes) like tables.
#View depends on tables so it is dynamic, table is independant and static.
#Refrence:https://pediaa.com

#Q17:
create view view2 as
	select jbdebit.id,(jbsale.item*jbsale.quantity) as total_cost from jbdebit left join jbsale on jbdebit.id=jbsale.debit;

#Q18:
select debit , (jbsale.quantity*jbitem.price) as total from jbsale join jbitem on jbsale.item=jbitem.id;
#Q19:
SET SQL_SAFE_UPDATES = 0;
SET FOREIGN_KEY_CHECKS=0; -- to disable them
 DELETE jbsupplier
from jbsupplier 
INNER JOIN jbcity on jbsupplier.city = jbcity.id
where jbcity.name="Los Angeles";
SET FOREIGN_KEY_CHECKS=1; -- to re-enable them
SET SQL_SAFE_UPDATES = 1;

#Q20:
create view jbsale_supply2(supplier,item,quantity) AS
	select jbsupplier.name,jbitem.name,jbitem.qoh
	from jbsupplier,jbitem
	where jbsupplier.id=jbitem.supplier;