SELECT <column list>
FROM <table names>
WHERE expression operator
               ( SELECT <column list>
                                             FROM <table names>)

SELECT  Customers.ContactName
FROM Customers
WHERE Customers.CustomerID IN
   (
    SELECT Orders.CustomerID
    FROM Orders
   );

SELECT Suppliers.ContactName
FROM Suppliers
WHERE  Suppliers.SupplierID IN
  (
   SELECT Products.SupplierID
   FROM Products
   WHERE Products.CategoryID=3
  );

SELECT Title
FROM Titles
WHERE Price >
(
   SELECT MIN(Price)
   FROM Titles
   WHERE Type = 'trad_cook'
);

SELECT <column list>
FROM <table names>,        ( SELECT <column list>
                                        FROM <table names>)

[WHERE <condition>]

SELECT C. ContactName "Cust Name", E.FirstName + ' ' + E.LastName "Emp Name"
FROM Customers C ,
           (
                            SELECT FirstName,LastName
                            FROM Employees
                                   WHERE Country = 'USA'
                                 ) E
WHERE C.Country = 'USA'
AND C.ContactTitle <> 'Sales Agent';

SELECT <column list> , ( SELECT <column list>
                                     FROM <table names>)

FROM <table names>      
[WHERE <condition>]

SELECT ContactName ,ContactTitle , (SELECT COUNT(*)
                                                               FROM Orders
                                                               WHERE Customers.CustomerId = Orders.CustomerId 
                           ) AS "Number of Orders"      
FROM Customers;