I need help making the SQL statements in visual studio 2017 Please read homework carefully

svxGS0201 9 1 0/Project%20Task%2075628 1 %29.pdf MGMT 329: Database Management Project Task 7: Requi The most advance quenes have becn provided for you. Now it's your turn to write SQL queries on your own Your task is to write the SQL state ements to produce the reports needed. Creatc a Microsoft Word document containing your sQL statements for each of the following reports and upload your document to UNM Leam We want a report that will list all the categories in our database and the total number of products we have for each category. Generate a report that will display the category ID, category name and the total number of products for each eategory, Note: There are an equal mumber of products under each category in our databas We want a report that will let us know who our repeat customers are. We want to be able to track customer loyalty and peovide benefits to these customens who have made our company a success. Generate a repoet that will display the customer ID, their first and last name and the number of orders that each customer has placed on our website. The report should only show customers that bave ordered from us more than oece We want a repoet that will show the total sales for the week of October 12. 2014. This report sbould include the total sales amount for each day from October 12 to October 18 Gencrate a report that will display the date and total sales for each day for the week of October 12, 2014 You heve writien theee queries to meet the reporting needs of management. Now it's your tun to you wish based on what you tables developed in the group project on of each query, what it is supposed to do and have learned in the class You should use the to produce your reports. write brief how it shosld be esed by the users. Below are the mininmum requirements for each Report 1: This report must join two or more tables together. You may use an ineer join our outer in. The number of asnibses you select and the fitering criteria are up to you. The minimom mast explain the reaults of the query and how these results ean be used by someone within the company Repon 2 This report must eontain an advanced SQl. functioe (aggregate or scalar). You may eles iesalta foent ngle table or on multiple tables together. The number of annbutes you sdvanced fusction and explaining the purpose of this query. You rmust explain the resalts of the query and how shese reailts can be used by soeseone with the comany. MGMT 329: Database Management shipping Address table has an address2 attribute but the paymentinfo table does not. Therefore, the word Null is entered into the SELECT statement to outpat Null values in order to make the table does not contain credit carcd into the second query to make the number of sttributes attribute count equal in both queries. The nformation, e shi o Null values where entered match the number of attributes from the first query, Both queries shouild filter the data based on be cuuiD-BeusdlD (i e. 343 in our example) Once the two queries have been written with the desired outpat, we use the word UNION between the two queries. The UNION command will combine the resalts of both queries into one outpat. Notice that both queries return the same eamber of attributes with matching data types. Try copying this UNION in your vLab to see it in action Qaery3: Fraud Detection are always concerned about fraidulent Iransactions Most oeline fraud can he etected by ceasually lurge tranuctions. While there are other measures of detecting fraud, we will keep it simple and look for any orders that are greater than $1,000. Using our Sum) function and fitbering based on the aggregate functions, we can use the fallowing query to detect transactions that should be flagged as possible fnud SELECT oOID, o.cutID, o onderDt, safoi qty ai price) FROM orders o, oeder ems WHERE o.OLD-oi OID GROUP BY o.OID,o custiD; o.onderD HAVING sumoigty"oi price)> 1000 This query identifies the castomer ID and the daie that the order took place, Using the Sum) function, we can summate the total of each order (gry price) and fiter using the HAVING classe to only ootput orders greater than 1000. These orders should sequire further attention to ensure that fraud s not taking place and protest our custoemers and our compaay. Try ranning the above query in vLab and view the rosalts MGMT 329: Database Management from the programmang code calling our query. Using (and introducing) the "Top function our SELECT statement, we will only display the top three results from the query. For customer 215, our query would produce the folilowing results. rPrice sPrice 63.96 636 44.00 44.00 313234 082 Teshiba Canvio Connect ITB Portable Extemal Hand Drive 424462 0ensen JENSEN CD.750 Portable AM FM Stereo CD Play 87.06 $7.06 also run a similar query for crossSells. The cross Selts table would recommend prodacts on the product page based on all castomer purchasing habits of that product. The We could prodiD woald be esed to fiter the results based ots which product was viewed. When the user adds itens to their shopping and g cart and decides to checkout, we need to display their nformation frorm the eustonmer, shipping.Adldress and poymentefo tables. If we join these three tables together then we will end up with both the shippingAddress and paymentlnto ioformation stceed in a single record and also have duplicate outpat if maltiple shipping addresses or payment methaods exist. To solve this probiem we will introdoce the UNION command. A UNION will combins the results of two or ricee queries into a single output For a union to work properly, there must be an even numaber of attributes in beth queries that we are trying to coesbine. Belew is the query to output shipping and billing information following by a detailed description of the query SELECT Balig Address p.billAddres, Null, pxity, pstate, pzip, ecType, eNum, ocExpire FROM customer e, paymentinfo p WHERE c.cusID-pasil) and ccustID243 UNION SELECT Shipping Addres, ddeess, dress2, scity, astate, saip, Nall, Nid, Null WHERE oadD ED and cousLD-243; The first query will output the Bilkieg Address records scored in the datahase. We use the Billing Adreu tring at the beginning to oulput this sext on each recoed to identify it as billinje The second query wall output shipping inferination and use the string Shipping ity these recenls as shipping address reconds in the Addne sring at the beginning to iden coipat. Notice the sse of Noll in the stribites list of the SELECT atatement. The LSVXGS0201 910/Proiect%20 MGMT 329: Database Management PROJECT TASK 7: DML AND REPORTING Overview of Project The class project will require students to apply the knowledge they learn in class in a practical manner. Each stadent will develop an E-Commerce datahase used to maintain customers products and sales in formation. Y ou are required to 1) gather and analyze requirements 2) design logical structure of the database 3) create stored procedures to develop the tables and insert the data 4) write SQL statements for data extraction and reporting All work for this project MUST he dose on your own. This is an individual assignment, not group assignment. Anybody caught copying the work of others will receive a zero for the project. Project Task 7: Background Throughout the counse of this semester you have analyzed the requirements for an cCommerce your database. As a class we have gone through the process of L seripts to create our database and the DML scnipts to load the data. Now that we have a functional database loaded with data, we can start working with our database performing besiness functions on all databases. Data s constantly being inserted, updated and deleted Managern noed repoits and users exccute of how our database can be used Then, you will wite youar own queries fot reporting Using dats mining techniques and fiuzy logic, we arr able to identiy products that customers are mest likely to parchase based on their parchase bistory or viewing habits. The upSels table sores the customer ID along with several prodacts and a percenlage ranking of how likcly they Sbping Cat page to try to increase sales. For example, we can use the following query to select the peodacts to be displayed for upSells SELECT Top 3 u prodiD, u.pencentage, p prodName, prPrice, paPrice FROM upSells u. product p WHERE u prodiD- p peodlD and u.cl 215 ORDER BY perentage dese We want to select the peodust information and percentage value froms our up Sells and product tabics An ainer join is used to soenect these two tables. We filter the resalts based on the custlD so that the customer only sees their yoSel percentage in descending onder so that the highest pencentage resalts are displsyed first. Wheyn appiying this qgsery to ear actual eCommeroe site, we would use a variable in the place of osir osD valie fie uoudd- geusnlD, The value for this varisble would be passed to our query Pinally, the results are ordered by svxGS0201 9 1 0/Project%20Task%2075628 1 %29.pdf MGMT 329: Database Management Project Task 7: Requi The most advance quenes have becn provided for you. Now it's your turn to write SQL queries on your own Your task is to write the SQL state ements to produce the reports needed. Creatc a Microsoft Word document containing your sQL statements for each of the following reports and upload your document to UNM Leam We want a report that will list all the categories in our database and the total number of products we have for each category. Generate a report that will display the category ID, category name and the total number of products for each eategory, Note: There are an equal mumber of products under each category in our databas We want a report that will let us know who our repeat customers are. We want to be able to track customer loyalty and peovide benefits to these customens who have made our company a success. Generate a repoet that will display the customer ID, their first and last name and the number of orders that each customer has placed on our website. The report should only show customers that bave ordered from us more than oece We want a repoet that will show the total sales for the week of October 12. 2014. This report sbould include the total sales amount for each day from October 12 to October 18 Gencrate a report that will display the date and total sales for each day for the week of October 12, 2014 You heve writien theee queries to meet the reporting needs of management. Now it's your tun to you wish based on what you tables developed in the group project on of each query, what it is supposed to do and have learned in the class You should use the to produce your reports. write brief how it shosld be esed by the users. Below are the mininmum requirements for each Report 1: This report must join two or more tables together. You may use an ineer join our outer in. The number of asnibses you select and the fitering criteria are up to you. The minimom mast explain the reaults of the query and how these results ean be used by someone within the company Repon 2 This report must eontain an advanced SQl. functioe (aggregate or scalar). You may eles iesalta foent ngle table or on multiple tables together. The number of annbutes you sdvanced fusction and explaining the purpose of this query. You rmust explain the resalts of the query and how shese reailts can be used by soeseone with the comany. MGMT 329: Database Management shipping Address table has an address2 attribute but the paymentinfo table does not. Therefore, the word Null is entered into the SELECT statement to outpat Null values in order to make the table does not contain credit carcd into the second query to make the number of sttributes attribute count equal in both queries. The nformation, e shi o Null values where entered match the number of attributes from the first query, Both queries shouild filter the data based on be cuuiD-BeusdlD (i e. 343 in our example) Once the two queries have been written with the desired outpat, we use the word UNION between the two queries. The UNION command will combine the resalts of both queries into one outpat. Notice that both queries return the same eamber of attributes with matching data types. Try copying this UNION in your vLab to see it in action Qaery3: Fraud Detection are always concerned about fraidulent Iransactions Most oeline fraud can he etected by ceasually lurge tranuctions. While there are other measures of detecting fraud, we will keep it simple and look for any orders that are greater than $1,000. Using our Sum) function and fitbering based on the aggregate functions, we can use the fallowing query to detect transactions that should be flagged as possible fnud SELECT oOID, o.cutID, o onderDt, safoi qty ai price) FROM orders o, oeder ems WHERE o.OLD-oi OID GROUP BY o.OID,o custiD; o.onderD HAVING sumoigty"oi price)> 1000 This query identifies the castomer ID and the daie that the order took place, Using the Sum) function, we can summate the total of each order (gry price) and fiter using the HAVING classe to only ootput orders greater than 1000. These orders should sequire further attention to ensure that fraud s not taking place and protest our custoemers and our compaay. Try ranning the above query in vLab and view the rosalts MGMT 329: Database Management from the programmang code calling our query. Using (and introducing) the "Top function our SELECT statement, we will only display the top three results from the query. For customer 215, our query would produce the folilowing results. rPrice sPrice 63.96 636 44.00 44.00 313234 082 Teshiba Canvio Connect ITB Portable Extemal Hand Drive 424462 0ensen JENSEN CD.750 Portable AM FM Stereo CD Play 87.06 $7.06 also run a similar query for crossSells. The cross Selts table would recommend prodacts on the product page based on all castomer purchasing habits of that product. The We could prodiD woald be esed to fiter the results based ots which product was viewed. When the user adds itens to their shopping and g cart and decides to checkout, we need to display their nformation frorm the eustonmer, shipping.Adldress and poymentefo tables. If we join these three tables together then we will end up with both the shippingAddress and paymentlnto ioformation stceed in a single record and also have duplicate outpat if maltiple shipping addresses or payment methaods exist. To solve this probiem we will introdoce the UNION command. A UNION will combins the results of two or ricee queries into a single output For a union to work properly, there must be an even numaber of attributes in beth queries that we are trying to coesbine. Belew is the query to output shipping and billing information following by a detailed description of the query SELECT Balig Address p.billAddres, Null, pxity, pstate, pzip, ecType, eNum, ocExpire FROM customer e, paymentinfo p WHERE c.cusID-pasil) and ccustID243 UNION SELECT Shipping Addres, ddeess, dress2, scity, astate, saip, Nall, Nid, Null WHERE oadD ED and cousLD-243; The first query will output the Bilkieg Address records scored in the datahase. We use the Billing Adreu tring at the beginning to oulput this sext on each recoed to identify it as billinje The second query wall output shipping inferination and use the string Shipping ity these recenls as shipping address reconds in the Addne sring at the beginning to iden coipat. Notice the sse of Noll in the stribites list of the SELECT atatement. The LSVXGS0201 910/Proiect%20 MGMT 329: Database Management PROJECT TASK 7: DML AND REPORTING Overview of Project The class project will require students to apply the knowledge they learn in class in a practical manner. Each stadent will develop an E-Commerce datahase used to maintain customers products and sales in formation. Y ou are required to 1) gather and analyze requirements 2) design logical structure of the database 3) create stored procedures to develop the tables and insert the data 4) write SQL statements for data extraction and reporting All work for this project MUST he dose on your own. This is an individual assignment, not group assignment. Anybody caught copying the work of others will receive a zero for the project. Project Task 7: Background Throughout the counse of this semester you have analyzed the requirements for an cCommerce your database. As a class we have gone through the process of L seripts to create our database and the DML scnipts to load the data. Now that we have a functional database loaded with data, we can start working with our database performing besiness functions on all databases. Data s constantly being inserted, updated and deleted Managern noed repoits and users exccute of how our database can be used Then, you will wite youar own queries fot reporting Using dats mining techniques and fiuzy logic, we arr able to identiy products that customers are mest likely to parchase based on their parchase bistory or viewing habits. The upSels table sores the customer ID along with several prodacts and a percenlage ranking of how likcly they Sbping Cat page to try to increase sales. For example, we can use the following query to select the peodacts to be displayed for upSells SELECT Top 3 u prodiD, u.pencentage, p prodName, prPrice, paPrice FROM upSells u. product p WHERE u prodiD- p peodlD and u.cl 215 ORDER BY perentage dese We want to select the peodust information and percentage value froms our up Sells and product tabics An ainer join is used to soenect these two tables. We filter the resalts based on the custlD so that the customer only sees their yoSel percentage in descending onder so that the highest pencentage resalts are displsyed first. Wheyn appiying this qgsery to ear actual eCommeroe site, we would use a variable in the place of osir osD valie fie uoudd- geusnlD, The value for this varisble would be passed to our query Pinally, the results are ordered by