Please answer Question 3 parts A and B in the image. The tricky part is only new lines can be added and the original script must stay unaltered. (3) Craft a SQL Select statement to illustrate the following: (A) SQL Series One: CRAFT a SELECT statement that returns two columns of the ORDERLINEITEM table: 1) Use CAST function to return UnitPrice as data type decimal with 2 digits to the right of the decimal point 2) Use CAST function to return UnitPrice as data type varchar. 3) Use CONVERT function to return UnitPrice s data type decimal with 2 digits to the right of the decimal point. 4) Use CONVERT function to return UnitPrice as data type varchar, using style 1.
please abswer both a and b parts within 30 minutes..
Here is my original SQL script. Please answer Question 3 parts A and B in the image. The tricky part is only new lines can be added and the original script must stay unaltered. Thanks for your help!
-------------------------------------------------------------------------------------------------------
-- ORDERS
CREATE TABLE CUSTOMER
(
CustomerID INT NOT NULL PRIMARY KEY,
CustomerName VARCHAR (50) NOT NULL,
CustomerAddress VARCHAR(50) NOT NULL,
CustomerPrimaryPhone VARCHAR(10) NULL
);
--
CREATE TABLE CUSTOMERORDER
(
OrderID INT NOT NULL PRIMARY KEY,
CustomerID INT NOT NULL FOREIGN KEY REFERENCES CUSTOMER(CustomerID),
OrderDate DATE NOT NULL,
ShipAddress VARCHAR(50) NOT NULL,
ShipDate DATE NOT NULL
);
--
CREATE TABLE PRODUCT
(
ProductID INT NOT NULL PRIMARY KEY,
ProductName VARCHAR(50) NOT NULL,
UnitPrice DECIMAL(10,2) NOT NULL,
AvailabilityStatus VARCHAR(10) NOT NULL
);
--
CREATE TABLE ORDERLINEITEM
(
OrderID INT NOT NULL,
OrderSequence INT NOT NULL,
ProductID INT NOT NULL,
Quantity INT NOT NULL DEFAULT 0 CHECK (Quantity >=0),
UnitPrice DECIMAL(10,2) NOT NULL,
PRIMARY KEY (OrderID, OrderSequence),
FOREIGN KEY (OrderID) REFERENCES CUSTOMERORDER(OrderID),
FOREIGN KEY (ProductID) REFERENCES PRODUCT(ProductID)
);
-- Revise objects
ALTER TABLE CUSTOMER
DROP COLUMN CustomerAddress;
--
ALTER TABLE CUSTOMER
ADD CustomerStreet VARCHAR(50) NOT NULL,
CustomerCity VARCHAR(50) NOT NULL,
CustomerState CHAR(2) NOT NULL,
CustomerZipCode VARCHAR(10) NOT NULL;
--
ALTER TABLE CUSTOMERORDER
DROP COLUMN ShipAddress;
--
ALTER TABLE CUSTOMERORDER
ADD ShipStreet VARCHAR(50) NOT NULL,
ShipCity VARCHAR(50) NOT NULL,
ShipState CHAR(2) NOT NULL,
ShipZipCode VARCHAR(10) NOT NULL;
--
--DML Script (insert data)
INSERT INTO CUSTOMER (CustomerID, CustomerName, CustomerPrimaryPhone, CustomerStreet, CustomerCity, CustomerState, CustomerZipCode)
VALUES (1, 'Joe', 3369991450, '123 Main', 'Raleigh', 'NC', 28201),
(2, 'Sandy',5032217777, '40th Avenue', 'Sandy', 'OR', 99887),
(3, 'Ben', 3608889999, '491 Street', 'Vancouver', 'WA', 98683);
select * from CUSTOMER;
INSERT INTO CUSTOMERORDER (OrderID, CustomerID, OrderDate, ShipDate, ShipStreet, ShipCity, ShipState, ShipZipCode)
VALUES (1, 1,'09/01/2022', '09/2/2022', '123 Main', 'Raleigh', 'NC', 28201),
(2, 2,'09/03/2022', '09/11/2022', '40th Avenue', 'Sandy', 'OR', 99887),
(3, 3,'08/01/2022', '08/31/2022', '491 Street', 'Vancouver', 'WA', 98683);
select * from CUSTOMERORDER;
INSERT INTO PRODUCT (ProductID, ProductName, UnitPrice, AvailabilityStatus)
VALUES (1, 'Textbook', 125.13,'Available'),
(2, 'Laptop', 455.99, 'BackOrder'),
(3, 'Chair', 124.88,'Available');
select * from PRODUCT;
INSERT INTO ORDERLINEITEM (OrderID, OrderSequence, ProductID, Quantity, UnitPrice)
VALUES (1, 1, 1, 1, 125.13),
(1, 2, 2, 1, 455.99),
(1, 3, 3, 1, 124.88);
select * from ORDERLINEITEM;
----------------------------------------------------------------------------------------------------------------------
Step by step
Solved in 3 steps
