Transact-SQL (T-SQL)

JOURNAL #SIX [DAT602]

Transact-SQL (T-SQL)

WHAT

SQL Queries

Working with data requires queries to be called against the database, these can take the form of:

Select queries - The retrieval of information from the database that is displayed in a ‘virtual’ table.
Action queries - Requests activities on the database such as the creation of new tables.
Aggregate queries - Perform calculations against a determined set of values.

Select Queries

Select queries retrieve rows or columns from tables within a database, the select statement can include the following clauses:

INTO - Creates a new table and inserts the results of the query

FROM - Can be used in SELECT, UPDATE and DELETE statements and specifies the table source

JOIN - A join is used where a result is produced from two or more tables in the database, join types are:

INNER - Returns all matching rows, discarding unmatched rows
FULL [OUTER] - A row from either the right or left table not meeting the join condition is included in the result set
LEFT [OUTER] - All rows from the left table not meeting the join condition is included in the result set
RIGHT [OUTER] - All rows from the right table not meeting the join condition is included in the result set

WHERE - Used to define the search condition for the returned rows

GROUP BY - Groups rows with the same values into summary rows, can be used with aggregate functions such as COUNT, SUM, AVG

HAVING - Exclusive use in SELECT statements and specifies a search condition for a GROUP BY or aggregate

DISTINCT - Used to return only those values that are different, those that are not duplicated in a table

OVER - Defines a set of rows within a query result as specified by the user

Action Queries

Action queries are used to make changes in a database, an action query can take the form of one of the following statements:

CREATE - Used to make tables in the database

ALTER - Modifies columns or constraints in a table

INSERT - Adds data into current table rows

UPDATE - Takes existing data in a table and changes it

DELETE - Deletes data contained in rows of a database

DROP - Deletes tables from a database

Aggregated Queries

Aggregate functions perform calculations on a set of values to return a single value, these types of functions ignore values that are NULL and are commonly used with the SELECT statement and GROUP BY clause.

An aggregate query can include the following clauses:

AVG - Used to return the average of a set of values in a group, ignoring NULL values

COUNT - Returns an int data type value representing the number of items found in a group

GROUPING - If the specified column expression is aggregated the statement returns 1, if not it returns 0

SUM - Takes all the values in a column and returns the sum, can only be used in numerical columns

MAX - The maximum value in the expression is returned

MIN - The minimum value in the expression is returned

WHY

Learning SQL is important primarily because it is the most commonly used language in database design. This makes it a valuable skill to have for IT professionals, particularly as data has become the most valuable asset today, being an important part of the organisation’s decision-making process.

The commonality of SQL makes it an incredibly sought-after skill by employees. SQL is a query language that applies to most database engines, meaning knowledge of the language allows professionals to work across any relational database.

HOW

There are, however, some differences between SQL depending on the application. Microsoft SQL Server uses different keywords to MySQL or MariaDB for example, in SQL Server the IDENTITY keyword is used to automatically increase the value of an int column type, whereas the keyword in MySQL and MariaDB is AUTO_INCREMENT.

The following SQL is procedural transactions in SQL Server:

References

cawrites. (n.d.). Aggregate Functions (Transact-SQL)—SQL Server. Retrieved April 6, 2021, from https://docs.microsoft.com/en-us/sql/t-sql/functions/aggregate-functions-transact-sql

JanbaskTraining. (2019, February 28). What Is SQL Queries? List Of All SQL Queries With Examples. JanbaskTraining. https://www.janbasktraining.com/blog/what-is-sql-queries

SQL GROUP BY Statement. (n.d.). Retrieved April 6, 2021, from https://www.w3schools.com/sql/sql_groupby.asp

VanMSFT. (n.d.-a). FROM clause plus JOIN, APPLY, PIVOT (T-SQL)—SQL Server. Retrieved April 6, 2021, from https://docs.microsoft.com/en-us/sql/t-sql/queries/from-transact-sql

VanMSFT. (n.d.-b). Queries—SQL Server. Retrieved April 6, 2021, from https://docs.microsoft.com/en-us/sql/t-sql/queries/queries

What is SQL and why is it important? - Quora. (n.d.). Retrieved April 6, 2021, from https://www.quora.com/What-is-SQL-and-why-is-it-important?share=1