James Madison University - College of Business



Working with T-SQL

Language Extensions, Stored Procedures, and User-Defined Functions

T-SQL, or Transact-SQL is Microsoft’s extension to the root SQL language. Oracle has a similar extension called PL/SQL.

The core SQL is a 4th generation language, in the sense that it is nonprocedural (unlike Java and VB). This makes it simpler to use, but also imposes limits on its functionality. Using additional constructs in T-SQL, you can make it behave a lot more like a procedural language such as Java or VB. For example, T-SQL includes in-memory variables for temporary storage of data (not stuff stored in tables). It also includes control structures such as if-statements, case-statements, and loops. In addition, using a feature called Common Table Expressions (CTE), you can handle hierarchical data (data that would ideally be expressed as trees requiring recursive processing). All of these additional language constructs can be used in queries, and can also be stored as code in permanent parameterizable modules such as procedures or functions. These stored procedures and user-defined functions are objects in the database, just like tables or indexes.

In this exercise, you will get experience working with all these things. Throughout the exercise, I ask you to answer questions, create T-SQL queries using the above-listed extensions, provide screen shots of query results, and create and use stored procedures and user-defined functions which work with AdventureWorks data.

Use this Word document to answer the questions below. Please make your answers a different color, such as blue, so they are easy to distinguish. Your deliverables for this exercise includes the answers, screen shots that you will embed in this document, and additional .SQL files containing queries and/or stored procedure code.

1. Using the CASE clause in an SQL query

Read this page: (v=SQL.90).aspx. Answer the following:

A. Which Java statement comes closest to what the CASE clause does?

B. Which VB statement comes closest to what the CASE clause does?

C. Which Microsoft Access function comes closest to what the CASE clause does?

D. There are several sample queries and one user defined function using the CASE clause in this page. Execute the queries from examples A, B, C, D, and F. Capture screen images of the results, and paste them here in the document.

E. Make up a useful query of your own, using the CASE clause and store it in a .SQL file. This query should provide something that you can’t do using regular SQL. Name this file Exercise 1E.SQL. Below, explain what this query does, and why you need the CASE statement for it. Capture a screen image of its result and paste it into this document underneath your explanation.

2. Using Variables and Loops

Read the article beginning at . Answer the following:

A. Create queries in SQL Server Management Studio and run the code for both examples on the first page of the article. Capture screen images of the output from both of these executions and paste them here:

B. In SQL Server Management Studio, what tab does the output from the print statement go to?

C. What is the special symbol that designates a variable in T-SQL?

D. Explain what the following two T-SQL statements do:

Break –

Continue –

E. The Set statement in T-SQL is used for (select one):

a) input and output

b) assignment into a variable

c) selecting the database to use

d) performing a query

F. In T-SQL, what is the equivalent of Visual Basic’s Dim statement?

G. Create a T-SQL query that computes an exponentiation (i.e., one number raised to the power of another). For example, if the number is 2 and the exponent is 8, the result printed out should be 256. Use variables for these numbers (you can make initial assignments of 2 and 8 in the code). The resulting output should look something like this:

2 to the power of 8 is: 256

NOTE: You may not use the built-in POWER function. Instead you must implement the loop for calculating the exponentiation yourself.

Store this query in a file named Exercise 2G.SQL.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download