CORRECT TEXT
You are employed as a SQL Server 2012 database developer at ABC.com. ABC.com has a
database named SalesDB with tables named Customer and Orders. The Customer and Orders
tables were created using the following Transact-SQL code:
CREATE TABLE SalesDB.Customers
(
CustomerID int NOT NULL PRIMARY KEY,
FirstName varchar (150) NOT NULL,
LastName varchar (150) NOT NULL,
Address1 varchar (200) NOT NULL,
Address2 varchar (200) NULL,
City varchar (100) NOT NULL,
StateCode varchar (2) NOT NULL,
Zip varchar (5) NOT NULL,
Phone varchar (10) NOT NULL
)
GO
CREATE TABLE SalesDB.Orders
(
OrderID int NOT NULL PRIMARY KEY,
CustomerID int NOT NULL,
OrderDate datetime NOT NULL,
ShipDate datetime NOT NULL,
CustomerID int NOT NULL,
SalesRepID int NOT NULL
)
GO
You must now create an OrderDetails table as shown in the following database diagram:
The TotalPrice column must be a computed column based on the product of the UnitPrice and
Quantity columns and the data must be stored in the table.
How would you create this table?
To answer, type the correct code in the answer area.
Answer: See the explanation.
Explanation:
CREATE TABLE SalesDB.OrderDetails
(
OrderID int NOT NULL,
ProductID int NOT NULL,
Quantity int NOT NULL,
UnitPrice money NOT NULL,
TotalPrice AS (Quantity * UnitPrice) PERSISTED
)
create table OrderDetails
(
OrderID int not null Primary key,
ProductID nvarchar not null,
Quantity int,
unitprice money,
totalprice as quantity * unitprice
)
tested
+ persisted (must be stored in the table.)
create table Test.OrderDetails
(
OrderID int not null Primary Key,
ProductID int not null Primary Key,
Quantity int null,
Unitprice money null,
Totalprice as (Quantity*Unitprice) persistend
)
GO
create table sales.orderDeatils
(
[orderid] int not null,
[productid] int not null,
[unitprice] money not null default(0),
[qty] smallint not null default(0),
totalprice as [unitprice]*qty persisted,
constraint pk_SalesOrder primary key ([orderid],[productid]),
constraint fk_Order foreign key ([orderid]) references [SalesDb].[Orders]([orderid])
)
I don’t think this question stresses about constraints at all..