DAY-1

T-SQL Training – Day 1

Topics:

1. Introduction to T-SQL
2. SQL vs T-SQL
3. SQL Sub-Languages
4. SELECT Statement
5. WHERE Clause
6. ORDER BY Clause
7. DISTINCT Keyword
8. Aliasing (Column & Table)

1. Introduction to T-SQL

T-SQL (Transact-SQL) is Microsoft’s extension of SQL for working with SQL Server.

🧠 Key Points:

• T-SQL = SQL + Programming Logic
• Used in Microsoft SQL Server for querying and managing data.

What is SQL and T-SQL?

Term	Meaning
SQL	Structured Query Language — a standard language to interact with databases (e.g., MySQL, PostgreSQL, SQL Server).
T-SQL	Transact-SQL — Microsoft’s extension of SQL used in SQL Server. It includes procedural programming features like variables, loops, functions, error handling, etc.

Basic Structure of a T-SQL Query

SELECT column1, column2, ...

FROM table_name

WHERE condition;

 

2. SQL vs T-SQL

Feature	SQL (Standard)	T-SQL (SQL Server)
Platform	Cross-platform	Microsoft SQL Server only
Procedural	❌	✅ (BEGIN...END, IF, TRY...CATCH)
Variables	❌	✅ DECLARE @var
Error Handling	❌	✅ TRY...CATCH

Example 1 – SQL:

SELECT * FROM Employees;

Example 2 – T-SQL:

DECLARE @Dept VARCHAR(20) = 'IT';

SELECT * FROM Employees WHERE Department = @Dept;

✨ Animation Idea: A switch toggling between “Generic SQL” and “T-SQL” with features lighting up.

3. SQL Sub-Languages

Sub-Language	Description	Example
DQL	Data Query Language	SELECT
DML	Data Manipulation	INSERT, UPDATE, DELETE
DDL	Data Definition	CREATE, ALTER, DROP
DCL	Data Control	GRANT, REVOKE
TCL	Transaction Control	BEGIN, COMMIT, ROLLBACK

Example 1 – DML:

INSERT INTO Employees (Name, Department) VALUES ('John', 'IT');

Example 2 – DDL:

CREATE TABLE Employees (ID INT, Name VARCHAR(100));

• DDL builds the structure,
• DML fills it with furniture (data),
• DCL installs locks (security),
• TCL handles moving decisions (transactions).

4. SELECT Statement

1. --Basic SELECT - Choose Specific Columns

SELECT FirstName, LastName FROM Employees;

🎯Gets only the First Name and Last Name from the Employees table.

 

2. SELECT All Columns

SELECT * FROM Employees;

🎯 Fetches all columns from the Employees table.

 

3. --SELECT with Aliasing

SELECT FirstName AS Name, Salary AS Income

FROM Employees;

🎯 Renames columns in the result set to make them more readable.

 

4. --SELECT with Expressions (Calculated Columns)

SELECT FirstName, Salary, Salary * 0.1 AS Bonus

FROM Employees;

🎯 Calculates 10% bonus and shows it as a new column.

 

5. --SELECT with DISTINCT

SELECT DISTINCT Department FROM Employees;

🎯 Fetches unique departments, removing duplicates.

 

6. --SELECT with WHERE Condition

SELECT FirstName, Department

FROM Employees

WHERE Department = 'IT';

🎯 Fetches employees only from the IT department.

 

7. --SELECT with ORDER BY Clause

SELECT FirstName, Salary

FROM Employees

ORDER BY Salary DESC;

🎯 Displays employees with the highest salary first.

 

8. --SELECT with TOP Clause (SQL Server Specific)

SELECT TOP 5 FirstName, Salary

FROM Employees

ORDER BY Salary DESC;

🎯 Shows top 5 highest paid employees.

 

9. --SELECT with JOIN (Preview)

SELECT E.FirstName, D.DeptName

FROM Employees E

JOIN Departments D ON E.DeptID = D.ID;

 

10. --SELECT with Aggregation Functions

SELECT Department, AVG(Salary) AS AvgSalary FROM Employees

GROUP BY Department;

 

11. --Select All Columns

SELECT * FROM Employees;

🧠 Retrieves every column and every row from the Employees table.

 

12. --Select Specific Columns

SELECT FirstName, LastName FROM Employees;

🧠 Fetches just the FirstName and LastName columns.

 

13. --Select a Single Column

SELECT Department FROM Employees;

🧠 Only returns the Department column from all records.

 

14. --Select with Column Aliasing

SELECT FirstName AS Name, Salary AS Pay FROM Employees;

🧠 Renames FirstName to Name and Salary to Pay in the output.

 

15. --Select with Literal Values

SELECT 'Hello World' AS Greeting;

🧠 Returns one row with a column labeled Greeting that shows "Hello World".

 

16. --Select Constant Values with Table

SELECT FirstName, 'Active' AS Status FROM Employees;

🧠 Adds a fixed column named Status with value 'Active' for all rows.

 

17. --Select Using Expressions

SELECT FirstName, Salary * 12 AS AnnualSalary FROM Employees;

🧠 Calculates annual salary from the monthly Salary column.

 

18. --Select with Concatenation

SELECT FirstName + ' ' + LastName AS FullName FROM Employees;

🧠 Combines first and last names into one FullName column.

 

19. --Select from Multiple Tables (Not Join)

SELECT * FROM Employees;

SELECT * FROM Departments;

🧠 Two independent SELECT statements pulling data from different tables.

 

20. --Select with Functions

SELECT UPPER(FirstName) AS CapitalName FROM Employees;

🧠 Uses the UPPER() function to convert names to uppercase.

Would you like me to create a mini GIF animation to show examples like:

•      SELECT *

•      SELECT specific columns

•      SELECT with aliasing

... cycling through on a sample table? I can prepare that next!

 

21. --Select a Numeric Value with Every Row

SELECT FirstName, 100 AS BonusPoints FROM Employees;

🧠 Adds a fixed value 100 as a new column for every employee.

 

22. --Select Date Literal Along with Table Data

SELECT FirstName, GETDATE() AS CurrentDate FROM Employees;

🧠 Displays current system date/time along with each employee.

 

23. --Select Mathematical Expression Without Table

SELECT 5 * 12 AS Total;

🧠 Returns a single value row with result 60 in a column named Total.

 

24. Select with Multiple Constants

SELECT 'SR University' AS College, 2025 AS Year;

🧠 Shows a single row with constant text and number (useful in testing).

 

25. Select Function Output Without a Table

SELECT DB_NAME() AS CurrentDatabase;

🧠 Returns the name of the currently connected database.

 

26. Select Columns from a View

SELECT * FROM ActiveEmployeesView;

🧠 Retrieves data from a SQL view instead of a physical table.

 

27. Select Columns from a Temporary Table

SELECT * FROM #TempEmployees;

🧠 Shows data from a temporary table created earlier in the session.

 

28. Select Columns from a Table Variable

DECLARE @EmployeeTable TABLE (Name VARCHAR(50));

-- Assume values inserted here...

SELECT * FROM @EmployeeTable;

🧠 Used in stored procedures and advanced queries.

 

29. Select Using Built-in Function as Column

SELECT FirstName, LEN(FirstName) AS NameLength FROM Employees;

🧠 Shows length of each employees name.

 

30. --Select Multiple Copies of Same Column

SELECT FirstName, FirstName AS NameAgain FROM Employees;

 

31. --Get Current Date and Time for Each Row

SELECT FirstName, GETDATE() AS CurrentDateTime FROM Employees;

🧠 Shows system’s current date and time.

 

32. --Get Only the Current Date

SELECT FirstName, CAST(GETDATE() AS DATE) AS CurrentDate FROM Employees;

🧠 Extracts just the date part, removing the time.

 

33. --Get Only the Current Time

SELECT FirstName, CAST(GETDATE() AS TIME) AS CurrentTime FROM Employees;

🧠 Extracts only the time portion from GETDATE().

 

34. --Add Days to Current Date

SELECT FirstName, DATEADD(DAY, 7, GETDATE()) AS NextWeek FROM Employees;

🧠 Adds 7 days to current date.

 

35. --Find the Difference Between Two Dates

SELECT FirstName, DATEDIFF(YEAR, HireDate, GETDATE()) AS YearsWorked FROM Employees;

🧠 Calculates the number of years since the employee was hired.

🧮 Examples with String and Numeric Functions

 

36. --Convert Name to Uppercase

SELECT FirstName, UPPER(FirstName) AS UpperName FROM Employees;

 

37. --Convert Name to Lowercase

SELECT FirstName, LOWER(FirstName) AS LowerName FROM Employees;

 

38. --Get First 3 Letters of Name

SELECT FirstName, LEFT(FirstName, 3) AS ShortName FROM Employees;

 

39. --Round Off Salary

SELECT FirstName, ROUND(Salary, -3) AS RoundedSalary FROM Employees;

 

40. --Get Length of Name

SELECT FirstName, LEN(FirstName) AS NameLength FROM Employees;

 

41. --Combine Date and Name

SELECT FirstName + ' joined on ' + CAST(HireDate AS VARCHAR) AS JoinInfo FROM Employees;

 

 

Covered Categories (All with Examples)
1. String Functions
2. Date & Time Functions
3. Mathematical Functions
4. System Functions
5. Type Conversion Functions
6. Metadata Functions
7. Logical / NULL Handling Functions
🧵 1. STRING FUNCTIONS
	Function	Example
	UPPER()	SELECT UPPER(FirstName) AS UpperName FROM Employees;
	LOWER()	SELECT LOWER(FirstName) AS LowerName FROM Employees;
	LEN()	SELECT LEN(FirstName) AS NameLength FROM Employees;
	LEFT()	SELECT LEFT(FirstName, 3) AS ShortName FROM Employees;
	RIGHT()	SELECT RIGHT(FirstName, 2) AS Ending FROM Employees;
	LTRIM()	SELECT LTRIM(' John') AS Trimmed;
	RTRIM()	SELECT RTRIM('John ') AS Trimmed;
	REPLACE()	SELECT REPLACE('abc-def', '-', '_') AS Result;
	SUBSTRING()	SELECT SUBSTRING(FirstName, 2, 3) AS MidName FROM Employees;
	CHARINDEX()	SELECT CHARINDEX('a', FirstName) AS Position FROM Employees;
⏰ 2. DATE & TIME FUNCTIONS
	GETDATE()	SELECT GETDATE() AS Now;
	SYSDATETIME()	SELECT SYSDATETIME() AS PreciseTime;
	CAST(... AS DATE)	SELECT CAST(GETDATE() AS DATE) AS OnlyDate;
	DATEADD()	SELECT DATEADD(DAY, 5, GETDATE()) AS Plus5Days;
	DATEDIFF()	SELECT DATEDIFF(YEAR, HireDate, GETDATE()) AS YearsWorked FROM Employees;
	EOMONTH()	SELECT EOMONTH(GETDATE()) AS EndOfMonth;
	YEAR()	SELECT YEAR(HireDate) AS YearJoined FROM Employees;
	MONTH()	SELECT MONTH(HireDate) AS MonthJoined FROM Employees;
	DAY()	SELECT DAY(HireDate) AS DayJoined FROM Employees;
🔢 3. MATH FUNCTIONS
	ABS()	SELECT ABS(-10) AS Absolute;
	CEILING()	SELECT CEILING(Salary) AS UpRound FROM Employees;
	FLOOR()	SELECT FLOOR(Salary) AS DownRound FROM Employees;
	POWER()	SELECT POWER(2, 3) AS Cube;
	ROUND()	SELECT ROUND(Salary, -2) AS Rounded FROM Employees;
	SQRT()	SELECT SQRT(144) AS Root;
	PI()	SELECT PI() AS PiValue;
🛠️ 4. SYSTEM FUNCTIONS
	DB_NAME()	SELECT DB_NAME() AS DatabaseName;
	HOST_NAME()	SELECT HOST_NAME() AS HostMachine;
	SUSER_NAME()	SELECT SUSER_NAME() AS LoginName;
	NEWID()	SELECT NEWID() AS UniqueID;
	GETUTCDATE()	SELECT GETUTCDATE() AS UTCNow;
🔁 5. TYPE CONVERSION FUNCTIONS
	CAST()	SELECT CAST(GETDATE() AS VARCHAR) AS DateText;
	CONVERT()	SELECT CONVERT(VARCHAR, GETDATE(), 103) AS UKFormatDate;
	TRY_CAST()	SELECT TRY_CAST('123' AS INT) AS SafeConvert;
	TRY_CONVERT()	SELECT TRY_CONVERT(DATE, '2023-09-01') AS SafeDate;
📋 6. METADATA FUNCTIONS
	OBJECT_NAME()	SELECT OBJECT_NAME(OBJECT_ID('Employees')) AS ObjectName;
	OBJECT_ID()	SELECT OBJECT_ID('Employees') AS ObjectID;
	COL_LENGTH()	SELECT COL_LENGTH('Employees', 'FirstName') AS Length;
	COLUMNPROPERTY()	SELECT COLUMNPROPERTY(OBJECT_ID('Employees'), 'FirstName', 'Precision') AS Precision;
❓ 7. NULL & LOGIC FUNCTIONS
	ISNULL()	SELECT ISNULL(ManagerName, 'Not Assigned') AS Supervisor FROM Employees;
	COALESCE()	SELECT COALESCE(ManagerName, AltManager, 'None') AS FinalManager FROM Employees;
	IIF()	SELECT IIF(Salary > 50000, 'High', 'Low') AS SalaryLevel FROM Employees;

 

5. WHERE Clause

Filters rows based on conditions.

SELECT * FROM Employees WHERE Department = 'HR';

Example 1:

SELECT * FROM Employees WHERE Salary > 50000;

Example 2:

SELECT * FROM Employees WHERE Department = 'IT' AND Age < 30;

 

6. ORDER BY Clause

Sorts result by one or more columns.

SELECT * FROM Employees ORDER BY Salary DESC;

Example 1:

SELECT Name FROM Students ORDER BY Name ASC;

Example 2:

SELECT Name, Marks FROM Students ORDER BY Marks DESC;

 

7. DISTINCT Keyword

Removes duplicates from result set.

SELECT DISTINCT Department FROM Employees;

Example 1:

SELECT DISTINCT City FROM Customers;

Example 2:

SELECT DISTINCT Country FROM Suppliers;

 

8. Aliasing

Column Alias:

SELECT FirstName AS Name FROM Employees;

Table Alias:

SELECT E.FirstName FROM Employees AS E;

Example 1 – Column:

SELECT Salary AS MonthlySalary FROM Employees;

Example 2 – Table:

SELECT C.Name FROM Customers AS C;

Previous                                                                     Next