Tuesday, 17 June 2014

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

No comments:

Post a Comment