Database Model-Assignment 6 Views, Temporary Tables and Performance Solved

Views and Temporary tables

Problem #1  

Create View using rearbooks that returns all authors that have volumes in good condition in the database.

To get full credit, the script should delete the view first, make sure your script is idempotent, meaning I can rerun this statement without erroring out many times.

Write select statement that shows your view data.

Problem #2 

Write an update statement that changes author_last_first  'Barth, John' into your first and last name using the view you just created

Did this work? Explain why.

If this worked run and submit queries that prove that it worked for view and table

 

Problem #3

Create view that shows author name and number of volumes present in the database per author

To get full credit, the script should delete the view first, make sure your script is idempotent, meaning I can rerun this statement without erroring out many times.

 

Problem #4 

Write an update statement that changes author_last_first  'Bronte, Charlotte' into your first and last name using the view you just created

Did this work? Explain why.

If this worked run and submit queries that prove that it worked for view and table

Problem #5 

Create temporary table that has following information

Book_title, publisher_name, average asking price per book.

Write select statement that shows your view data.

 

Performance

 

Problem #6  

Write select statement that finds author ‘Twain, Mark”  

Run the query 5 times, get an average on how long the query runs.

In your Submission for this problem

1.     Submit the query for the problem.

1.     How long it took to run each time to run the procedure.

2.     How long it was to run the procedure on average.

 

Problem #7 

Execute stored procedure call insert_authors(10000); run this procedure 5 times and  get an average on how long that procedure runs

1.     How long it took to run each time.

2.     How long it was run on average.

 

Problem #8 

Rerun the query from the problem #6 5 times and write down each time and average.

Figure out what to do to speed up the query.

Rerun the query 5 times again and calculate the average.

1.     How long it took to run query to find Mark Twain records each time after you run procedure in #7.

2.     How long it was run on average.

3.     Submit the fix for the performance done.

4.     How long it took to run each time after the fix.

5.     How long it was run on average after the fix.

6.     Describe in few words what you did and what happened, why we see numbers we see.

 

Problem #9 

3.     Run call insert_authors(10000) 5 more times after the performance fix

4.     Write down how long it took to run each time and average to run the procedure.

5.     Describe in few words what you did and what happened, why we see numbers we see.

 

Problem #10

1.     Alter table Publishers to have another column Preferred type bool, default value false.

2.     Run query that returns publisher Macmillan, and his Preferred status.

3.     Run the query 5 times and time it, and also calculate the average.

 

Problem #11 

 

1.     Create stored procedure insert_publishers(publisherCount INT) using procedure  insert_authors(authorsCount INT) from the script InsertRandomAuthors.sql  as an example

 

This procedure should insert publisher with random publisher_name (varchar) and random Preferred value (true or false).

 

2.     Run stored procedure insert_publishers(10000)  5 times and record times and average.

 

Problem #12 

1.     How long it takes to run the query to find Macmillan publisher records each time before the performance fix.

2.     How long it was run on average each time before the performance fix.

3.     Submit the fix for the performance.

4.     How long it took to run each time to run the query after the fix.

5.     How long it was run on average after the fix.

6.     Describe in few words what you did and what happened, why we see numbers we see.