TSQL practice

What is TSQL

T-SQL stands for Transact-SQL, which is an extension of SQL used by Microsoft SQL Server and Sybase ASE (Adaptive Server Enterprise) databases. T-SQL adds additional features to SQL, including programming constructs like variables, control flow statements, and error handling.

T-SQL includes all the basic SQL statements, such as SELECT, INSERT, UPDATE, and DELETE, as well as additional statements for creating and modifying database objects, like stored procedures, triggers, and views.

In addition, T-SQL includes support for transactions, which allows you to group a set of SQL statements into a single transaction that is either committed or rolled back as a single unit, ensuring data integrity.

T-SQL also provides built-in functions that can be used to perform complex calculations, manipulate strings, and work with date and time values.

Overall, T-SQL provides a robust set of features that can be used to create powerful, scalable database applications on Microsoft SQL Server and Sybase ASE platforms.

TSQL use cases

UPDATE using a SELECT subquery

Suppose we have two tables, orders and order_details, where orders contains order information and order_details contains details of the products ordered in each order. We want to update the order_date field in the orders table to the most recent order date for each customer, which we can find in the order_details table.

UPDATE orders
SET order_date = (
  SELECT MAX(order_date)
  FROM order_details
  WHERE orders.customer_id = order_details.customer_id
)

In this example, the subquery (SELECT MAX(order_date) FROM order_details WHERE orders.customer_id = order_details.customer_id) is used to retrieve the maximum order date for each customer in the order_details table. The WHERE clause ensures that only the order dates for the corresponding customer are selected.

The SET clause sets the order_date field in the orders table to the value returned by the subquery, effectively updating the order date to the most recent order date for each customer.

Select data from two databases

To select data from two different databases in T-SQL, you can fully qualify the table names with their database names using the following syntax:

SELECT database1.dbo.table1.column1, database2.dbo.table2.column2
FROM database1.dbo.table1
JOIN database2.dbo.table2
ON database1.dbo.table1.id = database2.dbo.table2.id;

In this example, we are selecting data from two different databases, database1 and database2. The dbo refers to the database owner and is typically set to dbo for most user-defined objects.

The JOIN keyword is used to join the two tables, table1 from database1 and table2 from database2, based on a common column id.

Note that you need to have appropriate permissions to access both databases and the tables within them. If the databases are on different servers, you may need to use a linked server to access the data from the other server.

Update using ORDER BY

in T-SQL, you cannot use the ORDER BY clause directly in an UPDATE statement. However, you can use a subquery with the TOP and ORDER BY clauses to update the top N rows based on a specific order.

Here is an example of an UPDATE statement that uses a subquery with TOP and ORDER BY to update the top 10 employees with the highest salaries:

UPDATE employees
SET salary = salary * 1.1 -- increase salary by 10%
WHERE employee_id IN (
  SELECT TOP 10 employee_id
  FROM employees
  ORDER BY salary DESC
)

In this example, we are updating the salary column of the employees table for the top 10 employees with the highest salaries. The subquery selects the top 10 employee_id values based on the descending order of the salary column. The IN keyword is used to specify the list of employee IDs to be updated.

Note that the subquery must return a unique list of values, otherwise the UPDATE statement will fail with a "Subquery returned more than 1 value" error.

Variables and Loops

DECLARE @counter INT = 1;
DECLARE @max_count INT = 10;
DECLARE @result INT = 0;

WHILE (@counter <= @max_count)
BEGIN
SET @result = @result + @counter;
SET @counter = @counter + 1;
END

SELECT @result AS 'Sum of numbers 1 to 10';

In this example, we declare three variables @counter, @max_count, and @result using the DECLARE statement.

We then set the initial values for @counter and @max_count to 1 and 10, respectively.

The loop is defined using the WHILE statement, which continues to execute the block of code between the BEGIN and END keywords as long as the condition in the parentheses is true.

Inside the loop, we add the value of @counter to @result using the SET statement. We also increment @counter by 1 in each iteration of the loop.

Once the loop completes, we use a SELECT statement to display the value of @result, which should be the sum of the numbers 1 to 10.