SQL Views and Stored Procedures Quiz

Why might an error occur when updating the ReturnDate column using the vw_BorrowedBooksInfo view?

• The view uses a join to retrieve its records

• The view includes a computed column in the output

• The view uses a WHERE clause to filter values

• The view was not indexed on the primary key

What condition must be added to the vw_BorrowedBooksInfo view to return only overdue, still borrowed books?

• WHERE ReturnStatus = 'Returned Late'

• WHERE ReturnDate IS NULL AND BorrowDate < GETDATE()

• WHERE ReturnStatus = 'Still Borrowed' AND BorrowDate < DATEADD(DAY, -14, GETDATE())

• WHERE ReturnStatus = 'Returned On Time' AND BorrowDate IS NOT NULL

Why is the vw_AvailableBooks view allowed to be updated using an UPDATE statement?

• The view contains only one base table and no complex joins

• The view filters with a condition but has no case logic

• The view returns selected fields from multiple base tables

• The view was indexed on its foreign key columns

Which change would most likely prevent the vw_BorrowedBooksInfo view from being updated?

• Adding a CASE expression to classify borrow status

• Adding an ORDER BY clause to the view definition

• Adding a JOIN with the Books table for genre details

• Adding a GROUP BY clause to summarize borrow counts

Why is it helpful to calculate ReturnStatus directly within the vw_BorrowedBooksInfo view?

• It makes output easier to interpret across related queries

• It forces SQL Server to cache values for quick filtering

• It avoids needing to create an additional summary table

• It ensures primary keys are properly preserved in joins

Which view structure would make updating book quantities through vw_AvailableBooks invalid?

• Adding a derived column using arithmetic logic

• Adding a CASE expression for genre classification

• Adding a JOIN with another supporting lookup table

• Adding a column alias to change a field name

Which method would best identify members who have never borrowed a book?

• Using a GROUP BY clause on the Members table

• Joining Members and BorrowedBooks with INNER JOIN

• Performing a LEFT JOIN and filtering for NULL in the result

• Filtering the BorrowedBooks table for ReturnDate IS NULL

What problem might occur if a view like vw_AvailableBooks is used in many update operations?

• It can lead to overuse of server resources in execution

• It may result in base table locks on foreign key updates

• It may allow invalid data to pass through constraints

• It could become outdated if view logic changes later

What is the best reason to avoid placing complex logic like aggregations in updateable views?

• Aggregates do not reflect base table structure accurately

• It requires extensive use of subqueries and filters

• SQL Server prevents updates due to structural complexity

• It forces developers to use temporary tables instead

What makes the vw_BorrowedBooksInfo view useful for reporting and dashboards?

• It automatically calculates and classifies return status

• It reduces the need for data validation before inserts

• It ensures all columns are directly editable by users

• It removes duplicate entries during borrow operations