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