Chapter 02 - Filtering and Sorting Data

Ky leksion 120 minutesh perfshin faqet 49-100 te librit te kursit.

CASE Expression and Related Functions

CASE expression i thjeshte

SELECT productid, productname, unitprice, discontinued,

CASE discontinued

WHEN 0 THEN 'No'

WHEN 1 THEN 'Yes'

ELSE 'Unknown'

END AS discontinued_desc

FROM Production.Products;

CASE expression duke perfshire kerkim

SELECT productid, productname, unitprice,

CASE

WHEN unitprice < 20.00 THEN 'Low'

WHEN unitprice < 40.00 THEN 'Medium'

WHEN unitprice >= 40.00 THEN 'High'

ELSE 'Unknown'

END AS pricerange

FROM Production.Products;

Filtering Data with Predicates

Predicates, Three-Valued-Logic and Search Arguments

USE TSQL2012;

--Te gjitha te dhenat e tabeles Employees

SELECT empid, firstname, lastname, country, region, city

FROM HR.Employees

--Employees nga United States

SELECT empid, firstname, lastname, country, region, city

FROM HR.Employees

WHERE country = N'USA';

--Employees nga Washington State

SELECT empid, firstname, lastname, country, region, city

FROM HR.Employees

WHERE region = N'WA';

--Employees qe nuk jane nga Washington State

SELECT empid, firstname, lastname, country, region, city

FROM HR.Employees

WHERE region <> N'WA';

--Employees qe nuk jane nga Washington State, (zgjidhje e problemit te NULL)

SELECT empid, firstname, lastname, country, region, city

FROM HR.Employees

WHERE region <> N'WA'

OR region IS NULL;

-- orders shipped on a given date

DECLARE @dt AS DATETIME = '20070212';

-- incorrect treatment of NULLs

SELECT orderid, orderdate, empid

FROM Sales.Orders

WHERE shippeddate = @dt;

-- correct treatment but not SARG

SELECT orderid, orderdate, empid

FROM Sales.Orders

WHERE COALESCE(shippeddate, '19000101') = COALESCE(@dt, '19000101');

-- correct treatment and also a SARG

SELECT orderid, orderdate, empid

FROM Sales.Orders

WHERE shippeddate = @dt

OR (shippeddate IS NULL AND @dt IS NULL);

Filtering Character Data

--Regular character string

SELECT empid, firstname, lastname

FROM HR.Employees

WHERE lastname = 'Davis';

--Unicode character string

SELECT empid, firstname, lastname

FROM HR.Employees

WHERE lastname = N'Davis';

--Employees qe u fillon mbiemri me D

SELECT empid, firstname, lastname

FROM HR.Employees

WHERE lastname LIKE N'D%';

Filtering Date and Time Data

--Language-dependent literal

SELECT orderid, orderdate, empid, custid

FROM Sales.Orders

WHERE orderdate = '02/12/07';

--Language-neutral literal

SELECT orderid, orderdate, empid, custid

FROM Sales.Orders

WHERE orderdate = '20070212';

--Jo SARG, search argument

SELECT orderid, orderdate, empid, custid

FROM Sales.Orders

WHERE YEAR(orderdate) = 2007 AND MONTH(orderdate) = 2;

--SARG

SELECT orderid, orderdate, empid, custid

FROM Sales.Orders

WHERE orderdate >= '20070201' AND orderdate < '20070301';

Sorting Data

--Query pa ORDER BY

SELECT empid, firstname, lastname, city, MONTH(birthdate) AS birthmonth

FROM HR.Employees

WHERE country = N'USA' AND region = N'WA';

--ORDER BY i thjeshte

SELECT empid, firstname, lastname, city, MONTH(birthdate) AS birthmonth

FROM HR.Employees

WHERE country = N'USA' AND region = N'WA'

ORDER BY city;

--Descending order

SELECT empid, firstname, lastname, city, MONTH(birthdate) AS birthmonth

FROM HR.Employees

WHERE country = N'USA' AND region = N'WA'

ORDER BY city DESC;

--Order ne shume kolona

SELECT empid, firstname, lastname, city, MONTH(birthdate) AS birthmonth

FROM HR.Employees

WHERE country = N'USA' AND region = N'WA'

ORDER BY city, empid;

-- Order by pozicione(nuk eshte e keshillueshme te perdoret)

SELECT empid, firstname, lastname, city, MONTH(birthdate) AS birthmonth

FROM HR.Employees

WHERE country = N'USA' AND region = N'WA'

ORDER BY 4, 1;

-- Sepse kur ndryshon SELECT list mund te harrohet te ndryshohet pozicioni tek ORDER BY

SELECT empid, city, firstname, lastname, MONTH(birthdate) AS birthmonth

FROM HR.Employees

WHERE country = N'USA' AND region = N'WA'

ORDER BY 4, 1;

-- Order by elemente te pa specifikuar ne SELECT

SELECT empid, city

FROM HR.Employees

WHERE country = N'USA' AND region = N'WA'

ORDER BY birthdate;

--Kur specifikohet DISTINCT, mund te behet order by mbi elementet ne SELECT

-- Deshton

SELECT DISTINCT city

FROM HR.Employees

WHERE country = N'USA' AND region = N'WA'

ORDER BY birthdate;

-- Sukses

SELECT DISTINCT city

FROM HR.Employees

WHERE country = N'USA' AND region = N'WA'

ORDER BY city;

-- Ndryshe nga WHERE, mund ti referohet aliaseve te krijuara ne SELECT

SELECT empid, firstname, lastname, city, MONTH(birthdate) AS birthmonth

FROM HR.Employees

WHERE country = N'USA' AND region = N'WA'

ORDER BY birthmonth;

-- NULL-et renditen te parat

SELECT orderid, shippeddate

FROM Sales.Orders

WHERE custid = 20

ORDER BY shippeddate;

Filtering Data with TOP and OFFSET-FETCH

Filtering Data with TOP

-- Gjej tre orders me te fundit

SELECT TOP (3) orderid, orderdate, custid, empid

FROM Sales.Orders

ORDER BY orderdate DESC;

-- Mund te perdoret PERCENT

/* TOP 120, Kthen 120 rreshtat e para te result set. TOP 15 PERCENT Kthen 15% e pare te totalit te rreshtave te result set.

Nese numri (15%) rezulton numer jo i plote, rrumbullakoset per te numri i plote pasardhes.*/

SELECT TOP (1) PERCENT orderid, orderdate, custid, empid

FROM Sales.Orders

ORDER BY orderdate DESC;

GO

-- Mund te perdoret nje variable, si input

DECLARE @n AS BIGINT = 5;

SELECT TOP (@n) orderid, orderdate, custid, empid

FROM Sales.Orders

ORDER BY orderdate DESC;

GO

-- Pa ORDER BY, renditje arbitrare e rreshtave

SELECT TOP(3) orderid, orderdate, custid, empid

FROM Sales.Orders;

-- Renditje arbitrare eksplicite

SELECT TOP (3) orderid, orderdate, custid, empid

FROM Sales.Orders

ORDER BY (SELECT NULL);

-- Jo-deterministe renditja me ORDER BY duke qene e aplikuar mbi vlera jo unike

SELECT TOP (3) orderid, orderdate, custid, empid

FROM Sales.Orders

ORDER BY orderdate DESC;

-- Kthe ties (te gjitha)

SELECT TOP (3) WITH TIES orderid, orderdate, custid, empid

FROM Sales.Orders

ORDER BY orderdate DESC;

-- Nderprit ties

SELECT TOP (3) orderid, orderdate, custid, empid

FROM Sales.Orders

ORDER BY orderdate DESC, orderid DESC;

Filtering Data with OFFSET-FETCH

-- Skip 50 rows, fetch next 25 rows

SELECT orderid, orderdate, custid, empid

FROM Sales.Orders

ORDER BY orderdate DESC, orderid DESC

OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY;

-- Fetch first 25 rows

SELECT orderid, orderdate, custid, empid

FROM Sales.Orders

ORDER BY orderdate DESC, orderid DESC

OFFSET 0 ROWS FETCH FIRST 25 ROWS ONLY;

-- Skip 50 rows, return all the rest

SELECT orderid, orderdate, custid, empid

FROM Sales.Orders

ORDER BY orderdate DESC, orderid DESC

OFFSET 50 ROWS;

-- ORDER BY i detyruar; kthe 3 rreshta

SELECT orderid, orderdate, custid, empid

FROM Sales.Orders

ORDER BY (SELECT NULL)

OFFSET 0 ROWS FETCH FIRST 3 ROWS ONLY;

GO

-- Perdoret variabel si input

DECLARE @pagesize AS BIGINT = 25, @pagenum AS BIGINT = 3;

SELECT orderid, orderdate, custid, empid

FROM Sales.Orders

ORDER BY orderdate DESC, orderid DESC

OFFSET (@pagenum - 1) * @pagesize ROWS FETCH NEXT @pagesize ROWS ONLY;

GO

Ushtrime

Ndertoni Query qe te shfaqin rezultatet e meposhtme:

1. Te afishohen hiredate nga HR.Employees, dhe nje kolone tjeter me emrin [Status Punonjesi], ku vlera te jete 'Punonjes i vjeter' nese hiredate eshte '20031017'

dhe 'Punonjes i ri' ne rast te kundert.

2. Te afishohen te gjitha te dhenat nga Production.Products ku productname te permbaje shkronjen Y.

3. Te afishohen te gjitha te dhenat nga Production.Products ku productname te perfundoje me shkronjen N.

4. Te listohen te gjitha titleofcourtesy te renditura ne rend zbrites.

5. Te listohen 3 titleofcourtesy qe ndodhen pas 5 rreshtave te pare.