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

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:

Users                                                  Downloads                                        Products                                        

UserID                                               DownloadID                                       ProductsID

EmailAddress                                    DownloadDate                                  ProductName

FirstName                                         FileName

LastName                                         ProductID

--------

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

Write a SELECT statement that joins the three tables and retrieves the data from these tables like this

email_address     first_name               last_name            download_date              filename               product_name

Johnsmith@gmail.com john              Smith              2012-04-24 16:15:38 pedals_are_falling.mp3 Local Music Vol 1

janedoe@yahoo.com Jane              Doe                2012-04-24 16:15:38 lum_signal.mps            Local Music Vol 1

janedoe@yahoo.com Jane           Doe                   2012-04-24 16:15:38 one_horse_town.mp3   Local Music vol 2

Sort the results by the email address in descending order and the product name in ascending order.

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 that

Explanation / Answer

1.  
The script that adds an index to the MyGuitarShop database for the zip code field in the Customers table is as shown below:

CREATE INDEX id ON MyGuitarShop. Customers (zipCode);

2.  
The script to drop and create the database MyWebDB and create the tables Users, Products and Downloads is as shown below:

DROP DATABASE MyWebDB;

CREATE DATABASE MyWebDB;

Use MyWebDB;

Create table Users
(
UserID   int primary key,
EmailAddress varchar(30),
FirstName varchar(20),
LastName varchar(20)
);

Create table Products
(
ProductsID   int primary key,
ProductName varchar(30)
);

Create table Downloads
(
DownloadID int,
DownloadDate DATE,
FileName varchar(40),
ProductsID int,
USERID int,
CONSTRAINT fk_ProductDownload FOREIGN KEY(ProductsID)REFERENCES Products(ProductsID),
CONSTRAINT fk_UsersDownload FOREIGN KEY(UserID)REFERENCES Users(UserID)
);

3.  
The script to add two rows to the Users and Products tables is as shown below:

insert into Users values(1, 'Johnsmith@mail.com',' john', 'Smith');
insert into Users values(2, 'janedoe@mail.com',' jane', 'Doe');

insert into Products values(1, 'Local Music Vol 1');
insert into Products values(2, ' Local Music Vol 2');
            
insert into Downloads values(1, GETDATE(),'pedals_are_falling.mp3',2,1);
insert into Downloads values(2, GETDATE(),'lum_signal.mps',1,2);
insert into Downloads values(3, GETDATE(),'one_horse_town.mp3',2,2);

SELECT EmailAddress, FirstName, LastName, DownloadDate, FileName, ProductName
FROM Users, Products, Downloads
WHERE Users.USERID=Downloads.USERID
AND Products.ProductsID=Downloads.ProductsID
ORDER BY EmailAddress desc, ProductName asc;

4.  
ALTER TABLE statement that adds two new columns to the Products table is as shown below:

ALTER TABLE Products
ADD COLUMN price float(3,2) DEFAULT 9.99;

ALTER TABLE Products
ADD COLUMN product_added_date datetime NOT NULL DEFAULT GETDATE();

5.
ALTER TABLE statement that adds a new columns to the Users table is as shown below:

ALTER TABLE Users
MODIFY COLUMN FIRSTNAME varchar(20) 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.