Subqueries

SELECT Name FROM Cities
WHERE Id IN (
    SELECT CityId FROM Customers
    WHERE FirstName LIKE 'F%'
);

SELECT AVG(NumOfIngredients) FROM (
    SELECT COUNT(*) AS NumOfIngredients FROM PizzaIngredients
    GROUP BY PizzaId
) T;

SELECT Street, City.ZipCode, City.Name FROM Customers
JOIN City ON Customers.CityId = City.Id
WHERE Customers.Id IN (
    SELECT CustomerId FROM Orders
    WHERE Id = 3
);

SELECT Name FROM Ingredients
WHERE Id IN (
    SELECT IngredientId FROM PizzaIngredients
    WHERE PizzaId IN (
        SELECT Id FROM Pizzas
        WHERE Price = (
            SELECT MAX(Price) FROM Pizzas
        )
    )
);

SELECT Name FROM Ingredients
WHERE Id IN (
    SELECT IngredientId
    FROM (SELECT IngredientId, COUNT(*) as Count
          FROM PizzaIngredients GROUP BY IngredientId) T
    WHERE Count = (
        SELECT MAX(Count)
        FROM (SELECT IngredientId, COUNT(*) as Count
              FROM PizzaIngredients GROUP BY IngredientId) TT
    )
);