SQL Subquery ,Adding Subqueries to the SELECT Clause,Adding Subqueries to the FROM Clause, Adding Subqueries to the WHERE Clause

SQL Subquery Statement

SELECT * FROM TABLE1 WHERE sales in (SELECT Sales FROM TABLE2)

SELECT * FROM TABLE1 WHERE sales LIKE (SELECT Sales FROM TABLE2 WHERE Sales <2900)>

SELECT * FROM TABLE1where sales > (SELECT Sales FROM TABLE2 WHERE Sales <2900)

Adding Subqueries to the SELECT Clause

SELECT  SalesOrderNumber,  SubTotal,  OrderDate,  (

    SELECT SUM(OrderQty)    FROM Sales.SalesOrderDetail    WHERE SalesOrderID = 43659

  ) AS TotalQuantity

FROM  Sales.SalesOrderHeader

WHERE  SalesOrderID = 43659;

SELECT  SalesOrderNumber,  SubTotal,  OrderDate,

  CASE WHEN

    (

      SELECT SUM(LineTotal)      FROM Sales.SalesOrderDetail      WHERE SalesOrderID = 43659

    ) =  SubTotal THEN 'balanced'    ELSE 'not balanced'

  END AS LineTotals

FROM  Sales.SalesOrderHeader

WHERE

  SalesOrderID = 43659;

Adding Subqueries to the FROM Clause

SELECT

                .ProductID,  p.Name AS ProductName,  p.ProductSubcategoryID AS SubcategoryID,  ps.Name AS SubcategoryName

FROM

  Production.Product p INNER JOIN

  (

    SELECT ProductSubcategoryID, Name

    FROM Production.ProductSubcategory

    WHERE Name LIKE '%bikes%'

  ) AS ps

  ON p.ProductSubcategoryID = ps.ProductSubcategoryID;

Adding Subqueries to the WHERE Clause

SELECT

                                sinessEntityID,  FirstName,  LastName

FROM

  Person.Person

WHERE

  BusinessEntityID =

  (

    SELECT BusinessEntityID    FROM HumanResources.Employee    WHERE NationalIDNumber = '895209680'

  );

SELECT

  p.BusinessEntityID,  p.FirstName,  p.LastName,  s.SalesQuota

FROM

  Person.Person p INNER JOIN  Sales.SalesPerson s

  ON p.BusinessEntityID = s.BusinessEntityID

WHERE

  s.SalesQuota IS NOT NULL AND

  s.SalesQuota >

  (

    SELECT AVG(SalesQuota)

    FROM Sales.SalesPerson

  );

SELECT

  BusinessEntityID,  FirstName,  LastName

FROM

  Person.Person

WHERE

  BusinessEntityID IN

  (

    SELECT BusinessEntityID

    FROM HumanResources.Employee

    WHERE JobTitle = 'Sales Representative'

  );

SELECT

                                                sinessEntityID,  FirstName,  LastName

FROM

  Person.Person

WHERE

  BusinessEntityID NOT IN

  (

    SELECT BusinessEntityID

    FROM HumanResources.Employee

    WHERE JobTitle = 'Sales Representative'

  );

SELECT ProductID, Name AS ProductName FROM Production.Product p

 WHERE EXISTS (

 SELECT * FROM Production.ProductSubcategory s WHERE p.ProductSubcategoryID = s.ProductSubcategoryID

AND s.Name = 'Mountain Bikes'

 );

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

SELECT

                                roductID,  Name AS ProductName

FROM

  Production.Product p

WHERE NOT EXISTS

  (

    SELECT *

    FROM Production.ProductSubcategory s

    WHERE p.ProductSubcategoryID = s.ProductSubcategoryID

      AND s.Name = 'Mountain Bikes'

  );

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

SELECT NAME

FROM SALESREPS

WHERE QUOTA < (.1 * (SELECT SUM(TARGET) FROM OFFICES))

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

select Company.Name, Company.Region,

  (select sum(Amount) from Orders where Orders.CompanyID = Company.CompanyID) as Total

from Company

select Company.Region,

                sum(select sum(Amount) from Orders             where Orders.CompanyID = Company.CompanyID) as Total

from Company

group by Company.Region