I am needing some help with some database sql quieries for my assessment. Instru
ID: 3746881 • Letter: I
Question
I am needing some help with some database sql quieries for my assessment.
Instructions In preparation to developing the web application forms, Smart Homes, Inc. requires the DML statements to insert, update, and delete content that they will be integrating into their code. Although these statements need to be customized to allow input from the Web site, SmartHomes wants statements based on the dummy data in the database first to ensure that they work correctly Some of them may require multiple statements and need to consider the order in which the statements must occur. For this assessment, complete the following DML statements to be used in the web application: . There are some situations in which a product requires some changes in terms of price and description. Create an UPDATE statement that will update a specific product's price and description (of your choice). Query the row to validate the changes. A category has to be removed, DELETE the category and UPDATE all of the products in that category to an existing category. . A "fake" user has made a few orders. DELETE the user account along with all of their orders Insert a new product with no category included. Then run an update statement to add it to a category There has been a price increase on some items. UPDATE all product prices that are greater than $10 to be $1 more The database server had been reset with the wrong date and time without anyone knowing. UPDATE any order date in the past week to add 7 days There are too many user accounts that do not have any orders and they should be removed. DELETE all customer records that do not have any orders UPDATE the product's description to include on the following string those products that have not been ordered yet: "Special promotion: 25% off." When the Web site is down, someone has taken in a manual order, create the INSERT statements to add a customer and their three products that they have purchased. A customer had called in with their name (no order or customerlD) stating that they had purchased the incorrect product. Using an UPDATE statement, update the OrderDetail table using their first and last name (consider it to be unique) In summary, for your assessmentExplanation / Answer
Hey! Your solution is here.
If it helps, please do Upvote. Thank you.
Subquestion 1: Product Update
update PRODUCT set UnitPrice = newPrice, ProductDescription=newDescription;
Subquestion 2 : Removing category
Query 1 :
update Product set CategoryId = existingCategory where CategoryId = categoryToBeDeleteId;
Query 2 :
delete from PictureCategory where CategoryId = categoryToBeDeleteId;
Subquestion 3 : Removing fake customer
Query 1 : Delete * from Customer wher CustomerId = deleteCustomerID;
Query 2: Delete from Order, OrderDetails USING Order INNER JOIN OrderDetails ON Order.OrderId = OrderDetails.OrderId WHERE Order.CustomerId= deleteCustomerID;
Subquestion 4 : Adding new product without category
Query 1 : Insert into Product(ProductId,ProductName,ProductDescription,UnitPrice) values(newProductId,newProductName,newProductDescription,newUnitPrice);
Query 2 : update Product set CategoryId = existingCategoryId where ProductId = newlyInsertedProductId;
Subquestion 5 : Update product price
Query 1 : update Product set UnitPrice = UnitPrice + 1 where UnitPrice >10;
Subquestion 6 : Updating date
Query 1 : update Order set OrderDate = OrderDate + 7 where OrderDate>CurrentDate-7;
Subquestion 7 : Delete customer record
Query 1: Delete from Customer where CustomerId NOT exist (select Order.CustomerId from Order);
Subquestion 8: update product for promotion offer
Query 1: Update Product set ProductDescription = "Special Promotion : 25% Off" where ProductID NOT exist (select OrderDetails.ProductId from OrderDetails);
Subquestion 9 : Adding Customer and 3 Products
Query 1: Insert into Customer values(newCustomerId,newFirstName,newLastName,newAddress,newState,newCity,newZip,newCountry,
newPhone,newEmail,newUsername,newPassword);
Query 2 : Insert into Order values(newOrderId, newCustomerId, newOrderDate,newShipDate);
Query 3 : Insert into OrderDetails values(newOrderDetailId,newOrderId,existingProductId,variablePrice,newQuantity);
Subquestion 10: Updating order details using first and last name.
Query 1 : Update OrderDetails set ProductId = newProductId, Price = newPrice, Quantity=newQuantity where CustomerId In (Select CustomerId from Customer where FirstName = providedFirstName and LastName = providedLastName);
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.