Select s.SalesOrderDate, s.SaleOrderID, s.TotalAmount, s.Quantity,

m.MarketName, r.RegionName ,c.CustomerName, p.ProductName, f.ProductFamilyName,

a.AgentName, mm.ManagerName, mm.Commissions,

p.UnitPrice * s.Quantity as OrderAmount,

p.UnitPrice * s.Quantity * mm.PcntCommissions *0.01 as OrderCommissionsAmmount

FROM SalesOrder s, Customer c, Market m, Region r, Product p, ProductFamily f, Agent a, Manager mm

WHERE c.MarketID = m.MarketID and c.RegionID = r.RegionID and and c.CustomerID = s.CustomerID and s.ProductID= p.ProductID, and p.ProductFamilyID = f.ProductFamilyID and s.AgentID = a.AgentID and a.ManagerID = mm.ManagerID ;

Make sure OrderAmount and CommissionsAmmount are calculated.

SQL must return 50 records

2. Using SQL from question 1

Run :

Create view Week6_sales_vi as { put your SQL from Q1 here } ;

SQL FROM MY DATABASE IS BELOW

1.) insert into so_MARKET(MarketID, MarketName) values (10, 'Electronics'); insert into so_MARKET(MarketID, MarketName) values (20, 'Furniture'); insert into so_MARKET(MarketID, MarketName) values (30, 'Toys'); insert into so_MARKET(MarketID, MarketName) values (40, 'Cosmetics'); insert into so_MARKET(MarketID, MarketName) values (50, 'Apparel'); 2.) insert into so_REGION(Regionid, Regionname) values (10, 'N. America'); insert into so_REGION(Regionid, Regionname) values (20, 'Europe'); insert into so_REGION(Regionid, Regionname) values (30, 'Asia'); insert into so_REGION(Regionid, Regionname) values (40, 'Africa'); insert into so_REGION(Regionid, Regionname) values (50, 'S. America'); 3.) insert into so_PRODUCT_FAMILY(ProductFamilyid, Familyname) values (10, 'TVs and Videogames'); insert into so_PRODUCT_FAMILY(ProductFamilyid, Familyname) values (20, 'Sofas and Tables'); insert into so_PRODUCT_FAMILY(ProductFamilyid, Familyname) values (30, 'Collectibles'); insert into so_PRODUCT_FAMILY(ProductFamilyid, Familyname) values (40, 'Fragrances'); insert into so_PRODUCT_FAMILY(ProductFamilyid, Familyname) values (50, 'Clothing and Shoes'); 4.) insert into so_MANAGER( Managerid, Managername, CommissionPercent ) values (10, 'Tom Wrzask', 0.85); insert into so_MANAGER( Managerid, Managername, CommissionPercent ) values (20, 'Julia Bell', 0.19); insert into so_MANAGER( Managerid, Managername, CommissionPercent ) values (30, 'Hans Gruber', 0.12); insert into so_MANAGER( Managerid, Managername, CommissionPercent ) values (40, 'John Travolta', 0.12); insert into so_MANAGER( Managerid, Managername, CommissionPercent ) values (50, 'Shelly Stone', 0.12); 5.) insert into so_SALES_AGENT(Agentid, Managerid, Agentname) values (101, 10, 'Scooby'); insert into so_SALES_AGENT(Agentid, Managerid, Agentname) values (102, 20, 'Quickshot Jim'); insert into so_SALES_AGENT(Agentid, Managerid, Agentname) values (103, 30, 'Speedy Gonzalez'); insert into so_SALES_AGENT(Agentid, Managerid, Agentname) values (104, 40, 'Nikita Kruschev'); insert into so_SALES_AGENT(Agentid, Managerid, Agentname) values (105, 50, 'Jimmy Carter'); 6.) insert into so_PRODUCT( Productid, ProductFamilyid, ProductDescription ) values (110, 10, 'Halo'); insert into so_PRODUCT( Productid, ProductFamilyid, ProductDescription ) values (120, 10, 'Halo 2'); insert into so_PRODUCT( Productid, ProductFamilyid, ProductDescription ) values (130, 10, 'halo 3: Fall of Reach'); insert into so_PRODUCT( Productid, ProductFamilyid, ProductDescription ) values (140, 10, 'TitanFall'); insert into so_PRODUCT( Productid, ProductFamilyid, ProductDescription ) values (150, 10, 'VIZIO SQ650U'); insert into so_PRODUCT( Productid, ProductFamilyid, ProductDescription ) values (160, 10, 'SHARP 620U'); insert into so_PRODUCT( Productid, ProductFamilyid, ProductDescription ) values (170, 10, 'Playstation 5'); insert into so_PRODUCT( Productid, ProductFamilyid, ProductDescription ) values (180, 10, 'XBOX Series X'); insert into so_PRODUCT( Productid, ProductFamilyid, ProductDescription ) values (190, 20, 'Corner Couch'); insert into so_PRODUCT( Productid, ProductFamilyid, ProductDescription ) values (200, 20, 'Wooden Table'); insert into so_PRODUCT( Productid, ProductFamilyid, ProductDescription ) values (210, 20, 'Foldable Table'); insert into so_PRODUCT( Productid, ProductFamilyid, ProductDescription ) values (220, 30, 'Baseball Cards'); insert into so_PRODUCT( Productid, ProductFamilyid, ProductDescription ) values (230, 30, 'Basketball Cards'); insert into so_PRODUCT( Productid, ProductFamilyid, ProductDescription ) values (240, 40, 'Armani'); insert into so_PRODUCT( Productid, ProductFamilyid, ProductDescription ) values (250, 50, 'Nike Air'); 7.) insert into so_CUSTOMER( Customerid, Customername, Regionid, marketid ) values (1201, 'Jason Bourne', 10, 30); insert into so_CUSTOMER( Customerid, Customername, Regionid, marketid ) values (1212, 'James Bond', 10, 50); insert into so_CUSTOMER( Customerid, Customername, Regionid, marketid ) values (1213, 'Miley Cyrus', 10, 50); insert into so_CUSTOMER( Customerid, Customername, Regionid, marketid ) values (1214, 'Jessica Simpson', 10, 20); insert into so_CUSTOMER( Customerid, Customername, Regionid, marketid ) values (1215, 'Tom Wrzask', 10, 10); insert into so_CUSTOMER( Customerid, Customername, Regionid, marketid ) values (1216, 'Julia Bell', 50, 40); insert into so_CUSTOMER( Customerid, Customername, Regionid, marketid ) values (1217, 'Aerosmith', 50, 30); insert into so_CUSTOMER( Customerid, Customername, Regionid, marketid ) values (1218, 'Jack Black', 30, 40); insert into so_CUSTOMER( Customerid, Customername, Regionid, marketid ) values (1219, 'Bradely Cooper', 50, 10); insert into so_CUSTOMER( Customerid, Customername, Regionid, marketid ) values (1220, 'joe Shmoe', 10, 10); insert into so_CUSTOMER( Customerid, Customername, Regionid, marketid ) values (1221, 'John Doe', 10, 30); insert into so_CUSTOMER( Customerid, Customername, Regionid, marketid ) values (1222, 'Tim Cano', 50, 20); insert into so_CUSTOMER( Customerid, Customername, Regionid, marketid ) values (1223, 'Jessie Rodriguez', 20, 50); insert into so_CUSTOMER( Customerid, Customername, Regionid, marketid ) values (1224, 'Tequila', 20, 10); insert into so_CUSTOMER( Customerid, Customername, Regionid, marketid ) values (1225, 'The Rock', 10, 40); 8.) insert INTO so_ORDER( OrderID, OrderDate, CustomerID, ProductID, AgentID ) values (30000, '2010-11-27', 1220, 110, 103); insert INTO so_ORDER( OrderID, OrderDate, CustomerID, ProductID, AgentID ) values (30001, '2010-10-12', 1225, 110, 105); insert INTO so_ORDER( OrderID, OrderDate, CustomerID, ProductID, AgentID ) values (30002, '2010-05-13', 1215, 240, 101); insert INTO so_ORDER( OrderID, OrderDate, CustomerID, ProductID, AgentID ) values (30003, '2010-08-19', 1218, 180, 102); insert INTO so_ORDER( OrderID, OrderDate, CustomerID, ProductID, AgentID ) values (30004, '2010-04-21', 1220, 130, 103); insert INTO so_ORDER( OrderID, OrderDate, CustomerID, ProductID, AgentID ) values (30005, '2010-12-21', 1219, 210, 105); insert INTO so_ORDER( OrderID, OrderDate, CustomerID, ProductID, AgentID ) values (30006, '2010-05-11', 1224, 190, 102); insert INTO so_ORDER( OrderID, OrderDate, CustomerID, ProductID, AgentID ) values (30007, '2010-05-21', 1220, 130, 103); insert INTO so_ORDER( OrderID, OrderDate, CustomerID, ProductID, AgentID ) values (30008, '2010-01-19', 1211, 110, 101); insert INTO so_ORDER( OrderID, OrderDate, CustomerID, ProductID, AgentID ) values (30009, '2010-02-13', 1215, 250, 105); insert INTO so_ORDER( OrderID, OrderDate, CustomerID, ProductID, AgentID ) values (30010, '2012-02-28', 1217, 150, 105); insert INTO so_ORDER( OrderID, OrderDate, CustomerID, ProductID, AgentID ) values (30011, '2012-08-08', 1221, 160, 101); insert INTO so_ORDER( OrderID, OrderDate, CustomerID, ProductID, AgentID ) values (30012, '2012-08-09', 1222, 150, 105); insert INTO so_ORDER( OrderID, OrderDate, CustomerID, ProductID, AgentID ) values (30013, '2012-11-10', 1223, 140, 105); insert INTO so_ORDER( OrderID, OrderDate, CustomerID, ProductID, AgentID ) values (30014, '2012-07-13', 1219, 110, 101); insert INTO so_ORDER( OrderID, OrderDate, CustomerID, ProductID, AgentID ) values (30015, '2012-10-19', 1220, 190, 102); insert INTO so_ORDER( OrderID, OrderDate, CustomerID, ProductID, AgentID ) values (30016, '2012-11-09', 1221, 180, 104); insert INTO so_ORDER( OrderID, OrderDate, CustomerID, ProductID, AgentID ) values (30017, '2012-08-11', 1211, 220, 105); insert INTO so_ORDER( OrderID, OrderDate, CustomerID, ProductID, AgentID ) values (30018, '2012-07-13', 1214, 210, 104); insert INTO so_ORDER( OrderID, OrderDate, CustomerID, ProductID, AgentID ) values (30019, '2012-01-17', 1216, 200, 103); insert INTO so_ORDER( OrderID, OrderDate, CustomerID, ProductID, AgentID ) values (30020, '2014-08-19', 1218, 110, 101); insert INTO so_ORDER( OrderID, OrderDate, CustomerID, ProductID, AgentID ) values (30021, '2014-09-18', 1219, 250, 102); insert INTO so_ORDER( OrderID, OrderDate, CustomerID, ProductID, AgentID ) values (30022, '2014-10-17', 1218, 240, 104); insert INTO so_ORDER( OrderID, OrderDate, CustomerID, ProductID, AgentID ) values (30023, '2014-05-20', 1211, 180, 105); insert INTO so_ORDER( OrderID, OrderDate, CustomerID, ProductID, AgentID ) values (30024, '2014-05-21', 1212, 190, 105); insert INTO so_ORDER( OrderID, OrderDate, CustomerID, ProductID, AgentID ) values (30025, '2014-05-22', 1213, 200, 105); insert INTO so_ORDER( OrderID, OrderDate, CustomerID, ProductID, AgentID ) values (30026, '2014-05-09', 1223, 160, 102); insert INTO so_ORDER( OrderID, OrderDate, CustomerID, ProductID, AgentID ) values (30027, '2014-05-21', 1220, 130, 103); insert INTO so_ORDER( OrderID, OrderDate, CustomerID, ProductID, AgentID ) values (30028, '2014-12-24', 1216, 130, 103); insert INTO so_ORDER( OrderID, OrderDate, CustomerID, ProductID, AgentID ) values (30029, '2014-05-11', 1215, 110, 101); insert INTO so_ORDER( OrderID, OrderDate, CustomerID, ProductID, AgentID ) values (30030, '2016-06-23', 1214, 110, 101); insert INTO so_ORDER( OrderID, OrderDate, CustomerID, ProductID, AgentID ) values (30031, '2016-08-21', 1225, 180, 102); insert INTO so_ORDER( OrderID, OrderDate, CustomerID, ProductID, AgentID ) values (30032, '2016-12-21', 1211, 240, 105); insert INTO so_ORDER( OrderID, OrderDate, CustomerID, ProductID, AgentID ) values (30033, '2016-05-13', 1214, 220, 101); insert INTO so_ORDER( OrderID, OrderDate, CustomerID, ProductID, AgentID ) values (30034, '2016-11-19', 1220, 130, 103); insert INTO so_ORDER( OrderID, OrderDate, CustomerID, ProductID, AgentID ) values (30035, '2016-05-21', 1219, 150, 105); insert INTO so_ORDER( OrderID, OrderDate, CustomerID, ProductID, AgentID ) values (30036, '2016-08-16', 1213, 160, 104); insert INTO so_ORDER( OrderID, OrderDate, CustomerID, ProductID, AgentID ) values (30037, '2016-05-21', 1215, 200, 101); insert INTO so_ORDER( OrderID, OrderDate, CustomerID, ProductID, AgentID ) values (30038, '2016-05-06', 1225, 170, 102); insert INTO so_ORDER( OrderID, OrderDate, CustomerID, ProductID, AgentID ) values (30039, '2016-09-21', 1211, 110, 101); insert INTO so_ORDER( OrderID, OrderDate, CustomerID, ProductID, AgentID ) values (30040, '2018-05-05', 1211, 200, 101); insert INTO so_ORDER( OrderID, OrderDate, CustomerID, ProductID, AgentID ) values (30041, '2018-03-21', 1217, 210, 101); insert INTO so_ORDER( OrderID, OrderDate, CustomerID, ProductID, AgentID ) values (30042, '2018-05-09', 1215, 120, 101); insert INTO so_ORDER( OrderID, OrderDate, CustomerID, ProductID, AgentID ) values (30043, '2018-05-16', 1218, 170, 103); insert INTO so_ORDER( OrderID, OrderDate, CustomerID, ProductID, AgentID ) values (30044, '2018-12-19', 1212, 200, 102); insert INTO so_ORDER( OrderID, OrderDate, CustomerID, ProductID, AgentID ) values (30045, '2018-11-21', 1224, 250, 104); insert INTO so_ORDER( OrderID, OrderDate, CustomerID, ProductID, AgentID ) values (30046, '2018-01-21', 1213, 110, 101); insert INTO so_ORDER( OrderID, OrderDate, CustomerID, ProductID, AgentID ) values (30047, '2018-05-11', 1218, 150, 102); insert INTO so_ORDER( OrderID, OrderDate, CustomerID, ProductID, AgentID ) values (30048, '2018-08-19', 1219, 190, 103); insert INTO so_ORDER( OrderID, OrderDate, CustomerID, ProductID, AgentID ) values (30049, '2018-09-28', 1220, 130, 103); OrderID, OrderDate, CustomerID, ProductID, AgentID ) ;