dbtutorials: January 2020

n Shfaq tabelen e plote “employees”.

SELECT *

FROM employees;

n Shfaq vetem 4 fushat e zgjedhura nga tabela “employees”.

SELECT FirstName, LastName, Title, City

FROM employees;

n Shfaq 4 fushat e zgjedhura te riemeruara nga tabela “employees”.

SELECT FirstName AS Emri, LastName AS Mbiemri, Title AS 'Titulli Personal',
City AS Qyteti

FROM employees;

n Shfaq puntoret te cileve u fillon emri me shkronjen “A”.

SELECT FirstName AS 'Emri', LastName AS 'Mbiemri', Title AS 'Titulli', City AS 'Qyteti'

FROM employees

WHERE FirstName LIKE 'A%';

n Shfaq puntoret te cileve u fillon shkronjen e pare te emrit e kane “A” dhe shkronjen e trete e kane “d”.

SELECT FirstName AS 'Emri', LastName AS 'Mbiemri', Title AS 'Titulli', City AS 'Qyteti'

FROM employees

WHERE FirstName LIKE 'A_d%';

n Shfaq puntoret te cileve u fillon emri me shkronjen “A” dhe mbiemri me shkronjen “D”

SELECT FirstName AS 'Emri', LastName AS 'Mbiemri', Title AS 'Titulli', City AS 'Qyteti'

FROM employees

WHERE FirstName LIKE 'A%' AND LastName LIKE 'D%';

n Shfaq puntoret te cileve u fillon emri me shkronjen “A” ose mbiemri me shkronjen “D”

SELECT FirstName AS 'Emri', LastName AS 'Mbiemri', Title AS 'Titulli', City AS 'Qyteti'

FROM employees

WHERE FirstName LIKE 'A%' OR LastName LIKE 'D%';

n Shfaq tabelen e plote “products”.

SELECT *

FROM products;

n Shfaq 3 fushat e zgjedhura te riemeruara nga tabela “products”.

SELECT ProductID AS 'ID', ProductName AS 'Emri Produktit', UnitPrice AS 'Cmimi per Njesi'

FROM products;

n Shfaq te gjitha productet te cilat kane Cmimin per Njesi (UnitPrice) me te madhe se 10.

SELECT ProductID AS 'ID', ProductName AS 'Emri Produktit', UnitPrice AS 'Cmimi per Njesi'

FROM products

WHERE UnitPrice > 10;

n Shfaq te gjitha productet te cilat kane Cmimin per Njesi (UnitPrice) me te vogel ose baraz se 20.

SELECT ProductID AS 'ID', ProductName AS 'Emri Produktit', UnitPrice AS 'Cmimi per Njesi'

FROM products

WHERE UnitPrice <= 20;

n Shfaq te gjitha productet te cilat kane Cmimin per Njesi (UnitPrice) me te madhe se 10 dhe me te vogel ose baraz se 20.

SELECT ProductID AS 'ID', ProductName AS 'Emri Produktit', UnitPrice AS 'Cmimi per Njesi'

FROM products

WHERE UnitPrice >= 10 AND UnitPrice <= 20;

n Shfaq te gjitha productet te cilat kane Cmimin per Njesi (UnitPrice) midis vlerave 10.01 dhe 19.99

SELECT ProductID AS 'ID', ProductName AS 'Emri Produktit', UnitPrice AS 'Cmimi per Njesi'

FROM products

WHERE UnitPrice BETWEEN 10.01 AND 19.99

n Shfaq tabelen e plote “orders”.

SELECT *

FROM orders;

n Shfaq 3 fushat e zgjedhura te riemeruara nga tabela “orders”.

SELECT CustomerID AS 'ID Konsumatori', OrderID AS 'ID Porosije',
OrderDate AS 'Data Porosise'

FROM orders;

n Shfaq te gjitha porosite e realizuar nga konsumatoret me CustomerID “ALFKI”, “BOLID” dhe “PARIS” nga tabela “orders”.

SELECT CustomerID AS 'ID Konsumatori', OrderID AS 'ID Porosije', OrderDate AS 'Data Porosise'

FROM orders

WHERE CustomerID = 'ALFKI' or CustomerID = 'BOLID' or CustomerID = 'PARIS';

n Shfaq te gjitha porosite e realizuar nga konsumatoret me CustomerID “ALFKI”, “BOLID” dhe “PARIS” nga tabela “orders” (e njejte me shprehjen e mesiperme).

SELECT CustomerID AS 'ID Konsumatori', OrderID AS 'ID Porosije', OrderDate AS 'Data Porosise'

FROM orders

WHERE CustomerID IN ( 'ALFKI' , 'BOLID' , 'PARIS' );

n Shfaq te gjitha porosite e realizuar pas dates 01-07-2007.

SELECT CustomerID AS 'ID Konsumatori', OrderID AS 'ID Porosije', OrderDate AS 'Data Porosise'

FROM orders

WHERE OrderDate > '2007-07-01';

n Shfaq te gjitha porosite e realizuar midis datatave 01-07-2007 dhe
15-07-2007.

SELECT CustomerID AS 'ID Konsumatori', OrderID AS 'ID Porosije', OrderDate AS 'Data Porosise'

FROM orders

WHERE OrderDate BETWEEN '2007-07-01' AND '2007-07-15';

n Shfaq tabelen e plote “orders”.

SELECT *

FROM orders;

n Shfaq numrin e rrjeshtave nga tabela “orders”.

SELECT COUNT(*)

FROM orders;

n Shfaq numrin e rrjeshtave nga tabela “orders” duke vendosur nje riemertim per fushen e re.

SELECT COUNT(*) AS 'Nr Total i Porosive'

FROM orders;

n Shfaq numrin e porosive te realizuar nga konsumatori me CustomerID “BOLID”

SELECT CustomerID, COUNT(*) AS 'Nr i blerjeve te regjistruara per klientin'

FROM orders

WHERE CustomerID = 'BOLID';

n Shprehja e meposhtme eshte gabim nese kerkohet te nxirret numri i porosive te realizuar nga konsumatoret me CustomerID “BOLID” dhe “ALFKI”.

SELECT CustomerID, COUNT(*) AS 'Nr i blerjeve te regjistruara per klientin'

FROM orders

WHERE CustomerID = 'BOLID' OR CustomerID = 'ALFKI'; --Gabim!

n Shfaq numrin e porosive te realizuara nga cdo konsumator.

SELECT CustomerID, COUNT(*) AS 'Nr i blerjeve te regjistruara per klientin'

FROM orders

GROUP BY CustomerID;

n Shfaq numrin e porosive te realizuara nga konsumatoret me CustomerID “BOLID” dhe “ALFKI”.

SELECT CustomerID, COUNT(*) AS 'Nr i blerjeve te regjistruara per klientin'

FROM orders

WHERE CustomerID = 'BOLID' OR CustomerID = 'ALFKI'

GROUP BY CustomerID;

n Shfaq numrin e porosive te realizuara nga konsumatoret me CustomerID “BOLID” , “ALFKI” dhe “FRANK”.

SELECT CustomerID, COUNT(*) AS Nr_Blerjeve

FROM orders

WHERE CustomerID IN ('BOLID','ALFKI','FRANK')

GROUP BY CustomerID;

n Shfaq numrin e porosive te realizuara nga konsumatoret Nr_Blerjeve
nga 5 tek 10.

SELECT CustomerID, COUNT(*) AS Nr_Blerjeve

FROM orders

GROUP BY CustomerID

HAVING Nr_Blerjeve BETWEEN 5 AND 10;

n Shfaq numrin e porosive te realizuara nga konsumatoret Nr_Blerjeve
nga 5 tek 10.

SELECT CustomerID, COUNT(*) AS Nr_Blerjeve

FROM orders

GROUP BY CustomerID

HAVING COUNT(*) BETWEEN 5 AND 10;

n Krijimi i nje fushe te re (Taksa) e cila del si rezultat i shumzimit te fushes UnitPrice me 0.2.

SELECT ProductID AS ID, ProductName AS Produktit, UnitPrice AS Cmimi, UnitPrice * 0.2 AS Taksa

FROM products

n Shfaqja e te gjitha produkteve ku rezultati i fushes “Taksa” eshte me e madhe se 3.

SELECT ProductID AS ID, ProductName AS Produktit, UnitPrice AS Cmimi, UnitPrice * 0.2 AS Taksa

FROM products

HAVING Taksa > 3;

n Shprehja e meposhtme eshte gabim pasi ne kushtin WHERE nuk mund te vendoset nje fushe te krijuar ne pjesen SELECT sic eshte edhe fusha “Taksa”.

SELECT ProductID AS ID, ProductName AS Produktit, UnitPrice AS Cmimi, UnitPrice * 0.2 AS Taksa

FROM products

WHERE Taksa > 3; --Gabim!

n Shfaq numrin e porosive te realizuara nga cdo konsumator dhe duke realizuar nje ri-renditje ne tabele nga Nr_Blerjeve me i vogel deri ne Nr_Blerjeve me i madhe

SELECT CustomerID, COUNT(*) AS Nr_Blerjeve

FROM orders

GROUP BY CustomerID

Order by Count(*) ASC; --Rendit vlerat nga me e vogla ne me te madhen ose nga A-Z

n Shfaq numrin e porosive te realizuara nga cdo konsumator dhe duke realizuar nje ri-renditje ne tabele nga Nr_Blerjeve me i madhe deri ne Nr_Blerjeve me i vogel

SELECT CustomerID, COUNT(*) AS Nr_Blerjeve

FROM orders

GROUP BY CustomerID

Order by Nr_Blerjeve DESC; --Rendit vlerat nga me e madhja tek me e vogla ose nga Z-A

n Shfaq 10 rrjeshtat e pare ose 10 konsumatoret me Nr_Blerjeve me te madhe ne tabelen “orders”

SELECT CustomerID, COUNT(*) AS Nr_Blerjeve

FROM orders

GROUP BY CustomerID

Order by Nr_Blerjeve DESC

LIMIT 10; -- Nxjerr 10 rrjeshtat e pare te rezultatit

n Shfaq rrjeshtat nga 11 ne 15

SELECT CustomerID, COUNT(*) AS Nr_Blerjeve

FROM orders

GROUP BY CustomerID

Order by Nr_Blerjeve DESC

LIMIT 10, 5 -- Heq 10 rreshtat e pare te rezultatit dhe nxjerr 5 rrjeshtat e rradhes.

n Shfaq tabelen e plote “order_details”.

SELECT *

FROM order_details;

n Krijon nje fushe te re qe nxjerr vleren totale te cdo rrjeshti nga tabela “order_details” qe del si rezultat i shumzimit te 3 fushave te tabeles.

SELECT OrderID, ProductID, (UnitPrice * Quantity * (1-Discount)) AS Total

FROM order_details ;

n Nxjerr totalin e cdo porosije nga tabela “order_details”.

SELECT OrderID, SUM(UnitPrice * Quantity * (1-Discount)) AS Total

FROM order_details

GROUP BY OrderID

ORDER BY Total

n Nxjerr daten e porosive nga tabela “orders” dhe pastaj kesaj date i nxirret viti, muaji, dita. Porosite filtrohen ne menyre qe te dalin vetem porosite e vitit 2007.

SELECT OrderDate, YEAR(OrderDate), MONTH(OrderDate), DAY(OrderDate)

FROM orders

WHERE YEAR(OrderDate) = 2007;

n Nxjerr datat e porosive per vitin 2007 si dhe nje date tjeter qe del si rezultat i dates se porosise + 3 Jave.

SELECT OrderDate, DATE_ADD( OrderDate , INTERVAL 3 WEEK )

FROM orders

WHERE YEAR(OrderDate) = 2007;

n Nxjerr datat e porosive per vitin 2007 diferencen (DATEDIFF) midis dates se fundit te muajit per daten e porosise LAST_DAY( OrderDate ) me daten e porosise.

SELECT OrderDate, DATEDIFF( LAST_DAY( OrderDate ) , OrderDate )

FROM orders

WHERE YEAR(OrderDate) = 2007;

n Krijon nje fushe te re qe del si rezultat i bashkimit te fushave dhe elementeve teks te vendosura ne shprehjen.

SELECT CONCAT( CompanyName, ' - ' , ContactName, ', Phone: ', Phone) AS Contact_Info

FROM customers;

n Krijon nje fushe te re qe del si rezultat i bashkimit te fushave dhe elementeve tekts te vendosura ne shprehjen.

SELECT ContactName,

CONCAT( -- Bashkon copezat e tekstit te me poshtme

SUBSTRING( ContactName, 1,1) , -- Nxjerr vetem shkronjen e pare te emrit

'. ' ,

SUBSTRING_INDEX( ContactName, ' ' , -1 ) -- Nxjerr tekstin qe ndodhet pas
-- hapesires se pare boshe

) AS cname

FROM customers

INNER JOIN

n Nxjerr informacion nga 2 tabela (customers dhe orders) per konsumatorin dhe porosite e realizuara prej tij midis datave ‘2007-07-01' dhe '2007-07-15'.

SELECT a.City, a.CompanyName, b.Orderid, b.OrderDate

FROM customers AS a

INNER JOIN

orders AS b

ON b.CustomerID = a.CustomeriID

WHERE b.OrderDate BETWEEN '2007-07-01' AND '2007-07-15';

n Nxjerr informacion nga 3 tabela (orders, order_details dhe products) per konsumatorin me CustomerID “ALFKI” dhe porosite e realizuara prej tij per cdo produkt me emrin, cmimin dhe sasine e blere.

SELECT a.CustomerID, a.OrderID, a.OrderDate,

c.ProductName,

b.UnitPrice, b.Quantity

FROM orders AS a

INNER JOIN

order_details AS b

ON b.OrderID = a.OrderID

INNER JOIN

products AS c

ON b.ProductID = c.ProductID

WHERE a.CustomerID = 'ALFKI';

SELECT a.CustomerID, a.OrderID, a.OrderDate,

c.ProductName,

b.UnitPrice, b.Quantity, b.Discount, b.UnitPrice * b.Quantity * (1-b.Discount) AS Total

FROM orders AS a

INNER JOIN

order_details AS b

ON b.OrderID = a.OrderID

INNER JOIN

products AS c

ON b.ProductID = c.ProductID

WHERE a.CustomerID = 'ALFKI';

n Nxjerr Totalin e shpenzuar nga konsumatori me CustomerID “ALFKI” si dhe numrin e produkteve te ndryshme per cdo porosi te realizuar.

SELECT a.CustomerID, a.OrderID,

SUM( b.UnitPrice * b.Quantity ) as Total,

COUNT(*) AS num_products

FROM orders AS a

INNER JOIN

order_details AS b

ON b.OrderID = a.OrderID

INNER JOIN

products AS c

ON b.ProductID = c.ProductID

WHERE a.CustomerID = 'ALFKI'

GROUP BY a.OrderID

n Nxjerr informacion nga 2 tabela (orders dhe order_details) duke nxjerrur Totalin e shpenzuar nga cdo konsumator te renditura nga ai qe ka shpenzuar me shume ne ate qe ka shpenzuar me pake.

SELECT a.CustomerID, SUM(b.UnitPrice * b.Quantity * (1-b.Discount)) AS Total

FROM orders AS a

INNER JOIN

order_details AS b

ON b.OrderID = a.OrderID

GROUP BY a.CustomerID

ORDER BY Total DESC;

OUTER JOIN

n Nxjerr te gjitha porosite e realizuara nga konsumatoret “ALFKI”, “BOLID” dhe “PARIS”. Vini re se konsumatori “PARIS” pavarsisht se nuk ka kryer porosi do te shfaqet ne tabele sepse ai ndodhet ne tabelen e majte gjate lidhjes se dy tabelave.

SELECT a.CustomerID, a.CompanyName, b.OrderID, b.OrderDate

FROM customers AS a

LEFT OUTER JOIN

orders AS b

ON b.CustomerID = a.CustomerID

WHERE a.CustomerID IN ( 'ALFKI' , 'BOLID' , 'PARIS' );

n Vini re se konsumatori “PARIS” pavarsisht nuk shfaqet ne tabele pasi tashme lidhja eshte realizuar me RIGHT OUTER JOIN dhe ai ndodhet ne tabelen e majte.

SELECT a.CustomerID, a.CompanyName, b.OrderID, b.OrderDate

FROM customers AS a

RIGHT OUTER JOIN

orders AS b

ON b.CustomerID = a.CustomerID

WHERE a.CustomerID IN ( 'ALFKI' , 'BOLID' , 'PARIS' );

n Vini re se konsumatori “PARIS” do te shfaqet perseri pasi tani me lidhjen RIGHT OUTER JOIN ai ndodhet ne tabelen e djathte.

SELECT a.CustomerID, a.CompanyName, b.OrderID, b.OrderDate

FROM orders AS b

RIGHT OUTER JOIN

customers AS a

ON b.CustomerID = a.CustomerID

WHERE a.CustomerID IN ( 'ALFKI' , 'BOLID' , 'PARIS' );

Nxjerr te gjitha porosite e realizuara nga konsumatoret “ALFKI”, “BOLID” dhe “PARIS” te realizuara nga data '2007-01-01' deri ne daten '2007-12-31'. Vini re se konsumatori “PARIS” pavarsisht se nuk ka kryer porosi do te shfaqet ne tabele sepse ai ndodhet ne tabelen e majte gjate lidhjes se dy tabelave dhe gjithashtu ne kushtin WHERE pranohet dhe b.OrderDate IS NULL.

SELECT a.CustomerID, a.CompanyName, b.OrderID, b.OrderDate

FROM customers AS a

LEFT OUTER JOIN

orders AS b

ON b.CustomerID = a.CustomerID

WHERE a.CustomerID IN ( 'ALFKI' , 'BOLID' , 'PARIS' )

AND

(

b.OrderDate BETWEEN '2007-01-01' AND '2007-12-31'

b.OrderDate IS NULL

);

n Nxjerr punetoret dhe shefat e tyre.

SELECT a.EmployeeID, a.FirstName, a.LastName,

b.EmployeeID, b.FirstName, b.LastName

FROM employees AS a

LEFT OUTER JOIN

employees AS b

ON b.EmployeeID = a.ReportsTo;

n Nxjerr punetoret dhe nen-punesit e tyre.

SELECT a.EmployeeID, a.FirstName, a.LastName,

b.EmployeeID, b.FirstName, b.LastName

FROM employees AS a

LEFT OUTER JOIN

employees AS b

ON b.ReportsTo = a.EmployeeID;

n Nxjerr shefat dhe nen-punesit e tyre.

SELECT a.EmployeeID, a.FirstName, a.LastName,

b.EmployeeID, b.FirstName, b.LastName

FROM employees AS a

INNER JOIN

employees AS b

ON b.ReportsTo = a.EmployeeID;

SUBQUERY ose NenPyetsor

n Shfaq porosite e konsumatoreve te cilet jane nga qyteti i berlinit.

SELECT CustomerID, OrderID, OrderDate

FROM Orders

WHERE CustomerID IN (

SELECT CustomerID

FROM Customers

WHERE City LIKE 'Berlin%'

);

DB Tutorials