← Back to Dashboard

Day 7: Practice Exercises

Hands-On SQL Assignments

Scenario: You are the database developer for a bookstore. You need to write reusable code blocks for the web application to use. You are working with a table named Books (BookID, Title, Author, Genre, Price, StockQuantity).

Task 1: The Basic Procedure

The web application has a "Clearance" page. Write a CREATE PROCEDURE statement named sp_GetClearanceBooks that returns all columns for books where the Price is less than $10.00.
Write the EXEC command to run your new procedure.

Task 2: Single Input Parameter

Users need to be able to filter books by Genre.
Create a procedure named sp_GetBooksByGenre that accepts one input parameter: @SearchGenre VARCHAR(50).
Inside the procedure, write a SELECT statement that filters the Books table where the Genre matches the @SearchGenre parameter.
Execute the procedure searching for the genre 'Sci-Fi'.

Task 3: Multiple Input Parameters (Data Manipulation)

When a customer buys a book, the stock needs to decrease. Instead of letting the application write a raw UPDATE query, you must build a secure procedure.
Create a procedure named sp_UpdateBookStock that accepts two parameters: @TargetBookID INT and @NewQuantity INT.
Inside the procedure, write an UPDATE statement that changes the StockQuantity to the @NewQuantity, WHERE the BookID matches the @TargetBookID.

Task 4: Procedure Maintenance

You realize the sp_GetClearanceBooks procedure from Task 1 should only show books that actually have stock. Write an ALTER PROCEDURE statement to update the query logic so it checks for Price < 10 AND StockQuantity > 0.