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;