1. Write an SQL that will create a table called Teams. The table will have colum

Question

1. Write an SQL that will create a table called Teams.

The table will have columns for:

TeamID which is a unique number and is primary key. ID must be a whole number between 100 and 999,999.

Team Name which can be up to 40 characters long and cannot be null.

Team Captain Name which can be up to 40 characters long and can be null.

LastModifiedDate holds the date and time of last insert/update. If nothing is provided it should use the current date and time.

2. You need to write a stored procedure (using AdventureWorks) that will select product id, name, productNumber for all products from the Product table.The procedure will accept parameters for MakeFlag, FinishGoodsFlag and a List Price greater than the input parameter.(20 pts for Stored Procedure)

Then write the SQL statement to execute the stored procedure using a make flag of 1, list price of 1000 and finished goods flag of 1. (5 pts for the call to the procedure)

Explanation / Answer

CREATE TABLE Teams
(
   TeamID int PRIMARY KEY,
   TeamName varchar(40) NOT NULL,
   TeamCaptain varchar(40),
   LastModifiedDate DATETIME DEFAULT GETDATE(),
   CONSTRAINT chk_id CHECK (TeamID>99 AND TeamID<1000000)
)

TeamID int PRIMARY KEY, -- Primary key constraint itself checks to make sure that values are unique

   TeamName varchar(40) NOT NULL, -- not null constraint

   TeamCaptain varchar(40), -- no constraints

   LastModifiedDate DATETIME DEFAULT GETDATE(), -- GETDATE() gives system date at that time

   CONSTRAINT chk_id CHECK (TeamID>99 AND TeamID<1000000) -- this is a constraint to check that ID is a whole number between 100 and 999,999.

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.