Max of a query which is inside Create a database for a drone delivery startup that distributes pizzas via drones. The startup has partnerships with several drone manufacturers and pizza shops. The entities of interest to the problem domain are: Drone Manufacturers, Drone Models, Drones, Pizza Shops, Customers, and Deliveries. A drone manufacturer has a name and can produce different drone models. A drone model belongs to a drone manufacturer; it has a name, battery life (in minutes, integer number) and maximum speed. An actual drone is of a particular model and has a serial number. A pizza shop has a name and address. A customer has a name and loyalty score. Pizza shop names and customer names are unique. For each delivery, the system records the corresponding customer and pizza shop, the drone that is used, as well as the date and time. This is how I proceeded:
CREATE TABLE DroneManufacturers(
    id INT PRIMARY KEY IDENTITY(1,1),
    name VARCHAR(30)
);

CREATE TABLE DroneModels(
    id INT PRIMARY KEY IDENTITY(1,1),
    manufacturer_id INT REFERENCES DroneManufacturers(id),
    name VARCHAR(30),
    battery_life INT,
    max_speed INT
);

CREATE TABLE Drones(
    serial_number VARCHAR(10) PRIMARY KEY,
    model_id INT REFERENCES DroneModels(id),
);

CREATE TABLE PizzaShops(
    name VARCHAR(30) PRIMARY KEY,
    address VARCHAR(50)
);

CREATE TABLE Customers(
    name VARCHAR(30) PRIMARY KEY,
    score INT
);

CREATE TABLE Deliveries(
    pizza_shop VARCHAR(30) REFERENCES PizzaShops(name),
    customer VARCHAR(30) REFERENCES Customers(name),
    drone VARCHAR(10) REFERENCES Drones(serial_number),
    delivery_time DATETIME
);

I think the design is ok, but I have some problems in the next query:
Create a view that shows the names of the startup’s favorite drone manufacturers, i.e., those with the largest number of drones used by the startup.
Example: suppose the startup has partnerships with 3 manufacturers: M1, M2, M3; it has 10 drones from M1, 10 drones from M2, and 8 drones from M3. M1 and M2 are the manufacturers with the largest number of drones.
I have tried like this:
SELECT t.name
FROM (
    SELECT dman.id, dman.name, COUNT(*) AS counts
    FROM Drones d
    INNER JOIN DroneModels dm
        ON d.model_id = dm.id
    INNER JOIN DroneManufacturers dman
        ON dm.id = dman.id
    GROUP BY dman.id, dman.name
    ) t
WHERE t.counts = MAX(t.counts)

And I get an error because the last &quot;where&quot; clause is not written properly. Can somebody help me with this query, please?
Just a heads up that you can't use aggregate functions (like MAX()) in a WHERE clause, rather that's the purpose of the HAVING clause. But instead of using an aggregate function, a window function like DENSE_RANK() simplifies the solution you're looking for, like so:
CREATE VIEW FavoriteDroneManufacturers AS

SELECT s.[name]
FROM
(
    SELECT dman.[name], DENSE_RANK() OVER (ORDER BY COUNT(1) DESC) AS DroneCountRankId
    FROM Drones d
    INNER JOIN DroneModels dm
        ON d.model_id = dm.id
    INNER JOIN DroneManufacturers dman
        ON dm.id = dman.id
    GROUP BY dman.id, dman.[name]
) AS s
WHERE s.DroneCountRankId = 1

(Note the above uses a subquery since you were weary of using CTEs, but the logic inside the subquery could've just as equally been created in a CTE instead, and a View can equivalently be created on both either way.)
DENSE_RANK() generates an ID for each row ordered by the logic you specify in the ORDER BY clause. When there are two rows that tie for ordering (based on the logic specified) then the same ID will be generated for both.
In the above query, I'm ordering (by descending) on the grouped COUNT() of drones for each manufacturer, and when two manufacturers share the same COUNT() of drones, then they get the same DroneCountRankId. In the outer query, I filter on DroneCountRankId = 1 to give us only the manufacturer(s) with the max number of drones (if it's two manufacturers like in your example, then they are both returned this way). (If you changed the WHERE clause to DroneCountRankId = 2 then you'd get all manufacturer(s) with the top 2 number of drones, etc.)
I highly recommend reading up on window functions and the DENSE_RANK() function in particular, via the links I provided above.