First day of SQL learning-SQL exercises (Table creation/SQL statements)

Question: consumers from Madrid who have less than 3 orders

Table creation:
Copy codeThe Code is as follows:
Set nocount on -- when set nocount is ON, no count is returned (the number of rows affected by the Transact-SQL statement ). When set nocount is OFF, return count
Use SY
GO
If object_Id ('dbo. Orders ') is not null
Drop table dbo. Orders
GO
If object_Id ('dbo. customer') is not null
Drop table dbo. MERs
GO
Create table dbo. MERs
(
Customerid char (5) not null primary key,
City varchar (10) not null
);
Insert into dbo. MERs values ('fissa ', 'porter ');
Insert into dbo. MERs values ('frndo', 'porter ');
Insert into dbo. MERs values ('krlos ', 'porter ');
Insert into dbo. MERs values ('mrphs', 'zion ');

Create table dbo. Orders
(
Orderid int not null primary key,
Customerid char (5) null references MERs (customerid)
)
Insert into dbo. Orders values (1, 'frndo ');
Insert into dbo. Orders values (2, 'frndo ');
Insert into dbo. Orders values (3, 'krlos ');
Insert into dbo. Orders values (4, 'krlos ');
Insert into dbo. Orders values (5, 'krlos ');
Insert into dbo. Orders values (6, 'mrphs ');
Insert into dbo. Orders values (7, null );

Bytes ------------------------------------------------------------------------------------------------------------------------------

Question Analysis:
Copy codeThe Code is as follows:
Select customerid as consumer, count (customerid) as Order Number
From dbo. Orders
Where customerid in (
Select customerid
From dbo. Customers
Where city = 'Madrid ')
Group by customerid
Having count (customerid) <3

Result:

-- The first thought of the answer suddenly found that a FISSA order from Madrid was missing, and the number of FISSA Orders was 0, so it was not displayed in the Orders table, so the writing method above would be one less.

-- Overturned the above answer, and thought of using Table connections, and the use of internal connections will be the same as the above, so I chose the left join, as shown below:
Copy codeThe Code is as follows:
Select C. customerid as consumer, count (O. customerid) as Order Number
From dbo. Customers as C left join dbo. Orders as O on C. customerid = O. customerid
Where C. city = 'Madrid'
Group by C. customerid
Having count (C. customerid) <3

Result:

-- The query result is correct.

-- Analyze and view left connections without conditions
Copy codeThe Code is as follows:
Select * from dbo. Customers as C left join dbo. Orders as O on C. customerid = O. customerid
Copy codeThe Code is as follows:
Select * from dbo. Customers as C left join dbo. Orders as O on C. customerid = O. customerid

Result:

-- The standard answer in the book is:
Copy codeThe Code is as follows:
Select C. customerid, count (O. orderid) as numorders
From dbo. Customers as C left join dbo. Orders as O on C. customerid = O. customerid
Where C. city = 'Madrid'
Group by C. customerid
Having count (O. orderid) <3
Order by numorders

Result:

--The book only gives an additional order by to define the sorting method (sort by the ascending order of the numorders column)