CITD 120 -- SQL Concepts
Homework Assignment 5
40 points
Use the Course Quizzes database for this assignment.
NOTE:
The Course Quizzes database you are using for this assignment is a test database and contains much less data than would be in the production database used to run the company. It has been made smaller to make it easier for you to check that your SELECT statement retrieves the correct data. However, retrieving the correct data from the testing database is not a guarantee that the SELECT statement is correct. You must write your commands as if they were run against the larger database which has different data than the testing database you are using.
The ER Diagram for the Course Quizzes database is shown below.
For EACH TASK for this assignment submit the following:
a. The SELECT statement you used.
b. The result of the SELECT statement.
1. (4 points) Professor Crewel would like to see how the correct answers are distributed among the various topics.
For each question, list the question Id, the title, the correct answer, and the difficulty. Sort the results by correct answer. Within correct answer sort the questions by title. Use the INNER JOIN syntax.
2. (4 points) Professor Crewel would like to know how many students choose answer D correctly.
For each question where the correct answer is D, List the Question Id, the Quiz Number, the Quiz Date and the number of students who chose answer D. Use the INNER JOIN syntax.
3. (4 points) Professor Crewel would like to know the topics that were on the quizzes where there is a significant difference between the high score and the low score.
List the quiz number, date, and topic id for those quizzes where the difference between the high score and the low score is greater than 8. Use the INNER JOIN syntax.
4. (4 points) Professor Crewel would like a list of all topics and the question ids, and difficulty levels for those questions that have four choices.
List the topic title, question id, and difficulty levels for those questions that have four choices. All topics must be included in the list regardless of whether there are any questions for the topic. Sort the results by title.
5. (4 points) Create a product of the Quizzes and Topics tables. Include the quiz number, quiz date, and number of class sessions in your result. Sort the result by quiz date.
6. (4 points) Professor Crewel would like to keep track of the retired questions and make some changes without affecting the current data.
Create a table named Retired with the same structure as the Questions table. Use the DESCRIBE command to show that the two tables have the same structure. Be sure to read the handout on Creating Duplicate Tables.
7. (4 points) Professor Crewel would like to keep track of the retired questions and make some changes without affecting the current data.
Insert into the Retired table the rows from the Questions table that have been retired (status is R). Verify that the data exists in the table.
8. (4 points) Professor Crewel would like to change the difficulty of the true/false questions.
In the Retired table double the difficulty of the true/false questions. Verify the changes.
9. (4 points) Professor Crewel does not need the question with the topic id Jewl.
Delete the Question in the Retired table having topic id Jewl. Verify the changes.
10. (4 points) Professor Crewel has decided to give the students no more than three choices for the questions about scrapbooking.
In the Retired table change Answer D for the questions that have topic id Scrp to null. Verify the changes.