Animal Class


public class Animal {


int age;

String gender;

int weightInLbs;

public Animal(int age, String gender, int weightInLbs) {

this.age = age;

this.gender = gender;

this.weightInLbs = weightInLbs;


}

public void eat() {

System.out.println("Eating...");

}

public void sleep() {

System.out.println("Sleeping...");

}

}


Zoo Class



public class Zoo {


public static void main(String[] args) {

Animal animal1 = new Animal(4, "F", 5);

animal1.eat();

Bird bird1 = new Bird();

Fish fish1 = new Fish("me luspa", 4);

}

}


Bird Class



public class Bird extends Animal {


public Bird(int age, String gender, int weightInLbs) {

super(age, gender, weightInLbs);


}


public void fly() {

System.out.println("Flying...");

}

}



Fish Class


public class Fish {

String tipi;

int mosha;

public Fish(String tipi, int mosha) {

this.tipi = tipi;

this.mosha = mosha;

}

public void swim() {

System.out.println("Swimming...");

}

}



Methods

A method is a block of code which only runs when it is called.

You can pass data, known as parameters, into a method.

Methods are used to perform certain actions, and they are also known as functions.

Why use methods? To reuse code: define the code once, and use it many times.

Constructors
A constructor in Java is a special method that is used to initialize objects. The constructor is called when an object of a class is created. It can be used to set initial values for object attributes:

Java Class Attributes

In the previous chapter, we used the term "variable" for x in the example (as shown below). It is actually an attribute of the class. Or you could say that class attributes are variables within a class:



by at No comments:
CREATE VIEW Cityinfo AS -- krijimi i nje Pamjeje ku listohen te gjithe konsumatoret dhe furnizuesit ne qytetet perkatese

SELECT City, CompanyName, ContactName, 'Customer' AS Relationship
FROM customers

UNION

SELECT City, CompanyName, ContactName, 'Supplier' AS Relationship
FROM Suppliers ;




DELIMITER $$  -- Procedura per nxjerrjen e nje raporti per konsumatorin (numrin e porosive), (Shpenzimi total i realizuar)
-- Daten porosise se pare te realizuar, Daten e porosise se fundit te realizuar

DROP PROCEDURE IF EXISTS Cus_info $$

CREATE PROCEDURE Cus_info (IN Cus_ID CHAR(5))
BEGIN

DECLARE Nr_Orders INT;
    DECLARE Total_amount FLOAT;
    DECLARE First_date DATE;
    DECLARE Last_date DATE;
   
    SELECT COUNT(OrderID) INTO Nr_orders
    FROM orders
    WHERE CustomerID = Cus_ID;
   
    SELECT SUM(b.UnitPrice * b.Quantity * (1-b.Discount)) INTO Total_amount
    FROM orders AS a
INNER JOIN
order_details AS b ON a.OrderID = b.OrderID
WHERE a.CustomerID = Cus_ID;
   
    SELECT MIN(OrderDate) INTO First_date
    FROM orders
    WHERE CustomerID = Cus_ID;
   
    SELECT MAX(OrderDate) INTO Last_date
    FROM orders
    WHERE CustomerID = Cus_ID;
   
    SELECT CustomerID, CompanyName, Nr_Orders, Total_amount, First_date,
Last_date
FROM Customers
    WHERE CustomerID = Cus_ID;

END $$

DELIMITER ;
 -- -------------------------------------------------------------------------------------------------------------




DELIMITER $$ -- Procedure qe kerkon ne baze te emrit per nr total te porosive te realizuar nga secili punonjes

DROP PROCEDURE IF EXISTS Search_emp $$

CREATE PROCEDURE Search_emp (IN FName VARCHAR(25))
BEGIN
SELECT CONCAT(b.EmployeeID,'-', b.FirstName,' ', b.LastName) AS Employee,
Count(a.OrderID) AS Num_orders
FROM orders AS a
INNER JOIN
employees AS b ON a.EmployeeID = b.EmployeeID
WHERE b.FirstName LIKE CONCAT('%',FName,'%')
GROUP BY b.EmployeeID;

END $$

DELIMITER ;
-- -----------------------------------------------------------------------------------------------------------------



DELIMITER $$ -- procedure qe nxjerr 5 produktet me te shitura ne vitin e dhene

DROP PROCEDURE IF EXISTS Max_Product $$

CREATE PROCEDURE Max_Product (IN Prod_Year YEAR)
BEGIN
SELECT b.ProductID, a.ProductName, SUM(b.Quantity) AS Prod_order
    FROM products AS a
INNER JOIN
order_details AS b ON b.ProductID = a.ProductID
INNER JOIN
orders AS c ON c.OrderID = b.OrderID
WHERE YEAR(c.OrderDate) = Prod_Year
    GROUP BY b.ProductID
    ORDER BY Prod_order DESC
    LIMIT 5;

END $$

DELIMITER ;
-- ------------------------------------------------------------------------------------------------------------------



DELIMITER $$ -- procedure qe nxjerr numrin e porosive te realizuar nga secili punetor ne harkun kohor te dy datave

DROP PROCEDURE IF EXISTS Emp_orders $$

CREATE PROCEDURE Emp_orders (IN Start_date DATE, IN End_date DATE)
BEGIN

    SELECT a.EmployeeID, a.FirstName, a.LastName,
COUNT(b.OrderID) AS Nr_orders
    FROM employees AS a
LEFT OUTER JOIN
orders AS b ON a.EmployeeID = b.EmployeeID
WHERE b.OrderDate BETWEEN Start_date AND End_Date
    GROUP BY a.EmployeeID;

END $$

DELIMITER ;

by at No comments:
DELIMITER $$

DROP FUNCTION IF EXISTS mosha  $$

CREATE FUNCTION mosha( Datelindja DATE) RETURNS INT
READS SQL DATA
BEGIN
DECLARE emp_age INT;
   
    SET emp_age = YEAR(NOW()) - YEAR(Datelindja);
   
    RETURN emp_age ;

END $$

DELIMITER ;

-- ------------------------------------------------------------------------

SELECT employeeID, FirstName, LastName, mosha(Birthdate) AS Emp_age
FROM employees;

-- ------------------------------------------------------------------------



DELIMITER $$

DROP FUNCTION IF EXISTS Cus_NrOrders $$

CREATE FUNCTION Cus_NrOrders ( CusID CHAR(5)) RETURNS INT
READS SQL DATA
BEGIN
DECLARE Cus_orders INT;
   
    SET Cus_orders = (SELECT COUNT(*) FROM orders WHERE CustomerID = CusID);
   
RETURN Cus_Orders;

END $$

DELIMITER ;

-- ------------------------------------------------------------------------

SELECT CustomerID, CompanyName, Cus_NrOrders(CustomerID) AS Total
FROM Customers

-- -------------------------------------------------------------------------



DELIMITER $$
DROP FUNCTION IF EXISTS Cus_Total $$

CREATE FUNCTION Cus_Total ( CusID CHAR(5)) RETURNS DOUBLE
READS SQL DATA
BEGIN
DECLARE Cus_sum DOUBLE;
   
    SET Cus_sum = (SELECT SUM(b.UnitPrice * b.Quantity * (1-b.Discount))
  FROM orders AS a
INNER JOIN
   order_details AS b ON a.OrderID = b.OrderID
  WHERE a.CustomerID = CusID);
   
RETURN Cus_sum;
END $$
DELIMITER ;

-- ------------------------------------------------------------------------

SELECT CustomerID, CompanyName, Cus_Total(CustomerID) AS Total
FROM Customers

-- ------------------------------------------------------------------------




DELIMITER $$
DROP FUNCTION IF EXISTS VIP_Check $$

CREATE FUNCTION VIP_CHECK (CusID CHAR(5)) RETURNS VARCHAR(15)
READS SQL DATA
BEGIN
DECLARE Third_Cus INT;
    DECLARE Cus_NumOrder INT;
    DECLARE Cus_Result VARCHAR(15);
   
    SET Third_Cus = ( SELECT COUNT(*) AS Num_orders
FROM orders
GROUP BY CustomerID
ORDER BY Num_Orders DESC
LIMIT 2, 1
);
SET Cus_numOrder = (SELECT COUNT(*) FROM orders WHERE CustomerID = CusID);

    IF Cus_numOrder >= Third_Cus THEN
SET Cus_Result = 'VIP';
ELSE
SET Cus_Result = 'NON-VIP';
    END IF;
   
    RETURN Cus_Result ;
END $$
DELIMITER ;

-- -----------------------------------------------------------------------------

SELECT CustomerID, CompanyName, Cus_NrOrders(CustomerID) AS Num_Orders,
 VIP_CHECK(CustomerID) AS Cus_Status
FROM Customers
ORDER BY Num_Orders DESC ;

-- -----------------------------------------------------------------------------




DELIMITER $$

DROP FUNCTION IF EXISTS Emp_NumOrders $$

CREATE FUNCTION Emp_NumOrders (EmpID INT) RETURNS DOUBLE
READS SQL DATA
BEGIN
DECLARE Emp_Total DOUBLE;

    SET Emp_Total = (SELECT SUM(b.UnitPrice * b.Quantity * (1-b.Discount))
FROM orders AS a
INNER JOIN
  order_details AS b ON a.OrderID = b.OrderID
WHERE a.EmployeeID = EmpID
                     );
   
RETURN Emp_total;
END $$

DELIMITER ;

-- ------------------------------------------------------------------------

SELECT EmployeeID, FirstName, LastName,
Emp_NumOrders(EmployeeID) AS Total_sum
FROM employees

-- --------------------------------------------------------------------------




DELIMITER $$
DROP FUNCTION IF EXISTS Emp_Order_status $$

CREATE FUNCTION  Emp_Order_status (EmpID INT) RETURNS VARCHAR(20)
READS SQL DATA
BEGIN
DECLARE Emp_status VARCHAR(20);
    DECLARE Emp_avg DECIMAL(6,2);
    DECLARE Emp_Num_orders INT;
   
SET Emp_avg = ( SELECT AVG(t1.Num_orders) AS Emp_AvgOrders
FROM ( SELECT EmployeeID, Count(OrderID) AS Num_orders
FROM orders
GROUP BY EmployeeID
) AS t1
   );
SET Emp_Num_orders = ( SELECT COUNT(*)
FROM orders
                            WHERE employeeID = EmpID
);
                       
IF Emp_Num_orders >= Emp_avg  THEN
SET Emp_status = 'Above Average';
ELSE
SET Emp_status = 'Below Average';
    END IF;
   
    Return Emp_status;
END $$
DELIMITER ;

-- ----------------------------------------------------------------------------------

SELECT EmployeeID, FirstName, LastName,
Emp_Order_status(EmployeeID) AS Emp_status
FROM employees

-- -----------------------------------------------------------------------------------




DELIMITER $$

DROP FUNCTION IF EXISTS Cus_Year_OrdersNUM $$

CREATE FUNCTION Cus_Year_OrdersNUM (CusID CHAR(5), Orders_Year INT) RETURNS INT
READS SQL DATA
BEGIN
DECLARE Year_Orders INT;
   
SET Year_Orders = ( SELECT COUNT(OrderID)
FROM orders
                        WHERE CustomerID = CusID AND YEAR(OrderDate) = Orders_Year 
    );
   
    RETURN Year_Orders;
END $$
DELIMITER ;

-- ----------------------------------------------------------------------------------

SELECT CustomerID, CompanyName, Cus_Year_OrdersNUM(CustomerID, 2008) AS Orders2008
FROM Customers

-- ----------------------------------------------------------------------------------
by at No comments:

DB Tutorials


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';
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 si dhe totalin e paguar.
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'
          OR
      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%'
                                  );


by at No comments:
Subscribe to: Posts (Atom)