1.Write a script that adds an index to the MyGuitarShop database for the zip cod

Question

1.Write a script that adds an index to the MyGuitarShop database for the zip code field

in the Customers table.

2. Write a script that implements the following design in a database named MyWebDB:

In the Downloads table, the UserID and ProductID columns are the foreign keys.

Include a statement to drop the database if it already exists.

Include statements to create and select the database.

Include any indexes that you think are necessary.

3. Write a script that adds rows to the database that you created in exercise 2.

Add two rows to the Users and Products tables.

Add three rows to the Downloads table: one row for user 1 and product 2; one for

user 2 and product 1; and one for user 2 and product 2. Use the GETDATE function

to insert the current date and time into the DownloadDate column.

4. Write an ALTER TABLE statement that adds two new columns to the Products table

created in exercise 2.

Add one column for product price that provides for three digits to the left of the

decimal point and two to the right. This column should have a default value of 9.99.

Add one column for the date and time that the product was added to the database.

5. Write an ALTER TABLE statement that modifies the Users table created in exercise

2 so the FirstName column cannot store null values and can store a maximum of 20

characters.

Code an UPDATE statement that attempts to insert a null value into this column. It

should fail due to the not null constraint.

Code another UPDATE statement that attempts to insert a first name thats longer

than 20 characters. It should fail due to the length of the column.

Explanation / Answer

The following script that adds an index to the MyGuitarShop database for the zip code field in the Addresses table.

CREATE INDEX IDX1 ON MyGuitarShop.Address(zip_code);

·         “CREATE INDEX” creates an index with name Idx1.

·         “ON MyGuitarShop.Addresses” is used to locate the table “Addresses” in the “MyGuitarShop” database. (zip_code) indicates the name of the field.

2.     

DROP DATABASE MyWebDB;

CREATE DATABASE MyWebDB;

Use MyWebDB;

Create table Downloads

(

DownloadID int,

DownloadDate DATE,

FileName varchar(2),

ProductsID int,

USERID int,

CONSTRAINT fk_ProductDownloadFOREIGN KEY(ProductsID)

REFERENCES Products(ProductsID)

CONSTRAINT fk_UsersDownloadFOREIGN KEY(UserID)

REFERENCES Users(UserID)

);

SELECT DB_NAME() AS DataBaseName;

EXEC MyWebDB;

·         Drop the database MyWebDB, if the name already existed.

·         Create a DATABASE MyWebDB.

·         Create table Downloads, and set the ProductId and UserId are foreign keys.

·         Select the Database and execute it.

3.                 

Add the three rows to insert the values in the table Downloads and insert the currents and time in to the Downloads table is as follows:

insert into Downloads values('user 1','product 2',GETDATE());

insert into Downloads values('user 2','product 1',GETDATE());

insert into Downloads values('user 2','product 2',GETDATE());

4.     

ALTER TABLE Products

ADD COLUMN product_price float(3,2) DEFAULS 0.99;

ALTER TABLE Products

ADD COLUMN product_added_date datetime NOT NULL DEFAULT GETDATE();

·         Alter the products table, which adds new column product_price:

·         The above statement float(3,2) is used to set the specified precision of digits and default value 9.99.

·         Alter the products table which adds the produc_added_date and set the default date is not null

5.        

ALTER TABLE Users

ALTER COLUMN FIRSTNAME varchar(20);

ALTER TABLE Users

ALTER COLUMN Firstname varchar(20) NOT NULL;

Alter the Users table to add the column FIRSTNAME of 20 characters and set it to NOT NULL;

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

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