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