Database Reference
In-Depth Information
7.5 Summary
In this chapter, you have learned some programming extensions. You have used Transact-SQL
to create, execute, and test functions, stored procedures, and triggers. Examples were used to
illustrate how to declare and use variables, and use low-control structures. In this chapter, the
advantages of stored procedures and triggers have been discussed in detail. hrough hands-on
practice, you have learned to create, execute, and test stored procedures and triggers. To further
understand the importance of triggers, you have developed various trigger applications in data-
base maintenance and implementation of business logic. here is much more to Transact-SQL. In
the next chapter, we will discuss the concepts and applications of some other important database
objects such as View, Index, and Cursor.
Review Questions
1. Why do we need variables in SQL code?
2. Why do we need low-control structures in SQL code?
3. Name eight low-control structures and describe how to use them.
4. State the diference between a function and a procedure.
5. What types of data can the sql_variant hold?
6. Create a user-deined function that updates the capacity of a classroom on a given new
capacity. Use an UPDATE clause to demonstrate the usage of your function. Run your SQL
statements in Windows Azure SQL Database.
7. Create a user-deined function that calculates the total classroom capacity for the East build-
ing. Use an UPDATE clause to demonstrate the usage of your function. Run your SQL
statements in Windows Azure SQL Database.
8. Describe table-valued user-deined functions.
9. For a given student id, create an inline table function to return a course taken by the student.
Use a SELECT statement to show the result. Run your SQL statements in Windows Azure
SQL Database.
10. What are the advantages of a stored procedure?
11. How is a stored procedure used?
12. Where can a trigger be used?
13. What do we need to be careful about when using a trigger?
14. How can an INSTEAD OF trigger be used? Give an example.
15. What are the tasks that an AFTER trigger can accomplish?
16. What is the default trigger type?
17. How can a trigger be executed?
18. Explain what the logical tables INSERTED and DELETED do.
19. Create an AFTER trigger on the CLASSROOM table. he trigger will display a message to
indicate that a new classroom is added. Use an INSERT statement to demonstrate the use of
the trigger. Run your SQL statements in Windows Azure SQL Database.
20. Create an INSTEAD OF trigger to verify if the building code is correct when adding a new
classroom to the table CLASSROOM. Use an INSERT statement to demonstrate the use of
the trigger. Run your SQL statements in Windows Azure SQL Database.
