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'

Ushtrime

Ndertoni Query qe te shfaqin rezultatet e meposhtme:

1. Te afishohen te gjitha informacionet nga Sales.Customers.

2. Te afishohen te gjitha contactname nga Sales.Customers por me emer kolone "Emrat e kontakteve".

3. Te afishohen te gjitha contactname dhe companyname nga Sales.Customers ku tabela te riemerohet si "Klientet".

4. Te listohen te gjitha titleofcourtesy te vecanta nga HR.Employees pa perseritje te tyre.

5. Te shfaqen numrat e kolones n tek tabela Nums me dy shifra pas presjes, tipi numeric.

6. Afishoni se sa eshte ora tani sipas UTC.

7. Gjeni sa dite kane kaluar qe nga dita kur keni lindur deri tani.

8. Shfaqni nen emrin e nje kolone te vetme "Informacion mbi produktin" te dhenat productname, unitprice te ndara me hapesire nga njera tjetra, nga tabela Production.Products.

9. Afishoni te gjithe numrat e telefonit (phone) dhe pozicionin ne te cilin ndodhet nje nga numrat [2-5] tek keto numra telefoni, te tabeles Production.Suppliers.

10. Afishoni te gjitha contactname nga Sales.Customers por me emer kolone "Emrat e kontakteve" dhe me te gjithe shkronjat te medha te shtypit.