This sample database consists of the following tables(see image for tables and there is one more at the bottom in this text): • Customers: stores customer’s data • Products: stores a list of scale model cars • ProductLines: stores a list of product line categories • Orders: stores sales orders placed by customers • OrderDetails: stores sales order line items for each sales order • Payments: stores payments made by customers based on their accounts • Employees: stores all employee information as well as the organization structure such as who reports to whom • Offices: stores sales office data Write SQL code for the following: We want to add a new sale order for the customer (customerNumber = 145) in the database. The steps of adding a sale orde
This sample database consists of the following tables(see image for tables and there is one more at the bottom in this text): • Customers: stores customer’s data • Products: stores a list of scale model cars • ProductLines: stores a list of product line categories • Orders: stores sales orders placed by customers • OrderDetails: stores sales order line items for each sales order • Payments: stores payments made by customers based on their accounts • Employees: stores all employee information as well as the organization structure such as who reports to whom • Offices: stores sales office data Write SQL code for the following: We want to add a new sale order for the customer (customerNumber = 145) in the database. The steps of adding a sale orde
This sample database consists of the following tables(see image for tables and there is one more at the bottom in this text): • Customers: stores customer’s data • Products: stores a list of scale model cars • ProductLines: stores a list of product line categories • Orders: stores sales orders placed by customers • OrderDetails: stores sales order line items for each sales order • Payments: stores payments made by customers based on their accounts • Employees: stores all employee information as well as the organization structure such as who reports to whom • Offices: stores sales office data Write SQL code for the following: We want to add a new sale order for the customer (customerNumber = 145) in the database. The steps of adding a sale orde
This sample database consists of the following tables(see image for tables and there is one more at the bottom in this text): • Customers: stores customer’s data • Products: stores a list of scale model cars • ProductLines: stores a list of product line categories • Orders: stores sales orders placed by customers • OrderDetails: stores sales order line items for each sales order • Payments: stores payments made by customers based on their accounts • Employees: stores all employee information as well as the organization structure such as who reports to whom • Offices: stores sales office data
Write SQL code for the following: We want to add a new sale order for the customer (customerNumber = 145) in the database. The steps of adding a sale order are described as follows: (1) Get latest sale order number from “orders” table, and use the next sale order number as the new sale order number (2) Insert a new sale order into “orders” table for the customer (customerNumber = 145). For this order, the orderNumber is the new sale order number from step (1), orderDate is the current date (you can use now() to get the date), requiredDate is 5 days from now (you can use date_add(now(), INTERVAL 5 DAY) to get the date), shippedDate is 2 days from now (you can use date_add(now(), INTERVAL 2 DAY) to get the date), status is “in process”. (3) Insert new sale order items into “orderdetails” table. The customer has bought two items in his order. One item has productCode = ‘S18_1749’, quantityOrdered = 30, priceEach for this item is 136, orderLineNumber = 1. The second item has productCode = ‘S18_2248’, quantityOrdered = 50, priceEach for this item is 55.09, orderLineNumber = 2.
