Chapter 01 - SELECT Statements

Querying Foundations

Understanding the Foundations of T-SQL

Using T-SQL in a Relational Way

USE TSQL2012;

SELECT country

FROM HR.Employees;

SELECT *

FROM HR.Employees;

SELECT DISTINCT country

FROM HR.Employees;

SELECT empid, lastname

FROM HR.Employees;

SELECT empid, lastname

FROM HR.Employees

ORDER BY empid;

SELECT empid, lastname

FROM HR.Employees

ORDER BY 1;

SELECT empid, firstname + ' ' + lastname

FROM HR.Employees;

SELECT empid, firstname + ' ' + lastname AS fullname

FROM HR.Employees;

Logical Query Processing

T-SQL as a Declarative English-Like Language

SELECT shipperid, phone, companyname

FROM Sales.Shippers;

Logical Query Processing Phases

SELECT country, YEAR(hiredate) AS yearhired, COUNT(*) AS numemployees

FROM HR.Employees

WHERE hiredate >= '20030101'

GROUP BY country, YEAR(hiredate)

HAVING COUNT(*) > 1

ORDER BY country, yearhired DESC;

-- Deshton, gjeni arsyen.

SELECT country, YEAR(hiredate) AS yearhired

FROM HR.Employees

WHERE yearhired >= 2003;

-- Deshton, gjeni arsyen.

SELECT empid, country, YEAR(hiredate) AS yearhired, yearhired - 1 AS prevyear

FROM HR.Employees;

Getting Started with the SELECT Statement

Using the FROM and SELECT Clauses

The FROM Clause

USE TSQL2012;

-- shembull baze

SELECT empid, firstname, lastname

FROM HR.Employees;

-- vendosja e nje table alias

SELECT E.empid, firstname, lastname

FROM HR.Employees AS E;

The SELECT Clause

-- Marrja e informacionit mbi disa atribute

SELECT empid, firstname, lastname

FROM HR.Employees;

-- Deshton, gjeni arsyen.

SELECT empid, firstname lastname

FROM HR.Employees;

-- Aliasing per riemerim te kolones

SELECT empid AS employeeid, firstname, lastname

FROM HR.Employees;

-- Shprehje pa alias

SELECT empid, firstname + N' ' + lastname

FROM HR.Employees;

-- Shprehje me alias

SELECT empid, firstname + N' ' + lastname AS fullname

FROM HR.Employees;

-- Heqje e dublikatave DISTINCT

SELECT DISTINCT country, region, city

FROM HR.Employees;

-- SELECT pa FROM

SELECT 10 AS col1, 'ABC' AS col2;

Working with Data Types and Built-In Functions

Choosing the Appropriate Data Type

-- Perafrimi i tipeve

DECLARE @f AS FLOAT = '29545428.022495';

SELECT CAST(@f AS NUMERIC(28, 14)) AS value;

-- Deshton, gjeni arsyen.

SELECT CAST('abc' AS INT);

-- Deshton,kthen nje NULL, gjeni arsyen.

SELECT TRY_CAST('abc' AS INT);

Date and Time Functions:

Current Date and Time

SELECT

GETDATE() AS [GETDATE],

CURRENT_TIMESTAMP AS [CURRENT_TIMESTAMP],

GETUTCDATE() AS [GETUTCDATE],

SYSDATETIME() AS [SYSDATETIME],

SYSUTCDATETIME() AS [SYSUTCDATETIME],

SYSDATETIMEOFFSET() AS [SYSDATETIMEOFFSET];

SELECT

CAST(SYSDATETIME() AS DATE) AS [current_date],

CAST(SYSDATETIME() AS TIME) AS [current_time];

Date and Time Parts

-- DATEPART

SELECT DATEPART(month, '20120212');

-- DAY, MONTH, YEAR

SELECT

DAY('20120212') AS theday,

MONTH('20120212') AS themonth,

YEAR('20120212') AS theyear;

-- DATENAME

SELECT DATENAME(month, '20090212');

-- fromparts

SELECT

DATEFROMPARTS(2012, 02, 12),

DATETIME2FROMPARTS(2012, 02, 12, 13, 30, 5, 1, 7),

DATETIMEFROMPARTS(2012, 02, 12, 13, 30, 5, 997),

DATETIMEOFFSETFROMPARTS(2012, 02, 12, 13, 30, 5, 1, -8, 0, 7),

SMALLDATETIMEFROMPARTS(2012, 02, 12, 13, 30),

TIMEFROMPARTS(13, 30, 5, 1, 7);

-- EOMONTH

SELECT EOMONTH(SYSDATETIME());

Add dhe Diff Functions

-- DATEADD

SELECT DATEADD(year, 1, '20120212');

-- DATEDIFF

SELECT DATEDIFF(day, '20110212', '20120212');

Offset Related Functions

-- SWITCHOFFSET

SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '-05:00');

SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '-08:00');

-- TODATETIMEOFFSET

SELECT TODATETIMEOFFSET('20130212 14:00:00.0000000', '-08:00') ;

-- te dyja funksionet

SELECT

SWITCHOFFSET('20130212 14:00:00.0000000 -08:00', '-05:00') AS [SWITCHOFFSET],

TODATETIMEOFFSET('20130212 14:00:00.0000000', '-08:00') AS [TODATETIMEOFFSET];

Character Functions

-- Concatenation

SELECT empid, country, region, city,

country + N',' + region + N',' + city AS location

FROM HR.Employees;

-- Null behet string bosh

SELECT empid, country, region, city,

country + COALESCE( N',' + region, N'') + N',' + city AS location

FROM HR.Employees;

-- CONCAT

SELECT empid, country, region, city,

CONCAT(country, N',' + region, N',' + city) AS location

FROM HR.Employees;

Substring Extraction dhe Position

SELECT SUBSTRING('abcde', 1, 3); -- 'abc'

SELECT LEFT('abcde', 3); -- 'abc'

SELECT RIGHT('abcde', 3); -- 'cde'

SELECT CHARINDEX(' ','Itzik Ben-Gan'); -- 6

SELECT PATINDEX('%[0-9]%', 'abcd123efgh'); -- 5

String Length

SELECT LEN(N'xyz'); -- 3

SELECT DATALENGTH(N'xyz'); -- 6

String Alteration

SELECT REPLACE('.1.2.3.', '.', '/'); -- '/1/2/3/'

SELECT REPLICATE('0', 10); -- '0000000000'

SELECT STUFF(',x,y,z', 1, 1, ''); -- 'x,y,z', STUFF ( character_expression , start , length , replaceWith_expression )

String Formating

SELECT UPPER('aBcD'); -- 'ABCD'

SELECT LOWER('aBcD'); -- 'abcd'

SELECT RTRIM(LTRIM(' xyz ')); -- 'xyz'

SELECT FORMAT(1759, '000000000'); -- '000001759'