SQL(Advanced)1;SubQueries.sql

USE SalesDB
GO

-- To check data information about the table 
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
-- Unique table_name
SELECT 
DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS

                 --- 🔆SUBQUERIES  : RESULT TYPES
--🔅SCALAR SUBQUERY   --only one column and one ROW result
SELECT 
AVG(Sales) as [avg sales]
FROM Sales.Orders

--🔅ROW SUBQUERIES  --MULTIPLE ROWS AND SINGLE COLUMN
SELECT 
CustomerID
FROM Sales.Orders

--🔅MULTIPLE SUBQUERIES  --Multiple rows and Multiple Columns
SELECT 
OrderID
,OrderDate
FROM Sales.Orders

           ---/*  SUBQUERIES🔆 */   ---NON_CORRELATED SUB-QRY
                                               --🔅--- SUBQUERIES  : LOCATION/CLAUSE TYPES
    --🔅Subqueries In The FROM Clause

/*Task 1.1 :  Finding the product that has a price
      higher OR LESS than the average price of all produts  */

     --Main Query: Used To Enhance Subquery
SELECT *
FROM (
     --SUBQUERY Used to Query the Main Query
    SELECT 
              ProductID
              ,Price
              ,AVG(Price) OVER ()  [avg price]
    FROM Sales.Products
)S
WHERE Price > [avg price]

/*Task 1.2 :  Ranking customers Based On their Total Amount Of Sales  */

---Main Query Model
SELECT *
    ,RANK() OVER (ORDER BY [Total Sales] DESC) CustomerRank
FROM (
 --- Subquery buildup
    SELECT 
            CustomerID
           ,SUM(Sales) [Total Sales]
    FROM Sales.Orders
GROUP BY CustomerID
           )s

  --🔅Subquery In The SELECT Clause
  ---  /*Task 1.1 :  Showing the PRODUCTt id, PRODUCT name, PRICES and the TOTAL No Of ORDERS in ONE Query RESULT */

  ---Main/Outer Query Housing  SELECT Subquery
  SELECT 
           ProductID
          ,Product
          ,Price
          ,(SELECT COUNT(*)  FROM Sales.Orders) As [Total Orders]   --- SELECT Subquery Model Build-up
  FROM Sales.Products

  --🔅Subquery In The JOIN Clause
      /*Task 1.1 :  Show ALL Customers Details AND
               Find the total orders for each Customer  */

--Main Query Model
SELECT *
FROM Sales.Customers c
LEFT JOIN (   ---JOINS Subquery Model
 SELECT 
      CustomerID
    ,COUNT(*) [Total Orders]
 FROM Sales.Orders 
 GROUP BY CustomerID
) o
ON c.CustomerID = o.CustomerID

-- OR
SELECT   c.* 
   ,o.[Total Orders]
FROM Sales.Customers c
LEFT JOIN ( 
 SELECT 
      CustomerID
    ,COUNT(*) [Total Orders]
 FROM Sales.Orders 
 GROUP BY CustomerID
) o
ON c.CustomerID = o.CustomerID

--🔅Subquery In The WHERE Clause  :(COMPARISON) Operators
        /*Task 1.1 :  Find the products that have a price 
                    higher than the average price of all products */

--Main Query Model
SELECT 
          ProductID
         ,Price
FROM Sales.Products
WHERE Price > (SELECT AVG(Price) FROM Sales.Products ) --Subquery Buildup in WHERE clause
                                                                                                  --using WHERE  (Comparison) Operators

  --🔅Subquery In The WHERE Clause  :(LOGICAL) Operators   (IN , ANY , ALL ,EXISTS )
  /*Task 1.0 : Show the details of orders made by CUSTOMERS IN germany */

  --Main Query Model
  SELECT 
    *
  FROM Sales.Orders
  WHERE CustomerID IN (SELECT CustomerID FROM Sales.Customers WHERE Country = 'Germany')  ---Subquery Buildup

  /*Task 2.0 : Showing the details of orders made by CUSTOMERS NOT IN germany */
  --MAIN Query Model
  SELECT *
  FROM Sales.Orders
  WHERE CustomerID NOT IN (SELECT CustomerID FROM Sales.Customers WHERE Country = 'Germany')  ---Subquery Buildup

  /*Task 3.0 : Querying FEMALE Employees Whose Salaries Are Greater
                                     than The Salaries of ANY MALE Employees */

---Main Query Model
SELECT 
          EmployeeID
        ,CONCAT(FirstName, ' ' , LastName) [Full Name]
        ,Gender
        ,Salary
FROM Sales.Employees
WHERE Gender = 'F' AND Salary > ANY (SELECT Salary FROM Sales.Employees WHERE Gender = 'M') --Subquery BuildUp

 /*Task 3.0 : Querying MALE Employees Whose Salaries Are Greater
                                     than The Salaries of ALL FEMALE Employees */
---Main Query Model
SELECT 
          EmployeeID
        ,CONCAT(FirstName, ' ' , LastName) [Full Name]
        ,Gender
        ,Salary
FROM Sales.Employees
WHERE Gender = 'M' AND Salary > ALL (SELECT Salary FROM Sales.Employees WHERE Gender = 'F') --Subquery BuildUp


---/*  SUBQUERIES🔆 */   ---🔅CORRELATED SUB-QRY : EXISTS
                 /*Task 1.0 : Show The Orders Details Made By Customers In Germany */
---Main Query Model
SELECT *
FROM   Sales.Orders as o
WHERE EXISTS (SELECT 1 FROM Sales.Customers c WHERE Country = 'Germany' AND  c.CustomerID = o.CustomerID ) --Subquery buildup