In SQL 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 structuresuch as who reports to whom• Offices: stores sales office dataWrite SQL code for the following:We want to add a new sale order for the customer (customerNumber = 145) in thedatabase. 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 ordernumber 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 boughttwo items in his order. One item has productCode = ‘S18_1749’, quantityOrdered= 30, priceEach for this item is 136, orderLineNumber = 1. The second item hasproductCode = ‘S18_2248’, quantityOrdered = 50, priceEach for this item is55.09, orderLineNumber = 2. CREATE TABLE `payments` ( `customerNumber` int(11) NOT NULL, `checkNumber` varchar(50) NOT NULL, `paymentDate` date NOT NULL, `amount` double NOT NULL, PRIMARY KEY (`customerNumber`,`checkNumber`), CONSTRAINT `payments_ibfk_1` FOREIGN KEY (`customerNumber`) REFERENCES `customers` (`customerNumber`)) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE 'offices' ('officeCode' varchar(10) NOT NULL,`city' varchar(50) NOT NULL,'phone varchar(50) NOT NULL,`addressLine1 varchar(50) NOT NULL,`addressLine2` varchar(50) DEFAULT NULL,'state varchar(50) DEFAULT NULL,`country` varchar(50) NOT NULL,postalCode varchar(15) NOT NULL,"territory' varchar(10) NOT NULL,PRIMARY KEY (`officeCode')) ENGINE=InnoDB DEFAULT CHARSET=latin1;CREATE TABLE 'employees' ('employeeNumber int(11) NOT NULL,`lastName' varchar(50) NOT NULL,'firstName' varchar(50) NOT NULL,'extension varchar(10) NOT NULL,`email` varchar(100) NOT NULL,'officeCode' varchar(10) NOT NULL,`reportsTo` int(11) DEFAULT NULL,`jobTitle` varchar(50) NOT NULL,PRIMARY KEY ('employeeNumber'),KEY 'reportsTo` (`reportsTo`),KEY 'officeCode' ('officeCode'),CONSTRAINT employees ibfk 2 FOREIGN KEY (`officeCode") REFERENCES offices (officeCode'),CONSTRAINT 'employees ibfk 1 FOREIGN KEY (reportsTo`) REFERENCES employees` (`employeeNumber')) ENGINE=InnoDB DEFAULT CHARSET=latin1;CREATE TABLE `customers' ('customerNumber int(11) NOT NULL,'customerName` varchar(50) NOT NULL,contactLastName` varchar(50) NOT NULL,contactFirstName` varchar(50) NOT NULL,'phone` varchar(50) NOT NULL,addressLine1 varchar(50) NOT NULL,addressLine2` varchar(50) DEFAULT NULL,`city' varchar(50) NOT NULL,'state varchar(50) DEFAULT NULL,9,postalCode varchar(15) DEFAULT NULL,`country' varchar(50) NOT NULL,'salesRepEmployee Number' int(11) DEFAULT NULL,`creditLimit' double DEFAULT NULL,PRIMARY KEY (`customerNumber),KEY 'salesRepEmployeeNumber` (`salesRepEmployeeNumber"),CONSTRAINT `customers ibfk 1 FOREIGN KEY (sales RepEmployee Number') REFERENCES employees' ('employeeNumber')) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE 'orders` (`orderNumber' int(11) NOT NULL,`orderDate` date NOT NULL,'requiredDate` date NOT NULL,`shippedDate` date DEFAULT NULL,'status' varchar(15) NOT NULL,`comments' text,'customerNumber' int(11) NOT NULL,PRIMARY KEY (`orderNumber"),KEY 'customerNumber` (`customer Number'),CONSTRAINT orders ibfk 1 FOREIGN KEY ('customerNumber') REFERENCES 'customers` (`customerNumber')) ENGINE=InnoDB DEFAULT CHARSET=latin1;CREATE TABLE `productlines (productLine' varchar(50) NOT NULL,'textDescription` varchar(4000) DEFAULT NULL,`htmlDescription mediumtext,`image` mediumblob,PRIMARY KEY (`productLine')) ENGINE=InnoDB DEFAULT CHARSET=latin1;CREATE TABLE `products (`productCode' varchar(15) NOT NULL,productName` varchar(70) NOT NULL,`productLine' varchar(50) NOT NULL,`productScale varchar(10) NOT NULL,`productVendor' varchar(50) NOT NULL,product Description` text NOT NULL,quantityInStock smallint (6) NOT NULL,'buyPrice' double NOT NULL,MSRP' double NOT NULL,PRIMARY KEY (`productCode"),KEY `productLine` (`productLine'),CONSTRAINT `products_ibfk 1 FOREIGN KEY (`productLine') REFERENCES `productlines` (`productLine')) ENGINE=InnoDB DEFAULT CHARSET=latin1;CREATE TABLE 'orderdetails (orderNumber' int(11) NOT NULL,"productCode' varchar(15) NOT NULL,`quantityOrdered` int(11) NOT NULL,`priceEach' double NOT NULL,`orderLineNumber' smallint(6) NOT NULL,PRIMARY KEY (`orderNumber`, `productCode"),KEY `productCode` (`productCode'),CONSTRAINT orderdetails_ibfk_2` FOREIGN KEY (`productCode') REFERENCES `products` (`productCode'),CONSTRAINT orderdetails_ibfk_1 FOREIGN KEY (`orderNumber') REFERENCES 'orders` (`orderNumber')) ENGINE=InnoDB DEFAULT CHARSET=latin1;

1. orderNumber is the primary key in the orders table, so it is always unique.This query selects…

In SQL 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. CREATE TABLE `payments` ( `customerNumber` int(11) NOT NULL, `checkNumber` varchar(50) NOT NULL, `paymentDate` date NOT NULL, `amount` double NOT NULL, PRIMARY KEY (`customerNumber`,`checkNumber`), CONSTRAINT `payments_ibfk_1` FOREIGN KEY (`customerNumber`) REFERENCES `customers` (`customerNumber`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

In SQL 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. CREATE TABLE `payments` ( `customerNumber` int(11) NOT NULL, `checkNumber` varchar(50) NOT NULL, `paymentDate` date NOT NULL, `amount` double NOT NULL, PRIMARY KEY (`customerNumber`,`checkNumber`), CONSTRAINT `payments_ibfk_1` FOREIGN KEY (`customerNumber`) REFERENCES `customers` (`customerNumber`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Computer Networking: A Top-Down Approach (7th Edition)

7th Edition

ISBN:9780133594140

Author:James Kurose, Keith Ross

Publisher:James Kurose, Keith Ross

Chapter1: Computer Networks And The Internet

Section: Chapter Questions

Problem R1RQ: What is the difference between a host and an end system? List several different types of end...

See similar textbooks

Related questions

Question

In SQL

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.

CREATE TABLE `payments` (

`customerNumber` int(11) NOT NULL,

`checkNumber` varchar(50) NOT NULL,

`paymentDate` date NOT NULL,

`amount` double NOT NULL,

PRIMARY KEY (`customerNumber`,`checkNumber`),

CONSTRAINT `payments_ibfk_1` FOREIGN KEY (`customerNumber`) REFERENCES `customers` (`customerNumber`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;