Chapter 01 - SELECT Statements

Ky leksion 120 minutesh perfshin 49 faqet e para te librit te kursit.

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'