Ketu keni disa komanda qe eksekutohen ne sql ose ne access...



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