Kodi:
Databaza Lexione (SQL)
( Intensiv lexion Shqip [Albania] )
(Ing.ViRuSi_ZeRo )
1-Select * From Customers
--Nxjer Te gjith informacionin qe gjendet ne tabelen Customers. // *
2-Select FirstName,LastName From Customers
--Shfaqet Vetem FirstName & Last Name Ne kollona te Vecanta.
3-Select FirstName as First Name,LastName as Last Name From Customers
--Shfaq FirstName & LastName duke ndryshur emrat e kollonave me ate qe ndoshet Brenda thonjzave // AS
4-Select FirstName + + LastName as Customer Name From Customers
--Shfaq FirstName & Last Name ne nji kolon te vetme te quajtur Customer Name // +
5-Select * From Products Where ProductID = 3;
-- Shgfaq te gjith informacionin Me kusht qe productID te jet = 3 // =
6-Select * From Product Where ProductPrice > 30
-- Shgfaq te gjith informacionin Me kusht qe ProductPrice me i madh se 30 // >
7--Select * From Product Where ProductPrice < 30
-- Shgfaq te gjith informacionin Me kusht qe ProductPrice me i Vogel se 30 // <
8- Select * From Product Where ProductPrice >= 29.95
-- Shgfaq te gjith informacionin Me kusht qe ProductPrice me i Madh & I barabart se 29.95 // >=
9-Select * From Product Where ProductTypeId <> 2
--Shfaq te gjith informacionin me kusht qe productType ID I ndryshem nga 2 // <>
Select * From Products Where ProductName <> Inspired By Widgats
Shfaq te gjith informacionin me kusht qe ProductName I ndryshem nga Fjala Brenda Thonjzave
10- Select * From Products Where ProductName = Inspired By Widgats
--Shfaq te gjith iformacionin kur ProductName eshte I barabart me Fjalen Kyce ne thonjza
11-Select * From Orders Where OrderDateTime > 1/10/2006 AND OrderDateTime < 1/13/2006
--Shfaq te gjith informacionin nga Nga Kushti I dative; // AND
12- Select * From Orders Where OrderDateTime < 1/9/2006 OR OrderDateTime > 1/12/2006
--Shfaq Info Me kusht qe data te jet me e vogel se kushti par dhe me e madhe se I dyti. // OR
13- Select SUM(Tax) as Taxes Colleced From Orders
--Shfaq Ne nji kolon te quajtur Taxes Collected Shumen e tabeles Tax // SUM
14-Select Count(*) as Total Orders From Orders
--Numero te gjith tabelen Orders dhe shfaqe ne kolon te quajtur Total Orders // Count
15-Select ProductID,ProductPrice as Regular Price,ProductPrice -5 as Sale Price From Products
-Shfaq ProductID ,ProductPrice duke Duke I nderuar emrin ne Regular Price,Dhe kolonen ProductPrice duke I ndryshuar emrin ne Sale Price dhe duke I zbritur 5 (-5) // -
16- Select ProductID,ProductPrice as Regular Price,ProductPrice * .9 as Sale Price From Products
-Shfaq ProductID ,ProductPrice duke Duke I nderuar emrin ne Regular Price,Dhe kolonen ProductPrice duke I ndryshuar emrin ne Sale Price dhe .9 // .9
17-Select Min (ProductPrice) From Products
--Shfaq Minimumin nga Kolona ProductPrice e tabeles Products // Min
18- Select Max (ProductPrice) From Products
--Shfaq Maksimumin nga Kolona ProductPrice e tabeles Products // Max
19-Select Avg (ProductPrice) From Products
--Mesatarja e ProductPrice nga tabela product // Avg
20-Select OrderID,Month(OrderDateTime), Day(OrderDateTime) From Orders
--Shfaq OrderID, Muajn & Daten NGa tabela Orders // Month ,Day
21-Select GetDate()
--Mer daten e sistemit
-----Shenim
-- yy = Viti
-- mm = Muaji
-- dd = Dita
-- hh = Ora
-- mi = Minutat
-- weekday = 9Sundday through Saturday)
22-Select datename (mm,OrderDateTime) From Orders
--Shfaq Muajin Me emer psh (Janar) // datename
23- Select datepart (mm,OrderDateTime) From Orders
--Shfaq Muajn me Nr psh (1) // datepart
24- Select datediff (dd,GetDate(), OrderDateTime) From Orders
--Sa dit kan kaluar nga data qe esht ber porosia deri sot.. // datepart
25- Select dateadd (dd, 5, OrderDateTime) From Orders
--Shton 5 dit ne daten OrderDateTime psh nese data esht 1/3/2009 behet 1/8/2009 // dateadd
26-Select substring (ProductDescrition, 3, 5) From Products
--Shfaq Nga shkronja 3 Duke e perfshir shkronjen e trete deri ne shkronjen e 5 Duke mos e perfshir te pesten // substring
27-Select right (ProductName,5) From Products
-Shfaq 5 Karaktere (Duke perfshir edhe hapsirat si karaktere) Nga e djathta ne te majt // right
28- Select left (productName ,5) From Products
-Shfaq 5 Karaktere (Duke perfshir edhe hapsirat si karaktere) Nga e majta ne te djatht // left
29-Select upper (ProductName) From Products --Uper Case
--Shfaq te gjitha me shkronja te medhaja // upper
30-Select lower (ProductName) From Products --Louer Case
--Shfaq te gjith me shkronja te vogla // lower
31-Select reverse (ProductName) From Products
--Shfaq Shrimin Mbrapsh ( Lexohet me shkronja nga e djathta ne te majte) // reverse
32-Select * From Customers Where FirstName Like J%
--Shfaq te gjith informacionin qe gjendet ne kolonen FirstName dhe fillon emir me J // Like J%
33- Select * From Customers Where State province IN (IL,KS,PA)
--Shfaq te gjith informacionin ne tabelen customer kur plotesohet kushti qe State province =IL=KS=PA
// IN (IL,KS,PA)
34-Select * From Order BY LasrName ASC
--Rendit Sipas alfabetit nga a // Order BY
Select * From Order BY LasrName DESC
35-Select * From Customers Order By LastName,FirstName
--Rendit informacionin sipas mbiemrit dhe emrit
36-Select CustomerID,Count(*),SUM(shipping) From Orders Group By CustomerID
--Shfaq CustomerID Numero Sa jan mbledhur,Shuma e Shipping,Grupoji sipas CustomerID // Group By
37-Select CustomerID,SUM(Shipping) From Orders
Group By CustomerID
Having Count(*) >= 3
--Grupojo sipas CustomerID Gjej Shumen totale per cdo iD (shipping) Per kushtin nese kan 3 ose me shum porosi // Having
38-Select Products.ProductID,Product.ProductName,ProductType.ProductTypeName
From Products INNER JOIN ProductType ON Products.ProductTypeID = ProductTypes.ProductTypeID
--Lidhja e dy tabelave
--Selset (shpjegim)
--Nga tabela product shfaq kolonen ProductID,nga tabela Product shfaq tabelen ProducName ,Nga tabela ProductType shfaq kolonen ProductID // INNER JOIN / ON
39- Select Products.ProductID,Product.ProductName,ProductType.ProductTypeName
From Products INNER JOIN ProductType ON Products.ProductTypeID = ProductTypes.ProductTypeID
Where Product.ProductPrice > 29.95
--Behet lidhja e dy tabela dhe shfaqet kush ploteson kushtin
40-Select Customers.LastName,Order.OrderID,Products.ProductName
From Customers INNER JOIN Orders ON Customers.CustomerID = Order.CustomerID
INNER JOIN OrderItems ON OrderID = OrderItems.OrderID
INNER JOIN Product ON Product.ProductID = OrderItems.ProductID
Order BY Customers.LastName,Orders.OrderID
--Ketu behet lidhja e 4 tabelave dhe renditja sipas lastName,Orders,OrderID
41-Select * From Orders Where OrderID IN
(Select DISTINCT OrderID From OrderItems Where ProductID = 3)
--Selsect dhe nenSelect. I pari ekzekutohet nenselecti pastaj selecti pra:
--Shfaq OrderID nga tabela OrderItems Kur ProductID = 3 dhe vazhdon selsecti I par shfaq te gjith
-- informacionin nga tabela Orders Kur OrderID = me nenselectin
// (Select DISTINCT OrderID From OrderItems Where ProductID = 3)
42-Insert Customers (FirstName,LastName)
Values (Endri,Dyrma)
--Shton ne tabelen Customer resht te ri ,ne kolonen FirstName Vleren Endri // Insert
43-Update Customer SET City =Lushnje,PostalCode=75088 Where LastName=Dyrma
--Nryshon te dhenat kur plotesohet kushti mbiemri = dyrma // Update
44-Delete Customers Where LastName = Dyrma
--Fshije kur plotesohet kushti me mbiemrin dyrma // Delete
Krijoni Kontakt