QUESTION 14 Product Table Product Name 715 716 717 718 719 720 721 722 724 725...

Transcribed Image Text:

QUESTION 14 Product Table Product Name 715 716 717 718 719 720 721 722 724 725 726 727 728 729 730 731 732 733 735 736 737 738 Long Sleeve Logo Jersey. L Long-Sleeve Logo Jersey, XL HL Road Frame-Red. 62 P HL Road Frame-Red. 44 HL Road Frame-Red. 40 HL Road Frame-Red 52 HL Road Frame Red 56 LL Road Frame-Black. 58 LL Road Frame-Black, 60 LL Road Frame-Black, 62 LL Road Frame-Red. 44 LL Road Frame-Red 48 LL Road Frame-Red. 52 LL Road Frame-Red. 58 LL Road Frame-Red. 60 LL Road Frame-Red, 62 ML Road Frame Red 44 Mul 30.4923 49.99 ProductNumber MakeFlag FinishedGoodsFlag Color SafetyStockLevel ReorderPoint StandardCost ListPrice LJ-0192-L LJ-0192-X FR-R92R-62 FR-R92R-44 49.99 FR-R92R-40 1 FR-R92-52 1 FR R02R 56 FR-R388-58 FR-R358-60 1 1 1 FR-R388-62 FR-R3-44 1 FR-RR-48 1 FR-R38R-52 1 FR-R38R-G8 1 FR-R31-60 1 FR-R35R-42 1 FR R72R 44 1 FR-R72R-48 1 FR-R72-52 1 FR-R72R-58 1 QUESTION 15 0 0 1 1 1 1 1 1 1 FR-R72R-60 1 FR-RM-44 FR-R388-48 FR-R388-52 1 1 1 1 1 1 4 Mut 4 Red 500 Red 500 Red 500 1 Rad 500 Red 500 Black 500 Black DESK D Black 500 500 Red 500 Rad 500 Rad 500 Red 500 Red 500 Red 500 Red 500 Red 500 Road 500 Red 500 Red 500 Black 500 Black 500 Black 500 3 3 375 375 375 375 375 375 375 375 375 375 375 375 375 375 375 ML Road Frame-Red. 48 ML Road Frame-Red 52 ML Road Frame-Red. 58 ML Read Frame-Red. 60 LL Road Frame-Black, 44 LL Road Frame-Black, 48 LL Road Frame-Black. 52 Write SQL query to display productid, name, listprice and color of all products that has listprice less or equal to 50 and standerdcost is greater 200 For the toolbar, press ALT+F10 (PC) or ALT+FN+F10 (Mac). BIUS Paragraph V Arial 375 375 375 375 375 375 375 868.6342 868.6342 686342 8686342 V 868.6342 204.6251 204.6251 204.6251 187.1571 187.1571 1431.50 1431.50 1431.50 1431.50 1431.50 337.22 33722 www 337.22 337.22 10pt 337 22 337.22 337.22 337 22 337.22 504.83 584 83 187.1571 187.1571 187,1571 187.1571 352.1304 352,1394 352.1394 352.1394 352.1394 504.83 504.83 504.83 337 22 337.22 204.6251 204.6251 204.6261 337.22 AV Ix XO Q as IIII X X X > < 2 points Save Answer + ... O WORDS POWERED BY TINY Dorinte Person table QUESTION 15 Business EntityD PersonType NameStyle Title FirstName NULL Ken 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 EM EM EM EM EM EM EM EM EM EM EM EM EM EM EM 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 NULL Terri NULL Roberto NULL Rob Ma Gail Mr. Jossel NULL Dylan NULL Diane bisa.. MiddleName LastName Suffix EmailPromotion Snchez NULL 0 Duffy NULL 1 Tamburello NULL 0 NULL 0 Lee NULL NULL NULL Gigi NULL Michael NULL Ovidiu NULL Thierry Ma Janice NULL Michael I NULL Sharon B NULL Walters Erickson Goldberg Miller NULL 0 NULL 0 NULL 2 Margheim NULL 0 Matthew NULL 0 NULL 2 Raheem Cracium D'Hers Galvin Sullivan Salavaria NULL 0 NULL 2 NULL 2 NULL 2 NULL 2 Write SQL query to display persontype,firstname, lastname and emailpromotion of all person that has persontype EM or IN using IN operator and last name is Galvin For the toolbar, press ALT+F10 (PC) or ALT+FN+F10 (Mac). BIU Paragraph Arial 10pt Ev A Ix XQ EE X X B > < 2 points L+ Save Answer QUESTION 16 Sales OrderDetail table SalesOrderiD SalesOrderDetaillD 43659 43659 43659 43659 43659 43659 43659 43659 43659 43659 43659 43659 43660 43660 43661 43661 43661 43661 43661 43661 1 2 P 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 ProductID Carrier Tracking Number OrderQty ProductID 4911-403C-98 4911-403C-98 4911-403C-98 4911-403C-98 4911-403C-98 4911-403C-98 4911-403C-98 QUESTION 17 4911-403C-98 4911-403C-98 4911-403C-98 4911-403C-98 4911-403C-98 6431-4D57-83 6431-4D57-83 4E0A-4F89-AE 4E0A-4F89-AE 4E0A-4F89-AE 4E0A-4F89-AE 4E0A-4F89-AE 4E0A-4F89-AE 1 3 1 1 1 2 1 3 1 6 2 4 1 1 1 1 2 4 4 2 776 777 778 771 772 773 774 714 716 709 712 711 762 758 745 743 747 712 715 742 Special OfferID UnitPrice UnitPriceDiscount LineTotal 2024.994 0.00 2024.994 0.00 2024.994000 6074.982000 2024.994 0.00 2024.994000 2039.994 0.00 2039.994000 2039.994000 2039.994 0.00 2039.994 0.00 4079.988000 2039.994 0.00 2039 994000 28.8404 0.00 28.8404 5.70 0.00 0.00 5.1865 20.1865 0.00 0.00 419.4589 0.00 874.794 0.00 809.76 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 0.00 714.7043 0.00 714.7043 0.00 5.1865 0.00 28.8404 0.00 722.5949 0.00 1 Write SQL query to display sales order detail id, productid, order qty and unitprice discount from salesOrderDetail table that has orderqty between 2 to 6 and unit price discount not equal to 10 For the toolbar, press ALT+F10 (PC) or ALT+FN+F10 (Mac). BIUS Paragraph Arial 10pt 86.521200 28.840400 V 34.200000 10.373000 80.746000 419.458900 874.794000 809.760000 714.704300 1429.408600 20.746000 115.361600 1445.189800 Ev A Ix XQ Q5 X XG 2 points L+ Save Answer 2 points ... 0 WORDS POWERED BY TINY Save Answer QUESTION 17 CREATE TABLE dbo.Purchases (PurchaselD int identity, Purchaser varchar(100), Amount decimal(8,2), PurchasDate date); Write SQL insert statement to insert one row with proper values. You can insert any relavant value for each column. For the toolbar, press ALT+F10 (PC) or ALT+FN+F10 (Mac). BIUS Paragraph Arial P 10pt AV V Ix XQ Q EE X X B > < 2 points Save Answer O WORDS POWERED BY TINY QUESTION 18 LOCATION TABLE location_id street_address 1000 1100 1200 1300 1400 1500 1600 1700 1800 1900 2000 2200 2300 10 20 30 40 50 60 70 1297 Via Cola di Rie 80 90 100 93091 Calle della Testa 2017 Shinjuku-ku 9450 Kamiya-cho 2014 Jabberwocky Rd 2011 Interiors Blvd 2007 Zagora St 2004 Charade Rd DEPARTMENT TABLE department_id department_name Administration 147 Spadina Ave 6092 Boxwood St 40-5-12 Laogianggen 12-98 Victoria Street 199 Clementi North Marketing Purchasing Human Resources Shipping IT Public Relations Sales Executive Finance postal code city 00989 10934 1689 6823 200 201 114 203 121 103 204 145 100 108 26192 99236 50090 98200 M5V 2L7 YSW 9T2 190518 2901 540199 manager_id location_id 1700 1800 1700 2400 1500 1400 2700 2500 1700 1700 Roma Venice Tokyo Hiroshima Southlake South San Fra South Brunsw Seattle Toronto Whitehorse Beijing Sydney Singapore Based on the above tables, write sql statement using JOIN to display departmentid, department name, locationid, street address and city. Include also locations which belongs to city Seattle For the toolbar, press ALT+F10 (PC) or ALT+FN+F10 (Mac). BIUS Paragraph Arial 10pt Ev A v XQ Q=== V Ix X X X P> < 3 points Save Answer + ... QUESTION 14 Product Table Product Name 715 716 717 718 719 720 721 722 724 725 726 727 728 729 730 731 732 733 735 736 737 738 Long Sleeve Logo Jersey. L Long-Sleeve Logo Jersey, XL HL Road Frame-Red. 62 P HL Road Frame-Red. 44 HL Road Frame-Red. 40 HL Road Frame-Red 52 HL Road Frame Red 56 LL Road Frame-Black. 58 LL Road Frame-Black, 60 LL Road Frame-Black, 62 LL Road Frame-Red. 44 LL Road Frame-Red 48 LL Road Frame-Red. 52 LL Road Frame-Red. 58 LL Road Frame-Red. 60 LL Road Frame-Red, 62 ML Road Frame Red 44 Mul 30.4923 49.99 ProductNumber MakeFlag FinishedGoodsFlag Color SafetyStockLevel ReorderPoint StandardCost ListPrice LJ-0192-L LJ-0192-X FR-R92R-62 FR-R92R-44 49.99 FR-R92R-40 1 FR-R92-52 1 FR R02R 56 FR-R388-58 FR-R358-60 1 1 1 FR-R388-62 FR-R3-44 1 FR-RR-48 1 FR-R38R-52 1 FR-R38R-G8 1 FR-R31-60 1 FR-R35R-42 1 FR R72R 44 1 FR-R72R-48 1 FR-R72-52 1 FR-R72R-58 1 QUESTION 15 0 0 1 1 1 1 1 1 1 FR-R72R-60 1 FR-RM-44 FR-R388-48 FR-R388-52 1 1 1 1 1 1 4 Mut 4 Red 500 Red 500 Red 500 1 Rad 500 Red 500 Black 500 Black DESK D Black 500 500 Red 500 Rad 500 Rad 500 Red 500 Red 500 Red 500 Red 500 Red 500 Road 500 Red 500 Red 500 Black 500 Black 500 Black 500 3 3 375 375 375 375 375 375 375 375 375 375 375 375 375 375 375 ML Road Frame-Red. 48 ML Road Frame-Red 52 ML Road Frame-Red. 58 ML Read Frame-Red. 60 LL Road Frame-Black, 44 LL Road Frame-Black, 48 LL Road Frame-Black. 52 Write SQL query to display productid, name, listprice and color of all products that has listprice less or equal to 50 and standerdcost is greater 200 For the toolbar, press ALT+F10 (PC) or ALT+FN+F10 (Mac). BIUS Paragraph V Arial 375 375 375 375 375 375 375 868.6342 868.6342 686342 8686342 V 868.6342 204.6251 204.6251 204.6251 187.1571 187.1571 1431.50 1431.50 1431.50 1431.50 1431.50 337.22 33722 www 337.22 337.22 10pt 337 22 337.22 337.22 337 22 337.22 504.83 584 83 187.1571 187.1571 187,1571 187.1571 352.1304 352,1394 352.1394 352.1394 352.1394 504.83 504.83 504.83 337 22 337.22 204.6251 204.6251 204.6261 337.22 AV Ix XO Q as IIII X X X > < 2 points Save Answer + ... O WORDS POWERED BY TINY Dorinte Person table QUESTION 15 Business EntityD PersonType NameStyle Title FirstName NULL Ken 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 EM EM EM EM EM EM EM EM EM EM EM EM EM EM EM 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 NULL Terri NULL Roberto NULL Rob Ma Gail Mr. Jossel NULL Dylan NULL Diane bisa.. MiddleName LastName Suffix EmailPromotion Snchez NULL 0 Duffy NULL 1 Tamburello NULL 0 NULL 0 Lee NULL NULL NULL Gigi NULL Michael NULL Ovidiu NULL Thierry Ma Janice NULL Michael I NULL Sharon B NULL Walters Erickson Goldberg Miller NULL 0 NULL 0 NULL 2 Margheim NULL 0 Matthew NULL 0 NULL 2 Raheem Cracium D'Hers Galvin Sullivan Salavaria NULL 0 NULL 2 NULL 2 NULL 2 NULL 2 Write SQL query to display persontype,firstname, lastname and emailpromotion of all person that has persontype EM or IN using IN operator and last name is Galvin For the toolbar, press ALT+F10 (PC) or ALT+FN+F10 (Mac). BIU Paragraph Arial 10pt Ev A Ix XQ EE X X B > < 2 points L+ Save Answer QUESTION 16 Sales OrderDetail table SalesOrderiD SalesOrderDetaillD 43659 43659 43659 43659 43659 43659 43659 43659 43659 43659 43659 43659 43660 43660 43661 43661 43661 43661 43661 43661 1 2 P 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 ProductID Carrier Tracking Number OrderQty ProductID 4911-403C-98 4911-403C-98 4911-403C-98 4911-403C-98 4911-403C-98 4911-403C-98 4911-403C-98 QUESTION 17 4911-403C-98 4911-403C-98 4911-403C-98 4911-403C-98 4911-403C-98 6431-4D57-83 6431-4D57-83 4E0A-4F89-AE 4E0A-4F89-AE 4E0A-4F89-AE 4E0A-4F89-AE 4E0A-4F89-AE 4E0A-4F89-AE 1 3 1 1 1 2 1 3 1 6 2 4 1 1 1 1 2 4 4 2 776 777 778 771 772 773 774 714 716 709 712 711 762 758 745 743 747 712 715 742 Special OfferID UnitPrice UnitPriceDiscount LineTotal 2024.994 0.00 2024.994 0.00 2024.994000 6074.982000 2024.994 0.00 2024.994000 2039.994 0.00 2039.994000 2039.994000 2039.994 0.00 2039.994 0.00 4079.988000 2039.994 0.00 2039 994000 28.8404 0.00 28.8404 5.70 0.00 0.00 5.1865 20.1865 0.00 0.00 419.4589 0.00 874.794 0.00 809.76 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 0.00 714.7043 0.00 714.7043 0.00 5.1865 0.00 28.8404 0.00 722.5949 0.00 1 Write SQL query to display sales order detail id, productid, order qty and unitprice discount from salesOrderDetail table that has orderqty between 2 to 6 and unit price discount not equal to 10 For the toolbar, press ALT+F10 (PC) or ALT+FN+F10 (Mac). BIUS Paragraph Arial 10pt 86.521200 28.840400 V 34.200000 10.373000 80.746000 419.458900 874.794000 809.760000 714.704300 1429.408600 20.746000 115.361600 1445.189800 Ev A Ix XQ Q5 X XG 2 points L+ Save Answer 2 points ... 0 WORDS POWERED BY TINY Save Answer QUESTION 17 CREATE TABLE dbo.Purchases (PurchaselD int identity, Purchaser varchar(100), Amount decimal(8,2), PurchasDate date); Write SQL insert statement to insert one row with proper values. You can insert any relavant value for each column. For the toolbar, press ALT+F10 (PC) or ALT+FN+F10 (Mac). BIUS Paragraph Arial P 10pt AV V Ix XQ Q EE X X B > < 2 points Save Answer O WORDS POWERED BY TINY QUESTION 18 LOCATION TABLE location_id street_address 1000 1100 1200 1300 1400 1500 1600 1700 1800 1900 2000 2200 2300 10 20 30 40 50 60 70 1297 Via Cola di Rie 80 90 100 93091 Calle della Testa 2017 Shinjuku-ku 9450 Kamiya-cho 2014 Jabberwocky Rd 2011 Interiors Blvd 2007 Zagora St 2004 Charade Rd DEPARTMENT TABLE department_id department_name Administration 147 Spadina Ave 6092 Boxwood St 40-5-12 Laogianggen 12-98 Victoria Street 199 Clementi North Marketing Purchasing Human Resources Shipping IT Public Relations Sales Executive Finance postal code city 00989 10934 1689 6823 200 201 114 203 121 103 204 145 100 108 26192 99236 50090 98200 M5V 2L7 YSW 9T2 190518 2901 540199 manager_id location_id 1700 1800 1700 2400 1500 1400 2700 2500 1700 1700 Roma Venice Tokyo Hiroshima Southlake South San Fra South Brunsw Seattle Toronto Whitehorse Beijing Sydney Singapore Based on the above tables, write sql statement using JOIN to display departmentid, department name, locationid, street address and city. Include also locations which belongs to city Seattle For the toolbar, press ALT+F10 (PC) or ALT+FN+F10 (Mac). BIUS Paragraph Arial 10pt Ev A v XQ Q=== V Ix X X X P> < 3 points Save Answer + ...