Page 132 - Computer Software Application TP - Volume 1

COMPUTER SOFTWARE APPLICATION - CITS

6 INDEXING –
CREATE INDEX idx email ON users(email);
7 TESTING –
-- Test Stored Procedure

CALL GetBooksByAuthor(‘Author 1’);
-- Test Trigger (Assuming you have a ‘borrowed books’ table)
INSERT INTO borrowed books (user id, book id) VALUES (1, 1);
-- Test Cursor
CALL DisplayBooksAndQuantities();

-- Test Indexing
SELECT * FROM users WHERE email = ‘user1@example.com’;
Question
1 How would you call the InsertUser stored procedure to add a new user with the username “nsti” and email
“nsti@example.com” to the database?
2 Explain the purpose of the before insert user trigger. How does it modify the incoming data before insertion into
the users table?

3 What does the DisplayUsers procedure do, and how would you execute it to see the details of all users in the
users table?
4 Describe the purpose of the idx username index on the users table. How does it improve query performance?

117
CITS : IT & ITES - Computer Software Application - Exercise 36 CITS : IT & ITES - Computer Software Application - Exercise 36