------------Q1---------------
--1A
SELECT  Customers.CustomerID ,
        Customers.CompanyName ,
        Customers.ContactName ,
        Customers.ContactTitle ,
        Customers.Address ,
        Customers.City
FROM    dbo.Customers
WHERE   Customers.City = 'London'
GO 
--1B
SELECT  Customers.CustomerID ,
        Customers.CompanyName ,
        Customers.ContactName ,
        Customers.ContactTitle ,
        Customers.Address ,
        Customers.City
FROM    dbo.Customers
WHERE   Customers.ContactTitle = 'Owner'
GO 

-----------------Q2
CREATE TABLE IncentivePackage
    (
      PackageID INT NOT NULL
                    PRIMARY KEY ,
      PackageName VARCHAR(50) NOT NULL
    )
GO

CREATE TABLE Customer_Package
    (
      CustomerID NCHAR(5) NOT NULL
                          FOREIGN KEY REFERENCES dbo.Customers ( CustomerID ) ,
      PackageID INT NOT NULL
                    FOREIGN KEY REFERENCES dbo.IncentivePackage ( PackageID ) ,
      Type VARCHAR(10) NOT NULL PRIMARY KEY ( CustomerID, PackageID )
    )
GO 

ALTER TABLE dbo.Customer_Package
ADD CONSTRAINT type CHECK (Customer_Package.Type IN  ('VIP','BIS','ECO'))

----------------------Q3
--3A
SELECT TOP 10
        Products.ProductID ,
        Products.ProductName ,
        Categories.CategoryName ,
        Suppliers.CompanyName ,
        Products.UnitPrice
FROM    dbo.Products
        INNER JOIN dbo.Suppliers ON Suppliers.SupplierID = Products.SupplierID
        INNER JOIN dbo.Categories ON Categories.CategoryID = Products.CategoryID
ORDER BY Products.UnitPrice DESC

--3B
SELECT TOP 10
        Products.ProductID ,
        Products.ProductName ,
        Categories.CategoryName ,
        Suppliers.CompanyName ,
        Products.UnitPrice
FROM    dbo.Products
        INNER JOIN dbo.Suppliers ON Suppliers.SupplierID = Products.SupplierID
        INNER JOIN dbo.Categories ON Categories.CategoryID = Products.CategoryID
ORDER BY Products.ProductName ASC ,
        Suppliers.CompanyName ASC 

---------Q4
SELECT  ( a.FirstName + ' ' + a.LastName ) AS [Staff FullName] ,
        ( b.FirstName + ' ' + b.LastName ) AS [Supervisor FullName]
FROM    dbo.Employees AS a
        LEFT JOIN dbo.Employees AS b ON b.EmployeeID = a.ReportsTo


------------Q5
--5A
--INSERT  INTO dbo.CustomerDemographics
INSERT  INTO dbo.CustomerDemographics
        ( CustomerTypeID, CustomerDesc )
VALUES  ( N'1', -- CustomerTypeID - nchar(10)
          'High income'  -- CustomerDesc - ntext
          )
GO
INSERT  INTO dbo.CustomerDemographics
        ( CustomerTypeID, CustomerDesc )
VALUES  ( N'2', -- CustomerTypeID - nchar(10)
          'Likes beer'  -- CustomerDesc - ntext
          )
GO
INSERT  INTO dbo.CustomerDemographics
        ( CustomerTypeID, CustomerDesc )
VALUES  ( N'3', -- CustomerTypeID - nchar(10)
          'Likes women'  -- CustomerDesc - ntext
          )
GO
INSERT  INTO dbo.CustomerDemographics
        ( CustomerTypeID, CustomerDesc )
VALUES  ( N'4', -- CustomerTypeID - nchar(10)
          'Only do big bussines'  -- CustomerDesc - ntext
          )
GO

--INSERT INTO dbo.CustomerCustomerDemo
INSERT  INTO dbo.CustomerCustomerDemo
        ( CustomerID, CustomerTypeID )
VALUES  ( N'ALFKI', -- CustomerID - nchar(5)
          N'1'  -- CustomerTypeID - nchar(10)
          )
GO 
INSERT  INTO dbo.CustomerCustomerDemo
        ( CustomerID, CustomerTypeID )
VALUES  ( N'A', -- CustomerID - nchar(5)
          N''  -- CustomerTypeID - nchar(10)
          )
GO 
INSERT  INTO dbo.CustomerCustomerDemo
        ( CustomerID, CustomerTypeID )
VALUES  ( N'', -- CustomerID - nchar(5)
          N''  -- CustomerTypeID - nchar(10)
          )
GO 
INSERT  INTO dbo.CustomerCustomerDemo
        ( CustomerID, CustomerTypeID )
VALUES  ( N'', -- CustomerID - nchar(5)
          N''  -- CustomerTypeID - nchar(10)
          )
GO 

-----------------------------Q6
--6A
SELECT  MAX(Products.UnitPrice) AS MaxPrice
FROM    dbo.Products
GO 
--6B
SELECT  MIN(Products.UnitPrice) AS MinPrice
FROM    dbo.Products
GO


-----------------------------Q9
--9A
CREATE PROCEDURE SP_QUERY_ORDERDETAIL
    (
      @CustomerID NCHAR(5) ,
      @OrderDate DATETIME
    )
AS
    BEGIN
        SELECT  Customers.CustomerID ,
                Customers.CompanyName ,
                Orders.OrderDate ,
                Orders.OrderID ,
                Products.ProductName ,
                [Order Details].Quantity ,
                [Order Details].UnitPrice
        FROM    dbo.Customers
                INNER JOIN dbo.Orders ON Orders.CustomerID = Customers.CustomerID
                INNER JOIN dbo.[Order Details] ON [Order Details].OrderID = Orders.OrderID
                INNER JOIN dbo.Products ON Products.ProductID = [Order Details].ProductID
        WHERE   Customers.CustomerID = @CustomerID
                AND Orders.OrderDate = @OrderDate
    END
GO 

EXECUTE dbo.SP_QUERY_ORDERDETAIL @CustomerID = N'VINET',
    @OrderDate = '19960704' 



---9B
CREATE PROCEDURE SP_QUERY_TRANSACTION_BY_CUSTOMERID ( @CustomerID NCHAR(5) )
AS
    BEGIN
        SELECT  Customers.CustomerID ,
                Customers.CompanyName ,
                COUNT(Orders.OrderID) AS [Number Of Transactions]
        FROM    dbo.Customers
                INNER JOIN dbo.Orders ON Orders.CustomerID = Customers.CustomerID
        WHERE   Customers.CustomerID = @CustomerID
        GROUP BY Customers.CustomerID ,
                Customers.CompanyName
    END 
GO 

EXECUTE SP_QUERY_TRANSACTION_BY_CUSTOMERID @CustomerID = N'VINET'


----Test
    SELECT  *
    FROM    dbo.CustomerDemographics

    SELECT  CustomerCustomerDemo.CustomerID ,
            Customers.CompanyName ,
            CustomerDemographics.CustomerDesc
    FROM    dbo.CustomerDemographics
            INNER JOIN dbo.CustomerCustomerDemo ON CustomerCustomerDemo.CustomerTypeID = CustomerDemographics.CustomerTypeID
            INNER JOIN dbo.Customers ON Customers.CustomerID = CustomerCustomerDemo.CustomerID
GO 

SELECT  Customers.CustomerID ,
        Customers.CompanyName ,
        Orders.OrderDate ,
        Orders.OrderID ,
        Products.ProductName ,
        [Order Details].Quantity ,
        [Order Details].UnitPrice
FROM    dbo.Customers
        INNER JOIN dbo.Orders ON Orders.CustomerID = Customers.CustomerID
        INNER JOIN dbo.[Order Details] ON [Order Details].OrderID = Orders.OrderID
        INNER JOIN dbo.Products ON Products.ProductID = [Order Details].ProductID
WHERE   Customers.CustomerID = 'VINET'
        AND Orders.OrderDate = '19960704'